【Oracle 学习笔记】Day 3 存储过程及函数

        
          create
        
        
          or
        
        
          replace
        
         package TestPack 
        
          is
        
        
          --
        
        
           Author  : ADMINISTRATOR
        
        
          --
        
        
           Created : 2012-4-27 14:01:29
        
        
          --
        
        
           Purpose : 测试
        
        
           

  type myCursor 
        
        
          is
        
         ref 
        
          cursor
        
        
          ;

  

  
        
        
          procedure
        
        
           TestPro(

     p_Cond 
        
        
          varchar2
        
        
          ,

     p_ReCursor0  out myCursor,

     p_ReCount out 
        
        
          number
        
        
          

     ); 

  
        
        
          FUNCTION
        
        
           TestFunc(

    p_MaterialID 
        
        
          varchar2
        
        
          

  )
        
        
          return
        
        
          varchar2
        
        
          ;




        
        
          end
        
        
           TestPack;




        
        
          create
        
        
          or
        
        
          replace
        
         package body TestPack 
        
          is
        
        
          procedure
        
        
           TestPro  

(

  p_Cond 
        
        
          varchar2
        
        
          ,

  p_ReCursor0  out myCursor,

  p_ReCount out 
        
        
          number
        
        
          

)


        
        
          is
        
        
          

  v_MySQL 
        
        
          varchar2
        
        (
        
          8000
        
        
          );


        
        
          Begin
        
        
          

  v_MySQL:
        
        
          =
        
        
          '
        
        
          select * from Materials where 1=1 
        
        
          '
        
        
          ;

  v_MySQL:
        
        
          =
        
        v_MySQL
        
          ||
        
        
          p_Cond;

  
        
        
          open
        
         p_ReCursor0 
        
          for
        
        
           v_MySQL;

  
        
        
          select
        
        
          Count
        
        (
        
          1
        
        ) 
        
          into
        
         p_ReCount 
        
          from
        
        
           Materials;

  
        
        
          ENd
        
        
           TestPro;

  


        
        
          FUNCTION
        
        
           TestFunc(

    p_MaterialID 
        
        
          varchar2
        
        
          

  )
        
        
          return
        
        
          varchar2
        
        
          is
        
        
          

    Type myCursor 
        
        
          is
        
         ref 
        
          cursor
        
        
          ;

    p_ReCursor0  myCursor;

    p_ReturnValue 
        
        
          varchar2
        
        (
        
          8000
        
        
          );

  
        
        
          Begin
        
        
          select
        
         MaterialName 
        
          into
        
         p_ReturnValue 
        
          from
        
         Materials 
        
          where
        
         MaterialID
        
          =
        
        
          ''
        
        
          ||
        
        p_MaterialID
        
          ||
        
        
          ''
        
        
          ;

    
        
        
          return
        
        
           p_ReturnValue;

    
        
        
          End
        
        
           TestFunc;


        
        
          end
        
         TestPack;