oracle 預存程序文法例子

包的定義:
create or replace package myTest
is
type out_cur is ref cursor;
procedure writeCount(codeid in nvarchar2);
procedure testSandyInSert(codeid in nvarchar2,counts out number);
end myTest;

預存程序的定義:
create or replace package body myTest
is
procedure writeCount(codeid in nvarchar2)
is
m_count number;
begin m_count:=0;
select count(1) into m_count from code where code_id = codeid;
dbms_output.put_line('輸入參數是'||codeid);
dbms_output.put_line('查詢結果是'||m_count);
end;
procedure testSandyInSert(codeid in nvarchar2,counts out number)
is
m_cur out_cur;
m_code_sn nvarchar2(50);
m_code_id nvarchar2(50);
m_code_name nvarchar2(50);
m_insertstr nvarchar2(50);
m_for number:=0;
begin counts:=0;
open m_cur for select code_sn,code_id,code_name from code where code_id= codeid;
loop fetch m_cur into m_code_sn,m_code_id,m_code_name;
exit when m_cur %notfound;
case UPPER(m_code_sn) when 'SP_CTRL_L' then m_code_sn:='0';
when 'dld' then m_code_sn:='1';
else m_code_sn:='3';
end case;
if UPPER(m_code_sn) ='SP_CTRL_L' then dbms_output.put_line('條件成立執行,測試if語句的使用');
else dbms_output.put_line('條件不成立執行,測試if語句的使用');
end if;
m_insertstr:=''''||m_code_sn||''','''||m_code_id||''','''||m_code_name||'''';
begin execute immediate 'insert into sandytest(col1,col2,col3) values('||m_insertstr||')';
dbms_output.put_line('插入表成功!');
counts:=1;
exception when others then dbms_output.put_line('插入表失敗!');
end;
end loop;
for m_for in 1..10 loop dbms_output.put_line('迴圈測試:'||m_for);
end loop;
close m_cur;
end;
end myTest;