笔记081121 游标(cursor)和存储过程(procedure)

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
创建一个游标

SQL> edit;
已写入 file afiedt.buf

  1  DECLARE
  2  CURSOR mycur IS
  3  SELECT * FROM STUDENT;
  4  myrecode student%ROWTYPE;
  5  BEGIN
  6  OPEN mycur;
  7  FETCH mycur INTO myrecode;
  8  WHILE mycur%FOUND LOOP
  9  DBMS_OUTPUT.PUT_LINE('the data is : '||myrecode.sno||myrecode.sname);
 10  FETCH mycur INTO myrecode;
 11  END LOOP;
 12  CLOSE mycur;
 13* END;
SQL> /

PL/SQL 过程已成功完成。
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
SET SERVEROUTPUT ON SIZE 10000

SQL> SET SERVEROUTPUT ON SIZE 10000
SQL> /
the data is : 01062401揍晔                                                     
the data is : 01062402黄费用                                                   
the data is : 01062403卡烂                                                     
the data is : 01062404住没                                                     
the data is : 01062405没猪                                                     
the data is : 01062406张故乡                                                   
the data is : 01062407刘晔                                                     
the data is : 01062408张晔                                                     
the data is : 01062101揍晔                                                     
the data is : 01062102黄费用                                                   
the data is : 01062103卡烂                                                     
the data is : 01062104住没                                                     
the data is : 01062105没猪                                                     
the data is : 01062106张故乡                                                   
the data is : 01062107刘晔                                                     
the data is : 01062108张晔                                                     

PL/SQL 过程已成功完成。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~```
一个含有参数的游标

SQL> EDIT
已写入 file afiedt.buf

  1  DECLARE
  2  CURSOR mycur(ID varchar2) IS
  3  SELECT SNAME FROM STUDENT WHERE SNO = ID;
  4  myrecode STUDENT.SNAME%TYPE;
  5  BEGIN
  6  OPEN mycur('01062104');
  7  LOOP
  8  FETCH mycur INTO myrecode;
  9  EXIT WHEN mycur%NOTFOUND;
 10  DBMS_OUTPUT.PUT_LINE('SNAME IS  '||myrecode);
 11  END LOOP;
 12  CLOSE mycur;
 13* END;
SQL> /
SNAME IS  住没