存储过程
1 什么是存储过程?
用于在数据库中完成特定的操作或者任务。是一个PLSQL程序块,可以永久的保存在数据库中以供其他程序调用。
2 存储过程的参数模式
存储过程的参数特性:
IN类型的参数 OUT类型的参数 IN-OUT类型的参数
值被 传递给子程序 返回给调用环境 传递给子程序
返回给调用环境
参数形式 常量 未初始化的变量 初始化的变量
使用时 默认类型 必须明确指定 必须明确指定
在oracle10中写好了存储过程,代码如下:
1 CREATE OR REPLACE Procedure Proc_Insert 2 ( 3 sName in varchar2, 4 sAge in int, 5 sExeTime in varchar2 6 ) 7 is 8 9 begin 10 Insert into T_TEST(C_ID, C_NAME, C_AGE, C_INTIME, C_EXETIME) 11 values(T_TEST_CID.nextval,sName,sAge, sysdate, to_date(sExeTime, 'yyyy-mm-dd')); 12 end Proc_Insert;
为了在PL/SQL中调用这个存储过程,采用了如下的代码:
1 begin 2 Proc_Insert('hello6',25,'2005-12-24'); 3 commit; 4 end;
另外增加了一个存储过程,但是sExeTime是date类型,那么调用的时候,就必须先把字符串转换成date类型,否则将会调用失败。
存储过程:
1 CREATE OR REPLACE Procedure Proc_Insert2 2 ( 3 sName in varchar2, 4 sAge in int, 5 sExeTime in date 6 ) 7 is 8 9 begin 10 Insert into T_TEST(C_ID, C_NAME, C_AGE, C_INTIME, C_EXETIME) 11 values(T_TEST_CID.nextval,sName,sAge, sysdate, sExeTime); 12 end Proc_Insert2;
调用的代码:
1 begin 2 Proc_Insert2('hellowhat', 26, to_date('2010-10-20', 'yyyy-mm-dd')); 3 commit; 4 end;
调用存储过程
存储过程建立完成后,只要通过授权,用户就可以在SQLPLUS 、ORACLE开发工具或第三方开发工具中来调用运行。ORACLE 使用EXECUTE 语句来实现对存储过程的调用:
EXEC[UTE] Procedure_name( parameter1, parameter2…);
例:查询指定员工记录
1 CREATE OR REPLACE PROCEDURE QueryEmp 2 (v_empno IN emp.empno%TYPE, 3 v_ename OUT emp.ename%TYPE, 4 v_sal OUT emp.sal%TYPE) 5 AS 6 BEGIN 7 SELECT ename, sal INTO v_ename, v_sal FROM emp WHERE empno=v_empno; 8 DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'的员工已经查到!'); 9 EXCEPTION 10 WHEN NO_DATA_FOUND THEN 11 DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!'); 12 WHEN OTHERS THEN 13 DBMS_OUTPUT.PUT_LINE('发生其它错误!'); 14 END QueryEmp;
调用方法:
DECLARE v1 emp.ename%TYPE; v2 emp.sal%TYPE; BEGIN QueryEmp(7788, v1, v2); DBMS_OUTPUT.PUT_LINE('姓名:'||v1); DBMS_OUTPUT.PUT_LINE('工资:'||v2); QueryEmp(7902, v1, v2); DBMS_OUTPUT.PUT_LINE('姓名:'||v1); DBMS_OUTPUT.PUT_LINE('工资:'||v2); QueryEmp(8899, v1, v2); DBMS_OUTPUT.PUT_LINE('姓名:'||v1); DBMS_OUTPUT.PUT_LINE('工资:'||v2); END;
例.计算指定部门的工资总和,并统计其中的职工数量。
1 CREATE OR REPLACE PROCEDURE proc_demo 2 (Dept_no NUMBER DEFAULT 10, 3 Sal_sum OUT NUMBER, 4 Emp_count OUT NUMBER) 5 IS 6 BEGIN 7 SELECT SUM(sal), COUNT(*) INTO sal_sum, emp_count 8 FROM emp WHERE deptno=dept_no; 9 EXCEPTION 10 WHEN NO_DATA_FOUND THEN 11 DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!'); 12 WHEN OTHERS THEN 13 DBMS_OUTPUT.PUT_LINE('发生其它错误!'); 14 END proc_demo;
调用方法:
1 DECLARE 2 V_num NUMBER; 3 V_sum NUMBER(8, 2); 4 BEGIN 5 Proc_demo(30, v_sum, v_num); 6 DBMS_OUTPUT.PUT_LINE('30号部门工资总和:'||v_sum||’,人数:’||v_num); 7 Proc_demo(sal_sum => v_sum, emp_count => v_num); 8 DBMS_OUTPUT.PUT_LINE('10号部门工资总和:'||v_sum||’,人数:’||v_num); 9 END;
在PL/SQL 程序中还可以在块内建立本地函数和过程,这些函数和过程不存储在数据库中,但可以在创建它们的PL/SQL 程序中被重复调用。本地函数和过程在PL/SQL 块的声明部分定义,它们的语法格式与存储函数和过程相同,但不能使用CREATE OR REPLACE 关键字。
例:建立本地过程,用于计算指定部门的工资总和,并统计其中的职工数量
1 DECLARE 2 V_num NUMBER; 3 V_sum NUMBER(8, 2); 4 PROCEDURE proc_demo 5 (Dept_no NUMBER DEFAULT 10, 6 Sal_sum OUT NUMBER, 7 Emp_count OUT NUMBER) 8 IS 9 BEGIN 10 SELECT SUM(sal), COUNT(*) INTO sal_sum, emp_count 11 FROM emp WHERE deptno=dept_no; 12 EXCEPTION 13 WHEN NO_DATA_FOUND THEN 14 DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!'); 15 WHEN OTHERS THEN 16 DBMS_OUTPUT.PUT_LINE('发生其它错误!'); 17 END proc_demo; 18 BEGIN 19 Proc_demo(30, v_sum, v_num); 20 DBMS_OUTPUT.PUT_LINE('30号部门工资总和:'||v_sum||’,人数:’||v_num); 21 Proc_demo(sal_sum => v_sum, emp_count => v_num); 22 DBMS_OUTPUT.PUT_LINE('10号部门工资总和:'||v_sum||’,人数:’||v_num); 23 END;
无参数存储过程的使用
CREATE OR REPLACE PROCEDURE 过程名 [(parameter,...)]
IS
定义变量
Begin
Plsql程序
End;
例:创建一个存储过程,用于向数据库中插入一条记录。
第一步:创建
1 CREATE OR REPLACE PROCEDURE pro_1 2 IS 3 Begin 4 insert into person values (11,'aa','aav'); 5 End;
第二步:在sql*plus中执行该过程
exec pro_1;
第三步:通过JDBC使用存储过程
private Connection conn = null;
private ResultSet rs = null;
private CallableStatement state = null;
//调用一个无参数的存储过程
public void testPro()
{
conn = Tools.getConnection();
try {
state = conn.prepareCall("{call pro_1}");
state.execute();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
4 带有IN类型参数的存储过程的使用。
例:创建一个存储过程,用于向数据库中插入一条记录。
第一步:创建
1 CREATE OR REPLACE PROCEDURE pro_2(id number,name varchar2,email varchar2) 2 IS 3 Begin 4 insert into person values (id,name,email); 5 End;
第二步:在sql*plus中执行该过程
1 exec pro_2(12,'aaa','aaa');
第三步:通过JDBC使用存储过程。
//使用一个带有 IN 类型参数的存储过程
1 public void testPro_in(int id,String name,String email) 2 { 3 conn = Tools.getConnection(); 4 try { 5 state = conn.prepareCall("{call pro_2(?,?,?)}"); 6 state.setLong(1, id); 7 state.setString(2, name); 8 state.setString(3, email); 9 state.execute(); 10 } catch (Exception e) { 11 // TODO Auto-generated catch block 12 e.printStackTrace(); 13 } 14 }
5 带有out类型参数的存储过程的使用。
例:创建一个存储过程,用于返回数据库中的Person表的总行数。
第一步:创建
1 CREATE OR REPLACE PROCEDURE pro_3(num out number) 2 IS 3 mynum number; 4 Begin 5 select count(*) into mynum from person; 6 num := mynum; 7 End;
或者
1 CREATE OR REPLACE PROCEDURE pro_3(num out number) 2 IS 3 Begin 4 select count(*) into num from person; 5 End;
第二步:在sql*plus中执行该过程
declare
a number;
begin
pro_3(a);
dbms_output.put_line(a);
end;
第三步:通过JDBC使用存储过程。
public void testPro_out()
{
conn = Tools.getConnection();
try {
state = conn.prepareCall("{call pro_3(?)}");
state.registerOutParameter(1, Types.NUMERIC);
state.execute();
int num = state.getInt(1);
System.out.println(num);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
6 带有in-out类型参数的存储过程的使用。
创建:
CREATE OR REPLACE PROCEDURE pro_4(num in out number)
IS
a number := 100;
Begin
num := a*num;
End;
在sql*plus中执行该过程
declare
a number := 12;
begin
pro_4(a);
dbms_output.put_line(a);
end;