Orale的struct,array,cursor,stored procedure,function,package的用法例子

===============================
Oracle Cursor 用法：
===============================
--@F:/sql.oracle/func.sp/other.sql

--------------------------ddl and dml : create, insert, delete-------------------------------
DROP TABLE customer;
/
CREATE TABLE customer
(
    pkey        VARCHAR2(10) NOT NULL    PRIMARY KEY,
    fname        VARCHAR2(20),
    lname        VARCHAR2(20),
    ccode        VARCHAR2(10)
)
/

insert into customer(pkey,fname,lname,ccode) values('1','david','Smith','');
insert into customer(pkey,fname,lname,ccode) values('2','david','Smith','');
insert into customer(pkey,fname,lname,ccode) values('3','david','Smith','');

/
-----------------------create a procedure:----------------------
drop procedure sp_cursor_retrieveall;
/

create or replace procedure sp_cursor_retrieveall
( resultset out sys_refcursor)
as
begin
  open resultset for select pkey,fname,lname,ccode from customer;
end;

/
-----------------------test procedure:----------------------

--动态变量绑定方法
variable lc refcursor;
exec sp_cursor_retrieveall(:lc);
print lc;

/

-----------------------create a function:----------------------
drop function f_cursor_retrieveall;
/

create or replace function f_cursor_retrieveall
return sys_refcursor IS
resultset sys_refcursor;
begin
  open resultset for select pkey,fname,lname,ccode from customer;
  return resultset;
end f_cursor_retrieveall;

/
-----------------------test function:----------------------
variable lc refcursor;
exec :lc := f_cursor_retrieveall();
print lc;
/


-----------------------create a procedure ----------------------
drop procedure sp_cursor_retrieve;
/

create or replace procedure sp_cursor_retrieve
( fname in varchar2,
  resultset out sys_refcursor)
as
begin
  open resultset for select pkey,fname,lname,ccode from customer where fname=fname;
end;

/

-----------------------test procedure:----------------------
variable lc refcursor;
exec sp_cursor_retrieve('david',:lc);
print lc;
/

-----------------------create a function:----------------------
drop function f_cursor_retrieve;
/

create or replace function f_cursor_retrieve
(fname in varchar2)
return sys_refcursor IS
resultset sys_refcursor;
begin
  open resultset for select pkey,fname,lname,ccode from customer where fname = fname;
  return resultset;
end f_cursor_retrieve;

/
-----------------------test function:----------------------
variable lc refcursor;
exec :lc := f_cursor_retrieve('david');
print lc;
/




===============================
Oracle struct array 在function 中的用法：
===============================
----------------------------------------------------------------------------------------------------------------
--define some structures as input parameter.
----------------------------------------------------------------------------------------------------------------

--struct1  (struct)
DROP TYPE STRUCT1;
/
DROP TYPE STRUCT2;
/
DROP TYPE STRUCT3;
/
DROP TYPE ARRAYOFSTRUCT2;
/
DROP TYPE ARRAYOFSTRUCT3;
/
DROP TYPE RETURNSTRUCT;
/
DROP TYPE ARRAYOFRETURNSTRUCT;
/
DROP FUNCTION f_array_struct;
/
CREATE OR REPLACE TYPE STRUCT1 AS OBJECT (
 CL_INT INT ,
 --CL_TIMESTAMPWITHLOCALTIMEZONE TIMESTAMP WITH LOCAL TIME ZONE ,
 CL_TIMESTAMPWITHTIMEZONE TIMESTAMP WITH TIME ZONE ,
 CL_NUMBER NUMBER ,
 CL_RAW RAW(1000) ,
  -- CL_REF REF
  -- CL_ARRAY ARRAY ,
  -- CL_BLOB BLOB ,
  -- CL_INTERVALDS INTERVALDS ,
  -- CL_INTERVALYM INTERVALYM ,
 --CL_LONG LONG ,
 CL_CHAR CHAR ,
 CL_FLOAT FLOAT ,
 CL_REAL REAL ,
 CL_VARCHAR2 VARCHAR2(200) ,
 CL_DATE DATE ,
 CL_TIMESTAMP TIMESTAMP ,
 CL_BLOB BLOB ,
 CL_CLOB CLOB
);
/

--STRUCT2  (struct)
CREATE OR REPLACE TYPE STRUCT2 AS OBJECT
(
 CL_INT INT ,
 --CL_TIMESTAMPWITHLOCALTIMEZONE TIMESTAMP WITH LOCAL TIME ZONE ,
 CL_TIMESTAMPWITHTIMEZONE TIMESTAMP WITH TIME ZONE ,
 CL_NUMBER NUMBER ,
 CL_RAW RAW(1000) ,
  -- CL_REF REF
  -- CL_ARRAY ARRAY ,
  -- CL_BLOB BLOB ,
  -- CL_INTERVALDS INTERVALDS ,
  -- CL_INTERVALYM INTERVALYM ,
 --CL_LONG LONG ,
 CL_CHAR CHAR ,
 CL_FLOAT FLOAT ,
 CL_REAL REAL ,
 CL_VARCHAR2 VARCHAR2(200) ,
 CL_DATE DATE ,
 CL_TIMESTAMP TIMESTAMP ,
 CL_BLOB BLOB ,
 CL_CLOB CLOB
);
/
--a array of STRUCT2  (struct)
CREATE OR REPLACE TYPE ARRAYOFSTRUCT2 AS TABLE OF STRUCT2;
/
--STRUCT3  (struct)
CREATE OR REPLACE TYPE STRUCT3 AS OBJECT (
 CL_INT INT ,
 --CL_TIMESTAMPWITHLOCALTIMEZONE TIMESTAMP WITH LOCAL TIME ZONE ,
 CL_TIMESTAMPWITHTIMEZONE TIMESTAMP WITH TIME ZONE ,
 CL_NUMBER NUMBER ,
 CL_RAW RAW(1000) ,
  -- CL_REF REF
  -- CL_ARRAY ARRAY ,
  -- CL_BLOB BLOB ,
  -- CL_INTERVALDS INTERVALDS ,
  -- CL_INTERVALYM INTERVALYM ,
 --CL_LONG LONG ,
 CL_CHAR CHAR ,
 CL_FLOAT FLOAT ,
 CL_REAL REAL ,
 CL_VARCHAR2 VARCHAR2(200) ,
 CL_DATE DATE ,
 CL_TIMESTAMP TIMESTAMP ,
 CL_BLOB BLOB ,
 CL_CLOB CLOB
);
/
--a array of STRUCT3  (struct)
CREATE OR REPLACE TYPE ARRAYOFSTRUCT3 AS TABLE of STRUCT3;
/
--return STRUCT3  (struct)
CREATE OR REPLACE TYPE RETURNSTRUCT AS OBJECT (
    CODE    VARCHAR2(2),
    MESSAGE    VARCHAR2(200)    
);
/
--a array of STRUCT3  (struct)
CREATE OR REPLACE TYPE ARRAYOFRETURNSTRUCT AS TABLE OF RETURNSTRUCT;
/

----------------------------------------------------------------------------------------------------------------
--define a function:
----------------------------------------------------------------------------------------------------------------
create or replace FUNCTION F_ARRAY_STRUCT (
parameter1 STRUCT1,
parameter2 ARRAYOFSTRUCT2,
parameter3 ARRAYOFSTRUCT3)

RETURN ARRAYOFRETURNSTRUCT IS
returnList ARRAYOFRETURNSTRUCT;

i NUMBER;

BEGIN
  -- initialize an array
  returnList := ARRAYOFRETURNSTRUCT();
 
  --define a size of an array
  returnList.EXTEND(1);
 
  --initialize a struct.
  returnList(1) := RETURNSTRUCT(NULL, NULL);
 
  --assign some values to a struct.
  returnList(1).CODE := 13;
  returnList(1).MESSAGE := 'You are lucky';
    
  RETURN returnList;
 
END F_ARRAY_STRUCT;

----------------------------------------------------------------------------------------------------------------
--test function program:
----------------------------------------------------------------------------------------------------------------
---oracle P1031

DECLARE
  PARAMETER1 YUANJS.STRUCT1;
  PARAMETER2 YUANJS.ARRAYOFSTRUCT2;
  PARAMETER3 YUANJS.ARRAYOFSTRUCT3;
  v_Return YUANJS.ARRAYOFRETURNSTRUCT;
BEGIN
  -- Modify the code to initialize the variable
   PARAMETER1 := NULL;
  --Modify the code to initialize the variable
   PARAMETER2 := NULL;
  -- Modify the code to initialize the variable
   PARAMETER3 := NULL;

  v_Return := F_ARRAY_STRUCT(
    PARAMETER1 => PARAMETER1,
    PARAMETER2 => PARAMETER2,
    PARAMETER3 => PARAMETER3
  );
  -- Modify the code to output the variable
  DBMS_OUTPUT.PUT_LINE('CODE = ' || v_Return(1).CODE);
  DBMS_OUTPUT.PUT_LINE('MESSAGE = ' || v_Return(1).MESSAGE);
END;


===============================
Oracle struct array 在procedure 中的用法：
===============================
----------------------------------------------------------------------------------------------------------------
--define a structure
----------------------------------------------------------------------------------------------------------------

--struct1  (struct)
DROP TYPE STRUCT1;
/
DROP TYPE ARRAYOFSTRUCT1;
/
DROP PROCEDURE SP_ARRAY_STRUCT;
/
CREATE OR REPLACE TYPE STRUCT1 AS OBJECT (
 CL_INT INT ,
 --CL_TIMESTAMPWITHLOCALTIMEZONE TIMESTAMP WITH LOCAL TIME ZONE ,
 CL_TIMESTAMPWITHTIMEZONE TIMESTAMP WITH TIME ZONE ,
 CL_NUMBER NUMBER ,
 CL_RAW RAW(1000) ,
  -- CL_REF REF
  -- CL_ARRAY ARRAY ,
  -- CL_BLOB BLOB ,
  -- CL_INTERVALDS INTERVALDS ,
  -- CL_INTERVALYM INTERVALYM ,
 --CL_LONG LONG ,
 CL_CHAR CHAR ,
 CL_FLOAT FLOAT ,
 CL_REAL REAL ,
 CL_VARCHAR2 VARCHAR2(200) ,
 CL_DATE DATE ,
 CL_TIMESTAMP TIMESTAMP ,
 CL_BLOB BLOB ,
 CL_CLOB CLOB
);
/

CREATE or REPLACE TYPE ARRAYOFSTRUCT1 AS VARRAY(99) OF STRUCT1;
/


----------------------------------------------------------------------------------------------------------------
--define a procedure:
----------------------------------------------------------------------------------------------------------------
CREATE or REPLACE PROCEDURE SP_ARRAY_STRUCT (
ARG_IN IN ARRAYOFSTRUCT1,
ARG_OUT OUT ARRAYOFSTRUCT1)
AS

i NUMBER;

BEGIN
    ARG_OUT := ARG_IN;
END SP_ARRAY_STRUCT;

/
----------------------------------------------------------------------------------------------------------------
--test procedure program:
----------------------------------------------------------------------------------------------------------------
---oracle P1031
DECLARE
  ARG_IN YUANJS.ARRAYOFSTRUCT1;
  ARG_OUT YUANJS.ARRAYOFSTRUCT1;
BEGIN
  -- Modify the code to initialize the variable
  -- ARG_IN := NULL;

  SP_ARRAY_STRUCT(
    ARG_IN => ARG_IN,
    ARG_OUT => ARG_OUT
  );
  -- Modify the code to output the variable
  -- DBMS_OUTPUT.PUT_LINE('ARG_OUT = ' || ARG_OUT);
END;

/

===============================
Oracle package 的用法：
===============================

--@F:/sql.oracle/package/package.sql
----------------------------------------------------------------------------------------------------------------
--define a package.
----------------------------------------------------------------------------------------------------------------
drop package pkg_resultset;
/

create or replace package pkg_resultset
as
     type search_result IS ref cursor;                                                           
    function area_search return search_result;                              
end pkg_resultset;   

/
                                                      
create or replace package body pkg_resultset
as
    function  area_search  
         return search_result  is
        cursor1 search_result;                                                       
    begin                                                                    
            open cursor1 for select user,sysdate from dual;                                
             return cursor1;                                                               
        exception                                                                
            when others then                                                         
                null;                                                                    
    end area_search;                                                                     
end pkg_resultset;                                                                     

/
------------------test program------------------------
variable lc refcursor;
exec :lc := PKG_RESULTSET.AREA_SEARCH();
print lc;
/

------------------test program-----------------------
DECLARE
  v_Return YUANJS.PKG_RESULTSET.search_result;
BEGIN
  v_Return := PKG_RESULTSET.AREA_SEARCH();
   
  -- Modify the code to output the variable
  -- DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
  --print v_Return;
END;

/