Oracle存储过程

存储过程和存储函数是指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数。

存储过程和存储函数的相同点：完成特定功能的程序
存储过程和存储函数的区别：是否用return语句返回值 //存储过程无返回值

语法
//创建或者替换一个存储过程(不能修改 替换相当于修改)
create [or replace] PROCEDURE 过程名(参数列表)
AS --说明部分 AS不可省略
PLSQL子程序体;

创建存储过程

简单存储过程的例子
每调用一次打印一次hello world
create or replace procedure sayhelloworld
as
begin
dbms_output.put_line('Hello world');
end;

--调用存储过程的两种方式
1.
execute sayhelloworld();

2.
begin
myprocsayhelloworld();
end;

--创建一个带存数的存储过程
--给指定的员工涨100块钱工资,并打印涨前和涨后的薪水
--带参数的存储过程需要指定参数是输入还是输出参数
create or replace procedure raisesalary(eno in number)--in 代表输入参数
as
--定义一个变量保存涨前的薪水
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;

--一般不在存储过程或者存储函数中进行commit或者roolback
--因为其是一个子程序,是给别人调用的,提交或者回滚应有调用者决定,但其本质可以commit或者roolback

--调用给两名员工涨工资
begin
raisesalary(1001);
raisesalary(1002);
commit;
end;

函数和过程的结构类似,但必须有一个return字句,
用于返回计算的值

语法格式
create [or replace] FUNCTION 函数名(参数列表)
return 函数值类型
AS
PLSQL子程序体;

例子
查询某个员工的年收入
create or replace function queryempincome(eno in number )

return number--指明返回值的类型

as

--定义变量保存员工的薪水和奖金
psal emp.sal%type;
pcomm emp.comm%type;

begin

--得到该员工的月薪和奖金
select sal,comm into psal,comm from emp where empno = eno;

--直接返回年收入
return psal*12+pcomm;

end;

in和out参数
一般情况下,存储函数可以有一个返回值,存储过程没有返回值

过程和函数都可以通过out指定一个或者多个输出参数,我么可以利用out参数,在过程和函数中时间返回多个值
1 过程和函数都可以有out参数
2 过程和函数都可以有多个out参数
3 存储过程可以通过out参数来实现返回值
一般默认原则:单个返回值用存储函数,多个或者无选用存储过程

--out参数:查询某个员工的姓名 月薪 和职位
create or replace procedure queryempinformation(eno in number,
pename out varchar2
psal out number,
pjob out varchar2)

as
begin
--得到员工的姓名 月薪和职
--into pename,psal,pjob赋值给输出参数
select ename,sal,empjob into pename,psal,pjob form emp where empno = eno;

end;

out中返回集合

在Java中访问存储过程和存储函数

JDBC

访问存储过程
String sql = "{call queryempinformation(?,?,?,?)}"
Connection conn = null;
CallableStatement call = null;

//得到一个链接
conn = JDBCUtils.getConnection();
//通过链接创建出statement
call = conn.getprepareCall(sql);
//对于in 参数,赋值 1代表参数位置,7839代表参数值
call.setInt(1,7839)
//对于out 参数 ,声明其是输出参数
//call.registerOutParamerter(parametrIndex,sqlType);
call.registerOutParamerter(2,OracleTypes.VARCHAR);
call.registerOutParamerter(3,OracleTypes.NUMBER);
call.registerOutParamerter(4,OracleTypes.VARCHAR);
//执行调用
call.execute;
//取出结果
String name = call.getString(2);
double sal = call.getDouble(3);
String job = call.getString(4);
System.out.println(name+sal+job);

访问存储函数

//第一个返回时是输出参数,第二个输入参数
String sql = "{?=call querryempincome(?)}";
Connection conn = null;
CallableStatement call = null;
//得到一个链接
conn = JDBCUtils.getConnection();
//通过链接创建出statement
call = conn.getprepareCall(sql);
//对于输出参数,声明
call.registerOutParamerter(1,OracleTypes.NUMBER);
//对于输入参数 赋值
call.setInt(2,7839);
//执行调用
call.execute();
//取出年收入的结果
double income = call.getDouble(1);
System.out.pringln("年收入"+income);

在out参数中使用光标
声明包结构
包头
包体

包头
create or replace package mypackage as

--自定义光标类型
type empcursor is ref cursor;
--声明存储过程
procedure queryEmplist(dno in number,emplist out empcursor);

end mypackage;
包体
BEGIN
--打开光标
open emplist for select * from emp where deptno = dno;

END queryEmplist

create or replace package mypackage as

--自定义光标类型
type empcursor is ref cursor;
--声明存储过程
procedure queryEmplist(dno in number,emplist out empcursor);

BEGIN
--打开光标
open emplist for select * from emp where deptno = dno;

END queryEmplist;
end mypackage;

应用中访问包中的存储过程
需要带上包名

import org.junit.Test
public class testCursor{
public void restCursor(){

string sql="{call mypackage.queryemplist()}";
connection conn=null;
callablestatement call=null;
resultset rs=null;
try {
conn=jdbcutils.getconnnection();
call=conn.prepareCall(sql);
call.setInt(1,10);
call.registoutparameter(2,oracletypes.cursor);
call.execute();
((oraclecallablestatement)call).getCursor(2);
while(rs.next()){
//取出该员工的姓名，薪水和职位
int empno=rs.getint("empno");
string name=rs.getstring("ename");
double salary =rs.getdouble("sal");
string job=rs.getstring ("job");
system.out.println(empno+" "+ename+" "+" "+sal+" "+job);
}
}
catch(exception e){
e.printstacktrace();
}
finally{
JDBCUtils.release(conn,call,rs);
}
}