Oracle学习（三）

一时失言乱红尘 2021-12-21 10:39 214阅读 0赞

# Oracle学习（三） #

主要学习了PL/SQL编程语言、存储过程、存储函数、java调用存储过程和触发器

### 文章目录 ###

*  Oracle学习（三）
 *   *  PL/SQL编程语言
     *  存储过程
     *  存储函数
     *  java调用存储过程
     *  触发器

## PL/SQL编程语言 ##

输出Hello Word

declare
      --说明部分
    begin
      --程序
      dbms_output.put_line('Hello World');
    end;

引用型变量：查询并打印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;

记录型变量: 查询并打印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;

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

--接受键盘输入
    --变量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;

循环：打印1~10

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

光标：查询并打印员工的姓名和薪水

/*
    光标的属性： %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
      rollback;  
    
      --打开光标
      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;

系统异常：被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号部门的员工

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

给每个员工涨薪10%，但涨薪总额不能超过5w

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

统计某个部门的薪资水平：小于3000，3000~6000，6000以上

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

/*调用存储过程：
    1、exec sayhelloworld();
    2、begin
    	sayhelloworld();
    	sayhelloworld();
       end;
    */
    
    create or replace procedure sayhelloworld
    as
       --说明部分
    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？
           
           dbms_output.put_line('涨前:'||psal||'   涨后：'||(psal+100));
    end raiseSalary;

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

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

查询某个部门中的所有员工信息 ----> 返回的是集合

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

## 存储函数 ##

查询某个员工的年收入

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调用存储过程 ##

JDBC工具类封装

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不受代码的控制
    			}
    		}
    		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;
    			}
    		}
    	}
    }

调用存储过程

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);
    		}
    	}
    
    /* * 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);
    		}		
    	}
    
    
    	@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);
    		}		
    		
    	}
    }

## 触发器 ##

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

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

禁止在非工作时间 插入新员工

/*
    实施复杂的安全性检查
    禁止在非工作时间 插入新员工
    
    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;

涨后的薪水不能少于涨前的薪水

/*
    数据的确认
    涨后的薪水不能少于涨前的薪水
    */
    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;