oracle的存储过程和存储函数
1.存储过程和存储函数的区别
(1)返回值:存储函数必须有一个通过return得到的返回值,而多个通过输出参数得到的返回值;存储过程只有通过输出参数得到的返回值。
存储函数:create or replace function fun1(参数1,参数2,参数...)
return 返回类型
is
--定义变量
begin
....执行语句
end
存储过程:create or replace procedure pro1(参数1,参数2,参数...)
is
--定义变量
begin
--执行语句
end
(2)调用的方式不同:函数可以直接在sql语句中调用,但是过程必须单独使用。
如:select fun1 from dual; -----在sql语句中调用函数。
(3)用途不一样:函数一般是用来返回一个计算结果,过程是用来完成特定的数据操作。
2.存储过程及其调用
(1)创建一个无参数的存储过程
create or replace procedure proce1 is
begin
dbms_output.put_line('HelloWorld!');
end;
调用方式1:exec proce1;
调用方式2:
begin
proce1;
end;
(2)创建一个有输入参数的存储过程
create or replace procedure proce2(v_empno in emp.empno%type,
v_ename in emp.ename%type,
v_sal in emp.sal%type)
is
begin
insert into emp(empno,ename,sal) values(v_empno,v_ename,v_sal);
end;
调用方式1:
begin
proce2('111','qaz',2000);
proce2('222','wsx',1020);
proce2('333','edc',3300);
end;
调用方式2:
exec proce2('111','qaz',2000);
exec proce2('222','wsx',1020);
exec proce2('333','edc',3300);
(3)创建一个有输入和输出参数的存储过程
create or replace procedure pro_query_enameAndSal_by_empno(v_empno in emp.empno%type
,v_ename out emp.ename%type
,v_sal out emp.sal%type)
is
begin
select ename,sal into v_ename,v_sal from emp where empno = v_empno;
end;
调用存储过程:
declare
p_ename emp.ename%type;
p_sal emp.sal%type;
begin
pro_query_enameAndSal_by_empno(7369,v_ename=>p_ename,v_sal=>p_sal);
dbms_output.put_line('员工号为7369的员工是'||p_ename||'薪水是'||p_sal);
end;
java调用存储过程:
String sql = "{call pro_query_enameAndSal_by_empno(?,?,?)}";
cs = ct.prepareCall(sql);
cs.setInt(1, 7369);
cs.registerOutParameter(2, OracleTypes.VARCHAR);
cs.registerOutParameter(3, OracleTypes.INTEGER);
cs.execute();
String ename = cs.getString(2);
String sal = cs.getString(3);
System.out.println("员工姓名是"+ename+"工资是"+sal);
3.存储函数及其调用
(1)创建一个无参数存储函数:
create or replace function fun_hello
return varchar2 is
begin
return 'hello world';
end;
调用方式1(sql语句中进行调用):select fun_hello from dual;
调用方式2:
declare
str varchar2(20);
begin
str :=fun_hello;
dbms_output.put_line(str);
end;
(2)创建一个有输入参数的存储函数
create or replace function fun_get_annualSal_by_empno(f_empno in emp.empno%type)
return number
is
v_sal emp.sal%type;
v_comm emp.comm%type;
begin
select sal,comm into v_sal,v_comm from emp where empno = f_empno;
return v_sal*12+nvl(v_comm,0);
end;
调用:select fun_get_annualSal_by_empno(7369) from dual;
(3)创建一个有输入和输出的存储函数
create or replace function fun_get_annualSal_by_empno1(v_empno in emp.empno%type,
v_ename out emp.ename%type,v_comm out emp.comm%type)
return number
is
v_sal emp.sal%type;
begin
select ename,sal,nvl(comm,0) into v_ename,v_sal,v_comm from emp where empno = v_empno;
return v_sal*12+v_comm;
end;
调用:
declare
v_annualSal number(10,2);
v_ename emp.ename%type;
v_comm emp.comm%type;
begin
v_annualSal := fun_get_annualSal_by_empno1(7499,v_ename,v_comm);
dbms_output.put_line('员工姓名是'||v_ename||'奖金是'||v_comm||'年薪是'||v_annualSal);
end;
用java操作该存储函数:
cs = ct.prepareCall("{? = call fun_get_annualSal_by_empno1(?,?,?)}");
//首先要注册函数返回值
cs.registerOutParameter(1, OracleTypes.DOUBLE);
cs.setInt(2, 7499);
cs.registerOutParameter(3, OracleTypes.VARCHAR);
cs.registerOutParameter(4, OracleTypes.DOUBLE);
//【注意】注册完成必须要执行!!!
cs.execute();
System.out.println("员工姓名是"+cs.getString(3)+"薪水是"+cs.getDouble(1)+"奖金是"+cs.getDouble(4));
posted on 2017-08-03 16:23 Luthien_rong 阅读(102) 评论(0) 编辑 收藏 举报