第四章 PL/SQL第一部分 PL/SQL基础第二部分 PL/SQL程序设计PL/SQL基础 PL/SQLPL/SQL基本概念基本概念 PL/SQLPL/SQL程序结构程序结构 PL/SQLPL/SQL流程控制流程控制 PL/SQLPL/SQL的异常处理的异常处理 游标的使用游标的使用 PL/SQL基本概念v什么是PL/SQLPL/SQL（Procedural Language for Structured Query Language）是Oracle公司对标准SQL语言扩 展而形成的过程化的查询语言。SQL的特点是非过 程化，即不用指明执行的具体方法和途径，而是简 单的调用相应语句来直接取得结果即可。虽然这种 不关注任何实现细节的语言对于开发者来说有着极 大的便利，然而对于有些复杂的业务流程又要求相 应的程序来描述，那么SQL就有些无能为力了。 PL/SQL的出现正是为了解决这一问题，它在SQL中 引入了变量、流程控制结构等结构化程序设计要素 ，与C,C+,Java等语言一样关注于处理细节，可以 用来实现比较复杂的业务逻辑。PL/SQLPL/SQL结构见结构见P201 FIGURE 6-1P201 FIGURE 6-1 PL/SQL程序结构 定义部分v简单变量定义的一般格式： CONSTANT NOT NULL DEFAULT|:= ; v说明 定义部分是包括在关键字DECLARE和BEGIN之间的 部分，每条语句后用；结束。每行只能定义一个变量。 如果加上关键字CONSTANT,则表示所定义的为一个 常量，必须为它赋初值。 为变量赋值时，使用赋值符号:=或DEFAULT，默 认值为空。如果定义的变量不能为空，则必须加上 关键字NOT NULL,并赋初值。 定义部分v示例 1、声明几个变量及常量DECLAREv_today DATE NOT NULL :=sysdate;v_age NUMBER(3) NOT NULL :=25；v_field VARCHAR2(10) ;c_aa CONSTANT NUMBER(2) :=18;c_bb CONSTANT NUMBER(2) DEFAULT 18;( (PL/SQLPL/SQL最常用数据类型见最常用数据类型见P205)P205) 2 2、声明一个变量，使它的类型与某个变量或数据库声明一个变量，使它的类型与某个变量或数据库 基本表中某个列的数据类型一致。（不知道该变量或基本表中某个列的数据类型一致。（不知道该变量或 列的数据类型）可以使用列的数据类型）可以使用 %TYPETYPE（P209P209） ，如果变如果变 量或表列数据类型发生变化，它会自动随之而变。量或表列数据类型发生变化，它会自动随之而变。?DECLAREDECLARE?enoeno EMP.EMPNO%TYPE; EMP.EMPNO%TYPE;?DECLAREDECLARE?teacher_name charteacher_name char（5 5）; ;?student_name teacher_name%TYPE;student_name teacher_name%TYPE;?BEGINBEGIN?ENDEND 定义部分v定义记录类型记录类型定义的一般格式： TYPE IS RECORD ( NOT NULLDEFAULT|:= , )； v说明 标识符 是定义的记录类型名;要定义记录型变量，定义方法与前面标量型变 量定义一样。 记录类型变量的属性引用方法是.引用。 定义部分v示例 1、声明记录类型和记录类型变量 DECLARE TYPE student IS RECORD (sno char(6) NOT NULL default 0, sname CHAR(10) , sex char(2) NOT NULL default 男, birthdate DATE);-下面定义一个student类型的变量student1 student; BEGIN 2 2、声明一个记录型变量，使它的类型与某个基本声明一个记录型变量，使它的类型与某个基本 表的数据结构一致，可以使用表的数据结构一致，可以使用%ROWTYPEROWTYPE的形式的形式 定义。定义。DECLAREDECLAREempemp_value _value empemp%ROWTYPE;%ROWTYPE;BEGINBEGIN引用方式：引用方式：empemp_value _value . .empnoempnoempemp_value_value. .enameename定义部分定义部分 可执行部分 v组成语句： 变量赋值语句； 流程控制语句； 游标语句； 数据查询、数据操纵和事务控制等SQL语句； 说明：在可执行部分，可以使用说明：在可执行部分，可以使用SQLSQL语句，但并不是所语句，但并不是所 有的有的SQLSQL语句都可以直接使用。可以直接使用的主要有语句都可以直接使用。可以直接使用的主要有 S SELECT,INSERT,UPDATE,DELETE,COMMIT,ROLLBACKELECT,INSERT,UPDATE,DELETE,COMMIT,ROLLBACK等数等数 据查询、数据操纵或事务控制命令，不能直接使用据查询、数据操纵或事务控制命令，不能直接使用 CREATE,ALTER,DROP,GRANT,REVOKECREATE,ALTER,DROP,GRANT,REVOKE等数据定义和数据等数据定义和数据 控制命令。控制命令。 vv赋值语句赋值语句 赋值符号为赋值符号为 ：=。 格式：格式： ：= ； 示例示例 赋值的几种情况。赋值的几种情况。?1 1、标量型变量赋值、标量型变量赋值tnametname := LIU; := LIU;?2 2、记录类型变量的赋值记录类型变量的赋值student1.student1.snosno:=980101; :=980101; student1.student1.snamesname:= LILY;:= LILY;student1.sex:=student1.sex:=女女; ;可执行部分可执行部分 ?3 3、%ROWTYPEROWTYPE型变量的赋值型变量的赋值empemp_value._value.enameename:=BLACK;:=BLACK;empemp_value._value.empnoempno:=8888;:=8888;empemp_value._value.deptnodeptno:=10;:=10;?4 4、用用SELECTSELECT语句为语句为%ROWTYPEROWTYPE型变量整个赋值型变量整个赋值SELECT * SELECT * INTO INTO empemp_value_valueFROM FROM empemp WHERE WHERE enameename:= BLACK;:= BLACK;可执行部分：可执行部分： 赋值语句赋值语句 可执行部分： SQL语句v示例1查询EMP中EMPNO = 7900的雇员的基本信息。 DECLARE -定义一个记录类型变量 emp_value emp%ROWTYPE; BEGIN SELECT * INTO emp_value FROM emp WHERE empno= 7900; -输出变量的值 DBMS_OUTPUT.PUT_LINE(emp_value.ename|emp_value.sal |emp_value.deptno); END; / 给程序加注释见给程序加注释见P228P228有关有关DBMS_OUTPUTDBMS_OUTPUT包的说明请见包的说明请见P211P211。 使用使用DBMS_OUTPUT.PUT_LINEDBMS_OUTPUT.PUT_LINE之前，应该设置环之前，应该设置环 境变量境变量SERVEROUTPUTSERVEROUTPUT。(SET SERVEROUTPUT ON)(SET SERVEROUTPUT ON) vv 示例示例22计算表计算表EMPEMP中所有雇员的平均工资。中所有雇员的平均工资。vvDECLAREDECLAREavgavg_ _salsal NUMBER; NUMBER;BEGINBEGIN-使用使用selectselect语句进行赋值语句进行赋值SELECT AVG(SELECT AVG(salsal) )INTO INTO avgavg_ _salsalFROM FROM empemp; ;DBMS_OUTPUT.PUT_LINE(DBMS_OUTPUT.PUT_LINE(avgavg_ _salsal););END;END;/ /可执行部分：可执行部分： SQLSQL语句语句 PL/SQL的流程控制 v条件控制 v循环控制 v跳转控制 条件控制vIF_THEN_ELSE语句(P225)语法格式： IF THENELS EEND IF; 功能： ?如果条件成立，将执行 ，否则执行。v执行流程语句组语句组1 1语句组语句组2 2条件条件truetruefalsefalse 条件控制vIF_THEN_ELSIF语句 (P226) 语法格式： IF THENELSIF THENELSIF THEN ELSE END IF; 功能： ?如果成立，将执行 ；否则判断，如果成立执行；否则，判断 ，如此循环，直到判断，如果都不成立，则 执行执行 。 说明： ?ELSIF不要误写为ELSEIF。v执行流程语句组语句组1 1语句组语句组2 2条件条件1 1truetruefalsefalse条件条件2 2条件条件n nfalsefalse语句组语句组n n语句组语句组n+1n+1falsefalsetruetruetruetrue 条件控制v示例根据表emp中DEPTNO字段的值，为姓名为SMITH的雇员修改 工资；若部门号为10，则工资加100；若部门号为20，则工资加300 ；否则工资加400。 DECLARE name emp.ename%TYPE := SMITH; Increment emp.sal%TYPE; dept emp.deptno% TYPE; BEGIN SELECT deptno INTO dept FROM emp WHERE ename =name; IF dept = 10 THEN increment :=100; ELSIF dept = 20 THEN increment :=300; ELSE increment :=400; END IF; UPDATE emp SET sal = sal +increment WHERE ename = name; COMMIT; END; / 循环控制语句(P229)vFOR循环语法格式： FOR IN REVERSE LOOPEND LOOP; v说明： 系统默认时，计数器从开