火龙果软件PLSQL经典教程

PL/SQL程序设计简介什么是PL/SQLPL/SQL是ProcedureLanguage&StructuredQueryLanguage（过程化SQL语言）的缩写PL/SQL是对SQL语言存储过程语言的扩展PL/SQL的优点它最大的优点是提供了一种服务器端存储过程语言

，可以对数据进行快速高效的处理。任何客户/服务器工具都能访问PL/SQL程序，具有很好的可重用性。可以使用ORACLE数据工具管理存储在服务器中的PL/SQL程序的安全性。可以授权或撤销数据库其他用户访问PL/SQL程序的能力。

对于SQL，ORACLE必须在同一时间处理每一条SQL语句，在网络环境下这就意味着每一个独立的调用都必须被oracle服务器处理，这就占用大量的服务器时间，同时导致网络拥挤。而PL/SQL是以整个语句块发给服务器，这就降低了网络拥挤。

PL/SQL块结构和组成元素PL/SQL程序由三个块组成，即声明部分、执行部分、异常处理部分。PL/SQL块PL/SQL块结构和组成元素PL/SQL块的结构如下：DECLARE/*声明部分:在此声明PL/SQL用到的变量,类型及游标，以及局部的存储过程和函数*/BEGIN/*执行部分:过程及SQL语句,即程序的主要部分*/EXCEPTION/*执行异常部分:错误处理*/END;其中执行部分是必须的。PL/SQL块结构和组成元素PL/SQL块可以分成三类：匿名块：动态构造，只能执行一次子程序：存储在数据库中的存储过程、函数及包等。当在数据库上建立好后可以在其它程序中调用它们触发器：当数据库发生操作时，会触发一些事件，从而自动执行相应的程序PL/SQL中的标识符PL/SQL程序设计中的标识符定义与SQL的标识符定义的要求相同标识符名不能超过30字符第一个字符必须为字母不能用’-‘(减号)不分大小写不能是SQL保留字PL/SQL中的标识符一般不要把变量名声明与表中字段名完全一样,如果这样可能得到不正确的结果例：DECLAREEnamevarchar2(20):=’KING’;BEGIN DELETEFROMempWHEREename=ename;END;PL/SQL中的标识符下面是建议使用的变量命名方法

标识符命名原则例程序变量

V_nameV_id程序常量

C_constantC_student_name游标变量

Name_cursorEmp_cursor异常标识

E_nameE_too_many_rows表类型

Name_table_typeEmp_record_type表

Name_tableEmp记录类型

Name_typeEmp_record参数P_nameP_id练习：给’KING’的工资涨10%PL/SQL中的变量类型类型说明CHAR(n)定长字符串,长度可选,缺省1。最大可达2000字节VARCHAR2(n)可变字符串,长度不能缺省,最大可达4000字节BINARY_INTEGER带符号整数,为整数计算优化性能NUMBER(p,s)数值，p代表刻度，s代表精度LONG变长字符串DATE日期BOOLEAN布尔ROWID存放数据库行号PL/SQL中声明变量在语句块的声明部分对变量声明，声明一个变量的语法是：variable_name[constant]type[notnull][:=value]其中：

variable_name为变量名type为类型

value为变量的初值PL/SQL中声明变量例：DECLARE V_DescriptionVARCHAR2(50); V_NumberNUMBER:=45; V_CounterBINARY_INTEGER:=0;注意：如果变量在声明时使用了NOTNULL选项则必须为变量指定初值。如果变量在声明时使用了CONSTANT选项则必须为变量指定初值， 并且该初值不能被改变。练习：查询员工号为7788的员工姓名，并显示在控制台。

PL/SQL中的复合类型

记录类型：记录类型是把逻辑相关的数据作为一个单元存储起来定义记录类型语法如下：TYPErecord_typeISRECORD(Field1type1[NOTNULL][:=exp1],Field2type2[NOTNULL][:=exp2],......Fieldntypen[NOTNULL][:=expn]);

练习1：创建一个图书记录类型（书号，书名），显示结果为： 书号：1100书名：VB.Net练习2：显示7788号员工的员工号，姓名，工资

PL/SQL中的复合类型

%TYPE:定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同 ，这时可以使用%TYPE

例：DECLARE--用%TYPE类型定义与表相配的字段

TYPEt_RecordISRECORD(T_noemp.empno%TYPE,T_nameemp.ename%TYPE,T_salemp.sal%TYPE);--声明接收数据的变量

v_empt_Record;

PL/SQL中的复合类型

%ROWTYPE:返回一个记录类型,其数据类型和数据库表的数据结构相 一致。

例：DECLARE v_empnoemp.empno%TYPE:=&no;recemp%ROWTYPE;BEGINSELECT*INTOrecFROMempWHEREempno=v_empno;DBMS_OUTPUT.PUT_LINE('姓名:'||rec.ename||'工资:'||rec.sal||'工作时间:'||rec.hiredate);END;PL/SQL表

PL/SQL表或者称为索引表(index-table)，是可以在PL/SQL程序中引用、能够模仿数组的非永久表。用户可以定义一个表类型，然后声明这种类型的变量。接下来，用户就可以将记录添加到用户的PL/SQL表中，并且采用与引用数组元素大体相同的方法引用他们

表包括两个基本成分： 数据处理类型为BINARY_INTEGER主键

标量或记录数据类型的列

PL/SQL表

定义一个表：TYPEtype_nameISTABLEOF {column_type|variable%TYPE|table.column%TYPE}[NOTNULL]|table%ROWTYPE INDEXBYBINARY_INTEGER;

例： DECLARE TYPEdept_table_typeISTABLEOF dept%ROWTYPEINDEXBYBINARY_INTEGER; my_dname_tabledept_table_type;

引用表元素

例：DECLARE typedept_table_typeistableof dept%ROWTYPEindexbybinary_integer; my_dname_tabledept_table_type;intnumber(3);BEGIN int:=-10; my_dname_table(int).dname:='searching'; dbms_output.put_line(my_dname_table(int).dname); int:=0; my_dname_table(int).dname:='market'; dbms_output.put_line(my_dname_table(int).dname); int:=10; my_dname_table(int).dname:='finance'; dbms_output.put_line(my_dname_table(int).dname);END;PL/SQL表

注意：

Index-by表中的元素不一定要按任何特定的顺序排序用于Index-by表的关键字没有必要是顺序的

关键字唯一允许的类型是BINARY_INTERGER练习：定义数组元素为部门表的记录的数组，保存10号，20号部门的信息并显示。PL/SQL中的可变数组

一般格式为：TYPEtype_nameISVARRAY(maximum_size)OFelement_type[NOTNULL]

其中：type_name是新可变长数组类型的类型名。 maximum_size是一个指定可变数组中元素最大数目的整数。element_type是一个PL/SQL标量、记录或对象类型。 NOTNULL子串表示集合中的每一个元素都必须有值。

一般格式为：DECLARE typenumberlistisvarray(10)ofnumber(5);typerecordlistisvarray(5)ofdept%rowtype;

引用数组元素

例：DECLARE typestringsisvarray(5)ofvarchar2(10);--Declareavarraywithfourelement v_liststrings:=strings('scott','peter','smith','tom');intnumber;BEGIN int:=1;dbms_output.put_line(v_list(int)); v_list(int):='urman';dbms_output.put_line(v_list(int)); int:=3;dbms_output.put_line(v_list(int)); v_list(int):='jackson'; dbms_output.put_line(v_list(int));v_list.extend;v_list(5):='oracle';dbms_output.put_line(v_list(5));END;PL/SQL表和可变数组的区别VARRAY集合中的元素的数量是有限的，Index_by则是没有限制的。Index_by表的下标可以有间隔，VARRAY集合的下标之间没有间隔。Index_by表不能存储在数据库中，但嵌套表和VARRAY可以被存储在数据库中。Index_by表初始化是自动进行的，VARRAY就必须使用内建的构造函数，构造函数和集合的名字相同。

表和数组属性

属性返回类型描述有效范围Exists(n)boolean指定索引为n的元素在集合中是否存在表、可变数组countnumber返回集合中元素的数目表、可变数组limitnumber返回集合中最大元素数可变数组First&lastBinary_integer返回第一个（最后一个）元素的索引表、可变数组Next&priorBinary_integer返回当前元素的下一个（前一个）元素的索引表、可变数组extendN/A向集合末尾添加一个元素可变数组trimN/A从集合的最后删除一个元素可变数组Delete(n)N/A删除元素下标为n的元素

表

表和数组属性

例：DECLARE typestrings_tableistableofvarchar2(10)indexbybinary_integer; stringsstrings_table; intnumber;BEGIN int:=1;strings(int):='element1';ifstrings.exists(int)thendbms_output.put_line(strings(int));elsedbms_output.put_line('nodata!');return;endif;

表和数组属性

例： strings(2):='element2'; strings(3):='element3';strings(4):='element4'; strings(5):='element5';strings(6):='element6';dbms_output.put_line(strings.count);dbms_output.put_line(strings.first);dbms_output.put_line(strings.last);dbms_output.put_line(strings.next(2));dbms_output.put_line(strings.prior(4));strings.delete(1,3);--dbms_output.put_line(strings(2));END;PL/SQL运算符和表达式

关系运算符

运算符意义=等于<>,!=,~=,^=不等于<小于>大于<=小于等于>=大于等于PL/SQL运算符和表达式

一般运算符运算符意义+加号-减号*乘号/除号:=赋值号||连接符..范围运算符PL/SQL运算符和表达式

逻辑运算符运算符意义ISNULL空值BETWEENAND介于两者之间IN在一个值列表之中AND逻辑与

OR逻辑或

NOT取返,如ISNOTNULL,NOTIN

PL/SQL中的变量赋值在PL/SQL编程中，变量赋值是一个值得注意的地方，它的语法如下：

variable:=expression;variable是一个PL/SQL变量,expression是一个PL/SQL表达式.BOOLEAN型变量赋值：

布尔值只有TRUE,FALSE及NULL三个值。空值加数字仍是空值：NULL+<数字>=NULL。空值加（连接）字符，结果为字符：NULL||<字符串>=<字符串>PL/SQL中的变量赋值例：DECLARE doneBOOLEAN; /*thefollowingstatementsarelegal:*/BEGINdone:=FALSE;WHILENOTdoneLOOP Null;ENDLOOP;END;PL/SQL中的变量作用范围及可见性

PL/SQL的变量作用范围特点是：变量的作用范围是在所引用的程序单元（块、子程序、包）内。即从声明变量开始到该块的结束一个变量（标识）只能在所引用的块内是可见的当一个变量超出了作用范围，PL/SQL引擎就释放用来存放该变量的空间（因为它可能不用了）在子块中重新定义该变量后，它的作用仅在该块内PL/SQL中的变量作用范围及可见性

例：

DECLARE v_NumberNUMBER(3,2); BEGIN DECLARE v_CharacterVARCHAR2(10); BEGIN ………… END; END;v_Number的作用域v_Character的作用域PL/SQL中的注释在PL/SQL里，可以使用两种符号来写注释：PL/SQL允许用双–来写注释，它的作用范围是只能在一行有效。

例： V_SalNUMBER(12,2);--工资变量使用/**/来加一行或多行注释。 例：/***********************************************//*文件名：department_salary.sql*//**********