Oracle存储过程和存储函数的笔记

为什么要学习存储过程和存储函数:

在各种应用程序中,对数据库操作是耗时的,特别是复杂的操作,比如对一个数据的查询修改更新,都是需要耗时的,存储过程和存储函数就是为了解决这个问题,在oracle中创建存储过程和存储函数,将查询,修改,更新的逻辑写入oracle的存储过程和函数中,在应用程序中使用jdbc直接调用oracle的存储过程和函数,让oracle内部处理这些业务逻辑,大大提升程序效率;

存储过程:

1.定义
　　存储过程是存储在数据库中提供所有用户程序调用的子程序，定义存储过程的关键字为procedure。
2.创建:
create or replace PROCEDURE reque(empn in number,psal out number ,pjob out varchar2)
as
[声明变量写这里]
begin
select sal,job into psal,pjob from emp where empno=empn;
end;
/
上述代码的含义为:创建一个名字为reque的存储过程,共有3个参数,分别是输入参数(关键字in)empn类型为nunber,输出参数(关键字out)psal类型为number,输出参数pjob类型为varchar2;
备注: in表示输入参数,用途是将参数传入主程序中(例如empno=empn)
out表示输出参数,用途是接收主程序中的结果;
3.PL/SQL中调用:
set SERVEROUTPUT ON
DECLARE
psal emp.sal%type;
pjob emp.job%type;
begin
reque(7839,psal,pjob);
DBMS_OUTPUT.PUT_LINE(psal);
DBMS_OUTPUT.PUT_LINE(pjob);
end;
/

存储函数

1.定义:存储函数是存储在数据库中提供所有用户程序调用的子程序，定义存储函数的关键字为function。和存储过程不同的是,存储函数有返回值,用return表示

2.创建:
create or replace function query(EMPN in number)
return number
as
psal emp.sal%type;
begin
select sal into psal from emp where EMPNO=EMPN;
return psal;
end;
/

3.PL/SQL中调用:
set SERVEROUTPUT ON
DECLARE
psal emp.sal%type;
begin
psal:=query(7839);
DBMS_OUTPUT.PUT_LINE(psal);
end;
/

在java程序中调用oracle存储过程和存储函数:

在java中调用oracle需要用到jdbc,所以需要将oracle提供的jar包添加到java程序中,
oracle提供的jar包的位置在D:\app\H-209-FX-XY\virtual\product\12.2.0\dbhome_1\jdbc\lib
这是我电脑的 oracle安装的目录,jar有多个,随便选一个ojdbc包放入java程序lib的文件夹下,然后add一下,
创建JDBCUtils工具类,并测试testProcedure()方法和testFruntion()方法:

public class JDBCUtils {
    //驱动类
    private static String driver = "oracle.jdbc.OracleDriver";
    //oracle 数据库的url格式为: jdbc:oracle:thin:@域名:端口:数据库名
    private static String url  = "jdbc:oracle:thin:@localhost:1521:orcl";
    //用户名
    private static String user = "c##scott";
    //密码
    private static String password = "tiger";

    /**注册数据库的驱动*/
    static{
        try {
            Class.forName(driver);//反射
        } catch (ClassNotFoundException e) {
            throw  new ExceptionInInitializerError(e);
        }
    }

    /**获取数据连接*/
    public static Connection getConnection(){
        try {
            return DriverManager.getConnection(url,user,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**释放资源*/
    public static void release(Connection conn, Statement statement, ResultSet resultSet){
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                conn = null;
            }
        } if(statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                statement = null;
            }
        } if(resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                resultSet = null;
            }
        }
    }
}

public void testProcedure(){
        Connection conn = JDBCUtils.getConnection();
        CallableStatement call = null;
        //sql语句格式:{call <procedure-name>[(<arg1>,<arg2>...)]}
        String sql =  "{call reque(?,?,?)}";
        try {
            call = conn.prepareCall(sql);
            call.setInt(1,7839);//设置第一个?(代表参数,下标从1开始)
            //注册out输出参数(下标,类型)
            call.registerOutParameter(2,OracleType.NUMBER);
            call.registerOutParameter(3,OracleType.VARCHAR2);
            call.execute();//执行sql语句

            String sal = call.getString(2);
            String job = call.getString(3);
            System.out.print("sal=" + sal + ",job=" + job);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(conn,call,null);
        }
    }

public void testFruntion(){
        Connection conn = JDBCUtils.getConnection();
        CallableStatement call = null;
        //sql语句格式:{? = call <function-name>[(<arg1>,<arg2>...)]} 
        String sql = "{? = call query(?)}";
        try {
            call = conn.prepareCall(sql);
            call.registerOutParameter(1, OracleType.NUMBER);
            call.setInt(2,7839);
            call.execute();
            int sal =  call.getInt(1);
            System.out.println("sal = " + sal);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(conn,call,null);
        }
    }

程序包:

1.定义:包是一组相关过程、函数、变量、游标、常量等PL/SQL程序设计元素的组合。它具有面向对象程序设计语言的特点，是对这些PL/SQL程序设计元素的封装。包类似于C++或Java程序中的类，而变量相当于类中的成员变量，过程和函数相当于方法，把相关的模块归类成为包，可使开发人员利用面向对象的方法进行存储过程的开发，从而提高系统性能。与类相同，包中的程序元素也分为公用元素和私有元素两种，这两种元素的区别是他们允许访问的程序范围不同，即他们的作用域不同。公用元素不仅可以被包中的函数、过程调用，也可以被包外的PL/SQl块调用。而私有元素只能被该包内部的函数或过程调用。
2、使用程序包的优点：在PL/SQL设计中，使用包不仅可以使程序模块化，对外隐藏包内所使用的信息，而写程序包可以提高程序的运行效率。因为，当程序首次调用程序包内部的函数或过程时，Oracle将整个程序包调入内存，当再次调用程序包中的元素时，oracle直接从内存中读取，而不需要进行磁盘的IO操作，从而使程序的执行效率提高。
3、一个程序包分为两部分组成：
(1)、包定义：包定义部分声明包内数据类型、变量、常量、游标、子程序和函数等元素，这些元素为包的共有元素。
(2)、包主体：包主题则定义了包定义部分的具体实现，在包主体中还可以声明和实现私有元素。

程序包的简单写法:(案例:查询某个部门中所有员工的所有信息)
1.创建一个程序包

2.将程序包命名为mypackage

3.创建好以后oracle会自动帮我们实现包定义的简要代码块,自己写入定义的代码块,然后保存,我们只声明了存储过程queryEmpList,但是没有实现,接下来去创建包主体,然后实现存储过程queryEmpList

CREATE OR REPLACE 
PACKAGE MYPACKAGE AS 
--定义代码块
    --这句话的意思是定义一个类型为empcursor,他引用cursor的类型(empcursor为cursor类型)
    type empcursor is ref cursor;
    procedure queryEmpList(dno in number,empList out empcursor);
--
END MYPACKAGE;

ctrl + s 保存
4.创建包主体

程序自动帮我们生成简要代码

CREATE OR REPLACE
PACKAGE BODY MYPACKAGE AS

  procedure queryEmpList(dno in number,empList out empcursor) AS
  BEGIN
    --打开光标,给光标赋值
    open empList for select * from emp where DEPTNO=dno;
  END queryEmpList;

END MYPACKAGE;

这样就查询到了员工的所有信息

在java应用程序中访问程序包

 public void testPackage(){
        Connection conn = null;
        CallableStatement call = null;
        ResultSet resultSet = null; //结果集
        //{call <function-name>[(<arg1>,<arg2>...)]}
        //注意要加上包名
        String sql =  "{call mypackage.queryEmpList(?,?)}";
        try {
            conn = JDBCUtils.getConnection();
            call = conn.prepareCall(sql);
            call.setInt(1,10);//部门号为10的员工
            call.registerOutParameter(2, OracleTypes.CURSOR);
            call.execute();
            resultSet =  ((OracleCallableStatement)call).getCursor(2);
            while (resultSet.next()){
                //取出该员工的员工号,姓名,薪水和职位
                int empno = resultSet.getInt("empno");
                String name = resultSet.getString("ename");
                double sal = resultSet.getDouble("sal");
                String job = resultSet.getString("job");
                System.out.println(empno + "   " + name + "   " + sal + "    " + job);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(conn,call,resultSet);
        }
    }

上述代码中resultSet = ((OracleCallableStatement)call).getCursor(2);
将通用接口CallableStatement强转为OracleCallableStatement类型(Oracle接口),是因为,程序中要用到oracle中的cursor,只有这样才能获取到oracle的cursor结果集;