Oracle数据库使用-PLSQL.ppt

使用PL/SQL,2,回顾,同义词是现有数据库对象的别名序列用于生成唯一、连续的序号视图是基于一个或多个表的虚拟表索引是与表相关的一个可选结构，用于提高SQL语句执行的性能索引类型有标准索引、唯一索引、反向键索引、位图索引和基于函数的索引索引组织表基于主键访问数据,3,目标,理解PL/SQL功能和特点了解数据类型及其用法理解逻辑比较理解控制结构掌握错误处理,4,PL/SQL简介,PL/SQL是过程语言(ProceduralLanguage)与结构化查询语言(SQL)结合而成的编程语言PL/SQL是对SQL的扩展支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构可用于创建存储过程、触发器和程序包，给SQL语句的执行添加程序逻辑与Oracle服务器和Oracle工具紧密集成，具备可移植性、灵活性和安全性,5,PL/SQL的优点2-1,支持SQL，在PL/SQL中可以使用：数据操纵命令事务控制命令游标控制SQL函数和SQL运算符支持面向对象编程(OOP)可移植性，可运行在任何操作系统和平台上的Oralce数据库更佳的性能，PL/SQL经过编译执行,用户将整个语句块发送给Oracle,6,PL/SQL的优点2-2,与SQL紧密集成，简化数据处理。支持所有SQL数据类型支持NULL值支持%TYPE和%ROWTYPE属性类型,安全性，可以通过存储过程限制用户对数据的访问,7,PL/SQL的体系结构,PL/SQL引擎驻留在Oracle服务器中该引擎接受PL/SQL块并对其进行编译执行,将PL/SQL块发送给Oracle服务器,用户,执行过程语句,引擎将SQL语句发送给SQL语句执行器,执行SQL语句,将结果发送给用户,8,PL/SQL块简介,PL/SQL块是构成PL/SQL程序的基本单元将逻辑上相关的声明和语句组合在一起PL/SQL分为三个部分，声明部分、可执行部分和异常处理部分DECLAREdeclarationsBEGINexecutablestatementsEXCEPTIONhandlersEND;,DECLAREqty_on_handNUMBER(5);BEGINSELECTquantityINTOqty_on_handFROMProductsWHEREproduct=芭比娃娃FORUPDATEOFquantity;IFqty_on_hand0THENUPDATEProductsSETquantity=quantity+1WHEREproduct=芭比娃娃;INSERTINTOpurchase_recordVALUES(已购买芭比娃娃,SYSDATE);ENDIF;COMMIT;EXCEPTION/*异常处理语句*/WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(出错:|SQLERRM);END;,声明部分定义变量、游标和自定义异常,包含SQL和PL/SQL语句的可执行部分,指定出现错误时需要执行的操作,9,变量和常量1-2,PL/SQL块中可以使用变量和常量在声明部分声明，使用前必须先声明声明时必须指定数据类型，每行声明一个标识符在可执行部分的SQL语句和过程语句中使用声明变量和常量的语法：,identifierCONSTANTdatatypeNOTNULL:=|DEFAULTexpr;,给变量赋值有两种方法：使用赋值语句:=使用SELECTINTO语句,10,变量和常量2-2,DECLAREicodeVARCHAR2(6);p_catgVARCHAR2(20);p_rateNUMBER;c_rateCONSTANTNUMBER:=0.10;BEGIN.icode:=i205;SELECTp_category,itemrate*c_rateINTOp_catg,p_rateFROMitemfileWHEREitemcode=icode;.END;,11,数据类型,PL/SQL支持的内置数据类型,数据类型,LOB类型,标量类型,属性类型,%ROWTYPE,数字,字符,布尔型,日期时间,BFILE,BLOB,CLOB,NCLOB,%TYPE,提供某个变量或数据库表列的数据类型,提供表示表中一行的记录类型,存储非结构化数据块,12,数字数据类型,指定数值的存储格式,数字数据类型,NUMBER,BINARY_INTEGER,PLS_INTEGER,NATURAL,NATURALLN,POSITIVE,POSITIVEN,DECIMAL,FLOAT,INTEGER,REAL,SIGNTYPE,存储有符号整数，所需存储空间少于NUMBER类型值,存储整数、实数和浮点数,存储有符号整数，可使算术计算快速而有效,13,字符数据类型,字符数据类型包括：CHARVARCHAR2LONGRAWLONGRAWPL/SQL的数据类型与SQL数据类型的比较,14,日期时间和布尔数据类型,日期时间类型存储日期和时间数据常用的两种日期时间类型DATETIMESTAMP布尔数据类型此类别只有一种类型，即BOOLEAN类型用于存储逻辑值(TRUE、FALSE和NULL)不能向数据库中插入BOOLEAN数据不能将列值保存到BOOLEAN变量中只能对BOOLEAN变量执行逻辑操作,15,LOB数据类型2-1,用于存储大文本、图像、视频剪辑和声音剪辑等非结构化数据。LOB数据类型可存储最大4GB的数据。LOB类型包括：BLOB将大型二进制对象存储在数据库中CLOB将大型字符数据存储在数据库中NCLOB存储大型UNICODE字符数据BFILE将大型二进制对象存储在操作系统文件中,16,LOB数据类型2-2,LOB类型的数据库列仅存储定位符，该定位符指向大型对象的存储位置DBMS_LOB程序包用于操纵LOB数据,SETSERVEROUTPUTONDECLAREclob_varCLOB;amountINTEGER;offsetINTEGER;output_varVARCHAR2(100);BEGINSELECTchapter_textINTOclob_varFROMmy_book_textWHEREchapter_id=5;amount:=24;-要读取的字符数offset:=1;-起始位置DBMS_LOB.READ(clob_var,amount,offset,output_var);DBMS_OUTPUT.PUT_LINE(output_var);END;/,从表中选择CLOB定位符到clob_var变量中,从CLOB数据中读取24个字符存储到output_var变量中,显示读到的信息,17,属性类型,用于引用数据库列的数据类型，以及表示表中一行的记录类型属性类型有两种：%TYPE-引用变量和数据库列的数据类型%ROWTYPE-提供表示表中一行的记录类型使用属性类型的优点：不需要知道被引用的表列的具体类型如果被引用对象的数据类型发生改变，PL/SQL变量的数据类型也随之改变,icodeitemfile.itemcode%TYPE;emp_recscott.emp%ROWTYPE;,18,布尔表达式的结果为TRUE、FALSE或NULL，通常由逻辑运算符AND、OR和NOT连接布尔表达式有三种类型：数字布尔型字符布尔型日期布尔型,逻辑比较,逻辑比较用于比较变量和常量的值，这些表达式称为布尔表达式布尔表达式由关系运算符与变量或常量组成,19,控制结构,PL/SQL支持的流程控制结构：条件控制IF语句CASE语句循环控制LOOP循环WHILE循环FOR循环顺序控制GOTO语句NULL语句,20,条件控制2-1,IF语句根据条件执行一系列语句，有三种形式：IF-THEN、IF-THEN-ELSE和IF-THEN-ELSIF,DECLAREicodeVARCHAR2(4);irateNUMBER;BEGINicode:=i203;SELECTitemrateINTOirateFROMitemfileWHEREitemcode=icode;IFirate200THENUPDATEitemfileSETitemrate=itemrate-200WHEREitemcode=icode;ELSEUPDATEitemfileSETitemrate=itemrate-50WHEREitemcode=icode;ENDIF;DBMS_OUTPUT.PUT_LINE(itemrate=|irate);END;,21,条件控制2-2,BEGINCASE,CASE语句用于根据单个变量或表达式与多个值进行比较执行CASE语句前，先计算选择器的值,22,循环控制,循环控制用于重复执行一系列语句循环控制语句包括：LOOP、EXIT和EXITWHEN循环控制的三种类型：LOOP-无条件循环WHILE-根据条件循环FOR-循环固定的次数,LOOPsequence_of_statementsENDLOOP;,WHILEconditionLOOPsequence_of_statementsENDLOOP;,FORcounterINREVERSEvalue1.value2LOOPsequence_of_statementsENDLOOP;,23,顺序控制,顺序控制用于按顺序执行语句顺序控制语句包括：GOTO语句-无条件地转到标签指定的语句NULL语句-什么也不做的空语句,DECLAREqtyhanditemfile.qty_hand%type;relevelitemfile.re_level%type;BEGINSELECTqty_hand,re_levelINTOqtyhand,relevelFROMitemfileWHEREitemcode=i201;IFqtyhandUPDATEitemfileSETqty_hand=qty_hand+re_levelWHEREitemcode=i201;NULL;END;,24,动态SQL,动态SQL是指在PL/SQL程序执行时生成的SQL语句编译程序对动态SQL不做处理，而是在程序运行时动态构造语句、对语句进行语法分析并执行DDL语句命令和会话控制语句不能在PL/SQL中直接使用，但是可以通过动态SQL来执行执行动态SQL的语法：EXECUTEIMMEDIATEdynamic_sql_stringINTOdefine_variable_listUSINGbind_argument_list;,DECLAREsql_stmtVARCHAR2(200);emp_idNUMBER(4):=7566;emp_recemp%ROWTYPE;BEGINEXECUTEIMMEDIATECREATETABLEbonus1(idNUMBER,amtNUMBER);sql_stmt:=SELECT*FROMempWHEREempno=:id;EXECUTEIMMEDIATEsql_stmtINTOemp_recUSINGemp_id;END;,25,错误处理2-1,在运行程序时出现的错误叫做异常发生异常后，语句将停止执行，控制权转移到PL/SQL块的异常处理部分异常有两种类型：预定义异常-当PL/SQL程序违反Oracle规则或超越系统限制时隐式引发用户定义异常-用户可以在PL/SQL块的声明部分定义异常，自定义的异常通过RAISE语句显式引发,26,错误处理2-2,处理预定义异常处理用户定义异常,DECLAREordernumVARCHAR2(5);BEGINSELECTordernoINTOordernumFROMorder_master;EXCEPTIONWHENTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE(返回多行);END;,DECLAREinvalidCATEGORYEXCEPTION;categoryVARCHAR2(10);BEGINcategory:=,27,RAISE_APPLICATION_ERROR过程用于创建用户定义的错误信息可以在可执行部分和异常处理部分使用错误编号必须介于20000和20999之间错误消息的长度可长达2048个字节引发应用程序错误的语法：RAISE_APPLICATION_ERROR(error_number,error_message);,引发应用程序错误,DECLARErateitemfile.itemrate%TYPE;rate_exceptionEXCEPTION;BEGINSELECTNVL(itemrate,0)INTOrateFROMitemfileWHEREitemcode=i207;IFrate=0THENRAISErate_exception;ELSEDBMS_OUTP