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 = '' then count80:=count80+1;
       elsif phiredate = '' then count81:=count81+1;
       elsif phiredate = '' 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;