PL/SQL程序设计

2022-11-05 14:46:11

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 := &num;

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 := &num;

  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 := &num;

  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 := &num;

  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;

码农公寓

1 PL/SQL简介

1 什么是PL/SQL?

2 PL/SQL的优点或特征

3 PL/SQL 可用的SQL语句

4 运行PL/SQL程序

5 PL/SQL程序结构及组成

6 变量和常量说明

2 第一个PL/SQL程序

3 引用型变量

4 记录型变量

5 if语句

6 求两个数的和

7 求1加到n的和使用while

8 求1加到n的和使用do while

9 求1加到n的和使用for

10 给员工涨工资(游标)

知识点

代码

11 使用游标打印员工的姓名和薪水

12 带参数的游标

13 打印指定部门的员工信息

14 系统例外的使用

知识点

代码

15 用户自定义例外

16 统计每年入职的员工的个数

方法一：直接使用sql语句得到结果

方法二：使用PL/SQL程序

17 给员工涨工资

18 按部门统计各工资段的职工人数

19 瀑布模型

20 存储过程入门

语法

代码一

代码二

调用方式一

调用方式二

删除存储过程

查看存储过程

21 带参数的存储过程

存储过程代码

调用存储过程

22 存储函数

知识点

创建一个存储函数

调用上面的存储函数

存储函数的查看

23 在java中调用存储过程

1.Java连接Oracle的jar包

2.常用配置

3.准备一个存储过程

4.1 编写一个JDBCUtils工具类

4.2 测试存储过程

5.1 编写一个C3P0Utils工具类

5.2 测试存储过程

24 在java中调用存储函数

1 准备一个存储函数

2 测试

25 游标引用的java测试

1 包和体

2 测试

3 注意事项

26 java操作Oracle数据库

1 导入驱动

2 测试查询

3 测试修改

4 注意事项

27 第一个触发器

1 知识点

2 演示代码

28 触发器的应用-实施复杂的安全性检查

29 触发器的应用-数据确认

30 触发器的模拟MySQL的自增效果

1 创建表和序列

2 创建一个自增的触发器

3 测试

相关文章