PL/SQL程序设计
1 PL/SQL简介
1 什么是PL/SQL?
PL/SQL是 Procedure Language & Structured Query Language 的缩写。PL/SQL是对SQL语言存储过程语言的扩展。
指在SQL命令语言中增加了过程处理语句（如分支、循环等），使SQL语言具有过程处理能力。
把SQL语言的数据操纵能力与过程语言的数据处理能力结合起来，使得PLSQL面向过程但比过程语言简单、高效、灵活和实用。
2 PL/SQL的优点或特征
1 有利于客户/服务器环境应用的运行
对于客户/服务器环境来说，真正的瓶颈是网络上。无论网络多快，只要客户端与服务器进行大量的数据交换。应用运行的效率自然就回受到影响。如果使用PL/SQL进行编程，将这种具有大量数据处理的应用放在服务器端来执行。自然就省去了数据在网上的传输时间。
2 适合于客户环境
PL/SQL由于分为数据库PL/SQL部分和工具PL/SQL。对于客户端来说，PL/SQL可以嵌套到相应的工具中，客户端程序可以执行本地包含PL/SQL部分，也可以向服务发SQL命令或激活服务器端的PL/SQL程序运行。
3 过程化
PL/SQL是Oracle在标准SQL上的过程性扩展，不仅允许在PL/SQL程序内嵌入SQL语句，而且允许使用各种类型的条件分支语句和循环语句，可以多个应用程序之间共享其解决方案。
4 模块化
PL/SQL程序结构是一种描述性很强、界限分明的块结构、嵌套块结构，被分成单独的过程、函数、触发器，且可以把它们组合为程序包，提高程序的模块化能力。
5 运行错误的可处理性
使用PL/SQL提供的异常处理（EXCEPTION），开发人员可集中处理各种ORACLE错误和PL/SQL错误，或处理系统错误与自定义错误，以增强应用程序的健壮性。
6 提供大量内置程序包
ORACLE提供了大量的内置程序包。通过这些程序包能够实现DBS的一些低层操作、高级功能，不论对DBA还是应用开发人员都具有重要作用。
当然还有其它的一些优点如：更好的性能、可移植性和兼容性、可维护性、易用性与快速性等。
3 PL/SQL 可用的SQL语句
PL/SQL是ORACLE系统的核心语言，现在ORACLE的许多部件都是由PL/SQL写成。在PL/SQL中可以使用的SQL语句有：
INSERT，UPDATE，DELETE，SELECT INTO，COMMIT，ROLLBACK，SAVEPOINT。
提示：在 PL/SQL中只能用 SQL语句中的 DML 部分，不能用 DDL 部分，如果要在PL/SQL中使用DDL(如CREATE table 等)的话，只能以动态的方式来使用。
ORACLE 的 PL/SQL 组件在对 PL/SQL 程序进行解释时，同时对在其所使用的表名、列名及数据类型进行检查。
PL/SQL 可以在SQL*PLUS 中使用。
PL/SQL 可以在高级语言中使用。
PL/SQL可以在ORACLE的开发工具中使用(如：SQL Developer或Procedure Builder等)。
其它开发工具也可以调用PL/SQL编写的过程和函数，如Power Builder 等都可以调用服务器端的PL/SQL过程。
4 运行PL/SQL程序
5 PL/SQL程序结构及组成
6 变量和常量说明
下面的所有练习使用的sqldeveloper工具。
常用快捷的alt+f10 打开一个SQL工作表。
sqldeveloper工具的安装及其使用：sqldeveloper安装及其使用教程
2 第一个PL/SQL程序
SQL> --如果要在屏幕上输出信息，需要将serveroutput开关打开 SQL> set serveroutput on SQL> --第一个pl/sql程序，控制台输出hello world SQL> declare 2 begin 3 dbms_output.put_line('hello world'); 4 end; 5 / hello world PL/SQL procedure successfully completed.
3 引用型变量
--如果要在屏幕上输出信息，需要将serveroutput开关打开 set serveroutput on --引用型变量: 查询并打印7839的姓名和薪水 declare --定义变量保存姓名和薪水 --pename varchar2(20); --psal number; pename emp.ename%type; psal emp.sal%type; begin --得到7839的姓名和薪水 select ename,sal into pename,psal from emp where empno=7839; --打印 dbms_output.put_line(pename||'的薪水是'||psal); end; /
4 记录型变量
--如果要在屏幕上输出信息，需要将serveroutput开关打开 set serveroutput on --记录型变量: 查询并打印7839的姓名和薪水 declare --定义记录型变量：代表一行 emp_rec emp%rowtype; begin select * into emp_rec from emp where empno=7839; dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal); end;
5 if语句
--如果要在屏幕上输出信息，需要将serveroutput开关打开 set serveroutput on -- 判断用户从键盘输入的数字 --接受键盘输入 --变量num：是一个地址值，在该地址上保存了输入的值 accept num prompt '请输入一个数字'; declare --定义变量保存输入 的数字 pnum number := # begin if pnum = 0 then dbms_output.put_line('您输入的是0'); elsif pnum = 1 then dbms_output.put_line('您输入的是1'); elsif pnum = 2 then dbms_output.put_line('您输入的是2'); else dbms_output.put_line('其他数字'); end if; end; /
6 求两个数的和
----如果要在屏幕上输出信息，需要将serveroutput开关打开 set serveroutput on -- 例6：求两个数得和 -- 接收键盘输入 accept num1 prompt '请输入第一个数' accept num2 prompt '请输入第二个数' declare --定义变量保存输入得数字 pnum1 number :=&num1; pnum2 number :=&num2; psum number :=0; begin psum :=pnum1+pnum2; DBMS_OUTPUT.PUT_LINE(pnum1||'+'||pnum2||'='||psum); end; /
7 求1加到n的和使用while
--如果要在屏幕上输出信息，需要将serveroutput开关打开 set serveroutput on -- 使用while:求 1 加到 n 的和(循环) -- 接收键盘输入 accept num prompt '请输入一个数' declare --定义变量保存输入 的数字 pnum number := # pn number :=1; psum number :=0; begin WHILE pn<=pnum loop psum :=psum+pn; pn :=pn+1; end loop; -- 循环结束打印 dbms_output.put_line('1加到'||pnum||'的和为'||psum); end; /
8 求1加到n的和使用do while
--如果要在屏幕上输出信息，需要将serveroutput开关打开 set serveroutput on -- 使用do while:求 1 加到 n 的和(循环) -- 接收键盘输入 accept num prompt '请输入一个数' declare --定义变量保存输入 的数字 pnum number := # pn number :=1; psum number :=0; begin loop exit when pn>pnum; psum :=psum+pn; pn :=pn+1; end loop; -- 循环结束打印 dbms_output.put_line('1加到'||pnum||'的和为'||psum); end; /
9 求1加到n的和使用for
--如果要在屏幕上输出信息，需要将serveroutput开关打开 set serveroutput on -- 使用for:求 1 加到 n 的和(循环) -- 接收键盘输入 accept num prompt '请输入一个数' declare --定义变量保存输入 的数字 pnum number := # psum number :=0; begin for I in 1..pnum --for循环的I直接使用，不用定义。1..100表示1到100的意思。不能有分号。 loop psum :=psum+I; end loop; -- 循环结束打印 dbms_output.put_line('1加到'||pnum||'的和为'||psum); end; /
10 给员工涨工资(游标)
知识点
代码
可以先把emp表复制一份
create table memp as select * from emp;
执行下面的代码涨工资
--如果要在屏幕上输出信息，需要将serveroutput开关打开 set serveroutput on -- 给员工涨工资 -- 需求：按员工的工种长工资,总裁 1000 元，经理长 800 元，其他人员长 400 元。 declare --定义光标 cursor cemp is select empno,job from memp; pempno memp.empno%type; pjob memp.job%type; begin rollback; --打开光标 open cemp; loop --取一个员工 fetch cemp into pempno,pjob; exit when cemp%notfound; --判断职位 if pjob = 'PRESIDENT' then update memp set sal=sal+1000 where empno=pempno; elsif pjob = 'MANAGER' then update memp set sal=sal+800 where empno=pempno; else update memp set sal=sal+400 where empno=pempno; end if; end loop; --关闭光标 close cemp; --提交 ----> why?: 事务 ACID commit; dbms_output.put_line('完成'); end; /
11 使用游标打印员工的姓名和薪水
--如果要在屏幕上输出信息，需要将serveroutput开关打开 set serveroutput on -- 使用游标打印员工的姓名和薪水 declare --定义光标(游标) cursor cemp is select ename,sal from emp; pename EMP.ENAME%type; psal emp.sal%type; begin --打开光标 open cemp; loop --取当前记录 fetch cemp into pename,psal; --exit when 没有取到记录退出 exit when cemp%notfound; dbms_output.put_line(lower(pename)||'的薪水是'||to_char(psal,'L9,999.99')); end loop; --关闭光标 close cemp; end;
/
12 带参数的游标
--如果要在屏幕上输出信息，需要将serveroutput开关打开 set serveroutput on -- 带参数的游标 打印指定部门的员工的姓名和薪水 declare --定义光标(游标) cursor cemp(dno number) is select ename,sal from emp where deptno=dno; pename EMP.ENAME%type; psal emp.sal%type; begin --打开光标 open cemp(10); loop --取当前记录 fetch cemp into pename,psal; --exit when 没有取到记录退出 exit when cemp%notfound; dbms_output.put_line(lower(pename)||'的薪水是'||to_char(psal,'L9,999.99')); end loop; --关闭光标 close cemp; end;
/
13 打印指定部门的员工信息
--如果要在屏幕上输出信息，需要将serveroutput开关打开 set serveroutput on -- 带参数的游标 打印指定部门的员工的姓名和薪水 --接收键盘输入 accept Jdeptno prompt '请输入要答应的部门号' declare -- 接收键盘输入 JPdeptno number :=&Jdeptno; --定义光标(游标) cursor cemp(dno number) is select ename,sal from emp where deptno=dno; pename EMP.ENAME%type; psal emp.sal%type; begin --打开光标 open cemp(JPdeptno); loop --取当前记录 fetch cemp into pename,psal; --exit when 没有取到记录退出 exit when cemp%notfound; dbms_output.put_line(lower(pename)||'的薪水是'||to_char(psal,'L9,999.99')); end loop; --关闭光标 close cemp; end;
/
14 系统例外的使用
知识点
代码
--如果要在屏幕上输出信息，需要将serveroutput开关打开 set serveroutput on -- 系统列外 演示系统列为被0除 declare pnum number; begin pnum :=1/0; exception when zero_divide then dbms_output.put_line('1,0不能作为分母'); dbms_output.put_line('2,0不能作为分母'); dbms_output.put_line('3,0不能作为分母'); when value_error then dbms_output.put_line('算术或者转换错误'); when others then dbms_output.put_line('其它列外'); end;
/
15 用户自定义例外
--如果要在屏幕上输出信息，需要将serveroutput开关打开 set serveroutput on -- 演示用户自定义例外 查询50号部门的员工(50号部门是不存在的) /*自定义例外的三步曲： 第一步：在declare节中定义例外。格式：例外名 exception 第二步：在可行语句中引起例外 格式：raise 例外名 第三步：在Exception节处理例外 */ declare cursor cemp (dno number) is select ename from emp where deptno=dno; pename emp.ename%type; -- 自定义例外 员工不存在 no_emp_found exception; begin --打开光标 open cemp(50); --取出第一个员工 fetch cemp into pename; if cemp%notfound then raise no_emp_found; end if; --关闭光标 close cemp; exception when no_emp_found then dbms_output.put_line('员工不存在或者部门不存在'); end;
/
16 统计每年入职的员工的个数
方法一：直接使用sql语句得到结果
或者下面的代码
方法二：使用PL/SQL程序
/* 1、SQL语句 select to_char(hiredate,'yyyy') from emp; ---> 集合 ---> 光标 ---> 循环---> 退出: notfound 2、变量：（*）初始值 （*）最终如何得到 每年入职的人数 count80 number := 0; count81 number := 0; count82 number := 0; count87 number := 0; */ declare --定义光标 cursor cemp is select to_char(hiredate,'yyyy') from emp; phiredate varchar2(4); --每年入职的人数 count80 number := 0; count81 number := 0; count82 number := 0; count87 number := 0; begin --打开光标 open cemp; loop --取一个员工的入职年份 fetch cemp into phiredate; exit when cemp%notfound; --判断年份是哪一年 if phiredate = '1980' then count80:=count80+1; elsif phiredate = '1981' then count81:=count81+1; elsif phiredate = '1982' then count82:=count82+1; else count87:=count87+1; end if; end loop; --关闭光标 close cemp; --输出 dbms_output.put_line('Total:'||(count80+count81+count82+count87)); dbms_output.put_line('1980:'|| count80); dbms_output.put_line('1981:'|| count81); dbms_output.put_line('1982:'|| count82); dbms_output.put_line('1987:'|| count87); end; /
17 给员工涨工资
这里为了先不破坏emp表的数据。所以先创建一个表，复制emp的数据。
create table memp as select * from emp;
实现涨工资的代码如下：
--如果要在屏幕上输出信息，需要将serveroutput开关打开 set serveroutput on /* 1、SQL语句 selet empno,sal from emp order by sal; ---> 光标 ---> 循环 ---> 退出：1. 总额>5w 2. notfound 2、变量：（*）初始值 （*）最终如何得到 涨工资的人数: countEmp number := 0; 涨后的工资总额：salTotal number; (1)select sum(sal) into salTotal from emp; (2)涨后=涨前 + sal *0.1 练习： 人数：8 总额:50205.325 */ declare cursor cemp is select empno,sal from memp order by sal; pempno memp.empno%type; psal memp.sal%type; --涨工资的人数: countEmp number := 0; --涨后的工资总额： salTotal number; begin --得到工资总额的初始值 select sum(sal) into salTotal from memp; open cemp; loop -- 1. 总额 >5w exit when salTotal > 50000; --取一个员工 fetch cemp into pempno,psal; --2. notfound exit when cemp%notfound; --先判断，假设给员工涨工资后，工资总额是否超过5万，没有超过就涨，超过了就不涨 exit when (salTotal+psal*0.1)>50000; --没有超过5万，给员工涨工资 update memp set sal=sal*1.1 where empno=pempno; --人数+1 countEmp := countEmp +1; --涨后=涨前 + sal *0.1 salTotal := salTotal + psal * 0.1; end loop; close cemp; commit; dbms_output.put_line('人数：'||countEmp||' 总额:'||salTotal); end; /
18 按部门统计各工资段的职工人数
创建表的代码如下：
create table msg( pdeptno number, count1 number, count2 number, count3 number, saltotal number );
代码实现：
/* 1、SQL语句 部门：select deptno from dept; ---> 光标 部门中员工的薪水: select sal from emp where deptno=?? ---> 带参数的光标 2、变量：（*）初始值 （*）最终如何得到 每个段的人数 count1 number; count2 number; count3 number; 部门的工资总额 salTotal number := 0; (1)select sum(sal) into salTotal from emp where deptno=?? (2)累加 */ declare --部门 cursor cdept is select deptno from dept; pdeptno dept.deptno%type; --部门中员工的薪水 cursor cemp(dno number) is select sal from emp where deptno=dno; psal emp.sal%type; --每个段的人数 count1 number; count2 number; count3 number; --部门的工资总额 salTotal number := 0; begin --部门 open cdept; loop --取一个部门 fetch cdept into pdeptno; exit when cdept%notfound; --初始化 count1:=0; count2:=0; count3:=0; --得到部门的工资总额 select sum(sal) into salTotal from emp where deptno=pdeptno; --取部门的中员工薪水 open cemp(pdeptno); loop --取一个员工的薪水 fetch cemp into psal; exit when cemp%notfound; --判断 if psal < 3000 then count1:=count1+1; elsif psal >=3000 and psal<6000 then count2:=count2+1; else count3:=count3+1; end if; end loop; close cemp; --保存结果 insert into msg values(pdeptno,count1,count2,count3,nvl(saltotal,0)); end loop; close cdept; commit; dbms_output.put_line('完成'); end; /
执行完成后，查看msg表。
19 瀑布模型
20 存储过程入门
语法
代码一
--如果要在屏幕上输出信息，需要将serveroutput开关打开 set serveroutput on create or replace procedure sayhelloworld as --说明部分 begin dbms_output.put_line('hello world'); end sayhelloworld; /
代码二
--如果要在屏幕上输出信息，需要将serveroutput开关打开 set serveroutput on create or replace procedure sayhelloworld2 is --说明部分 begin dbms_output.put_line('hello world 22222222'); end; /
调用方式一
exec sayhelloworld2();
调用方式二
--如果要在屏幕上输出信息，需要将serveroutput开关打开 set serveroutput on declare begin sayhelloworld(); sayhelloworld2(); end; /
删除存储过程
drop procedure 过程名；
查看存储过程
双击可以看到源代码
21 带参数的存储过程
存储过程代码
--如果要在屏幕上输出信息，需要将serveroutput开关打开 set serveroutput on --给指定的员工涨指定的工资，并且打印涨前和涨后的薪水 create or replace procedure raisesalary( eno in number, raisenum in number )is --定义变量保存涨前的薪水 psal emp.sal%type; begin --获得涨前的薪水 select sal into psal from memp where empno=eno; --涨钱 update memp set sal = sal+raisenum where empno=eno; --要不要commit？不需要，让调用者决定什么时候提交 dbms_output.put_line('涨前'||psal||' 涨后'||(psal+raisenum)); end; /
调用存储过程
exec raisesalary(7839,-986);
22 存储函数
知识点
创建一个存储函数
--如果要在屏幕上输出信息，需要将serveroutput开关打开 set serveroutput on --查询某个员工的年收入 create or replace function queryEmpIncome(eno in number) return number is --定义变量保存月薪和奖金 psal emp.sal%type; pcomm emp.comm%type; begin --得到月薪和奖金 select sal,comm into psal,pcomm from emp where empno=eno; --返回年收入 return psal*12+nvl(pcomm,0); end queryEmpIncome; /
调用上面的存储函数
--如果要在屏幕上输出信息，需要将serveroutput开关打开 set serveroutput on declare annsal number; begin annsal :=queryEmpIncome(7369); dbms_output.put_line('7369的年收入是'||annsal); end; /
存储函数的查看
点击可以打开源码
23 在java中调用存储过程
1.Java连接Oracle的jar包
2.常用配置
String driver = "oracle.jdbc.OracleDriver"; String url = "jdbc:oracle:thin:@192.168.47.10:1521/orcl"; String user = "scott"; String password = "tiger";
3.准备一个存储过程
--查询某个员工的姓名 薪水和职位 /* 1、查询某个员工的所有信息 ---> out参数太多 2、查询某个部门中的所有员工信息 ----> 返回的是集合 */ create or replace procedure queryEmpInformation(eno in number, pename out varchar2, psal out number, pjob out varchar2) is begin select ename,sal,job into pename,psal,pjob from emp where empno=eno; end queryEmpInformation; /
4.1 编写一个JDBCUtils工具类
package utils; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class JDBCUtils { private static String driver = "oracle.jdbc.OracleDriver"; private static String url = "jdbc:oracle:thin:@192.168.47.10:1521/orcl"; private static String user = "scott"; private static String password = "tiger"; static{ //注册驱动 //DriverManager.registerDriver(driver) 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; } /* * 运行Java： * java -Xms100M -Xmx200M HelloWorld * * 技术方向： * 1、性能优化 * 2、故障诊断：死锁(JDK: ThreadDump) * Oracle: 自动处理 */ public static void release(Connection conn,Statement st,ResultSet rs){ if(rs != null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); }finally{ rs = null; ///-----> 原因：Java GC: Java的GC不受代码的控制 //java的垃圾回收不受代码的控制 gc()垃圾回收 } } if(st != null){ try { st.close(); } catch (SQLException e) { e.printStackTrace(); }finally{ st = null; } } if(conn != null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); }finally{ conn = null; } } } }
4.2 测试存储过程
package utils; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.ResultSet; import oracle.jdbc.OracleCallableStatement; import oracle.jdbc.OracleTypes; import org.junit.Test; public class TestOracle { /* * create or replace procedure queryEmpInformation(eno in number, pename out varchar2, psal out number, pjob out varchar2) */ @Test public void testProcedure(){ //{call <procedure-name>[(<arg1>,<arg2>, ...)]} String sql = "{call queryEmpInformation(?,?,?,?)}"; Connection conn = null; CallableStatement call = null; try { conn = JDBCUtils.getConnection(); call = conn.prepareCall(sql); //对于in参数，赋值 call.setInt(1,7839); //对于out参数，申明 call.registerOutParameter(2, OracleTypes.VARCHAR); call.registerOutParameter(3, OracleTypes.NUMBER); call.registerOutParameter(4, OracleTypes.VARCHAR); //执行 call.execute(); //输出 String name = call.getString(2); double sal = call.getDouble(3); String job = call.getString(4); System.out.println(name+"\t"+sal+"\t"+job); } catch (Exception e) { e.printStackTrace(); }finally{ JDBCUtils.release(conn, call, null); } } }
5.1 编写一个C3P0Utils工具类
c3p0-config.xml配置文件内容如下
<?xml version="1.0" encoding="UTF-8"?> <c3p0-config> <default-config> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql:///student</property> <property name="user">root</property> <property name="password">root</property> <property name="initialPoolSize">5</property> <property name="maxPoolSize">20</property> </default-config> <named-config name="oracle"> <property name="driverClass">oracle.jdbc.OracleDriver</property> <property name="jdbcUrl">jdbc:oracle:thin:@192.168.47.10:1521/orcl</property> <property name="user">scott</property> <property name="password">tiger</property> </named-config> <named-config name="mysql"> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql:///student</property> <property name="user">root</property> <property name="password">root</property> </named-config> </c3p0-config>
C3P0Utils.java代码如下
package utils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import javax.sql.DataSource; import com.mchange.v2.c3p0.ComboPooledDataSource; public class C3P0Utils { // 1 获得Connection ----- 从连接池中获取 private static DataSource dataSource = new ComboPooledDataSource("oracle"); // 2 创建ThreadLocal 存储的类型是Connection private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>(); // 3 直接可以获取一个连接池 public static DataSource getDataSource() { return dataSource; } // 4 直接获取一个连接 public static Connection getConnection() throws SQLException { return dataSource.getConnection(); } // 5 获取绑定到ThreadLocal上的连接对象 public static Connection getCurrentConnection() throws SQLException { //从ThreadLocal寻找 当前线程是否有对应Connection Connection con = tl.get(); if (con == null) { //获得新的connection con = dataSource.getConnection(); //将conn资源绑定到ThreadLocal（map）上 tl.set(con); } return con; } // 6 开启事务 public static void startTransaction() throws SQLException { Connection con = getCurrentConnection(); if (con != null) { con.setAutoCommit(false); } } // 7 事务回滚 public static void rollback() throws SQLException { Connection con = getCurrentConnection(); if (con != null) { con.rollback(); } } // 8 提交并且 关闭资源及从ThreadLocall中释放 public static void commitAndRelease() throws SQLException { Connection con = getCurrentConnection(); if (con != null) { con.commit(); // 事务提交 con.close();// 关闭资源 tl.remove();// 从线程绑定中移除 } } // 9 关闭资源方法 public static void release(Connection conn,Statement st,ResultSet rs){ if(rs != null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); }finally{ rs = null; ///-----> 原因：Java GC: Java的GC不受代码的控制 //java的垃圾回收不受代码的控制 gc()垃圾回收 } } if(st != null){ try { st.close(); } catch (SQLException e) { e.printStackTrace(); }finally{ st = null; //设置为空可以方便java的垃圾回收 } } if(conn != null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); }finally{ conn = null; } } } }
5.2 测试存储过程
/* create or replace procedure queryEmpInformation(eno in number, pename out varchar2, psal out number, pjob out varchar2) */ @Test //测试在java中调用存储过程 public void fun1() throws Exception{ //{call <procedure-name>[(<arg1>,<arg2>, ...)]} String sql ="call queryEmpInformation(?,?,?,?)"; Connection conn =null; CallableStatement call =null; try { conn = C3P0Utils.getConnection(); call = conn.prepareCall(sql); //对于in参数，赋值 call.setObject(1, 7839); //对于out参数，申明 call.registerOutParameter(2, OracleTypes.VARCHAR); call.registerOutParameter(3, OracleTypes.NUMBER); call.registerOutParameter(4, OracleTypes.VARCHAR); //执行 call.execute(); //输出 System.out.println(call.getObject(2)+"\t"+call.getObject(3) +"\t"+call.getObject(4)); } catch (Exception e) { }finally{ C3P0Utils.release(conn,call, null); } }
24 在java中调用存储函数
1 准备一个存储函数
create or replace function queryEmpIncome(eno in number) return number is --定义变量保存月薪和奖金 psal emp.sal%type; pcomm emp.comm%type; begin --得到月薪和奖金 select sal,comm into psal,pcomm from emp where empno=eno; --返回年收入 return psal*12+nvl(pcomm,0); end queryEmpIncome;
2 测试
/* * create or replace function queryEmpIncome(eno in number) return number */ @Test public void testFunction(){ //{?= call <procedure-name>[(<arg1>,<arg2>, ...)]} String sql = "{?=call queryEmpIncome(?)}"; Connection conn = null; CallableStatement call = null; try { conn = JDBCUtils.getConnection(); call = conn.prepareCall(sql); call.registerOutParameter(1, OracleTypes.NUMBER); call.setInt(2, 7839); //执行 call.execute(); //取出年收入 double income = call.getDouble(1); System.out.println(income); } catch (Exception e) { e.printStackTrace(); }finally{ JDBCUtils.release(conn, call, null); } }
25 游标引用的java测试
1 包和体
--1、查询某个员工的所有信息--->out参数太多 --2、查询某个部门中的所有员工信息 ----> 返回的是集合 -- 申明包结构 create or replace package mypackage is type empcursor is ref cursor; procedure queryEmpList(dno in number,empList out empcursor); end mypackage; / -- 创建包体 create or replace package body mypackage is procedure queryEmpList(dno in number,empList out empcursor) as begin open empList for select * from emp where deptno=dno; end; end mypackage; /
2 测试
@Test public void testCursor(){ String sql = "{call mypackage.QUERYEMPLIST(?,?)}"; Connection conn = null; CallableStatement call = null; ResultSet rs = null; try { conn = JDBCUtils.getConnection(); call = conn.prepareCall(sql); //对于in参数，赋值 call.setInt(1,20); //对于out参数，申明 call.registerOutParameter(2, OracleTypes.CURSOR); //执行 call.execute(); //取出结果 rs = ((OracleCallableStatement)call).getCursor(2); while(rs.next()){ //取出一个员工 String name = rs.getString("ename"); double sal = rs.getDouble("sal"); System.out.println(name+"\t"+sal); } } catch (Exception e) { e.printStackTrace(); }finally{ JDBCUtils.release(conn, call, rs); } }
3 注意事项
26 java操作Oracle数据库
1 导入驱动
创建 lib 目录， 用于存放当前项目需要的所有 jar 包
选择 jar 包， 右键执行 build path / Add to Build Path
2 测试查询
@Test // 测试查询 public void fun1() throws Exception{ //1 注册驱动 Class.forName("oracle.jdbc.OracleDriver"); //2 获取连接 String url ="jdbc:oracle:thin:@192.168.47.10:1521/orcl"; String user ="scott"; String password ="tiger"; Connection conn = DriverManager.getConnection(url, user, password); //3 获取执行者对象 String sql ="select * from emp"; PreparedStatement pst = conn.prepareStatement(sql); //4 执行 ResultSet rs = pst.executeQuery(); //5 结果处理 while(rs.next()){ System.out.println(rs.getObject("empno")+"\t"+ rs.getObject("ename")+"\t"+rs.getObject("sal")); } //6 关闭资源 rs.close(); pst.close(); conn.close(); }
3 测试修改
@Test // 测试修改 public void fun2() throws Exception{ //1 注册驱动 Class.forName("oracle.jdbc.OracleDriver"); //2 获取连接 String url ="jdbc:oracle:thin:@192.168.47.10:1521/orcl"; String user ="scott"; String password ="tiger"; Connection conn = DriverManager.getConnection(url, user, password); //3 获取执行者对象 String sql ="update emp set sal=sal-100 where empno=?"; PreparedStatement pst = conn.prepareStatement(sql); //设置参数 pst.setObject(1, 7369); //4 执行 int rows = pst.executeUpdate(); //5 结果处理 System.out.println(rows); //6 关闭资源 pst.close(); conn.close(); }
4 注意事项
27 第一个触发器
1 知识点
2 演示代码
--每当成功插入新员工后，自动打印“成功插入新员工” create trigger firsttrigger after insert on emp declare begin dbms_output.put_line('成功插入新员工'); end; /
28 触发器的应用-实施复杂的安全性检查
/* 实施复杂的安全性检查 禁止在非工作时间 插入新员工 1、周末: to_char(sysdate,'day') in ('星期六','星期日') 2、上班前 下班后：to_number(to_char(sysdate,'hh24')) not between 9 and 17 */ create or replace trigger securityemp before insert on emp begin if to_char(sysdate,'day') in ('星期六','星期日') or to_number(to_char(sysdate,'hh24')) not between 9 and 17 then --禁止insert raise_application_error(-20001,'禁止在非工作时间插入新员工'); end if; end securityemp; /
29 触发器的应用-数据确认
/* 数据的确认 涨后的薪水不能少于涨前的薪水 */ create or replace trigger checksalary before update on emp for each row begin if 涨后的薪水 < 涨前的薪水 then raise_application_error(-20002,'涨后的薪水不能少于涨前的薪水'); end if; end checksalary; /
30 触发器的模拟MySQL的自增效果
1 创建表和序列
2 创建一个自增的触发器
3 测试
insert into test (name) values ('lisi')； insert into test (name) values ('zhangsan')； select * from test;
