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

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

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

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

4、创建存储过程语法：

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

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

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

—查看当前用户创建好的所有存储过程,t.object_type = ‘FUNCTION’：存储函数、TRIGGER：触发器
select from user_procedures t where t.object_type = ‘PROCEDURE’ ;
—查看所有用户创建好的所有存储过程,t.object_type = ‘FUNCTION’：存储函数、TRIGGER：触发器
select from all_procedures t where t.object_type = ‘PROCEDURE’ ;

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;

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、创建存储函数语法：

create [or replace] function 存储函数名(参数名1 in/out 参数类型,参数名2 in/out 参数类型,…) return 数据类型
    is/as
    声明部分
    begin
    —业务逻辑处理
   return 结果变量;
    end;

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;

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

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

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

—查看当前用户创建好的所有存储函数,t.object_type = ‘PROCEDURE’：存储过程、TRIGGER：触发器
select from user_procedures t where t.object_type = ‘FUNCTION’ ;
—查看所有用户创建好的所有存储函数，t.object_type = ‘PROCEDURE’：存储过程、TRIGGER：触发器
select from all_procedures t where t.object_type = ‘FUNCTION’ ;

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

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

存储过程 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 驱动包。

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();//释放资源
    }
}