oracle procedure 存储过程

一

存储过程用于执行特定的操作，当建立存储过程时，既可以指定输入参数(in)，也可以指定输出参数(out)，

通过在过程中使用输入参数，可以将数据传递到执行部分

通过使用输出参数，可以将执行部分的数据传递到应用环境

在sqlplus中可以使用create procedure命令来建立过程

 如：

create or replace procedure insert_emp_proce( v_id in number, v_name in varchar2) is

begin

  update test set name=v_name where id = v_id;

  dbms_output.put_line('update successful!');

end;

/

--dbms_output.put_line('update successful!');  可以在控制台输出一句话

二

存储过程

CREATE OR REPLACE PROCEDURE 存储过程名

　　 IS

　　 BEGIN

　　   NULL;

   END;

/

行1:CREATE OR REPLACE PROCEDURE 是一个SQL语句通知Oracle数据库去创建一个叫做skeleton存储过程, 如果存在就覆盖它;

行2:IS关键词表明后面将跟随一个PL/SQL体。

行3:BEGIN关键词表明PL/SQL体的开始。

行4:NULL PL/SQL语句表明什么事都不做，这句不能删去，因为PL/SQL体中至少需要有一句;

行5:END关键词表明PL/SQL体的结束

 

实例如下：

1.请考虑编写一个存储过程，可以输入雇员名，新工资，用来修改雇员的工资

--根据雇员名去修改工资

CREATE PROCEDURE sp_update(uname VARCHAR2, newsal NUMBER) IS

BEGIN

   update emp set sal=newsal where ename=uname;

END;

/

或：注意参数

create or replace procedure update_emp_sal_proc (v_id in emp.empno%type,v_sal in emp.sal%type) is
          begin
            update emp set sal = v_sal  where empno = v_id;
          end;
         /
in表示输入参数
%type是常用的声明参数数据类型的方法

 

调用：

SQL> execute sp_update('SMITH',4000);

java 代码调用 ：注意此时没有返回值

package junit.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
/**
 * 演示java程序调用oracle的存储过程案例
 */
public class ProcedureTest {
    public static void main(String[] args) {
        try {
            // 1.加载驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            // 2.得到连接
            Connection ct = DriverManager.getConnection( "jdbc:oracle:thin:@127.0.0.1:1521:orcl4372", "scott", "m123");
            // 3.创建CallableStatement
            CallableStatement cs = ct.prepareCall("{call sp_update(?,?)}");
            // 4.给?赋值
            cs.setString(1, "SMITH");
            cs.setInt(2, 4444);
            // 5.执行
            cs.execute();
            // 关闭
            cs.close();
            ct.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

 

2 编写存储过程，显示所指定雇员名所在的部门名和位置

create or replace procedure print_info_pro
( v_id in emp.empno%type ,v_deptno out nocopy emp.deptno%type ,v_loc out nocopy dept.loc%type )is
begin 
select e.deptno, d.loc into v_deptno, v_loc from emp e, dept d where e.empno = v_id and e.deptno = d.deptno;
end;
/

注意：

1 select e.deptno, d.loc into v_deptno, v_loc    select ... into ...的使用

2 此时是有返回值的（值并非列表）：

Java 调用

 public  void  testOutProce()
    {

        Connection ct = OracleConnect.getOracleConnection(); //获取数据库连接
        CallableStatement cs = null;  
        try {
            cs = ct.prepareCall("{call print_info_pro(?,?,?)}");
            
            cs.setInt(1, 7369);
            
            cs.registerOutParameter(2, Types.VARCHAR);//输出参数是要注册的!!!
            cs.registerOutParameter(3, Types.VARCHAR);
            
            cs.execute();
            
            String deptno = cs.getString(2);
            String loc    = cs.getString(3);
            System.out.println(deptno+" "+loc);
            
        } catch (Exception e) {
           e.printStackTrace();
        }finally
        {
            OracleConnect.close(null, cs, ct);//关闭数据库资源
        }
        
    }

 

3 如何处理返回一个列表

返回emp表的全部记录

（1）定义包 和 游标

create or replace package list_package as
   type list_cursor is ref cursor;
end list_package;

（2）

create or replace procedure test_crosor (v_cursor out list_package.list_cursor) is
begin
  open v_cursor for select * from emp where empno > 6000;
end;

(3)java 调用的部分代码

    Connection ct = OracleConnect.getOracleConnection();
        CallableStatement cs = null;
        ResultSet rs = null;
        try {
            cs = ct.prepareCall("{call  test_crosor(?)}");
            cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);//注册输出参数 类型是游标
            cs.execute();
            rs = (ResultSet) cs.getObject(1);
            while(rs.next()){
                System.out.println("empno："+rs.getString(1));
                System.out.println("ename："+rs.getString(2));
                System.out.println("job  ："+rs.getString(3));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally
        {
            OracleConnect.close(rs, cs, ct);
        }

 

三 更复杂的procedure

存储过程创建语法：

create or replace procedure 存储过程名（param1 in type，param2 out type） 

as 

变量1 类型（值范围）;   --vs_msg   VARCHAR2(4000); 

变量2 类型（值范围）;  -- v_ename emp.ename%type;

Begin

Select count(*) into 变量1 from 表A where列名=param1；

 

    If (判断条件) then

       Select 列名 into 变量2 from 表A where列名=param1；

       Dbms_output.Put_line(‘打印信息’);

    Elsif (判断条件) then

       Dbms_output.Put_line(‘打印信息’);

    Else

       Raise 异常名（NO_DATA_FOUND）;

    End if;

Exception

    When others then Rollback;

End;

 

 

注意事项：

1， 存储过程参数不带取值范围，in表示传入，out表示输出  类型可以使用任意Oracle中的合法类型。

2，  变量带取值范围，后面接分号

3，  在判断语句前最好先用count（*）函数判断是否存在该条操作记录

4，  用select 。。。into。。。给变量赋值

5，  在代码中抛异常用 raise+异常名

 

 

还请多指教