点击“蓝字”关注我们
晟数学院DBA成长日记--PL/SQL篇
游标 上篇
目标
掌握游标的主要作用
可以操作隐式游标与显式游标
可以对游标数据进行修改
定义游标变量
在使用SQL编写查询语句时，所有的查询结果会直接显示给用户，但是在很多情况下，用户需要对返回结果中的每一条数据分别进行操作，则这个时候普通的查询语句就无法使用了，那么就可以通过结果集（由查询语句返回完整的行集合叫做结果集）来接收，之后就可以利用游标来进行操作。
隐式游标
在PL/SQL块之中所编写的每条SQL语句实际上是隐式游标。通过在DML操作之后使用“SQL%ROWCOUNT”属性，可以知道语句所改变的行数（INSERT、 UPDATE、DELETE返回更新行数，SELECT返回查询行数）。
（1）验证ROWCOUNT
SCOTT@SDEDU> ed11401.sql
declare
v_count number;
begin
select count(*) into v_count from dept;
dbms_output.put_line('SQL%rowcount'||SQL%rowcount);
end;
/
SCOTT@SDEDU> @11401
SQL%rowcount1
PL/SQL procedure successfully completed.
（2）验证ROWCOUNT，增加新数据返回行数
SCOTT@SDEDU> ed11402.sql
declare
begin
insert into dept(deptno,dname,loc) values(90,'DATA','pk');
dbms_output.put_line('SQL%rowcount= '||SQL%rowcount);
end;
/
SCOTT@SDEDU> @11402
SQL%rowcount= 1
PL/SQL procedure successfully completed.
（3）单行隐式游标
SCOTT@SDEDU> ed11403.sql
declare
v_empRow emp%rowtype; --保存emp每行记录
begin
select * into v_empRow from emp where empno=7369;
if sql%found then
dbms_output.put_line('name is: '||v_empRow.ename||' job is: '||v_empRow.job);
end if;
end;
/
SCOTT@SDEDU> @11403
name is: SMITH job is: CLERK
PL/SQL procedure successfully completed.
（4）更新多行记录
SCOTT@SDEDU> ed11404.sql
declare
begin
update emp set sal=sal*1.2;
if sql%found then
dbms_output.put_line('update rowcount is: '||sql%rowcount);
else
dbms_output.put_line('no update!');
end if;
end;
/
SCOTT@SDEDU> @11404
update rowcount is: 15
PL/SQL procedure successfully completed.
显示游标
隐式游标是用户操作SQL时自动生成的，而显式游标指的是在声明块中直接定义的游标，而在每一个游标之中，都会保存SELECT查询后的返回结果，显式游标的创建语法如下所示：
创建显式游标
CURSOR 游标名称([参数列表]) [RETURN 返回值类型]
IS 子查询
[FOR UPDATE [OF 数据列, 数据列,)] [NOWAIT]];
（5）定义显式游标
SCOTT@SDEDU> ed11405.sql
DECLARE
CURSOR cur_emp IS SELECT * FROM emp ;
v_empRow emp%ROWTYPE ;
BEGIN
IF cur_emp%ISOPEN THEN -- 游标已经打开
NULL ;
ELSE -- 游标未打开
OPEN cur_emp ; -- 打开游标
END IF ;
FETCH cur_emp INTO v_empRow ;
WHILE cur_emp%FOUND LOOP -- 判断是否有数据
DBMS_OUTPUT.put_line(cur_emp%ROWCOUNT || '、雇员姓名：' || v_empRow.ename || '，职位：' || v_empRow.job || '，工资：' || v_empRow.sal) ;
FETCH cur_emp INTO v_empRow ;-- 把游标指向下一行 END LOOP ;
CLOSE cur_emp ; -- 关闭游标
END ;
/
（6）使用FOR循环操作游标
SCOTT@SDEDU> ed11407.sql
declare
cursor cur_emp is select * from emp;
begin
for v_emprow in cur_emp loop--不用声明就可使用，相当于引导参数
dbms_output.put_line(cur_emp%rowcount||' name is: '||v_emprow.ename||' job is: '||v_emprow.job||' sal is: '||v_emprow.sal);
end loop;
end;
/
SCOTT@SDEDU> @11407
1 name is: SMITH job is: CLERK sal is: 960
2 name is: ALLEN job is: SALESMAN sal is: 1920
3 name is: WARD job is: SALESMAN sal is: 1500
4 name is: JONES job is: MANAGER sal is: 3570
5 name is: MARTIN job is: SALESMAN sal is: 1500
6 name is: BLAKE job is: MANAGER sal is: 3420
7 name is: CLARK job is: MANAGER sal is: 2940
8 name is: SCOTT job is: ANALYST sal is: 3600
9 name is: KING job is: PRESIDENT sal is: 6000
10 name is: TURNER job is: SALESMAN sal is: 1800
11 name is: ADAMS job is: CLERK sal is: 1320
12 name is: JAMES job is: CLERK sal is: 1140
13 name is: FORD job is: ANALYST sal is: 3600
14 name is: MILLER job is: CLERK sal is: 1560
15 name is: VDATAMAG job is: MANAGER sal is: 2760
PL/SQL procedure successfully completed.
（7）将游标数据保存在索引表中
SCOTT@SDEDU> ed11408.sql
declare
cursor emp_cur is select * from emp;
--定义游标取得emp表数据
type emp_index is table of emp%rowtype index by
PLS_INTEGER; --定义索引表类型
v_emp emp_index;
--定义索引表变量
begin
for emp_row in emp_cur loop
v_emp(emp_row.empno):=emp_row;
end loop;
dbms_output.put_line('number is: '||v_emp(7369).empno||' name is: '||v_emp(7369).ename||' job is: '||v_emp(7369).job);
end;
/
SCOTT@SDEDU> @11408
number is: 7369 name is: SMITH job is: CLERK
PL/SQL procedure successfully completed.
推荐阅读
晟数学院DBA成长日记
晟数学院DBA成长日记
晟数学院DBA成长日记
