[PLSQL] 游标学习

最近因为需要准备一下PLSQL方面的面试，用了一天的时间重新看了看PLSQL方面的内容，而游标是PLSQL方面的一个最大的短板，于是从网上找了些文章，自己重新做了一下测试:

先了解一下两个模糊的语法:

1. 参数的类型定义

CREATE OR REPLACE PROCEDURE aaa (account_id IN accounts.id%type)

2. 设置exception的raise类型:

 

1. IF l_balance_remaining<1000 
2. Then 
3.    raise l_balance_remaining 
4. END IF; 
5.  
6. EXCEPTION  
7.    when l_balance_remaining then 
8.         log_error(....); 
9.    RAISE; 
10. END; 

另外了解一下提高PLSQL compiler性能的方式:

要了解一个参数:

PLSQL_OPTIMIZE_LEVEL :  0 不优化， 1 适度优化(消除一些多余的计算和异常处理, 但是不对source code的顺序进行改变) ， 2 高度优化(包含1里面的优化，且多source code顺序进行改变)

alter session set plsql_optimize_level=2;  (default)

alter procedure bigproc complie reuse settings;  (对单个procedure编译，重用当前setting)

alter session set plsql_warnings='enable:all';  (打开警告，默认是disable, all包括severe, information 和performance )

 

进入温习的重点---游标:

隐式游标:
SQL%NOTFOUND, SQL%FOUND, SQL%ROWCOUNT, SQL%ISOPEN

1. create or replace procedure remove_from_emp(empno_in in employees.employee_id%type) 
2. is 
3. begin 
4.   delete employees where employee_id=empno_in; 
5.   dbms_output.put_line('删除了'||sql%rowcount||'条记录了！'); 
6. end;   
7. / 
8. create or replace procedure show_emp_count is 
9.   i_count integer; 
10.   i_numfound pls_integer; 
11. begin 
12.   select count(*) into i_count from employees; 
13.   i_numfound :=sql%rowcount; 
14.   remove_from_emp(1); 
15.   dbms_output.put_line(i_numfound); 
16. end; 
17. /create or replace procedure remove_from_emp(empno_in in employees.employee_id%type) 
18. is 
19. begin 
20.   delete employees where employee_id=empno_in; 
21.   dbms_output.put_line('删除了'||sql%rowcount||'条记录了！'); 
22. end;   
23. / 
24. create or replace procedure show_emp_count is 
25.   i_count integer; 
26.   i_numfound pls_integer; 
27. begin 
28.   select count(*) into i_count from employees; 
29.   i_numfound :=sql%rowcount; 
30.   remove_from_emp(1); 
31.   dbms_output.put_line(i_numfound); 
32. end; 
33. / 

 

---定义显式游标
1) 如果没有任何where条件可用如下定义方式:
cursor emp_cur1 is select * from employee;
2) 如果要指定where条件，cursor后面要跟 (v_name in v_type) ,然后在where条件后跟where ...=v_name:
cursor emp_cur2 (empno_in in number) is select name,hiredate from employee where empno=empno_in;
3) 要返回employee表的值，跟return table_name%rowtype ..
cursor emp_cur3 return employee%rowtype is select * from employee where empno=1;
 

如果想通过游标更新数据,for update是必要的，最好加上nowait
可以在select into, fetch into , returning into子句中使用bulk collect将数据输出到集合:
cursor emp_cur4 (empno_in in number) is select name, salary from employee where empno=empno_in for update;
emp_row1 employee%rowtype;

 

1. declare  
2.   cursor empcur is select * from emp; 
3.   emprow emp%ROWTYPE; 
4. begin 
5.   open empcur; 
6.   fetch empcur into emprow; 
7.   dbms_output.put_line(emprow.empno||' '||emprow.ename); 
8.   close empcur; 
9. end; 
10. / 

7369 SMITH  # 只返回第一行？如果要返回所有的行，需要指定循环。

 

1. declare 
2.   cursor empcur is select * from emp; 
3.   emprow emp%rowtype; 
4. begin 
5.   open empcur; 
6.   loop 
7.     fetch empcur into emprow; 
8.     if empcur%notfound then  
9.       exit; 
10.     end if; 
11.     dbms_output.put_line(emprow.empno||' '||emprow.ename); 
12.     dbms_output.put_line('Fetched '||empcur%rowcount||' rows'); 
13.   end loop; 
14.   close empcur;   
15. end; 
16. / 

还有一种方式 : While

 

1. declare 
2.   cursor empcur is select * from emp; 
3.   emprow emp%rowtype; 
4. begin 
5.   open empcur; 
6.   fetch empcur into emprow; 
7.   while empcur%found loop 
8.     DBMS_OUTPUT.put_line(emprow.empno||'  '||emprow.ename); 
9.     DBMS_OUTPUT.put_line('提取了'||empCur%ROWCOUNT||'行'); 
10.     ----如果不加这段fetch，就会成为死循环，这是为什么呢？ 
11.     fetch empCur into emprow; 
12.   end loop; 
13.   close empcur; 
14. end; 
15. / 

 --- 注，有时候也许你只需要对emp里面的某几个字段进行操作，这是emp%rowtype这种字段全集就不适用了，需要单独定义变量，然后顺序fetch into ,如下例子:

1. declare  
2.       cursor empcur is select EMPNO,ENAME,JOB from emp;  
3.       v_empno   emp.EMPNO%type;  
4.       v_empname emp.ENAME%type; 
5.       v_empjob  emp.JOB%type; 
6.        
7.     begin  
8.       open empcur;  
9.       fetch empcur into v_empno,v_empname,v_empjob;  
10.       while empcur%found loop  
11.         DBMS_OUTPUT.put_line(v_empno||'  '||v_empname);  
12.         DBMS_OUTPUT.put_line('提取了'||empCur%ROWCOUNT||'行');  
13.        ----如果不加这段fetch，就会成为死循环，这是为什么呢？  
14.        fetch empCur into v_empno,v_empname,v_empjob;  
15.      end loop;  
16.      close empcur;  
17.   end;  
18.    /  

 

带参数的游标:

1. declare  
2.    cursor c1(p_deptno varchar2) is select ename,sal from emp where emp.deptno=p_deptno; 
3.    v_deptno varchar2(3); 
4.    v_ename emp.ename%type; 
5.    v_sal emp.sal%type; 
6. begin 
7.    v_deptno:='20'; 
8.    dbms_output.put_line(v_deptno||':'); 
9.    open c1(v_deptno); 
10.    loop 
11.      if c1%notfound then 
12.         exit; 
13.      end if; 
14.      fetch c1 into v_ename, v_sal; 
15.      dbms_output.put_line(v_ename||'---'||v_sal); 
16.    end loop; 
17.    close c1; 
18. end; 
19. /    

 

BULK Collect

plsql的代码是plsql引擎执行，而非传送到sql引擎中，如果代码中有sql豫剧，在sql引擎
中会执行sql，然后把信息返回给plsql引擎：
forall的规则:
# forall必须是对单个dml;
# dml必须引用集合元素，按照forall中index_row变量确定索引, Index_row的scope仅在forall中有效，
  不能再scope之外引用它。
# 不可以申明变量来充当index_row的角色。
# lower and upper bounds针对被forall中的sql使用，必须是一个有效的连续的索引值，
  否则ORA-22160: element at index [3] does not exist.
   10g以后提供了indices of 和values of 来支持非连续性的集合.
# DML中，不能饮用集合的单个field，相反，仅能整体引用集合的行，否则报错；
# 集合的索引变量，在DML与语句中不能是表达式;

INDEX BY Binary_integer, type是表，indexs是连续的；

 

1. drop table test1; 
2. create table test1  
3. as select object_id id, object_name name from dba_objects  
4. where rownum<=5000; 
5. create table test2 as select * from test1 where 1=0; 
6. ---- type是表的内容，说明里面的ｉｎｄｅｘ是连续的 
7. declare  
8.   type dr_type is table of test1%rowtype index by binary_integer; 
9.   dr_table dr_type; 
10. begin 
11.   select id,name BULK COLLECT into dr_table from test1; 
12.   FORALL i in dr_table.first .. dr_table.last 
13.      insert into test2 values dr_table (i); 
14.      --error statement   
15.      --1.insert into test2 values(dr_table(i));报没有足够的值错误，此处外面不可以加括号，当有多个字段的时候，单个字段可以加括号   
16.      --2.insert into test2 values(dr_table(i).id,dr_table(i).name);集合的field不可以在forall中使用，必须是整体使用   
17.      --3.insert into test2 values dr_table(i+1);错误，不可以对索引变量进行运算   
18.      --4.insert into test2 values dr_table(i);dbms_output.put_line(i);不正确，找不到i，因为forall中只能使用单条语句可以引用索引变量   
19.   commit;     
20. end; 
21. / 

 

type is number (或者其他单列)，　index是binary_integer, 但index不是连续的。

# type mylist_type is table of number index by binary_integer;

 

1. drop table mynumtable;  
2. create table mynumtable(id number(10));  
3.  
4. declare 
5.   type mylist_type is table of number index by binary_integer; 
6.   mylist mylist_type; 
7. begin 
8.   mylist(1) :=2; 
9.   mylist(2) :=3; 
10.   mylist(5) :=6; 
11.   forall i in indices of mylist 
12.         insert into mynumtable (id) values (mylist(i));  
13. end; 
14. / 

 

##############################
### FORALL的时间比较:      ###
##############################

 

1. set serveroutput on 
2. drop table ts_forall purge; 
3. create table ts_forall as 
4. select object_id id, object_name name  
5. from dba_objects where 1=0; 
6.  
7. declare  
8.   type ts_id is table of ts_forall.id%type index by binary_integer; 
9.   type ts_nam is table of ts_forall.name%type index by binary_integer; 
10.   p_ts_id ts_id; 
11.   p_ts_nam ts_nam; 
12.   t1 number; 
13.   t2 number; 
14.   t3 number; 
15. begin 
16.   for i in 1 .. 20000 loop 
17.     p_ts_id(i):=i; 
18.     p_ts_nam(i):='Part'||to_char(i); 
19.   end loop; 
20.   t1:=dbms_utility.get_time; 
21.    
22.   for i in 1 .. 20000 loop 
23.     insert into ts_forall values (p_ts_id(i),p_ts_nam(i)); 
24.   end loop; 
25.   t2:=dbms_utility.get_time; 
26.  
27.   forall i in 1 .. 20000 
28.     insert into ts_forall values(p_ts_id(i),p_ts_nam(i)); 
29.   t3:=dbms_utility.get_time; 
30.    
31.   dbms_output.put_line('Execution Time (secs)'); 
32.   dbms_output.put_line('-------------------'); 
33.   dbms_output.put_line('For loop:'||TO_CHAR(t2-t1)); 
34.   dbms_output.put_line('For All:'||TO_CHAR(t3-t2)); 
35. end; 
36. / 
37.  
38. Execution Time (secs) 
39. ------------------- 
40. For loop:423 
41. For All:3 

 

##############################
### FORALL回滚的特点:      ###
##############################

 

1. drop table emp2 purge; 
2. CREATE TABLE emp2 (deptno NUMBER(2), job VARCHAR2(15)); 
3. INSERT INTO emp2 VALUES(10, 'Clerk'); 
4. INSERT INTO emp2 VALUES(10, 'Clerk'); 
5. -- 10-char job title 
6. INSERT INTO emp2 VALUES(20, 'Bookkeeper');  
7. INSERT INTO emp2 VALUES(30, 'Analyst'); 
8. INSERT INTO emp2 VALUES(30, 'Analyst'); 
9. commit; 
10.  
11. declare 
12.   type numlist is table of number; 
13.   depts numlist := numlist(10,20,30); 
14. BEGIN 
15.   FORALL i IN depts.first .. depts.last 
16.     update emp2 set job=job||'temp123' where deptno=depts(i); 
17.   --EXCEPTION 
18.   --  WHEN OTHERS THEN 
19.   --COMMIT;      
20. end; 
21. /    
22. ---get error: 
23. ORA-12899: value too large for column "KL"."EMP2"."JOB" 
24. kl@k01> select * from emp2; 
25.     DEPTNO JOB 
26. ---------- --------------- 
27.         10 Clerk 
28.         10 Clerk 
29.         20 Bookkeeper 
30.         30 Analyst 
31.         30 Analyst 
32.          
33. declare 
34.   type numlist is table of number; 
35.   depts numlist := numlist(10,20,30); 
36. BEGIN 
37.   FORALL i IN depts.first .. depts.last 
38.     update emp2 set job=job||'temp123' where deptno=depts(i); 
39.   EXCEPTION 
40.     WHEN OTHERS THEN 
41.   COMMIT;    
42. end; 
43. / 

--- 此时如果加了EXCEPTION，前面的update会正常进行，但后面出现错误的保持原状，
这与上面的结果不同，对于forall如果不加exception,将会对所有forall纪录回滚，
这也说明forall实际上也是对dml出线错误时，并不会对所有未commit的事务进行回滚。       
kl@k01> select * from emp2;

    DEPTNO JOB
---------- ---------------
        10 Clerktemp123  --commit了
        10 Clerktemp123  --commit了
        20 Bookkeeper
        30 Analyst
        30 Analyst

 

###################################
####  BULK_ROWCOUNT         #######
###################################
对于SQL引擎的隐式游标而言，该游标有如下标量属性(scalar attribute)
%FOUND, %ISOPEN, %NOTFOUND, %ROWCOUNT
对于FORALL，除了上边的属性，还有个复合属性，%BULK_ROWCOUNT,
类似索引的特点，它的第i个元素存储SQL语句(insert, update, delete)的
第i个执行的处理行数，如果第i个执行未影响到行，%buld_rowcount(i)返回0。
FORALL与%bulk_rowcount使用相同的下标。

--它的第i个元素存贮SQL语句(INSERT, UPDATE或DELETE)第i个执行的处理行数
--如%BULK_ROWCOUNT(i)为0时，%FOUND和%NOTFOUND分别是FALSE和TRUE.
--第三个数如果是80，就是0，如果是30，就是1。

1. DECLARE 
2.   TYPE Numlist is table of number; 
3.   depts numlist :=numlist(10,20,80); 
4. begin 
5.   FORALL j IN depts.first .. depts.last 
6.     UPDATE emp2 set job=job||'t' where deptno=depts(j); 
7.   if sql%bulk_rowcount(3)=0 then  
8.      dbms_output.put_line('No Action...'); 
9.   else  
10.      dbms_output.put_line('Update sucessfully...'); 
11.   end if; 
12. end; 
13. / 

 

 

###################################
####  BULK COLLECT          #######
###################################

用于告知SQL引擎批榜定输出集合，该关键字用于SELECT INTO, FETCH INTO和RETURNING INTO
..BULK COLLECT INTO collection_name ...

 

 

1. 1) 示例: 
2. declare  
3.   type numtab is table of number; 
4.   type nametab is table of emp.ename%type; 
5.   pnum numtab; 
6.   pname nametab; 
7. begin 
8.   select empno, ename bulk collect into pnum,pname from emp; 
9.     ......... 
10. end; 
11. /    
12. 2) 示例: 
13. CREATE TYPE Coords AS OBJECT(X number, y number); 
14. / 
15. create table grid (num number, loc coords); 
16. insert into grid values (10,coords(1,2)); 
17. insert into grid values (20,coords(3,4)); 
18. DECLARE 
19.   TYPE CoordsTab IS TABLE OF Coords; 
20.   pairs CoordsTab; 
21. BEGIN 
22.   SELECT loc BULK COLLECT INTO pairs FROM grid; 
23. END;   
24. / 
25. 3) 示例: 
26. DECLARE 
27.   TYPE SalList IS TABLE OF emp.sal%TYPE; 
28.   sals SalList; 
29. BEGIN 
30.   select sal bulk collect into sals from emp where rownum<=100; 
31. END; 
32. / 
33. 4) 示例: 
34. DECLARE 
35.   TYPE NameList IS TABLE OF emp.ename%TYPE; 
36.   TYPE SalList IS TABLE OF emp.sal%TYPE; 
37.   CURSOR c1 IS SELECT ename, sal from emp WHERE sal>1000; 
38.   names NameList; 
39.   Sals SalList; 
40. BEGIN 
41.   OPEN c1; 
42.   FETCH c1 BULK COLLECT INTO names, sals; 
43. END; 
44. / 
45. 5) 示例: 
46. DECLARE 
47.   Type DeptRecTab is TABLE of dept%rowtype; 
48.   dept_recs DeptRecTab; 
49.   cursor c1 IS SELECT deptno, dname, loc FROM dept WHERE deptno > 10; 
50. BEGIN 
51.   open c1; 
52.   FETCH c1 BULK COLLECT INTO dept_recs; 
53.   close c1; 
54. END; 
55. / 

 

###################################
####  RETURNING INTO        #######
###################################

 

1. 注意复合目标不能用于RETURN INTO子句中。 
2. DECLARE 
3.  TYPE NumList IS TABLE OF emp.empno%TYPE; 
4.  enums NumList; 
5. BEGIN 
6.   DELETE FROM emp WHERE deptno = 20 
7.   RETURN empno BULK COLLECT INTO enums; 
8. END; 
9. / 

 

###############################################
####  FORALL & BULK COLLECT           #########
###############################################

在什么情况下同时使用forall与bulk collect?
一个集合有三个元素，每个元素导致5行被删除，dml完成时，集合enums有15个元素:
FORALL j IN depts.FIRST .. depts.LAST
  DELETE FROM emp WHERE empno=depts(j)
  RETURNING empno BULK COLLECT INTO enums;
##注意，不能在FORALL语句中使用select ... BULK COLLECT语句；

转载于:https://blog.51cto.com/mengya520/341224