数据库原理与应用 Oracle版 教学课件 马忠贵 宁淑荣 曾广平 姚琳- 第6章 PL SQL编程基础

数据库原理与应用（Oracle版）2第6章PL/SQL编程基础6.1PL/SQL简介6.2PL/SQL程序结构6.3PL/SQL控制结构6.4异常处理6.5游标6.6存储过程6.7函数6.8触发器6.9程序包6.1PL/SQL简介PL/SQL语言（ProceduralLanguage/SQL，过程化SQL语言）是Oracle推出的过程化的SQL编程语言，使用PL/SQL可以为SQL语言引入结构化的程序处理能力，例如可以在PL/SQL中定义常量、变量、游标、存储过程等，可以使用条件、循环等流程控制语句。PL/SQL的这种特性使得开发人员可以在数据库中添加业务逻辑，并且由于业务逻辑与数据均位于数据库服务器端，比客户端编写的业务逻辑能提供更好的性能。36.1PL/SQL简介PL/SQL是一种块结构的语言，它将一组语句放在一个块中，一次性发送给服务器，PL/SQL引擎分析收到的PL/SQL语句块中的内容，把其中的过程控制语句由PL/SQL引擎自身去执行，把PL/SQL块中的SQL语句交给服务器的SQL语句执行器执行。PL/SQL块发送给服务器后，先被编译然后执行，对于有名称的PL/SQL块（如存储过程、函数、触发器、程序包）可以单独编译，永久的存储在数据库中，随时准备执行。46.1PL/SQL简介6.1PL/SQL简介编写和编译PL/SQL程序块主要分以下6个步骤：1）启动SQL*Plus工具。2）打开PL/SQL程序文件，例如：SQL>EDITc:\plsqlblock1.sql;3）在编辑窗口中输入PL/SQL语句，在END;结束符的下一行开头加“/”作为结束标志。4）保存刚输入的PL/SQL块，关闭编辑窗口。5）激活dbms_output包，编译和运行块。SQL>SETSERVEROUTPUTON;SQL>STARTc:\plsqlblock1.sql;6）如果编译有错，回到第3）步检查语法，然后回到第5）步重新编译。直到成功为止。56.2PL/SQL程序结构基本块结构每个PL/SQL逻辑块包括3部分，语法如下：[DECLARE

声明变量、常量、游标、自定义异常]--声明语句（1）BEGIN

SQL语句

PL/SQL语句

--执行语句（2）[EXCEPTION

异常发生时执行的动作]--异常执行语句（3）END;66.2PL/SQL程序结构变量定义变量定义的方法如下：DECLARE变量名[CONSTANT]数据类型[NOTNULL][:=值|DEFAULT值];在程序中为变量赋值的语法如下：变量名:=值或PL/SQL表达式;76.2PL/SQL程序结构【例】变量的定义和初始化。SQL>SETSERVEROUTPUTONSQL>DECLARE--声明部分标识 v_jobVARCHAR2(9); v_countBINARY_INTEGERDEFAULT0; v_total_salNUMBER(9,2):=0; v_dateDATE:=SYSDATE; c_tax_rateCONSTANTNUMBER(3,2):=8.25; v_validBOOLEANNOTNULL:=TRUE; BEGIN v_job:='MANAGER';--在程序中赋值 DBMS_OUTPUT.PUT_LINE(v_job);--输出变量v_job的值 DBMS_OUTPUT.PUT_LINE(v_count);--输出变量v_count的值 DBMS_OUTPUT.PUT_LINE(v_date);--输出变量v_date的值 DBMS_OUTPUT.PUT_LINE(c_tax_rate);--输出变量c_tax_rate的值 END;86.2PL/SQL程序结构定义字段变量的方法DECLARE变量名表名.字段名%TYPE;【例】根据表的字段定义变量。SQL>SETSERVEROUTPUTONSQL>DECLARE v_enameemp.ename%TYPE;--根据字段定义变量 BEGIN SELECTenameINTOv_enameFROMempWHERE

empno=7788; DBMS_OUTPUT.PUT_LINE(v_ename);--输出变量的值 END;96.2PL/SQL程序结构记录变量的定义方法如下：DECLARE记录变量名表名%ROWTYPE;获得记录变量的字段的方法是：记录变量名.字段名，如。106.2PL/SQL程序结构PL/SQL常见的运算符1）算术运算：加(+)、减(?)、乘(*)、除(/)、指数(**)。2）关系运算：小于(<)、小于等于(<=)、大于(>)、大于等于(>=)、等于(=)、不等于(!=或<>)。3）字符运算：连接(||)。4）逻辑运算：与(AND)、或(OR)、非(NOT)。ISNULL：用来判断运算对象是否为空，为空则返回TRUE。LIKE：用来判断字符串是否与模式匹配。BETWEEN…AND…：判断值是否位于一个区间。IN(…)：测试运算对象是否在一组值的列表中。116.3PL/SQL控制结构PL/SQL程序段中有3种控制结构：条件结构循环结构顺序结构126.3PL/SQL控制结构条件结构分支结构IF条件表达式1THEN

语句序列1;[ELSIF条件表达式2THEN

语句序列2;ELSE

语句序列n;]ENDIF;136.3PL/SQL控制结构条件结构根据具体情况，分支结构可以有以下几种形式：IF…THEN…ENDIFIF…THEN…ELSE…ENDIFIF…THEN…ELSIF…ELSE…ENDIF【例】如果温度大于30℃，则显示“温度偏高”。SQL>SETSERVEROUTPUTONSQL>DECLARE v_tempratureNUMBER(5):=32; v_resultBOOLEAN:=FALSE; BEGIN v_result:=v_temprature>30; IFv_resultTHEN DBMS_OUTPUT.PUT_LINE('温度'||v_temprature||'度，偏高'); ENDIF; END;146.3PL/SQL控制结构条件结构选择结构(CASE语句适用于分情况的多分支处理)1）基本CASE结构基本CASE结构的语法如下：CASE选择变量名

WHEN表达式1THEN语句序列1

WHEN表达式2THEN语句序列2

……

WHEN表达式nTHEN语句序列n

ELSE语句序列n+1ENDCASE;156.3PL/SQL控制结构条件结构选择结构2）表达式结构CASE语句变量:=CASE选择变量名

WHEN表达式1THEN值1

WHEN表达式2THEN值2

……

WHEN表达式nTHEN值n

ELSE值n+1END;166.3PL/SQL控制结构条件结构选择结构搜索CASE结构CASE

WHEN条件表达式1THEN语句序列1

WHEN条件表达式2THEN语句序列2

WHEN条件表达式nTHEN语句序列n

ELSE语句序列n+1ENDCASE;176.3PL/SQL控制结构循环结构基本LOOP循环基本LOOP循环的语法如下：LOOP--循环起始标识

执行语句;EXIT[WHEN条件];ENDLOOP;--循环结束标识该循环的作用是反复执行LOOP与ENDLOOP之间的语句。EXIT用于在循环过程中退出循环，WHEN用于定义EXIT的退出条件。如果没有WHEN条件，遇到EXIT语句则无条件退出循环。186.3PL/SQL控制结构循环结构FOR…LOOP循环FOR…LOOP循环是固定次数循环，语法如下：FOR控制变量IN[REVERSE]下限..上限LOOP

执行语句;ENDLOOP;循环控制变量是隐含定义的，不需要声明。下限和上限用于指明循环次数。正常情况下循环控制变量的取值由下限到上限递增，REVERSE关键字表示循环控制变量的取值由上限到下限递减。196.3PL/SQL控制结构循环结构WHILE…LOOP循环WHILE循环是有条件循环，其格式如下：WHILE条件LOOP

执行语句;ENDLOOP;当条件满足时，执行循环体；当条件不满足时，则循环结束。如果第一次判断条件为假，则不执行循环体。206.3PL/SQL控制结构GOTO语句GOTO语句的格式如下：GOTO标签标记;这是个无条件转向语句。执行GOTO语句时，控制会立即转到由标签标记的语句（使用<<>>声明）。PL/SQL中对GOTO语句有一些限制，对于块、循环、IF语句而言，从外层跳转到内层是非法的。216.4异常处理异常处理的语法异常处理部分一般放在PL/SQL程序体的后半部，语法如下：EXCEPTION

WHEN表达式1THEN<异常处理语句1>

WHEN表达式2THEN<异常处理语句2>

……

WHEN表达式nTHEN<异常处理语句n>

WHENOTHERSTHEN<其它异常处理语句>END;226.4异常处理异常处理的分类1）预定义异常处理Oracle预定义的异常情况大约有24个。对这种异常情况的处理，无需在程序中定义，由Oracle自动将其触发。2）非预定义异常处理即其他标准的Oracle错误。对这种异常情况的处理，需要用户在程序中定义，然后由Oracle自动将其触发。3）用户自定义异常处理程序执行过程中，出现编程人员认为的非正常情况。当与一个异常错误相关的错误出现时，就会隐含触发该异常错误。用户定义的异常错误是通过显式使用RAISE语句来触发。当触发一个异常错误时，控制就转向到EXCEPTION块异常错误部分，执行错误处理代码。236.5游标SQL是面向集合的，其结果一般是集合量（多条记录），而PL/SQL的变量一般是标量。其一组变量一次只能存放一条记录。所以仅仅使用变量并不能完全满足SQL语句向应用程序输出数据的要求。因为查询结果的记录数是不确定的，事先也就不知道要声明几个变量。为此。在PL/SQL中引入了游标（Cursor）的概念，用游标来协调这两种不同的处理方式246.5游标对于不同的SQL语句，游标的使用情况不同：SQL语句

显式游标非查询语句

隐式游标结果是单行的查询语句

隐式或显式游标结果是多行的查询语句

显式游标256.5游标显式游标与隐式游标的比较266.6存储过程存储过程是PL/SQL语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其他强大的编程功能。存储过程在数据库开发过程以及数据库维护和管理等任务中有非常重要的作用。276.6存储过程创建存储过程CREATE[ORREPLACE]PROCEDURE[模式名.]存储过程名 [参数名[IN|OUT|INOUT]数据类型,…]{IS|AS} [变量的声明部分]BEGIN <执行部分>EXCEPTION <可选的异常错误处理程序>END[存储过程名];286.6存储过程调用存储过程存储过程建立完成后，只要通过授权，用户就可以在SQLPLUS、Oracle开发工具或第三方开发工具中来调用运行。Oracle使用EXECUTE语句来实现对存储过程的调用：EXEC[UTE]存储过程名(参数1,参数2…);删除存储过程DROPPROCEDURE存储过程名;296.7函数函数一般用于计算和返回一个值，可以将经常需要进行的计算写成函数。函数的调用是表达式的一部分，而存储过程的调用是一条PL/SQL语句。函数与存储过程在创建的形式上有些相似，也是编译后放在内存中供用户使用，只不过调用时函数要用表达式，而不像存储过程只需调用过程名。另外，函数必须有一个返回值，而存储过程则没有。306.7函数创建函数CREATE[ORREPLACE]FUNCTION[模式名.]函数名 [参数名[IN]数据类型,…] RETURN数据类型{IS|AS} [变量的声明部分]BEGIN <执行部分>

（RETURN表达式）EXCEPTION <可选的异常错误处理程序>END[函数名];316.7函数调用函数调用函数时可以用全局变量接收其返回值。如：SQL>VARIABLEemp_numNUMBER;SQL>EXECUTEemp_num:=fun_empcount(10);删除函数DROPFUNCTION函数名;326.8触发器在Oracle系统里，触发器类似过程和函数，都有声明、执行和异常处理过程的PL/SQL块。触发器在数据库里以独立的对象存储，它与存储过程不同的是，存储过程通过其它程序来启动运行或直接启动运行，而触发器是由触发事件来启动运行。即触发器是当某个事件发生时自动地隐式运行，并且触发器不能接收参数，所以运行触发器就叫触发。336.8触发器创建触发器CREATEORREPLACETRIGGER[模式名.]触发器名 [BEFORE|AFTER][INSERT|DELETE|UPDATE{OF}]ON[模式名.]表名 [FOREACHROW--包含该选项时为行级触发器，不包含时为语句级触发器 [WHEN字句]]--触发条件，仅在行级触发器中使用 [DECLARE声明变量、常量等] BEGIN <触发操作> END;346.8触发器删除触发器DROPTRIGGERtrigger_emp_secure;356.9程序包程序包(PACKAGE，简称包)是一组相关过程、函数、变量、常量和游标等PL/SQL程序设计元素的组合，作为一个完整的单元存储在数据库中，用名称来标识程序包。它具有面向对象程序设计语言的特点，是对这些PL/SQL程序设计元素的封装。程序包类似于C#和JAVA等面向对象语言中的类，其中变量相当于类中的成员变量，而存储过程和函数相当于类方法。把相关的模块归类成为程序包，可使开发人员利用面向对象的方法进行存储过程的开发，从而提高系统性能。366.9程序包创建程序包一个程序包由2个独立的部分组成：包说明和包主体。包说明部分是程序包与应用程序之间的接口，仅声明程序包内数据类型、变量、常量、游标、存储过程、函数和异常错误处理等元素，这些元素为包的公有元素。包主体则是包说明部分的具体实现，它定义了包说明部分所声明的游标、存储过程、函数等的具体实现。在包主体中还可以声明程序包的私有元素。376.9程序包包说明部分的创建语法如下：CREATE[ORREPLACE]PACKAGE<包名> [AUTHID{CURRENT_USER|DEFINER}]{IS|AS} [公有数据类型定义[公有数据类型定义]…] [公有游标声明[公有游标声明]…] [公有变量、常量声明[公有变量、常量声明]…] [公有函数声明[公有函数声明]…] [公有过程声明[公有过程声明]…]END[包名];386.9程序包包主体部分的创建语法如下：CREATE[ORREPLACE]PACKAGEBODY<包名>{IS|AS} [私有数据类型定义[私有数据类型定义]…] [私有变量、常量声明[私有变量、常量声明]…] [私有异常错误声明[私有异常错误声明]…] [私有函数声明和定义[私有函数声明和定义]…] [私有函过程声明和定义[私有函过程声明和定义]…] [公有游标定义[公有游标定义]…] [公有函数定义[公有函数定义]…] [公有过程定义[公有过程定义]…]BEGIN

执行部分(初始化部分)END[包名];396.9程序包程序包的调用语法为：包名.变量名(常量名)包名.游标名包名.函数名(过程名)删除程序包DROPPACKAGEemp_pkg;40小结PL/SQL也是一种程序语言，称为过程化SQL语言（ProceduralLanguage/SQL）。PL/SQL是Oracle数据库对SQ