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;