存储过程与PL/SQL语言

PL/SQL 语言

2.1 概念作用

---

之前我们编写的PLSQL程序可以进行表的操作,判断,循环逻辑处理的工作,但无法重复调用.

可以理解之前的代码全都编写在了main方法中,是匿名程序. JAVA可以通过封装对象和方法来解决复用问题

PLSQL是将一个个PLSQL的业务处理过程存储起来进行复用,这些被存储起来的PLSQL程序称之为存储过程

存储过程作用：

1， 在开发程序中，为了一个特定的业务功能，会向数据库进行多次连接关闭(连接和关闭是很耗费资源), 需要对数据库进行多次I/O读写，性能比较低。如果把这些业务放到PLSQL中，在应用程序中只需要调用PLSQL就可以做到连接关闭一次数据库就可以实现我们的业务,可以大大提高效率.

2， ORACLE官方给的建议：能够让数据库操作的不要放在程序中。在数据库中实现基本上不会出现错误，在程序中操作可能会存在错误.(如果在数据库中操作数据,可以有一定的日志恢复等功能.)

2.2 语法

---

CREATEORREPLACEPROCEDURE 过程名称[(参数列表)] IS
BEGIN
​
END [过程名称];
根据参数的类型，我们将其分为3类讲解：
l 不带参数的
l 带输入参数的
l 带输入输出参数(返回值)的。

2.3 无参存储

---

2.3.1 创建存储

通过Plsql Developer或者语句创建存储过程:

【示例】 通过调用存储过程打印hello world

创建存储过程:

--通过调用存储过程打印hello world
-- 过程中没有declare关键字，declare用在语句块中
CREATEORREPLACEPROCEDURE P_HELLO IS/as   -- is和as是可以互用的，用哪个都没关系的
​
BEGIN
​
  DBMS_OUTPUT.PUT_LINE('hello world');
​
END P_HELLO;

2.3.2 调用存储过程

1. 通过PLSQL程序调用：

BEGIN
--直接输入调用存储过程的名称
  P_HELLO;
​
END P_HELLO;

提示:SQLPLUS中显示结果的前提是需要 set serveroutput on

2.4 带输入参数的存储过程

---

【示例】查询并打印某个员工（如7839号员工）的姓名和薪水--存储过程：要求，调用的时候传入员工编号，自动控制台打印。

--查询并打印某个员工（如7839号员工）的姓名和薪水
--要求，调用的时候传入员工编号，自动控制台打印。
CREATEORREPLACEPROCEDURE P_QUERYNAMEANDSAL(I_EMPNO IN EMP.EMPNO%TYPE) IS
  --声明变量接受查询结果
  V_ENAME EMP.ENAME%TYPE;
  V_SAL   EMP.SAL%TYPE;
​
BEGIN
​
  --根据用户传递的员工号查询姓名和薪水
  SELECT ENAME, SAL INTO V_ENAME, V_SAL FROM EMP WHERE EMPNO = I_EMPNO;
​
  --打印结果
  DBMS_OUTPUT.PUT_LINE('姓名:' || V_ENAME || ',薪水:' || V_SAL);
​
END P_QUERYNAMEANDSAL;

命令调用：

SQL>execp_querynameandsal(7839);

姓名:KING,薪水:5000

​

PL/SQL过程已成功完成。

PLSQL程序调用：

BEGIN
​
  P_QUERYNAMEANDSAL(7839);
​
END;

2.5 带输出参数的存储过程

---

【示例】输入员工号查询某个员工（7839号员工）信息，要求，将薪水作为返回值输出，给调用的程序使用。

--输入员工号查询某个员工（7839号员工）信息，要求，将薪水作为返回值输出，给调用的程序使用。
CREATEORREPLACEPROCEDURE P_QUERYSAL_OUT(I_EMPNO IN EMP.EMPNO%TYPE,O_SAL OUT EMP.SAL%TYPE) IS
​
BEGIN
​
  SELECT SAL INTO O_SAL FROM EMP WHERE EMPNO = I_EMPNO;
​
END P_QUERYSAL_OUT;

PLSQL程序调用：

DECLARE
  --声明一个变量接受存储过程的输出参数
  V_SAL EMP.SAL%TYPE;
​
BEGIN
​
  P_QUERYSAL_OUT(7839, V_SAL); --注意参数的顺序
​
  DBMS_OUTPUT.PUT_LINE(V_SAL);
​
END; 

注意:调用的时候,参数要与定义的参数的顺序和类型一致.

2.6 JAVA程序调用存储过程

---

需求:如果一条语句无法实现结果集, 比如需要多表查询,或者需要复杂逻辑查询,我们可以选择调用存储查询出你的结果.

2.6.1 分析jdk API

通过Connection对象的prepareCall方法可以调用存储过程

得出结论: 通过Connection对象调用prepareCall方法传递一个转义sql语句调用存储过程, 输入参数直接调用set方法传递.输出参数需要注册后,执行存储过程,通过get方法获取.参数列表的下标是从1开始的

2.6.2 实现代码

准备环境：

l 导入Oracle的jar包

【示例】通过员工号查询员工的姓名和薪资

packagecn.itcast.oracle.jdbc;
​
importoracle.jdbc.OracleTypes;
importjava.sql.CallableStatement;
importjava.sql.Connection;
importjava.sql.DriverManager;
​
publicclassProcedureTest {
    publicstaticvoidmain(String[] args) throwsException {
        //1.加载驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
​
        //2.获得连接对象
​
        //2.1 设置连接字符串
        Stringurl="jdbc:oracle:thin:@localhost:1521:xe" ;
​
        Stringname="scott";
​
        Stringpassword="tiger";
​
        Connectionconn=DriverManager.getConnection(url, name, password);
​
​
        //3.获取语句对象
        Stringsql="{call p_querysal_out(?,?)}";//转义语法,{call  存储过程(参数列表)}
        CallableStatementcall=conn.prepareCall(sql);
​
        //4.设置输入参数
        call.setInt(1,7839);
​
        //5.注册输出参数
        call.registerOutParameter(2, OracleTypes.DOUBLE);
​
        //6.执行存储过程
        call.execute();
​
        //7.获取输出参数
        doublesal=call.getDouble(2);
        System.out.println("薪水:"+sal);
​
        //8.释放资源
        call.close();
        conn.close();
​
    }
}