Oracle——PL/SQL，存储过程/函数，java连接Oracle操作存储过程/函数，触发器

什么是PL/SQL

PL/SQL是一种过程化SQL语言（Procedural Language/SQL）。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL就是把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言。

PL/SQL语法

1）hello,world

--打印Hello World

declare
  --说明部分
begin
  --程序部分
  dbms_output.put_line('Hello World');　　-- dbms_output相当于java中的类
end;
/

2）定义变量类型

• 引用型变量

--引用型变量: 查询并打印7839的姓名和薪水

declare
  --定义变量保存姓名和薪水
  --pename varchar2(20);	-- 声明变量类型的方式1：直接定义变量的类型
  --psal   number;
  pename emp.ename%type;	-- 声明变量类型的方式2（引用型变量）：与emp表中的ename列的类型相同
  psal   emp.sal%type;
begin
  --得到7839的姓名和薪水
  select ename,sal into pename,psal from emp where empno=7839;	-- 使用into为变量赋值

  --打印
  dbms_output.put_line(pename||'的薪水是'||psal);	-- ||是Oracle中的字符串拼接
end;
/

• 记录型变量

--记录型变量: 查询并打印7839的姓名和薪水

declare
  --定义记录型变量：代表emp表中的一行
  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;
/

3）if语句

-- 判断用户从键盘输入的数字

--接受键盘输入
--变量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
     elsif pnum = 2 then dbms_output.put_line('您输入的是2');
     else dbms_output.put_line('其他数字');
  end if;
end;
/

4）循环

普通循环

-- 打印1~10
declare 
  -- 定义变量
  pnum number := 1;
begin
  loop
    --退出条件
    exit when pnum > 10;
    
    --打印
    dbms_output.put_line(pnum);
    --加一
    pnum := pnum + 1;
  end loop;
end;
/

While 循环，先判定条件，每次循环时条件都要变化，如果不变化就是死循环

Declare
V1 number(2) :=1;
Begin
    While v1<10 Loop
        Insert into t1 values(v1);
        v1:=v1+1;
    End loop;
End;
/

For循环，pl/sql中的最常见的循环，是和游标操作的绝配。方便而直观。

begin
    for v1 in 1..9 loop
    	Insert into t1 values(v1);
    end loop;
end;
/

 

For循环特点

 

 

• 步长为1
• 计数器不要声明，自动声明
• 对计数器只能引用。不能做赋值操作
• 计数器的数据类型和上下界的数据类型相同
• 计数器只能在循环体内引用

 

5）光标

-- 查询并打印员工的姓名和薪水
/*
光标的属性： %isopen（是否打开）   %rowcount（影响的行数）
             %found（是否有值）    %notfound（是否无值）

*/
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(pename||'的薪水是'||psal);
  end loop;

  --关闭光标
  close cemp;
end;
/

示例：给员工涨工资

-- 给员工涨工资，总裁1000 经理800 其他400
declare 
  --定义光标
  cursor cemp is select empno,job from emp;
  pempno emp.empno%type;
  pjob   emp.job%type;
begin

  --打开光标
  open cemp;  
  loop
       --取一个员工
       fetch cemp into pempno,pjob;
       exit when cemp%notfound;
       
       --判断职位
       if pjob = 'PRESIDENT' then update emp set sal=sal+1000 where empno=pempno;
          elsif pjob = 'MANAGER' then update emp set sal=sal+800 where empno=pempno;
          else update emp set sal=sal+400 where empno=pempno;
       end if;

  end loop;
  --关闭光标
  close cemp;
  
  --提交  ----> why?: 事务 ACID
  commit;
  
  dbms_output.put_line('完成');
end;
/

• 带参数的光标

-- 查询某个部门的员工姓名
declare 
   --形参
   cursor cemp(dno number) is select ename from emp where deptno=dno;
   pename emp.ename%type;
begin
   --实参
   open cemp(20);
   loop
        fetch cemp into pename;
        exit when cemp%notfound;
        
        dbms_output.put_line(pename);

   end loop;
   close cemp;
end;
/

6）例外（异常）

• 系统例外

-- 被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不能做分母');
  when value_error then dbms_output.put_line('算术或者转换错误');                      
  when others then dbms_output.put_line('其他例外');
end;
/

• 自定义例外

-- 查询50号部门（50号部门不存在）的员工
declare 
  cursor cemp  is select ename from emp where deptno=50;
  pename emp.ename%type;
  
  --自定义例外
  no_emp_found exception;
begin
  open cemp;
  
  --取第一条记录
  fetch cemp into pename;
  if cemp%notfound then
    --抛出例外，使用raise
    raise no_emp_found;
  end if;
  
  --进程：pmon进程(proccesss monitor)
  close cemp;

exception
  when no_emp_found then dbms_output.put_line('没有找到员工');
  when others then dbms_output.put_line('其他例外');
end;
/

实例1：统计每年入职的员工个数（使用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;
/

实例2：为员工涨工资，总工资最低的人开始涨，没人涨10%，但工资总额不超过万元，请计算长工资的人数和长工资后的工资总额。

/*
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 emp order by sal;
    pempno emp.empno%type;
    psal   emp.sal%type;
    
    --涨工资的人数: 
    countEmp number := 0;
    --涨后的工资总额：
    salTotal number;
begin
    --得到工资总额的初始值
    select sum(sal) into salTotal from emp;
    
    open cemp;
    loop
         -- 1. 总额 >5w
         exit when salTotal > 50000;
         --取一个员工
         fetch cemp into pempno,psal;
         --2. notfound
         exit when cemp%notfound;
         
         --涨工资
         update emp 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;
/

自认为正确的

-- Created on 2018/7/9 星期一 by X5456 
declare 
  
   -- 定义光标，存放id和工资
   cursor cemp is select empno,sal from emp order by sal;
   
   -- 定义相应的变量
   pempno emp.empno%type;
   psal emp.sal%type;
   
   --涨工资的人数:
   countEmp number := 0;
   --涨后的工资总额：
   salTotal number;
   --循环结束的flag
   flag boolean := false;
   
begin
  
   -- 1.获取总工资
   select sum(sal) into salTotal from emp;
   
   dbms_output.put_line('涨后的工资123123:'|| salTotal);
   
   --打开光标
   open cemp;
   
   -- 2.循环的涨工资
   loop
      
      -- 退出条件，总工资大于5w
      exit when flag;  
   
      fetch cemp into pempno,psal;
         --exit when 没有取到记录;
         exit when cemp%notfound;
         
      -- 进行判断，是否涨工资
      if salTotal+psal*0.1 > 50000 then flag := true;
        else update emp set sal = psal*1.1 where empno = pempno;
             salTotal := salTotal+psal*0.1;
             countEmp := countEmp+1;
      end if;

  end loop;
   
  --关闭光标
  close cemp;
  
  commit;
  
  dbms_output.put_line('涨后的工资:'|| salTotal);
  dbms_output.put_line('涨工资的人数:'|| countEmp);
end;

实例3：

/*
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;
/

存储过程

-- 打印Hello World

/*
调用存储过程的2种方式：
1、exec sayhelloworld();	-- SQLplus命令
2、begin
	sayhelloworld();
	sayhelloworld();
   end;
   /
*/

create or replace procedure sayhelloworld
as	-- 使用is也可以
   --说明部分
begin
   dbms_output.put_line('Hello World');

end;
/

• 带参数的存储过程

--给指定的员工涨100，并且打印涨前和涨后的薪水

create or replace procedure raiseSalary(eno in number)
is
       --定义变量保存涨前的薪水
       psal emp.sal%type;
begin
       --得到涨前的薪水
       select sal into psal from emp where empno=eno;
       
       --涨100
       update emp set sal=sal+100 where empno=eno;
       
       --要不要commit？在存储过程中一般都不commit和rollback，应该交给调用者来操作
       
       dbms_output.put_line('涨前:'||psal||'   涨后：'||(psal+100));
end raiseSalary;
/


-- 调用方法，给员工号为7839,7566涨工资

begin
	raiseSalary(7839);
	raiseSalary(7566);
	commit;
end;
/

• out（返回）参数

--查询某个员工的姓名 薪水和职位

/*
两个问题
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;
/

-- 运行

begin
    queryEmpInformation(eno => 7839,	-- 参数名 => 值 （为指定的参数名赋值）
                        pename => :pename,
						psal => :psal,
						pjob => :pjob);
end;
/

有了out，存储函数就多余了，因为之前存储过程不能返回参数，存储函数可以返回一个参数

• 实现返回一个集合（采用package与package body）

--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;
/

• java程序调用

@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+"	"+sal);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JDBCUtils.release(conn, call, rs);
        }

    }

存储函数

--查询某个员工的年收入

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;
/

java连接Oracle操作存储过程/函数

JDBCUtils（建立/断开与Oracle连接）

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.137.129: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不受代码的控制），将引用置为空，就会被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;
			}
		}
	}
}

有返回值与没返回值的调用方法

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+"	"+sal+"	"+job);
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JDBCUtils.release(conn, call, null);
        }
    }

    /* 无返回值
     * 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);
        }
    }
}

触发器

数据库触发器是一个与表相关联，存储PL/SQL的程序。每当一个特定的数据操作语句（insert、update、delete）在指定的表上发出时，Oracle自动的执行触发器。

-- 每当成功插入新员工后，自动打印“成功插入新员工”

create trigger firsttrigger
after insert
on emp
declare
begin
  dbms_output.put_line('成功插入新员工');
end firsttrigger;
/

触发器的类型

语句级触发器：在指定的操作语句之前或之后执行一次，不管这条语句影响了多少行。

行级触发器（FOR EACH ROW）：触发语句作用的每一条记录都被触发。在行级触发器中使用:old和:new伪记录变量来识别值的状态

触发器的格式

create [or replace] trigger 触发器名
{after|before} {insert|delete|update[of 列名]}
on 表名
[for each row [when(条件)]]	-- 行级触发器

-- PLSQL块
declare
begin
  dbms_output.put_line('成功插入新员工');
end;
/

触发器作用

• 1.数据确认
• 2.实施复杂的安全性检查
• 3.做审计，跟踪表上所做的数据操作等
• 4.数据的备份和同步

语句级触发器Demo

/*
实施复杂的安全性检查（作用2）
禁止在非工作时间 插入新员工

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

   -- SQL的单行函数可以在PLSQL中直接使用
   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;
/

行级触发器Demo

/*
数据的确认（作用1）
涨后的薪水不能少于涨前的薪水
*/
create or replace trigger checksalary
before update
on emp
for each row
begin
    --if 涨后的薪水 < 涨前的薪水 then
    if :new.sal < :old.sal then
       raise_application_error(-20002,'涨后的薪水不能少于涨前的薪水。涨前:'||:old.sal||'   涨后:'||:new.sal);
    end if;
end checksalary;
/

 练习题（学会：new的使用）

create or replace trigger limitempcount
  before insert
  on emp 
  for each row
declare
  -- local variables here
  count_emp number;
  
begin
  select count(*) into count_emp from emp where deptno=:new.deptno;
  
  if count_emp >= 5 then 
    raise_application_error('20001','部门号：'||:new.deptno||'已经有'||count_emp||'人');
  end if; 

end limitempcount;