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