--一.基础
--(一).SQL游标分为两种
--1.隐含游标专门处理DML,SELECT INTO操作。
--2.显示游标专门处理多行的SELECT语句。
--(二).隐含游标
--1.当使用增删改语句获取结果时,必须要使用SQL游标属性。
--2.SQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNT,SQL%ISOPEN
--SQL%ISOPEN用于确定SQL游标是否已打开，ORACLE会隐含打开和关闭游戏，开发时不需要使用，对于开发人员来说永远是FALSE。
--SQL%FOUND用来确定SQL执行是否成功，是否成功依据作用行来判断。
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 v_deptno emp.deptno%type:=&no;
3 BEGIN
4 UPDATE emp SET sal=sal*1.1
5 WHERE deptno=v_deptno;
6 IF SQL%FOUND THEN
7 dbms_output.put_line('语句执行成功');
8 ELSE
9 dbms_output.put_line('该部门不存在雇员');
10 END IF;
11 END;
12 /
输入 no 的值: 30
原值 2: v_deptno emp.deptno%type:=&no;
新值 2: v_deptno emp.deptno%type:=30;
语句执行成功
PL/SQL 过程已成功完成。
--SQL%NOTFOUND与SQL%FOUND相反。
SQL> DECLARE
2 v_deptno emp.deptno%type:=&no;
3 BEGIN
4 UPDATE emp SET sal=sal*1.1
5 WHERE deptno=v_deptno;
6 IF SQL%NOTFOUND THEN
7 dbms_output.put_line('语句执行成功');
8 ELSE
9 dbms_output.put_line('该部门不存在雇员');
10 END IF;
11 END;
12 /
输入 no 的值: 30
原值 2: v_deptno emp.deptno%type:=&no;
新值 2: v_deptno emp.deptno%type:=30;
该部门不存在雇员
PL/SQL 过程已成功完成。
--SQL%ROWCOUNT用来返回SQL所作用的行数。
SQL> DECLARE
2 v_deptno emp.deptno%type:=&no;
3 BEGIN
4 UPDATE emp SET sal=sal*1.1
5 WHERE deptno=v_deptno;
6 dbms_output.put_line('修改了'||SQL%ROWCOUNT||'行');
7 END;
8 /
输入 no 的值: 30
原值 2: v_deptno emp.deptno%type:=&no;
新值 2: v_deptno emp.deptno%type:=30;
修改了6行
PL/SQL 过程已成功完成。
--(三).SAVEPOINT,ROLLBACK,COMMIT
SQL> DECLARE
2 v_sal emp.sal%TYPE:=&salary;
3 v_ename emp.ename%TYPE:='&name';
4 BEGIN
5 UPDATE emp SET sal=v_sal WHERE ename=v_ename;
6 COMMIT;
7 EXCEPTION
8 WHEN OTHERS THEN
9 ROLLBACK;
10 END;
11 /
输入 salary 的值: 1200
原值 2: v_sal emp.sal%TYPE:=&salary;
新值 2: v_sal emp.sal%TYPE:=1200;
输入 name 的值: SCOTT
原值 3: v_ename emp.ename%TYPE:='&name';
新值 3: v_ename emp.ename%TYPE:='SCOTT';
PL/SQL 过程已成功完成。
--二.使用游标
--(一).使用显示游标
--a.定义游标;b.打开游标;c.提取数据;d.关闭游标
--DEMO1:使用 FETCH..INTO
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 CURSOR emp_cursor IS
3 SELECT ename,sal FROM emp WHERE deptno=10;
4 v_ename emp.ename%TYPE;
5 v_sal emp.sal%TYPE;
6 BEGIN
7 OPEN emp_cursor;
8 LOOP
9 FETCH emp_cursor INTO v_ename,v_sal;
10 EXIT WHEN emp_cursor%NOTFOUND;
11 dbms_output.put_line(v_ename||':'||v_sal);
12 END LOOP;
13 CLOSE emp_cursor;
14 END;
15 /
CLARK:2450
KING:5000
MILLER:1300
PL/SQL 过程已成功完成。
--DEMO2:使用 FETCH..BULK COLLECT INTO
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 CURSOR emp_cursor IS
3 SELECT ename FROM emp WHERE deptno=10;
4 TYPE ename_table_type IS TABLE OF emp.ename%TYPE;
5 ename_table ename_table_type;
6 BEGIN
7 OPEN emp_cursor;
8 FETCH emp_cursor BULK COLLECT INTO ename_table;
9 FOR i IN 1..ename_table.COUNT LOOP
10 dbms_output.put_line(ename_table(i));
11 END LOOP;
12 CLOSE emp_cursor;
13 END;
14 /
CLARK
KING
MILLER
PL/SQL 过程已成功完成。
--DEMO3:使用 FETCH..BULK COLLECT INTO..LIMIT
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 CURSOR emp_cursor IS
3 SELECT ename FROM emp;
4 TYPE ename_varray_table IS VARRAY(5) OF emp.ename%TYPE;
5 ename_varray ename_varray_table;
6 rows INT:=5;
7 BEGIN
8 OPEN emp_cursor;
9 LOOP
10 FETCH emp_cursor BULK COLLECT INTO ename_varray
11 LIMIT rows;
12 dbms_output.put('雇员名：');
13 FOR i IN 1..ename_varray.COUNT LOOP
14 dbms_output.put(ename_varray(i)||',');
15 END LOOP;
16 dbms_output.new_line;
17 EXIT WHEN emp_cursor%NOTFOUND;
18 END LOOP;
19 CLOSE emp_cursor;
20 END;
21 /
雇员名：SMITH,ALLEN,WARD,JONES,MARTIN,
雇员名：BLAKE,CLARK,SCOTT,KING,TURNER,
雇员名：ADAMS,JAMES,FORD,MILLER,
PL/SQL 过程已成功完成。
--DEMO4:使用基于游标类型的PL/SQL记录简化
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 CURSOR emp_cursor IS SELECT ename,sal FROM emp;
3 emp_record emp_cursor%ROWTYPE;
4 BEGIN
5 OPEN emp_cursor;
6 LOOP
7 FETCH emp_cursor INTO emp_record;
8 EXIT WHEN emp_cursor%NOTFOUND;
9 dbms_output.put(emp_record.ename||','||emp_record.sal);
10 END LOOP;
11 dbms_output.new_line;
12 CLOSE emp_cursor;
13 END;
14 /
SMITH,800ALLEN,2342.56WARD,1830.13JONES,2975MARTIN,1830.13BLAKE,4172.69CLARK,245
0SCOTT,1200KING,5000TURNER,2196.15ADAMS,1100JAMES,1390.9FORD,3000MILLER,1300
PL/SQL 过程已成功完成。
--(二).使用参数游标
SQL> --语法：CURSOR cursor_name(parameter_name datatype) IS select_statment;
SQL> --DEMO
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 CURSOR emp_cursor(no NUMBER) IS
3 SELECT ename FROM emp WHERE deptno=no;
4 v_ename emp.ename%TYPE;
5 BEGIN
6 OPEN emp_cursor(10);
7 LOOP
8 FETCH emp_cursor INTO v_ename;
9 EXIT WHEN emp_cursor%NOTFOUND;
10 dbms_output.put_line(v_ename);
11 END LOOP;
12 CLOSE emp_cursor;
13 END;
14 /
CLARK
KING
MILLER
PL/SQL 过程已成功完成。
--(三).使用游标更新或删除数据
SQL> --语法：CURSOR cursor_name(parameter_name datatype)
SQL> --IS select_statment
SQL> --FOR UPDATE [OF column_reference] [NOWAIT];
SQL> --语法：UPDATE table_name SET column=.. WHERE CURRENT OF cursor_name;
SQL> --语法：DELETE table_name WHERE CURRENT OF curros_name;
SQL> --DEMO
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 CURSOR emp_cursor(no NUMBER) IS
3 SELECT ename FROM emp WHERE deptno=no
4 FOR UPDATE OF ename
5 NOWAIT;
6 v_ename emp.ename%TYPE;
7 BEGIN
8 OPEN emp_cursor(10);
9 LOOP
10 FETCH emp_cursor INTO v_ename;
11 EXIT WHEN emp_cursor%NOTFOUND;
12 dbms_output.put_line('原始：'||v_ename);
13 UPDATE emp SET ename=v_ename||'1' WHERE CURRENT OF emp_cursor;
14 dbms_output.put_line('受影响的行数：'||SQL%ROWCOUNT);
15 END LOOP;
16 CLOSE emp_cursor;
17 --COMMIT;
18 END;
19 /
原始：CLARK11
受影响的行数：1
原始：KING11
受影响的行数：1
原始：MILLER11
受影响的行数：1
PL/SQL 过程已成功完成。
--(四).使用FOR循环
SQL> --语法：FOR record_name IN cursor_name LOOP
SQL> -- END LOOP;
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 CURSOR emp_cursor(no NUMBER) IS
3 SELECT ename FROM emp WHERE deptno=no
4 FOR UPDATE OF ename
5 NOWAIT;
6
7 BEGIN
8 FOR emp_record IN emp_cursor(10) LOOP
9 dbms_output.put_line('原始：'||emp_record.ename);
10 UPDATE emp SET ename=emp_record.ename||'1' WHERE CURRENT OF emp_c
11 dbms_output.put_line('受影响的行数：'||SQL%ROWCOUNT);
12 END LOOP;
13 --COMMIT;
14 END;
15 /
原始：CLARK111
受影响的行数：1
原始：KING111
受影响的行数：1
原始：MILLER111
受影响的行数：1
PL/SQL 过程已成功完成。
--(五).使用游标变量
--定义REF CURSOR类型和游标变量
--语法：TYPE ref_cursor_name IS REF CURSOR [RETURN return_type];
-- cursor_variable ref_cursor_name;
--非RETURN返回的是记录类型
--打开游标
--语法：OPEN cursor_variable FOR select_statement;
--提取游标数据
--语法一：FETCH cursor_variable INTO variable1,variable2,...;
--语法二：FETCH cursor_variable BULK COLLECT INTO collect1,collet2,...[LIMIT rows];
--关闭游标变量
--语法：CLOSE cursor_variable;
SQL> DECLARE
2 TYPE emp_record_type IS RECORD
3 (
4 name VARCHAR2(10),
5 salary NUMBER(6,2)
6 );
7 TYPE emp_cursor_type IS REF CURSOR
8 RETURN emp_record_type;
9 emp_cursor emp_cursor_type;
10 emp_record emp_record_type;
11 BEGIN
12 OPEN emp_cursor
13 FOR SELECT ename,sal FROM emp WHERE deptno=20;
14 LOOP
15 FETCH emp_cursor INTO emp_record;
16 EXIT WHEN emp_cursor%NOTFOUND;
17 dbms_output.put_line('第'||emp_cursor%ROWCOUNT||'个雇员：'||emp_record.name);
18 END LOOP;
19 CLOSE emp_cursor;
20 END;
21 /
第1个雇员：SMITH
第2个雇员：JONES
第3个雇员：SCOTT
第4个雇员：ADAMS
第5个雇员：FORD
PL/SQL 过程已成功完成。
--(六).使用游标表达式
--语法：CURSOR()
SQL> DECLARE
2 TYPE emp_cursor_type IS REF CURSOR;
3 CURSOR dept_cursor(no NUMBER) IS
4 (
5 SELECT a.dname,CURSOR(SELECT ename,sal
6 FROM emp WHERE deptno=a.deptno)
7 FROM dept a WHERE a.deptno=no
8 );
9 emp_cursor emp_cursor_type;
10 v_ename emp.ename%TYPE;
11 v_dname dept.dname%TYPE;
12 v_sal emp.sal%TYPE;
13 BEGIN
14 OPEN dept_cursor(&no);
15 LOOP
16 FETCH dept_cursor INTO v_dname,emp_cursor;
17 EXIT WHEN dept_cursor%NOTFOUND;
18 dbms_output.put_line('部门名：'||v_dname);
19 LOOP
20 FETCH emp_cursor INTO v_ename,v_sal;
21 EXIT WHEN emp_cursor%NOTFOUND;
22 dbms_output.put_line('雇员名：'||v_ename||',工资：'||v_sal);
23 END LOOP;
24 CLOSE emp_cursor;
25 END LOOP;
26 CLOSE dept_cursor;
27 END;
28 /
输入 no 的值: 10
原值 14: OPEN dept_cursor(&no);
新值 14: OPEN dept_cursor(10);
部门名：ACCOUNTING
雇员名：CLARK1111,工资：2450
雇员名：KING1111,工资：5000
雇员名：MILLER1111,工资：1300
PL/SQL 过程已成功完成。
浙公网安备 33010602011771号