游标的概念:
游标是一个内存工作区,由系统或者用户以变量的形式定义。其作用就是临时存储从数据库提取的数据块。(将数据从磁盘表中提取出来到计算机内存,最后将处理结果显示出来和写回到数据库)
游标分类:
隐式游标和显式游标
隐式游标:
一次只能从数据库中提取一行数据,包括查询与DML(insert update delete select… into )
通过属性来了解操作的状态,从而控制程序流程(通过SQL游标名只能访问前一个DML和单行select操作的游标属性即执行完立即使用SQL游标名来访问属性)。
例如:
SQL%ROWCOUNT ----DML成功执行的数据行
SQL%FOUND ---- 布尔型 true代表操作成功
SQL%NOTFOUND -----与上一个返回值相反
SQL%ISOPEN -----DML执行过程中为真,结束为假
显式游标:
提取多行数据
1.声明游标(declare部分)
CURSOR cursor_name [(para1 datatype[,para2 datatype])]
IS SELECT … ;
注意:定义了参数打开游标时候需要传递参数 。select可以对表或视图或者联合查询可以带where、odery by、group by子句,但不能使用into子句。也可以使用定义在游标之前的语句。
2.打开游标
可执行部分打开游标
OPEN cursor_name [(para1[,para2])]
打开游标时候,查询的结果就被传送到游标工作区了。
3.提取数据
在可执行部分,按以下格式将数据提取到变量中,打开游标之后的操作
FETCH cursor_name into var_name[,var_name2…]
注意:变量名字需要提前定义,个数与类型与select语句一致。
or
FETCH cursor_name into var_records;
注意:需要%ROWTYPE事先定义记录变量。
注意:游标打开后有一个指针指向数据区,fetch 一次返回指针指向的一行数据,要返回多行需要重复执行,需要用循环语句来实现。控制循环可以用判断游标的属性来进行。
4.关闭游标
关闭后,游标占用的资源就被释放了,游标无效,需要重新打开才能使用。
例子:
–取出一条
declare
v_empno NUMBER;
v_ename varchar(10);
CURSOR cursor1 is
select t.empno, t.ename from emp t where empno = '7369';
begin
open cursor1;
FETCH cursor1
INTO v_empno, v_ename;
dbms_output.put_line('用户编号:' || v_empno || '用户名字:' || v_ename);
close cursor1;
end;
–取出三条
declare
v_empno NUMBER;
v_ename varchar(10);
CURSOR cursor1 is
select t.empno, t.ename from emp t;
begin
open cursor1;
for i in 1 .. 3 loop --注意for循环的点为两个
FETCH cursor1
INTO v_empno, v_ename;
dbms_output.put_line('用户编号:' || v_empno || '用户名字:' || v_ename);
end loop;
close cursor1;
end;
–特殊for循环打印(没有open 游标与close游标)
declare
CURSOR cursor1 is
select t.empno, t.ename from emp t;
begin
for Emp_record in cursor1 loop
dbms_output.put_line('用户编号:' || Emp_record.empno || '用户名字:' || Emp_record.ename);
end loop;
end;
注意:Emp_record为隐含定义的记录变量,循环执行的次数与游标取得的数据行相一致。
–另一种形式
declare
begin
for res in (select t.empno, t.ename from emp t) loop
dbms_output.put_line('用户编号:' || res.empno || '用户名字:' || res.ename);
end loop;
end;
注意:省略了游标的定义,游标的select查询语句在循环中直接出现。
显式游标属性:
游标名%属性(属性与静态的属性一致)
特别注意:如果循环的次数大于数据行数,则会循环打印最后一行直到循环结束。
–带参数的游标
declare
v_empno NUMBER;
v_ename varchar(10);
CURSOR cursor1(v_deptno number) is
select t.empno, t.ename from emp t where t.deptno=v_deptno;
begin
open cursor1(10);
for res in 1 .. 3 loop
FETCH cursor1
INTO v_empno, v_ename;
dbms_output.put_line('用户编号:' ||v_empno || '用户名字:' || v_ename);
end loop;
dbms_output.put_line(cursor1%rowcount);
close cursor1;
end;
–变量方式
declare
v_empno NUMBER;
v_ename varchar(10);
v_deptno number;
CURSOR cursor1 is
select t.empno, t.ename from emp t where t.deptno=v_deptno;
begin
v_deptno:=10;
open cursor1;
loop
FETCH cursor1
INTO v_empno, v_ename;
dbms_output.put_line('用户编号:' ||v_empno || '用户名字:' || v_ename);
EXIT WHEN cursor1%NOTFOUND;
end loop;
close cursor1;
end;
动态游标
oracle支持动态select与动态游标,动态的方法大大扩展程序设计能力。
使用动态生成查询语句字符串的方法,在程序执行阶段临时的生成并执行。
execute immediate 查询字符串 into 变量1[,变量2…]
例子:
declare
v_empno NUMBER;
v_ename varchar(10);
str varchar(100);
例子:
begin
str := 'select empno,ename from scott.emp where empno=7499';
execute immediate st
into v_empno, v_ename;
dbms_output.put_line('用户编号:' || v_empno || '用户名字:' || v_ename);
end;
注意:使用动态游标需要先定义一个游标类型,然后声明一个游标变量,游标对应的查询语句可以在程序的执行过程中动态说明。
declare
type cur_type is ref cursor;
cursor1 cur_type;
v_empno NUMBER;
v_ename varchar2(10);
str varchar2(100);
v_par2 number(10):=7369;
begin
str := 'select empno,ename from emp where empno = '||v_par2||'';
open cursor1 for str;
fetch cursor1
into v_empno, v_ename;
dbms_output.put_line('pare:'||v_par2);
dbms_output.put_line('用户编号:' || v_empno || '用户名字:' || v_ename);
end;
特别注意:参数v_par2必须用单引号和两个连接符||
总结
有志者,事竟成。