存储过程代码示例
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;