Oracle的PL/SQL、过程、函数、游标、触发器

1 PL/SQL

1.1简介

PL/SQL是oracle的核心。

①PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言；
（oracle自身提供了很多程序包，也可实现邮件发送、校验等）
②PL/SQL 是对 SQL 的扩展；
③支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构；
④可用于创建存储过程、触发器和程序包，给SQL语句的执行添加程序逻辑；
⑤与 Oracle 服务器和 Oracle 工具紧密集成，具备可移植性、灵活性和安全性。

1.1.1 优点

支持 SQL，在 PL/SQL 中可以使用：

数据操纵命令
事务控制命令
游标控制

SQL 函数和 SQL 运算符；
用户把PL/SQL块整个发送到服务器端，oracle服务器端编译、运行，再把结果返回给用户（可节省网络流量）；
可移植性，可运行在任何操作系统和平台上的Oralce 数据库；
更佳的性能，PL/SQL 经过编译执行；
安全性，可以通过存储过程限制用户对数据的访问;
与 SQL 紧密集成，简化数据处理。

支持所有 SQL 数据类型
支持 NULL 值
支持 %TYPE 和 %ROWTYPE 属性类型

1.1.2 体系结构

PL/SQL 引擎驻留在 Oracle 服务器中，该引擎接受 PL/SQL 块并对其进行编译执行。

将PL/SQL 块发送给 Oracle 服务器->Oracle 服务器{[PL/SQL引擎（过程语言执行器）],[SQL引擎（SQL语句执行器）]}->将结果发送给用户

1.2 PL/SQL 块简介

PL/SQL 块是构成 PL/SQL 程序的基本单元，将逻辑上相关的声明和语句组合在一起。
PL/SQL 分为三个部分，声明部分、可执行部分和异常处理部分。

[DECLARE 
declarations]
BEGIN
executable statements
[EXCEPTION 
handlers]
END;

1.2.1 常量和变量

PL/SQL 块中可以使用变量和常量

在声明部分声明，使用前必须先声明
声明时必须指定数据类型，每行声明一个标识符
在可执行部分的 SQL语句和过程语句中使用

声明变量和常量的语法：
identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr];

给变量赋值有两种方法：
>
使用赋值语句 :=
使用 SELECT INTO 语句

• 给变量赋值的区别：

T/SQL
select @a=3
set    @a=3
select @a = count(*) from student;

PL/SQL
a := 3
select count(*) into a from student;

1.2.2 PL/SQL 支持的内置数据类型

• PL/SQL 支持的内置数据类型
  

  { 标量类型[数字、字符、布尔型、日期时间]、
  LOB类型（存储非结构化数据块）[BFILE、BLOB、CLOB、NCLOB]、
  属性类型[%TYPE(提供某个变量或数据库表列的数据类型
  )、%ROWTYPE（提供表示表中一行的记录类型 ）]}

1.2.2.1 数字数据类型

**BINARY_INTEGER** （存储有符号整数，所需存储空间少于NUMBER类型值）->NATURAL、NATURALLN、POSITIVE、POSITIVEN、SIGNTYPE **NUMBER**（存储整数、实数和浮点数）->DEMICAL、FLOAT、INTEGER、REAL **PLS_INTEGER** （存储有符号整数，可使算术计算快速而有效）

②字符数据类型

CHAR
VARCHAR2
LONG
RAW
LONG RAW

③日期时间类型

存储日期和时间数据 常用的两种日期时间类型

DATE
TIMESTAMP

④布尔数据类型

此类别只有一种类型，即BOOLEAN类型
用于存储逻辑值(TRUE、FALSE和NULL)
不能向数据库中插入BOOLEAN数据
不能将列值保存到BOOLEAN变量中
只能对BOOLEAN变量执行逻辑操作

1.2.2.2、LOB数据类型

用于存储大文本、图像、视频剪辑和声音剪辑等非结构化数据。 LOB数据类型可存储最大4GB的数据。 LOB类型包括：

BLOB 将大型二进制对象存储在数据库中
CLOB 将大型字符数据存储在数据库中
NCLOB 存储大型UNICODE字符数据
BFILE 将大型二进制对象存储在操作系统文件中

LOB类型的数据库列仅存储定位符，该定位符指向大型对象的存储位置

1.2.2.3、属性类型

用于引用数据库列的数据类型，以及表示表中一行的记录类型,属性类型有两种：

%TYPE - 引用变量和数据库列的数据类型
%ROWTYPE - 提供表示表中一行的记录类型

使用属性类型的优点：

不需知道被引用的表列具体类型
如被引用对象的数据类型发生改变，PL/SQL 变量的数据类型也随之改变，健壮性

1.2.3 逻辑比较

逻辑比较用于比较变量和常量的值，这些表达式称为布尔表达式 布尔表达式由关系运算符与变量或常量组成 布尔表达式的结果为TRUE、FALSE或NULL，通常由逻辑运算符AND、OR和NOT连接 布尔表达式有三种类型：

数字布尔型
字符布尔型
日期布尔型

declare 
  sbirth1 student2.sbirthday%type;
begin
  select sbirthday into sbirth1 from student2 where sno =1;
  if sbirth1 < to_date('19900101','yyyymmdd') then
     dbms_output.put_line('大于25岁');
  end if;
  if sbirth1 < to_date('19900101','yyyymmdd') then
     dbms_output.put_line('大于25岁');
  end if;
end;

1.2.4 控制结构

PL/SQL 支持的流程控制结构：

条件控制

IF 语句
CASE 语句

循环控制

LOOP 循环
WHILE 循环
FOR 循环

顺序控制

GOTO 语句
NULL 语句

①条件控制

IF语句根据条件执行一系列语句，有三种形式：IF-THEN、IF-THEN-ELSE 和 IF-THEN-ELSIF
CASE 语句用于根据单个变量或表达式与多个值进行比较
执行CASE 语句前，先计算选择器的值

②循环控制

循环控制用于重复执行一系列语句,循环控制语句包括： > LOOP、EXIT 和 EXIT WHEN 、FOR 、WHILE 循环控制的三种类型：

LOOP - 无条件循环
WHILE - 根据条件循环
FOR - 循环固定的次数

LOOP 
  sequence_of_statements
END LOOP;

WHILE condition LOOP 
  sequence_of_statements
END LOOP;

FOR counter IN [REVERSE] value1..value2
LOOP 
  sequence_of_statements
END LOOP;

③顺序控制

顺序控制用于按顺序执行语句, 顺序控制语句包括：

GOTO 语句 - 无条件地转到标签指定的语句
NULL 语句 - 什么也不做的空语句

④动态SQL

动态 SQL 是指在PL/SQL程序执行时生成的 SQL 语句 编译程序对动态 SQL 不做处理，而是在程序运行时动态构造语句、对语句进行语法分析并执行 DDL 语句命令和会话控制语句不能在 PL/SQL 中直接使用，但是可以通过动态 SQL 来执行 执行动态 SQL 的语法：

EXECUTE IMMEDIATE dynamic_sql_string
[INTO define_variable_list]
[USING bind_argument_list];

含有ddl的时候，要使用动态sql。 eg.

begin 
  execute immediate 'create table T(t1 int)'
end;

1.3 错误处理

在运行程序时出现的错误叫做异常 发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分 异常有两种类型：

预定义异常 - 当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发
用户定义异常 - 用户可以在 PL/SQL 块的声明部分定义异常，自定义的异常通过 RAISE 语句显式引发

• RAISE_APPLICATION_ERROR 过程

  用于创建用户定义的错误信息
  可以在可执行部分和异常处理部分使用
  错误编号必须介于 –20000 和 –20999 之间
  错误消息的长度可长达 2048 个字节

• 引发应用程序错误的语法：
  RAISE_APPLICATION_ERROR(error_number, error_message);

2 存储过程

命名的 PL/SQL 块，编译并存储在数据库中。

2.1 子程序

子程序的各个部分：

声明部分
可执行部分
异常处理部分(可选)

子程序的分类：

过程 － 执行某些操作
函数 － 执行操作并返回值

• 子程序的优点：
  

  模块化:将程序分解为逻辑模块;
  可重用性:可以被任意数目的程序调用;
  可维护性:简化维护操作;
  安全性:通过设置权限，使数据更安全.

2.2 过程

创建过程的语法（和C语言很相似。or replace可以省略。）：

CREATE [OR REPLACE] PROCEDURE 
   <procedure name> [(<parameter list>)]
IS|AS 
   <local variable declaration>
BEGIN
   <executable statements>
[EXCEPTION
   <exception handlers>]
END;

• 过程参数的三种模式：

  IN：用于接受调用程序的值，默认的参数模式；
  OUT：用于向调用程序返回值；
  IN OUT：用于接受调用程序的值，并向调用程序返回更新的值。

• 执行过程的语法：
  EXECUTE procedure_name(parameters_list);

-- IN示范
create or replace procedure proc1(i in number)
as
  a varchar2(50);
begin
  a := '';
  for j in 1..i loop
    a := a || '*';
    dbms_output.put_line(a);
  end loop;
end;
/

exec proc1(6);
---------------------------------------------------------------------
-- OUT示范
create or replace procedure proc2(j out int)
as
begin
  j := 100;
  dbms_output.put_line(j);
end;

--只能在另外一个pl/sql段调用:
declare
  k number;
begin
  proc2(k);
end;

--在sql*plus运行存储过程，要加‘exec’，而在pl/sql中可以直接调用。
---------------------------------------------------------------------
-- IN OUT示范
create or replace procedure proc3(p1 in out number, p2 in out number)
as
  v_temp number;
begin
  v_temp := p1;
  p1 := p2;
  p2 := v_temp;
end;

declare
  num1 number := 100;
  num2 number := 200;
begin
  proc3(num1,num2);
  dbms_output.put_line(num1);
  dbms_output.put_line(num2);
end;
---------------------------------------------------------------------
show user
exec scott.proc1(6)没有权限
赋权限
grant execute on proc1 to zsr

• 将过程的执行权限授予其他用户：

GRANT EXECUTE ON find_emp TO MARTIN;
GRANT EXECUTE ON swap TO PUBLIC;

• 删除过程：

DROP PROCEDURE find_emp;

2.3 自主事务处理

• 自主事务处理

  主事务处理启动独立事务处理;
  然后主事务处理被暂停;
  自主事务处理子程序内的 SQL 操作;
  然后终止自主事务处理;
  恢复主事务处理.
  PRAGMA AUTONOMOUS_TRANSACTION 用于标记子程序为自主事务处理。

• 自主事务处理的特征：

  与主事务处理的状态无关;
  提交或回滚操作不影响主事务处理;
  自主事务处理的结果对其他事务是可见的;
  能够启动其他自主事务处理.

-- 自主事务处理
create or replace procedure p2
as
  a varchar2(50);
  -- 自主事务处理，p1的个更改不影响p2
  PRAGMA AUTONOMOUS_TRANSACTION;
begin
  select sname into a from scott.student where sno=2;
  dbms_output.put_line(a);
end;

create or replace procedure p1
as
  n varchar2(50);
begin
  update scott.student set sname = 'ZSR' where sno=2;
  p2();
  select sname into b from scott.student where sno=2;
  dbms_output.put_line(b);
end;

3 函数

3.1 简介

函数是可以返回值的命名的 PL/SQL 子程序。
创建函数的语法：

CREATE [OR REPLACE] FUNCTION 
  <function name> [(param1,param2)]
RETURN <datatype>  IS|AS 
  [local declarations]
BEGIN
  Executable Statements;
  RETURN result;
EXCEPTION
  Exception handlers;
END;

• 
  
• 定义函数的限制：
  

函数只能接受 IN 参数，而不能接受 IN OUT 或 OUT 参数;
形参不能是 PL/SQL 类型，只能是数据库类型;
函数的返回类型也必须是数据库类型.
访问函数的两种方式：

使用 PL/SQL 块;
使用 SQL 语句.

-- 创建函数
create or replace function fun_hell return varchar2
is
begin
  return '你好，朋友';
end;

--调用函数；
sql*plus 
select fun_hello from dual;

pl/sql
declare
  ss varchar2(20);
begin
  ss := fun_hello;
  dbms_output.put_line(ss);
end;

3.2 函数练习

create table 分数表 (student_no number(3),name varchar2(10), score number(3));

insert into  分数表  values (1,'张一', 56);
insert into  分数表 values(2,'张二', 82);
insert into  分数表 values  (3,'张三', 90);

要求：创建一个函数，可以接受用户输入的学号，得到该学生的名次，并输出这个名次。

create or replace function fun1(sno1 int) return int
is
  score1 number;
  mingci1 number;
begin
  select score into score1 from 分数表 where student_no = sno1;
  select count(*) into mingci1 from 分数表 where score > score1;
  mingci1 := mingci1 + 1;
  return mingci1;
end;
/

--编译错误，查看错误
show error

--修改
declare
 mc number;
begin
 mc := func1(2);
 dbms_output.put_line('第' || mc || '名');
end;

3.3 过程和函数的比较

过程	函数
作为 PL/SQL 语句执行	作为表达式的一部分调用
在规格说明中不包含 RETURN 子句	必须在规格说明中包含 RETURN 子句
不返回任何值	必须返回单个值
可以包含 RETURN 语句，但是与函数不同，它不能用于返回值	必须包含至少一条 RETURN语句

4 游标

4.1 简介

什么情况下，一定要使用游标，什么情况下，不使用游标也行。能够根据不同情况，选取不同的游标进行使用。
显式游标是用的最广泛的。
oracle在进行取行记录的时候，实际上内部使用了游标。
只要进行select操作，内部使用游标，相当于指针。

oracle服务器->执行PL/SQL程序->检索行->保存到游标中（内存单元）->提取行->一次处理一行

不使用游标，没有办法单独的一行一行控制。

eg.需求：A表存储了邮件地址，B表是从网上爬的邮件地址（内含一些错误邮箱），要实现从B表剔除不合法的邮箱，当A表不含有该邮箱记录，则插入A表的需求。

逐行处理查询结果，以编程的方式访问数据。

4.2 游标的类型：

1，隐式游标：在 PL/SQL 程序中执行DML SQL 语句时自动创建隐式游标，名字固定叫sql；
2，显式游标：显式游标用于处理返回多行的查询；
3，REF 游标：REF 游标用于处理运行时才能确定的动态 SQL 查询的结果。

4.2.1 隐式游标

①在PL/SQL中使用DML语句时自动创建隐式游标;
②隐式游标自动声明、打开和关闭，其名为 SQL;
③通过检查隐式游标的属性可以获得最近执行的DML 语句的信息;
④隐式游标的属性有：

   %FOUND – SQL 语句影响了一行或多行时为 TRUE；
    %NOTFOUND – SQL 语句没有影响任何行时为TRUE；
    %ROWCOUNT – SQL 语句影响的行数；
    %ISOPEN  - 游标是否打开，始终为FALSE。

4.2.2 显示游标

显式游标在 PL/SQL块的声明部分定义查询，该查询可以返回多行。
显式游标的操作过程:
数据库->打开游标->select *from student->提取行->变量->关闭游标

声明游标、打开游标、使用游标取出记录、关闭游标。

SET SERVER OUTPUT ON
<!--声明游标-->
DECLARE
   my_toy_price  toys.toyprice%TYPE;                  CURSOR toy_cur IS
   SELECT toyprice FROM toys
   WHERE toyprice<250;
BEGIN
   <!--打开游标-->
   OPEN toy_cur;  
     LOOP
        <!--提取行-->
         FETCH toy_cur INTO my_toy_price;
         EXIT WHEN toy_cur%NOTFOUND;
         DBMS_OUTPUT.PUT_LINE 
      ('TOYPRICE=:玩具单价=：'||my_toy_price);
     END LOOP;
     <!--关闭游标-->
     CLOSE toy_cur;
END;

declare
  stu1 student%rowtype;
  cursor mycursor is select * from student;
begin
  open mycursor;
  fetch mycursor into stu1;
  while mycursor%found loop
    dbms_output.put_line('学号是：'||stu1.sno||',姓名是:'||stu1.sname);
    fetch mycursor into stu1;
  end loop;
  close mycursor;
end;
/

4.2.2.1 带参数的显示游标

①声明显式游标时可以带参数以提高灵活性；
②声明带参数的显式游标的语法如下：
CURSOR <cursor_name>(<param_name> <param_type>)
IS select_statement;
③允许使用游标删除或更新活动集中的行；
④声明游标时必须使用 SELECT … FOR UPDATE语句。

declare
  stu1 student%rowtype;
  sno1 student.sno%type;
  cursor mycursor（input_no number） is select * from student where sno > input_no;
begin
  <!--从键盘输入-->
  sno1 := &学生学号：
  open mycursor;
  fetch mycursor into stu1;
  while mycursor%found loop
    dbms_output.put_line('学号是：'||stu1.sno||',姓名是:'||stu1.sname);
    fetch mycursor into stu1;
  end loop;
  close mycursor;
end;
/

4.2.2.2 使用显示游标更新行

CURSOR <cursor_name> IS SELECT statement FOR UPDATE;

UPDATE <table_name>
SET <set_clause>
WHERE CURRENT OF <cursor_name>

DELETE FROM <table_name>
WHERE CURRENT OF <cursor_name>

declare
  stu1 student%rowtype;
  cursor mycursor is select * from student where sno=2 or sno=3 for update;
begin
  open mycursor;
  fetch mycursor into stu1;
  while mycursor%found loop
    update student set sno=sno+100 where current of mycursor;
    fetch mycursor into stu1;
  end loop;
  close mycursor;
end;

4.2.2.3 循环游标

①循环游标用于简化游标处理代码；
②当用户需要从游标中提取所有记录时使用；
③循环游标的语法如下：

    FOR <record_index> IN <cursor_name>
    LOOP
    <executable statements>
    END LOOP;

循环游标只能用于查询，而不能用于更新。

declare
  stu1 student%rowtype;
  cursor mycursor is select * from student;
begin
  for cur_2 in mycursor loop
    dbms_output.put_line('学号是：'||stu1.sno||',姓名是:'||stu1.sname);
  end loop;
end;
/

fetch … bulk collect into

适用于大数据量，速度远远快于普通的fetch … into。

4.2.3 REF 游标

REF 游标和游标变量用于处理运行时动态执行的 SQL 查询。
- 创建游标变量需要两个步骤：
①声明 REF 游标类型;
②声明 REF 游标类型的变量。
- 用于声明 REF 游标类型的语法为：
TYPE <ref_cursor_name> IS REF CURSOR
[RETURN <return_type>];
- 打开游标变量的语法如下：
OPEN cursor_name FOR select_statement;

declare 
  type refcur is ref cursor；
  cursor2 refcur;
  tab varchar2(50);
  tb_name varchar2(50);
  sno2 student.sno%type;
  name2 student.sname%type;
begin
  tb_name := '&tab';
  if tb_name='student' then
    open cursor2 for select sno,sname from student;
    fetch cursor2 into no2,name2;
    while cursor2%found
    loop
      dbms_output.put_line(cursor2%rowcount||'. 学号是'||no2||',姓名是:'||sname2);
      fetch cursor2 into no2,name2;
    end loop;
    close cursor2;
  else
    dbms_output.put_line('不是正确的表的名字');
  end if;
end;
/

5 触发器

5.1 简介

触发器是当特定事件出现时自动执行的存储过程;
特定事件可以是执行更新的DML语句和DDL语句;
触发器不能被显式调用;
触发器的功能：

    ①自动生成数据;
    ②自定义复杂的安全权限;
    ③提供审计和日志记录;
    ④启用复杂的业务逻辑.

5.1.1 触发器语法

CREATE [OR REPLACE] TRIGGER trigger_name
AFTER | BEFORE | INSTEAD OF
[INSERT] [[OR] UPDATE [OF column_list]] 
[[OR] DELETE]
ON table_or_view_name
[REFERENCING {OLD [AS] old / NEW [AS] new}]
[FOR EACH ROW]
[WHEN (condition)]
pl/sql_block;

5.1.2 组成部分

>
触发器由三部分组成：
触发器语句（事件）
定义激活触发器的 DML 事件和 DDL 事件.
触发器限制
执行触发器的条件，该条件必须为真才能激活触发器
触发器操作（主体）
包含一些 SQL 语句和代码，它们在发出了触发器语句且触发限制的值为真时运行

5.1.3 触发器操作

当用户插入、更新成绩表中的记录时候，就输出一个提示“触发器响应了”。

--针对整张表的
create or replace trigger trig1 before insert or update on grade
begin 
  dbms_output.put_line('触发器 trig1 响应了');
end;
/

--针对每一行的
create or replace trigger trig1 before insert or update on grade for each row
begin 
  dbms_output.put_line('触发器 trig1 响应了');
end;
/

有了for each row，就是行级触发器，没有for each row就是表级触发器。行级触发器，对于每一行都做触发器的动作。

:new:用户即将插入的记录；
:old:用户即将删除的记录。

触发器不能使用：rollback、commit、create、drop、alter、saveopint等内容。

5.2 after和before触发器

after触发器工作原理： 更新->表->保存更新->oracle数据库->激活->触发器
before触发器工作原理： 更新->表->激活->触发器->保存更新->oracle数据库

保证学生的sno列，不能为负值，我们用触发器解决。
当用户插入记录到student时，不能插入sno是负值的记录。

create or replace trigger trig2 before insert on student for each row
begin 
  if :new.sno < 0 then
    raise_application_error(-20001,'学号错误，不能插入表中');
  end if;
  dbms_output.put_line('触发器 trig1 响应了');
end;
/

5.3 触发器类型

触发器类型->模式（DDL）触发器、数据库级触发器、DML触发器。
DML触发器包括：行级、语句级、istead of触发器

类型	备注
DDL触发器	在模式中执行DDL语句时执行
数据库级触发器	在发生打开、关闭、登录和退出数据库等系统事件时执行
DML 触发器	在对表或视图执行DML语句时执行
语句级触发器	无论受影响的行数是多少，都只执行一次
行级触发器	对DML语句修改的每个行执行一次
INSTEAD OF 触发器	用于用户不能直接使用 DML 语句修改的视图

行级触发器：有for each row语句，在begin代码
段中可以使用:new和:old；
语句级触发器：没有有for each row语句，在begin代码段中不可以使用:new和:old。

如果在触发器的plsql内使用:new :old，就必须是行级触发器，就是要有for each row。

当执行insert的时候，:new存在，:old没有；
当执行delete的时候，:new不存在,:old存在；
当执行update的时候，:new存在，:old存在。

oracle要更新某行时，是先删除原来的记录，然后插入新的记录。

平时用的最多的是DML触发器。

5.4 系统常用变量

变量	备注
Ora_client_ip_address	返回客户端的ip地址
Ora_database_name	返回当前数据库名
Ora_login_user	返回登录用户名
Ora_dict_obj_name	返回ddl操作所对应的数据库对象名
Ora_dict_obj_type	返回ddl操作所对应的数据库对象的类型

启用、禁用和删除触发器

• 启动和禁用触发器

ALTER TRIGGER aiu_itemfile DISABLE;
ALTER TRIGGER aiu_itemfile ENABLE;

• 删除触发器

DROP TRIGGER aiu_itemfile;

• 当用户对成绩表进行增删改时，把当时的情况输出

create or replace trigger trig3 before insert or update or delete on grade for each row
begin
  if inserting then
    dbms_output.put_line('插入学生学号:'||:new.code||'，姓名:'||:new.name||',成绩:'||:new.score);
  end if;
  if updating then
    dbms_output.put_line('原学生学号:'||:old.code||'，姓名:'||:old.name||',成绩:'||:old.score||'，新学生学号:'||:new.code||'，姓名:'||:new.name||',成绩:'||:new.score);
  end if;
  if deleting then
    dbms_output.put_line('删除的学生学号:'||:old.code||'，姓名:'||:old.name||',成绩:'||:old.score);
  end if; 
end
/

• 模式触发器是一种特殊的触发器。
  只要满足该模式，就会在我们的模式触发器表，生成记录。