oracle存储过程
1、没有返回值!函数一定要有返回值!
2、只能在PL块/JDBC中调用!
----------------------------------------------------------------------------------------------------------------------------------------------------------
hello 例:
create or replace procedure procHello
is
begin
dbms_output.put_line('Hello');
end;
两种调用方法:
1、
begin
procHello;
end;
2、
execute procHello
存储过程的创建授权和调用授权与函数完全一样!
授予建立函数的权限是create procedure
grant create procedure to test99;
revoke create procedure from test99;
授予别人使用函数的权限
grant execute on hello to test99;
revoke execute on hello from test99;
----------------------------------------------------------------------------------------------------------------------------------------------------------
-- 根据雇员编号返回雇员信息
create or replace procedure procGetEmp(eno emp.empno%type, e out emp%rowtype)
is
begin
select * into e from emp where empno=eno;
end;
declare
e emp%rowtype;
begin
procGetEmp(7369, e); -- e就是接收返回值参数
dbms_output.put_line(e.ename||' '||e.sal);
end;
----------------------------------------------------------------------------------------------------------------------------------------------------------
create or replace procedure procGetEmps(dno emp.deptno%type, emps out sys_refcursor)
is
begin
open emps for select * from emp where deptno=dno;
-- 不能关闭游标emps
end;
declare
emps sys_refcursor;
e emp%rowtype;
begin
procGetEmps(10, emps);
loop
fetch emps into e;
exit when emps%notfound;
dbms_output.put_line(e.ename||' '||e.sal);
end loop;
-- 关闭游标
close emps;
end;
----------------------------------------------------------------------------------------------------------------------------------------------------------
-- 保存学生,返回学生的新学号(序列值)create or replace procedure procSaveStudent(sname tblStu.name%type, sid out tblStu.id%type)
is
begin
sid:=seq.nextval;
insert into tblStu values (sid, sname);
end;
declare
sid int;
begin
procSaveStudent('Sophia', sid);
dbms_output.put_line(sid);
end;
----------------------------------------------------------------------------------------------------------------------------------------------------------
JDBC
// 返回序列值(存储过程返回参实现)
@Test
public void testGetSequence2() throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@192.168.5.8:1521:orcl", "test99", "test99");
// 注意调用方式是{call xxx(?, ?)}
CallableStatement call=conn.prepareCall("{call procSaveStudent(?,?)}");
call.setString(1, "Jasmine");
call.registerOutParameter(2, Types.INTEGER);
call.execute();
int sid=call.getInt(2);
System.out.println(sid);
call.close();
conn.close();
}
----------------------------------------------------------------------------------------------------------------------------------------------------------
-- 注意,没有函数的user_functions表!!!
select * from user_procedures -- 包含了存储过程和函数
select * from user_objects where object_type='FUNCTION'--函数
select * from user_objects where object_type='PROCEDURE'--存储过程
所有的用户对象都不能重名(表、函数、视图、存储过程、触发器都不能重名)