plsql、游标、存储过程
以下为个人学习笔记
plsql语言不区分大小写。
1.程序结构：
Plsql分为三个部分：声明部分、可执行部分、异常处理部分 。语法：
declare --声明变量，游标 i integer; begin --执行语句 --异常处理 end;
示例：打印hello world（在测试窗口编写）
begin -- 打印hello world DBMS_OUTPUT.PUT_LINE('hello world'); end;
（在命令窗口编写）“set serveroutput on“开启输出命令
Sqlplus中执行plsql程序需要在程序最后添加/，表示程序的结束。
SQL> set serveroutput on SQL> begin -- 打印hello world DBMS_OUTPUT.PUT_LINE('hello world'); end; /
2.变量
（1）普通数据类型（char,varchar2,date,number,boolean,long）
（2）特殊变量类型（引用型变量、记录型变量）
声明变量方式：
变量名 变量类型(变量长度) 例如：v_name varchar2(20);
变量赋值：
（1）直接赋值（变量 :=’值’）
V_name :=’zhangsan’
（2）语句赋值 （select 值 into 变量）
普通变量：
--打印人员个人信息，包括：姓名、薪水、地址 declare -- 姓名 v_name varchar2(20) := '张三';--声明变量时直接赋值 --薪水 v_sal number; --地址 v_addr varchar2(200); begin -- 在程序中直接赋值 v_sal := 10000; -- 语句赋值 select '北京' into v_addr from dual; -- 打印变量 dbms_output.put_line('姓名' || v_name || '，薪水’ || v_sal || '，地址' || v_addr); end;
引用型变量：
变量的类型和长度取决于表中字段的类型和长度
语法：变量名 表名.列名%TYPE;
--查询emp表中1089号员工个人信息，包括：姓名、薪水 declare -- 姓名 v_name emp.ename%TYPE; --薪水 v_sal emp.sal%TYPE; begin -- 查询表中信息并赋值，保持字段顺序一致 select ename,sal into v_name,v_sal from emp where id=1089; -- 打印变量 dbms_output.put_line('姓名' || v_name || '，薪水’ || v_sal); end;
记录型变量：
接受表中的一整行记录，相当于java中的一个对象
语法：变量名 表名%ROWTYPE
--查询emp表中1089号员工个人信息，包括：姓名、薪水 declare -- 记录型变量 v_emp emp%ROWTYPE; --薪水 v_sal emp.sal%TYPE; begin -- 查询表中一行记录并赋值 select * into v_mep from emp where id=1089; -- 打印变量 dbms_output.put_line('姓名' || v_emp.ename || '，薪水’ || v_emp.sal); end;
3.流程控制
（1）条件分支
语法：
begin if 条件1 then 执行1 -- 注意关键字elsif elsif 条件2 then 执行2 else 执行3 end if; end;
示例：
--判断emp表中记录数 declare -- 记录型变量 v_count number; begin select count(1) into v_count from emp; if v_count > 20 then dbms_output.put_line('emp表中记录数大于20条，为：' || v_count); elsif v_count >= 10 then dbms_output.put_line('emp表中记录数在10到20条之间，为：' || v_count); else dbms_output.put_line('emp表中记录数小于10条，为：' || v_count); end if; end;
（2）loop循环
语法：
begin loop exit when 退出循环条件 end loop; end;
示例：
--打印数字1-10 declare -- 声明循环变量 v_num number := 1; begin loop exit when v_num > 10; dbms_output.put_line(v_num); -- 循环变量自增 v_num := v_num + 1; end loop; end;
4.游标
（1）概念：用于临时存储一个查询返回的多行数据（结果集，类似于Java的jdbc连接返回的ResultSet集合），通过遍历游标，可以逐行访问处理该结果集的数据。
游标的使用方式：声明——打开——读取——关闭
（2）游标声明语法
-- 游标声明 cursor 游标名[(参数列表)] is 查询语句; --游标打开: open 游标名; -- 游标的取值 fetch 游标名 into 变量列表; -- 游标的关闭 close 游标名;
（3）游标的属性
| 游标的属性 | 返回值类型 | 说明 |
|---|---|---|
| %ROWCOUNT | 整型 | 获取fetch语句返回的数据行数 |
| %FOUND | 布尔型 | 最近的fetch语句返回一行数据则为真，否则为假 |
| %NOTFOUND | 布尔型 | 与%FOUND属性返回值相反，找不到元素时返回true，通常用来判断推出循环 |
| %ISOPEN | 布尔型 | 游标已经打开时值为真，否则为假 |
不带参数游标应用：
--使用游标查询emp表中所有员工的姓名和工资，并将其以此打印出来 declare -- 游标声明 cursor c_emp is select ename,sal from emp; -- 声明变量接收游标中的数据 v_ename emp.ename%TYPE; v_sal emp.sal%TYPE; begin --游标打开: open c_emp; loop -- 获取游标中的数据 fetch c_emp into v_ename,v_sal; -- 退出循环条件 exit when c_emp%NOTFOUND; dbms_output.put_line(v_ename || '-' || v_sal); close loop; -- 游标的关闭 close c_emp; end;
带参数游标应用：
--使用游标查询并打印某部门的员工的姓名和工资，部门编号为运行时手动输入 declare -- 游标声明(v_deptno是传递的部门参数) cursor c_emp(v_deptno emp.deptno%TYPE) is select ename,sal from emp where deptno = v_deptno; -- 声明变量接收游标中的数据 v_ename emp.ename%TYPE; v_sal emp.sal%TYPE; begin --游标打开（传递参数v_deptno的值） open c_emp(10); loop -- 获取游标中的数据 fetch c_emp into v_ename,v_sal; -- 退出循环条件（注意：要先fetch获取数据，然后在做判断） exit when c_emp%NOTFOUND; dbms_output.put_line(v_ename || '-' || v_sal); close loop; -- 游标的关闭 close c_emp; end;
5.存储过程
（1）概念：之前我们编写的plsql程序可以进行表的操作、判断、循环、逻辑处理的工作，但无法重复调用。可以理解为之前的代码都编写在了main方法，是匿名程序，Java可通过封装对象和方法来解决服用问题。Plsql是将一个个plsql的业务处理过程存储起来进行复用，这些被存储起的plsql程序称之为存储过程。
（2）作用：在开发程序中，为了一个特定的业务功能，会向数据库进行多次来连接关闭(非常消耗资源)，需要对数据库进行多次I/O读写，性能较低。如果把这些业务放到plsql中，在应用程序中只需要调用plsql就可以做到连接关闭一次数据库就可以实现我们的业务，可以大大提高效率。
（3）语法（is可以换成as）
create or replace procedure 过程名称[(参数列表)] is -- 声明变量（没有declare关键字，declare用在语句块中） begin end[过程名称];
无参示例：
create or replace procedure p_hello is begin -- 打印hello world dbms_output.put_line('hello world'); end p_hello;
在测试窗口中调用：
begin -- plsql调用存储过程 pl_hello; end;
在命令窗口调用：
SQL> set serveroutput on SQL> exec p_hello;
带输入参数例子：
-- 查询并打印某个员工（如2189号员工）的姓名和薪水 -- 存储过程：要求调用的时候传入员工编号，自动控制台打印 -- 存储过程名(入参名 in 入参类型) create or replace procedure p_querynameAndSal(i_empno in emp.empno%TYPE) is -- 声明变量 v_name emp.ename%TYPE; v_sal emp.sal%TYPE; begin -- 查询emp表中某员工的姓名和薪水并赋值给变量 select ename,sal into v_name,v_sal from emp where empno = i_empno; dbms_output.put_line(v_name || '-' || v_sal); end; -- 在测试窗口中调用 begin -- plsql调用存储过程 pl_querynameAndSal(2189); end;
带输出参数（返回值）的存储过程：
-- 查询并返回某个员工（如2189号员工）的薪水 -- 存储过程名(入参名 in 入参类型,输出参数名 out 输出参数类型) create or replace procedure 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; -- 测试窗口调用存储过程 declare -- 声明变量接收存储过程中的输出参数 v_sal emp.sal%TYPE; begin -- plsql调用存储过程 p_querySal_out(2189,v_sal); dbms_output.put_line(v_sal); end;
Java程序调用存储过程：
需求：如果一条语句无法实现结果集，比如需要多表查询，或者需要复杂逻辑查询，我们可以选择调用存储过程查询需要的结果。
public class ProcedureTest { public static void main(String[] args) { // 1.加载驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); // 2.创建连接 String url = "jdbc:oracle:thin:@localhost:1521:xe"; String user = "root"; String password = "123456"; Connection conn = DriverManager.getConnection(url, user, password); // 3.获得语句对象 String sql = "{call p_querySal_out(?,?)}"; CallableStatement call = conn.prepareCall(sql); // 4.设置输入参数 call.setInt(1,2189); // 5.注册输出参数 call.registerOutParameter(2, OracleTypes.DOUBLE); // 6.执行存储过程 call.execute(); // 7.获取输出参数 double sal = call.getDouble(2); System.out.println(sal); // 8.释放资源 call.close(); conn.close(); } }
Hibernate调用存储过程：
/** * 直接调用存储过程 */ public void callProcedure(String procString,List<Object> params) throws Exception { CallableStatement stmt = null; try { stmt = this.getSession().connection().prepareCall(procString); if (params != null){ int idx = 1; for (Object obj : params) { if (obj != null) { stmt.setObject(idx, obj); } else { stmt.setNull(idx, Types.NULL); } idx++; } } stmt.execute(); } catch (SQLException e) { e.printStackTrace(); throw new Exception("调用存储过程的时候发生错误[sql = " + procString + "]", e); }
或：
tx = session.beginTransaction(); Connection con=session.connection(); String procedure = "{call batchUpdateStudent(?) }"; CallableStatement cstmt = con.prepareCall(procedure); cstmt.setInt(1,0); //把年龄参数设为0 cstmt.executeUpdate(); tx.commit();
上文提到的测试窗口和命令窗口：
