PL/SQL_高级编程

一.PL/SQL基础知识

图片

1.什么是PL/SQL

PL/SQL（Procedural Language/SQL，过程语言/SQL）是结合了Oracle过程语言和结构化查询语言（SQL）的一种扩展语言。使用PL/SQL可以编程具有很多高级功能的程序。

优点：

（1）具有编程语言的特点，能把一组SQL语句放到一个模块中，使其更具有模块化程序的特点，如判断循环

（2）可以采用过程性语言控制程序的结构，它能把一组SQL语句放到一个模块中，使其更具有模块化程序结构，如判断循环等程序结构

（3）和其他编程语言一样，PL/SQL可以对程序中的错误进行自动处理，使程序能够在遇到错误时不会中断，即它的异常处理机制。

（4）PL/SQL程序块具有更好的可移植性，可以移植到另一个Oracle数据库中。

（5）PL/SQL程序减少了网络的交互，有助于提高程序性能。

（1）.PL/SQL体系结构

PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言

PL/SQL的工作原理

PL/SQL引擎接受 PL/SQL 块并对其进行编译执行

该引擎执行所有过程语句

将SQL语句发送给Oracle的SQL语句执行器

图片

（2）PL/SQL块简介

PL/SQL是一种块结构的语言，它将一组语句放在一个块中。

PL/SQL块将逻辑上相关的声明和语句组合在一起。

匿名块是一个未在数据库中命名的PL/SQL块，在运行时被传递到PL/SQL引擎以便执行。

在PL/SQL块中可以使用select、insert、update、delete等DML语句，事务控制语句及SQL函数等。

PL/SQL不允许直接使用create、drop或alter等DDL语句，但可以通过动态SQL来执行它们。

一个PL/SQL块由三部分组成，即声明、执行部分、异常处理部分。

PL/SQL的结构

[DECLARE]
	--声明部分：在此声明PL/SQL用到的变量、类型及游标，以及局部的存储过程和函数
BEGIN
	--执行部分：过程及SQL语句，即程序的主要部分
[ECEPTION]
	--异常处理部分：错误处理
END;

--在dos/sqlplus中运行时,输入完以上，还需要set serverout on
SQL>set serverout on
SQL>/

注意：其中执行部分不可省略

（3）运算符和表达式

PL/SQL支持的操作符包含关系运算符、一般运算符和逻辑运算符等，与SQL语言类似

1.关系运算符

运算符	意义
=	等于
<>、!=、~=、^=	不等于
<	小于
>	大于
<=	小于或等于
>=	大于或等于

2.一般运算符

运算符	意义
+	加号
-	减号
*	乘号
/	除号
:=	赋值符
=>	关系符
..	范围运算符
||	字符连接符

3.逻辑运算符

运算符	意义
IS NULL	是空值
BETWEEN AND	介于两者之间
IN	在一列值中间
AND	逻辑与
OR	逻辑或
NOT	取反，如IS NOT NULL,NOT NUll

4.常量和变量的声明

在PL/SQL块的可执行部分引用变量和常量前，必须先对其进行声明。常量和变量在PL/SQL块的部分声明，在PL/SQL块的可执行部分被使用。

语法：

--声明变量
variable_name data_type[(size)][:=init_value]
解释：
1.variable_name:变量名称
2.data_type:变量的SQL或PL/SQL数据类型
3.size：变量范围
4.init_value:变量初始值

--声明常量
variable_name CONSTANT data_type:=value

--引用型变量:即e_no的类型与emp表中的empno相同
--表.字段%type  赋给e_no
declare
	e_no emp.empno%type;
begin
	select empno into e_no from emp where empno=7369;
	--输出
	dba_output.put_line(e_no)；
end;

--记录型变量：记录变量分量的引用
--表%rowtype  赋给emps，相当于对象
declare
	emps emp.empno%rowtype;
begin
	select * into emps from emp where empno=7369;
	--输出
	dba_output.put_line(emps.empno)；
end;

PL/SQL程序设计中的标识符定义与SQL的标识符定义的要求相同，要求限制如下：

（1）标识符名不能超过30个字符

（2）第一个字符必须为字母

（3）不区分大小写

（4）不能用“-”（减号）

（5）不能是SQL保留字段

标 识 符	命名规则	例 子
程序变量	v_name	v_student_name
程序常量	c_name	c_company_name
游标变量	cursor_name	cursor_emp
异常标志	e_name	e_too_many
表类型	name_table_type	emp_record_type
表	name_table	emp_table
记录类型	name_record	emp_record
绑定变量	g_name	g_year_sal

​

案例：

declare
	v_name varchar2(20);
	v_rate number(7,2);
	c_rate_incr CONSTANT number(7,2):=1.10
begin
	--方法一：通过select into 给变量赋值
	select ename,sal*c_rate_incr into v_ename.v_rate from employee where 		 empno='7788';
	
	--方法二：通过赋值操作符“:=”给变量赋值
	v_ename:='SCOTT';
end;

select 语句在pl/sql中有改变，为select into。查询结果只能返回一行并赋值到变量中保存。

Oracle 11g新增特性：可以使用pl/sql块中的赋值语句访问序列，提高灵活性。

v_no:=emp_seq.nextval;

5.注释

单行 --

多行 /* */

2.PL/SQL数据类型

PL/SQL变量，常量和参数都必须有一个有效的数据类型，指定存储格式，约束和值的有效范围。本教程介绍标量和LOB在PL/SQL，其他两种数据类型可用的数据类型将涵盖在后面的章节。

分类	描述
标量	单值没有内部组件，如NUMBER, DATE 或 BOOLEAN
大对象(LOB)	指向其他数据项，如文本，图形图像，视频剪辑和声音等存储大对象
复合	具有可单独访问的内部组件的数据项。例如，集合和记录
引用	指向其他数据项

1.PL/SQL标量数据类型和子类型

PL/SQL标量数据类型和子类型受到以下几大类：

数据类型	描述
数字	在其上执行的算术运算的数值
字符	代表单个字符或字符的字母数字字符串值
布尔	在其上执行的逻辑运算的逻辑值
日期时间	日期和时间

PL/SQL提供的数据类型的子类型。例如，数据类型数有一个叫作INTEGER的子类型。可以使用子类型在PL/SQL程序中，使数据类型与其他程序中的数据类型兼容，而嵌入PL/SQL代码在另一个程序，如Java程序。

2.PL/SQL数值数据类型和子类型

以下是PL/ SQL预定义的数字数据类型及其子类型的详细信息：

数据类型	描述
PLS_INTEGER	通过2,147,483,647到-2147483648范围内有符号整数，以32位表示
BINARY_INTEGER	通过2,147,483,647到-2147483648范围内的有符号整数，以32位表示
BINARY_FLOAT	单精度IEEE 754格式的浮点数
BINARY_DOUBLE	双精度IEEE 754格式的浮点数
NUMBER(prec, scale)	定点或浮点数在范围1E-130至（但不包括）绝对值1.0E126。 NUMBER变量也可以表示0
DEC(prec, scale)	ANSI具体的定点类型使用38位小数最大精度
DECIMAL(prec, scale)	IBM特定的固定点型具有38位小数最大精度
NUMERIC(pre, secale)	浮点类型具有38位小数最大精度。
DOUBLE PRECISION	具有126个二进制数字最大精度ANSI特定浮点型（约38位十进制数）
FLOAT	具有126个二进制数字（约38位十进制数）最大精度ANSI和IBM特定的浮点型
INT	具有38位小数最大精度ANSI具体的整数类型
INTEGER	ANSI和IBM的38位小数最大精度具体的整数类型
SMALLINT	ANSI和IBM的38位小数最大精度具体的整数类型
REAL	具有63位二进制数字最大精度浮点型（大约18位小数）

下面是一个有效的声明：

DECLARE
   num1 INTEGER;
   num2 REAL;
   num3 DOUBLE PRECISION;
BEGIN
   null;
END;
/

让我们编译和运行上面的程序，这将产生以下结果：

PL/SQL procedure successfully completed

3.PL/SQL字符数据类型和子类型

以下是PL/SQL预定义的字符数据类型及其子类型的详细信息：

数据类型	描述
CHAR	具有32,767个字节的最大尺寸固定长度字符串
VARCHAR2	具有32,767个字节的最大尺寸变长字符串
RAW	可变长度的二进制或字节字符串的32,767个字节的最大尺寸，而不是由PL/ SQL解释
NCHAR	具有32,767个字节的最大尺寸的固定长度国家字符串
NVARCHAR2	具有32,767个字节的最大尺寸可变长度国家字符串
LONG	具有32,760字节最大尺寸变长字符串
LONG RAW	可变长度的二进制或字节字符串的32,760字节的最大尺寸，而不是由PL/SQL解释
ROWID	物理行标识符，一行在一个普通的表中的地址
UROWID	物理通用行标识符（物理，逻辑，或外国的行标识符）

4.PL/SQL布尔数据类型

在逻辑操作中使用布尔数据类型存储的逻辑值。逻辑值为：true 和 false 的布尔值以及 NULL 值。

但是，SQL没有数据类型等同于布尔。因此，布尔值不能用于：

• SQL语句
• 内置SQL函数（如TO_CHAR）
• 从SQL语句调用PL/ SQL函数

5.PL/SQL日期时间和间隔类型

DATE数据类型存储固定长度的日期时间，其中包括每天在几秒钟内从午夜开始的时间。有效的日期范围从公元前4712年1月1日至999912月31日。

默认的日期格式由Oracle初始化参数NLS_DATE_FORMAT设置。例如，默认的可以是“DD-MON-YY'，它包括一个两位数字的月份中的日期，月份名称的缩写，以及年的最后两位数字，例如，01- OCT-12。

每个日期，包括世纪，年，月，日，时，分，秒。下表显示了每个字段的有效值：

字段名称	有效日期时间值	有效的间隔值
YEAR	-4712 to 9999 (年除外 0)	任何非零整数
MONTH	01 to 12	0 to 11
DAY	01 to 31 (受制于年份和月份的数值，按日历的区域设置的规则)	任何非零整数
HOUR	00 to 23	0 to 23
MINUTE	00 to 59	0 to 59
SECOND	00 to 59.9(n), 其中，9（n）是时间小数秒精度	0 to 59.9(n), 其中，9（n）是区间小数秒精度
TIMEZONE_HOUR	-12 to 14 （范围可容纳夏令时更改）	不适用
TIMEZONE_MINUTE	00 to 59	不适用
TIMEZONE_REGION	在动态性能视图找到V$TIMEZONE_NAMES	不适用
TIMEZONE_ABBR	在动态性能视图找到V$TIMEZONE_NAMES	不适用

###6.PL/SQL大对象（LOB）数据类型

大对象（LOB）数据类型是指大到数据项，例如文本，图形图像，视频剪辑和声音波形。 LOB数据类型允许高效的，随机的，分段访问这些数据。以下是预定义的PL/SQL LOB数据类型：

数据类型	描述	大小
BFILE	用于存储大型二进制对象在操作数据库之外的系统文件	依赖于系统，不能超过4千兆字节（GB）
BLOB	用于存储大型二进制对象在数据库中	8到128兆兆字节（TB）的
CLOB	用于存储字符大块数据在数据库中	8 - 128 TB
NCLOB	用于在数据库中存储大块NCHAR数据	8 - 128 TB

7.PL/SQL用户定义的子类型

子类型是另一种数据类型，这是所谓的基本类型的子集。 子类型具有其基本类型相同的操作，但其只是有效值的一个子集。

PL/SQL预定义了一些子类型的封装标准。例如，PL/SQL预定义的子类型CHARACTER和INTEGER如下：

SUBTYPE CHARACTER IS CHAR;
SUBTYPE INTEGER IS NUMBER(38,0);

可以定义和使用自己的子类型。下面的程序说明定义和使用一个用户定义的子类型：

DECLARE
   SUBTYPE name IS char(20);
   SUBTYPE message IS varchar2(100);
   salutation name;
   greetings message;
BEGIN
   salutation := 'Reader ';
   greetings := 'Welcome to the World of PL/SQL';
   dbms_output.put_line('Hello ' || salutation || greetings);
END;
/

当上述代码在SQL提示符执行时，它产生了以下结果：

Hello Reader Welcome to the World of PL/SQL

PL/SQL procedure successfully completed.

8.PL/SQL NULL

PL/SQL NULL值表示缺少或未知的数据，蛤它不是一个整数，字符，或任何其他特定的数据类型。需要注意的是NULL不是一样的空数据串或空字符值'\0'。NULL可以被分配，但它不能与任何东西等同，包括其本身。

二.PL/SQL控制语句

条件控制，循环控制，顺序控制

（1）条件控制

语法:

--if语句
if <布尔表达式> then
	pl/sql和sql语句；
end if；
-------------------
if <布尔表达式> then
	pl/sql和sql语句；
elsif
	其他语句；
end if；

--case语句
--格式一
case 条件表达式
	when 条件表达式结果1 then
		语句段1
	when 条件表达式结果2 then
		语句段2
	......
	when 条件表达式结果n then
		语句段n
	[else 语句段]
end case;
--格式二
case
	when 条件表达式结果1 then
		语句段1
	when 条件表达式结果2 then
		语句段2
	......
	when 条件表达式结果n then
		语句段n
	[else 语句段]
end case;

案例：

--sqldeveloper 相当于dos
set serverout on
--键盘输入
--num:地址符
accept num prompt'请输入一个数字';
declare 
  --定义保存变量
  pnum number:=#
begin
  if pnum = 0 then dbms_output.put_line('输入为0');
  elsif pnum = 1 then dbms_output.put_line('输入为1');
  else dbms_output.put_line('输入为其他');
  end if;
end;
/

（2）循环控制

循环控制包括LOOP和EXIT语句，使用exit语句可以立即退出循环，使用exit when语句可以根据条件结束循环

循环共三种：loop、while、for

1.loop循环

语法：

loop
	要执行的语句
	exit when <条件语句> --条件满足，退出循环语句
end loop;

案例：

declare
num number:=1;
begin
  loop
    num:=num+1;
    exit when num=10;
    dbms_output.put_line(num);
  end loop;
end;

2.while循环

语法：

while <布尔表达式> loop
	要执行的语句
end loop;

案例：

declare
num number:=1;
begin
  while num<10 loop
        num:=num+1;
        dbms_output.put_line(num);
  end loop;
end;

3.for循环

语法：

for 循环计数器 in [reverse] 下限...下限 loop
	要执行的语句
end loop;

案例:

declare
  i number;
begin
	--循环遍历1-10
  for i in 1..10 loop
     dbms_output.put_line(i);
  end loop;
  	--反向循环遍历1-10
  for i in reverse 1..10 loop
    dbms_output.put_line(i);
  end loop;
end;

（3）顺序控制

顺序控制用于顺序执行语句。顺序控制包括null语句和goto语句。goto不推荐

null语句：null语句是一个可执行语句，相当于一个占位符或不可执行任何操作的空语句，提高可读性。

三.异常

发生异常后，语句将停止执行，PL/SQL引擎立即将控制权转到PL/SQL块的异常处理部分。

异常情况分为预定义异常和用户自定义异常（例外）

1.预定义异常

24个预定义异常

异常	错误	何时出现
ACCESS_INTO_NULL	ORA-06530	试图访问未初始化对象的时候出现
CASE_NOT_FOUND	ORA-06592	如果定义了一个没有ELSE子句的CASE语句，而且没有CASE语句满足运行时条件时出现该异常
COLLECTION_IS_NULL	ORA-06531	当程序去访问一个没有进行初始化的NESTED TABLE或者是VARRAY的时候，会出现该异常
CURSOR_ALREADY_OPEN	ORA-06511	游标已经被OPEN，如果再次尝试打开该游标的时候，会出现该异常
DUP_VAL_ON_INDEX	ORA-00001	如果插入一列被唯一索引约束的重复值的时候，就会引发该异常（该值被INDEX认定为冲突的）
INVALID_CURSOR	ORA-01001	不允许的游标操作，比如关闭一个已经被关闭的游标，就会引发
INVALID_NUMBER	ORA-01722	给数字值赋非数字值的时候，该异常就会发生，这个异常也会发生在批读取时候LIMIT子句返回非正数的时候
LOGIN_DENIED	ORA-01017	程序中，使用错误的用户名和密码登录的时候，就会抛出这个异常
NO_DATA_FOUND	ORA_06548	在使用SELECT INTO 结构，并且语句返回NULL值的时候；访问嵌套表中已经删除的表或者是访问INDEX BY表（联合数组）中的未初始化元素就会出现该异常
NOT_LOGGED_ON	ORA-01012	当程序发出数据库调用，但是没有连接的时候（通常，在实际与会话断开连接之后）
PROGRAM_ERROR	ORA-06501	当Oracle还未正式捕获的错误发生时常会发生，这是因为数据库大量的Object功能而发生
ROWTYPE_MISMATCH	ORA-06504	如果游标结构不适合PL/SQL游标变量或者是实际的游标参数不同于游标形参的时候发生该异常
SELF_IS_NULL	ORA-30625	调用一个对象类型非静态成员方法（其中没有初始化对象类型实例）的时候发生该异常
STORAGE_ERROR	ORA-06500	当内存不够分配SGA的足够配额或者是被破坏的时候，引发该异常
SUBSCRIPT_BEYOND_COUNT	ORA-06533	当分配给NESTED TABLE或者VARRAY的空间小于使用的下标的时候，发生该异常（类似于java的ArrayIndexOutOfBoundsException）
SUBSCRIPT_OUTSIDE_LIMIT	ORA-06532	使用非法的索引值来访问NESTED TABLE或者VARRAY的时候引发
SYS_INVALID_ROWID	ORA-01410	将无效的字符串转化为ROWID的时候引发
TIMEOUT_ON_RESOURCE	ORA-00051	当数据库不能安全锁定资源的时候引发
TOO_MANY_ROWS	ORA-01422	常见错误，在使用SELECT INTO 并且查询返回多个行时引发。如果子查询返回多行，而比较运算符为相等的时候也会引发该异常。
USERENV_COMMITSCN_ERROR	ORA-01725	只可使用函数USERENV('COMMITSCN')作为INSERT语句的VALUES子句中的顶级表达式或者作为UPDATE语句的SET子句中的右操作数
VALUE_ERROR	ORA-06502	将一个变量赋给另一个不能容纳该变量的变量时引发
ZERO_DIVIDE	ORA-01476	将某个数字除以0的时候，会发生该异常

语法：

begin
	sequence_of_statements;
exception
	when <exception_name> then
		sequence_of_statements;
	when others then
		sequence_of_statements;
end;
注意：
PL/SQL块只能有一个others异常处理。
可以使用函数sqlcode和sqlerrm来返回错误代码和错误文本信息。

案例：

declare 
  num number;
begin
  num:=1/0;
  exception
  when ZERO_DIVIDE then
      dbms_output.put_line('除数不能为0');
  when others then 
      dbms_output.put_line('其他异常');
end;

2.处理用户自定义异常（例外）

当引发一个异常错误时，控制就跳转到exception块异常错误部分，执行错误处理代码。

对于这类异常情况的处理，步骤如下。

（1）在PL/SQL块的定义部分定义异常情况

<异常情况> exception；

（2）抛出异常情况

raise <异常情况>

（3）在PL/SQL块的异常情况处理部分对异常情况做出相应的处理

案例：查找empno为80的，没有找到提示错误信息

declare 
  cursor list is select ename from emp where empno=80;
  eno emp.empno%type;
  error_no_found exception;
begin
  open list;
  fetch list into eno;
  if list%notfound then raise error_no_found;
  end if;
  close list;
  
  exception
     when error_no_found then dbms_output.put_line('没有找到');
     when others then dbms_output.put_line('其他错误'); 
end;

除了以上，还可以通过raise_application_error存储过程还可以重新定义异常错误信息。

语法：

raise_application_error(error_number,error_message);
解释：
1.error_number:异常编号
2.error_message：异常指定错误文本

案例：

declare 
......
begin
......
  if v_column is null then raise_application_error(-20001,'该员工没有补助');
......
end;

四.游标（光标Cursor）

游标是用来处理使用select语句从数据库检索到的多行记录的工具

游标分类：

​ 1.显示游标：返回多条记录时，使用显示游标逐行读取

​ 2.隐式游标：PL/SQL自动为DML语句创建隐式游标，包含一条返回记录

1.显式游标

（1）游标的使用

（1）声明光标：

语法：

cursor cursor_name [(parameter[,patameter]...)]
[return return_type] is select_statement;
解释：
1.cursor_name:游标的名称
2.parameter：用于为游标指定输入参数，不能使用长度约束
3.return_type：用于定义游标提取的行的类型
4.select_statement:指定游标定义的查询语句

（2）打开光标：

语法：

open curosr_name [(parameters)];

（3）提取游标

语法：

fetch cursor_name into variables;
解释：
1.cursor_name：游标名称
2.variables：变量名

（4）关闭光标

语法：

close cursor_name;

案例：

declare 
  name emp.ename%type;
  sal emp.sal%type;
  cursor emp_cursor is select ename,sal from emp;
begin
  open emp_cursor;
  loop
  fetch emp_cursor into name,sal;
  exit when emp_cursor%notfound;
  dbms_output.put_line('第'||emp_cursor%rowcount||'个雇员:'||name||'的月薪是'||sal);
  end loop;
  close emp_cursor;
end;
----------
第1个雇员:SMITH的月薪是800
第2个雇员:ALLEN的月薪是1600
第3个雇员:WARD的月薪是1250
第4个雇员:JONES的月薪是2975
第5个雇员:MARTIN的月薪是1250
第6个雇员:BLAKE的月薪是2850
第7个雇员:CLARK的月薪是2450
第8个雇员:KING的月薪是5000
第9个雇员:TURNER的月薪是1500
第10个雇员:JAMES的月薪是950
第11个雇员:FORD的月薪是3000
第12个雇员:MILLER的月薪是1300

（2）显式子游标的属性

属性名称	说 明
%found	用于检验游标是否成功，通常在FETCH语句前使用，当游标按照条件查询出一条记录时，返回true
%isopen	判断游标是否处于打开状态，视图打开一个已经打开或者已经关闭的游标，将会出现错误
%notfound	与%found的作用相反，当按照条件无法查询到记录时，返回true
%rowcount	循环执行游标读取数据时，返回检索出的记录数据的行数

（3）使用显式游标删除或更新

使用游标时，如果处理过程中需要删除或更新行，在定义游标时必须使用select ...for update语句，而在执行delete和update时使用where current of子句指定游标的当前行。

声明更新游标语法：

update table_name is select_sataement for update [of columns];
解释：
1.for update [of columns]为更新查询，锁定选择的行。
（1）当前单表更新查询时，可以省略of子句
（2）当选择多个表更新查询时，被锁定的行来源于of子句后声明的列所在的表中的行。

在使用for update子句声明游标之后，可以使用以下更新行：

update table_name 
	set columns_name = columns_value 
where current of cursor_name;
解释：
多表查询更新时，更新表为锁定行所在的表。

案例：给所有员工增加200工资

declare 
  cursor emp_cursor is select ename,sal from emp e inner join dept d on e.deptno=d.deptno for update of sal;
  --定义记录性变量
  v_num emp_cursor%rowtype;
begin
  open emp_cursor;
  if not emp_cursor%isopen then
    open emp_cursor;
  end if;
  loop
  --fetch后为游标名，into后为变量
  fetch emp_cursor into v_num;  
  exit when emp_cursor%notfound;
  update emp set sal=sal+200 where current of emp_cursor;
  end loop;
  close emp_cursor;
end;

###（4）使用循环游标简化游标的读取

循环游标隐式打开游标自动从活动集获取行，然后再处理完所有行时关闭游标。循环游标自动创建%rowtype类型的记录型变量用作记录索引。

语法：

for record_index in cursor_name
loop
	exectable_statements
end loop;
解释：
1.record_index:是PL/SQL声明的记录变量，此变量的属性为%rowtype类型，作用域在for循环之内，即在for循环之外不能访问此变量。

循环游标特性：

（1）在从游标中提取了所有记录之后自动终止

（2）提取和处理游标中的每一条记录

（3）如果在提取记录之后%notfound属性返回true，则终止循环。

（4）如果未返回行，则不进入循环。

案例：

--显示员工表所有员工的姓名和薪水
declare 
  cursor emp_cursor is select ename,sal from emp;
begin
  for emp_record in emp_cursor loop
      dbms_output.put_line('第'||emp_cursor%rowcount||'个员工：'||emp_record.ename||'的工资是'||emp_record.sal);
  end loop;
end;
--------------
第1个员工：SMITH的工资是1000
第2个员工：ALLEN的工资是1800
第3个员工：WARD的工资是1450
第4个员工：JONES的工资是3175
第5个员工：MARTIN的工资是1450
第6个员工：BLAKE的工资是3050
第7个员工：CLARK的工资是2650
第8个员工：KING的工资是5200
第9个员工：TURNER的工资是1700
第10个员工：JAMES的工资是1150
第11个员工：FORD的工资是3200
第12个员工：MILLER的工资是1500

（5）带参的游标

declare 
cursor list(dno number) is select ename from emp where deptno=dno;
pename emp.ename%type;
begin
  open list(10); --传参
  loop
    fetch list into pename;
    exit when list%notfound;
    dbms_output.put_line(pename);
  end loop;
  close list;
end;

（6）no_data_found和%notfound区别

1.select...into语句返回0条和多条记录时触发not_data_found

2.当update或delete语句的where子句未找到时，触发%notfound

3.在提取循环中用%notfound或%found来确定循环的退出条件，而不用not_data_found

五.存储过程

1.子程序组成

子程序是已命名的PL/SQL块，它们存储在数据库中，可以为它们指定参数，也可以从任何数据客户端和应用程序中调用它们。

子程序包括存储过程和函数。使用存储过程执行操作，使用函数执行操作并返回值。

子程序的三部分：

1.声明部分：类型、游标、常量、变量、异常和嵌套子程序的声明。属于局部，退出子 程序将不存在

2.可执行部分：赋值、控制执行过程及操纵Oracle数据的语句

3.异常处理部分（可选）：异常处理程序，负责处理执行存储过程中出现的异常

子程序优点：

​ 1.模块化：通过子程序可以将程序分解为可管理的、明确的逻辑模块

​ 2.可重用性：子程序在创建并执行后，就可以在任意数目的应用程序中使用

​ 3.可维护性：子程序可以简化维护操作。

​ 4.安全性：用户可以设置权限

2.存储过程用法

存储过程是执行某些操作的子程序，是执行特定任务的模块。

本质上，存储过程是命名的PL/SQL程序块，它可以被赋予参数并存储在数据库中，然后由一个应用程序或其他PL/SQL程序调用。

用法：（1）创建存储过程

​ （2）调用存储过程

​ 用命令在SQL提示符下调用

​ 在PL/SQL块中调用

​ （3）存储过程的参数模式

​ （4）存储过程的访问权限

​ （5）删除存储过程

（1）创建存储过程

语法：

create [or replace] procedure procedure_name
	[(parameter_list)]
{is|as}
	[local_declaration]
begin
	executable_statements
[exception]
	[exception_handlers]
end [procedure_name]
解释：
1.procedure_name：存储过程的名称
2.parameter_list：参数列表 可选
3.local_declaration：局部声明 可选
4.executable_statements：可执行语句
5.exception_handlers：异常处理程序 可选
6.or replace：可选，如果不包含or replace语句，则表示仅仅新建一个存储过程，如果系统存在该存储过程。则会报错；如果包含or replace，则表示如果系统中没有 此存储过程则新建，有就用现在的替换原来的存储过程

案例：

create or replace procedure salay(eno in number) is
esal emp.sal%type;
begin
  select sal into esal from emp where empno=eno;
  update emp set sal=sal*1.1 where empno=eno;
  dbms_output.put_line('涨前：'||esal||',涨后：'||(esal*1.1));
end salay;

（2）调用存储过程

1.用命令调用

语法：

exec[ute] procedure_name (parameters_list);
解释：
1.execute：执行命令，可以缩写为exec
2.procedure_name：过程的名称
3.parameters_list：参数列表

案例：

--调用以上的存储过程
exec salay(7369);--按位置传递参数
-----------------
exec salay(eno=>7369);--按名称传递参数

--混合传递，两者合二为一

2.在PL/SQL中调用

begin
  salay(7369);--以上三种参数传递方式都适用
end;

（3）存储过程的参数模式

调用程序是通过参数向被调用的存储过程传递值的。

参数传递的模式有三种：in、out、in out，即输入参数、输出参数和输入/输出参数。

in模式只能将实参传递给形参，进入函数内部，但只能读不能写，函数返回值时实参的值不变。

out模式会忽略调用时的实参值（或说该形参的初始值总是null），但函数内部可以被读或写，函数返回时形参的值会赋给实参。

in out具有前两种模式的特性，即调用时，实参的值总是传递给形参；结束时，形参的值传递给实参。

语法：

parameter_name [in | out | in out] datatype
[{:= | default} expression]
in模式是默认参数传递模式。若为指定，则是in

（4）存储过程的访问权限

存储过程创建之后，只有创建该存储过程的用户和管理员才有权调用它。其他用户如果要调用该存储过程，需要得到存储过程的execute权限。

案例：

--授予A_oe执行emp的权限
grant execute on salay to A_oe;
--撤销权限
revoke execute on salay from A_oe;

（5）删除存储过程

语法：

drop procedure procedure_name;

3.存储过程的调用和跟踪

（1）在sqlplus下调试

案例：

SQL>set serveroutput on;
SQL>--调用salay存储过程
SQL>show errors procedure salay;

（2）用PL/SQL Developer工具调试

1.获得debug connect session权限

grant debug connect session to A_hr;

2.进入测试窗口，编写调用存储过程代码

3.单击测试脚本中的“开始调试器”或者F9键进入调试状态

4.单击“单步进入”，进行调试

4.存储过程规则

1.存储过程中不可以直接使用DDL语句，可以通过动态SQL实现。但不建议频繁的使用DDL语句

2.存储过程必须有相应的出错处理功能

3.存储过程变量使用%type和％rowtype类型

4.必须在存储过程体中作异常捕获，并将异常信息通过os_Msg变量输出

5.–1 ~ -19999的异常为Oracle定义的异常代码

6.存储过程必须包含两个输出参数分别用于标识过程的执行状态及过程提示信息

7."WHEN OTHERS"必须放置在异常处理代码的最后面作为缺省处理器处理没有显式处理的异常

案例：

CREATE OR REPLACE PROCEDURE add_employee(
    eno employee.empno%type,                  --输入参数,雇员编号
    name employee.ename%type,                --输入参数,雇员名称
    salary employee.sal%type,                --输入参数,雇员薪水
    job employee.job%type DEFAULT 'CLERK',        --输入参数,雇员工种默认'CLERK'
    dno employee.deptno%type,                 --输入参数,雇员部门编号
    on_Flag OUT number,       --执行状态
    os_Msg OUT VARCHAR2       --提示信息
)
IS
BEGIN
   INSERT INTO employee (empno,ename,sal,job,deptno)VALUES (eno,name,salary,job, dno);
   on_Flag:=1;
   os_Msg:='添加成功';
EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
      on_Flag:=-1;
      os_Msg:='该雇员已存在。';
   WHEN OTHERS THEN
      on_Flag:=SQLCODE;
      os_Msg:=SQLERRM;     
END;
----------------------------------------------
DECLARE
   on_Flag NUMBER;
   os_Msg VARCHAR2(100);     
BEGIN
   --按位置传递参数
   add_employee(2111,'MARY',2000,'MANAGER',10,on_Flag,os_Msg);
   dbms_output.put_line(on_Flag||os_Msg);
END;

六.触发器

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

触发器的类型 1.语句级触发器 ​ 在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行 。 2.行级触发器（FOR EACH ROW） ​ 触发语句作用的每一条记录都被触发。在行级触发器中使用:old和:new伪记录变量, 识别值的状态。

触发器可用于 ​ 1.数据确认 ​ 2.实施复杂的安全性检查 ​ 3.做审计，跟踪表上所做的数据操作等 ​ 4.数据的备份和同步

查询触发器、过程及函数 ：

select * from user_triggers;
select * from user_source;

1.创建触发器

语法：

CREATE [or REPLACE] TRIGGER 触发器名
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF 列名]}
ON 表名
[FOR EACH ROW [WHEN(条件) ] ]
PLSQL 块

案例：

create or replace trigger securityEmp
  before insert
  on  emp
declare

begin
  if to_char(sysdate,'day') in ('星期一')
    or to_number(to_char(sysdate,'hh24')) not between 8 and 18 then 
    raise_application_error(-20001,'不能在非工作时间插入数据');
    end if;
end securityEmp;

2.触发语句与伪记录变量的值

触发语句	:old	:new
insert	所有字段都是空的（null）	将要插入的数据
update	更新以前该行的值	更新后的值
delete	删除以前该行的值	所有字段都是空（null）

案例：确认数据（检查emp表中sal 的修改值不低于原值）

create or replace trigger checksal
  before update of sal
  on emp
  for each row
declare

begin
  if :new.sal <:old.sal then
   raise_application_error(-20002,'更新后的工资比更新前小');

   end if;
end checksal;

其他

1.&

--手动给a赋值
select empno,&a from emp;  
select * from &a;
insert into emp(empno,sal) values(&a,&b)；

2.使用%TYPE和%ROWTYPE的区别

%TYPE：

定义一个变量，其数据类型与已定义的某个 数据变量的类型相同，或者与数据库表的某个列的数据类型

使用%TYPE 特性的优点在于：

1.所引用的数据库列的数据类型可以不必知道；
2. 所引用的数据库列的数据类型可以实时改变。

%ROWTYPE：

PL/SQL 提供%ROWTYPE 操作符, 返回一个记录类型, 其数据类型和数据库表的数据结构相一致。

使用%ROWTYPE 特性的优点在于：

1.所引用的数据库中列的个数和数据类型可以不必知道；
2.所引用的数据库中列的个数和数据类型可以实时改变。

记录类型：

记录类型是把 逻辑相关 的数据作为一个单元存储起来 ，称作 PL/SQL RECORD 的域(FIELD) ，其作用是存放互不相同但逻辑相关的信息。​

定义记录类型语法如下:

TYPE record_type IS RECORD
(
 Field1 type1 [NOT NULL] [:= exp1 ],
 Field2 type2 [NOT NULL] [:= exp2 ],
 . . . . . .
 Fieldn typen [NOT NULL] [:= expn ] 
 ) ;

提示 : 1) DBMS_OUTPUT.PUT_LINE 过程的功能类似于 Java 中的 System.out.println() 直接将输出结果送到标准输出中 . ​ 2) 在使用上述过程之前必须将 SQL * PLUS 的环境参数 SERVEROUTPUT 设置为 ON, 否则将看不到输出结果 :

set serveroutput on

3.案例：按员工的工种长工资,总裁1000元，经理长800元其，他人员长400元。

--总裁+1000 经理+800 其他400
declare 
--定义光标
          cursor list is select empno,job from emp;--集合
          peno emp.empno%type;--变量，工号
          pjob emp.job%type;--变量，职位
begin
  --打开光标
  open list;
  loop
    --取出一个员工
    fetch list into peno,pjob;
    exit when list%notfound;--找不到退出
    if pjob='SALESMAN' then update emp set sal=sal+1000 where empno=peno;--总裁
    elsif pjob='MANAGER' then update emp set sal=sal+800 where empno=peno;--经理
    else update emp set sal=sal+400 where empno=peno;--经理
    end if;
   --关闭循环
  end loop;
  --关闭光标
  close list;
end;