PL/SQL

1.什么是PL/SQL,有什么作用
  --Procedure Language/SQL 过程语言+SQL
  --PL/SQL是Oracle数据库内部存储和运行
    一段程序
  --PL/SQL是Oracle在SQL语句基础上扩展了
   一些判断,循环等编程元素.可以实现一些
   复杂的运算和逻辑功能.
  --PL/SQL适合大量数据运算.

2.PL/SQL基本结构
   DECLARE
     ....//变量声明区
   BEGIN
     ....//主要处理逻辑;循环,判断+SQL
   EXCEPTION 
     ....//异常处理逻辑
   END;//结束

  set serveroutput on;//打开oracle控制台输出
  //最简单的pl/sql
  begin
     dbms_output.put_line('Hello PL/SQL');
  end;
  注意:pl/sql语句块是以"/"作为结束运行标识.
上面结构的PL/SQL程序段,只编译运行一次,
不会在Oracle存储,如果需要存储可以写成
存储过程,触发器等元素.

3.PL/SQL基本语法
   1)注释
      -- 单行注释内容
      /*
         多行注释内容
      */
   2)变量定义和赋值操作
      变量名 类型;
      a number(5);//a为null
      b number(3) := 100;//b为100

    注意:变量定义,如果没有指定值,默认为null.
赋值语句为:=格式. =号用于判断比较是否相等.

    declare
        a number(5) := 100; --定义变量a赋值100
        b number(5) :=200; --定义变量a赋值300
        c number(5);
    begin
        c := a+b; --将a+b结果给c赋值
        dbms_output.put_line('结果是:' || c);
    end;

   3)if判断
     if 条件 then
        ...
     elsif 条件 then
        ...
     else
        ...
     end if;

    ------------------
    declare
       sex varchar2(2) := 'F';
    begin
       if sex='M' then
          dbms_output.put_line('Male');
       elsif sex='F' then
          dbms_output.put_line('Female');
       else
           dbms_output.put_line('Yao!!!');
       end if;
    end;
    //有a,b,c三个number(3)变量,如果a>b,c=1;
如果a=b,c=0;如果a<b,c=-1;最后将c打印输出
   declare
      a number(3) :=300;
      b number(3) :=200;
      c number(3);
   begin
       if a>b then
          c:=1;
      elsif a=b then
          c:=0;
      else
          c:=-1;
      end if;
      dbms_output.put_line('a,b比较结果:' || c);
   end;

  4)循环
    提供了3种循环语法;
   a.loop循环
    declare
       i number(2) :=1;
    begin
       loop   --循环开始标记
          dbms_output.put_line(i);
          i:=i+1;
          exit when i>10; --指定结束循环条件
       end loop; --循环结束标记
    end;
   ---------------------
      declare
          i number(3) :=1;
          s number(11) := 0;
              --合计变量,指定初值0,否则为null,
              --null和其他数累加结果还是null
      begin
          loop
             s := s+i;
             i:=i+1;
             exit when i>100;
          end loop;
          dbms_output.put_line(s);
      end;
  
    b.while循环
      declare
         i number(3):=1;
      begin
         while i<=10 loop --满足条件进入循环;不满足退出循环
            dbms_output.put_line(i);
            i:=i+1;
         end loop;--循环结束标记
      end;
  
    c.for循环
     begin
        for i in 1..10 loop --循环1到10输出
           dbms_output.put_line(i);
        end loop;--循环结束标记
     end;
   FOR循环使用注意事项:
-- 循环变量i不需要定义,会根据集合元素自动定义
-- in 后面的集合只适用于数值范围
  5)PL/SQL使用DML和TCL事务语句
   DML和TCL语句可以直接在PL/SQL程序中
使用,没有任何特殊要求.
   begin
      --向dept表插入一条记录
      insert into dept(deptno,dname,loc)
     values (50,'testing','beijing');
      --删除编号为60的部门
      delete from dept where deptno=60;
      --提交事务
      commit;
   end;
   6)PL/SQL使用DDL语句
     先前学习的create tabe...,drop table,alter table
语句必须采用下面格式:
     execute immediate 'DDL语句';
------------------
    begin
        execute immediate 'create table emp_bak (EMPNO number(11),ENAME varchar2(20),SAL number(9,2))';
    end;
-----------------------
   begin
       --建表
       execute immediate
              'create table c001 (c1 number(5))';
       --插入记录
       insert into c001 (c1) values (5);
       commit;--提交事务
   end;
  上面PL/SQL编译会发生错误,因为编译insert
语句时,找不到c001表.
   execute immediate 'ddl语句';在编译时将dll语句
当作普通的字符串.执行时将字符串当作sql指令执行
-----------------------------
   begin
       --建表
       execute immediate
              'create table c001 (c1 number(5))';
       --插入记录
       execute immediate
             'insert into c001 (c1) values (5)';
       commit;--提交事务
   end;

     execute immediate
        'insert into c002 (c1) values ('abc')';
   使用:chr()函数,chr(39)将ascii=39字符返回. 
    execute immediate
        'insert into c002 (c1) values ('||chr(39)||'abc'||chr(39)||')';
--------------------------------
   7)PL/SQL使用DQL查询操作
     a)有且只有一条结果(使用select语句)
      使用select语句必须按照下面结构使用
       select 字段... into 变量...
       from 表 where 条件;
      上面语句可以将查询出的字段值给变量.
   declare
      --v_deptno number(2);
      --v_dname varchar2(14);
      v_deptno dept.deptno%TYPE;
      v_dname dept.dname%TYPE;
   begin
       --查询将结果给变量
       select deptno,dname into v_deptno,v_dname
       from dept where deptno=30;
       --显示变量值
       dbms_output.put_line(v_deptno||v_dname);
   end;

    使用%TYPE定义变量.格式如下:
--v_deptno类型大小与dept表的deptno字段一致
v_deptno dept.deptno%TYPE;
--v_dname类型大小与dept表的dname字段一致
v_dname dept.dname%TYPE;


   b)如果返回0或多个结果(使用CURSOR游标)
   CURSOR使用步骤:
  --在declare定义游标
    CURSOR 游标名 IS 原有select语句;
  --在begin中使用
    open 游标名;//打开游标,执行select语句.
    循环获取数据进行运算;
    fetch 游标名 into 变量;
    close 游标名;   
========================
declare
   cursor c_dept_cursor is
      select deptno,dname from dept order by deptno;
   v_deptno dept.deptno%type;
   v_dname dept.dname%type;
begin
   open c_dept_cursor;--打开游标
   loop
       --向下移动指针,抓取记录
       fetch c_dept_cursor into v_deptno,v_dname;
       --判断是否该结束循环
       exit when c_dept_cursor%NOTFOUND;
        --打印变量值
        dbms_output.put_line(v_deptno||'  '||v_dname);
   end loop;
    close c_dept_cursor;--关闭游标
end;
=========游标变量数据过程===========
--游标初始状态,指针指向第一行记录上方
--当使用fetch抓取记录时,会向下移动游标指针,
   然后将指针指向的记录取出
--当指针指向最后一条记录时,再执行fetch,
指针会停留在最后一行,但是会将%NOTFOUND
属性值改为true;
=================================
declare
  cursor c_dept_cursor is
    select deptno,dname from dept order by deptno;
  v_deptno dept.deptno%type;
  v_dname dept.dname%type;
begin
  open c_dept_cursor;
  --初始指针指向第一行记录上方,没有记录
  --因此需要fetch向下移动指针
  fetch c_dept_cursor into v_deptno,v_dname;
  while c_dept_cursor%FOUND loop
      dbms_output.put_line(v_deptno||' '||v_dname);
      fetch c_dept_cursor into v_deptno,v_dname;
  end loop;
  close c_dept_cursor;
end;

=========%ROWTYPE============
v_dept dept%ROWTYPE;--记录类型变量
v_dept是一个记录类型,其中有多个字段,
字段个数,名称,类型,大小与dept表一致.
它可以存储dept中的一行记录.
利用"v_dept.字段名"可以访问记录数据.

declare
   cursor c_dept_cursor is
     select * from dept order by deptno;
   --v_dept变量和dept字段数量,类型,大小一致
   v_dept dept%ROWTYPE;--记录类型
begin
   open c_dept_cursor;
   loop
      fetch c_dept_cursor into v_dept;
      exit when c_dept_cursor%NOTFOUND;
      dbms_output.put_line(
            v_dept.deptno||' '||v_dept.dname);
   end loop;
   close c_dept_cursor;
end;

PL/SQL中匿名块不能存储在Oracle,如果需要
存储在Oracle上,需要定义成存储过程,函数,
触发器,包等对象元素.
1.存储过程
CREATE [OR REPLACE]  PROCEDURE 过程名
[(参数 IN | OUT | IN OUT 类型,参数 类型...)]
IS|AS
  --原declare声明区
BEGIN
  --主处理区
EXCEPTION
  --异常处理区
END;
  -----示例1------
create or replace procedure helloworld
is
  v_count number(11);
begin
   --统计dept数量显示
   select count(*) into v_count from dept;
   dbms_output.put_line('dept toal:' || v_count);
end;
----------调用----------
set serveroutput on;--先打开控制台显示
exec helloworld();或 call helloworld();或匿名块

------示例2--------
根据传入的empno显示名字和工资(工资+奖金)
--参数类型不要指定大小,名字不要与字段冲突
create or replace procedure show_emp
(no in number)
is
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
begin
   select ename,sal+NVL(comm,0)
   into v_ename,v_sal
   from emp
   where empno=no;
   dbms_output.put_line(v_ename || '  ' || v_sal);
end;

------示例3-------
传入两个整数,传出合计结果和差值结果
create or replace procedure sumsub
(v_i in number,v_j in number,
v_sum out number,v_sub out number)
is
begin
    v_sum := v_i+v_j;    --求和
    v_sub := v_i-v_j;      --求差
end;
--------测试-----------
declare
v_sum number;
v_sub number;
begin
--调用存储过程,in参数给常量,out参数给变量
sumsub(10,50,v_sum,v_sub);
dbms_output.put_line('sum:'||v_sum);
dbms_output.put_line('sub:'||v_sub);
end;
/
---------综合练习-------------
基于EMP表,根据EMP员工记录和工资信息
利用PL/SQL语句向EMP_TAX员工纳税记录表
生成数据信息.
EMP_TAX表结构如下:
EMPNO (员工编号)
ENAME (员工名)
SALARAY(工资+奖金)
TAX  (纳税金额)
TAX_DATE(纳税日期-系统日期)
create table emp_tax(
empno number(4) ,
ename varchar2(10),
salary number(7,2),
tax number(7,2),
tax_date date);
-----------扣税规则--------------
工资+奖金=总金额按一下规则计算
2000(包含)以下不扣税,2000为免税基数
2000-3000之间的超出部分按5%扣税
3000(包含)以上超出部分按10%扣税

2500-->500*5%
3500-->1000*5%+500*10%

declare
  CURSOR c_emp_cursor IS
    select * from emp; --定义游标
  v_emp emp%ROWTYPE; --存储一行游标记录
  v_salary number(7,2) :=0.0 ;  --存储工资合计
  v_tax number(7,2) :=0.0; --存储缴税金额
begin
   open c_emp_cursor;
   loop --循环抓取员工记录
       fetch c_emp_cursor into v_emp;
         exit when c_emp_cursor%NOTFOUND;
       --计算该员工工资合计
       v_salary := v_emp.sal + NVL(v_emp.comm,0);
       --计算应缴税金额
       if v_salary <= 2000 then
          v_tax := 0.0;
       elsif v_salary <3000 then
          v_tax := (v_salary-2000)*0.05;
       else
           v_tax := (v_salary-3000)*0.1+1000*0.05;
       end if;
       --将该员工缴税信息插入emp_tax表
       insert into emp_tax
           (empno,ename,salary,tax,tax_date)
       values (v_emp.empno,v_emp.ename,v_salary,v_tax,sysdate);
   end loop;
   commit; --提交事务
   close c_emp_cursor;
end;

---------将上述匿名块改成存储过程---------
将declare关键字改为
create or replace procedure emp_tax_pro
is
  CURSOR c_emp_cursor IS
    select * from emp; --定义游标
  v_emp emp%ROWTYPE; --存储一行游标记录
  v_salary number(7,2) :=0.0 ;  --存储工资合计
  v_tax number(7,2) :=0.0; --存储缴税金额
begin
   open c_emp_cursor;
   loop --循环抓取员工记录
       fetch c_emp_cursor into v_emp;
         exit when c_emp_cursor%NOTFOUND;
       --计算该员工工资合计
       v_salary := v_emp.sal + NVL(v_emp.comm,0);
       --计算应缴税金额
       if v_salary <= 2000 then
          v_tax := 0.0;
       elsif v_salary <3000 then
          v_tax := (v_salary-2000)*0.05;
       else
           v_tax := (v_salary-3000)*0.1+1000*0.05;
       end if;
       --将该员工缴税信息插入emp_tax表
       insert into emp_tax1
           (empno,ename,salary,tax,tax_date)
       values (v_emp.empno,v_emp.ename,v_salary,v_tax,sysdate);
   end loop;
   commit; --提交事务
   close c_emp_cursor;
end;

2.函数
  数据库本身提供了很多函数,例如字符串处理,
数值处理,日期函数等.当Oracle提供的不能满足
需求时,可以自定义函数.
   CREATE [OR REPLACE] FUNCTION 函数名
   [(参数 类型,参数 类型)]
   RETURN 返回类型
   IS或AS
   PL/SQL语句块
注意:函数必须得有返回值.参数都是IN传入模式
----------示例1------------
sign(n):Oracle提供的判断n值的符号(>0,<0,=0).
自定义一个mysign,功能与sign一致.
create or replace function mysign(n number)
return number    --定义返回类型
is
v_f number := 0;
begin
   --根据n参数变量值,判断
   if n>0 then
      v_f := 1;
   elsif n=0 then
      v_f := 0;
   else
       v_f := -1;
   end if;
   return v_f;
end;

--------示例2----------
根据指定的empno值返回工资合计值
create or replace function get_sal
(v_empno emp.empno%TYPE)
return number
is
v_total_sal number(7,2) :=0.0; --存储工资合计
begin
select sal+NVL(comm,0) into v_total_sal
from emp where empno=v_empno;
return v_total_sal; --返回合计结果
end;
------------删除过程和函数--------------
drop function xxx; --删除函数
drop procedure xxx; --删除存储过程
-------------示例3--------------------
自定义函数,传入工资合计值,返回应缴税额.
纳税逻辑参考存储过程部分的综合练习.
create or replace function get_tax
(v_salary number) return number
is
v_tax number(7,2) :=0.0; --存储应纳税额
begin
       if v_salary <= 2000 then
          v_tax := 0.0;
       elsif v_salary <3000 then
          v_tax := (v_salary-2000)*0.05;
       else
           v_tax := (v_salary-3000)*0.1+1000*0.05;
       end if;
       return v_tax;
end;
--------基于get_tax函数编写纳税存储过程------------
create or replace procedure emp_tax_pro
is
  CURSOR c_emp_cursor IS
    select * from emp; --定义游标
  v_emp emp%ROWTYPE; --存储一行游标记录
  v_salary number(7,2) :=0.0 ;  --存储工资合计
  v_tax number(7,2) :=0.0; --存储缴税金额
begin
   open c_emp_cursor;
   loop --循环抓取员工记录
       fetch c_emp_cursor into v_emp;
         exit when c_emp_cursor%NOTFOUND;
       --计算该员工工资合计
       v_salary := v_emp.sal + NVL(v_emp.comm,0);
       --计算应缴税金额
      v_tax := get_tax(v_salary);
       --将该员工缴税信息插入emp_tax表
       insert into emp_tax
           (empno,ename,salary,tax,tax_date)
       values (v_emp.empno,v_emp.ename,v_salary,v_tax,sysdate);
   end loop;
   commit; --提交事务
   close c_emp_cursor;
end;

3.触发器
   1)触发器分类
DML触发器:由insert,update,delete动作触发调用
系统触发器:由登录,登出Oracle系统事件触发调用
2)DML触发器的使用
    a.语句级触发器
一个insert或update或delete语句自动触发一次.
  --------示例1----------
每次执行emp表的insert,update,delete操作,
都会自动统计出emp中员工总人数,
平均工资数,工资合计信息
create or replace trigger  emp_trigger1
after insert or update or delete on emp
declare
   v_count number(7) := 0; --存储员工人数
   v_avg number(7,2) := 0.0; --存储平均工资
   v_sum number(11,2) :=0.0; --存储工资总额
begin
   select count(*),avg(sal),sum(sal)
   into v_count,v_avg,v_sum
   from emp;
   dbms_output.put_line('员工人数' || v_count);
   dbms_output.put_line('平均工资' || v_avg);
   dbms_output.put_line('工资总额' || v_sum);
end;


    *b.行级触发器
insert,update,delete语句影响的每一行记录都要
自动触发一次.
可以在触发器中获取操作影响的记录信息.
在行级触发器中,可以使用下面两个关键字
:NEW 表示执行SQL之后的记录状态
:OLD 表示执行SQL之前的记录状态

insert操作只能使用:NEW,表示要插入的记录
delete操作只能使用:OLD,表示要删除之前的记录
update操作可以使用:NEW和:OLD, :NEW表示
更新之后的记录;:OLD表示更新之前的记录
----------示例1------------
将emp表删除的记录,存入emp_bak备份表.
create or replace trigger emp_trigger2
before delete on emp for each row
declare
begin
    --将要删除的记录empno,ename,sal写入emp_bak
    --OLD表示即将要删除的EMP记录
    insert into emp_bak(empno,ename,sal)
       values (:OLD.empno,:OLD.ename,:OLD.sal);
end;
注意:触发器中不要使用事务提交操作.

4.异常处理
begin
....
exception
....//异常处理区
end;
------------------
exception
  when  异常类型 then
      .......
  when 异常类型 then
      .......
  when others then
      .......
--------------------
1)异常分类
  a.预定义异常
    在Oracle内部,提前将错误编码和特定的
异常类型名关联.由Oracle自动触发该异常.
    exception
       when no_data_found then
           dbms_output.put_line('没有记录');
  -------示例----------
  declare
      v_sal number(7,2);
   begin
       select sal into v_sal from emp
       where empno=7654;
       dbms_output.put_line(v_sal);
   exception
       when too_many_rows then
           dbms_output.put_line('返回记录太多啦');
       when others then
           dbms_output.put_line('出错啦,原因自己找吧');
   end;


  b.非预定义异常
    需要在程序中将错误编码和定义的异常类型
关联.由Oracle自动触发该异常
    myexception exception;--定义类型名
    PRAGMA EXCEPTION_INIT
     (myexception,-2292);
   ----------------------
    exception
        when myexception then
             dbms_output.put_line('错误')
  c.自定义异常
    没有错误编号,违反业务需求时,程序员自定义
一个异常类型,在程序中使用raise 异常类型抛出.
由程序定义和触发.
      no_update_exception exception;
   begin
      ......
      raise no_update_exception;//抛出异常
      .....
   exception
      when no_update_exception then
         dbms_output.put_line('没有更新记录');
   end;