Oracle提高(PL/SQL、存储过程、存储函数、触发器)

Myth丶恋晨 2022-05-31 04:56 160阅读 0赞

## PLSQL ##

1、PLSQL是什么?  
是专用于Oracle服务器，在SQL基础之上，添加了一些过程化控制语句，叫PLSQL。  
过程化包括有：类型定义，判断，循环，游标，异常或例外处理。。。  
PLSQL强调过程

2、为什么要用PLSQL  
因为SQL是第四代命令式语言，无法显示处理过程化的业务，所以得用一个过程化程序设计语言来弥补SQL的不足之处，  
SQL和PLSQL不是替代关系，是弥补关系  
/3、PLSQL程序的完整组成结构如下：  
\[declare\]  
变量声明;  
变量声明;  
begin  
DML/TCL操作;  
DML/TCL操作;  
\[exception\]  
例外处理;  
例外处理;  
end;  
/  
注意：在PLSQL程序中；号表示每条语句的结束，/表示整个PLSQL程序结束

PLSQL与SQL执行有什么不同：  
（1）SQL是单条执行的  
（2）PLSQL是整体执行的，不能单条执行，整个PLSQL结束用/，其中每条语句结束用；号

------------------------------------------------PLSQL类型
    --1.写一个PLSQL程序，输出"hello"字符串，语法：
    --dbms_output.put_line('需要输出的字符串');
    begin
        --向SQLPLUS客户端工具输出字符串
        dbms_output.put_line('hello');
    end;
    /
    
    /* 注意： dbms_output是oracle中的一个输出对象 put_line是上述对象的一个方法，用于输出一个字符串自动换行 */
    
    --2.设置显示PLSQL程序的执行结果，默认情况下，不显示PLSQL程序的执行结果，语法：set serveroutput on/off;
    set serveroutput on;
    
    --3.使用基本类型变量,常量和注释，求100+100的和
    declare
        --定义变量
        mysum number(3) := 0;
        tip varchar2(10) := '结果是';
    begin
        /*业务算法*/   
        mysum := 100 + 100;
        /*输出到控制器*/
        dbms_output.put_line(tip || mysum);
    end;
    /
    
    --4.输出7369号员工姓名和工资，格式如下：7369号员工的姓名是SMITH,薪水是800，语法：使用表名.字段%type
    declare
        --定义二个变量，分别装姓名和工资
        pename emp.ename%type;
        psal   emp.sal%type;
    begin  
        --PLSQL语句，将ename的值放入pename变量中，sal的值放入psal变量中 
        select ename,sal into pename,psal from emp where empno = 7369;
        --输出
        dbms_output.put_line('7369号员工的姓名是'||pename||',薪水是'||psal);    
    end;
    /
    
    --5.输出7788号员工姓名和工资，格式如下：7788号员工的姓名是SMITH,薪水是3000，语法：使用表名%rowtype
    declare
        emp_record emp%rowtype;
    begin
        select * into emp_record from emp where empno = 7788;
        dbms_output.put_line('7788号员工的姓名是'||emp_record.ename||',薪水是'||emp_record.sal);
    end;
    /
    
    /* 何时使用%type，何时使用%rowtype？ 当定义变量时，该变量的类型与表中某字段的类型相同时，可以使用%type 当定义变量时，该变量与整个表结构完全相同时，可以使用%rowtype，此时通过变量名.字段名，可以取值变量中对应的值 项目中，常用%type */
    
    ------------------------------------------PLSQL判断
    --6.使用if else end if显示今天星期几，是 工作日 还是 休息日
    declare
        pday varchar2(10);
    begin
        select to_char(sysdate,'day') into pday from dual;
        dbms_output.put_line('今天是'||pday);
        if pday in ('星期六','星期日') then
        dbms_output.put_line('休息日');
        else
        dbms_output.put_line('工作日');
        end if;
    end;
    /
    
    --7.从键盘接收值，使用if-elsif-else-end if显示"age<16"，"age<30"，"age<60"，"age<80"
    declare
        age number(3) := &age;
    begin
        if age < 16 then
           dbms_output.put_line('未成人');
        elsif age < 30 then
           dbms_output.put_line('青年人');
        elsif age < 60 then
           dbms_output.put_line('中年人');
        elsif age < 80 then 
           dbms_output.put_line('老年人');
        else
           dbms_output.put_line('。。。');
        end if;
    end;
    /
    
    ---------------------------------------PLSQL循环
    --8.使用loop循环显示1-10
    declare
        i number(2) := 1;
    begin
        loop
            --当i>10时，退出循环
            exit when i>10;
            --输出i的值
            dbms_output.put_line(i);
            --变量自加
            i := i + 1;  
        end loop;
    end;
    /
    
    --9.使用while循环显示1-10
    declare
        i number(2) := 1;
    begin
        while i<11 
        loop
            dbms_output.put_line(i);
            i := i + 1;
        end loop;
    end;
    /
    
    --10.使用while循环，向emp表中插入999条记录
    declare
        i number(4) := 1;
    begin 
        while( i < 1000 )
        loop
            insert into emp(empno,ename) values(i,'哈哈');
            i := i + 1;
        end loop;   
    end;
    /
    
    --11.使用while循环，从emp表中删除999条记录
    declare
        i number(4) := 1;
    begin 
        while i<1000
        loop
            delete from emp where empno = i;
            i := i + 1;
        end loop;
    end;
    /
    
    --12.使用for循环显示20-30
    declare
        i number(2) := 20;
    begin
        for i in 20 .. 30
        loop
            dbms_output.put_line(i);
        end loop;
    end;
    /
    
    -----------------------------------------PLSQL游标
    
    /* 什么是光标 cursor 类似于JDBC中的ResultSet对象的功能，从上向下依次获取每一记录的内容 用于存储一个查询返回的多行数据 */
    
    --1.使用无参光标cursor，查询所有员工的姓名和工资【如果需要遍历多条记录时，使用光标
    --cursor，无记录找到使用cemp%notfound】
    declare
        --定义游标
        cursor cemp is select ename,sal from emp;
        --定义变量
        vename emp.ename%type;
        vsal   emp.sal%type;
    begin
        --打开游标，这时游标位于第一条记录之前
        open cemp;
        --循环
        loop
           --向下移动游标一次
           fetch cemp into vename,vsal; 
           --退出循环,当游标下移一次后，找不到记录时，则退出循环
           exit when cemp%notfound;
           --输出结果
           dbms_output.put_line(vename||'--------'||vsal);
        end loop;
        --关闭游标
        close cemp;
    end;
    /
    
    --2.使用带参光标cursor，查询10号部门的员工姓名和工资
    declare
        cursor cemp(pdeptno emp.deptno%type) is select ename,sal from
          emp where deptno=pdeptno;
        pename emp.ename%type;
        psal emp.sal%type; 
    begin 
        open cemp(&deptno);
        loop
            fetch cemp into pename,psal;     
            exit when cemp%notfound;
            dbms_output.put_line(pename||'的薪水是'||psal);
        end loop;
        close cemp;
    end;
    /
    
    --3.使用无参光标cursor，真正给员工涨工资，ANALYST涨1000，MANAGER涨800，
    --其它涨400，要求显示编号，姓名，职位，薪水
    declare
        cursor cemp is select empno,ename,job,sal from emp;
        pempno emp.empno%type;
        pename emp.ename%type;
        pjob   emp.job%type;
        psal   emp.sal%type;
    begin
        open cemp;
        loop
            fetch cemp into pempno,pename,pjob,psal;
            --循环退出条件一定要写
            exit when cemp%notfound;
            if pjob='ANALYST' 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;
        commit;
        close cemp;
    end;
    /
    
    ----------------------------------------------PLSQL例外
    
    
    --1.使用oracle系统内置例外，演示除0例外【zero_divide】
    declare
        myresult number;
    begin
        myresult := 1/0;
        dbms_output.put_line(myresult);
    exception
        when zero_divide then 
         dbms_output.put_line('除数不能为0');
         delete from emp;  
    end;
    /
    
    --2.使用oracle系统内置例外，查询100号部门的员工姓名，演示没有找到数据【no_data_found】
    declare
        pename varchar2(20);
    begin
        select ename into pename from emp where deptno = 100;
        dbms_output.put_line(pename);
    exception
        when NO_DATA_FOUND then 
         dbms_output.put_line('查无该部门员工');
         insert into emp(empno,ename) values(1111,'ERROR');
    end;
    /

## 存储过程与存储函数 ##

**什么是存储过程和存储函数？**  
指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数。  
一般来讲，过程和函数的区别在于函数至少有一个返回值；而过程至少没有返回值。  
**为什么要用存储过程？**  
（1）PLSQL每次执行都要整体运行一遍，才有结果  
（2）PLSQL不能将其封装起来，长期保存在oracle服务器中  
（3）PLSQL不能被其它应用程序调用，例如：Java

--1.无参数存储过程
    create or replace procedure pro_helloWorld as begin dbms_output.put_line('Hello World.');
    end;
    /
    
    --调用方式1：直接在plsql块中执行
    begin pro_helloWorld;
    end;
    /
    --调用方式2：直接使用exec执行存储过程
    exec pro_helloWorld;
    
    
    /* 2.使用有输入参存储过程：通过存储过程新增数据到emp表 */
    create or replace procedure pro_emp_ins( p_empno in emp.empno%type, p_ename in emp.ename%type, p_sal number ) as begin --通过传入进来的3个参数新增一条数据到emp表 insert into emp(empno, ename, sal) values(p_empno, p_ename, p_sal);
      commit;
    end;
    /
    
    begin pro_emp_ins(1111, 'WWW1111', 1111);
      pro_emp_ins(2222, 'WWW2222', 2222);
      pro_emp_ins(3333, 'WWW3333', 3333);
    end;
    /
    
    /* 3.使用有输出参存储过程，计算1到10的总和并通过参数返回 */
    create or replace procedure pro_1to10_sum( p_sum out number ) as tem_sum number(2):=0;
    begin for i in 1..10 loop tem_sum := tem_sum + i;
     end loop;
     --将总和赋值给输出类型的返回参数
     p_sum := tem_sum;
    end;
    /
    
    declare
      p_sum number(2);
    begin pro_1to10_sum(p_sum);
      --输出通过存储过程设置的变量的值
      dbms_output.put_line('1-10的总和为：' || p_sum);
    end;
    /
    
    /* 4.使用有输入、输出参存储过程；根据empno查询该员工号对应的员工的姓名和工资 */
    create or replace procedure pro_query_enameAndSal_by_empno( p_empno emp.empno%type, p_ename out emp.ename%type, p_sal out emp.sal%type ) as begin select ename,sal into p_ename, p_sal from emp where empno= p_empno;
    end;
    /
    
    --调用上述的存储过程
    declare
    s_empno emp.empno%type:=7499;
    s_ename emp.ename%type;
    s_sal emp.sal%type;
    begin pro_query_enameAndSal_by_empno(s_empno, p_sal => s_sal, p_ename => s_ename);
      dbms_output.put_line('员工号为：'|| s_empno ||'，姓名为：'|| s_ename ||'，工资为：'|| s_sal);
    end;
    /
    
    --5.删除存储过程
    drop procedure pro_1to10_sum;
    
    /* 6.使用无参存储函数；注意创建时函数名称不能使用() 但是在调用时候可加可不加() */
    create or replace function fun_helloWorld return VARCHAR2 as begin return 'Hello World';
    end;
    /
    
    --调用方式1：直接使用select
    select fun_helloworld() from dual;
    
    --调用方式2：使用plsql语句块调用
    begin dbms_output.put_line(fun_helloworld());
    end;
    /
    
    /* 7.使用存储函数：根据员工号，查询并返回该员工的年薪 */
    create or replace function fun_get_annualSal_by_empno(p_empno emp.empno%type) return NUMBER as p_sal emp.sal%type;
    p_comm emp.comm%type;
    begin select sal,nvl(comm,0) into p_sal, p_comm from emp where empno= p_empno;
      return 12* p_sal + p_comm;
    end;
    /
    
    select fun_get_annualSal_by_empno(7369) from dual;
    
    declare
    temSal number(10,2);
    begin temSal := fun_get_annualSal_by_empno(7499);
     dbms_output.put_line(temSal);
    end;
    /
    
    /* 8.使用具有输入输出参数的存储函数：根据员工号，查询并返回该员工的年薪,姓名，奖金 */
    create or replace function fun_get_annualSal_by_empno2( p_empno emp.empno%type, p_ename out emp.ename%type, p_comm out number ) return number as p_sal emp.sal%type;
    begin select ename,sal,nvl(comm,0) into p_ename, p_sal, p_comm from emp where empno= p_empno;
     return 12* p_sal + p_comm;
    end;
    /
    
    --调用上述函数
    declare
    s_ename emp.ename%type;
    s_comm emp.comm%type;
    s_annualSal number(10,2);
    begin s_annualSal := fun_get_annualSal_by_empno2(7499, s_ename, s_comm);
      dbms_output.put_line('7499员工姓名为：'|| s_ename||'，奖金为：'|| s_comm||'，年薪：'||s_annualSal);
    end;
    /

## 触发器 ##

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

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

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

/* 1.触发器使用：给员工涨工资（涨后工资应该大于涨前）后，在后台输出更新前和更新后的工资 */
    create or replace trigger tri_emp_upd_sal after update of sal on emp for each row --每行进行调整工资后执行一次 begin if :old.sal < :new.sal then dbms_output.put_line('更新前工资为：'||:old.sal||'更新后工资为：'||:new.sal);
      else
        raise_application_error(-20003, '工资不能越涨越低！');
      end if;
    end;
    /
    
    -- 更新工资并触发触发器
    update emp set sal = sal + 100 where empno = 7499;
    
    /* 2.触发器使用：给emp表的empno添加触发器，在插入记录时自动填入值 */
    create or replace trigger tri_emp_ins_empno before insert on emp for each row begin --对将要插入数据库中emp表的数据进行处理 select emp_empno_seq.nextval into :new.empno from dual;
    end;
    /
    
    insert into emp(ename,sal) values('www2016',2000);
    insert into emp(ename,sal) values('www2017',2500);
    insert into emp(ename,sal) values('www2018',3000);
    
    commit;
    
    select emp_empno_seq.currval from dual;
    
    create table myemp_bak as select * from emp where 1=2;
    /* 触发器使用：删除表的同时备份表数据到另一张备份表 */
    create or replace trigger tri_myemp_del before delete on emp begin --在删除之前将数据备份到备份表 insert into myemp_bak select * from emp;
    end;
    /
    
    delete from emp;
    
    select * from emp;
    
    select * from myemp_bak;
    
    --禁用触发器
    alter trigger tri_emp_upd_sal disable;
    
    --开启触发器
    alter trigger tri_emp_upd_sal enable;
    
    --禁用表的所有触发器
    alter table emp disable all triggers;
    
    --开启表对应的所有触发器
    alter table emp enable all triggers;
    
    --删除触发器
    drop trigger tri_myemp_del;