oracle3968607246

Oracle10g数据库应用教程授课教师：职务：第10章PL/SQL程序设计课程描述介绍Oracle数据库程序设计中经常会用到的5个概念，即游标、过程、函数、包、和触发器。本章知识点复合数据类型游标过程和函数子程序和包触发器10.1复合数据类型1.PL/SQL记录（RECORD）记录有以下特点：1.每个记录内可以有许多字段2.记录可以赋初值，NOTNULL可以限定记录3.无初值的记录为NULL4.可以使用保留字DEFAULT5.可以在任意一个块、子程序、或包中的声明部分定义RECORD类型并声明用户子定义的记录。6.可以声明并引用嵌套的记录2.创建PL/SQL记录TYPE记录类型名ISRECORD(字段名数据类型[，字段名数据类型]…);记录变量记录类型名；数据类型：可以是PL/SQL的数据类型或%TYPE和%ROWTYPE属性10.1复合数据类型3.为PL/SQL记录赋值记录变量.字段名对记录赋值可以逐个为记录中的字段赋值也可以一次对所有字段值赋值1.通过将一个记录赋值给另一个具有相同类型的记录emp_record1:=emp.record22.使用SELECTINTO或FETCHINTO语句SELECTename,job,salINTOemp_recordFROMempWHEREename=‘SMITH’;列名必须按照与记录中字段相同的顺序出现10.1复合数据类型4.%ROWTYPE为表或试图中的列的集合数据类型。可以定义同表一样的数据类型，而且不必知道数据库中表列的数量和类型与长度声明语法：DECLARE记录变量表名%ROWTYPE例如：利用%ROWTYPE声明一个记录型变量emp_record,并将从emp表中选出的数据行存入记录emp_record。DECLAREemp_recordemp%ROWTYPE;BEGINSELECT*INTOemp_recordFROMemp;WHERE…END;10.1复合数据类型5.嵌套记录PL/SQL容许声明并引用嵌套记录。也就是说一个记录可以是另一个记录的组件。TYPEtimetypeISRECORD(tminuteSMALLINT,thourSMALLINT);TYPEmeetingtypeISRECORD(mdayDATE,mtimetimetype);meetingmeetingtype;seminarmeetingtype;TYPEpartytypeISRECORD(pdayDATE,ptimetimetype);partypartytype;10.1复合数据类型PL/SQL容许嵌套记录赋值给另一个具有相同数据类型记录seminar.mtime:=meeting.mtime;属性不同记录类型的属性之间可以相互赋值party.ptime:=meeting.mtime;10.2游标(CURSOR)游标的基本概念游标控制语句游标属性游标FOR循环10.2.1游标的基本概念游标作用游标(CURSOR)可以将多条查询记录进行逐行提取，并逐个处理这些数据。是PL/SQL的一种控制结构，ORACLE服务器使用专用SQL工作区来存储处理这些查询记录数据。游标有两种形式隐式游标--也叫SQL游标，由ORACLE创建。显示游标—用户自己显示声明。2019年7月31日星期三Oracle10g管理及应用10.2.1游标的基本概念（续）1.隐式游标隐式游标是指在PL/SQL程序在执行一个SQL查询语句时，Oracle服务器自动创建的未命名的游标。隐式游标是内存中处理此查询语句的工作区域。与显式游标不同的是，隐式游标不需要声明、不能使用OPEN、CLOSE、FETCH语句，但可以使用游标属性从最近执行的SQL语句中获取记录。注意：由于SELECTINTO语句只能读取一行数据到记录变量或一组变量，所以隐式游标只能用于只有一行数据需要处理的情况，对于有多行数据需要处理时，只能使用显示游标而不能使用隐式游标。10.2.1游标的基本概念（续）2、显式游标显式游标对表的行数据进行处理的操作过程，主要包括以下四步：声明游标、打开游标、提取数据和关闭游标。1．声明游标声明游标就是声明变量，使变量成为指定的PL/SQL控制结构。2．打开游标在游标声明以后，读取数据之前，必须先打开游标才能使用游标，打开游标使用OPEN语句3．提取数据提取游标处理表中的各数据行，放到对应结构的变量中，提取数据的命令为FETCH。4．关闭游标在游标使用完之后，必须要关闭游标。关闭游标使用CLOSE语句。10.2.2游标控制语句1、声明游标。2、打开游标。3、读取数据。4、关闭游标。10.2.2游标控制语句（续）1声明游标语句：DECLARECURSOR游标名[(参数定义列表)]ISSELECT语句;【例】声明两个游标分别是emp_cursor,dept_cursorDECLARE…CURSORemp_cursorISSELECTempno,enameFROMemp;CURSORdept_cursorISSELECT*FROMdeptWHEREdeptno=10;…BEGIN…10.2.2游标控制语句（续）【例】游标可以使用子查询。DECLARECURSORmy_cursorISSELECTt1.deptno,t1dname,t2.STAFFFROMdeptt1,(SELECTdeptno,count(*)STAFFFROMempGROUPBYdeptno)t2WHEREt1.deptno=t2.deptnoANDT2.STAFF=5;…游标作用：游标建立了数据集合，包括部门号，部门名以及工人数不小于5的部门的员工人数10.2.2游标控制语句（续）【例】声明一个带参数的游标MyCur，读取指定类型的用户信息：DECLARECURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;注意：参数仅可用于游标的SELECT语句的输入，在查询中常量出现的位置上出现10.2.2游标控制语句（续）2打开游标语句：OPEN游标名[(参数列表)];发生一系列动作：•为查询数据动态分配内存•执行查询语句•绑定输入参数，并为参数变量赋值•标识活动集，游标指针指向活动集的第一行【例】打开游标OPENemp_cursor；OPENmy_cursor；OPENMyCur(1);10.2.2游标控制语句（续）3游标取值语句。游标取值语句FETCH的语法结构如下：FETCH游标名INTO变量列表;•一次从活动集中提取一行记录•每执行一次语句后，游标指针指向活动集的下一行•变量列表中的变量要与SELECT语句中的列的数量相同，并数据类型也要一致【例】在打开的游标MyCur的当前位置读取数据：FETCHMyCurINTOvarId,varName;10.2.2游标控制语句（续）【例】利用游标依次检索10个员工的编号和姓名，保存在变量中：SETSERVEROUTPUTON;DECLAREv_empnoemp.empno%TYPE;v_enameemp.ename%TYPE;iNUMBER:=1;CURSORc1ISSELECTempno,enameFROMemp;BEGINOPENc1;FORiIN1..10LOOPFETCHc1INTOv_empno,v_ename;DBMS_OUTPUT.PUT_LINE(‘empnois’||v_empno||’andenameis’||v_ename);ENDLOOP;END;10.2.2游标控制语句（续）4关闭游标语句：CLOSE游标名;•关闭游标后，所以和游标相关的资源全部释放•如果再用还可以再打开。可以多次创建活动集，同时打开游标数由OPEN_CURSORS参数决定，缺省值为50•任何对关闭了的游标操作都会引发INVALID_CLURSOR错误【例】关闭游标MyCur：CLOSEMyCur;10.2.2游标控制语句(续)【例】下面介绍一个完整的游标应用实例：SETServerOutputON;DECLARE--开始声明部分varIdNUMBER;--声明变量,用来保存游标中的用户编号varNameVARCHAR2(50);--声明变量,用来保存游标中的用户名--定义游标,varType为参数,指定用户类型编号CURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;BEGIN--开始程序体OPENMyCur(1);--打开游标,参数为1,表示读取用户类型编号为1的记录FETCHMyCurINTOvarId,varName;--读取当前游标位置的数据CLOSEMyCur;--关闭游标dbms_output.put_line('用户编号:'||varId||',用户名:'||varName);--显示读取的数据END;--结束程序体10.2.2游标控制语句(续)5．使用游标更新数据在声明游标时，需要应用FORUPDATE选项，并通过UPDATE语句完成更新数据操作，此时游标声明的语法格式如下所示：CURSOR游标名ISSELECT语句FORUPDATE[OF列名][NOWAIT];NOWAIT作用，如果其他事务锁定了这一行，就返回一个ORACLE错误FORUPDATE子句声明游标，则WHERECURRENTOF游标名可以用于UPDATE或DELETE语句中10.2.2游标控制语句(续)例如：利用游标检索部门号为30的每一个员工，将工资提高10%DECLAREvar_salemp.sal%TYPE;CURSORsal_cursor(var_deptnonumber)ISSELECTsalFROMempWHEREdeptno=var_deptnoFORUPDATEOFsalNOWAIT;BEGINOPENsal_cursor(30);LOOPFETCHsal_cursorINTOvar_sal;EXITWHENsal_cursor%NOTFOUNDORsal_cursor%NOTFOUNDISNULL;UPDATEempSETsal=var_sal*1.1WHERECURRENTOFsal_cursor;ENDLOOP;CLOSEsal_cursor;END;9.4.2游标的属性操作在游标的使用过程中，经常需要应用到游标的四个属性，以确定游标当前和总体状态：属性名说明%ISOPEN逻辑值，判断游标是否已打开，如游标未打开其值为false，否则为true%FOUND逻辑值，判断游标是否指向数据行。如游标当前指向一行数据则返回true，否则为false。本属性常用于控制游标循环的结束。%NOTFOUND逻辑值，判断游标是否没指向数据行。其值是%FOUND属性值的非。本属性常用于控制游标循环的结束。%ROWCOUNT返回游标当前已提取的记录的行数，每成功提取一次数据行其值加110.2.3游标属性（1）%ISOPEN属性【例】下面的代码演示当使用未打开的游标时，将会出现错误：/*打开显示模式*/SETServerOutputON;DECLAREvarNameVARCHAR2(50);--声明变量,用来保存游标中的用户名varIdNUMBER;--声明变量,用来保存游标中的用户编号--定义游标,varType为参数,指定用户类型编号CURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;BEGIN--开始程序体FETCHMyCurINTOvarId,varName;--读取当前游标位置的数据CLOSEMyCur;--关闭游标dbms_output.put_line('用户编号:'||varId||',用户名:'||varName);END;10.2.3