PLSQL--清华大学计算中心ORACLE培训资料

《Oracle9iPL/SQL程序设计》ProceduralLanguage《PL/SQL程序设计》2PL/SQL概述PL/SQL是Oracle对关系数据库语言SQL的过程化扩充,它将数据库技术和过程化程序设计语言联系起来,是一种应用开发语言,可使用循环,分支处理数据,将SQL的数据操纵功能与过程化语言数据处理功能结合起来.PL/SQL的使用,使SQL成为一种高级程序设计语言,支持高级语言的块操作,条件判断,循环语句,嵌套等,与数据库核心的数据类型集成,使SQL的程序设计效率更高.PL/SQL是一种块结构语言,构成PL/SQL的基本单位是逻辑块,块中可以嵌套子块,以块为单位向OracleServer传递数据执行,其块结构如下:DECLAREBEGINEXCEPTIONEND;说明部分:说明变量及常量执行部分:使用SQL的DML语言事务处理例外处理:处理错误及警告信息逻辑块结束《PL/SQL程序设计》3PL/SQL语言的特点PL/SQL中可以定义变量,变量有其作用范围PL/SQL是以块的方式设计,块中可以嵌套子块,子块可以位于块中任何部分子块可以是PL/SQL中的一条命令PL/SQL中可以使用条件判断,循环,游标等高级语言程序设计方法模块式的过程化语言,以块为单位执行使用PL/SQL可以优化程序设计,得到更好的性能(例如,执行10个SQL语句,需要访问10次Oracle核心,如果10个SQL组成一个PL/SQL程序,则只需访问一次Oracle核心,将结果一次返回给用户,则程序执行效率高,节省时间,降低通信量)《PL/SQL程序设计》4减少对于Oracle核心的访问,降低网络负责数据库数据类型集成PL/SQL支持全部的SQL数据类型,这些共享的数据类型与SQL所提供的直接存取相结合,使PL/SQL与Oracle数据字典结合成一体.PL/SQL与OracleRDBMS集成在一起,可以使用PL/SQL的集成数据类型,使变量的数据类型在数据库的操作中实时确定.PL/SQL语言的特点《PL/SQL程序设计》5PL/SQL与SQL语言结合PL/SQL语句含有SQL的DML语句,检索及事务处理PL/SQL中可以使用的SQL语句如下:INSERTUPDATEDELETESELECTINTOCOMMITROLLBACKSAVEPOINT支持RDBMS的所有数据类型对于SQL的数据类型进行了扩充,增加了新的数据类型,使PL/SQL所支持的数据类型更灵活.OracleRDBMS对于PL/SQL中引用的表名,列名进行语句检查对于SQL语句的执行结果,PL/SQL提供返回信息检查,通过PL/SQL的例外处理,记录SQL的执行结果.《PL/SQL程序设计》6PL/SQL基础变量的使用和其他高级语言一样,在PL/SQL中可以使用变量注释单行注释:--多行注释:/**/数据类型日期型Date字符型CHAR,VARCHAR2数字型NUMBER布尔型BOOLEAN(TRUE,FALSE,NULL)数据类型FloatIntRealSmallint子类PL/SQL数据类型扩展数字型NUMBER存储整型及实型数据，可以定义精度和取值范围BINARY_INTEGER可存储带符号整数，为整数计算优化性能DECNUMBER的子类型，存储实型数据DOUBLEPRECISIONNUMBER的子类型，高精度实数INTEGERNUMBER的子类型，整数INTNUMBER的子类型，整数PL/SQL数据类型扩展数字型SMALLINTNUMBER的子类型，取值范围比INTEGER小NUMERICNUMBER的子类型，与NUMBER等价REALNUMBER的子类型，存储实型数据PL/SQL数据类型扩展字符型VARCHAR2存放可变长字符串，有最大长度限制CHAR字符型，固定长度LONG长字符型CLOB大对象字符(Oracle8、Oracle8i)日期型Date存储日期数据PL/SQL数据类型扩展布尔型BOOLEAN结果是TRUE或FALSE二进制数据RAW存储多媒体数据，如图象、声音、视频等BLOB大对象如图象、声音、视频等（Oracle8、Oracle8i）《PL/SQL程序设计》11PL/SQL基础数据定义标示符数据类型;标示符数据类型:=PL/SQL表达式;例如,v_salarynumber(7,2);v_enamevarchar2(20):=‘SMITH’;《PL/SQL程序设计》12PL/SQL基础变量赋值变量名:=值;v_salary:=890.76;v_date:=‘25-MAR-99’;Available_now:=FALSE;%TYPE类型CreditReal(7,2);DebitCredit%TYPE;My_nameEmployee.ename%TYPE;%TYPE属性+不必知道My_name的真正数据类型+当数据库中列ename列定义改变时，数据库运行时自动修改．%ROWTYPE:行类型《PL/SQL程序设计》13PL/SQL基础PL/SQL的记录类型把逻辑相关的数据作为一个单元存储起来,在Declare段中定义record类型数据，使某一变量使用该record型数据．定义方法：TYPEr_recordisRECORD(v_nameemp.ename%TYPE,v_jobemp.job%TYPE,v_salemp.sal%TYPE);变量定义r_empr_record;变量使用SELECTename,job,salINTOr_empFROMempWHEREempno=7934;则，r_emp.v_ename,r_emp.v_job,r_emp.v_sal已有值；给变量赋值：r_employeer_record;r_employee.v_ename:=‘JACK’;r_employee.v_job:=‘CLERK’;r_employee.v_sal:=890.98;PL/SQL程序设计《PL/SQL程序设计》14PL/SQL基础TABLE类型数据PL/SQL中的表(table)类型类似于Ｃ语言中的结构类型数组．定义方法：TYPEtable_empISTABLEOFemp.ename%TYPEINDEXBYBINARY_INTEGER;一个PL/SQL表有两个列，(key,value),key列类型即是BINARY_INTEGER,而value类型则是所定义的数据类型．Table类型使用：定义变量my_name为table_emp类型,则可以使用变量my_name,也可以在SQL语句中使用table类型变量．my_nametable_emp;my_name(0):=‘SCOTT’;my_name(1):=‘SMITH’;my_name(2):=‘SUSAN’;SELECTenameINTOmy_name(10)FROMempWHEREempno=7934;《PL/SQL程序设计》15在PL/SQL中使用SQLINSERT语句的使用Declarev_empnoEMP.empno%TYPE:=1234;v_enameEMP.ename%TYPE:=‘SCOTT’;v_jobVARCHAR2(15):=‘MANAGER’;v_deptnoEMP.deptno%TYPE:=20;v_salNUMBER(7,2):=890.50;BeginINSERTINTOemp(empno,ename,job,hiredate,sal,deptno)VALUES(v_empno,v_ename,v_job,SYSDATE,v_sal,v_deptno);END;/注意:非空(NOTNULL)必须有值NOTNULL《PL/SQL程序设计》16PL/SQL中使用SQLDELETE语句的使用Declarev_empnoEMP.empno%TYPE:=1234;BeginDELETEFROMempWHEREempno=v_empno;End;事务处理语句的使用在PL/SQL中可以使用SQL的COMMIT,ROLLBACK及SAVEPOINT语句．Declarev_empnoEMP.empno%TYPE:=1234;BeginDELETEFROMempWHEREempno=v_empno;COMMIT;End;/PL/SQL程序设计条件控制语句IF-THEN语句IF条件成立THEN可执行语句;ENDIF;条件控制语句IF-THEN-ELSE语句IF条件成立THEN执行语句1;ELSE执行语句2；ENDIF;条件控制语句IF-THEN-ELSIF语句IF条件1成立THEN执行语句1;ELSIF条件2成立THEN执行语句2；ELSIF条件3成立THEN执行语句3;...ENDIF;循环语句基本循环LOOP执行语句；EXITWHEN条件成立；ENDLOOP;循环语句WHILE循环WHILE条件成立LOOP执行语句；ENDLOOP;循环语句FOR循环FOR计数器IN低界..高界LOOP执行语句；ENDLOOP;《PL/SQL程序设计》23光标(CURSOR)设计什么是光标光标使用光标说明：在Declare段中命名光标，将光标与一查询语句联系起来.语法：CURSOR光标名(参数)ISSELECT字句；打开光标获取活动集中的行FETCH语句检索活动集中的行，每次一行，每执行一次FECTCH，光标前进到活动集中的下一行。光标下移关闭光标《PL/SQL程序设计》24光标属性光标属性每一个光标有四种属性%FOUND查询语句(FETCH语句)返回记录%NOTFOUND查询语句(FETCH语句)无返回记录，用于循环退出条件%ROWCOUNTFETCH已获取的记录数%ISOPEN光标已打开标记ifnotC%ISOPENthenopenC;endif;LoopFETCHCINTOv_ename,v_sal;EXITWHENC%NOTFOUND;ENDLOOP;属性使用《PL/SQL程序设计》25隐式光标处理隐式光标处理隐式光标是指在处理SQL时,不需定义光标，所使用的SQL语句包括：INSERT,UPDATE,DELETE子句。隐式光标属性SQL%FOUNDSQL%NOTFOUND由执行上述语句结果引起SQL%ROWCOUNT隐式光标的使用Beginupdateempsetsal=1200whereempno=1234;ifSQL%NOTFOUNDtheninsertintoErrors(empno,sal)values(1234,1200);endif;End;数据库中没有编码为1234的职工《PL/SQL程序设计》26例外处理(EXCEPTION)在PL/SQL中,警告信息、出错信息、或返回信息统称为例外(Exception)。有两中类型的例外。Oracle预定义的例外:是由PL/SQL运行过程中，系统自动产生的信息。用户定义例外:是用户根据需要，自己定义使用的例外，执行时由用户自己引起。预定义的例外CURSOR_ALREADY_OPENVALUE_ERRORNO_DATA_FOUNDINVALID_NUMBERTOO_MANY_ROWSZERO_DIVIDEINVALID_CURSOR用户自定义例外用户定义的例外必须在DECLARE段中说明，在Begin段中用RAISE引起，在EXCEPTION段中使用。《PL/SQL程序设计》27例外处理(EXCEPTION)用户定义例外的说明：定义名为out_of_range的例外Declareout_of_rangeEXCEPTION;例外的使用DeclareBeginExceptionWhenNO_DATA_FOUNDTHEN…WhenCURSOR_ALREADY_OPENTHENWhenTOO_MANY_ROWSTHEN...…WhenOTHERSTHEN...END;例外段在Exception段中对返回信息一一作出响应《PL/