《Oracle数据库应用与开发》第05章[PLSQL]理论课

使用PL/SQL《Oracle数据库应用与开发》第05章回顾•同义词是现有数据库对象的别名•序列用于生成唯一、连续的序号•视图是基于一个或多个表的虚拟表•索引是与表相关的一个可选结构，用于提高SQL语句执行的性能•索引类型有标准索引、唯一索引、反向键索引、位图索引和基于函数的索引•索引组织表基于主键访问数据目标•理解PL/SQL功能和特点•了解数据类型及其用法•理解逻辑比较•理解控制结构•掌握错误处理PL/SQL简介•PL/SQL是过程语言(ProceduralLanguage)与结构化查询语言(SQL)结合而成的编程语言•PL/SQL是对SQL的扩展•支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构•可用于创建存储过程、触发器和程序包，给SQL语句的执行添加程序逻辑•与Oracle服务器和Oracle工具紧密集成，具备可移植性、灵活性和安全性PL/SQL的优点2-1•支持SQL，在PL/SQL中可以使用：–数据操纵命令–事务控制命令–游标控制–SQL函数和SQL运算符•支持面向对象编程(OOP)•可移植性，可运行在任何操作系统和平台上的Oralce数据库•更佳的性能，PL/SQL经过编译执行用户将整个语句块发送给OracleOracleProcedureBeginProcedureCallSQLCommand…EndPL/SQL的优点2-2PL/SQLSQL•与SQL紧密集成，简化数据处理。–支持所有SQL数据类型–支持NULL值–支持%TYPE和%ROWTYPE属性类型•安全性，可以通过存储过程限制用户对数据的访问PL/SQL的体系结构•PL/SQL引擎驻留在Oracle服务器中•该引擎接受PL/SQL块并对其进行编译执行将PL/SQL块发送给Oracle服务器用户执行过程语句引擎将SQL语句发送给SQL语句执行器Oracle服务器PL/SQL引擎SQL语句执行器过程语句执行器执行SQL语句将结果发送给用户PL/SQL块简介•PL/SQL块是构成PL/SQL程序的基本单元•将逻辑上相关的声明和语句组合在一起•PL/SQL分为三个部分，声明部分、可执行部分和异常处理部分[DECLAREdeclarations]BEGINexecutablestatements[EXCEPTIONhandlers]END;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语句的可执行部分指定出现错误时需要执行的操作PL/SQL中的复合符号•:=赋值操作符•||连接操作符•--单行注释•/**/多行注释•标签分隔符•..范围操作符•**求幂操作符变量和常量1-2•PL/SQL块中可以使用变量和常量–在声明部分声明，使用前必须先声明–声明时必须指定数据类型，每行声明一个标识符–在可执行部分的SQL语句和过程语句中使用•声明变量和常量的语法：identifier[CONSTANT]datatype[NOTNULL][:=|DEFAULTexpr];•给变量赋值有两种方法：–使用赋值语句:=–使用SELECTINTO语句变量和常量2-2DECLAREicodeVARCHAR2(6);p_catgVARCHAR2(20);p_rateNUMBER;c_rateCONSTANTNUMBER:=0.10;BEGIN...icode:='i205';SELECTp_category,itemrate*c_rateINTOp_catg,p_rateFROMitemfileWHEREitemcode=icode;...END;数据类型•PL/SQL支持的内置数据类型数据类型LOB类型标量类型属性类型%ROWTYPE数字字符布尔型日期时间BFILEBLOBCLOBNCLOB%TYPE提供某个变量或数据库表列的数据类型提供表示表中一行的记录类型存储非结构化数据块数字数据类型•指定数值的存储格式数字数据类型NUMBERBINARY_INTEGERPLS_INTEGERNATURALNATURALNPOSITIVEPOSITIVENDECIMALFLOATINTEGERREALSIGNTYPE存储有符号整数，所需存储空间少于NUMBER类型值存储整数、实数和浮点数存储有符号整数，可使算术计算快速而有效字符数据类型•字符数据类型包括：–CHAR–VARCHAR2–LONG–RAW–LONGRAW•PL/SQL的数据类型与SQL数据类型的比较数据类型SQL类型PL/SQL类型CHAR1..20001..32767LONG1..2GB1..32760LONGRAW1..2GB1..32760RAW1..20001..32767VARCHAR21..40001..32767日期时间和布尔数据类型•日期时间类型–存储日期和时间数据–常用的两种日期时间类型•DATE•TIMESTAMP•布尔数据类型–此类别只有一种类型，即BOOLEAN类型–用于存储逻辑值(TRUE、FALSE和NULL)–不能向数据库中插入BOOLEAN数据–不能将列值保存到BOOLEAN变量中–只能对BOOLEAN变量执行逻辑操作LOB数据类型2-1•用于存储大文本、图像、视频剪辑和声音剪辑等非结构化数据。•LOB数据类型可存储最大4GB的数据。•LOB类型包括：–BLOB将大型二进制对象存储在数据库中–CLOB将大型字符数据存储在数据库中–NCLOB存储大型UNICODE字符数据–BFILE将大型二进制对象存储在操作系统文件中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变量中显示读到的信息属性类型•用于引用数据库列的数据类型，以及表示表中一行的记录类型•属性类型有两种：–%TYPE-引用变量和数据库列的数据类型–%ROWTYPE-提供表示表中一行的记录类型•使用属性类型的优点：–不需要知道被引用的表列的具体类型–如果被引用对象的数据类型发生改变，PL/SQL变量的数据类型也随之改变icodeitemfile.itemcode%TYPE;emp_recscott.emp%ROWTYPE;•布尔表达式的结果为TRUE、FALSE或NULL，通常由逻辑运算符AND、OR和NOT连接•布尔表达式有三种类型：–数字布尔型–字符布尔型–日期布尔型逻辑比较•逻辑比较用于比较变量和常量的值，这些表达式称为布尔表达式•布尔表达式由关系运算符与变量或常量组成关系运算符说明=比较两个变量是否相等，如果值相当，则返回True,!=比较两个变量，如果不相等，则返回True比较两个变量，检查值1是否小于值2比较两个变量，检查值1是否大于值2=比较两个变量，检查变量1是否小于等于变量2=比较两个变量，检查变量1是否大于等于变量2控制结构PL/SQL支持的流程控制结构：•条件控制–IF语句–CASE语句•循环控制–LOOP循环–WHILE循环–FOR循环•顺序控制–GOTO语句–NULL语句条件控制2-1•IF语句根据条件执行一系列语句，有三种形式：IF-THEN、IF-THEN-ELSE和IF-THEN-ELSIFDECLAREicodeVARCHAR2(4);irateNUMBER;BEGINicode:='i203';SELECTitemrateINTOirateFROMitemfileWHEREitemcode=icode;IFirate200THENUPDATEitemfileSETitemrate=itemrate-200WHEREitemcode=icode;ELSEUPDATEitemfileSETitemrate=itemrate-50WHEREitemcode=icode;ENDIF;DBMS_OUTPUT.PUT_LINE('itemrate='||irate);END;条件控制2-2BEGINCASE‘&grade’WHEN’A’THENDBMS_OUTPUT.PUT_LINE(’优异’);WHEN’B’THENDBMS_OUTPUT.PUT_LINE(优秀’);WHEN’C’THENDBMS_OUTPUT.PUT_LINE(’良好’);WHEN’D’THENDBMS_OUTPUT.PUT_LINE(’一般’);WHEN’F’THENDBMS_OUTPUT.PUT_LINE(’较差’);ELSEDBMS_OUTPUT.PUT_LINE(’没有此成绩’);ENDCASE;END;•CASE语句用于根据单个变量或表达式与多个值进行比较•执行CASE语句前，先计算选择器的值循环控制•循环控制用于重复执行一系列语句•循环控制语句包括：–LOOP、EXIT和EXITWHEN•循环控制的三种类型：–LOOP-无条件循环–WHILE-根据条件循环–FOR-循环固定的次数LOOPsequence_of_statementsENDLOOP;WHILEconditionLOOPsequence_of_statementsENDLOOP;FORcounterIN[REVERSE]value1..value2LOOPsequence_of_statementsENDLOOP;顺序控制•顺序控制用于按顺序执行语句•顺序控制语句包括：–GOTO语句-无条件地转到标签指定的语句–NULL语句-什么也不做的空语句DECLAREqtyhanditemfile.qty_hand%type;relevelitemfile.re_level%type;BEGINSELECTqty_hand,re_levelINTOqtyhand,relevelFROMitemfileWHEREitemcode='i201';IFqtyhandrelevelTHENGOTOupdation;ELSEGOTOquit;ENDIF;updationUPDATEitemfileSETqty_hand=qty_hand+re_levelWHEREitemcode='i201';quitNULL;END;动态SQL•动态SQL是指在PL/SQL程序执行时生成的SQL语句•编译程序对动态SQL不做处理，而是在程序运行时动态构造语句、对语句进行语法分析并执行•DDL语句命令和会话控制语句不能在PL/SQL中直接使用，但是可以通过动态SQL来执行•执行动态SQL的语法：EXECUTEIMMEDIATEdynamic_sql_strin