Oracle存储过程创建和使用举例

oracle 存储过程创建和使用举例

1.创建存储过程create or replace package pk_1 as; //创建包的声明TYPE cur is ref cursor; //声明一个指针procedure p1(cr out cur); end;

/

create or replace package body pk_1 as; //创建包体 procedure p1(cr out cur) is begin open cr for select STATUS,table_name; from dba_tables; end; end;

/

2.调用(在sqlplus中):set serveroutput on //设置允许进行输出操作/

declare cc pk_1.cur; s varchar2(20);t varchar2(30);c11 number :=0; //变量c11一定要赋初值,不然将不能正常运算beginpk_1.p1(cc);loop fetch cc into s,t; exit when cc%notfound; c11 := c11 + 1; dbms_output.put_line('status = ' || s || '; table_name; = ' || t);end loop;close cc;dbms_output.put_line(c11);end;

/

3.对于不是返回一个结果集的存储过程的调用

CREATE OR REPLACE PROCEDURE p1( parameter1 in number, parameter2 out number; ) is value1 INTEGER :=0;begin select count(*) into value1 from dba_tables where table_name like '%t%'; parameter2 := value1;end p1;

sqlplus 中运行procedure第一种调用方式:

(1)set serveroutput on declare v_p_o_succeed varchar2; begin pr_jwry_info('a_p_i_date',v_p_o_succeed) ; dbms_output.put_line(v_p_o_succeed); end;第二种调用方式:

(2) var cc varchar(20) p1(1,:cc) print cc;