Oracle复习笔记(六)
------------------------------*/
游标：指向查询结果集的指针（对查询结果集的引用）
一、隐式游标(例：SQL%rowcount)
1、Oracle自动为增删改操作创建隐式游标，名称统一为SQL
2、用于获取最近的增删改操作对数据库的影响（在提交或回滚之前）
3、游标属性
1）%rowcount 返回受影响的行数
2）%found 如果影响了数据，则返回true否则返回false
3）%notfound 如果没影响数据，则返回true否则返回false
4）%isopen 如果游标打开，则返回true否则返回false（隐式游标始终返回false）
二、显式游标(例：cursor my_cursor is select * from emp)
1、显示游标必须在声明部分显式声明
2、用于处理返回多行的查询结果集，便于用户逐行处理数据
3、游标属性
1）%rowcount 返回游标所在的行号
2）%found 如果找到了数据（fetch成功），则返回true否则返回false
3）%notfound 如果没找到数据（fetch失败），则返回true否则返回false
4）%isopen 如果游标打开，则返回true否则返回false
三、for循环游标：简化游标的操作（自动打开、提取数据、关闭）
declare
cursor mycursor
is
select * from emp;
begin
--'r'表示游标中结果集对应的行类型变量
for r in mycursor
loop
if(r.sal>=3000) then
dbms_output.put_line(mycursor%rowcount||'-'||r.ename||'-'||r.sal);
end if;
end loop;
end;
四、带参数的游标：提高游标的灵活性
--eg 根据部门编号查询员工信息
declare
--声明参数，类型不能指定长度
cursor mycursor(d_no number)
is
select * from emp where deptno=d_no;
begin
for r in mycursor('&d_no') --传参
loop
dbms_output.put_line(r.ename);
end loop;
end;
五、使用游标更新数据
1）使用select..for update nowait;给游标加锁
2）使用where current of 游标名;限定更新游标所在行
--eg 给员工加薪（10+100,20+200,30+300）
declare
money number;
cursor mycursor
is
select * from emp for update nowait;--给游标加锁
begin
for r in mycursor
loop
if(r.deptno=10) then
money:=100;
elsif(r.deptno=20) then
money:=200;
elsif(r.deptno=30) then
money:=300;
end if;
--where current of 游标名; 限定更新游标所在行
update emp set sal=sal+money where current of mycursor;
end loop;
end;
六、REF游标（动态游标）
1、用于处理运行时才能确定的查询结果集
2、REF游标必须在声明部分：
1）声明REF游标类型，type 类型�
� is ref cursor;
2）声明REF游标变量，变量名 类型名;
3) 动态游标不能带参数？
--查询每个员工的姓名和薪水
declare
type my_refcursor_type is ref cursor;
my_refcursor my_refcursor_type;
emp_row emp%rowtype;
begin
open my_refcursor for select * from emp;
loop
fetch my_refcursor into emp_row;
exit when my_refcursor%notfound;
dbms_output.put_line(emp_row.ename || ':' ||emp_row.sal);
end loop;
close my_refcursor;
end;
/*----------------------------------------存储过程、函数、复合类型(record,table)、包----------------------------------------*/
一、分类
1）过程 procedure，完成特定功能
2）函数 function，完成特定功能并返回1个结果
2、优点
1）模块化，按业务功能进行封装
2）重用性好，易于维护
3）执行效率高，减少网络流量的占用
4）安全性高（涉及权限管理）
二、组成
1）声明部分（必须的） create [or replace]...
2）可执行部分（必须的） begin...end;
3）异常处理部分（可选的） exception...
三、参数模式
1）输入参数 in
接收用户输入，不允许在过程中修改，如果未指定参数模式默认为输入参数
2）输出参数 out
向用户返回结果，必须声明变量传参
3）输入输出参数 in out
既接收用户输入，又向用户返回结果
四、创建语法
过程：
create [or replace]
procedure 过程名[(参数列表)]
as | is
[变量列表;]
begin
可执行代码;
[exception
when others then null;]
end;
函数：
create [or replace]
function 函数名[(参数列表)]
return 返回类型
as | is
[变量列表;]
begin
可执行代码;
return 返回值;
[exception
when others then return null;]
end;
五、删除子程序
drop procedure 过程名;
drop function 函数名;
--根据员工姓名查询工资(过程)
create or replace procedure myproc1(e_ename in varchar2,e_sal out number)
is
v_sal emp.sal%type;
begin
select sal into e_sal from emp where ename = e_ename;
end;
declare
sal number(20);
begin
myproc1('KING',sal);
dbms_output.put_line('SAL:' || sal);
end;
--根据员工姓名查询编号(函数)
create or replace function myfun1(e_ename varchar2) return number
is
v_empno emp.empno%type;
begin
select empno into v_empno from emp where ename = e_ename;
return v_empno;
end;
declare
empno emp.empno%type;
begin
empno := m