第5章plsql储存过程函数分页北大青鸟oracle9i

第五章PL/SQl基础PL/SQl块结构PL/SQl数据类型和操作符复合数据类型PL/SQL中的SQL条件结构循环结构本章目标PL/SQl简介PL/SQL是过程语言(ProceduralLanguage)与结构化查询语言(SQL)结合而成的编程语言PL/SQL是对SQL的扩展支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构可用于创建存储过程、触发器和程序包，给SQL语句的执行添加程序逻辑与Oracle服务器和Oracle工具紧密集成，具备可移植性、灵活性和安全性PL/SQl简介支持SQL，在PL/SQL中可以使用：数据操纵命令事务控制命令游标控制SQL函数和SQL运算符支持面向对象编程(OOP)可移植性，可运行在任何操作系统和平台上的Oralce数据库更佳的性能，PL/SQL经过编译执行安全性，可以通过存储过程限制用户对数据的访问用户将整个语句块发送给OracleOracleProcedureBeginProcedureCallSQLCommand…EndPL/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;

写一个简单的PL/SQL过程,输出HelloWorld!!!

PL/SQl数据类型和操作符PL/SQL支持的内置数据类型数据类型LOB类型标量类型属性类型%ROWTYPE数字字符布尔型日期时间BFILEBLOBCLOBNCLOB%TYPE提供某个变量或数据库表列的数据类型提供表示表中一行的记录类型存储非结构化数据块PL/SQl数据类型和操作符指定数值的存储格式数字数据类型NUMBERBINARY_INTEGERPLS_INTEGERNATURALNATURALLNPOSITIVEPOSITIVENDECIMALFLOATINTEGERREALSIGNTYPE存储有符号整数，所需存储空间少于NUMBER类型值存储整数、实数和浮点数PL/SQl数据类型和操作符字符数据类型包括：CHARVARCHAR2LONGRAWLONGRAWPL/SQL的数据类型与SQL数据类型的比较数据类型SQL类型PL/SQL类型CHAR1..20001..32767LONG1..2GB1..32760LONGRAW1..2GB1..32760RAW1..20001..32767VARCHAR21..40001..32767PL/SQl数据类型和操作符用于引用数据库列的数据类型，以及表示表中一行的记录类型属性类型有两种：%TYPE-引用变量和数据库列的数据类型%ROWTYPE-提供表示表中一行的记录类型使用属性类型的优点：不需要知道被引用的表列的具体类型如果被引用对象的数据类型发生改变，PL/SQL变量的数据类型也随之改变icodeitemfile.itemcode%TYPE;emp_recscott.emp%ROWTYPE;PL/SQL变量定义PL/SQL块中可以使用变量和常量在声明部分声明，使用前必须先声明声明时必须指定数据类型，每行声明一个标识符在可执行部分的SQL语句和过程语句中使用声明变量和常量的语法：identifier[CONSTANT]datatype[NOTNULL] [:=|DEFAULTexpr];给变量赋值有两种方法：使用赋值语句:=使用SELECTINTO语句演示变量用法,以及输入变量,变量赋值用户管理要连接到Oracle数据库，就需要创建一个用户帐户每个用户都有一个默认表空间和一个临时表空间CREATEUSER命令用于创建新用户CREATEUSER命令的语法是：更改用户密码删除用户CREATEUSERtomIDENTIFIEDBYaccpDEFAULTTABLESPACEUSERSTEMPORARYTABLESPACETEMP;复合数据类型复合数据类型可以描述结构复杂的数据,主要包括:记录类型Index_by表嵌套表可变数组所有复合数据类型都要遵守先定义类型后基于类型声明变量的顺序数组复合数据类型记录类型

1显式定义

TYPEtypeNameISRECORD(fileds);varRectypeName;

演示：区别使用属性类型的优势。

2隐式定义

varRecdept%ROWTYPE;3记录变量的初始化方法：

varRec.field:=value;

SELECTINTOvarRec4记录变量的使用

复合数据类型Index_by表（关联数组）

1定义

TYPEarrTypeISTABLEOFsqlTypeINDEXBYpls_integer;arrarrType;3初始化方法：

arr(i):=value;SELECTINTOarr(i)4特点没有边界可以动态增加元素

5可以作为map结构DECLARETYPEidMapTypeISTABLEOFVARCHAR2(50)INDEXBYVARCHAR2(20);--定义map类型

stuInfoidMapType;--基于类型，创建变量BEGIN stuInfo('TOM'):='34546457/6'; stuInfo('ROCKY'):='89756756756'; stuInfo('ROSE'):=''; DBMS_OUTPUT.PUT_LINE(stuInfo('ROCKY'));END;什么时候使用作为映射结构

复合数据类型嵌套表

1定义

TYPEnestArrISTABLEOFsqlType;或者CREATEORREPLACETYPEnestArrISTABLEOFVARCHAR2(10);arrnestArr;3初始化方法：

arr:=nestArr(e1,…);构造器初始化，OOP特征。4特点使用arr.extend(n)动态增加元素，否则越界。（与indexby表不同）

5什么时候使用？索引值不连续，没有边界值需要经常更新的情况

复合数据类型可变数组

1定义

TYPEvArrISVARRAY(n)OFsqlType;或者CREATEORREPLACETYPEvArrISTABLEOFVARCHAR2(10);arrvArr;3初始化方法：

arr:=nestArr(e1,…);构造器初始化，OOP特征。4特点如果元素超出初始定义或者使用没初始化的元素则使用arr.extend(n)动态增加元素，否则越界。

5什么时候使用？元素数量可以确定通常按顺序存取

复合数据类型集合处理函数Index_By表，嵌套表，VARRAY区别

定义初始化自动增长删除元素是否存储Typetableindexby逐个是delete()否Typetable构造器extenddelete()是Typevarray(n)构造器extend赋null是

countDelete(n)或delete删除指定或者所有Varray非法Extend(x)或extend末尾添加Index_by非法first首元素下标last末元素下标Trim或trim(x)末尾删除Index_by非法Next()下个元素值Prior()上个元素值PL/SQL中的SQL静态SQL编译时确定的

SELECT…INTO…FROM…INSERTUPDATEDELETE

注意提交动态SQL

编译不确定，运行时确定

DDL语句在LP/SQL中必须用动态的

DML语句也可以使用动态方式

执行动态语句使用EXECUTEIMMEDIATEsql[USINGparas][RETURNINTOvars]

例如：EXECUTEIMMEDIATE‘INSERTINTOdeptVALUES(82,:1,:2)’USING‘IT’,‘SZ‘;

返回参数：EXECUTEIMMEDIATE'INSERTINTOdeptVALUES(83,:1,:2)RETURNdNameINTO:3'USING'IT','SZ'RETURNINGINTOstr;

如果需要返回值，则使用RETURNcolNameINTOvars条件语句

1IF..THEN..ENDIF;2IF..THEN…ELSE…ENDIF3IF..THEN…ELSIF…THEN…ENDIF4CASEselectorWHENexpTHEN…;WHENexpTHEN…;ELSE…;ENDCASE;

CASEWHENexpTHEN…;WHEN表达式THEN…;WHEN表达式