Oracle Cursor用法總結

標籤：into   弱類型   其他   exit   exp   not   開關   tput   tab   

cursor分為三種，一是直接聲明為cursor變數，二是首先宣告類型再聲明變數，三是聲明為sys_refcursor。

（1）直接聲明

declare

 cursor emp_cur  is select *  from emp;

 emp_record emp%rowtype;

begin 

 open emp_cur;

 loop 

  fetch emp_cur  into emp_record;

  exit when  emp_cur%notfound;

  dbms_output.put_line(‘name is:‘ || emp_record.ename ||‘ and sal is:‘ || emp_record.sal);

 end loop;

 close emp_cur;

end;

/

(2)ref cursor:分為強型別（有return子句的）和弱類型，強型別在使用時，其傳回型別必須和return中的類型一致，否則報錯，而弱類型可以隨意開啟任何類型。

例如：

強型別

declare

 type emp_cur_type  is ref cursor return emp%rowtype;

 emp_cur emp_cur_type;

 emp_record emp%rowtype;

begin

 open emp_cur  for select *  from  emp;

 loop

  fetch emp_cur  into emp_record;

  exit when emp_cur%notfound;

  dbms_output.put_line(‘name is:‘ ||  emp_record.ename || ‘ and sal is:‘ || emp_record.sal);

 end loop;

 close emp_cur;

 --open emp_cur for select * from dept; 錯誤的，類型不一致。

 --close emp_cur;

end;

/

弱類型：

declare

 type emp_cur_type is ref cursor;

 emp_cur emp_cur_type;

 emp_record emp%rowtype;

 dept_record dept%rowtype;

begin

 open emp_cur for select *  from emp;

 loop

  fetch emp_cur into emp_record;

  exit when emp_cur%notfound;

  dbms_output.put_line(‘name is:‘ || emp_record.ename || ‘ and sal is:‘ || emp_record.sal);

 end loop;

 close emp_cur;

 

 open emp_cur  for select *  from dept; --可再次開啟，不同類型的

 loop

  fetch emp_cur  into dept_record;

  exit when emp_cur%notfound;

  dbms_output.put_line(‘dname is:‘ || dept_record.dname);

 end loop;

 close emp_cur;

end;

/

（3）sys_refcursor:可多次開啟，直接聲明此類型的變數，不用先定義類型再聲明變數。

declare

 emp_cur sys_refcursor;

 emp_record emp%rowtype;

 dept_record dept%rowtype;

begin

 open emp_cur for select *  from emp;

 loop

  fetch emp_cur  into emp_record;

  exit when emp_cur%notfound;

  dbms_output.put_line(‘name is:‘ || emp_record.ename || ‘ and sal is:‘ || emp_record.sal);

 end loop;

 close emp_cur;

 

 open emp_cur  for select *  from dept; --可再次開啟，不同類型的

 loop

  fetch emp_cur  into dept_record;

  exit when emp_cur%notfound;

  dbms_output.put_line(‘dname is:‘ || dept_record.dname);

 end loop;

 close emp_cur;

end;

/

其他總結：

1、遊標可以用for迴圈，但只限於cursor cur_var is ……這種類型，用在其他的裡面都是錯誤的；for本身就包含了開啟、關閉遊標，此時再顯示開啟關閉都是錯誤的。

declare

cursor emp_cur is select *  from emp;

begin

--open emp_cur; 是錯誤的，因為for本身就包含了開啟、關閉

for emp_record in emp_cur

loop

   dbms_output.put_line(‘name is:‘ ||  emp_record.ename || ‘ and sal is:‘ || emp_record.sal);

end loop;

--close emp_cur; 是錯誤的，for本身包含了關閉。

end;

 

--是不是表示：ref cursor變數不支援for開啟並迴圈？

declare

type emp_cur_type  is ref cursor return emp%rowtype;

emp_cur emp_cur_type;

begin

open emp_cur  for select *  from emp;--怎麼都是錯，for已經開啟了。

for emp_record  in emp_cur -- 不管前面有沒有開啟語句，for都不承認這種類型

loop

   dbms_output.put_line(‘name is:‘ ||  emp_record.ename || ‘ and sal is:‘ || emp_record.sal);

end loop;

end;

2、遊標可以帶參數

DECLARE

  CURSOR c1 (job VARCHAR2, max_wage NUMBER) IS

    SELECT * FROM employees  WHERE job_id = job  AND salary > max_wage;

BEGIN

  FOR person  IN  c1(‘CLERK‘, 3000)

  LOOP

     DBMS_OUTPUT.PUT_LINE(‘Name = ‘ || person.last_name || ‘, salary = ‘ ||

                         person.salary || ‘, Job Id = ‘ || person.job_id );

  END LOOP;

END;

3、bulk collect批量賦值

declare

 type emp_cur_type  is ref cursor;

 emp_cur emp_cur_type;

 type name_list is table of emp.ename%type;

 type sal_list  is table of emp.sal%type;

 names name_list;

 sals sal_list;

begin

 open emp_cur for select ename,sal from emp;

 fetch emp_cur bulk collect into names,sals;

 close emp_cur;

 

 for i  in names.first .. names.last

 loop

  dbms_output.put_line(‘name is:‘||names(i)||‘ and sal is:‘||sals(i));

 end loop;

end;

/

4、cursor變數的位置

CREATE PACKAGE emp_data AS

  TYPE EmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE;

-- emp_cv EmpCurTyp; -- not allowed

  PROCEDURE open_emp_cv;

END  emp_data;

/

CREATE PACKAGE BODY emp_data  AS

-- emp_cv EmpCurTyp; -- not allowed

PROCEDURE open_emp_cv  IS

  emp_cv EmpCurTyp; -- this is legal

  BEGIN

    OPEN emp_cv  FOR SELECT *  FROM  employees;

  END  open_emp_cv;

END  emp_data;

/

5、嵌套cursor

開啟父cursor時，子cursor隱含開啟；當

文法格式:cursor(subquery)

A   nested  cursor  is  implicitly  opened when  the  containing  row  is  fetched from  the  parent  cursor.

The  nested  cursor  is  closed  only  when:

The  nested  cursor  is  explicitly  closed by  the  user

The  parent  cursor  is  reexecuted

The  parent  cursor  is  closed

The  parent  cursor  is  canceled

樣本；
declare

 type emp_cur_type  is ref cursor ;

 type dept_cur_type is ref cursor ;

 v_ename emp.ename%type;

 v_dname dept.dname%type;

 emp_cur emp_cur_type;

 dept_cur dept_cur_type;

begin

 open dept_cur  for

 select d.dname,

  cursor(select  e.ename  from  emp  e  where e.deptno=d.deptno )emps

 from dept d;

 loop

  fetch dept_cur into v_dname,emp_cur;

  exit when dept_cur%notfound;

  dbms_output.put_line(‘dname is : ‘||v_dname);

  loop

   fetch emp_cur into  v_ename;

   exit when emp_cur%notfound;

   dbms_output.put_line(‘--ename is : ‘||v_ename);

  end  loop;

 end  loop;

 close  dept_cur;

end;

Oracle Cursor用法總結