PL/sql_syntax_type_cursor简单样例

--------------------------
--eg:
CREATE TABLE xjytab(col1 number,col2 varchar2(20),col3time date,d date);
---
....--insert into table some information;
---
create or replace procedure xjyp is  
  cur sys_refcursor;
  mstr xjytab%rowtype;
begin 
  open cur for 
  select * from xjytab ;
  loop
     fetch cur into mstr;
     dbms_output.put_line(mstr.col1||'  '||mstr.col2||' date1: '||to_char(mstr.col3time,'yyyymmdd')
     ||' date2: '||to_char(mstr.d,'yyyymmdd'));--数据不是一行报错  
     EXIT When cur%NOTFOUND;
     end loop;
  close cur;
 end;
/
--------------------
SQL> create or replace procedure xjyp is
  2    cur sys_refcursor;
  3    mstr xjytab%rowtype;
  4  begin
  5    open cur for
  6    select * from xjytab ;
  7    loop
  8       fetch cur into mstr;
  9       dbms_output.put_line(mstr.col1||'  '||mstr.col2||' date1: '||to_char(mstr.col3time,'yyyymmdd')
 10       ||' date2: '||to_char(mstr.d,'yyyymmdd'));--数据不是一行报错
 11       EXIT When cur%NOTFOUND;
 12       end loop;
 13    close cur;
 14   end;
 15  /

Procedure created

SQL> set serverout on;
SQL> exec xjyp;
23  just write date1: 20160830 date2: 20160901
1  20160803 date1: 20160907 date2: 20160701
7  procedure Test date1: 20160803 date2: 20160903
9  proceT2 date1: 20160825 date2: 20160902
4  test just date1: 20160708 date2: 20160902
5  includetest de date1: 20160805 date2: 20160730
9  proceT2 date1: 20160825 date2: 20160902
9  proceT2 date1: 20160825 date2: 20160902

PL/SQL procedure successfully completed

SQL> select *from xjytab;

      COL1 COL2                 COL3TIME    D
---------- -------------------- ----------- -----------
        23 just write           2016/8/30   2016/9/1
         1 20160803             2016/9/7    2016/7/1
         7 procedure Test       2016/8/3    2016/9/3
         9 proceT2              2016/8/25   2016/9/2
         4 test just            2016/7/8    2016/9/2
         5 includetest de       2016/8/5    2016/7/30
         9 proceT2              2016/8/25   2016/9/2

7 rows selected

--base_type
variable_name BOOLEAN
variable_name Number;
variable_name Number(intergral_num,decimal_num);
variable_name date;
variable_name varchar2(count_num);
--------------|||
SUBTYPE subtype_name IS base_type [ NOT NULL ]
--eg:
declare
SUBTYPE Counter IS Number;
accounts     Counter := 1;
--------------|||
SUBTYPE subtype_name IS base_type
  { precision [, scale ] | RANGE low_value .. high_value } [ NOT NULL ]
--eg:
DECLARE
  SUBTYPE Balance IS NUMBER(8,2);
 
  checking_account  Balance;
----------------------------------

DECLARE
  TYPE Foursome IS VARRAY(4) OF VARCHAR2(15);  -- VARRAY type 默认number当下标
  -- team Foursome := Foursome();  -- initialize to empty
  -- team Foursome:= Foursome('Arun', 'Amitha', 'Allan', 'Mae');
  -- team(1) team(2) team(3) team(4)
  TYPE Roster IS TABLE OF VARCHAR2(15);  -- nested table type默认number当下标
  --names Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
  TYPE aa_type IS TABLE OF INTEGER INDEX BY VARCHAR2(10);--用字符串当下标
  --aa_str  aa_type;
  --aa_str('sindex'):=3;
  --cindex varchar2(10);
  --cindex=aa_str.first;
  --while cindex is not null loop
  --cindex=aa_str.next(cindex); end loop;
----------------------------------
--Cursors
--one is implicit cursors  other is explicit cursors 

---*************************************************
--implicit cursor --隐式游标，执行语句系统内分配
--一些属性attribute
SQL%ISOPEN-- Attribute: Is the Cursor Open?游标是否打开
SQL%FOUND-- Attribute: Were Any Rows Affected?有行受影响？
SQL%NOTFOUND-- Attribute: Were No Rows Affected?没有行受影响？
SQL%ROWCOUNT-- Attribute: How Many Rows Were Affected?多少行受影响
SQL%BULK_ROWCOUNT-- (see "Getting Number of Rows Affected by FORALL Statement"获得受影响行数
SQL%BULK_EXCEPTIONS-- (see "Handling FORALL Exceptions After FORALL Statement Completes"不受影响的行数

---*************************************************
--explicit cursor --用户自定义游标
--You can either declare an explicit cursor first and then define it later in the same block, subprogram, or package,
--可以先声明游标，而后定义在一个代码块内（子程序或者包内）
-- or declare and define it at the same time.
--也可以声明定义一起。
--explicit cursor  declaration声明
CURSOR cursor_name 
[ parameter_list ] --(name1 type,name2 type...)
RETURN return_type;
--explicit cursor definitions定义
CURSOR cursor_name 
[ parameter_list ] --
[ RETURN return_type ]
  IS 
select_statement;
--Opening  Explicit Cursors
OPEN cursor_name;
--Fetching Data with Explicit Cursors
FETCH cursor_name INTO into_clause;
EXIT WHEN cursor_name%NOTFOUND;
--Closing Explicit Cursors
CLOSE cursor_name;

--Explicit Cursor Attributes
cursor_name%ISOPEN --Attribute: Is the Cursor Open?打开？
cursor_name%FOUND --Attribute: Has a Row Been Fetched?取到数据？
cursor_name%NOTFOUND --Attribute: Has No Row Been Fetched?取不到数据？
cursor_name%ROWCOUNT --Attribute: How Many Rows Were Fetched?已取多少行？
--
TYPE type_name IS REF CURSOR [ RETURN return_type ]
---------------------
declare
  ...
  cur sys_refcursor;
  ...
begin 
  ...
  open cur for select_statement;
  loop
     fetch cur into into_clause;
     EXIT When cur%NOTFOUND;
     ...
     end loop;
  close cur;
  ...
end;
/
--------------------------