《ORACLEPLSQL编程详解--全8篇.doc》由会员分享，可在线阅读，更多相关《ORACLEPLSQL编程详解--全8篇.doc（115页珍藏版）》请在三一办公上搜索。
1、ORACLE PL/SQL编程详解第一章：PL/SQL程序设计简介SQL语言只是访问、操作数据库的语言，并不是一种具有流程控制的程序设计语言，而只有程序设计语言才能用于应用软件的开发。PL /SQL是一种高级数据库程序设计语言，该语言专门用于在各种环境下对ORACLE数据库进行访问。由于该语言集成于数据库服务器中，所以PL/SQL代码可以对数据进行快速高效的处理。除此之外，可以在ORACLE数据库的某些客户端工具中，使用PL/SQL语言也是该语言的一个特点。本章的主要内容是讨论引入PL/SQL语言的必要性和该语言的主要特点，以及了解PL/SQL语言的重要性和数据库版本问题。还要介绍一些贯穿全书
2、的更详细的高级概念，并在本章的最后就我们在本书案例中使用的数据库表的若干约定做一说明。 SQL与PL/SQL 什么是PL/SQLPL/SQL是 Procedure Language & Structured Query Language 的缩写。ORACLE的SQL是支持ANSI(American national Standards Institute)和ISO92 (International Standards Organization)标准的产品。PL/SQL是对SQL语言存储过程语言的扩展。从ORACLE6以后，ORACLE的RDBMS附带了PL/SQL。它现在已经成为一种过程处理语
3、言，简称PL/SQL。目前的PL/SQL包括两部分，一部分是数据库引擎部分；另一部分是可嵌入到许多产品（如C语言，JAVA语言等）工具中的独立引擎。可以将这两部分称为：数据库PL/SQL和工具PL/SQL。两者的编程非常相似。都具有编程结构、语法和逻辑机制。工具PL/SQL另外还增加了用于支持工具（如ORACLE Forms）的句法，如：在窗体上设置按钮等。本章主要介绍数据库PL/SQL内容。 PL/SQL的优点或特征 有利于客户/服务器环境应用的运行对于客户/服务器环境来说，真正的瓶颈是网络上。无论网络多快，只要客户端与服务器进行大量的数据交换。应用运行的效率自然就回受到影响。如果使用PL/
4、SQL进行编程，将这种具有大量数据处理的应用放在服务器端来执行。自然就省去了数据在网上的传输时间。 适合于客户环境PL/SQL由于分为数据库PL/SQL部分和工具PL/SQL。对于客户端来说，PL/SQL可以嵌套到相应的工具中，客户端程序可以执行本地包含PL/SQL部分，也可以向服务发SQL命令或激活服务器端的PL/SQL程序运行。 过程化PL/SQL是Oracle在标准SQL上的过程性扩展，不仅允许在PL/SQL程序内嵌入SQL语句，而且允许使用各种类型的条件分支语句和循环语句，可以多个应用程序之间共享其解决方案。 模块化PL/SQL程序结构是一种描述性很强、界限分明的块结构、嵌套块结构，被
5、分成单独的过程、函数、触发器，且可以把它们组合为程序包，提高程序的模块化能力。 运行错误的可处理性使用PL/SQL提供的异常处理（EXCEPTION），开发人员可集中处理各种ORACLE错误和PL/SQL错误，或处理系统错误与自定义错误，以增强应用程序的健壮性。 提供大量内置程序包ORACLE提供了大量的内置程序包。通过这些程序包能够实现DBS的一些低层操作、高级功能，不论对DBA还是应用开发人员都具有重要作用。当然还有其它的一些优点如：更好的性能、可移植性和兼容性、可维护性、易用性与快速性等。 PL/SQL 可用的SQL语句PL/SQL是ORACLE系统的核心语言，现在ORACLE的许多部件
6、都是由PL/SQL写成。在PL/SQL中可以使用的SQL语句有：INSERT，UPDATE，DELETE，SELECT INTO，COMMIT，ROLLBACK，SAVEPOINT。提示：在 PL/SQL中只能用 SQL语句中的 DML 部分，不能用 DDL 部分，如果要在PL/SQL中使用DDL(如CREATE table 等)的话，只能以动态的方式来使用。ORACLE 的 PL/SQL 组件在对 PL/SQL 程序进行解释时，同时对在其所使用的表名、列名及数据类型进行检查。PL/SQL 可以在SQL*PLUS 中使用。PL/SQL 可以在高级语言中使用。PL/SQL可以在ORACLE的开发
7、工具中使用(如：SQL Developer或Procedure Builder等)。其它开发工具也可以调用PL/SQL编写的过程和函数，如Power Builder 等都可以调用服务器端的PL/SQL过程。 运行PL/SQL程序PL/SQL程序的运行是通过ORACLE中的一个引擎来进行的。这个引擎可能在ORACLE的服务器端，也可能在 ORACLE 应用开发的客户端。引擎执行PL/SQL中的过程性语句，然后将SQL语句发送给数据库服务器来执行。再将结果返回给执行端。第二章：PL/SQL块结构和组成元素PL/SQL块PL/SQL程序由三个块组成，即声明部分、执行部分、异常处理部分。PL/SQL块
8、的结构如下：DECLARE-声明部分:在此声明PL/SQL用到的变量,类型及游标，以及局部的存储过程和函数BEGIN-执行部分:过程及SQL语句,即程序的主要部分EXCEPTION-执行异常部分:错误处理END;其中：执行部分不能省略。PL/SQL块可以分为三类：1.无名块或匿名块（anonymous）：动态构造，只能执行一次，可调用其它程序，但不能被其它程序调用。2.命名块（named）：是带有名称的匿名块，这个名称就是标签。3.子程序（subprogram）：存储在数据库中的存储过程、函数等。当在数据库上建立好后可以在其它程序中调用它们。4.触发器（Trigger）：当数据库发生操作时，会
9、触发一些事件，从而自动执行相应的程序。5.程序包（package）：存储在数据库中的一组子程序、变量定义。在包中的子程序可以被其它程序包或子程序调用。但如果声明的是局部子程序，则只能在定义该局部子程序的块中调用该局部子程序。PL/SQL结构PL/SQL块中可以包含子块；子块可以位于PL/SQL中的任何部分；子块也即PL/SQL中的一条命令；标识符PL/SQL程序设计中的标识符定义与SQL的标识符定义的要求相同。要求和限制有：1.标识符名不能超过30字符；2.第一个字符必须为字母；3.不分大小写；4.不能用-(减号);5.不能是SQL保留字。提示:一般不要把变量名声明与表中字段名完全一样,如果这
10、样可能得到不正确的结果.例如：下面的例子将会删除所有的纪录，而不是EricHu的记录；DECLAREenamevarchar2(20):=EricHu;BEGINDELETEFROMWHEREename=ename;END;变量命名在PL/SQL中有特别的讲究，建议在系统的设计阶段就要求所有编程人员共同遵守一定的要求，使得整个系统的文档在规范上达到要求。下面是建议的命名方法：标识符命名规则例子程序变量V_nameV_name程序常量C_NameC_company_name游标变量Cursor_NameCursor_Emp异常标识E_nameE_too_many表类型Name_table_typ
11、eEmp_record_type表Name_tableEmp记录类型Name_recordEmp_recordSQL*Plus替代变量P_nameP_sal绑定变量G_nameG_year_salPL/SQL变量类型在前面的介绍中，有系统的数据类型，也可以自定义数据类型。下表给出ORACLE类型和PL/SQL中的变量类型的合法使用列表：变量类型在ORACLE8i中可以使用的变量类型有：类型子类说明范围ORACLE限制CHARCharacterStringRowidNchar定长字符串民族语言字符集032767可选,确省=12000VARCHAR2Varchar, StringNVARCHAR2
12、可变字符串民族语言字符集03276740004000BINARY_INTEGER带符号整数,为整数计算优化性能NUMBER(p,s)DecDouble precisionIntegerIntNumericRealSmall int小数, NUMBER的子类型高精度实数整数, NUMBER的子类型整数, NUMBER的子类型与NUMBER等价与NUMBER等价整数,比integer小LONG变长字符串0-4732,767字节DATE日期型公元前4712年1月1日至公元后4712年12月31日BOOLEAN布尔型TRUE, FALSE,NULL不使用ROWID存放数据库行号UROWID通用行标识符
13、，字符类型例1. 插入一条记录并显示；DECLARERow_idROWID;infoVARCHAR2(40);BEGININSERTINTOVALUES(90,财务室,海口)RETURNINGrowid,dname|:|to_char(deptno)|:|locINTOrow_id,info;(ROWID:|row_id);(info);END;其中：RETURNING子句用于检索INSERT语句中所影响的数据行数，当INSERT语句使用VALUES 子句插入数据时，RETURNING 字句还可将列表达式、ROWID和REF值返回到输出变量中。在使用RETURNING 子句是应注意以下几点限制
14、：1不能与DML语句和远程对象一起使用；2不能检索LONG 类型信息；3当通过视图向基表中插入数据时，只能与单基表视图一起使用。例2. 修改一条记录并显示DECLARERow_idROWID;infoVARCHAR2(40);BEGINUPDATEdeptSETdeptno=100WHEREDNAME=财务室RETURNINGrowid,dname|:|to_char(deptno)|:|locINTOrow_id,info;(ROWID:|row_id);(info);END;其中：RETURNING子句用于检索被修改行的信息。当UPDATE语句修改单行数据时，RETURNING子句可以检索
15、被修改行的ROWID和REF值，以及行中被修改列的列表达式，并可将他们存储到PL/SQL变量或复合变量中；当UPDATE语句修改多行数据时，RETURNING子句可以将被修改行的ROWID和REF值，以及列表达式值返回到复合变量数组中。在UPDATE中使用RETURNING子句的限制与INSERT语句中对RETURNING子句的限制相同。例3.删除一条记录并显示DECLARERow_idROWID;infoVARCHAR2(40);BEGINDELETEdeptWHEREDNAME=办公室RETURNINGrowid,dname|:|to_char(deptno)|:|locINTOrow_i
16、d,info;(ROWID:|row_id);(info);END;其中：RETURNING子句用于检索被删除行的信息：当DELETE语句删除单行数据时，RETURNING 子句可以检索被删除行的ROWID和REF值，以及被删除列的列表达式，并可将他们存储到PL/SQL变量或复合变量中；当DELETE语句删除多行数据时，RETURNING 子句可以将被删除行的ROWID和REF值，以及列表达式值返回到复合变量数组中。在DELETE中使用RETURNING 子句的限制与INSERT语句中对RETURNING子句的限制相同。 复合类型ORACLE 在 PL/SQL 中除了提供象前面介绍的各种类型外
17、,还提供一种称为复合类型的类型-记录和表. 记录类型记录类型类似于C语言中的结构数据类型，它把逻辑相关的、分离的、基本数据类型的变量组成一个整体存储起来，它必须包括至少一个标量型或RECORD 数据类型的成员，称作PL/SQL RECORD 的域(FIELD)，其作用是存放互不相同但逻辑相关的信息。在使用记录数据类型变量时，需要先在声明部分先定义记录的组成、记录的变量，然后在执行部分引用该记录变量本身或其中的成员。定义记录类型语法如下:TYPErecord_nameISRECORD(v1data_type1NOTNULL:=default_value,v2data_type2NOTNULL:=
18、default_value,.vndata_typenNOTNULL:=default_value);例4：DECLARETYPEtest_recISRECORD(NameVARCHAR2(30)NOTNULL:=胡勇,InfoVARCHAR2(100);rec_booktest_rec;BEGIN:=胡勇;:=谈PL/SQL编程;|;END;可以用SELECT语句对记录变量进行赋值,只要保证记录字段与查询结果列表中的字段相配即可。例5：DECLARE-定义与表中的这几个列相同的记录数据类型TYPERECORD_TYPE_EMPLOYEESISRECORD(f_nameh_datej_id-声
19、明一个该记录数据类型的记录变量v_emp_recordRECORD_TYPE_EMPLOYEES;BEGINSELECTfirst_name,hire_date,job_idINTOv_emp_recordFROMemployeesWHEREemployee_id=&emp_id;(雇员名称：|雇佣日期：|岗位：|;END;一个记录类型的变量只能保存从数据库中查询出的一行记录，若查询出了多行记录，就会出现错误。数组类型数据是具有相同数据类型的一组成员的集合。每个成员都有一个唯一的下标，它取决于成员在数组中的位置。在PL/SQL中，数组数据类型是VARRAY。定义VARRY数据类型语法如下：例6
20、：DECLARE-定义一个最多保存5个VARCHAR(25)数据类型成员的VARRAY数据类型TYPEreg_varray_typeISVARRAY(5)OFVARCHAR(25);-声明一个该VARRAY数据类型的变量v_reg_varrayREG_VARRAY_TYPE;BEGIN-用构造函数语法赋予初值v_reg_varray:=reg_varray_type(中国,美国,英国,日本,法国);(地区名称：|v_reg_varray(1)|、|v_reg_varray(2)|、|v_reg_varray(3)|、|v_reg_varray(4);(赋予初值NULL的第5个成员的值：|v_r
21、eg_varray(5);-用构造函数语法赋予初值后就可以这样对成员赋值v_reg_varray(5):=法国;(第5个成员的值：|v_reg_varray(5);END;使用%TYPE定义一个变量，其数据类型与已经定义的某个数据变量(尤其是表的某一列)的数据类型相一致，这时可以使用%TYPE。使用%TYPE特性的优点在于：所引用的数据库列的数据类型可以不必知道；所引用的数据库列的数据类型可以实时改变，容易保持一致，也不用修改PL/SQL程序。例7：DECLARE-用%TYPE类型定义与表相配的字段TYPET_RecordISRECORD(T_no%TYPE,T_name%TYPE,T_sal
22、%TYPE);-声明接收数据的变量v_empT_Record;BEGINSELECTempno,ename,salINTOv_empFROMempWHEREempno=7788;(TO_CHAR|TO_CHAR);END;例8：DECLAREv_empno%TYPE:=&no;Typet_recordisrecord(v_name%TYPE,v_sal%TYPE,v_date%TYPE);Rect_record;BEGINSELECTename,sal,hiredateINTORecFROMempWHEREempno=v_empno;|-|-|;END;使用%ROWTYPEPL/SQL提供%R
23、OWTYPE操作符,返回一个记录类型,其数据类型和数据库表的数据结构相一致。使用%ROWTYPE特性的优点在于：所引用的数据库中列的个数和数据类型可以不必知道；所引用的数据库中列的个数和数据类型可以实时改变，容易保持一致，也不用修改PL/SQL程序。例9：DECLAREv_empno%TYPE:=&no;recemp%ROWTYPE;BEGINSELECT*INTOrecFROMempWHEREempno=v_empno;(姓名:|工资:|工作时间:|;END;LOB类型ORACLE提供了LOB (Large OBject)类型，用于存储大的数据对象的类型。ORACLE目前主要支持BFILE,
24、 BLOB, CLOB及NCLOB类型。BFILE (Movie)存放大的二进制数据对象，这些数据文件不放在数据库里，而是放在操作系统的某个目录里，数据库的表里只存放文件的目录。BLOB(Photo)存储大的二进制数据类型。变量存储大的二进制对象的位置。大二进制对象的大小=4GB。CLOB(Book)存储大的字符数据类型。每个变量存储大字符对象的位置，该位置指到大字符数据块。大字符对象的大小=4GB。NCLOB存储大的NCHAR字符数据类型。每个变量存储大字符对象的位置，该位置指到大字符数据块。大字符对象的大小=4GB。BIND变量绑定变量是在主机环境中定义的变量。在PL/SQL程序中可以使用
25、绑定变量作为他们将要使用的其它变量。为了在PL/SQL环境中声明绑定变量，使用命令VARIABLE。例如：VARIABLEreturn_codeNUMBERVARIABLEreturn_msgVARCHAR2(20)可以通过SQL*Plus命令中的PRINT显示绑定变量的值。例如：PRINTreturn_codePRINTreturn_msg例10：VARIABLEresultNUMBER;BEGINSELECT(sal*10)+nvl(comm,0)INTO:resultFROMempWHEREempno=7844;END;-然后再执行PRINTresultPL/SQL表(TABLE)定义记
26、录表（或索引表）数据类型。它与记录类型相似，但它是对记录类型的扩展。它可以处理多行记录，类似于高级中的二维数组，使得可以在PL/SQL中模仿数据库中的表。定义记录表类型的语法如下：TYPEtable_nameISTABLEOFelement_typeNOTNULLINDEXBYBINARY_INTEGER|PLS_INTEGER|VARRAY2;关键字INDEX BY表示创建一个主键索引，以便引用记录表变量中的特定行。方法描述EXISTS(n)如果集合的第n个成员存在，则返回trueCOUNT返回已经分配了存储空间即赋值了的成员数量FIRSTLASTFIRST：返回成员的最低下标值LAST：返
27、回成员的最高下标值PRIOR(n)返回下标为n的成员的前一个成员的下标。如果没有则返回NULLNEXT(N)返回下标为n的成员的后一个成员的下标。如果没有则返回NULLTRIMTRIM：删除末尾一个成员TRIM(n)：删除末尾n个成员DELETEDELETE：删除所有成员DELETE(n)：删除第n个成员DELETE(m, n)：删除从n到m的成员EXTENDEXTEND：添加一个null成员EXTEND(n)：添加n个null成员EXTEND(n,i)：添加n个成员，其值与第i个成员相同LIMIT返回在varray类型变量中出现的最高下标值例11：DECLARETYPEdept_table_
28、typeISTABLEOFdept%ROWTYPEINDEXBYBINARY_INTEGER;my_dname_tabledept_table_type;v_countnumber(2):=4;BEGINFORintIN1.v_countLOOPSELECT*INTOmy_dname_table(int)FROMdeptWHEREdeptno=int*10;ENDLOOP;FORintIN.LOOP(Departmentnumber:|my_dname_table(int).deptno);(Departmentname:|my_dname_table(int).dname);ENDLOOP;
29、END;例12：按一维数组使用记录表DECLARE-定义记录表数据类型TYPEreg_table_typeISTABLEOFvarchar2(25)INDEXBYBINARY_INTEGER;-声明记录表数据类型的变量v_reg_tableREG_TABLE_TYPE;BEGINv_reg_table(1):=Europe;v_reg_table(2):=Americas;v_reg_table(3):=Asia;v_reg_table(4):=MiddleEastandAfrica;v_reg_table(5):=NULL;(地区名称：|v_reg_table(1)|、|v_reg_tabl
30、e(2)|、|v_reg_table(3)|、|v_reg_table(4);(第5个成员的值：|v_reg_table(5);END;例13：按二维数组使用记录表DECLARE-定义记录表数据类型TYPEemp_table_typeISTABLEOFemployees%ROWTYPEINDEXBYBINARY_INTEGER;-声明记录表数据类型的变量v_emp_tableEMP_TABLE_TYPE;BEGINSELECTfirst_name,hire_date,job_idINTOv_emp_table(1).first_name,v_emp_table(1).hire_date,v_e
31、mp_table(1).job_idFROMemployeesWHEREemployee_id=177;SELECTfirst_name,hire_date,job_idINTOv_emp_table(2).first_name,v_emp_table(2).hire_date,v_emp_table(2).job_idFROMemployeesWHEREemployee_id=178;(177雇员名称：|v_emp_table(1).first_name|雇佣日期：|v_emp_table(1).hire_date|岗位：|v_emp_table(1).job_id);(178雇员名称：|v
32、_emp_table(2).first_name|雇佣日期：|v_emp_table(2).hire_date|岗位：|v_emp_table(2).job_id);END;运算符和表达式(数据定义)关系运算符运算符意义=等于 , != , = , =不等于大于=大于或等于一般运算符运算符意义+加号-减号*乘号/除号:=赋值号=关系号.范围运算符|字符连接符逻辑运算符运算符意义IS NULL是空值BETWEENAND介于两者之间IN在一列值中间AND逻辑与OR逻辑或NOT取返,如IS NOT NULL, NOT IN变量赋值在PL/SQL编程中，变量赋值是一个值得注意的地方，它的语法如下：va
33、riable:= expression ;variable是一个PL/SQL变量, expression是一个PL/SQL表达式.字符及数字运算特点空值加数字仍是空值：NULL + = NULL空值加（连接）字符，结果为字符：NULL | = BOOLEAN赋值布尔值只有TRUE, FALSE及NULL三个值。如：DECLAREbDoneBOOLEAN;BEGINbDone:=FALSE;WHILENOTbDoneLOOPNull;ENDLOOP;END;数据库赋值数据库赋值是通过SELECT语句来完成的，每次执行SELECT语句就赋值一次，一般要求被赋值的变量与SELECT中的列名要一一对应
34、。如：例14：DECLAREemp_id%TYPE:=7788;emp_name%TYPE;wages%TYPE;BEGINSELECTename,NVL(sal,0)+NVL(comm,0)INTOemp_name,wagesFROMempWHEREempno=emp_id;(emp_name|-|to_char(wages);END;提示：不能将SELECT语句中的列赋值给布尔变量。可转换的类型赋值CHAR转换为NUMBER：使用TO_NUMBER函数来完成字符到数字的转换，如：v_total := TO_NUMBER() + sal;NUMBER转换为CHAR：使用TO_CHAR函数可以
35、实现数字到字符的转换，如：v_comm := TO_CHAR() |元;字符转换为日期：使用TO_DATE函数可以实现字符到日期的转换，如：v_date := TO_DATE(,);日期转换为字符使用TO_CHAR函数可以实现日期到字符的转换，如：v_to_day := TO_CHAR(SYSDATE, hh24:mi:ss) ;变量作用范围及可见性在PL/SQL编程中，如果在变量的定义上没有做到统一的话，可能会隐藏一些危险的错误，这样的原因主要是变量的作用范围所致。变量的作用域是指变量的有效作用范围，与其它高级语言类似，PL/SQL的变量作用范围特点是：变量的作用范围是在你所引用的程序单元（
36、块、子程序、包）内。即从声明变量开始到该块的结束。一个变量（标识）只能在你所引用的块内是可见的。当一个变量超出了作用范围，PL/SQL引擎就释放用来存放该变量的空间（因为它可能不用了）。在子块中重新定义该变量后，它的作用仅在该块内。例15：DECLAREEmesschar(80);BEGINDECLAREV1NUMBER(4);BEGINSELECTempnoINTOv1FROMempWHERELOWER(job)=president;(V1);EXCEPTIONWhenTOO_MANY_ROWSTHEN(Morethanonepresident);END;DECLAREV1NUMBER(4)
37、;BEGINSELECTempnoINTOv1FROMempWHERELOWER(job)=manager;EXCEPTIONWhenTOO_MANY_ROWSTHEN(Morethanonemanager);END;EXCEPTIONWhenothersTHENEmess:=substr(SQLERRM,1,80);(emess);END;注释在PL/SQL里，可以使用两种符号来写注释，即：使用双- (减号)加注释PL/SQL允许用来写注释，它的作用范围是只能在一行有效。如：V_SalNUMBER(12,2);-人员的工资变量。使用/*/来加一行或多行注释，如：/*/*文件名：*/*作 者：
38、EricHu*/*时 间：2011-5-9*/*/提示：被解释后存放在数据库中的PL/SQL程序，一般系统自动将程序头部的注释去掉。只有在PROCEDURE之后的注释才被保留；另外程序中的空行也自动被去掉。简单例子简单数据插入例子例16：/*/*文件名：*/*说明:一个简单的插入测试，无实际应用。*/*作者：EricHu*/*时间：2011-5-9*/*/DECLAREv_enameVARCHAR2(20):=Bill;v_salNUMBER(7,2):=;v_deptnoNUMBER(2):=10;v_empnoNUMBER(4):=8888;BEGININSERTINTOemp(empno,ename,JOB,sal,deptno,hiredate)VALUES(v_empno,v_ename,Manager,v_sal,v_deptno,TO_DATE(,);COMMIT;END;简单数据删除例子例17：/*/*文件名：*/*说明:简单的删除例子，不是实际应用。*/*作者：EricHu*/*时间：2011-5-9*/*/DECLAREv_enameVARCHAR2(20):=Bill;v_salNUMBER(7
