oracle-存儲過程代碼示例

存儲過程代碼示例

declare
  proc_date date not null default sysdate;

  --常量
  const_lbs_brch_bj VARCHAR2(2) := 'BJ';
  const_cur_year constant pls_integer := to_number(to_char(sysdate,'YYYY'));
  const_author constant varchar2(100) default 'bill priby1';
  --const_steven constant person_ot:=person_ot('','',175,to_date('09-23-1958','MM-DD-YYYY'));


  --變量
  v_char varchar2(2000);
  v_num  number(9,2) := 0.001;
  v_date date;
  v_bool boolean;
  v_blob blob;
  v_bfile bfile;
  v_rowid rowid;
  --v_urowid;urowid;
  Type v_type is ref cursor return pol_ben_actuary%RowType;
  v_pol_ben_actuary v_type;

  Type v_type_2 is ref cursor;
  v_pol_ben_actuary_2 v_type_2;
  v_pol_ben_actuary sys_refcursor;

  --集合
  Type v_list_t is table of pol_ben_actuary%RowType index by binary_integer;
  p_list v_list_t;

  --關聯數組
  Type v_list_of_names_t is table of  pol_ben_actuary.polno%Type;
   v_list_of_names v_list_of_names_t;

  --嵌套表
   type list_of_names is table of varchar2(100);
   --list_of_names.extend(4);
   --list_of_names multiset except children;

   --varray
   type first_names_t is varray(2) of varchar2(100);
   parents first_names_t;
   type child_names_t is varray(1) of varchar2(100);
   child child_names_t;


   cursor v_cur is select polno from pol_ben_actuary;

   cursor name_cur (v_param in number)
   is
   select polno from pol_ben_actuary
   where polno = v_param;


   cursor emp_cur return pol_ben_actuary%rowtype
   is
   select * from pol_ben_actuary
   where polno = '10';

   cursor company_cur is select * from pol_ben_actuary;
   record_or_variable_list   company_cur%Rowtype;

begin
  -- Call the procedure
proc_date:=sysdate;
dbms_output.put_line(proc_date);

   --parents.extend(2);
   --parents(1):='Samuel';
   --parents(2):='Charina';

   --children.Extend;
   --children(1):='Feather';

    if not company_cur%ISOpen
     then
        open company_cur;
          fetch company_cur into record_or_variable_list;
        close company_cur;
     end if;

exception
  when others
    then
      if company_cur%ISOpen then
        close company_cur;
      end if;



end;