第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程序文件，例如：SQLEDITc:\plsqlblock1.sql;3）在编辑窗口中输入PL/SQL语句，在END;结束符的下一行开头加“/”作为结束标志。4）保存刚输入的PL/SQL块，关闭编辑窗口。5）激活dbms_output包，编译和运行块。SQLSETSERVEROUTPUTON;SQLSTARTc:\plsqlblock1.sql;6）如果编译有错，回到第3）步检查语法，然后回到第5）步重新编译。直到成功为止。人民邮电出版社56.2PL/SQL程序结构基本块结构每个PL/SQL逻辑块包括3部分，语法如下：[DECLARE声明变量、常量、游标、自定义异常]--声明语句（1）BEGINSQL语句PL/SQL语句--执行语句（2）[EXCEPTION异常发生时执行的动作]--异常执行语句（3）END;人民邮电出版社66.2PL/SQL程序结构变量定义变量定义的方法如下：DECLARE变量名[CONSTANT]数据类型[NOTNULL][:=值|DEFAULT值];在程序中为变量赋值的语法如下：变量名:=值或PL/SQL表达式;人民邮电出版社76.2PL/SQL程序结构【例6.1】变量的定义和初始化。SQLSETSERVEROUTPUTONSQLDECLARE--声明部分标识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;BEGINv_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;【例6.2】根据表的字段定义变量。SQLSETSERVEROUTPUTONSQLDECLAREv_enameemp.ename%TYPE;--根据字段定义变量BEGINSELECTenameINTOv_enameFROMempWHEREempno=7788;DBMS_OUTPUT.PUT_LINE(v_ename);--输出变量的值END;人民邮电出版社96.2PL/SQL程序结构记录变量的定义方法如下：DECLARE记录变量名表名%ROWTYPE;获得记录变量的字段的方法是：记录变量名.字段名，如emp_record.ename。人民邮电出版社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℃，则显示“温度偏高”。SQLSETSERVEROUTPUTONSQLDECLAREv_tempratureNUMBER(5):=32;v_resultBOOLEAN:=FALSE;BEGINv_result:=v_temprature30;IFv_resultTHENDBMS_OUTPUT.PUT_LINE('温度'||v_temprature||'度，偏高');ENDIF;END;人民邮电出版社146.3PL/SQL控制结构条件结构选择结构(CASE语句适用于分情况的多分支处理)1）基本CASE结构基本CASE结构的语法如下：CASE选择变量名WHEN表达式1THEN语句序列1WHEN表达式2THEN语句序列2……WHEN表达式nTHEN语句序列nELSE语句序列n+1ENDCASE;人民邮电出版社156.3PL/SQL控制结构条件结构选择结构2）表达式结构CASE语句变量:=CASE选择变量名WHEN表达式1THEN值1WHEN表达式2THEN值2……WHEN表达式nTHEN值nELSE值n+1END;人民邮电出版社166.3PL/SQL控制结构条件结构选择结构搜索CASE结构CASEWHEN条件表达式1THEN语句序列1WHEN条件表达式2THEN语句序列2WHEN条件表达式nTHEN语句序列nELSE语句序列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程序体的后半部，语法如下：EXCEPTIONWHEN表达式1THEN异常处理语句1WHEN表达式2THEN异常处理语句2……WHEN表达式nTHEN异常处理语句nWHENOTHERSTHEN其它异常处理语句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游标显式游标与隐式游标的比较人民邮电出版社26隐式游标显式游标PL/SQL维护，当执行查询时自动打开和关闭在程序中显式定义、打开、关闭，游标有一个名字。游标属性前缀是SQL游标属性的前缀是游标名属性%ISOPEN总是为FALSE%ISOPEN根据游标的状态确定值SELECT语句带有INTO子串，只有一行数据被处理可以处理多行数据，在程序中设置循环，取出每一行数据。6.6存储过程存储过程是PL/SQL语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其他强大的编程功能。存储过程在数据库开发过程以及数据库维护和管理等任务中有非常重要的作用。人民邮电出版社276.6存储过程创建存储过程CREATE[ORREPLACE]PROCEDURE[模式名.]存储过程名[参数名[IN|OUT|INOUT]数据类型,…]{IS|AS}[变量的声明部分]BEGIN执行部分EXCEPTION可选的异常错误处理程序END[存储过程名];人民邮电出版社286.6存储过程调用存储过程存储过程建立完成后，只要通过授权，用户就可以在SQLPLUS、Oracle开发工具或第三方开发工具中来调用运行。Oracle使用EXECUTE语句来实现对存储过程的调