oracle存储过程

1，存储过程实例一，参数的in可以省略。参数默认为输入参数。

create or replace procedure p1(pname in varchar2,newSal in number) is

begin

update emp set sal = newSal where name = pname;

end;

2，java程序中调用上述存储过程的方法：

//先获得数据库连接connection，

connection conn = ...

CallableStatement cs = conn.prepareCall("{call p1(?,?)}");

cs.setString(1,"zhangsan");

cs.setInt(2,5000);

cs.execute();

cs.close();

conn.close();

3，定义pl/sql记录的实例

declare

type emp_record_type is record(name emp.name%type,sal emp.sal%type,title emp.title%type);

sp_record emp_record_type; --定义 emp_record_type类型的变量sp_record

begin

select name,sal,title into sp_record

from emp where empno = '1111';

dbms_output.put_line("name:"|| sp_record.name);

end;

/

4，定义和使用表类型(相当于java中的数组）

declare 

type sp_table_type is table of emp.name%type index by binary_integer;

sp_table sp_table_type;

begin

select name into sp_table(0) from emp where empno = '1111';

dbms_output.put_line("name:" || sp_table(0));

end;

5，定义和使用游标类型

declare

type sp_emp_cursor is ref cursor; --定义游标类型

test_cursor sp_emp_cursor;

v_name emp.name%type;

begin

open test_cursor for select name,sal from emp;

loop

fetch test_cursor into v_name;

exit when test_cursor%notfound;

dbms_output.put_line("name:"|| v_name);

end loop

end;

6，存储过程实例二，返回结果集的过程

create or replace package testpackage as

type test_cursor is ref cursor;

end testpackage;

create or replace procedure p1(pname in varchar2,p_cursor out testpackage.test_cursor) is

begin

open p_cursor for select * from emp where name like '%'||panme||'%';

end;