典型数据库ORACLEch41(PLSQL设计).ppt

第四章PL/SQL,第一部分PL/SQL基础第二部分PL/SQL程序设计,PL/SQL基础,PL/SQL基本概念PL/SQL程序结构PL/SQL流程控制PL/SQL的异常处理游标的使用,PL/SQL基本概念,什么是PL/SQLPL/SQL（ProceduralLanguageforStructuredQueryLanguage）是Oracle公司对标准SQL语言扩展而形成的过程化的查询语言。SQL的特点是非过程化，即不用指明执行的具体方法和途径，而是简单的调用相应语句来直接取得结果即可。虽然这种不关注任何实现细节的语言对于开发者来说有着极大的便利，然而对于有些复杂的业务流程又要求相应的程序来描述，那么SQL就有些无能为力了。PL/SQL的出现正是为了解决这一问题，它在SQL中引入了变量、流程控制结构等结构化程序设计要素，与C,C+,Java等语言一样关注于处理细节，可以用来实现比较复杂的业务逻辑。,PL/SQL结构见P201FIGURE6-1,PL/SQL程序结构,定义部分,简单变量定义的一般格式：CONSTANTNOTNULLDEFAULT|:=;说明定义部分是包括在关键字DECLARE和BEGIN之间的部分，每条语句后用；结束。每行只能定义一个变量。如果加上关键字CONSTANT,则表示所定义的为一个常量，必须为它赋初值。为变量赋值时，使用赋值符号:=或DEFAULT，默认值为空。如果定义的变量不能为空，则必须加上关键字NOTNULL,并赋初值。,定义部分,示例1、声明几个变量及常量DECLAREv_todayDATENOTNULL:=sysdate;v_ageNUMBER(3)NOTNULL:=25；v_fieldVARCHAR2(10);c_aaCONSTANTNUMBER(2):=18;c_bbCONSTANTNUMBER(2)DEFAULT18;,(PL/SQL最常用数据类型见P205),2、声明一个变量，使它的类型与某个变量或数据库基本表中某个列的数据类型一致。（不知道该变量或列的数据类型）可以使用%TYPE（P209），如果变量或表列数据类型发生变化，它会自动随之而变。DECLAREenoEMP.EMPNO%TYPE;DECLAREteacher_namechar（5）;student_nameteacher_name%TYPE;BEGINEND,定义部分,定义记录类型记录类型定义的一般格式：TYPEISRECORD(NOTNULLDEFAULT|:=,)；说明标识符是定义的记录类型名;要定义记录型变量，定义方法与前面标量型变量定义一样。记录类型变量的属性引用方法是.引用。,定义部分,示例1、声明记录类型和记录类型变量DECLARETYPEstudentISRECORD(snochar(6)NOTNULLdefault0,snameCHAR(10),sexchar(2)NOTNULLdefault男,birthdateDATE);-下面定义一个student类型的变量student1student;BEGIN,2、声明一个记录型变量，使它的类型与某个基本表的数据结构一致，可以使用%ROWTYPE的形式定义。DECLAREemp_valueemp%ROWTYPE;BEGIN,引用方式：emp_value.empnoemp_value.ename,定义部分,可执行部分,组成语句：变量赋值语句；流程控制语句；游标语句；数据查询、数据操纵和事务控制等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:=女;,可执行部分,3、%ROWTYPE型变量的赋值emp_value.ename:=BLACK;emp_value.empno:=8888;emp_value.deptno:=10;4、用SELECT语句为%ROWTYPE型变量整个赋值SELECT*INTOemp_valueFROMempWHEREename:=BLACK;,可执行部分：赋值语句,可执行部分：SQL语句,示例1查询EMP中EMPNO=7900的雇员的基本信息。DECLARE-定义一个记录类型变量emp_valueemp%ROWTYPE;BEGINSELECT*INTOemp_valueFROMempWHEREempno=7900;-输出变量的值DBMS_OUTPUT.PUT_LINE(emp_value.ename|emp_value.sal|emp_value.deptno);END;/,给程序加注释见P228有关DBMS_OUTPUT包的说明请见P211。使用DBMS_OUTPUT.PUT_LINE之前，应该设置环境变量SERVEROUTPUT。(SETSERVEROUTPUTON),示例2计算表EMP中所有雇员的平均工资。DECLAREavg_salNUMBER;BEGIN-使用select语句进行赋值SELECTAVG(sal)INTOavg_salFROMemp;DBMS_OUTPUT.PUT_LINE(avg_sal);END;/,可执行部分：SQL语句,PL/SQL的流程控制,条件控制循环控制跳转控制,条件控制,IF_THEN_ELSE语句(P225)语法格式：IFTHENELSEENDIF;功能：如果条件成立，将执行，否则执行。,执行流程,语句组1,语句组2,条件,true,false,条件控制,IF_THEN_ELSIF语句(P226)语法格式：IFTHENELSIFTHENELSIFTHENELSEENDIF;功能：如果成立，将执行；否则判断，如果成立执行；否则，判断，如此循环，直到判断，如果都不成立，则执行执行。说明：ELSIF不要误写为ELSEIF。,执行流程,语句组1,语句组2,true,false,false,语句组n,语句组n+1,false,true,true,条件控制,示例根据表emp中DEPTNO字段的值，为姓名为SMITH的雇员修改工资；若部门号为10，则工资加100；若部门号为20，则工资加300；否则工资加400。DECLAREnameemp.ename%TYPE:=SMITH;Incrementemp.sal%TYPE;deptemp.deptno%TYPE;BEGINSELECTdeptnoINTOdeptFROMempWHEREename=name;IFdept=10THENincrement:=100;ELSIFdept=20THENincrement:=300;ELSEincrement:=400;ENDIF;UPDATEempSETsal=sal+incrementWHEREename=name;COMMIT;END;/,循环控制语句(P229),FOR循环语法格式：FORINREVERSELOOPENDLOOP;说明：系统默认时，计数器从开始值往结束值递增1计数，如果使用REVERSE关键字，则表示计数器从结束值到开始值递减1计数。循环变量不需要显式地在变量定义部分进行定义。系统隐含地将它看成一个整型变量。循环变量只能在循环体中使用，不能在循环体外使用。,循环控制语句,使用联编变量SQLVARIABLEfacNUMBERDECLAREnumNUMBER(1):=5;BEGIN:fac:=1;FORIin1.numLOOP:fac:=:fac*I;ENDLOOP;END;/SQLPRINTfac;,SQLSETSERVEROUTPUTONDECLAREnumNUMBER(1):=5;resuNUMBER(3):=1;BEGINFORiin1.numLOOPresu:=resu*i;ENDLOOP;DBMS_OUTPUT.PUT_LINE(resu);END;/,示例计算一个整数的阶乘，并在屏幕上显示出来。,注意：在块内部引用联编变量(BindVariable)时，必须加上冒号(:)，SQL*PLUS中显示联编变量要用PRINT。,循环控制语句,EXITWHEN循环语法格式：LOOPEXITWHEN；ENDLOOP;执行过程：先执行循环体，然后判断，如果条件为真，则结束循环，否则继续循环。,示例求1100所有整数的和SQLVARIABLEsumNUMBERDECLAREiNUMBER(3):=100;BEGIN:sum:=0;LOOP:sum:=:sum+i;i:=i-1;EXITWHENi=0;ENDLOOP;END;/SQLPRINTsum;,循环控制语句,WHILE循环(P229)语法格式：WHILELOOPENDLOOP;执行过程：先判断，如果条件为真，则执行循环体，继续循环，否则结束循环。说明：WHILE型循环的循环体可能一次也不执行。,示例用WHILE循环求1100所有整数的和DECLAREsummNUMBER:=0;iNUMBER(3):=100;BEGINWHILEi0LOOPsumm:=summ+i;i:=i-1;ENDLOOP;DBMS_OUTPUT.PUT_LINE(summ);END;/,跳转语句,GOTO语句语法格式：标号GOTO标号；说明：块内可以跳转，内层块可以跳到外层块，但外层块不能跳到内层。IF语句不能跳入。不能从循环体外跳入循环体内。不能从子程序外部跳到子程序中。由于goto语句的缺点，建议尽量少用甚至不用goto语句。,跳转语句,示例采用GOTO求1100的和。SQLVARIABLEsummNUMBERDECLAREiNUMBER(3):=100;BEGIN:summ:=0;:summ:=:summ+i;i:=i-1;IFi0THENGOTOlabel;ENDIF;END;/SQLPRINTsumm;,PL/SQL中的异常处理,PL/SQL中的异常处理预定义异常对于Oracle预定义的异常，当预定义的情况发生时，系统将自动触发。用户自定义的异常需要程序员自己定义代码，对异常情况进行处理。异常处理的一般格式：DECLARE;BEGIN;EXCEPTIONWHEN异常情况1OR异常情况2THEN;WHEN异常情况3OR异常情况4THEN；WHENOTHERSTHEN；END;,预定义的异常处理(P217),预定义的异常处理,示例处理预定义异常的一个例子DECLAREemp_valueemp%ROWTYPE;BEGINSELECT*INTOemp_valueFROMempWHEREempno=7999;DBMS_OUTPUT.PUT_LINE(emp_value.ename);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE(Nomatchingrows!);END;/,用户自定义的异常处理(P219),说明用户自定义异常必须在定义部分进行声明。当异常发生时，系统不能自动触发，需要用户使用RAISE语句。示例DECLAREout_of_stockEXCEPTION;number_on_handNUMBER;BEGINIFnumber_on_handv_sc;v_recscore%rowtype;beginv_sc:=80;openc_1;fetchc_1intov_rec;dbms_ouput.put_line(v_rec.sno);end;/,带参数游标的使用：使用形参方式传递参数,游标定义语法格式：CURSOR游标名（，）IS；说明打开带参数的游标时，参数个数和数据类型必须与其定义