Oracle 存储过程、存储函数与 JDBC 调用

绝地灬酷狼 2022-01-23 00:35 437阅读 0赞

**目录**

存储过程 创建、删除、查看

存储函数 创建、删除、查询

引用游标读取存储过程返回的结果集

存储过程 VS 存储函数

JDBC 调用存储过程/函数

--------------------

[~~~ 准备员工表与部门表测试数据][Link 1]

# 存储过程 创建、删除、查看 #

1、存储过程是自定义的且封装在数据库服务器上的 PLSQL 代码片段，它已经编译好了，可以像调用数据库函数一样来调用存储过程，可以给它传递参数，它也可以有返回值。

2、因为存储过程是编译好放在数据库服务器上的代码段，所以执行效率很高，这也是必须掌握的技能。

3、Oralce 的 PLSQL 因为有判断、循环等操作，就像是脚本语言一样，很多业务 java 代码中的逻辑都可以放在数据库这边进行处理，游标、存储过程就是典型的操作。

4、创建存储过程语法：

<table> 
 <tbody> 
  <tr> 
   <td>--[or replace] 表示如果已经存在，则替换更新。in 表示传递进去的参数（可以省略），out 表示存储过程返回的参数。<br> -- is /as ：关键字，任意一个都可以，没有区别<br> create [or repalce] procedure 存储过程名(参数名1 in/out 参数类型,参数名2 in/out 参数类型,...)<br> is / as<br> &nbsp;&nbsp; &nbsp;--变量声明部分<br> begin<br> &nbsp;&nbsp; &nbsp;--业务逻辑处理<br> [exception<br> &nbsp;&nbsp; &nbsp;--异常处理部分(可省略不写) ]<br> end;</td> 
  </tr> 
 </tbody> 
</table>

5、删除存储过程：drop procedure 存储过程名;

6、查看创建好的存储过程：

<table> 
 <tbody> 
  <tr> 
   <td>--查看当前用户创建好的所有存储过程,t.object_type = 'FUNCTION'：存储函数、TRIGGER：触发器<br> select * from user_procedures t where t.object_type = 'PROCEDURE' ;<br> --查看所有用户创建好的所有存储过程,t.object_type = 'FUNCTION'：存储函数、TRIGGER：触发器<br> select * from all_procedures t where t.object_type = 'PROCEDURE' ;</td> 
  </tr> 
 </tbody> 
</table>

select * from emp;--查收所有员工
    
    --创建存储过程 给指定 empno 的员工加薪 sal，并输出/打印加薪前后的薪水
    create or replace procedure proc_update_emp_sal_by_empno(vempno in number,vnum in number)
    is
    	vsal number;--声明变量，记录当前工资.
    begin
    	select sal into vsal from emp where empno = vempno;--查询员工当前的薪水,并赋值(into)给 vsal 变量
    	dbms_output.put_line(vempno||' 加薪前为 '|| vsal);--打印加薪前薪水
    	update emp set sal = sal + vnum where empno = vempno;--更新员工薪水
    	dbms_output.put_line(vempno||' 加薪前为 '|| (vsal+vnum));--打印加薪后薪水
    	commit;--提交事务
    end;
     
    --存储过程本身自己是不执行其中的脚本的，它只是编译到了数据库中，需要调用执行
    --调用存储过程 方式1
    call proc_update_emp_sal_by_empno(7499,1000);
    --调用存储过程 方式2
    declare
    begin
    	proc_update_emp_sal_by_empno(7499,-100);
    end;

![20190601152325774.gif][]

select * from emp;--查询所有员工数据
    
    --查询指定员工(empno) 的年薪，使用存储过程实现
    create or replace procedure proc_get12sal_by_empno(vempno in emp.empno%type,v12sal out emp.sal%type) is
    begin
      select sal * 12 + nvl(comm,0) into v12sal from emp where empno = vempno;
    end;
    
    --调用存储过程 proc_get12sal_by_empno
    declare
      v12sal emp.sal%type;
    begin
      proc_get12sal_by_empno(7788,v12sal);
      dbms_output.put_line('年薪：'||v12sal);
    end;

# 存储函数 创建、删除、查询 #

1、存储函数与存储过程一样，都是封装好的 PLSQL 代码片段，编译好后放在 Oralce 服务器中。

2、创建存储函数语法：

<table> 
 <tbody> 
  <tr> 
   <td>&nbsp;create [or replace] <span style="color:#f33b45;">function </span>存储函数名(参数名1 in/out 参数类型,参数名2 in/out 参数类型,...) <span style="color:#f33b45;">return 数据类型</span><br> &nbsp;&nbsp;&nbsp;&nbsp;is/as<br> &nbsp;&nbsp;&nbsp;&nbsp;声明部分<br> &nbsp;&nbsp;&nbsp;&nbsp;begin<br> &nbsp;&nbsp;&nbsp;&nbsp;--业务逻辑处理<br><span style="color:#f33b45;">&nbsp;&nbsp; &nbsp;return 结果变量; </span><br> &nbsp;&nbsp;&nbsp;&nbsp;end;</td> 
  </tr> 
 </tbody> 
</table>

3、drop function 函数名; --删除存储函数

select * from emp;--查收所有员工
    
    --使用存储函数 查询指定员工的年薪
    create or replace function func_get12sal(vempno emp.empno%type) return number is
    v12sal emp.sal%type;--声明变量，类型为 emp 表中 sal 字段的类型
    begin
      --查询指定员工的薪水乘以12个月然后加上它的奖金，因为有些员工奖金为空(null)，null不能做四则运算，所以使用 nvl 函数处理
      --将结果赋值（into）给 v12sal 变量
      select sal*12+nvl(comm,0) into v12sal from emp where empno = vempno;
      return v12sal;--返回存储函数的结果
    end;
    
    --调用存储函数 方式1
    declare
    v12sal emp.sal%type;--定义变量，类型为 emp 表中的 sal 字段类型
    begin
      v12sal := func_get12sal(7521);--调用编译好的存储函数
      dbms_output.put_line('年薪：'|| v12sal);--打印结果
    end;
    --调用存储函数 方式2。查看所有员工的姓名、工作岗位、年薪
    select ename,job,func_get12sal(empno) from emp;

![20190602091706133.gif][]

注意：如果执行报错说精度超过了限制，则可以看检查是否是 sal \* 12 之后长度超过了 number 约束的长度，如果超过了可以修改它：alert table emp modify sal number(11,2);--修改 sal 字段的长度

3、存储过程与存储函数本质上没有区别，存储过程内部可以调用存储函数。

4、查询创建好的存储函数：

<table> 
 <tbody> 
  <tr> 
   <td>--查看当前用户创建好的所有存储函数,t.object_type = 'PROCEDURE'：存储过程、TRIGGER：触发器<br> select * from user_procedures t where t.object_type = 'FUNCTION' ;<br> --查看所有用户创建好的所有存储函数，t.object_type = 'PROCEDURE'：存储过程、TRIGGER：触发器<br> select * from all_procedures t where t.object_type = 'FUNCTION' ;</td> 
  </tr> 
 </tbody> 
</table>

示例2：[自定义基础库身份证解密函数.sql][.sql]

# [引用游标读取存储过程返回的结果集][Link 2] #

# 存储过程 VS 存储函数 #

1、存储函数可以使用 return 指定返回值，存储过程不能通过 return 返回。但是过程和函数都可以使用 out 参数指定一个或者多个输出参数，可以利用 out 参数在过程和函数中实现返回多个值。

2、存储过程中可以调用存储函数，存储函数中无法调用存储过程

3、存储函数可以在 sql 语句中直接调用，存储过程中无法在 sql 中直接调用

4、单纯就实现的功能来说，存储过程和存储函数是一样的。

# JDBC 调用存储过程/函数 #

1、本人环境为笔记本上安装了 Oralce 12C。这里将新建一个简单的 Java SE 应用来调用 Oracle 中创建好的存储过程与存储函数。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdteDE5OTMzMjg_size_16_color_FFFFFF_t_70][]

2、Java 代码调用时，必须先导入 Oracle 驱动包。

![20190602112500384.png][]

3、调用代码如下：

import oracle.jdbc.driver.OracleTypes;
    import java.sql.CallableStatement;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.util.logging.Logger;
    public class MainApp {
        public static void main(String[] args) throws Exception {
            new MainApp().invokeProcedure();
            new MainApp().invokeFunction();
        }
        /**
         * JDBC 调用存储过程
         * 已经在 Oracle 中创建好了存储过程如下（获取指定员工(empno) 的年薪）：
         * create or replace procedure proc_get12sal_by_empno(vempno in emp.empno%type,v12sal out emp.sal%type) is
         * begin
         * select sal * 12 + nvl(comm,0) into v12sal from emp where empno = vempno;
         * end;
         *
         * @throws Exception
         */
        public void invokeProcedure() throws Exception {
            Logger logger = Logger.getAnonymousLogger();//日志记录器
            Class.forName("oracle.jdbc.driver.OracleDriver");//1、注册驱动
            String url;
            url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
            String username = "c##wmx";
            String password = "123456";
            Connection conn = DriverManager.getConnection(url, username, password);//2、获取连接
            /**
             * sql 为固定格式，proc_get12sal_by_empno 为存储过程名，"?" 为占位符
             * 存储过程 sql 格式为：{call <procedure-name>[(<arg1>,<arg2>, ...)]}，arg1、arg2...参数使用占位符
             */
            String sql = "{call proc_get12sal_by_empno(?,?)}";
            CallableStatement callableStatement = conn.prepareCall(sql);
            callableStatement.setInt(1, 7788);设置输入参数，占位符从1开始，设置员工编号
            callableStatement.registerOutParameter(2, OracleTypes.NUMBER);//注册输出参数类型
            callableStatement.execute();//执行statement
            int sal12Total = callableStatement.getInt(2);//获取执行结果，第二个占位符为输出参数
            logger.info("调用存储过程，年薪=" + sal12Total);
            callableStatement.close();//释放资源
            conn.close();//释放资源
        }
    
        /**
         * 调用存储函数，Oracle 数据库中已经创建好了存储函数如下：
         * create or replace function func_get12sal(vempno emp.empno%type) return number is
         * v12sal emp.sal%type;--声明变量，类型为 emp 表中 sal 字段的类型
         * begin
         * --查询指定员工的薪水乘以12个月然后加上它的奖金，因为有些员工奖金为空(null)，null不能做四则运算，所以使用 nvl 函数处理
         * --将结果赋值（into）给 v12sal 变量
         * select sal*12+nvl(comm,0) into v12sal from emp where empno = vempno;
         * return v12sal;--返回存储函数的结果
         * end;
         *
         * @throws Exception
         */
        public void invokeFunction() throws Exception {
            Logger logger = Logger.getAnonymousLogger();//日志记录器
            Class.forName("oracle.jdbc.driver.OracleDriver");//注册驱动
            String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
            String username = "c##wmx";
            String password = "123456";
            Connection conn = DriverManager.getConnection(url, username, password);//2.获取连接
            /**
             * 存储函数使用 return 返回参数时，则 sql 字符串固定格式为： {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
             * 如果存储函数不是使用 return 返回参数，而是像存储过程一样使用 out 返回，则使用：{call <procedure-name>[(<arg1>,<arg2>, ...)]}
             */
            String sql = "{?= call func_get12sal(?)}";
            CallableStatement statement = conn.prepareCall(sql);
            statement.registerOutParameter(1, OracleTypes.NUMBER);//注册返回类型参数
            statement.setInt(2, 7788);//设置第二个参数，输入参数
            statement.execute();//执行SQL
            int sal12Total = statement.getInt(1);//获取结果
            logger.info("调用存储函数，年薪=" + sal12Total);
            statement.close();//释放资源
            conn.close();//释放资源
        }
    }

4、Oracle 12C 运行时会报错：Exception in thread "main" java.sql.SQLException: ORA-28040: No matching authentication protocol

此时需要修改 Oracle 安装目录下的一个 E:\\app\\wmx\\product\\12.1.0\\dbhome\_1\\NETWORK\\ADMIN\\sqlnet.ora 文件，在末尾添加一行：SQLNET.ALLOWED\_LOGON\_VERSION=8

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdteDE5OTMzMjg_size_16_color_FFFFFF_t_70 1][]

5、Oracle 12c 修改文件后运行即可成功：

![20190602113149341.gif][]

[Link 1]: https://wangmaoxiong.blog.csdn.net/article/details/90581383#%E5%87%86%E5%A4%87%E5%91%98%E5%B7%A5%E8%A1%A8%E4%B8%8E%E9%83%A8%E9%97%A8%E8%A1%A8%E6%B5%8B%E8%AF%95%E6%95%B0%E6%8D%AE
[20190601152325774.gif]: /images/20220123/7a27d4f6aa3544e598a2e02eedc722b3.png
[20190602091706133.gif]: /images/20220123/0e6f57f02a884cc48c33ef7681d9884b.png
[.sql]: https://gitee.com/wangmx1993/material/blob/master/sql/oracle/%E8%87%AA%E5%AE%9A%E4%B9%89%E5%9F%BA%E7%A1%80%E5%BA%93%E8%BA%AB%E4%BB%BD%E8%AF%81%E8%A7%A3%E5%AF%86%E5%87%BD%E6%95%B0.sql
[Link 2]: https://wangmaoxiong.blog.csdn.net/article/details/90702872#%E5%BC%95%E7%94%A8%E6%B8%B8%E6%A0%87%E8%AF%BB%E5%8F%96%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B%E8%BF%94%E5%9B%9E%E7%9A%84%E7%BB%93%E6%9E%9C%E9%9B%86
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdteDE5OTMzMjg_size_16_color_FFFFFF_t_70]: /images/20220123/b6890a658627497d9cb516bede271cd2.png
[20190602112500384.png]: /images/20220123/7767d7b764324bf0842bdc4922c629db.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdteDE5OTMzMjg_size_16_color_FFFFFF_t_70 1]: /images/20220123/c4f6441825f84e55bc9ef0bc92db0260.png
[20190602113149341.gif]: /images/20220123/f90039e32421435f9ce20310bfe95f1c.png