oracle 存储过程的一些应用-02

1.头格式

create or replace package PACK_TT is

 --定义变量
 TYPE M_REFCUR IS REF CURSOR; 
 CONST_NULL CONSTANT VARCHAR2(40) := '00000000-0000-0000-0000-000000000000';
 
 PROCEDURE TEST_PRO(TEST IN VARCHAR2, 
                    CURTABLES OUT M_REFCUR); 
 
end PACK_TT;

2.body 

create or replace package body PACK_TT is

 PROCEDURE TEST_PRO(TEST IN VARCHAR2, 
                    CURTABLES OUT M_REFCUR)   is 
                    
  --定义变量
   V_STRSQL       VARCHAR2(30000);
    CUR_TABLENAME  M_REFCUR;
    nACount        Number;
   begin    
   --1 通过游标
   V_STRSQL := 'select table_name from dba_tab_columns where lower(column_name) =''Test'' and DATA_LENGTH = 36 ';

  OPEN CUR_TABLENAME FOR V_STRSQL;--读取数据
  --循环读取数据
  LOOP
    FETCH CUR_TABLENAME    INTO V_STRTABLENAME; --可以去多个变量
    EXIT WHEN CUR_TABLENAME%NOTFOUND;      
    /*
     --do 
    */      
    END LOOP;
    
    --2  直接读取到变量中
    V_STRSQL := ' select count(1)  from dba_tab_columns ';  
    EXECUTE IMMEDIATE V_STRSQL  INTO nACount;   
    
    --3 读取到游标
    OPEN CURTABLES FOR V_STRSQL;
    
    EXCEPTION
        WHEN OTHERS THEN
        vERR := 'XXXX出错 ' || sqlerrm;
        vERR := REPLACE(vERR,'ORA-20125','');
end TEST_PRO;

end PACK_TT;

3. 异常处理

------
EXCEPTION
   WHEN first_exception THEN  <code to handle first exception >
   WHEN second_exception THEN  <code to handle second exception >
   WHEN OTHERS THEN  <code to handle others exception >
END;
-----

4.SQL语句中设置变量

 ---使用变量  

V_STRSQL := 'INSERT INTO ' || V_TEMPTABLENAME
                           || '(COMPANYID,ITEMNAME )  VALUES ( :1,:2)';

EXECUTE IMMEDIATE V_STRSQL USING  SUBSTR( V_COMPANYID,2,36 ), V_UNITDATA_RC.ITEMNAME;

5.自定义类型接受游标数据

 ---定义类型
 TYPE UNIOTDATA_RCT IS RECORD
    (
        ITEMNAME VARCHAR2(100),
        INDEXNO  NUMBER,
        TOTALSUM NUMBER
    );
   
   V_UNITDATA_RC  UNIOTDATA_RCT;
    
 ---循环游标将数据传给   V_UNITDATA_RC 
     LOOP
              FETCH V_UNITDATA_CS INTO V_UNITDATA_RC;
              EXIT WHEN V_UNITDATA_CS%NOTFOUND;
              
    end LOOP;
    close V_UNITDATA_CS;