PL/SQL 总结

一、.基本语法
PL/SQL的结构块如下：

declare
      --声明变量、类型、游标 
begin
        --程序执行的部分，类似main方法
exception
        --针对异常，提供如何处理的机制
        --when ....then....
end;
注意，没有用的部分，就 不需要写，比如程序的确是没有异常要处理，那么exception就不写

建议的命名方法：
标识符  命名规则 例子
程序变量  V_name V_name
程序常量  C_Name C_company_name
游标变量          Name_cursor Emp_cursor
异常标识  E_name E_too_many
表类型  Name_table_typeEmp_record_type
表  Name_table Emp
记录类型  Name_recordEmp_record
SQL*Plus替代变量  P_name P_sal
绑定变量  G_name G_year_sal

HelloWorld演示：

begin
  dbms_output.put_line('HelloWorld');
end;

输出100员工的工资：

declare
  v_sal number(20) :=0;    --声明变量，要注意的是，数据类型和要查询的表中的数据类型是对应得
			  --附一个默认值，:= 
  v_email varchar2(20);--另一种写法，v_email employees.email % type
  v_hire_date date;    --v_hire_date employees.hire_date % type   //v_hire_date与employees表中的hire_date类型一样
			--%type ，如果自定义类型的精度不够，那么%type就是动态的获取表中的类型及精度
begin 
  select salary,email , hire_date into v_sal,v_email,v_hire_date
   from employees where employee_id = 100;
  --实现select操作，注意是select into
  dbms_output.put_line(v_sal||','||v_email||','||v_hire_date);--打印
end;

修改数据

declare
     v_emp_id employees.employee_id%type;
begin
     v_emp_id :=100;
     update employees
     set salary = salary + 100
     where employee_id = v_emp_id;
     dbms_output.put_line('OK');
end; 

使用%TYPE
定义一个变量，其数据类型与已经定义的某个 数据变量的类型相同，或者与数据库表的某个列的数据类型
相同，这时可以使用%TYPE。
使用%TYPE 特性的优点在于：
  所引用的数据库列的数据类型可以不必知道；
  所引用的数据库列的数据类型可以实时改变。
使用%ROWTYPE
PL/SQL 提供%ROWTYPE  操作符,  返回一个记录类型,  其数据类型和数据库表的数据结构相一致。
使用%ROWTYPE 特性的优点在于：
  所引用的数据库中列的个数和数据类型可以不必知道；
  所引用的数据库中列的个数和数据类型可以实时改变。
二、记录类型
type ... is record(,,);

DECLARE
  --声明一个记录类型，类似于java中的类
 TYPE emp_record IS RECORD(
      v_sal employees.salary % type,
      v_email employees.email % type,--以逗号链接
      v_hire_date employees.hire_date % type);--分号
   --定义一个记录变量
   v_emp_record emp_record;
BEGIN
      SELECT salary , email, hire_date INTO v_emp_record
      FROM employees WHERE employee_id = 101;
      dbms_output.put_line(v_emp_record.v_sal||','||v_emp_record.v_email||','||v_emp_record.v_hire_date);
END;

获取一个表中所有列的记录类型

declare
     v_emp_record employees%rowtype;
     v_emp_id number(10);
begin
     v_emp_id  := 100;
     select * into v_emp_record
     from employees
     where employee_id = v_emp_id;
     dbms_output.put_line(v_emp_record.salary||' , '||v_emp_record.last_name);--用表中的列名字即可
end; 

三、流程控制：
条件判断：（两种）
方式一：if ..then elseif then...else...end if;
方式二：case...when...then ..end;
循环结构：（三种）
方式一：loop...exit when ...end loop;
方式二：while..loop...end;
方式三：for i in ..loop ..end loop;
关键字：goto （类似java中的break后接一个标签，跳出那个循环）、 exit
条件判断：
--查询出 150号 员工的工资, 若其工资大于或等于 10000 则打印 ‘salary >= 10000‘; 
--若在 5000 到 10000 之间, 则打印 ‘5000<= salary < 10000‘; 否则打印 ‘salary < 5000‘
方式一

declare
     v_sal employees.salary%type;
     v_result varchar2(20);
begin
     select salary into  v_sal from employees where employee_id = 150;
     if v_sal >= 10000 then v_result := 'salary >= 10000';
     elsif v_sal >=5000 then v_result := 'salary >= 5000';
     else v_result := 'salary < 5000';
     end if;
     dbms_output.put_line(v_sal||' , '||v_result);
end;

方式二

declare
     v_sal employees.salary%type;
     v_result varchar2(20);
begin
     select salary into  v_sal from employees where employee_id = 150;
     v_result :=
     case trunc(v_sal/5000) when 2  then 'salary >= 10000'
                            when 1  then '5000 <= salary < 10000'
                            when 0  then 'salary < 5000'
     end;
     dbms_output.put_line(v_sal||' , '||v_result);
end;

case...when..then..相当于java的switch，使用起来有局限性，还是if..then..elsif...then比较好
注意，if..then每一次后面都要加“分号”，而case..when..then不能加，分号的问题在PL/DQL中比较坑
循环结构：
--使用循环语句打印 1 - 100.（三种方式）
--（1）初始化 （2）循环体 （3）循环条件 （4）迭代条件

declare
       --(1)
       v_i number(3) := 1;
/*方式一
begin
       loop
       --(2)
           dbms_output.put_line(v_i);
           --(4)
          v_i := v_i + 1;
       --(3)
       exit when v_i >100;
       end  loop;--注意每一行，都要有“分号”
end;*/
方式二：（推荐使用 ）
begin
       while v_i <= 100 loop
             dbms_output.put_line(v_i);
             v_i := v_i + 1;
        end loop;
end;


/*方式三
begin
        for i in 1..100 loop --for i in reverse 1..100 loop，这样就是反着，从100-1，还要注意in后面是两个“.”
            dbms_output.put_line(i);
        end loop;
end;
*/

输出1-100的所有质数

declare
  v_i number(3) := 2;
  v_j number(3) := 2;
  v_flag number(3) := 1;
begin
  /*while v_i<=100 loop
  
        while v_j <= sqrt(v_i) loop
              if mod(v_i,v_j)=0 then v_flag := 0;
              goto OK;
              end if;
          v_j := v_j + 1;
      
        end loop;
  <<OK>>
  if v_flag = 1 then dbms_output.put_line(v_i);
  end if;
  v_j := 2;
  v_i := v_i + 1;
  v_flag := 1;
  end loop;
  */
  for i in 2..100 loop
      for j in 2..sqrt(i) loop
          if mod(i,j)=0 then v_flag := 0;
          goto OK;
          end if;
      end loop;
      <<OK>>
            
      if v_flag = 1 then dbms_output.put_line(i);
      end if;
      v_flag := 1;
  end loop;      
end;

对于while 循环的嵌套。特别要注意初始化条件

四、游标的使用（类似于java的iterator）
主要方便用于处理多行数据。

declare
  --定义一个记录类型
  type v_record is record(
       v_sal employees.salary%type,
       v_last_name employees.last_name%type
  );
  --声明一个记录类型
  v_emp_record v_record;
  --定义一个游标
  cursor emp_sal_cursor is select salary,last_name from employees where department_id = 80;
begin
  --打开游标
  open emp_sal_cursor;
  --提取游标
  fetch emp_sal_cursor into v_emp_record;
  while emp_sal_cursor%found loop
        dbms_output.put_line(v_emp_record.v_last_name||' : '||v_emp_record.v_sal);
        fetch emp_sal_cursor into v_emp_record;
  end loop;
  close emp_sal_cursor;
end;

游标的使用，用for比较简单

declare
  cursor emp_sal_cursor is select salary,last_name from employees where department_id = 80;
begin


  for c in emp_sal_cursor loop 
      dbms_output.put_line(c.last_name||c.salary);
      end loop;
end;

带参数的游标

declare
    --定义游标
    cursor emp_sal_cursor(dept_id number, sal number) is 
           select salary + 1000 sal, employee_id id 
           from employees 
           where department_id = dept_id and salary > sal;
    
    --定义基数变量
    temp number(4, 2);
begin
    --处理游标的循环操作
    for c in emp_sal_cursor(sal => 4000, dept_id => 80) loop
          --判断员工的工资, 执行 update 操作
          --dbms_output.put_line(c.id || ': ' || c.sal);
          
          if c.sal <= 5000 then
             temp := 0.05;
          elsif c.sal <= 10000 then
             temp := 0.03;   
          elsif c.sal <= 15000 then
             temp := 0.02;
          else
             temp := 0.01;
          end if;
          
          dbms_output.put_line(c.sal || ': ' || c.id || ', ' || temp);
          --update employees set salary = salary * (1 + temp) where employee_id = c.id;
    end loop;
end;

隐式游标
隐式游标属性
SQL%FOUND 布尔型属性,当最近一次读记录时成功返回，则值为 TRUE；
SQL%NOTFOUND 布尔型属性,与%FOUND 相反；
SQL %ROWCOUNT 数字型属性, 返回已从游标中读取得记录数；
SQL %ISOPEN 布尔型属性, 取值总是 FALSE。SQL 命令执行完毕立即关闭隐式游标。

--更新指定员工信息，如果该员工没有找到，则打印”查无此人”信息。

declare 
  v_sal employees.salary%type;
  v_empid employees.employee_id%type := 101;
begin
  update employees
  set last_name = 'ABC'
  where employee_id = v_empid;
  if sql%notfound then --
     dbms_output.put_line('查无此人');
  end if;
end;

五、异常处理（三种类型）
异常处理概念
  异常情况处理(EXCEPTION) 是用来处理正常执行过程中未预料的事件, 程序块的异常处理预定义的错误
和 自定义错误, 由于 PL/SQL  程序块一旦产 生异常而没有指出如何处理时,程序就会自动终止整个程序运行
共有三种类型：
1.预定义 ( Predefined ) 错误
ORACLE 预定义的异常情况大约有 24 个。对这种异常情况的处理，无需在程序中定义，由 由 ORACLE  自动
将其引发。
2.非预定义 ( Predefined )错误
即其他标准的 ORACLE 错误。对这种异常情况的处理，需要用户在程序中定义，然后由 ORACLE 自动将
其引发。
3.用户定义(User_define) 错误
程序执行过程中，出现编程人员认为的非正常情况。对这种异常情况的处理， 需要 用户在程序中定义，
然后显式地在程序中将其引发。

异常处理的基本结构：

EXCEPTION
WHEN first_exception THEN <code to handle first exception >
WHEN second_exception THEN <code to handle second exception >
WHEN OTHERS THEN <code to handle others exception >
END;

异常处理可以按任意次序排列,但 OTHERS 必须放在最后.

预定义异常

declare
  v_sal employees.salary%type;
begin
  select salary into v_sal from employees
  where employee_id > 100;
  dbms_output.put_line(v_sal);
exception
  when too_many_rows then dbms_output.put_line('要输出的行数太多了!');
  when others then dbms_output.put_line('其他错误!');
end;

非预定义异常

步骤如下：
1.在 PL/SQL  块的定义部分定义异常情况：
<异常情况> EXCEPTION;
2.  将其定义好的异常情况，与标准的 ORACLE  错误联系起来，使用 PRAGMA EXCEPTION_INIT  语句：
PRAGMA EXCEPTION_INIT(< 异常情况>, < 错误代码>);
3. 在 PL/SQL  块的异常情况处理部分对异常情况做出相应的处理。

SQL>delete employees
where employee_id = 100
ORA-02292: 违反完整约束条件 (SCOTT.DEPT_MGR_FK) - 已找到子记录
因为自己表里有manager_id也指向自己表里的employee_id，而删除emp_id，因为manager_id指向，所以或无法删除

declare
  e_deleteid_exception exception;
  pragma exception_init(e_deleteid_exception,-2292);--将2292这个错和自己定义的异常名称关联起来
begin
  delete employees
  where employee_id = 100;
exception
  when e_deleteid_exception then dbms_output.put_line('违反完整性约束异常!');
end;

用户自定义的异常处理

当与一个异常错误相关的错误出现时，就会隐含触发该异常错误。 用户定义的异常错误是通过显式使
用 RAISE  语句来触发。当引发一个异常错误时，控制就转向到 EXCEPTION  块异常错误部分，执行错误处
理代码。

步骤如下：
1． 在 在 PL/SQL  块的定义部分定义异常情况：
< 异常情况> EXCEPTION;
2． RAISE < 异常情况>；
3．在 PL/SQL  块的异常情况处理部分对异常情况做出相应的处理

在非预言定义异常的基础上

declare
  e_deleteid_exception exception;
  pragma exception_init(e_deleteid_exception,-2292);
  e_too_high_sal_exception exception; --自定义异常
  v_sal employees.salary % type;
begin 
  delete from employees where employee_id = 100;
  select salary into v_sal from employees where employee_id = 100;
  if v_sal > 10000 then 
  raise e_too_high_sal_exception;   --抛出异常
  end if;
exception
  when e_too_high_sal_exception then dbms_output.put_line('工资太高了');
  when e_deleteid_exception then dbms_output.put_line('违反完整性约束异常');
  when others then dbms_output.put_line('其他异常');
end;

1-5为基础中的基础

六、存储函数和存储过程

过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。
即存储函数（有返回值）、存储过程（无返回值）

-----存储函数：

创建函数
1.  建立内嵌函数
语法如下：
CREATE [OR REPLACE] FUNCTION function_name
[ (argment [ { IN | IN OUT }] Type,
argment [ { IN | OUT | IN OUT } ] Type ]
[ AUTHID DEFINER | CURRENT_USER ]
RETURN return_type
{ IS | AS }
<类型.变量的说明>
BEGIN
FUNCTION_body
EXCEPTION
其它语句
END;

说明:
1) OR REPLACE 为可选. 有了它, 可以或者创建一个新函数或者替换相同名字的函数, 而不会出现冲突
2) 函数名后面是一个可选的参数列表, 其中包含 IN, OUT 或 IN OUT 标记. 参数之间用逗号隔开. IN  参数
标记表示传递给函数的值在该函数执行中不改变; OUT  标记表示一个值在函数中进行计算并通过该参
数传递 给调用语句; IN OUT  标记表示传递给函数的值可以变化并传递给调用语句.  若省略标记,  则参数
隐含为 IN 。
3) 因为函数需要返回一个值, 所以 RETURN 包含返回结果的数据类型。

--存储函数结构

create or replase function func_name (depart_id number, salary number)
return number --返回某部门所有的工资
is     
       --函数在使用过程中，需要声明的变量、记录类型record、游标cursor
begin
       --函数的执行体
exception
       --处理函数执行过程中的异常
end;

Hello，World演示

create or replace function HelloDemo
return varchar2
is
begin
       return 'Hello,World';
end;

显示Function created，说明函数已经创建

调用函数
方式一：

begin
  dbms_output.put_line(HelloDemo);
end;

方式二：

SQL>select HelloDemo from dual; 

/*
create or replace function HelloDemo(v_world varchar2)
return varchar2
is
begin
       return 'Hello'||v_world;
end;
*//*
select HelloDemo('Worrld') from dual
*/

获取系统时间函数

create or replace function get_sysdate
return date
is
   v_date date;
begin
       v_date := sysdate;
       return v_date;
end;

select get_date from  dual;

定义两个数相加的函数

create or replace function add_func(v_num1 number,v_num2 number)
return number
is
   v_sum number(10);--这里需要指定长度
begin
       v_sum := v_num1 + v_num2;
       return v_sum;
end;

select add_func(1,2) from dual;

获取指定部门的工资总和

create or replace function get_all_sal(dept_id number)
return number
is
   v_sumsal number(20,2) := 0;
   
   cursor salary_cursor is select salary from employees where department_id = dept_id;
begin
       for c in salary_cursor loop
       v_sumsal := v_sumsal + c.salary;
       end loop;
       
       return v_sumsal;
end;

关于OUT形的参数
因为函数只能有一个返回值，PL/SQL程序可以通过OUT参数实现多个返回值

获取指定部门的工资总和，人员总和

create or replace function get_all_sal(dept_id number,emp_num out number)--注意out这里
return number
is
   v_sumsal number(20,2) := 0;
   
   cursor salary_cursor is select salary from employees where department_id = dept_id;
begin
          emp_num := 0;
       for c in salary_cursor loop
       v_sumsal := v_sumsal + c.salary;
       emp_num := emp_num + 1;
       end loop;
       
       return v_sumsal;
end;


输出


declare
  v_num number(3):=0;
begin
  dbms_output.put_line(get_all_sal(80,v_num));
     dbms_output.put_line(v_num); --v_num就是部门人数
end;    
      
-----存储过程：


--定义存储过程，实现指定部门的工资（通过out参数），要求部门id和工资总数为参数


create or replace procedure get_sal(dept_id number,v_sal out number)--不需要返回值
is
   cursor salary_cursor is select salary from employees where department_id = dept_id;
begin
          v_sal := 0;
       for c in salary_cursor loop
       v_sal := v_sal + c.salary;
       end loop;
       dbms_output.put_line(v_sal);
end;




declare
  v_sal number(10):=0;
begin
  get_sal(80,v_sal); --直接调用即可
end;


因为存储过程不需要返回值，所以可以把增删改的操作定义存储过程






七、触发器


触发器是许多关系数据库系统都提供的一项技术。在 ORACLE 系统里， 触发器类似过程和函数，都有
声明，执行和异常处理过程的 PL/SQL


一个helloworld级别的触发器
create or replace trigger hello_trigger
after 
update on employees
--for each row
begin 
    dbms_output.put_line(‘hello...‘);
    --dbms_output.put_line(‘old.salary:‘|| :OLD.salary||‘,new.salary‘||:NEW.salary);
end;
然后执行：update employees set salary = salary + 1000;


6-7重点