PL-SQL编程

PL/SQL编程第一节PL/SQL块的组成第二节事务控制命令第三节PL/SQL流程控制第四节游标的使用第五节PL/SQL的异常处理第六节PL/SQL示例第七节PL/SQL编程对象第一节程序块结构语言PL/SQL的组成•PL/SQL块的组成–PL/SQL语言以块为单位，块中可以嵌套子块。–一个基本的PL/SQL块由3部分组成：定义部分（DECLARE），可执行部分（BEGIN），异常处理部分（EXCEPTION）。定义部分执行部分异常处理部分PL/SQL块主要内容PL/SQL块的定义部分与C语言类似，PL/SQL中使用的变量、常量、游标和异常处理的名字都必须先定义后使用。并且必须定义在以DECLARE关键字开头的定义部分。PL/SQL块的可执行部分该部分是PL/SQL块的主体，包含该块的可执行语句。该部分定义了块的功能，是必须的。由关键字BEGIN开始，EXCEPTION或END结束。PL/SQL块的异常处理部分该部分包含该块的异常处理程序（错误处理程序）。当该块程序体中的某个语句出现异常（检测到一个错误）时，他将程序控制转到异常部分的相应的异常处理程序中进行进一步的处理。该部分由关键字EXCEPTION开始，END关键字结束。定义部分•简单变量定义的一般格式：–variablename[CONSTANT]datatype[[NOTNULL]{DEFAULT|:=}expression];•[说明]–在PL/SQL中使用的变量、常量、游标和异常处理的名字都必须先定义后使用。–定义部分是包括在关键字DECLARE和BEGIN之间的部分，每条语句后用‘；’结束。–每行只能定义一个标识符。–如果加上关键字CONSTANT,则表示所定义的标识符为一个常量，必须为它赋初值。–标识符的命名规则与SQL的规则基本相同，即每个标识符必须以字母开头，而且不分大小写。如果定义的标识符不能为空，则必须加上关键字NOTNULL,并赋初值。–为标识符赋值时，使用赋值符号‘：=’，默认值为空。•Oracle中常用的基本数据类型数据类型描述Number数字型，范围10-130~10125Char字符型，最大2000个字符Date日期型，包括日期、小时、分、秒Long大文本Varchar2变长字符型，最大为4000个字符定义部分•[示例]–1、声明几个变量•todayDATEnotnull：=sysdate;•agenumber(3)notnull：=25；2、声明一个变量，使它的类型与某个变量或数据库基本表中某个列的数据类型一致。（不知道该变量或列的数据类型）可以使用%TYPE。DECLAREteacher_namechar（5）;student_nameteacher_name%TYPE;BEGIN……ENDDECLAREnoEMP.EMPNO%TYPE;BEGIN……定义部分•记录类型定义的一般格式：–TYPErecordtypenameISRECORD–(fielddatatype[[NOTNULL]{DEFAULT|:=}expression]–[,field…]–)；•[说明]–标识符recordtypename是定义的记录类型名;–要定义记录型变量，定义方法与前面标量型变量定义一样。–记录类型变量的属性引用方法是‘.’引用。定义部分•[示例]–1、声明记录类型和记录类型变量DECLARETYPEstudentISRECORD(idNUMBER(4)NOTNULLdefault0,--非空时必须加上缺省值。nameCHAR(10),birthdateDATE,physicsNUMBER(3),chemistryNUMBER(3));--下面定义一个student类型的变量student1student;BEGIN……2、声明一个记录型变量，使它的类型与某个基本表的数据结构一致，可以使用%ROWTYPE的形式定义。DECLAREEMP_VALUEEMP%ROWTYPE;BEGIN……引用方式：EMP_VALUE.empnoEMP_VALUE.ename定义部分可执行部分•组成语句：–变量赋值语句；–流程控制语句；–数据查询、数据操纵和事务控制语句；–游标语句；赋值语句赋值赋号为‘：=’。格式：变量：=表达式；[示例]赋值的几种情况。1、标量型变量赋值TEACHER_NAME:=‘LIU’;2、记录类型变量的赋值STUDENT1.NAME:=‘FAN’;STUDENT1.SEX:=TRUE;STUDENT1.PHYSICS:=90;可执行部分3、%ROWTYPE型变量的赋值EMP_VALUE.NAME:=’BLACK’;EMP_VALUE.EMPNO:=8888;EMP_VALUE.DEPTNO:=10;4、用SELECT语句为%ROWTYPE型变量整个赋值SELECT*INTOEMP_VALUEFROMEMPWHEREENAME:=’FA’;可执行部分：赋值语句可执行部分•SQL语句的使用–在可执行部分，可以使用SQL语句，但是不是所有的SQL语句都可以使用。可以使用的主要有：SELECT,INSERT,UPDATE,DELETE,COMMIT,ROLLBACK等数据查询、数据操纵或事务控制命令，不能使用CREATE,ALTER,DROP,GRANT,REVOKE等数据定义和数据控制命令。•[说明]–在PL/SQL中，SELECT语句必须与INTO子句相配合，在INTO子句后面跟需要赋值的变量。–在使用SELECT…INTO…时，结果只能有一条，如果返回了多条数据或没有数据，则将产生错误。（对于多条记录的遍历，可以使用游标）–在PL/SQL中，SQL语句的语法和交互命令时是一样的。可执行部分：SQL语句•[示例1]查询EMP中EMPNO=9000的雇员的信息。DECLARE--定义一个记录类型变量EMP_VALUEEMP%ROWTYPE;BEGINselect*intoemp_valuefromempwhereempno=7876;--输出变量的值dbms_output.put_line(emp_value.ename);END;/--使用DBMS_OUTPUT.PUT_LINE之前，应该设置环境变量SERVEROUTPUT。(SETSERVEROUTPUTON)[示例2]计算表EMP中所有雇员的平均工资。DECLAREavg_salnumber(8,3);BEGIN--使用select语句进行赋值selectavg(sal)intoavg_salfromemp;dbms_output.put_line(avg_sal);END;/可执行部分：SQL语句第二节事务控制命令•事务（transaction）的概念–事务是指在相邻两条事务控制命令之间的一组sql语句，它是对数据库操作的逻辑单元。–事务是一个完整的活动序列，包含一组操作，这些操作或者全部成功地执行，此时整个活动序列的所有执行结果都被保存到数据库中。或者都不执行并恢复到执行前的状态，我们说事务已经回滚。•买东西是一个典型的事务过程：交钱、拿走货物。•验货入库也是一个典型的事务过程：来货验收、入库。•财务帐目转移：A帐户转到B帐户。事务控制命令COMMIT(事务提交)ROLLBACK（事务回退）SAVEPOINT（设置保存点命令）第二节事务控制命令事务提交（COMMIT）•一般格式：COMMIT;•说明–提交命令用于提交自上次提交以后对数据库中数据所作的改动。–在Oracle数据库中，为了维护数据的一致性，系统为每个用户分别设置了一个工作区。对表中数据所作的增、删、改操作都在工作区中进行，在执行提交命令之前，数据库中的数据（永久存储介质上的数据）并没有发生任何改变，用户本人可以通过查询命令查看对数据库操作的结果，但是整个网络上的其他用户并没有看到你对数据库所作的改动。–提交命令就是使对数据的改变永久化。Oracle中设置了一个自动提交的开关，如果把它设置为ON,那么所有的改变数据库中数据的语句都会立即生效，影响数据库中的数据，缺省值为OFF。格式：SETAUTOCOMMIT[ON|OFF];事务提交（COMMIT）DDL（创建表、索引等）会生成隐含的COMMIT命令。COMMIT语句应用的概念性脚本：insertnewrowsupdaterowCOMMIT;事务回退（ROLLBACK）•一般格式：–ROLLBACK;•说明–在尚未对数据库提交的时候，可以用事务回退命令ROLLBACK，将数据库回退到上次COMMIT后的状态。–一旦事务已经提交，就不能再使用事务回退命令进行回退了。–事务回退将回退整个事务，如果只需回退事务的一部分时，可以使用保存点，将整个事务划分为若干部分，这样就可以回退部分事务了。•保存点的设置和使用格式：–SAVEPOINT保存点名称；–ROLLBACKTO保存点名称;事务处理示例：ROLLBACK•[例]使用cw_xs_sfzd表，练习事务处理命令。DELETEFROMcw_xs_sfzdWHERESFBM=‘01’;SELECT*FROMEMPWHERESFBM=‘01’;ROLLBACK;SELECT*FROMEMPWHERESFBM=‘01’;INSERTINTOcw_xs_sfzd(sfbm,sf,bmdm)VALUES(‘01’,’北京’,’04’);SAVEPOINTINSERT_POINT;INSERTINTOcw_xs_sfzd(sfzd,sf,bmdm)VALUES(‘99’,’东软’,’04’);ROLLBACKTOINSER_POINT;COMMIT；第三节PL/SQL的流程控制•条件控制•循环控制•跳转控制条件控制•IF_THEN_ELSE语句–语法格式：IF条件THEN语句组1`ELSE语句组2ENDIF;–功能：•如果条件成立，将执行语句组1，否则执行语句组2，执行完后，转到下一条语句执行。–说明：•条件是一个布尔型变量或表达式，取值只能是TRUE，FALSE，NULL。•执行流程语句组1语句组2条件truefalse条件控制•IF_THEN_ELSIF语句–语法格式：IF条件1THEN语句组1ELSIF条件2THEN语句组2［ELSIF条件nTHEN语句组n．．．］[ELSE语句组n+1]]ENDIF;–功能：•如果条件1成立，将执行语句组1；否则判断条件2，如果成立执行语句组2；否则，判断条件3，如此循环，直到判断条件n，如果都不成立，则执行执行语句组n+1，执行完后，转到下一条语句执行。–说明：•ELSIF不要误写为ELSEIF。•执行流程语句组1语句组2条件1truefalse条件2条件nfalse语句组n语句组n+1falsetruetrue……条件控制•[示例]根据表emp中DEPTNO字段的值，为姓名为FAN的雇员修改工资；若部门号为10，则工资加100；若部门号为20，则工资加300；否则工资加400。declarenameemp.ename%type:='FAN';incrementemp.sal%type;fandeptemp.deptno%type;beginselectdeptnointofandeptfromempwhereename='FAN';iffandept=10thenincrement:=200;elsiffandept=20thenincrement:=300;elseincrement:=400;endif;updateempsetsal=sal+incrementwhereename='FAN';commit;end;/循环控制语句•FOR循环–语法格式：FOR循环变量IN[REVERSE]下界．．上界LOOP语句组ENDLOOP;•说明：–循环变量是控制循环的变量，它不需要