106---oracle的PL/SQL，异常处理， 游标（CURSOR）， 子程序，存储过程

一、     PL/SQL

 

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

PL/SQL的工作原理

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

该引擎执行所有过程语句

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

 

PL/SQL引擎负责进行语句的传递

Oracle服务器执行SQL指令

 

 

 

二、     PL/SQL块简介

 

声明部分定义变量、游标和自定义异常

包含 SQL 和 PL/SQL语句的可执行部分

指定出现错误时需要执行的操作

 

DECLARE

        qty_on_hand NUMBER(5);

BEGIN

        SELECT quantity INTO qty_on_hand

           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的优点

 

与 SQL 紧密集成，简化数据处理。

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

数据操纵命令

事务控制命令

游标控制

SQL 函数和 SQL 运算符

支持所有 SQL 数据类型

支持 NULL 值

支持 %TYPE 和 %ROWTYPE 属性类型

更佳的性能，PL/SQL 经过编译执行

支持面向对象编程 (OOP)

     安全性，可以通过存储过程限制用户对数据的访问

 

四、     PL/SQL编码规则

/*

|| 在注释开始的首行只放斜线星号，标志注释开始，

|| 然后注释块的每一行以双垂直线开头，突出注释后面

|| 的内容是注释部分，最后，将星号斜线单置于一行

*/

DECLARE

       v_enameVARCHAR2(20);

       v_rateNUMBER(7,2);

       c_rate_incrCONSTANT 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;

 

五、        声明:使用declare关键字

1)       用于定义变量或者常量

DECLAREvariable_name [CONSTANT] type [NOT NULL] [:=value];

 

 

 

BEGIN

       过程及SQL语句;

EXCEPTION

        WHEN 异常名称THEN

                 过程及SQL语句;

        WHEN OTHERS THEN

                过程及SQL语句;

END;

 

 

 

2)       命名规则

变量名首字母必须是英文字母，其后可以是字母、数字或者特殊字符$、#和下划线

变量名长度不超过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

 

 

 

控制结构

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

条件控制

     IF 语句

     CASE 语句

循环控制

     LOOP 循环

     WHILE 循环

     FOR 循环

顺序控制

     GOTO 语句

     NULL 语句

 

--方法二

DECLARE

       grade NUMBER:=70;

       remark varchar2(20);

BEGIN

       CASE

            WHEN grade=100 THEN remark:='isExcellent';

            WHEN grade>=80  THEN remark:='is Good';

            WHEN grade>=60  THEN remark:='is Normal';

            WHEN grade>=50 THEN remark:='isBad';

            ELSE remark:='big Problem';

       END CASE;

END;

 

--方法一

DECLARE

       grade char:='A';

       remark varchar2(20);

BEGIN

       CASE grade

            WHEN 'A' THEN remark:='is Excellent';

            WHEN 'B' THEN remark:='is Good';

            WHEN 'C' THEN remark:='is Normal';

            WHEN 'D' THEN remark:='is Bad';

            ELSE remark:='big Problem';

       END CASE;

END;

 

七、     异常处理

     在运行程序时出现的错误叫做异常

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

     异常有两种类型：

     预定义异常 -  当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发

     用户定义异常 -  用户可以在 PL/SQL 块的声明部分定义异常，自定义的异常通过 RAISE 语句显式引发

 

 

异常处理

预定义异常	说  明
ACCESS_INTO_NULL	视图给一个没有初始化的对象赋值
DUP_VAL_ON_INDEX	重复的值存储在使用唯一索引的数据库列中
INVALID_NUMBER	视图将一个非有效的字符串转换成数字
LOGIN_DENIED	使用无效的用户名和口令登录Oracle
NO_DATA_FOUND	语句无返回数据
TOO_MANY_ROWS	在执行SELECT INTO语句后返回多行时出现

 

 

八、     游标（CURSOR）

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

游标的分类

显示游标

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

隐式游标

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

 

 

游标的属性

 

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

 

游标的声明

CURSOR：用于声明一个游标

parameter：可选参数，用于指定参数类型、模式等

return：可选，指定游标的返回类型

selectsql:需要处理的select语句，不能含INTO子句

 

CURSORcursor_name [ ( parameter [ , parameter]……)]

[RETURN return_type ] IS selectsql

 

 

打开游标

 

使用OPEN语句开启一个游标：open cursor_name

提取游标：FETCH cursor_name INTO variable_list

使用FETCH语句实现对游标内容的读取

variable_list必须与从游标提取的结果集类型相同

 

关闭游标：close cursor_name

使用CLOSE语句关闭一个游标

关闭游标后，所有资源都将被释放

 

使用显式游标更新行

允许使用游标删除或更新活动集中的行

声明游标时必须使用 SELECT … FOR UPDATE语句

              CURSOR <cursor_name> IS

             SELECT statement FOR UPDATE;

然后可以使用以下语法更新行

            UPDATE <table_name>

                    SET <set_clause>

            WHERE CURRENT OF <cursor_name>

也可以使用以下语句删除行

DELETEFROM <table_name>

WHERECURRENT OF <cursor_name>

 

 

 

九、     子程序

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

子程序的各个部分：

声明部分

可执行部分

异常处理部分(可选)

子程序的分类：

过程 － 执行某些操作

函数 － 执行操作并返回值

 

 

 

十、     创建存储过程

CREATE[OR REPLACE] PROCEDURE

   <procedure name> [(<parameterlist>)]

IS|AS

   <local variable declaration>

BEGIN

   <executable statements>

[EXCEPTION

   <exception handlers>]

END;

 

调用存储过程

EXEC[UTE]procedure_name(parameters_list);

参数传递的三种方式？

按位置传递参数

按名称传递参数

混合方式传递参数

 

 

存储过程传递参数有哪三种模式？

IN

用于接受调用程序的值

默认的参数模式

OUT

用于向调用程序返回值

IN OUT

用于接受调用程序的值，并向调用程序返回更新的值

 

 

存储过程的访问权限和删除存储过程

如何将过程的执行权限授予其他用户？

SQL>GRANT EXECUTE ON find_emp TO MARTIN;

SQL>GRANT EXECUTE ON swap TO PUBLIC;

删除存储过程的语法是什么？

DROPPROCEDURE procedure_name;

 

十一、        学员操作——使用存储过程技术完善需求

需求说明

1 ．根据输入的员工编号，解雇相应的员工

2．创建输出参数为薪水集合的存储过程，调用并显示所有员工薪水

                      要求：存储过程输出参数为薪水集合

使用游标作为输出参数

        SYS_REFCURSOR类型是Oracle提供的系统游标类型

                关键代码：

CREATEOR REPLACE PROCEDURE get_sals

           (cur_salary OUT SYS_REFCURSOR)

AS

BEGIN

       OPEN cur_salary FOR

             SELECT empno,sal FROM emp;

END;

 

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

PL/SQL块由声明部分、可执行部分和异常处理部分组成

PL/SQL数据类型包括标量数据类型、LOB 数据类型和属性类型

控制结构包括条件控制、循环控制和顺序控制

运行时出现的错误叫做异常

异常可以分为预定义异常和用户定义的异常

 

 

游标用于处理查询结果集中的数据

显式游标用于处理返回多行的查询

显式游标可以删除和更新活动集中的行

要处理结果集中所有记录时，可使用循环游标

子程序是命名的 PL/SQL 块，可带参数并可在需要时随时调用

有两种类型的PL/SQL子程序，即过程和函数

过程用户执行特定的任务