SSH Chapter 03 PL/SQL编程_ssh 怎么写sql-CSDN博客

本文链接：https://blog.csdn.net/weixin_40154786/article/details/110790871

SSH Chapter 03 PL/SQL编程笔记

本章目标 :

了解PL/SQL的概念
掌握变量的声明
理解并运用控制语句
会使用异常处理问题
了解游标的基本原理
掌握显式游标的用法
掌握Oracle存储过程

15分钟测评：

需求说明：

编写存储过程，根据输入雇员编号，修改该雇员薪水
薪水增加为原来的20%
要有异常处理 .

代码如下：

CREATE OR REPLACE PROCEDURE modify_sal(
   eno employee.empno%type
)
IS
  e1 EXCEPTION;
BEGIN 
  UPDATE employee SET sal=sal*1.2 WHERE empno=eno;
  IF SQL%NOTFOUND THEN
    RAISE e1;
  ELSE
    commit;  
  END IF;
EXCEPTION
  WHEN e1 THEN
    dbms_output.put_line('雇员编号不存在');
  WHEN others THEN
    dbms_output.put_line('其他异常'); 
END;

1. PL/SQL 基础知识

1.1 什么是PL/SQL

PL/SQL是由甲骨文公司在90年代初开发，以提高SQL的功能.PL/SQL是嵌入在Oracle数据库中的编程语言之一.

PL/SQL是Oracle数据库对SQL语句的扩展.在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL就是把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言.

虽然多个SQL语句也能实现同样的功能,但是相比而言,PL/SQL具有更为明星的一些优点:

PL/SQL支持静态和动态SQL.静态SQL支持DML操作和事务PL/SQL块控制.动态SQL是SQL允许嵌入PL/SQL块的DDL语句.

PL/SQL允许一次发送语句的整块到数据库.这降低了网络流量，并提供高性能的应用程序.

PL/SQL给编程人员高的生产效率，因为它可以查询，转换并在数据库中更新数据.

PL/SQL强劲的功能，如异常处理，封装，数据隐藏和面向对象数据类型可以节省设计和调试的时间.

编写PL/SQL应用程序是完全可移植的.

PL/SQL提供了高的安全级别.

PL/SQL提供了访问预定义SQL包.

PL/SQL提供了面向对象的编程支持.

PL/ SQL提供了用于开发Web应用程序和服务器页面的支持.

总结就是：PL/SQL是一门为扩展Oracle中SQL功能的编程语言.

1、PL/SQL 体系结构：

PL/SQL 引擎用来编译和执行PL/SQL 块或子程序,该引擎驻留在Oracle服务器中. PL/SQL 引擎仅执行过程语句,而将SQL语句发送给Oracle服务器的SQL语句执行器. 由SQL语句执行器执行这些SQL语句.体系结构如图:
在这里插入图片描述
PL/SQL的工作原理

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

该引擎执行所有过程语句

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

2、PL/SQL块简介：

PL/SQL 是一种块结构的语言,它将一组语句放在一个块中. 将逻辑上相关的声明和语句组合在一起.

PL/SQL 分为三个部分，声明部分、可执行部分和异常处理部分：

语法:

[DECLARE declarations]—声明开始关键字

/* 声明部分，包括PL/SQL中的变量、常量、类型及游标,
以及局部的存储过程和函数 */

BEGIN —执行部分开始的标志

executable statements /* 这里是执行部分，是整个PL/SQL块的主体部分 */

[EXCEPTION handlers] –异常开始部分的关键字

/* 这里是异常处理部分 */

END; --执行结束标志

其中执行部分不能省略

示例如下:

DECLARE
   qty_on_hand NUMBER(5);  ----声明部分定义变量、  游标和自定义异常

BEGIN
  SELECT quantity 
    INTO qty_on_hand --包含 SQL 和 PL/SQL语句的可执行部分
   FROM Products
    WHERE product = '芭比娃娃';
    IF qty_on_hand > 0 THEN
         UPDATE Products SET quantity = quantity + 1
          WHERE product = '芭比娃娃';
         INSERT INTO purchase_record
                 VALUES ('已购买芭比娃娃', SYSDATE);
    END IF;
    COMMIT;
EXCEPTION  /* 异常处理语句 */
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('出错:'|| SQLERRM);  
END;

使用PL/SQL块输出Hello,World:

-- 在PL/SQL控制台需要输出某个变量时,必须设置开启服务器输出,代码如下:
set serveroutput on;
DECLARE
   --注意语法	
   v_msg VARCHAR2(20);
BEGIN
   v_msg := 'World!!';
   DBMS_OUTPUT.PUT_LINE('Hello,'||v_msg);
EXCEPTION
   WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('Error'||SQLERRM);   
-- 注意语法 END之后必须加;     
END;

3、运算符和表达式：

PL/SQL 语言支持的操作符包含关系运算符,一般运算符和逻辑运算符等.与SQL语言类似,参照表:

关系运算符:

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

一般运算符:

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

逻辑运算符:

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

4、常量和变量声明

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

声明变量语法如下:

variable_name data_type[ ( size ) ] [ := init_value]

在语法中:

variable_name 表示变量名称
data_type 表示变量的SQL 或 PL/SQL 数据类型
size 指变量的范围
init_value 指定变量的初始值

声明常量的语法如下:

variable_name CONSTANT data_type := value;

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

标识符名不能超过30个字符
第一个字符必须为字母
不区分大小写
不能用"-"(减号)
不能是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

注意:

部分变量通常使用范围不广,需要了解的可以自行查阅相关资料

示例1:

给变量和常量声明赋值:

/*
|| 在注释开始的首行只放斜线星号，标志注释开始，
|| 然后注释块的每一行以双垂直线开头，突出注释后面
|| 的内容是注释部分，最后，将星号斜线单置于一行
*/
DECLARE
       v_ename 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';
      dbms_output.put_line('v_ename：'|| v_ename);
      dbms_output.put_line('v_rate：'|| v_rate);
END;

dbms_output.put_line('变量的值：'|| 变量名);
-- 在PL/SQL控制台需要输出某个变量时,必须设置开启服务器输出,代码如下:
set serveroutput on;

注意:

SELECT 语句在PL/SQL 中的写法稍有改变,为SELECT INTO.查询结果只能返回一条语句并赋值到变量中保存,返回多条或者零条语句则报错.在PL/SQL 中处理查询结果集会用到后面的游标技术.

通过上面的示例,需要明确一下编码规则:

(1) 利用缩进排列展现逻辑结构，保留字后出现的列开始缩进三个空格，如DECLARE下面声明变量行，上面的示例中可非常清晰的分辨出声明部分和可执行部分。

(2) 利用大小写增强可读性.保留字统一大写,应用程序专用名称或者标识符统一小写,以增强程序的可读性.

(3) 格式化单独语句.每行至多写一条语句:声明语句中尽量保持声明的数据类型靠近变量名,而不是与数据类型对齐.

(4) 格式化SQL语句.右对齐DML语句中的子句中的保留字. 如图所示:
在这里插入图片描述
提示:

Oracle 11g 新增特性 : 可以使用 PL/SQL 块中的赋值语句访问序列,提高序列使用的灵活性。例如：

v_no := emp_seq.nextval

5、注释

在PL/SQL 里,可以使用如下两种符号来写注释:

使用"-"(减号)加注释

PL/SQL 允许使用"-"来注释,它只能在一行有效.例如:
```
v_sal NUMBER(12,2); -- 人员的工资变量
```

使用"/* */"来加一行或者多行注释,建议使用如下方法进行多行注释:

/*
|| 在注释开始的首行只放斜线星号，标志注释开始，
|| 然后注释块的每一行以双垂直线开头，突出注释后面
|| 的内容是注释部分，最后，将星号斜线单置于一行
*/

其中"||"可以不写,但上面示例为推荐的多行注释写法

6、PL/SQL的编码规则

利用缩进排列展现逻辑结构
利用大小写增强可读性
格式化单独语句
格式化SQL语句
规范注释

1.2 PL/SQL 数据类型

1、标量数据类型

标量数据类型包含单个值，没有内部组件。标量数据类型包括数字、字符、布尔值和日期时间值四类。

这些类型有的是Oracle SQL中定义的数据类型，有的是PL/SQL自身附加的数据类型.字符型和数字型又有子类型，子类型只与限定的范围有关，比如NUMBER类型可以表示整数，也可以表示小数，而其子类型POSITIVE只表示正整数.

类型	说明
VARCHAR2(长度)	可变长度字符串，Oracle SQL定义的数据类型，在PL/SQL中使用时最长32767字节。在PL/SQL中使用没有默认长度，因此必须指定。
NUMBER(精度，小数)	Oracle SQL定义的数据类型
DATE	Oracle SQL定义的日期类型
TIMESTAMP	Oracle SQL定义的日期类型
CHAR(长度)	Oracle SQL定义的日期类型，固定长度字符，最长32767字节，默认长度是1，如果内容不够用空格代替。
LONG	Oracle SQL定义的数据类型，变长字符串基本类型，最长32760字节。在Oracle SQL中最长2147483647字节。
BOOLEAN	PL/SQL附加的数据类型，逻辑值为TRUE、FALSE、NULL
BINARY_INTEGER	PL/SQL附加的数据类型，介于-231和231之间的整数。
PLS_INTEGER	PL/SQL附加的数据类型，介于-231和231之间的整数。类似于BINARY_INTEGER，只是PLS_INTEGER值上的运行速度更快。
NATURAL	PL/SQL附加的数据类型，BINARY_INTEGER子类型，表示从0开始的自然数。
NATURALN	与NATURAL一样，只是要求NATURALN类型变量值不能为NULL。
POSITIVE	PL/SQL附加的数据类型，BINARY_INTEGER子类型，正整数。
POSITIVEN	与POSITIVE一样，只是要求POSITIVE的变量值不能为NULL。
REAL	Oracle SQL定义的数据类型，18位精度的浮点数
INT,INTEGER,SMALLINT	Oracle SQL定义的数据类型，NUMBERDE的子类型，38位精度整数。
SIGNTYPE	PL/SQL附加的数据类型，BINARY_INTEGER子类型。值有：1、-1、0。
STRING	与VARCHAR2相同。

2、LOB数据类型

Oracle 提供了LOB(Large Object) 类型,用于存储大的数据对象的类型.Oracle目前主要支持BFILE,BLOB,CLOB以及NCLOB类型.

3、属性类型

当声明一个变量的值是数据库中的一行或者是数据库中某列时，可以直接使用属性类型来声明.Oracle中存在两种属性类型：%TYPE和%ROWTYPE.

1) %TYPE

定义一个变量,其数据类型与已经定义的某个数据变量(尤其是表的某一列)的数据类型相一致,这是可以使用%TYPE.

使用%TYPE属性的优点在于:

(1) 可以不必知道所引用的数据库列的数据类型

(2) 所引用的数据库列的数据类型可以实时改变,容易保持一致,不用修改PL/SQL程序.

2) %ROWTYPE

引用数据库表中的一行作为数据类型，即RECORD类型（记录类型），是PL/SQL附加的数据类型.表示一条记录，就相当于Java中的一个对象.可以使用“.”来访问记录中的属性.

使用%ROWTYPE属性的优点在于:

(1) 可以不必知道所用的数据库中列的个数和数据类型

(2) 所引用的数据库中列的个数和数据类型可以实时改变,容易保持一致,不用修改PL/SQL程序.

总结:%TYPE值存储一个就是当列的值类型 %ROWTYPE存储一条记录,一条记录中所有的列的类型都在%ROWTYPE中

示例2

需求说明:根据员工编号查询员工信息.

/*
===========================================================
||         数据类型
============================================================
*/
DECLARE
   v_empno employee.empno%TYPE :=7788;
   v_rec employee%ROWTYPE;
BEGIN
   SELECT * INTO FROM employee WHERE empno=v_empno;
   DBMS_OUTPUT.PUT_LINE
	   ('姓名:'||v_rec.ename||'工资:'||v_rec.sal||
		'工作时间:'||v_rec.hiredate); 
END;

1.3 PL/SQL 控制语句

PL/SQL 程序可通过控制结构来控制命令执行流程.标准SQL没有流程控制的概念,而PL/SQL提供了丰富的流程控制语句.

1、条件控制

条件控制用于根据条件执行一系列语句.条件控制包括IF语句和CASE语句.

(1) IF语句语法如下:

语法:

– IF

IF <布尔表达式> THEN

PL/SQL 和 SQL语句

END IF;

示例演示如下:
DECLARE
 grade number := 100;
 remark varchar2(20);
BEGIN
 IF grade='100' THEN
   remark := 'iS Excellent';
 END IF;
 DBMS_OUTPUT.PUT_LINE(remark);
END;   
– IF ELSE

IF <布尔表达式> THEN

PL/SQL 和 SQL语句

ELSE

其它语句

END IF;

演示示例如下:
DECLARE
 grade number := 80;
 remark varchar2(20);
BEGIN
 IF grade=100 THEN
   remark := 'iS Excellent';
 ELSE
   remark := 'is Good';  
 END IF;
 DBMS_OUTPUT.PUT_LINE(remark);
END;
– IF ELSIF ELSE

IF <布尔表达式> THEN
PL/SQL 和 SQL语句
ELSIF < 其它布尔表达式> THEN
其它语句
ELSIF < 其它布尔表达式> THEN
其它语句
ELSE
其它语句
END IF;

提示: ELSIF 不能写成 ELSEIF

示例如下:
DECLARE
 grade number := 60;
 remark varchar2(20);
BEGIN
 IF grade=100 THEN
   remark := 'iS Excellent';
 ELSIF grade=80 THEN
   remark := 'is Good';
 ELSE 
   remark := 'is Normal';    
 END IF;
 DBMS_OUTPUT.PUT_LINE(remark);
END; 

(2) CASE语句语法如下:

语法:

---------格式一---------

CASE 条件表达式
WHEN 条件表达式结果1 THEN
语句段1
WHEN 条件表达式结果2 THEN
语句段2
…
WHEN 条件表达式结果n THEN
语句段n
[ELSE 条件表达式结果]
END CASE;

示例如下:
DECLARE
    grade number := 60;
    remark varchar2(20);
BEGIN
    CASE grade
       WHEN 100 THEN
          remark := 'iS Excellent';
       WHEN 80 THEN
          remark := 'is Good';
       ELSE 
          remark := 'is Normal';    
    END CASE;
    DBMS_OUTPUT.PUT_LINE(remark);
END;  
---------格式二---------

CASE
WHEN 条件表达式1 THEN
语句段1
WHEN 条件表达式2 THEN
语句段2
…
WHEN 条件表达式n THEN
语句段n
[ELSE 语句段]
END CASE;
DECLARE
    grade number := 90;
    remark varchar2(20);
BEGIN
    CASE 
       WHEN grade=100 THEN
          remark := 'iS Excellent';
       WHEN grade>=80 THEN
          remark := 'is Good';
       WHEN grade>=60 THEN
          remark := 'is Normal';   
       ELSE 
          remark := 'is Bad';    
    END CASE;
    DBMS_OUTPUT.PUT_LINE(remark);
END;  

2、循环控制

循环控制用于重复执行一系列语句.循环控制包括LOOP 和 EXIT 语句,使用EXIT语句可以立即退出循环;使用EXIT WHEN语句可以根据条件结束循环.

循环共有3中类型,包括LOOP循环,WHILE循环和FOR循环.

(1) LOOP 循环语法如下:

LOOP
要执行的语句;
EXIT WHEN <条件语句> --条件满足，退出循环语句
END LOOP;

(2) WHILE 循环语法如下:

WHILE <布尔表达式> LOOP
要执行的语句;
END LOOP;

(3) FOR 循环语法如下:

[<<循环标签>>]
FOR 循环计数器 IN [ REVERSE ] 下限 … 上限 LOOP
要执行的语句;
END LOOP [循环标签];

每循环一次，循环变量自动加1；使用关键字REVERSE，循环变量自动减1.跟在IN REVERSE 后面的数字必须是从小到大的顺序，而且必须是整数，不能是变量或表达式.可以使用EXIT 退出循环.

3、顺序控制

顺序控制语句用于按顺序执行语句.顺序控制包括NULL 语句和 GOTO 语句. GOTO语句不推荐使用,下面介绍NULL 语句.

NULL 语句 : NULL 语句是一个可执行语句,相当于一个占位符或不执行任何操作的空语句,它可以使用某些语句变得有意义,提高程序的可读性,保证其他语句结构的完整性和正确性.

示例3

需求说明:显示变量v_counter的值，如果该变量小于10，则增加10并显示该变量改变后的值.

/*
================================================================
||显示变量v_counter的值，如果该变量小于10，则增加10并显示该变量改变后的值.
================================================================
*/
DECLARE
    v_counter NUMBER := 5;
BEGIN
   DBMS_OUTPUT.PUT_LINE('v_counter的当前值为:'||v_counter);
    IF v_counter >= 10 THEN
      NULL; --为了使语法变得有意义,去掉NULL会报语法错误
    ELSE
	  v_counter := v_counter + 10;
		  DBMS_OUTPUT.PUT_LINE('v_counter的改变后值为:'||v_counter);
    END IF;
 END;

2. 异常处理

2.1 什么是异常

在运行程序时出现的错误叫做异常.发生异常后，语句将停止执行,PL/SQL引擎立即将控制权转到PL/SQL块的异常处理部分.注意:PL/SQL编译错误发生在PL/SQL程序执行之前,因此不能由PL/SQL异常处理部分来处理.

异常情况处理(EXCEPTION)用来处理正常执行过程中未预料的事件.这里介绍三种比较典型的即预定义异常,非预定义异常和用户定义异常.

1．预定义 ( Predefined )错误	ORACLE预定义的异常情况大约有24个。对这种异常情况的处理，无需在程序中定义，由ORACLE自动将其引发。
2．非预定义 ( Predefined )错误	即其他标准的ORACLE错误。对这种异常情况的处理，需要用户在程序中定义，然后由ORACLE自动将其引发。
3．用户定义(User_define) 错误	程序执行过程中，出现编程人员认为的非正常情况。对这种异常情况的处理，需要用户在程序中定义，然后显式地在程序中将其引发。

2.2 预定义异常

当PL/SQL程序违反 Oracle规则或超越系统限制时隐式引发.有PL/SQL为其声明的预定义文字.

ORACLE预定义的异常情况大约有24个.对这种异常情况的处理，无需在程序中定义，由ORACLE自动将其引发.常见的预定义异常如下:

异常	说明
ACCESS_INTO_NULL	未定义对象
CASE_NOT_FOUND	CASE 中若未包含相应的 WHEN ，并且没有设置 ELSE 时
COLLECTION_IS_NULL	集合元素未初始化
CURSER_ALREADY_OPEN	游标已经打开
DUP_VAL_ON_INDEX	唯一索引对应的列上有重复的值
INVALID_CURSOR	在不合法的游标上进行操作
INVALID_NUMBER	内嵌的 SQL 语句不能将字符转换为数字
NO_DATA_FOUND	使用 select into 未返回行，或应用索引表未初始化的元素时
TOO_MANY_ROWS	执行 select into 时，结果集超过一行
ZERO_DIVIDE	除数为 0
SUBSCRIPT_BEYOND_COUNT	元素下标超过嵌套表或 VARRAY 的最大值
SUBSCRIPT_OUTSIDE_LIMIT	使用嵌套表或 VARRAY 时，将下标指定为负数
VALUE_ERROR	赋值时，变量长度不足以容纳实际数据
LOGIN_DENIED	PL/SQL 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码
NOT_LOGGED_ON	PL/SQL 应用程序在没有连接 oralce 数据库的情况下访问数据
PROGRAM_ERROR	PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL 系统包
ROWTYPE_MISMATCH	宿主游标变量与 PL/SQL 游标变量的返回类型不兼容
SELF_IS_NULL	使用对象类型时，在 null 对象上调用对象方法
STORAGE_ERROR	运行 PL/SQL 时，超出内存空间
SYS_INVALID_ID	无效的 ROWID 字符串
TIMEOUT_ON_RESOURCE	Oracle 在等待资源时超时

异常处理程序的语法如下:

语法:

BEGIN

sequence_of_statements;

EXCEPTION

WHEN <exception_name> THEN

sequence_of_statements;

WHEN OTHERS THEN

sequence_of_statements;
END;

其中：OTHERS 处理程序确保不会漏过任何异常,如果没有在前面的异常处理部分显示获取命名的异常,它就可以获取其余的异常.PL/SQL 块只能有一个OTHERS 异常处理程序.可以使用函数SQLCODE和SQLERRM来返回错误代码和错误文本信息.

示例4:

/*
===========================================================
||         预定义异常
============================================================
*/
DECLARE
   v_ename employee.ename%TYPE;
BEGIN
   SELECT ename 
      INTO v_ename 
     FROM employee 
      WHERE empno=1234;
   dbms_output.put_line('雇员名:'||v_ename);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      dbms_output.put_line('雇员号不正确');   
   WHEN TOO_MANY_ROWS THEN
      dbms_output.put_line('查询只能返回单行');
   WHEN OTHERS THEN
      dbms_output.put_line('错误号：'||SQLCODE||'错误描述：'||SQLERRM);
END;

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

2.3 非预定义异常

当PL/SQL程序违反 Oracle规则或超越系统限制时隐式引发.无PL/SQL为其声明的预定义文字.因为非预定义异常只有编号，没有名称，所以不能直接处理.对这种异常情况的处理，需要用户在程序中定义，然后由ORACLE自动将其引发.

示例5:

需求说明:Dept表中deptno是主键,不能重复,所以执行如下语句时:

BEGIN
  INSERT INTO dept VALUES(40, 'CODE', 'CAL');
EXCEPTION
  WHEN others THEN
    dbms_output.put_line(SQLCODE||':'||SQLERRM);
END;

程序会抛出如下异常:

-1:ORA-00001: unique constraint (SCOTT.PK_DEPT) violated
PL/SQL procedure successfully completed

根据异常编号定义非预定义异常信息如下:

DECLARE
  e_unique EXCEPTION; --定义非预定义异常
  PRAGMA EXCEPTION_INIT(e_unique,-1);--在oracle错误号和异常之间建立关联
BEGIN
  INSERT INTO dept VALUES(40, 'CODE', 'CAL');
EXCEPTION
  WHEN e_unique THEN
	dbms_output.put_line('数据重复!!');
  WHEN others THEN
    dbms_output.put_line(SQLCODE||':'||SQLERRM);
END;

2.4 处理用户定义异常

程序在执行过程中,出现编程人员认为的非正常情况.对于这种异常情况的处理,需要用户在程序中定义,然后显式的在程序中将其引发.用户自定义的异常错误通过显示使用RAISE语句触发.当引发一个异常错误时,控制就转到EXCEPTION块异常错误部分.执行错误处理代码:

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

(1) 在PL/SQL 块的定义部分定义异常情况：

<异常情况> EXCEPTION;

(2) 抛出异常情况:

RAISE <异常情况>；

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

示例6:

需求说明: 查询编号为7788的雇员的福利补助（comm列）

/*
===========================================================
||       查询编号为7788的雇员的福利补助（comm列）.            ||  
============================================================
*/

DECLARE
 	v_comm employee.comm%TYPE;
	e_comm_is_null EXCEPTION; -- 1.定义异常类型变量
BEGIN
	SELECT comm INTO v_comm FROM employee WHERE empno=7788;
	IF v_comm IS NULL THEN
	   RAISE e_comm_is_null; -- 2.抛出
	END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
       dbms_output.put_line('雇员不存在！错误为:'||SQLCODE||SQLERRM);
    WHEN e_comm_is_null THEN -- 3.处理
	   dbms_output.put_line('该雇员无补助');
    WHEN others THEN
	   dbms_output.put_line('出现其他异常');
END;

/*
|| 从控制台接收一个员工编号来修改其工资,若更新不成功抛出异常,
|| 抛出用户自定义的异常
*/
DECLARE
   v_empno employee.empno%TYPE :=&empno;
   e_no_result  EXCEPTION;              --1、定义
BEGIN
   UPDATE employee SET sal = sal+100 WHERE empno = v_empno;
   IF SQL%NOTFOUND THEN
      RAISE e_no_result;                --2、抛出
   ELSE
      COMMIT;
   END IF;    
EXCEPTION
   WHEN e_no_result THEN                --3、处理
      DBMS_OUTPUT.PUT_LINE('数据更新失败');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;      

-- 备注:
-- SQL%NOTFOUND返回的数据类型是一个布尔值.布尔值与前一条sql语句相关.
-- 当最近的一条sql语句没有操作任何行的时候，返回true.否则返回false.
-- SQL%FOUND 的用法与sql%notfound用法相反，也是与最近的sql语句发生交互，
-- 如果影响行数大于0条，则为true,否则为false.
-- &变量名:是在sqlplus执行的时候，会提示你输入一个值,并赋值带引用它的变量

除了异常情况的处理外,RAISE_APPLICATION_ERROR存储过程可以重新定义异常错误消息,为应用程序提供一种与Oracle交互的方法.

RAISE_APPLICATION_ERROR 语法如下:

语法:

RAISE_APPLICATION_ERROR(error_number, error_message, [keep_errors] );

error_number: 表示用户为异常指定的编号,是从 –20,000 到 –20,999 之间的参数，这样就不会与 ORACLE 的任何错误代码发生冲突
error_message: 表示用户为异常指定的消息文本.消息长度可达2048字节,是与error_number表示关联的文本.

示例7:

/*
===========================================================
|         查询编号为7788的雇员的福利补助（comm列）.
============================================================
*/
DECLARE
 	v_comm employee.comm%TYPE;
	e_comm_is_null EXCEPTION; --定义异常类型变量
BEGIN
	SELECT comm INTO v_comm FROM employee WHERE empno=7788;
	IF v_comm IS NULL THEN
	   RAISE e_comm_is_null;-- 抛出
	END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
       dbms_output.put_line('雇员不存在！错误为:'||SQLCODE||SQLERRM);
    WHEN e_comm_is_null THEN
	  RAISE_APPLICATION_ERROR(-20001,'该雇员无补助!');
    WHEN others THEN
	  dbms_output.put_line('出现其他异常');
END;

3. 游标

在Oracle数据库中,游标是一个十分重要的概念.由于首次接触游标的概念,所以要求在学习过程中掌握显式游标的特点,并运用自如.

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

游标(Cursor)形象地看出一个变动的光标。它实际上是一个指针，它在一段Oracle存放数据查询结果集的内存中，它可以指向结果集中的任意记录，初始是指向首记录，像数组的结构.

3.1 游标的基本原理

在Oracle中,在执行一个有SELECT,INSERT,UPDATE和DELETE语句的PL/SQL块时,Oracle会在内存中为其分配一个缓冲区,将执行结果放在这个缓冲区中,==而游标是指向该区的一个指针.==游标为应用程序提供了一种多行数据查询结果集中的每行数据进行单独处理的方法,是嵌入式SQL语句的应用程序的常用编程方式.

3.2 游标分类

在Oracle中提供了两种游标类型,即静态游标和动态游标.静态游标是在编译时知道明确的SELECT语句的游标.静态游标分为两种类型,即隐式游标和显式游标.

备注:隐式游标

隐式游标:它被PL/SQL自动关联，也叫SQL游标，由Oracle管理，用户无法控制但可以得到它的属性信息.

每当运行SELECT或DML语句时，PL/SQL会打开一个隐式的游标.隐式游标不受用户的控制，这一点和显示游标有明显的不同.

隐式游标由PL/SQL自动管理
隐式游标的默认名称是SQL
SELECT或DML操作产生隐式游标
隐式游标的属性值始终是最新执行的SQL语句

隐式游标的属性:

%ISOPEN 判断是否打开，永远返回FALSE
%FOUND 检测DML操作是否有效，有效返回TRUE
%NOTFOUND 如果没有提取返回TRUE
%ROWCOUNT DML操作对数据影响的数量

3.3 显式游标

1. 显式游标的使用步骤

显式游标有标准的操作过程.使用显式游标的四个步骤:定义游标、打开游标、提取游标数据和关闭游标.

1) 声明游标

声明游标的语法如下:

– 声明游标

DECLARE CURSOR cursor_name[(parameter [, parameter]…)]

[RETURN return_type] IS SELECT_STATEMENT ;

在语法中:

cursor_name 指游标的名称
parameter 用于为游标指定输入参数.在指定的数据类型时,不能使用长度约束. 例如:NUMBER(4),CHAR(10)等都是错误的.
return_type 用于定义游标提取的行的类型
select_statement 指游标定义的查询语句

2) 打开游标

打开游标的语法如下:

OPEN cursor_name[(parameters)] ;

3) 提取游标

提取游标的语法如下:

FETCH cursor_name INTO variables;

在语法中:

cursor_name 指游标的名称
variables 是变量名

4) 关闭游标

关闭游标语法如下:

CLOSE cursor_name;

示例8:

需求说明:使用显式游标输出每个员工的姓名和薪水

/*
===========================================================
||         使用显式游标输出每个员工的姓名和薪水.              ||
===========================================================
*/
DECLARE
 name employee.ename%type; 
 sal employee.sal%type; --定义两个变量来存放ename和sal的内容
 CURSOR emp_cursor IS -- 声明显式游标并赋值
    SELECT ename,sal 
      FROM employee;
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;

/*
===========================================================
||          FOR 循环游标的用法.                       ||
============================================================
*/
--显示雇员表中所有雇员的姓名和薪水
DECLARE
    name employee.ename%type; 
    sal employee.sal%type; --定义两个变量来存放ename和sal的内容
	CURSOR emp_cursor IS
	   SELECT ename,sal FROM employee;
BEGIN
	FOR emp_record IN emp_cursor LOOP
	   name:=emp_record.ename;
	   sal:=emp_record.sal;
	   DBMS_OUTPUT.PUT_LINE
          ('第'||emp_cursor%ROWCOUNT||'个雇员：'
           ||name|| sal);
	END LOOP;
END;


/*
===========================================================
||         WHILE循环游标输出每个员工的姓名和薪水.   ||
===========================================================
*/
DECLARE
   v_name emp.ename%TYPE;
   v_sal emp.sal%TYPE;
   -- 声明游标
   CURSOR emp_cursor IS
      SELECT ename,sal 
         FROM emp;
BEGIN
   --打开游标
   OPEN emp_cursor;
   -- 先填充数据
   FETCH emp_cursor INTO v_name,v_sal;
   WHILE emp_cursor%FOUND LOOP --注意这里用%FOUND判断  
      DBMS_OUTPUT.PUT_LINE
       ('第'||emp_cursor%ROWCOUNT||'雇员'
        ||v_name||':'||v_sal);
      -- 重新填充数据
      FETCH emp_cursor INTO v_name,v_sal;
   END LOOP;
   -- 关闭游标
   CLOSE emp_cursor;
END;

2. 显示游标属性:

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

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

==使用游标时,如果处理过程中需要删除或更新行,在定义游标时必须使用SELECT ... FOR UPDATE语句,而在执行DELETE和UPDATE时使用WHERE CURRENT OF子句指定游标的当前行.==声明更新游标的语法如下:

语法:

CURSOR cursor_name IS

select_statement FOR UPDATE [OF columns]

在语法中:

FOR UPDATE [OF columns] 为更新查询,锁定选择的行

当选择单表更新查询时,可以省略OF子句:
当选择多个表更新查询时,被锁定的行来源于OF字句后声明的列所在的表中的行.例如:使用FOR UPDATE of sal,则锁定employee表中的行;使用FOR UPDATE of dname,则锁定dept表中的行.

使用FOR UPDATE子句声明游标之后,可以使用一下语法更新行.

语法:

UPDATE table_name

SET column_name = column_value

WHERE CURRENT OF cursor_name;

在语法中:

多表查询更新时,更新表为锁定行所在的表.

示例9:

需求说明:多表查询更新时，更新表为锁定行所在表.

/*
===========================================================
||         多表查询更新时，更新表为锁定行所在表.  ||
============================================================
*/

DECLARE
  CURSOR emp_cursor IS
    SELECT ename,sal 
      FROM employee e INNER join dept d
	       ON  e.deptno=d.deptno
       FOR UPDATE OF sal;-- 更新薪水
   -- 用于loop循环中的临时变量      
   v_emp emp_cursor%ROWTYPE;    
 BEGIN
   OPEN emp_cursor;	
   LOOP
     FETCH emp_cursor INTO v_emp; 
     EXIT WHEN emp_cursor%NOTFOUND;
     UPDATE employee
        SET sal=sal+200
	  WHERE CURRENT OF emp_cursor;--更新当前行
   END LOOP;
   CLOSE emp_cursor;
END;


/*
===========================================================
||      多表查询更新时，更新表为锁定行所在表.(使用FOR循环简化)  ||
============================================================
*/
DECLARE
   cursor emp_cursor is
    select ename,sal
      from employee
     for update of sal;
BEGIN
  FOR v_emp IN emp_cursor LOOP
    update employee 
      set sal = sal+200
       where current of emp_cursor;
  END LOOP;
END;  

/*
===========================================================
||      多表查询更新时，更新表为锁定行所在表.(使用WHILE循环简化)  ||
============================================================
*/
DECLARE
   -- 声明游标
   CURSOR emp_cursor IS
      SELECT sal 
         FROM employee 
            FOR UPDATE OF sal;
   -- 用于loop循环中的临时变量        
   v_emp emp_cursor%ROWTYPE;         
BEGIN
   --打开游标
   OPEN emp_cursor;
   -- 使用WHILE
   FETCH emp_cursor INTO v_emp;
   WHILE emp_cursor%FOUND LOOP
      update employee 
         set sal = sal-200
      where current of emp_cursor; 
      FETCH emp_cursor INTO v_emp;   
   END LOOP;
   -- 关闭游标
   CLOSE emp_cursor;
END;

上例只能更新employee表,不能更新dept表

4. 使用循环游标简化游标的读取

使用循环游标可简化显式游标的处理代码. 循环游标隐式打开由标,自动从活动集获取行,然后在处理完所有行时关闭游标.循环游标自动创建%ROWTYPE类型的变量并将此变量用作记录索引.其语法如下:

语法:

FOR record_index IN cursor_name

LOOP

executable_statements

END LOOP;

在语法中:

record_index 是PL/SQL声明的记录变量.此变量的属性声明为%ROWTYPE类型,作用与在FOR循环之内,即在FOR循环之外不能访问此变量.

循环游标的特性如下:

从游标中提取了所有记录之后自动终止
提取和处理游标中的每一条记录
如果在提取记录之后%NOTFOUND属性返回TRUE,则终止循环
如果未返回行,则不进入循环

示例10:

需求说明:FOR 循环游标的用法.

/*
===========================================================
||          FOR 循环游标的用法.                       ||
============================================================
*/
--显示雇员表中所有雇员的姓名和薪水
DECLARE
	CURSOR emp_cursor IS
	   SELECT ename,sal FROM employee;
BEGIN
	FOR emp_record IN emp_cursor LOOP
	   DBMS_OUTPUT.PUT_LINE
          ('第'||emp_cursor%ROWCOUNT||'个雇员：'
           ||emp_record.ename|| emp_record.sal);
	END LOOP;
END;


/*
===========================================================
||      多表查询更新时，更新表为锁定行所在表.(使用FOR循环简化)  ||
============================================================
*/
DECLARE
   cursor emp_cursor is
    select ename,sal
      from emp
     for update of sal;
BEGIN
  FOR v_emp IN emp_cursor LOOP
    update emp 
      set sal = sal-200
       where current of emp_cursor;
  END LOOP;
END;

5. `NO_DATA_FOUND` 和 `%NOTFOUND` 区别

(1) SELECT ... INTO 语句返回0条记录时触发NO_DATA_FOUND

(2) 当UPDATE或DELETE语句的WHERE子句未找到时,触发%NOTFOUND

(3) 在提取循环中用%NOTFOUND或%FOUND来确定循环的退出条件,而不用NO_DATA_FOUND

4. 存储过程

存储过程是Oracle开发者在数据转换或查询报表时经常使用的方式之一.它就是像编程语言一样一旦运行成功，就可以被用户随时调用，这种方式极大的节省了用户的时间，也提高了程序的执行效率.存储过程在数据库开发中使用比较频繁，它有着普通SQL语句不可替代的作用.

所谓存储过程，就是一段存储在数据库中执行某种功能的程序.其中包含一条或多条SQL语句，但是它的定义方式和PL/SQL中的块、包等有所区别.

存储过程可以通俗地理解为是存储在数据库服务器中的封装了一段或多段SQL语句的PL/SQL代码块.在数据库中有一些是系统默认的存储过程，那么可以直接通过存储过程的名称进行调用.另外，存储过程还可以在编程语言中调用，如Java、C#等.

存储过程是存储在数据库中提供所有用户程序调用的子程序，定义存储过程的关键字为procedure

4.1 子程序的组成

子程序是已命名的PL/SQL块,它们存储在数据库中,可以为它们指定参数,也可以从任何数据库客户端和应用程序中调用它们.子程序包括存储过程和函数.使用存储过程执行操作,使用函数执行操作并返回值,就像应用程序中的方法一样.

与匿名的PL/SQL块一样,子程序具有声明部分,可执行部分,异常处理部分(可选).

1. 声明部分

声明部分包括类型,游标,常量,变量,异常和嵌套子程序的声明.这些项是局部的,退出子程序将不复存在

2. 可执行部分

可执行部分包括赋值,控制执行过程及操作Oracle数据的语句

3. 异常处理部分

异常处理部分包括异常处理程序,负责处理执行存储过程中出现的异常.

子程序的优点如下:

模块性：子程序可以将程序划分成易于管理，定义明确的逻辑模块，它支持自顶向下的设计方法.

可维护性：子程序可以简化维护操作,如果一个子程序受到影响,则只需修改子程序的定义.

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

安全性：通过设置权限，使数据更安全.

4.2 存储过程用法

下面介绍存储过程的基本用法:

创建存储过程
调用存储过程:用命令在SQL 提示符下调用或者在PL/SQL块中调用
存储过程的参数模式
存储过程的访问权限
删除存储过程

1. 创建存储过程

语法:

– 创建存储过程，可指定运行过程需传递的参数

CREATE [OR REPLACE] PROCEDURE

<procedure name> [(<parameter list>)]

IS|AS – 这里is，as都可以，后面还可以声明变量，如：as resultCount number

<local variable declaration>

BEGIN

----这里写sql业务代码

<executable statements>

[EXCEPTION

----这里写异常处理信息，常用处理方法when then—when others then—end;

<exception handlers>]

END;

在语法中:

procedure_name : 存储过程的名称
parameter_list : 参数列表,可选
local variable declaration : 局部声明,可选
executable statements : 可执行语句
exception handlers : 异常处理程序,可选
OR REPLACE : 可选.如果不包含OR REPLACE语句,则表示仅仅新建一个存储过程,如果系统存在该存储过程,则会报错;如果包含OR REPLACE语句,则表示系统中没有此存储过程的则新建,有就用现在的替换原来的存储过程.

示例11:

需求说明:创建一个添加员工记录的存储过程.

/*
===========================================================
||        添加员工记录.            ||
============================================================
*/
CREATE OR REPLACE PROCEDURE add_employee(
    eno NUMBER,  						--输入参数,雇员编号
    name VARCHAR2,						--输入参数,雇员名称
    salary NUMBER,						--输入参数,雇员薪水
    job VARCHAR2 DEFAULT 'CLERK',		--输入参数,雇员工种默认'CLERK'
    dno NUMBER 							--输入参数,雇员部门编号
)
IS

BEGIN
   INSERT INTO employee 
      (empno,ename,sal,job,deptno)VALUES (eno,name,salary,job, dno);
END;

-- 查看当前用户下有哪些存储过程
select * from user_objects where object_type='PROCEDURE';
-- 查看存储过程的内容
SELECT text FROM user_source WHERE NAME='ADD_EMPLOYEE' ORDER BY line;

提示:

上例中有几点说明:

(1) 存储过程参数值声明类型,不声明大小. 例如: eno NUMBER 不能写成 eno NUMBER(4)

(2) job 列中有一个默认值CLERK,调用时如果不提供该参数值, 则自动用默认值.

2. 调用存储过程

存储过程创建完成后,通过授权,就可以被调用了.

1) 用命令调用

用命令在SQL提示符下调用,使用EXECUTE语句来执行过程

(1) 执行存储过程.其语法如下:

语法:

EXEC[UTE] procedure_name (parameters_list);

语法中:

EXECUTE : 执行命令,可以缩写为 EXEC.
procedure_name : 过程的名字
parameters_list : 参数的列表

(2) 参数的传递方式.参数的传递方式可分为三种:

按位置传递参数:

例如:

EXEC add_employee(1111,'MARY',2000,'MANAGER',10);

按名称传递参数:

按名称传递参数加载调用时按名称对应.名称的对应关系是重要的,次序不重要

例如:
```
EXEC add_employee(dno=>10,name=>'MARY',salary=>2000,job=>'MANAGER',eno=>1112);
```
混合方式传递参数:

例如:
```
EXEC add_employee(1113,dno=>10,name=>'MARY',salary=>2000,job=>'MANAGER');
```
其中,雇员编号为1113,后面的部门编号按照名称传递参数,后续都要按照名称传递参数,不可以按照位置传递.

2) 在PL/SQL 块中调用

在PL/SQL块中调用存储过程,示例如下:

示例12:

/*
===========================================================
||         PL/SQL下调用存储过程              ||
============================================================
*/
BEGIN
   --按位置传递参数
   add_employee(2111,'MARY',2000,'MANAGER',10);
   --按名字传递参数
   add_employee(dno=>10,name=>'MARY',salary=>2000,eno=>2112, job=>'MANAGER');
   --混合方法传递参数
   add_employee(3111,dno=>10,name=>'MARY',salary=>2000,
job=>'MANAGER');
   --默认值法
   add_employee(4111,dno=>10,name=>'MARY',salary=>2000);
END;

注意:

在PL/SQL块中调用存储过程时不需要写EXEC, 直接写存储过程名称即可 , EXEC是命令行中调用存储过程的命令

3. 存储过程的参数模式

调用程序是通过参数向被调用的存储过程传递值的.参数传递的模式有三种:IN,OUT和IN OUT,即输入参数,输出参数和输入/输出参数.

IN: 输入类型,即由应用程序将实参传入oracle存储过程中；这种参数在存储过程中是只读参数，在存储过程中无法对该类型的参数进行修改,存储过程返回时实参的值不变；

OUT : 输出参数，是在存储过程中对该值进行赋值，在程序中获取值.

IN OUT : 输入输出参数，兼具以上两种特性，但可读可写.即调用时,实参的值传递给形参;结束时, 形参的值传递给实参.

语法:

parameter_name [IN | OUT | IN OUT] datatype

[{ := | DEFAULT } expression ]

IN模式是默认的参数传递模式.如果未指定参数的模式,则认为该参数是 IN 参数. 而对于OUT 和IN OUT参数,必须明确指定OUT 和IN OUT.

在返回调用环境之前,必须先给OUT 和 IN OUT 参数赋值.

可以在参数列表中为IN参数赋予一个默认值 , 但不能为OUT,IN OUT参数赋予默认值.

示例13:

--定义打印的存储过程
CREATE OR REPLACE PROCEDURE println (str VARCHAR)
AS
BEGIN
  dbms_output.put_line(str);
END;

定义IN模式参数:

--定义测试in模式的存储过程
CREATE OR REPLACE PROCEDURE pro(p1 IN INT,p2 IN INT)--参数的个数、类型可以自定义，但是参数不允许指定长度
AS
BEGIN
  println(p1);
  --p2:=11; --in模式参数不能为其赋值
  println(p2);
END;

--通过语句块调用存储过程
BEGIN
  pro(10,100);
END;

测试OUT模式的参数

--测试out模式的存储过程
CREATE OR REPLACE PROCEDURE pro(p3 OUT INT)
AS
BEGIN
  println(p3);--p3会忽略传入的值
  p3:=33;--设定存储过程调用后的值
END;
--注意out参数传递时 必须使用变量的方式 而不能使用值得方式
DECLARE
   var3 INT :=30;--声明一个变量用于设定存储过程调用前的值
BEGIN
   println('存储过程调用前的值：'||var3);
   pro(var3);--调用pro存储过程重新赋值；调用过程，如果过程形参是out模式，必须采用变量实参
   println('存储过程调用后的值：'||var3);
END;

测试IN OUT 模式参数

--测试in out模式的存储过程
CREATE OR REPLACE PROCEDURE pro(p4 IN OUT INT)
AS
BEGIN
  println(p4);
  p4:=44;--in out模式参数的值可以修改
END;

DECLARE
   age INT :=40;--声明一个变量用于设定存储过程调用前的值
BEGIN
   println('存储过程调用前的值：'||age);
   pro(age);--调用pro存储过程重新赋值
   println('存储过程调用后的值：'||age);
END;

4. 存储过程的访问权限

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

示例14:

需求说明:授予A_oe执行add_employee的权限:

-- 授予A_oe执行add_employee的权限:
GRANT EXECUTE ON add_employee TO a_oe;

需求说明:撤销A_oe执行add_employee的权限:

-- 撤销A_oe执行add_employee的权限:
REVOKE EXECUTE ON add_employee FROM a_oe;

5. 删除存储过程

存储过程可以使用DROP PROCEDURE语句来删除 , 其语法如下 , 其中procedure_name 是存储过程的名字.

语法如下:

DROP PROCEDURE procedure_name;

示例17

需求说明:删除存储过程add_employee

-- 删除存储过程
DROP PROCEDURE add_employee;

4.3 存储过程的调试与跟踪

1. 在`SQL*Plus`下调试

在SQL*Plus下调试主要用的方法是使用SHOW ERRORS语句.

例如:

SET SERVEROUTPUT ON;
--调试 add_employee存储过程
SHOW ERRORS PROCEDURE add_employee;

2. 用PL/SQL Developer 工具调试

获得DEBUG CONNECT SESSION权限:
```
GRANT DEBUG CONNECT SESSION TO a_hr;
```
进入测试窗口，调用存储过程代码。
单击测试脚本中的"开始调试器"按钮，或者按 "F9" 键进入调试状态：
单击"单击步入" 按钮，或者 Ctrl+N 快捷键，进行调试。如图所示：

注意：若想存储过程调试，需要添加该存储过程的调试信息，如图所示：在这里插入图片描述

4.4 存储过程规则

以下是编写存储过程需遵循的规则:

存储过程中不可以直接使用DDL语句，可以通过动态SQL实现.但不建议频繁的使用DDL语句
存储过程必须有相应的出错处理功能
存储过程变量使用%type和％rowtype类型
必须在存储过程体中作异常捕获，并将异常信息通过os_Msg变量输出
–1 ~ -19999的异常为Oracle定义的异常代码
存储过程必须包含两个输出参数分别用于标识过程的执行状态及过程提示信息
"WHEN OTHERS"必须放置在异常处理代码的最后面作为缺省处理器处理没有显式处理的异常

示例18:

需求说明:将add_employee按照推荐规则修改:

/*
=========================================================
||         将add_employee按照推荐规则修改.               ||
=========================================================
*/
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;

5. 存储函数(理解)

5.1 存储函数语法如下:

-- 存储函数和存储过程创建的语法类似
create [or replace] function 函数名(Name1 in type,Name2 in type,..)   
   return 返回值类型
   is | as
     定义参数
   begin
    存储函数过程
    return (结果变量);
   end；

5.2 存储函数案例演示:

需求: 定义根据empno求emp年薪的存储函数 , 语法如下:

-- 根据empno求emp年薪 存储函数
-- 存储函数和存储过程的参数类型 都不能声明长度
-- 存错函数的返回值类型不能带长度
create or replace function fun_getyearsal(eno in number) 
return number
is
  v_sal number;
begin
  select sal*12 + nvl(comm,0) into v_sal from emp where empno = eno;
  return v_sal;
end;


-- plsql中调用存储函数
-- 存储函数在调用的时候 返回值需要接收
declare
  v_sal number;
begin
  v_sal := fun_getyearsal(7369);
  dbms_output.put_line(v_sal);
end;
-- 也可以使用SQL语句调用函数
select fun_getyearsal(7369) from dual;

5.3 存储过程和存储函数的区别:

存储过程和存储函数的区别:

语法区别: 关键字不一样(存储函数比存储过程多了两个return关键字)

本质区别: 存储函数有返回值 , 存储过程没有返回值.

如果存储过程想实现有返回值的效果 , 必须使用out类型的参数 , 即便是存储过程使用了out类型的参数 , 其本质也不是真的有了返回值 , 而是在存错过程内部给out类型的参数做了赋值操作 , 在执行完毕之后 , 可以直接拿到out类型的参数的值

使用存储过程实现5.2案例需求:

-- 存储过程实现年薪
create procedure proc_getyearsal(eno in number,sumsal out number)
is
 
begin
  select sal*12 + nvl(comm,0) into sumsal from emp where empno = eno;
end;
 
-- plsql中调用
declare
  total number;
begin
  proc_getyearsal(7369,total);
  dbms_output.put_line(total);
end;

存储函数可以直接嵌套在SQL语句中

需求:查询雇员的姓名 , 以及所在的部门名称

示例如下:

-- 创建根据部门编号查询部门名称 并返回部门名称
create or replace function fun_dname(dno dept.deptno%type) 
return dept.dname%type
is
  v_name dept.dname%type;
begin
  select dname into v_name from dept where deptno=dno;
  return v_name;
end;  

-- 查询雇员的姓名 , 以及所在的部门名称
select e.ename,fun_dname(e.empno) from emp;

6. Java调用存储过程和存储函数

6.1 JDBC调用存储过程和存储函数

JDBC调用存储过程或者存储函数跟执行DML操作方法类似:

加载驱动
创建Connection对象
编写SQL语句
通过conn.prepareCall(sql)的这个方法进行存储过程和存储函数的调用,返回值使用CallableStatement接收
输入参数可以直接使用set方法 , 输出参数或者存储函数的返回值使用CallableStatement对象的registerOutParameter(int num, OracleTypes.NUMBER)方法来进行设置
释放资源.

不同的SQL书写方式:

存储过程：String sql = “{call proc_getsumsal(?,?)}”;
{call 存储过程名(?,?)} 无返回值
存储函数：String sql = “{ ？ = call f_getsumsal(?)}”;
{？ = call 存储函数名(?)} 有返回值

调用存储过程 , 代码如下:

@Test
public void demo01() throws Exception {
    //注册驱动
    Class.forName("oracle.jdbc.driver.OracleDriver");
    //获取连接
    String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
    String username = "scott";
    String password = "tiger";
    Connection conn = DriverManager.getConnection(url, username, password);
    //获取执行SQL的statement.这是一个固定格式  proc_gettotalsal是一个存储过程
    String sql = "{call proc_getyearsal(?,?)}";
    CallableStatement cs = conn.prepareCall(sql);
    // 设置输入参数
    cs.setInt(1, 7369);
    // 设置输出参数
    cs.registerOutParameter(2, OracleTypes.NUMBER);
    // 执行
    cs.execute();
    // 获取输出值
    int total = cs.getInt(2);
    System.out.println(total);
    // 关闭资源
    cs.close();
    conn.close();
}

单独调用存储函数 , 代码如下:

@Test
public void demo02() throws Exception {
    //注册驱动
    Class.forName("oracle.jdbc.driver.OracleDriver");
    //获取连接
    String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
    String username = "scott";
    String password = "tiger";
    Connection conn = DriverManager.getConnection(url, username, password);
    //获取执行SQL的statement.
    //这是一个固定格式  fun_getsumyearsal是一个存储函数
    String sql = "{? = call fun_getyearsal(?)}";
    CallableStatement cs = conn.prepareCall(sql);
    // 设置存储函数的返回值类型
    cs.registerOutParameter(1, OracleTypes.NUMBER);
    // 设置输入参数
    cs.setInt(2, 7369);
    // 执行
    cs.execute();
    // 获取返回值
    int total = cs.getInt(1);
    System.out.println(total);
    // 关闭资源
    cs.close();
    conn.close();
}

调用SQL(包含存储函数):

@Test
public void demo03() throws Exception {
    //加载驱动 oracle.jdbc.driver.OracleDriver
    Class.forName("oracle.jdbc.driver.OracleDriver");
    String url = "jdbc:oracle:thin:@localhost:1521:orcl";
    String username = "scott";
    String password = "tiger";
    Connection connection = DriverManager.getConnection(url, username, password);
    String sql = "select fun_getyearsal(?) from dual";
    PreparedStatement ps = connection.prepareStatement(sql);
    ps.setInt(1,7788);
    ResultSet rs = ps.executeQuery();
    if (rs.next()){
        System.out.println(rs.getInt(1));
    }
    rs.close();
    ps.close();
    connection.close();
}

6.2 Hibernate调用存储过程和存储函数:

使用Session调用没有输出参数的存储过程
先在数据库中创建没有输出参数的存储过程 , 例如打印员工的年薪,代码如下:

create or replace procedure proc_printyearsal
 (eno in emp.empno%type)
 is
  sumsal number;
 begin
   select sal*12+nvl(comm,0) into sumsal from emp 
      where empno=eno;
   -- dbms_output.put_line(sumsal);
 end;

使用Session调用上面的存储过程 , 代码如下:

@Test
public void demo03() {
    final StandardServiceRegistry registry = new StandardServiceRegistryBuilder()
            .configure() // configures settings from hibernate.cfg.xml
            .build();
    SessionFactory sessionFactory =
            new MetadataSources(registry).buildMetadata().buildSessionFactory();
    Session session = sessionFactory.openSession();
    Transaction tx = session.beginTransaction();
    //调用没有输出参数的存储过程 也可以是使用参数命名的方式
    String sql = "{call proc_printyearsal(?0)}";
    session.createSQLQuery(sql).setParameter(0, 7788).executeUpdate();
    tx.commit();
    session.close();
    sessionFactory.close();
}

使用Session调用有输出参数的存储过程
先在数据库中创建有输出参数的存储过程 , 例如打印员工的年薪,代码如下:

-- 存储过程实现年薪
create or replace procedure proc_getyearsal(eno in number,sumsal out number)
is
 
begin
  select sal*12 + nvl(comm,0) into sumsal from emp where empno = eno;
end;

使用Session调用上面有输出参数的存储过程 , 代码如下:

@Test
public void demo04() {
    final StandardServiceRegistry registry = new StandardServiceRegistryBuilder()
            .configure() // configures settings from hibernate.cfg.xml
            .build();
    SessionFactory sessionFactory =
            new MetadataSources(registry).buildMetadata().buildSessionFactory();
    Session session = sessionFactory.openSession();
    Transaction tx = session.beginTransaction();
    //调用有输出参数的存储过程
    ProcedureCall procedureCall = session.createStoredProcedureCall(
            "proc_getyearsal");
    //设置输入参数并绑定值
    procedureCall.registerParameter("eno", Long.class,
            ParameterMode.IN).bindValue(7788L);
    //设置输出参数
    procedureCall.registerParameter("sumsal", Long.class,
            ParameterMode.OUT);
    //获取输出参数
    Object result = procedureCall.getOutputParameterValue("sumsal");
    System.out.println(result);
    tx.commit();
    session.close();
    sessionFactory.close();
}

使用Session单独调用存储函数:

@Test
public void demo05() {
    final StandardServiceRegistry registry = new StandardServiceRegistryBuilder()
           .configure() // configuressettings from hibernate.cfg.xml
           .build();
    SessionFactory sessionFactory =
            new MetadataSources(registry).buildMetadata().buildSessionFactory();
    Session session = sessionFactory.openSession();
    Transaction tx = session.beginTransaction();
    //单独调用存储函数
    String sql = "{?=call fun_getyearsal(?)}";
    session.doWork((connection)->{
        CallableStatement cs =
            connection.prepareCall(sql);
        cs.setInt(2, 7788);
        cs.registerOutParameter(1,OracleTypes.NUMBER);
        cs.execute();

        System.out.println(cs.getInt(1));
    });
    tx.commit();
    session.close();
    sessionFactory.close();
}

使用Session调用SQL(包含存储函数):

@Test
public void demo06() {
    final StandardServiceRegistry registry = new StandardServiceRegistryBuilder()
        .configure() // configures settings from hibernate.cfg.xml
        .build();
    SessionFactory sessionFactory =
        new MetadataSources(registry).buildMetadata().buildSessionFactory();
    Session session = sessionFactory.openSession();
    Transaction tx = session.beginTransaction();
    //调用存储函数 也可以使用命名参数传递 :eno
    String sql = "select fun_getyearsal(?1) from dual";
    Object result = session.createNativeQuery(sql).setParameter(1, 7788).uniqueResult();//eno
    System.out.println(result);
    tx.commit();
    session.close();
    sessionFactory.close();
}

7. 触发器

7.1 触发器定义:

触发器（TRIGGER）是一种特殊的存储过程，它不能被显式地调用，而是由事件来触发某个操作.这些事件包括INSERT语句、UPDATE语句和DELETE语句.当数据库系统执行这些事件时，会激活促发其执行相应的操作

一句话总结 : 触发器 , 就是制定一个规则 , 在我们做增删改操作的时候 , 只要满足该规则 , 自动触发 , 无需调用

7.2 创建和使用触发器

触发器主要分为两种:

语句触发器 : 对每个DML语句执行一次
行级触发器 : 对DML语句影响的每个行执行一次
举例说明:如果一条insert语句在一个表中插入500行, 那么这个表的语句级触发器只执行一次, 而行级触发器要执行500次

语法如下:

create [or replace] trigger trigger_name --触发器名称
    {before | after} trigger_event --触发事件
    on table_name --表名
    [for each row] --指定创建的是行级触发器，若没有该子句则创建的是语句级触发器
    [when trigger_condition] --添加的触发条件	
    trigger_body --触发体，PL/SQL块

语法解释:

trigger_name：触发器名称
before | after : 指定触发器是在触发事件发生之前触发还暗示发生之后触发
trigger_event：触发事件，在DML触发器中主要为insert、update、delete等
table_name：表名，表示发生触发器作用的对象
for each row:指定创建的是行级触发器,若没有该子句则创建的是语句级触发器
备注:加上for each row是为了使用:old或者:new对象或者一行记录

when trigger_condition：添加的触发条件
trigger_body：触发体，是标准的PL/SQL语句块

7.3 案例演示

1. 语句触发器案例

插入一条部门记录 , 并在控制台上打印新增一个部门,语句如下:

-- 在PL/SQL控制台需要输出某个变量时,必须设置开启服务器输出,代码如下:
set serveroutput on;

create or replace trigger tri_dept
   after insert 
   on dept
declare
begin
  dbms_output.put_line('新增一个部门');
end;

执行部门的插入语句 , 控制台打印:新增一个部门:

insert into dept values(60,'测试部','西区');

2. 行级触发器案例

不能给员工降薪 , 若修改之后的薪水比原来的低 , 则需要将异常信息抛给客户端

-- raise_application_error(-20001,'不能给员工降薪');
-- 是将应用程序专有的错误从服务器端转达到客户端应用程序
-- 函数里面参数是:错误代码和错误提示的内容，都是自定义的,
-- 但是错误代码数字必须在 -20001 到 -20999 之间,这样就不会与 ORACLE 的任何错误代码发生冲突
create or replace trigger tri_emp
   before update
   on emp
   for each row
declare
begin
   if :old.sal>:new.sal then
     raise_application_error(-20001,'不能给员工降薪');
   end if;  
end;
-- :old为修改之前的一行记录 
-- :new为修改之后的一行记录

执行如下修改员工薪水的语句:

update emp set sal=sal-200 where empno=7788;

程序抛出的异常如图所示：在这里插入图片描述

3. 触发器实现Oracle中的主键自增

使用触发器配合Oracle中的序列 , 为部门表实现主键自增

分析 : 在用户进行插入操作之前 , 获取到用户即将插入的数据 , 然后给执行表中的主键赋值(使用行级触发器)

-- 先创建一个与部门表有关的序列
CREATE SEQUENCE  seq_dept_id
    START WITH 50
    INCREMENT BY 10
    MINVALUE 50
    MAXVALUE 100
    NOCACHE;
    
-- 给部门定义主键自增的触发器
create or replace trigger tri_dept_id
   before insert
   on dept_demo
   for each row
declare
begin
  select seq_dept_id.nextval into :new.deptno from dual;
end;   
-- 为部门新增一条信息(不指定主键)
insert into dept(dname,loc) values('开发部','东区');
-- 可以多执行几次 或者给定主键之后 再新增几次看看效果

如图所示：在这里插入图片描述
部门信息已按照需求实现了主键自增

备注:

Windows下开启Oracle服务的命令如下:

net start oracleserviceorcl

Windows下开启Oracle监听器服务的命令如下:

lsnrctl start

补充:使用 PL/SQL 连接远程Oracle的方法

由于Oracle的庞大，有时候我们需要在只安装Oracle客户端如plsql、toad等的情况下去连接远程数据库，可是没有安装Oracle就没有一切的配置文件去支持.最后终于发现一个很有效的方法，Oracle的Instant client工具包可以很好地解决这个问题，而且小而方便.

1. 首先到Oracle网站下载Instant Client :

https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html

对应Oracle的版本下载对应的Oracle的客户端,例如:Oracle 11g 可以下载 [instantclient-basic-win-x86-64-11.1.0.7.0.zip]:https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html#license-lightbox)

解压之后的文件夹叫:instantclient_11_1.可以放在本地磁盘任意目录下.例如:D:\app\instantclient_11_1

2. 修改添加文件:

在上一步解压出来的客户端目录下(例如:D:\app\instantclient_11_1)目录下新建文件tnsnames.ora,或者将Oracle安装目录\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora复制到该目录下 ,打开写入如下内容:

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.100)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

其中:ORCL是远程数据库在本地的主机名，192.168.0.100是远程服务器的IP地址，ORCL是远程数据库的名称.

3. 配置环境变量:

新建一个环境变量，名为TNS_ADMIN，值为tnsnames.ora文件所在路径.

例如: 变量名 : TNS_ADMIN 变量值 : D:\app\instantclient_11_1

4. 下载并安装PL.SQL.Developer配置应用

配置tools->preferences->connection

# Oracle Home
D:\app\instantclient_11_1

# OCI library
D:\app\instantclient_11_1\oci.dll

在这里插入图片描述
配置完成后关闭PL/SQL ,再重启.

主机名就会出现在PL/SQL Developer的列表里，输入用户名密码，就可以登录远程oracle 数据库.

若发现oci.dll文件报错,则是客户端版本与服务器版本冲突,重新下载对应的客户端版本

5. 查询数据乱码问题解决:

当我们连接成功后有时候查询出来的数据会出现乱码的问题，这是因为本地的编码和服务器端编码不一致，这时候我们可以通过SQL语句：
```
select userenv('language') from dual;
```

查询出服务器端的编码，结果如下:

USERENV('LANGUAGE')
AMERICAN_AMERICA.ZHS16GBK

我们就需要添加一个环境变量NLS_LANG,值为: AMERICAN_AMERICA.ZHS16GBK 然后重启PL/SQL就不会再有乱码问题了

6. 无法连接远程Oracle 服务器解决方式

找到服务器的Net Manager工具 , 修改监听器的主机名为本地计算机名 , 如图所示：
设置服务器命名的主机名为本地计算机名 , 如图所示：
重启oracle的数据库服务以及监听服务

补充:创建pdb的步骤

1. 创建pdb 可插拔式数据库

-- 查看插接式数据库
show pdbs;
-- 查看参数 FILE_NAME_CONVERT
select name from v$datafile;
-- 创建 插接式数据库
create pluggable database orclpdb 
	admin user orclpdb identified by  orcl 
	file_name_convert=('D:\APP\ADMINISTRATOR\ORADATA\ORCL\PDBSEED\','D:\APP\ADMINISTRATOR\ORADATA\ORCL\ORCLPDB\');

2. 打开单个pdb数据库

-- 打开单个pdb数据库
alter pluggable database orclpdb open;
-- 关闭pdb数据库
alter pluggable database orclpdb close immediate;

3. 切换到pdb数据库中

-- 切换到pdb数据库中
alter session set container=ORCLPDB;

4. 创建scott用户

-- 创建scott用户
creat user scott identified by tiger;

5. 授予dba权限(可选)

-- 授予dba权限
grant dba to scott;

6. 执行sql脚本文件

-- 执行sql脚本文件
@D:\app\Administrator\product\12.2.0\dbhome_1\rdbms\admin\scott.sql;
-- 查看某用户下有哪些表名
select table_name from all_tables a where a.OWNER = upper('scott');

7. 保存开启状态

ALTER PLUGGABLE DATABASE orclpdb SAVE STATE;--保存开启状态

8. 删除 pdb 数据库以及数据文件

drop pluggable database orclpdb including datafiles;

SSH Chapter 03 PL/SQL编程

SSH Chapter 03 PL/SQL编程 笔记

本章目标 :

15分钟测评：

1. PL/SQL 基础知识

1.1 什么是PL/SQL

1、PL/SQL 体系结构：

2、PL/SQL块简介：

3、运算符和表达式：

4、常量和变量声明

5、注释

6、PL/SQL的编码规则

1.2 PL/SQL 数据类型

1、标量数据类型

2、LOB数据类型

3、属性类型

1.3 PL/SQL 控制语句

1、条件控制

(1) IF语句语法如下:

2、循环控制

(1) LOOP 循环语法如下:

(2) WHILE 循环语法如下:

(3) FOR 循环语法如下:

3、顺序控制

2. 异常处理

2.1 什么是异常

2.2 预定义异常

2.3 非预定义异常

2.4 处理用户定义异常

3. 游标

3.1 游标的基本原理

3.2 游标分类

3.3 显式游标

1. 显式游标的使用步骤

2. 显示游标属性:

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

4. 使用循环游标简化游标的读取

5. NO_DATA_FOUND 和 %NOTFOUND 区别

4. 存储过程

4.1 子程序的组成

1. 声明部分

2. 可执行部分

3. 异常处理部分

4.2 存储过程用法

1. 创建存储过程

2. 调用存储过程

1) 用命令调用

2) 在PL/SQL 块中调用

3. 存储过程的参数模式

4. 存储过程的访问权限

5. 删除存储过程

4.3 存储过程的调试与跟踪

1. 在SQL*Plus下调试

2. 用PL/SQL Developer 工具调试

4.4 存储过程规则

5. 存储函数(理解)

5.1 存储函数语法如下:

5.2 存储函数案例演示:

5.3 存储过程和存储函数的区别:

6. Java调用存储过程和存储函数

6.1 JDBC调用存储过程和存储函数

6.2 Hibernate调用存储过程和存储函数:

7. 触发器

7.1 触发器定义:

7.2 创建和使用触发器

7.3 案例演示

1. 语句触发器案例

2. 行级触发器案例

3. 触发器实现Oracle中的主键自增

备注:

补充:使用 PL/SQL 连接远程Oracle的方法

1. 首先到Oracle网站下载Instant Client :

2. 修改添加文件:

3. 配置环境变量:

4. 下载并安装PL.SQL.Developer配置应用

5. 查询数据乱码问题解决:

6. 无法连接远程Oracle 服务器解决方式

补充:创建pdb的步骤

1. 创建pdb 可插拔式数据库

2. 打开单个pdb数据库

SSH Chapter 03 PL/SQL编程笔记

5. `NO_DATA_FOUND` 和 `%NOTFOUND` 区别

1. 在`SQL*Plus`下调试

8. 删除 pdb 数据库以及数据文件