PLSQL经典教程

1-1PL/SQL程序设计简介1-2什么是PL/SQLPL/SQL是ProcedureLanguage&StructuredQueryLanguage（过程化SQL语言）的缩写PL/SQL是对SQL语言存储过程语言的扩展1-3PL/SQL的优点它最大的优点是提供了一种服务器端存储过程语言，可以对数据进行快速高效的处理。任何客户/服务器工具都能访问PL/SQL程序，具有很好的可重用性。可以使用ORACLE数据工具管理存储在服务器中的PL/SQL程序的安全性。可以授权或撤销数据库其他用户访问PL/SQL程序的能力。对于SQL，ORACLE必须在同一时间处理每一条SQL语句，在网络环境下这就意味着每一个独立的调用都必须被oracle服务器处理，这就占用大量的服务器时间，同时导致网络拥挤。而PL/SQL是以整个语句块发给服务器，这就降低了网络拥挤。1-4PL/SQL块结构和组成元素PL/SQL程序由三个块组成，即声明部分、执行部分、异常处理部分。PL/SQL块1-5PL/SQL块结构和组成元素PL/SQL块的结构如下：DECLARE/*声明部分:在此声明PL/SQL用到的变量,类型及游标，以及局部的存储过程和函数*/BEGIN/*执行部分:过程及SQL语句,即程序的主要部分*/EXCEPTION/*执行异常部分:错误处理*/END;其中执行部分是必须的。1-6PL/SQL块结构和组成元素PL/SQL块可以分成三类：匿名块：动态构造，只能执行一次子程序：存储在数据库中的存储过程、函数及包等。当在数据库上建立好后可以在其它程序中调用它们触发器：当数据库发生操作时，会触发一些事件，从而自动执行相应的程序1-7PL/SQL中的标识符PL/SQL程序设计中的标识符定义与SQL的标识符定义的要求相同标识符名不能超过30字符第一个字符必须为字母不能用’-‘(减号)不分大小写不能是SQL保留字1-8PL/SQL中的标识符一般不要把变量名声明与表中字段名完全一样,如果这样可能得到不正确的结果例：DECLAREEnamevarchar2(20):=’KING’;BEGINDELETEFROMempWHEREename=ename;END;1-9PL/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%1-10PL/SQL中的变量类型类型说明CHAR(n)定长字符串,长度可选,缺省1。最大可达2000字节VARCHAR2(n)可变字符串,长度不能缺省,最大可达4000字节BINARY_INTEGER带符号整数,为整数计算优化性能NUMBER(p,s)数值，p代表刻度，s代表精度LONG变长字符串DATE日期BOOLEAN布尔ROWID存放数据库行号1-11PL/SQL中声明变量在语句块的声明部分对变量声明，声明一个变量的语法是：variable_name[constant]type[notnull][:=value]其中：variable_name为变量名type为类型value为变量的初值1-12PL/SQL中声明变量例：DECLAREV_DescriptionVARCHAR2(50);V_NumberNUMBER:=45;V_CounterBINARY_INTEGER:=0;注意：如果变量在声明时使用了NOTNULL选项则必须为变量指定初值。如果变量在声明时使用了CONSTANT选项则必须为变量指定初值，并且该初值不能被改变。练习：查询员工号为7788的员工姓名，并显示在控制台。1-13PL/SQL中的复合类型记录类型：记录类型是把逻辑相关的数据作为一个单元存储起来定义记录类型语法如下：TYPErecord_typeISRECORD(Field1type1[NOTNULL][:=exp1],Field2type2[NOTNULL][:=exp2],......Fieldntypen[NOTNULL][:=expn]);练习1：创建一个图书记录类型（书号，书名），显示结果为：书号：1100书名：VB.Net练习2：显示7788号员工的员工号，姓名，工资1-14PL/SQL中的复合类型%TYPE:定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同，这时可以使用%TYPE例：DECLARE--用%TYPE类型定义与表相配的字段TYPEt_RecordISRECORD(T_noemp.empno%TYPE,T_nameemp.ename%TYPE,T_salemp.sal%TYPE);--声明接收数据的变量v_empt_Record;1-15PL/SQL中的复合类型%ROWTYPE:返回一个记录类型,其数据类型和数据库表的数据结构相一致。例：DECLAREv_empnoemp.empno%TYPE:=&no;recemp%ROWTYPE;BEGINSELECT*INTOrecFROMempWHEREempno=v_empno;DBMS_OUTPUT.PUT_LINE('姓名:'||rec.ename||'工资:'||rec.sal||'工作时间:'||rec.hiredate);END;1-16PL/SQL表PL/SQL表或者称为索引表(index-table)，是可以在PL/SQL程序中引用、能够模仿数组的非永久表。用户可以定义一个表类型，然后声明这种类型的变量。接下来，用户就可以将记录添加到用户的PL/SQL表中，并且采用与引用数组元素大体相同的方法引用他们表包括两个基本成分：数据处理类型为BINARY_INTEGER主键标量或记录数据类型的列1-17PL/SQL表定义一个表：TYPEtype_nameISTABLEOF{column_type|variable%TYPE|table.column%TYPE}[NOTNULL]|table%ROWTYPEINDEXBYBINARY_INTEGER;例：DECLARETYPEdept_table_typeISTABLEOFdept%ROWTYPEINDEXBYBINARY_INTEGER;my_dname_tabledept_table_type;1-18引用表元素例：DECLAREtypedept_table_typeistableofdept%ROWTYPEindexbybinary_integer;my_dname_tabledept_table_type;intnumber(3);BEGINint:=-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;1-19PL/SQL表注意：Index-by表中的元素不一定要按任何特定的顺序排序用于Index-by表的关键字没有必要是顺序的关键字唯一允许的类型是BINARY_INTERGER练习：定义数组元素为部门表的记录的数组，保存10号，20号部门的信息并显示。1-20PL/SQL中的可变数组一般格式为：TYPEtype_nameISVARRAY(maximum_size)OFelement_type[NOTNULL]其中：type_name是新可变长数组类型的类型名。maximum_size是一个指定可变数组中元素最大数目的整数。element_type是一个PL/SQL标量、记录或对象类型。NOTNULL子串表示集合中的每一个元素都必须有值。一般格式为：DECLAREtypenumberlistisvarray(10)ofnumber(5);typerecordlistisvarray(5)ofdept%rowtype;1-21引用数组元素例：DECLAREtypestringsisvarray(5)ofvarchar2(10);--Declareavarraywithfourelementv_liststrings:=strings('scott','peter','smith','tom');intnumber;BEGINint:=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;1-22PL/SQL表和可变数组的区别•VARRAY集合中的元素的数量是有限的，Index_by则是没有限制的。•Index_by表的下标可以有间隔，VARRAY集合的下标之间没有间隔。•Index_by表不能存储在数据库中，但嵌套表和VARRAY可以被存储在数据库中。•Index_by表初始化是自动进行的，VARRAY就必须使用内建的构造函数，构造函数和集合的名字相同。1-23表和数组属性属性返回类型描述有效范围Exists(n)boolean指定索引为n的元素在集合中是否存在表、可变数组countnumber返回集合中元素的数目表、可变数组limitnumber返回集合中最大元素数可变数组First&lastBinary_integer返回第一个（最后一个）元素的索引表、可变数组Next&priorBinary_integer返回当前元素的下一个（前一个）元素的索引表、可变数组extendN/A向集合末尾添加一个元素可变数组trimN/A从集合的最后删除一个元素可变数组Delete(n)N/A删除元素下标为n的元素表1-24表和数组属性例：DECLAREtypestrings_tableistableofvarchar2(10)indexbybinary_integer;stringsstrings_table;intnumber;BEGINint:=1;strings(int):='element1';ifstrings.exists(int)thendbms_output.put_line(strings(int));elsedbms_output.put_line('nodata!');return;endif;1-25表和数组属性例：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(