pl/sql篇之游標的使用

游標是什麼

在PL/SQL块中执行SELECT、INSERT、DELETE和UPDATE语句时，ORACLE会在内存中为其分配上下文区（Context Area），即缓冲区。游标是指向该区的一个指针，或是命名一个工作区（Work Area），或是一种结构化数据类型。它为应用等量齐观提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法，是设计嵌入式SQL语句的应用程序的常用编程方式。

游標的類型

pl/sql中，游標分為顯示游標和隱式游標，顯示游標由用戶定義，隱式游標由系統分配管理，不同類型的sql語句會觸發不同類型的游標。

游標語法

游标属性

Cursor_name%FOUND 布尔型属性，当最近一次提取游标操作FETCH成功则为 TRUE,否则为FALSE；

Cursor_name%NOTFOUND 布尔型属性，与%FOUND相反；

Cursor_name%ISOPEN 布尔型属性，当游标已打开时返回 TRUE；

Cursor_name%ROWCOUNT 数字型属性，返回已从游标中读取的记录数。

游標的簡單的例子

--定義簡單的游標
declare
--定義游標
cursor mycursor
--指定sql
is select username from user_lin;
myname user_lin.username%TYPE;
begin 
--打開游標
open mycursor;
 --將結果存入變量
fetch mycursor into myname;  
--循環遍歷，獲取數據
while mycursor%found loop
   dbms_output.put_line('---'||myname);
fetch mycursor into myname;
end loop;
close mycursor;
end;

for循環讀取游標例子

--for 循環語句練習
declare
type emp_record is record(
v_empno number,
v_ename varchar2(20)

);
--定義一個多行的數據集合
type title_collection is table of emp_record;
lv_title_collection title_collection;
cursor c is
select empno,ename 
from emp_lin;
begin
   open c;
   loop 
--循環取出數據
     fetch c bulk collect into lv_title_collection limit 20;
     exit when lv_title_collection.count = 0;
     for i in 1..lv_title_collection.count loop
       dbms_output.put_line(lv_title_collection(i).v_empno);
    end loop;
   end loop;
   close c;
end;

使用游標批量修改數據

--- 綜合性練習
declare
 v_sal emp_lin.sal%type;
 v_empno  emp_lin.empno%type;
 v_num   number;
cursor c is select empno, sal from emp_lin;
begin
open c;
loop
fetch c into v_empno,v_sal;
exit  when c%notfound;
--少於2300則補齊
if v_sal<2300 then
    v_num := 2300-v_sal;
    update emp_lin set sal=sal+v_num where empno=v_empno;
end if;
end loop;
close c;
end;
      

使用游標傳遞參數

declare
 v_sal emp_lin.sal%type;
 v_empno  emp_lin.empno%type;
 v_num   number;
--此處傳遞參數
cursor c(v_job varchar ) is select empno, sal from emp_lin where job = v_job;
begin
open c('Salesman');
loop
fetch c into v_empno,v_sal;
exit  when c%notfound;
if v_sal<2300 then
    v_num := 2300-v_sal;
    update emp_lin set sal=sal+v_num where empno=v_empno;
end if;
end loop;
close c;
end;

使用游標並返回結果

--攜帶參數並帶返回值
declare
 v_sal emp_lin.sal%type;
 v_empno  emp_lin.empno%type;
 v_num   number;
type   emp_record_type is record(
      v_sal emp_lin.sal%type,
     v_empno  emp_lin.empno%type
);
v_record emp_record_type;
cursor c(v_job varchar ) 
return v_record
is select empno, sal from emp_lin where job = v_job;
begin
open c('Salesman');
loop
fetch c into  v_record;
if c%found then
      dbms_output.put_line(v_record.v_sal);

end if;
end loop;
close c;
end;