1第9章 PL/SQL语言基础2主要内容PL/SQL语言简介PL/SQL程序结构词法单元数据类型、常量与变量控制结构游标异常处理39.1 PL/SQL语言简介与SQL语言紧密集成。减小网络流量，提高应用程序的运行性能。模块化的程序设计功能，提高了系统可靠性。服务器端 程序设计，可移植性好。49.2 PL/SQL程序结构PL/SQL块的组成PL/SQL块分类 5（1）PL/SQL块的组成PL/S QL程序的基本单元是语句块，所有的PL/SQL程序都是由语句块构成的 。一个完整的PL/SQL语句块由3个部分组成。 6声明部分主 要用于声明变量、常量、数据类型、游标、异常处理名称以及本地（局部）子程序定义等。 可执行部分执行部分是PL/SQL块的功能实现部分 。该部分通过变量赋值、流程控制、数据查询、数据操纵、数据定义、事务控制、游标处理等实现块的功能。异常处理部分异常处理部分用于处理该 块执行过程中产生的异常。 7注意：执行部分是必须的，而声明部分和异常部分是可选的可以在一个块的执行部分或异常处理部分嵌套其他的PL /SQL块；所有的PL/SQL块都是以“END;”结束。8DECLARE v_fname VARCHAR2(10);BEGIN SELECT first_name INTO v_fname FROM employees WHERE employee_id =109; DBMS_OUTPUT.PUT_LINE(v_fname);EXCEPTION WHEN NO_DATA_FOUN D THEN DBMS_OUTPUT.PUT_LINE(''There is not such an employee'');END ; 9DECLARE v_sal NUMBER(8,2); v_deptno NUMBER(4);BEGIN BEG IN SELECT department_id INTO v_deptno FROM employees WHERE employee_id=109; END; SELECT avg(salary) INTO v_sal FROM employees WHERE department_id=v_deptno; DBMS_OUTPUT.PUT_L INE(v_sal);END; 10注意若要在SQLPlus环境中看到DBMS_OUTPUT.PUT_LINE方法的输出结果，必 须将环境变量SERVEROUTPUT设置为ON。SET SERVEROUTPUT ON11（2）PL/SQL块分类匿名块匿名块是指 动态生成，只能执行一次的块，不能由其他应用程序调用。命名块命名块是指一次编译可多次执行的PL/SQL程序，包括函数、存储过程、包、 触发器等。它们编译后放在服务器中，由应用程序或系统在特定条件下调用执行。 12命名块示例CREATE OR REPLACE PRO CEDURE showavgsal( p_deptno NUMBER)ASv_sal NUMBER(6,2);BEGIN SEL ECT avg(salary) INTO v_sal FROM employees WHERE department_id =p_deptno; DBMS_OUTPUT.PUT_LINE(v_sal);END showavgsal;139.3 词法单元 字符集标识符分隔符常量值注释14（1）字符集PL/SQL的字符集包括：大小写字母：A~Z，a~z数字：0~9空白：制表符、空格和回 车数字符号：+ - / 〈 〉 =标点符号：~ ! @ # $ % ^& （）_ | { } [ ] ？ ； ：, . “ ‘ 注意PL/SQL字符集不区分大小写。15（2）标识符标识符用于定义PL/SQL变量、常量、异常、游标名称、游标变量、参数 、子程序名称和其他的程序单元名称等。在PL/SQL程序中，标识符是以字母开头的，后边可以跟字母、数字、美元符号（$）、井号（#）或 下划线（_），其最大长度为30个字符，并且所有字符都是有效的。例如，X，v_empno，v_$等都是有效的标识符，而X+y，_te mp则是非法的标识符。注意如果标识符区分大小写、使用预留关键字或包含空格等特殊符号，则需要用“”括起来，称为引证标识符。例如标识符 “my book”和“exception”。16（3）分隔符+-/=:=<><=>=<>!=~=^=()//<<>>%;:. ‘“..@||=>-分隔符是指有特定含义的单个符号或组合符号 17（4）常量值字符型文字以单引号引起来的字符串，在字符串中的字 符区分大小写。如果字符串中本身包含单引号，则用两个连续的单引号进行转义。数字型文字分为整数与实数两类。其中，整数没有小数点，如12 3；而实数有小数点，如123.45。可以用科学计数法表示数字型文字，如123.45可以表示为1.2345E2。布尔型文字预定义的布 尔型变量的取值，包括TRUE，FALSE，NULL三个值。日期型文字表示日期值，其格式随日期类型格式不同而不同。18（5）注释单行 注释--多行注释以 “/”开始，以“/”结束。DECLAREv_department CHAR(10); -- variable to hold the department name BEGIN / query the departm ent name which department number is 10 ouput the department name into v_department/ SELECT department_name INTO v_department F ROM departments WHERE department_id=50;END; 199.4 数据类型、变量与常量数据 类型变量与常量20PL/SQL中常用的基本数据类型9.4.1 数据类型21记录类型记录类型是复合类型，类似于C语言中的结构体，是 一个包含若干个成员分量的复合类型。在使用记录类型时，需要先在声明部分定义记录类型和记录类型的变量，然后在执行部分引用该记录类型变量 或其成员分量。集合类型集合类型是复合类型，包括索引表类型、嵌套表类型和可变数组类型。集合类型与记录类型的区别在于，记录类型中的成员 分量可以是不同类型的，类似于结构体，而集合类型中所有的成员分量必须具有相同的数据类型，类似于数组。 22%TYPE与%ROWTYP E如果要定义一个类型与某个变量的数据类型或数据库表中某个列的数据类型一致（不知道该变量或列的数据类型）的变量，可以利用%TYPE来 实现。如果要定义一个与数据库中某个表结构一致的记录类型的变量，可以使用%ROWTYPE来实现。 注意变量的类型随参照的变量类型、数 据库表列类型、表结构的变化而变化； 如果数据库表列中有NOT NULL约束，则%TYPE与%ROWTYPE返回的数据类型没有此限制 。 23DECLARE v_sal employees.salary%TYPE; v_emp employees%ROWTYP E;BEGIN SELECT salary INTO v_sal FROM employees WHERE employee _id=109; SELECT INTO v_emp FROM employees WHERE employee_id= 150; DBMS_OUTPUT.PUT_LINE(v_sal); DBMS_OUTPUT.PUT_LINE(v_emp.fi rst_name|| v_emp.salary);END; 249.4.2 变量与常量变量与常量的定义变量的作用域25变量声明（ 1）变量与常量的定义变量定义的一般格式variable_name [CONSTANT] datatype [NOT NULL] [ DEFAULT|:=expression];说明变量或常量名称是一个PL/SQL标识符，应符合标识符命名规范；每行只能定义一个变量 ；如果加上关键字CONSTANT，则表示所定义的是一个常量，必须为它赋初值；如果定义变量时使用了NOT NULL关键字，则必须为变 量赋初值；如果变量没有赋初值，则默认为NULL；使用DEFAULT或“:=”运算符为变量初始化。26DECLARE v1 NUM BER(4); v2 NUMBER(4) NOT NULL :=10; v3 CONSTANT NUMBER(4) DEFAU LT 100;BEGIN IF v1 IS NULL THEN DBMS_OUTPUT.PUT_LINE(''V1 IS NULL! ''); END IF; DBMS_OUTPUT.PUT_LINE(v2||'' ''||v3);END; 27（2） 变量的作用域变量的作用域是指变量的有效作用范围，从变量声明开始，直到块结束。如果PL/SQL块相互嵌套，则在内部块中声明的变量是局 部的，只能在内部块中引用，而在外部块中声明的变量是全局的，既可以在外部块中引用，也可以在内部块中引用。如果内部块与外部块中定义了同 名变量，则在内部块中引用外部块的全局变量时需要使用外部块名进行标识。 28<>DECLARE v_fname C HAR(15); v_outer NUMBER(5);BEGIN v_outer :=10; DECLARE v_f name CHAR(20); v_inner DATE; BEGIN v_inner:=sysdate; v _fname:= ''INNER V_ENAME''; OUTER.v_fname:= ''OUTER V_ENAME''; EN D; DBMS_OUTPUT.PUT_LINE(v_fname);END;299.5 PL/SQL中SQL语句由于PL/SQL执 行采用早期绑定，即在编译阶段对变量进行绑定，识别程序中标识符的位置，检查用户权限、数据库对象等信息，因此在PL/SQL中只允许出现 : SELECT DML(UPDATE、DELETE、INSERT)事务控制语句（COMMIT、ROLLBACK、SAVEPOIN T）注意DDL语句不可以直接使用30通常，利用SQL语句对数据库进行操作时，各种相关量都在代码中以常量的形式指定，而在PL/SQL 中可以通过变量动态指定各种相关量的值，从而实现对数据库的动态操作。DECLARE v_empno NUMBER(4);BEGI N v_empno:=&x; UPDATE employees SET salary=salary+100 WHERE e mployee_id=v_empno;END; 31SELECT语句在PL/SQL程序中，使用SELECT…INTO语句查询一个记 录的信息。其语法为：SELECT select_list_item INTO variable_list|record_varia ble FROM tableWHERE condition; 32根据员工名或员工号查询员工信息，程序为：DECLARE v_ emp employees%ROWTYPE; v_lname employees.last_name%type; v_sal employees.salary%type;BEGIN SELECT INTO v_emp FROM employees WHERE last_name=''Bell''; DBMS_OUTPUT.PUT_LINE(v_emp.employee_i d||'' ''|| v_emp.salary); SELECT last_name,salary INTO v_lname,v_ sal FROM employees WHERE employee_id=109; DBMS_OUTPUT.PUT_LINE (v_lname||'' ''||v_sal);END; 33注意：SELECT…INTO语句只能查询一个记录的信息，如果没有查询到任 何数据，会产生NO_DATA_FOUND异常；如果查询到多个记录，则会产生TOO_MANY_ROWS异常。INTO句子后的变量用于 接收查询的结果，变量的个数、顺序应该与查询的目标数据相匹配，也可以是记录类型的变量。34用SELECT…INTO语句查询50号部门 所有员工信息。DECLARE v_emp employees%ROWTYPE; BEGIN SELECT INTO v _emp FROM employees WHERE department_id=50; END; ERROR 位于第 1 行:ORA-01422: 实际返回的行数超出请求的行数ORA-06512: 在line 435DML语句PL/SQL中DML语句对 标准SQL语句中的DML语句进行了扩展，允许使用变量。DECLARE v_empno employees.employee_id %TYPE :=500;BEGIN INSERT INTO employees(employee_id,last_name, email,hire_date,job_id) VALUES(v_empno,''Wang'',''wx@sian.com'', s ysdate,''PU_MAN''); UPDATE employees SET salary=salary+100 WHERE employee_id=v_empno; DELETE FROM employees WHERE employee_id= v_empno;END; 369.6控制结构 选择结构循环结构跳转结构379.6.1选择结构IF语句CASE语句38（1）IF语句 语法IF condition1 THEN statements1;[ELSIF condition2 THEN statement s2;]……[ELSE else_statements];END IF; 注意条件是一个布尔型变量或表达式，取值只能是TRUE，F ALSE，NULL。39输入一个员工号，修改该员工的工资，如果该员工为10号部门，则工资增加100；若为20号部门，则工资增加15 0；若为30号部门，则工资增加200；否则增加300。 40DECLARE v_deptno employees.depart ment_id%type; v_increment NUMBER(4); v_empno employees.employe e_id%type;BEGIN v_empno:=&x; SELECT department_id INTO v_deptno FROM employees WHERE employee_id=v_empno; IF v_deptno=10 THEN v_increment:=100; ELSIF v_deptno=20 THEN v_increment:=150; ELS IF v_deptno=30 THEN v_increment:=200; ELSE v_increment:=300; E ND IF; UPDATE employees SET salary=salary+v_increment WHERE em ployee_id=v_empno;END; 41由于PL/SQL中的逻辑运算结果有TRUE，FALSE和NULL三种，因此在进行 选择条件判断时，要考虑条件为NULL的情况。例如，下面两个程序，如果不考虑条件为NULL的情况，则运行结果是一致的，但是若考虑条件 为NULL的情况，则结果就不同了。 4243为了避免条件为NULL时出现歧义，应该在程序中进行条件是否为NULL的检查。 44（2 ）CASE语句基本语法CASE WHEN condition1 THEN statements1; WHEN condition2 THEN statements2; …… WHEN conditionn THEN st atementsn; [ELSE else_statements;]END CASE;注意 在CASE语句中，当第 一个WHEN条件为真时，执行其后的操作，操作完后结束CASE语句。其他的WHEN条件不再判断，其后的操作也不执行。 45根据输入的 员工号，修改该员工工资。如果该员工工资低于1000，则工资增加200；如果工资在1000～2000之间，则增加150；如果工资在2 000～3000之间，则增加100；否则增加50。 46DECLARE v_sal employees.salary%type; v_increment NUMBER(4); v_empno employees.employee_id%type;BEG IN v_empno:=&x; SELECT salary INTO v_sal FROM employees WHERE employee_id=v_empno; CASE WHEN v_sal<1000 THEN v_increme nt:=200; WHEN v_sal<2000 THEN v_increment:=150; WHEN v_sa l<3000 THEN v_increment:=100; ELSE v_increment:=50; END CAS E; UPDATE employees SET salary=salary+v_increment WHERE employ ee_id=v_empno;END; 47等值比较的CASE语句基本语法CASE test_value WHEN value 1 THEN statements1; WHEN value2 THEN statements2; …… WHE N valuen THEN statementsn; [ELSE else_ statements;]END CASE ; 48DECLARE v_deptno employees.department_id%type; v_increment NUMBER(4); v_empno employees.employee_id%type;BEGIN v_empno:=& x; SELECT department_id INTO v_deptno FROM employees WHERE emp loyee_id=v_empno; CASE v_deptno WHEN 10 THEN v_increment:=1 00; WHEN 20 THEN v_increment:=150; WHEN 30 THEN v_increment :=200; ELSE v_increment:=300; END CASE; UPDATE employees SET salary=salary+v_increment WHERE employee_id=v_empno; END; 499.6. 2循环结构简单循环WHILE循环FOR循环50（1）简单循环语法 LOOP sequence_of_statement ; EXIT [WHEN condition] ; …….END LOOP;注意在循环体中一定要包含EX IT语句，否则程序进入死循环 51利用简单循环求1～100之间偶数的和。DECLARE v_counter BINARY_INT EGER :=1; v_sum NUMBER :=0;BEGIN LOOP IF mod(v_counter,2)=0 THEN v_sum:=v_sum+v_counter; END IF; v_counter := v_co unter + 1; EXIT WHEN v_counter>100; END LOOP; DBMS_OUTPUT.PU T_LINE(v_sum);END;52（2）WHILE循环基本语法WHILE condition LOOP sequen ce_of_statement;END LOOP; 53利用WHILE循环求1～100之间偶数的和。DECLARE v_coun ter BINARY_INTEGER :=1; v_sum NUMBER :=0;BEGIN WHILE v_counter <= 100 LOOP IF mod(v_counter,2)=0 THEN v_sum:=v_sum+v_cou nter; END IF; v_counter := v_counter + 1; END LOOP; D BMS_OUTPUT.PUT_LINE(v_sum);END; 54（3）FOR循环基本语法FOR loop_counter IN [REVERSE] low_bound..h igh_boundLOOP sequence_of_statement;END LOOP;注意：循环变量不需要显式定义 ，系统隐含地将它声明为BINARY_INTEGER变量；系统默认时，循环变量从下界往上界递增计数，如果使用REVERSE关键字，则 表示循环变量从上界向下界递减计数；循环变量只能在循环体中使用，不能在循环体外使用。55利用FOR循环求1～100之间偶数的和。DE CLAREv_sum NUMBER :=0;BEGINFOR v_counter IN 1..100 LOOPIF mod(v_c ounter,2)=0 THENv_sum:=v_sum+v_counter;END IF;END LOOP;DBMS_OUTPU T.PUT_LINE(v_sum);END; 569.6.3跳转结构语法格式：《标号》 … GOTO 标号；说明：块内可以跳转 ，内层块可以跳到外层块，但外层块不能跳到内层。IF语句不能跳入。不能从循环体外跳入循环体内。不能从子程序外部跳到子程序中。由于go to语句的缺点，建议尽量少用甚至不用goto语句。 57DECLARE v_counter BINARY_INTEGER := 1;BEGIN <
