《典型数据库ORACLEch41(PLSQL设计).ppt》由会员分享，可在线阅读，更多相关《典型数据库ORACLEch41(PLSQL设计).ppt（49页珍藏版）》请在三一办公上搜索。
1、第四章 PL/SQL,第一部分PL/SQL基础 第二部分 PL/SQL程序设计,PL/SQL基础,PL/SQL基本概念 PL/SQL程序结构 PL/SQL流程控制 PL/SQL的异常处理 游标的使用,PL/SQL基本概念,什么是PL/SQL PL/SQL（Procedural Language for Structured Query Language）是Oracle公司对标准SQL语言扩展而形成的过程化的查询语言。SQL的特点是非过程化，即不用指明执行的具体方法和途径，而是简单的调用相应语句来直接取得结果即可。虽然这种不关注任何实现细节的语言对于开发者来说有着极大的便利，然而对于有些复杂的业
2、务流程又要求相应的程序来描述，那么SQL就有些无能为力了。PL/SQL的出现正是为了解决这一问题，它在SQL中引入了变量、流程控制结构等结构化程序设计要素，与C,C+,Java等语言一样关注于处理细节，可以用来实现比较复杂的业务逻辑。,PL/SQL结构见P201 FIGURE 6-1,PL/SQL程序结构,定义部分,简单变量定义的一般格式：CONSTANT NOT NULL DEFAULT|:=;说明定义部分是包括在关键字DECLARE和BEGIN之间的部分，每条语句后用；结束。每行只能定义一个变量。如果加上关键字CONSTANT,则表示所定义的为一个常量，必须为它赋初值。为变量赋值时，使用赋
3、值符号:=或DEFAULT，默认值为空。如果定义的变量不能为空，则必须加上关键字NOT NULL,并赋初值。,定义部分,示例1、声明几个变量及常量DECLARE v_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/SQL最常用数据类型见P205),2、声明一个变量，使它的类型与某个变量或数据库基本表中某个列的数据类型一致。（不知道该变量或列的数据类型）可以
4、使用%TYPE（P209），如果变量或表列数据类型发生变化，它会自动随之而变。DECLAREeno EMP.EMPNO%TYPE;DECLAREteacher_name char（5）;student_name teacher_name%TYPE;BEGINEND,定义部分,定义记录类型 记录类型定义的一般格式：TYPE IS RECORD(NOT NULLDEFAULT|:=,)；说明标识符 是定义的记录类型名;要定义记录型变量，定义方法与前面标量型变量定义一样。记录类型变量的属性引用方法是.引用。,定义部分,示例1、声明记录类型和记录类型变量DECLARETYPE student IS R
5、ECORD(sno char(6)NOT NULL default 0,sname CHAR(10),sex char(2)NOT NULL default 男,birthdate DATE);-下面定义一个student类型的变量student1 student;BEGIN,2、声明一个记录型变量，使它的类型与某个基本表的数据结构一致，可以使用%ROWTYPE的形式定义。DECLAREemp_value emp%ROWTYPE;BEGIN,引用方式：emp_value.empnoemp_value.ename,定义部分,可执行部分,组成语句：变量赋值语句；流程控制语句；游标语句；数据查询、数
6、据操纵和事务控制等SQL语句；,说明：在可执行部分，可以使用SQL语句，但并不是所有的SQL语句都可以直接使用。可以直接使用的主要有SELECT,INSERT,UPDATE,DELETE,COMMIT,ROLLBACK等数据查询、数据操纵或事务控制命令，不能直接使用CREATE,ALTER,DROP,GRANT,REVOKE等数据定义和数据控制命令。,赋值语句赋值符号为：=。格式：=；示例赋值的几种情况。1、标量型变量赋值tname:=LIU;2、记录类型变量的赋值student1.sno:=980101;student1.sname:=LILY;student1.sex:=女;,可执行部分,
7、3、%ROWTYPE型变量的赋值emp_value.ename:=BLACK;emp_value.empno:=8888;emp_value.deptno:=10;4、用SELECT语句为%ROWTYPE型变量整个赋值SELECT*INTO emp_value FROM emp WHERE ename:=BLACK;,可执行部分：赋值语句,可执行部分：SQL语句,示例1查询EMP中EMPNO=7900的雇员的基本信息。DECLARE-定义一个记录类型变量emp_value emp%ROWTYPE;BEGINSELECT*INTO emp_valueFROM empWHERE empno=790
8、0;-输出变量的值DBMS_OUTPUT.PUT_LINE(emp_value.ename|emp_value.sal|emp_value.deptno);END;/,给程序加注释见P228有关DBMS_OUTPUT包的说明请见P211。使用DBMS_OUTPUT.PUT_LINE之前，应该设置环境变量SERVEROUTPUT。(SET SERVEROUTPUT ON),示例2计算表EMP中所有雇员的平均工资。DECLAREavg_sal NUMBER;BEGIN-使用select语句进行赋值SELECT AVG(sal)INTO avg_salFROM emp;DBMS_OUTPUT.PUT
9、_LINE(avg_sal);END;/,可执行部分：SQL语句,PL/SQL的流程控制,条件控制循环控制跳转控制,条件控制,IF_THEN_ELSE语句(P225)语法格式：IF THENELSEEND IF;功能：如果条件成立，将执行，否则执行。,执行流程,语句组1,语句组2,条件,true,false,条件控制,IF_THEN_ELSIF语句(P226)语法格式：IF THENELSIF THENELSIF THENELSEEND IF;功能：如果成立，将执行；否则判断，如果成立执行；否则，判断，如此循环，直到判断，如果都不成立，则执行执行。说明：ELSIF不要误写为ELSEIF。,执行
10、流程,语句组1,语句组2,true,false,false,语句组n,语句组n+1,false,true,true,条件控制,示例根据表emp中DEPTNO字段的值，为姓名为SMITH的雇员修改工资；若部门号为10，则工资加100；若部门号为20，则工资加300；否则工资加400。DECLAREname emp.ename%TYPE:=SMITH;Increment emp.sal%TYPE;dept emp.deptno%TYPE;BEGINSELECT deptno INTO dept FROM emp WHERE ename=name;IF dept=10 THENincrement:=
11、100;ELSIF dept=20 THENincrement:=300;ELSE increment:=400;END IF;UPDATE emp SET sal=sal+increment WHERE ename=name;COMMIT;END;/,循环控制语句(P229),FOR循环语法格式：FOR IN REVERSE LOOPEND LOOP;说明：系统默认时，计数器从开始值往结束值递增1计数，如果使用REVERSE关键字，则表示计数器从结束值到开始值递减1计数。循环变量不需要显式地在变量定义部分进行定义。系统隐含地将它看成一个整型变量。循环变量只能在循环体中使用，不能在循环体外使用
12、。,循环控制语句,使用联编变量SQLVARIABLE fac NUMBERDECLAREnum NUMBER(1):=5;BEGIN:fac:=1;FOR I in 1.num LOOP:fac:=:fac*I;END LOOP;END;/SQL PRINT fac;,SQLSET SERVEROUTPUT ONDECLAREnum NUMBER(1):=5;resu NUMBER(3):=1;BEGINFOR i in 1.num LOOPresu:=resu*i;END LOOP;DBMS_OUTPUT.PUT_LINE(resu);END;/,示例计算一个整数的阶乘，并在屏幕上显示出来。
13、,注意：在块内部引用联编变量(Bind Variable)时，必须加上冒号(:)，SQL*PLUS中显示联编变量要用PRINT。,循环控制语句,EXIT WHEN循环语法格式：LOOPEXIT WHEN；END LOOP;执行过程：先执行循环体，然后判断，如果条件为真，则结束循环，否则继续循环。,示例求1100所有整数的和 SQLVARIABLE sum NUMBERDECLARE i NUMBER(3):=100;BEGIN:sum:=0;LOOP:sum:=:sum+i;i:=i-1;EXIT WHEN i=0;END LOOP;END;/SQLPRINT sum;,循环控制语句,WHIL
14、E循环(P229)语法格式：WHILE LOOPEND LOOP;执行过程：先判断，如果条件为真，则执行循环体，继续循环，否则结束循环。说明：WHILE型循环的循环体可能一次也不执行。,示例用WHILE循环求1100所有整数的和 DECLAREsumm NUMBER:=0;i NUMBER(3):=100;BEGINWHILE i0 LOOPsumm:=summ+i;i:=i-1;END LOOP;DBMS_OUTPUT.PUT_LINE(summ);END;/,跳转语句,GOTO语句语法格式：标号GOTO 标号；说明：块内可以跳转，内层块可以跳到外层块，但外层块不能跳到内层。IF语句不能跳入
15、。不能从循环体外跳入循环体内。不能从子程序外部跳到子程序中。由于goto语句的缺点，建议尽量少用甚至不用goto语句。,跳转语句,示例 采用GOTO求1100的和。SQLVARIABLE summ NUMBERDECLAREi NUMBER(3):=100;BEGIN:summ:=0;:summ:=:summ+i;i:=i-1;IF i0 THEN GOTO label;END IF;END;/SQLPRINT summ;,PL/SQL中的异常处理,PL/SQL中的异常处理预定义异常对于Oracle预定义的异常，当预定义的情况发生时，系统将自动触发。用户自定义的异常需要程序员自己定义代码，对异
16、常情况进行处理。异常处理的一般格式：DECLARE;BEGIN;EXCEPTION WHEN 异常情况1 OR 异常情况2 THEN;WHEN异常情况3 OR 异常情况4 THEN；WHEN OTHERS THEN；END;,预定义的异常处理(P217),预定义的异常处理,示例 处理预定义异常的一个例子DECLAREemp_value emp%ROWTYPE;BEGINSELECT*INTO emp_valueFROM empWHERE empno=7999;DBMS_OUTPUT.PUT_LINE(emp_value.ename);EXCEPTIONWHEN NO_DATA_FOUND TH
17、EN DBMS_OUTPUT.PUT_LINE(No matching rows!);END;/,用户自定义的异常处理(P219),说明用户自定义异常必须在定义部分进行声明。当异常发生时，系统不能自动触发，需要用户使用RAISE语句。示例DECLAREout_of_stock EXCEPTION;number_on_hand NUMBER;BEGINIF number_on_hand 1 THENRAISE out_of_stock;END IF;EXCEPTIONWHEN out_of_stock THEN-handle the errorEND;,游标的使用(P213),游标的概念 游标的
18、属性 FOR循环中游标的使用 带参数游标的使用,游标的概念,游标的定义游标（cursor）是系统在内存中开辟的一个工作区，在其中存放SELECT语句返回的查询结果。Oracle中游标分为显式游标（explicit cursor)和隐式（implicit cursor)游标。,定义游标CURSOR IS;打开游标OPEN；打开游标，实际上是执行游标定义时对应的SELECT语句，将查询结果检索到工作区中，指针指向结果集中第一条记录。提取数据FETCH INTO 变量1,变量2,；当前行取入变量，推进游标指针。关闭游标CLOSE；使游标所对应的内存工作区变为无效，并释放与游标相关的系统资源。,游标的
19、属性,游标的属性%ISOPEN该属性是布尔型。如果游标已经打开，返回TRUE,否则为FALSE。%FOUND布尔型，如果最近一次使用FETCH语句，有返回结果则为TRUE,否则为FALSE;%NOTFOUND布尔型，如果最近一次使用FETCH语句,没有返回结果则为TRUE,否则为FALSE;%ROWCOUNT数值型，描述的是到目前为止实际从游标工作区抽取的记录数。,说明游标属性只能在PL/SQL块中使用，不能在SQL命令中使用。属性的引用格式游标名属性名说明属性名与游标名之间没有空格。,游标的属性,游标的属性,示例修改表emp中各个雇员的工资，若雇员属于10号部门，则增加$100，若雇员属于2
20、0号部门，则增加$200；若雇员属于30号部门，则增加$300。,DECLARE row emp%rowtype;increment number(4);CURSOR cursor_emp IS select*from emp;BEGIN OPEN cursor_emp;LOOP FETCH cursor_emp INTO row;EXIT WHEN cursor_emp%NOTFOUND;IF row.deptno=10 THEN increment:=100;ELSIF row.deptno=20 THEN increment:=200;ELSE increment:=300;END IF
21、;,UPDATE emp SET sal=sal+increment WHERE ename=row.ename;END LOOP;CLOSE cursor_emp;END;/,FOR循环中游标的使用(P215),语法格式FOR IN LOOP END LOOP;说明系统自动打开游标，不用显式地使用OPEN语句打开；系统隐含地定义了一个记录型变量，并以此作为循环的计数器。系统重复地自动从游标工作区中提取数据并放入计数器变量中。当游标工作区中所有的记录都被提取完毕或循环中断时，系统自动地关闭游标。,FOR循环中游标的使用,示例修改表emp中各个雇员的工资，若雇员属于10号部门，则增加$100，若
22、雇员数据20号部门，则增加$200；若雇员数据30号部门，则增加$300。,DECLARE increment number(4);CURSOR cursor_emp is select*from emp;BEGIN FOR i IN cursor_emp LOOP-隐含打开，隐含提取 IF i.deptno=10 THEN increment:=100;ELSIF i.deptno=20 THEN increment:=200;ELSE increment:=300;END IF;,UPDATE empSET sal=sal+increment WHERE empno=i.empno;END
23、 LOOP;-隐含关闭END;/,游标例子：直接使用变量的方式传递参数,declarev_sc score.degree%type;cursor c_1 is select*from score where degree v_sc;v_rec score%rowtype;beginv_sc:=80;open c_1;fetch c_1 into v_rec;dbms_ouput.put_line(v_rec.sno);end;/,带参数游标的使用：使用形参方式传递参数,游标定义语法格式：CURSOR 游标名（，）IS；说明打开带参数的游标时，参数个数和数据类型必须与其定义时保持一致。,带参数的游标示例：,DECLARE CURSOR cursor_emp(v_deptno emp.deptno%type)IS SELECT*FROM emp WHERE deptno=v_deptno;mm emp%ROWTYPE;BEGIN OPEN cursor_emp(20);,LOOP FETCH cursor_emp INTO mm;EXIT WHEN cursor_emp%NOTFOUND;UPDATE emp SET sal=sal+200 WHERE empno=mm.empno;END LOOP;END;/,多个参数，多次打开,
