用table类型变量保存s_region表中id为1，2，3信息，
并遍历输出（可以使用循环输出）
declare
type regiontable is table of s_region%rowtype
index by binary_integer;
var_regions regiontable;
var_ind binary_integer;
begin
select * into var_regions(1) from s_region where id=1;
select * into var_regions(2) from s_region where id=2;
select * into var_regions(3) from s_region where id=3;
var_ind:=var_regions.first();
loop
dbms_output.put_line(var_regions(var_ind).id||':'||
var_regions(var_ind).name);
exit when var_ind=3;
var_ind:=var_regions.next(var_ind);
endloop;
end;
/
---------------------------------------------------------
1、动态sql
1.1 概念
1.2 举例(ddl)
ddl语句不能直接在plsql中使用，必须用动态sql实现
1.3dml的动态sql
1)dml语句可以直接在plsql中使用
2)常规的字符串的拼接
3)带变量的字符串的拼接
4)用占位符配合using解决字符串的拼接问题
:标识符 占位符
execute immediate sqlstr using 变量列表;
1.4select语句的动态sql
/*定义两个变量，类型分别和s_emp表中的id,first_name
相同，使用查询语句查询id=1的员工的id,first_name
存入变量并输出 */
declare
sqlstr varchar2(100);
var_id s_emp.id%type;
var_name s_emp.first_name%type;
begin
sqlstr:='select id,first_name from s_emp where id=1';
execute immediate sqlstr into var_id,var_name;
dbms_output.put_line(var_id||':'||var_name);
end;
/
2、游标 cursor
2.1作用
处理多行的结果集
2.2游标的使用步骤
1)声明游标
cursor 游标名 is select语句;
2)打开游标
open 游标名;
3)提取数据
fetch 游标名 into 变量;
4)关闭游标
close 游标名;
2.3把s_emp表中所有数据保存在游标中，遍历输出
declare
/* 声明游标 */
cursor empscursor is select * from s_emp;
/* 声明变量接收一行数据 */
var_emp empscursor%rowtype;
begin
/* 打开游标 */
open empscursor;
/* 提取数据 */
fetch empscursor into var_emp;
dbms_output.put_line(var_emp.id||':'
||var_emp.first_name);
fetch empscursor into var_emp;
dbms_output.put_line(var_emp.id||':'
||var_emp.first_name);
/* 关闭游标 */
close empscursor;
end;
/
2.4如何使用循环遍历游标？-- 循环条件或者退出条件
使用游标的属性
游标名%属性
found 在提取数据时，如果提取到了新数据则返回真
如果没有提取到新数据则返回假
如果没有打开游标，则返回非法游标
如果没有fetch，则返回null
notfound 在提取数据时，如果提取到了新数据则返回假
如果没有提取到新数据则返回真
如果没有打开游标，则返回非法游标
如果没有fetch，则返回null
isopen 判断游标的状态是否为打开
已打开返回真，没有打开返回假
打开的游标不能再打开
关闭的游标不能再关闭
rowcount 游标的偏移量
2.5使用简单循环遍历游标
declare
/* 声明游标 */
cursor empscursor is select * from s_emp;
/* 声明变量接收一行数据 */
var_emp empscursor%rowtype;
begin
/* 打开游标 */
open empscursor;
/* 循环提取数据 */
loop
fetch empscursor into var_emp;
exit when empscursor%notfound;
dbms_output.put_line(var_emp.id||':'
||var_emp.first_name);
end loop;
/* 关闭游标 */
close empscursor;
end;
/
2.6使用while循环遍历游标
declare
/* 声明游标 */
cursor empscursor is select * from s_emp;
/* 声明变量接收一行数据 */
var_emp empscursor%rowtype;
begin
/* 打开游标 */
open empscursor;
/* 循环提取数据 */
fetch empscursor into var_emp;
while empscursor%found loop
dbms_output.put_line(var_emp.id||':'
||var_emp.first_name);
fetch empscursor into var_emp;
end loop;
/* 关闭游标 */
close empscursor;
end;
/
2.7使用for循环遍历游标
for循环：智能循环（自动打开游标、提取数据、关闭游标）
declare
cursor empscursor is select * from s_emp;
begin
for var_emp in empscursor loop
dbms_output.put_line(var_emp.id||':'
||var_emp.first_name);
end loop;
end;
2.8带参的游标
select * from s_emp where id>10;
cursor 游标名(参数) is select 语句;
参数的数据类型：不允许带长度和精度的修饰
可以使用表名.字段%type的方式
打开游标的时候传值
open 游标名(实参);
declare
cursor empscursor(var_id number)
is select * from s_emp where id>var_id;
begin
for var_emp in empscursor(16) loop
dbms_output.put_line(var_emp.id||':'||
var_emp.first_name||':'||
var_emp.salary);
end loop;
end;
/
2.9参考游标 ref cursor
游标 + 动态sql
参考游标的使用步骤
sqlstr:='select * from s_emp';
1)定义参考游标类型
type 参考游标类型名 is ref cursor;
2)声明参考游标类型变量
3)把参考游标变量和动态sql语句结合
open 参考游标变量 for sqlstr;
declare
/* 定义参考游标类型 */
type emprefcursor is ref cursor;
/* 声明参考游标变量 */
empscursor emprefcursor;
/* 声明变量接收一行数据 */
var_emp s_emp%rowtype;
/* 动态sql字符串 */
sqlstr varchar2(100);
begin
sqlstr:='select * from s_emp';
/* 参考游标变量和动态sql结合 */
open empscursor for sqlstr;
/* 循环提取数据 */
loop
fetch empscursor into var_emp;
exit when empscursor%notfound;
dbms_output.put_line(var_emp.id||':'||
var_emp.first_name||':'||
var_emp.salary);
end loop;
/* 关闭游标 */
close empscursor;
end;
/
/* 动态sql 字符串带占位符的情况 */
declare
type emprefcursor is ref cursor;
var_empscursor emprefcursor;
var_emp s_emp%rowtype;
sqlstr varchar2(100);
begin
sqlstr:='select * from s_emp where id>:b0';
/* using后边可以使用变量，也可以使用常量通常是变量 */
open var_empscursor for sqlstr using 16;
loop
fetch var_empscursor into var_emp;
exit when var_empscursor%notfound;
dbms_output.put_line(var_emp.id||':'||
var_emp.first_name||':'||
var_emp.salary);
end loop;
close var_empscursor;
end;
3、plsql中的异常
3.1异常处理的步骤
1）定义异常
2）根据条件 引发异常
3）捕获异常
4）处理异常
3.2异常的分类
1）系统预定义异常
oracle定义和自动引发
2）系统非预定义异常
自己定义 oracle引发
3）自定义异常
3.3常用的系统预定义异常
cursor_already_open:试图打开已打开的游标
invalid_cursor:在不合法的游标上操作
invalid_number:内嵌的sql无法将字符串转换成数字
dup_val_on_index:在唯一性的字段上有重复值
no_data_found:在执行select into语句时，未返回行
too_many_rows:在执行select into语句时，返回超过一行的数据
zero_divide:除数为0
3.4案例：预定义异常的使用
declare
var_id s_emp.id%type;
var_name s_emp.first_name%type;
var_i number:=100;
begin
select id,first_name into var_id,var_name
from s_emp where id<var_i;
dbms_output.put_line(var_id||':'||var_name);
exception
when no_data_found then
dbms_output.put_line('no employee whose number is '||
var_i);
--when too_many_rows then
--dbms_output.put_line('too many rows');
when others then
dbms_output.put_line('others:'||sqlcode||'###'
||sqlerrm);
end;
/
3.5非预定义异常的处理语法
declare
异常名称 exception;
pragma exception_init(异常名称,错误编号);
begin
exception
when 异常名称 then
异常处理
end;
/
3.6自定义异常
raise 引发异常
declare
/* 定义异常 */
too_many_emps exception;
var_num number;
begin
select count(id) into var_num from s_emp
where salary>1500;
/* 根据条件引发异常 */
if var_num>2 then
raise too_many_emps;
end if;
exception
when too_many_emps then
dbms_output.put_line('too many emps');
end;
/
4、存储过程--procedure
4.1 匿名块和有名块
/* 定义两个变量，输出最大值 */
declare
var_a number:=10;
var_b number:=20;
begin
if var_a>var_b then
dbms_output.put_line(var_a);
else
dbms_output.put_line(var_b);
end if;
end;
/
匿名块：
不保存在数据库中
每次使用会进行编译
不能被其它的块调用
有名块：
一般保存在数据库中
可以在需要的时候调用
procedure function package trigger
4.2创建存储过程的语法
create [or replace] procedure 过程名[(
参数名[{in|out|in out}]类型[:=值|default 值]
)]
{is|as}
-- 临时变量
begin
-- 执行的操作
end;
/
4.3无参的存储过程
create or replace procedure getmax_zsm_00
is
var_a number:=10;
var_b number:=20;
begin
if var_a>var_b then
dbms_output.put_line(var_a);
else
dbms_output.put_line(var_b);
end if;
end;
/
/* 查看警告 */
show errors;
4.4调用无参的存储过程
begin
getmax_zsm_00;
end;
/
4.5带参的存储过程的定义
参数的数据类型不允许带有任何长度、精度的修饰
create or replace procedure getmax_zsm_00(
a number:=10,b in number)
is
begin
if a>b then
dbms_output.put_line(a);
else
dbms_output.put_line(b);
end if;
end;
4.6参数的模式和默认值
/*查看存储过程 */
desc getmax_zsm_00;
PROCEDURE getmax_zsm_00
Argument Name Type In/Out Default?
------------------- ------------ ------ --------
A NUMBER IN DEFAULT
B NUMBER IN
in: 输入参数 调用时向过程中传值
缺省方式(默认)
值或者赋值后的变量
out: 输出参数 从过程中返回值
变量，不需要赋值
inout:输入输出参数 调用时向过程传入一个值，
在过程中重新赋值后带回
赋值后的变量
默认值：有默认值的参数可以不用传参
4.7有参的存储过程的调用
1)按位置传参
declare
var_a number:=100;
var_b number:=200;
begin
getmax_zsm_00(20,30);
getmax_zsm_00(var_a,var_b);
end;
/
2)按名称传参
参数名称=>参数值
declare
var_b number:=2;
begin
getmax_zsm_00(b=>var_b);
end;
/
4.8练习：创建一个存储过程，
有两个参数，输出两个参数的最大值
把两数之和保存在第二个参数中
测试这个存储过程
create or replace procedure getmax_zsm_00(
a in number,b in out number
)
is
begin
if a>b then
dbms_output.put_line(a);
else
dbms_output.put_line(b);
end if;
b:=a+b;
end;
/
declare
var_b number:=100;
begin
getmax_zsm_00(50,var_b);
dbms_output.put_line(var_b);
end;
/
5、函数 --function
5.1 函数和存储过程的区别
1）关键字不同 存储过程是procedure 函数是function
2）函数有返回值和返回类型，存储过程没有
3）调用时，在plsql中存储过程是直接调用，函数要组成表达式调用
5.2案例：创建函数，传入两个参数，返回最小值
create or replace function getmin_zsm_00(
a in number,b number
)return number
is
begin
if a<b then
return a;
else
return b;
end if;
end;
调用函数：
declare
var_res number;
begin
var_res:=getmin_zsm_00(10,20);
dbms_output.put_line(var_res);
end;
/
5.3练习：创建一个函数，
传入两个参数，返回两个参数的最小值
并把两数之和保存在第二个参数中
测试这个函数
create or replace function getmin_zsm_00(
a in number,b in out number
) return number
is
temp number;
begin
temp:=b;
b:=a+b;
if a<temp then
return a;
else
return temp;
end if;
end;
/
/* 调用函数 */
declare
var_res number;
var_a number:=10;
var_b number:=100;
begin
var_res:=getmin_zsm_00(var_a,var_b);
dbms_output.put_line('sum='||var_b);
dbms_output.put_line('min='||var_res);
end;
/
6.包 -- package
6.1概念
对相关的函数、存储过程、变量、类型、游标、异常等的封装
6.2常用的系统包
dbms_output:输出包
dbms_random:随机包
dbms_job:任务调度包
/* 查看包 */
desc dbms_output;
desc dbms_random;
desc dbms_job;
/* 调用包中的数据时，前面加包名 */
begin
dbms_output.put_line(dbms_random.value(1,100));
end;
6.3自定义包
1）包由包规范和包主体构成
包规范：
create or replace package 包名
is|as
procedure 过程名(参数列表);
function 函数名(参数列表) return type;
end [包名];
/
包主体：
create or replace package body 包名
is|as
procedure 过程名(参数列表)
is
begin
end;
function 函数名(参数列表) return type
is
begin
end;
end [包名];
/
/* 创建包规范 */
create or replace package mypack_zsm_00
is
procedure getmax(a number,b number);
function getmin(a number,b number) return number;
end mypack_zsm_00;
/
/* 创建包主体 */
create or replace package body mypack_zsm_00
is
procedure getmax(a number,b number)
is
begin
if a>b then
dbms_output.put_line(a);
else
dbms_output.put_line(b);
end if;
end;
function getmin(a number,b number) return number
is
begin
if a<b then
return a;
else
return b;
end if;
end;
end mypack_zsm_00;
/
/* 调用包中的数据 */
begin
mypack_zsm_00.getmax(10,200);
dbms_output.put_line(mypack_zsm_00.getmin(10,200));
end;
/
7、触发器 -- trigger
7.1 概念
在数据库中保存的，隐式执行的有名块
dml触发器
7.2 语法
create or replace trigger 触发器名
before|after dml{insert|delete|update}
on 表名 { |for each row}
declare
begin
end;
/
7.3 语句级触发器
/* 复制s_emp */
create table emp_zsm_00
as select id,first_name,salary from s_emp;
create or replace trigger emp_tri_zsm_00
before update
on emp_zsm_00
declare
begin
dbms_output.put_line('table updated');
end;
/
update emp_zsm_00 set salary=salary+100 where id=1;
update emp_zsm_00 set salary=salary+100 where id<1;
update emp_zsm_00 set salary=salary+100 where id>1;
7.4行级触发器
create or replace trigger emp_tri_zsm_00
before update
on emp_zsm_00 for each row
declare
begin
dbms_output.put_line('table updated');
dbms_output.put_line('old: '||:old.id||' '||:old.salary);
dbms_output.put_line('new: '||:new.id||' '||:new.salary);
end;
/
update emp_zsm_00 set salary=salary+100 where id=1;
update emp_zsm_00 set salary=salary+100 where id<1;
update emp_zsm_00 set salary=salary+100 where id>1;
表名%rowtype类型
:old
:new
insert delete update
:old :old
:new :new
注意：触发器中不能使用事务控制语句
--------------------------------------------------------
总结：
1、动态sql
2、游标
3、异常
4、存储过程
5、函数
6、包
7、触发器
练习：
1.使用游标保存s_dept表中的全部信息并遍历输出
2.设计一个存储过程，包括两个参数(n,n_sum)
其中第一个参数代表从1加到的数字
第二个参数保存前n项的和
测试这个存储过程