Oracle -存储过程

语法

create [or replace] procedure 过程名[(参数名in/out 数据类型)]
as
begin
        PLSQL子程序体
end

或者

create [or replace] procedure 过程名[(参数名in/out 数据类型)]
is
begin
        PLSQL子程序体
end

调用

第一方式

CALL SCOTT.SAYHELLOWORLD();

调用存储过程第二方式

begin
  sayHelloWord();
  sayHelloWord();
end;

无参数

create or replace procedure sayhelloworld
as
--说明部分
begin
dbms_output.put_line('Hello World');

end;

带参数

给指定的员工涨100，并且打印涨前和涨后的薪水

create or replace procedure raiseSalary(eno in number)
is
--定义变量保存涨前的薪水
psal emp.sal%type;
begin
--得到涨前的薪水
select sal into psal from emp where empno=eno;
--涨100
update emp set sal=sal+100 where empno=eno;
dbms_output.put_line('涨前:'||psal||'   涨后：'||(psal+100));
end raiseSalary;

begin
 raisesalary(7369);
 raisesalary(7369);
 --需要在这里提交事务，不要在内部提交
 commit;
 
end;
CALL SCOTT.RAISESALARY(7844);

Out

create or replace procedure queryEmpInformation(eno in number,
pename out varchar2,
psal   out number,
pjob   out varchar2)
is
begin
select ename,sal,job into pename,psal,pjob from emp where empno=eno;                                             
end queryEmpInformation;

CALL SCOTT.QUERYEMPINFORMATION('7521',?,?,?);

存储过程的调试

存储函数

函数(Function)为一命名的存储函数，可以带参数，并返回一计算值。函数和过程的结构相似，但必需要有一个return语句，用于返回函数值。函数说明要指定函数名、结果值的类型，以及参数类型。

语法

create [or replace] function 函数名（参数）return 返回值类型
as
    PLSQL子程序

例子

create or replace function queryincome(eno in number) return number
as
psal emp.sal%type;--月薪
pcomm emp.comm%type;--奖金
begin
--得到当前月薪和奖金
select sal,comm into psal,pcomm from emp where empno = eno;

--返回年收入
return psal*12 + nvl(pcomm,0);
end;

函数的调用

declare
y_sal number;
begin
y_sal:=queryincome(7369);
DBMS_OUTPUT.put_line('年薪:'||y_sal);
end;

什么时候用存储函数和存储过程

如果有一个返回值就用存储函数，否则就用存储过程

out 中使用游标

CREATE OR REPLACE package SCOTT.mypackage is
type empcursor is ref cursor;
procedure queryEmpList(dno in number,empList out empcursor);

end mypackage;

CREATE OR REPLACE package body SCOTT.mypackage is

procedure queryEmpList(dno in number,empList out empcursor)
as
begin

open empList for select * from emp where deptno=dno;

end;

end mypackage;

java中调用

存储过程

conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql);

//对于in参数，赋值
call.setInt(1,7839);

//对于out参数，申明
call.registerOutParameter(2, OracleTypes.VARCHAR);
call.registerOutParameter(3, OracleTypes.NUMBER);
call.registerOutParameter(4, OracleTypes.VARCHAR);

//执行
call.execute();

存储函数

Connection conn = null;
CallableStatement call = null;
try {
   conn = JDBCUtils.getConnection();
   call = conn.prepareCall(sql);

   call.registerOutParameter(1, OracleTypes.NUMBER);
   call.setInt(2, 7839);

   //执行
   call.execute();

   //取出年收入
   double income = call.getDouble(1);

游标

conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql);

//对于in参数，赋值
call.setInt(1,20);

//对于out参数，申明
call.registerOutParameter(2, OracleTypes.CURSOR);

//执行
call.execute();