PL/SQL 04 遊標 cursor

--遊標

declare
  cursor 遊標名字
  is
  查詢語句;
begin
  其他語句;
end;

--遊標的屬性
%FOUND
%NOTFOUND
%ISOPEN
%ROWCOUNT（當前遊標的指標位移量）

--FETCH的兩種形式
FETCH cursor_name INTO var1, var2, …;
FETCH cursor_name INTO record_var;

--遊標的FETCH迴圈
LOOP
  FETCH cursor INTO…
  EXIT WHEN cursor%NOTFOUND;
END LOOP

WHILE cursor%FOUND LOOP
  FETCH cursor INTO…
END LOOP

FOR var IN cursor LOOP
  FETCH cursor INTO…
END LOOP

--如果使用了PL/SQL變數在select_statement中, 變數的宣告必須放在遊標前面
  v_major students.major%TYPE;
DELCARE
  CURSOR c_student IS
  SELECT first_name, last_name
  FROM students
  WHERE major = v_major;

--CURSOR可以帶引數
DECLARE
  CURSOR c_student(p_major students.major%TYPE)       --注意返回型別
  SELECT *
  FROM students
  WHERE major = p_major;
  BEGIN
  OPEN c_student( 101 );
  …

 

--遊標舉例
declare
  cursor cur_teaname
  is
  select teaname from t_teacher where teatitle='教授';
  teanme t_teacher.teaname%type;
  result varchar2(100);
begin
  open cur_teaname;
  loop
    fetch cur_teaname into teaname;
    exit when cur_teaname%notfound;
    result:=result||teaname||' ';
  end loop;
  dbms_output.put_line(result);
  close cur_teaname;
end;

帶引數的遊標
IKKI@ test10g> edit
Wrote file ././././afiedt.buf

  1  declare
  2    cursor cust_cursor(p_cust_id int,p_last_name varchar2)
  3      is
  4        select cust_id,first_name,last_name,credit_limit
  5        from customer
  6        where cust_id=p_cust_id
  7        and last_name=p_last_name;
  8    customer_record customer%rowtype;
  9  begin
 10    open cust_cursor('&id','&lname');
 11    loop
 12      fetch cust_cursor into customer_record;
 13      exit when cust_cursor%notfound;
 14      dbms_output.put_line(customer_record.cust_id||':'||customer_record.last_name);
 15    end loop;
 16    close cust_cursor;
 17* end;
IKKI@ test10g> /
Enter value for id: 1
Enter value for lname: smith
old  10:   open cust_cursor('&id','&lname');
new  10:   open cust_cursor('1','smith');
1:smith

PL/SQL procedure successfully completed.

使用遊標更新資料
IKKI@ test10g> edit
Wrote file ././././afiedt.buf

  1  declare
  2    cursor test_cursor is
  3      select ddh,ydrq,jfrq from test for update;
  4    test_record test%rowtype;
  5  begin
  6    open test_cursor;
  7    loop
  8      fetch test_cursor into test_record;
  9      exit when test_cursor%notfound;
 10      dbms_output.put_line('ddh:'||test_record.ddh||',ydrq:'||test_record.ydrq||',jfrq:'||test_record.jfrq);
 11    if test_record.jfrq-test_record.ydrq>15 then
 12      update test set jfrq=ydrq+15 where current of test_cursor;
 13    end if;
 14    end loop;
 15    close test_cursor;
 16* end;
IKKI@ test10g> /
ddh:601,ydrq:01-MAY-90,jfrq:30-MAY-90
ddh:600,ydrq:01-MAY-90,jfrq:29-MAY-90

PL/SQL procedure successfully completed.

IKKI@ test10g> select * from test;

       DDH YDRQ         JFRQ
---------- ------------ ------------
       601 01-MAY-90    16-MAY-90
       600 01-MAY-90    16-MAY-90

--用for迴圈簡化的遊標

for 記錄變數名 in 遊標名字 loop
  程式碼;
end loop;

create or replace function fun_get_teaname(title varchar2)
return varchar2
as
  cursor cur_teaname
  is
  select teaname from t_teacher where teatitle=title;
  result varchar2(100);
begin
  for rec in cur_teaname loop
    result:=result||rec.teaname||' ';
  end loop;
  return(result);
end;

--儲存過程和遊標配合使用

create or replace procedure  test1(j emp.job%type)
as
  cursor test
  is select empno,ename from emp where job=j;
  eno emp.empno%type;
  ena emp.ename%type;
begin
  open test;
  loop
    fetch test into eno,ena;
    exit when test%notfound;
     dbms_output.put_line(eno||' '||ena);
  end loop;
  close test;
end;

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27633655/viewspace-1080389/，如需轉載，請註明出處，否則將追究法律責任。