大型数据库技术：PL SQL基础

PL/SQL基础概述PL/SQL（ProcedureLanguageandSQL）是Oracle对SQL的一种扩充，集成了程序化设计语言中的许多特性PL/SQL的特点过程化和模块化：将逻辑上相关的语句组织在一个PL/SQL程序块中使用过程化语言的控制结构：使用了控制语句，以及游标的显式处理错误处理：使用EXCEPTION处理语句处理异常可移植性：程序可移植到任何支持Oracle和PL/SQL的主机环境中集成：通过PL/SQL可以将前台和后台技术结合起来改善性能：PL/SQL可以改善一个应用的性能、减少网络的流量概述支持所有的事务控制命令支持所有SQL的DML命令支持所有SQL的DDL命令支持所有SQL的DCL命令支持所有SQL的数据类型、函数、各种运算符PL/SQL程序块可以存储在服务器中，被其他程序或SQL命令调用对PL/SQL程序块可以进行权限管理

PL/SQL中的Select语句PL/SQL的SELECTSELECT列名，列名...INTO

变量1，变量2...FROM表WHERE条件...;在使用SELECT…INTO…时，结果只能有一条，如果返回了多条数据或没有数据，则将产生错误。（对于多条记录的遍历，可以使用游标）PL/SQL程序结构PL/SQL程序的单元由逻辑块（BLOCK）组成块可以顺序出现，也可以相互嵌套每一个块分成三部分[DECLEAR] --说明部分（可选的）BEGIN --语句执行部分（必需的）[EXCEPTION] --出错处理程序（可选的）END;/PL/SQL类型程序块描述无名块嵌入在应用内或交互式发出的无名块有名块（应用的过程、函数）可以接受参数，并返回结果的有名块，存储在应用程序中。可以被反复调用。存储过程、函数可以接受参数，并返回结果的有名块，存储在服务器端。可以被反复调用。包有名的PL/SQL模块，是相关的过程、函数、标识符的集合。存储在服务器端。可以被反复调用。数据库触发器与数据库表相关的PL/SQL块，存储在服务器端。在客户与服务器端触发事件发生时自动触发。应用触发器与一个应用事件相关的PL/SQL块，存储在服务器端。在应用程序的触发事件时自动触发。变量定义CONSTANT表示标识符是一个常量，常量必须赋初值NOTNULL表示变量必须赋初值没有赋初值的变量，初值都是NULLV_num number(2) :=12;C_zero_value constantnumber :=0;V_char varchar2(10) :=‘asasasa’;V_date date :=’05-jul-99’;V_bool

boolean :=false;V_str varchar2(20);变量[CONSTANT]数据类型[NOTNULL][:=DEFAULTPL/SQL表达式];变量类型%TYPE和%ROWTYPE类型%TYPE：表示已经定义的变量类型定义%ROWTYPE：表示已经定义的表、视图中的纪录的类型或游标的结构类型优点不必了解数据库中列的个数和数据类型如果表结构改变，PL/SQL程序可以不变，减少程序的维护工作BOOKPRINT NUMBER;V_BOOK BOOKPRINT%TYPE;V_EMPNO EMP.EMPNO%TYPE;/*EMP表名，EMPNO列名*/V_REC EMP%ROWTYPE;/*EMP可以是表名或游标名*/自定义类型根据用户的需要，用现有的PL/SQL标量类型组合成一个用户自定义的类型createorreplacetypestudent_typeasobject( id number(5),

firstname varchar2(20),

lastname varchar2(20), major varchar2(30),

current_credits number(3));

v_stu

student_type;变量赋值变量名:=常量或PL/SQL表达式可在说明部分赋值，也可在执行部分赋值DECLEAR

num_var number(5):=5;/*说明部分赋值*/

v_emp

emp%rowtype;BEGIN

v_emp.empno:=11011;

/*执行部分赋值*/ ……

selectsal

intonum_varfromempwhereempno=7788;变量作用范围变量如果不在子块中重新定义，则在PL/SQL块的所有子块中有效如果变量在子块内重新定义，子块内定义的变量优先，此变量的作用范围仅在本子块内有效。如果主块中的变量A和子块变量A中同时定义时，在子块中要用主块的变量A时，必须在变量前加块的标识符（如：块的标识符.A)。常用内置函数函数说明转换前的类型TO_CHAR转换成VARCHAR类型数字型、日期型TO_DATE转换成DATE字符型TO_NUMBER转换成number类型字符型SELECTTO_CHAR(current_credits)INTOv_1FROMstudent2WHEREid=1002IF语句DECLARE

v_sal

emp.sal%type;BEGIN SELECTsalINTOv_salFROMempWHEREempno=7788; IFv_sal<500THEN UPDATEempSETsal=sal*1.5WHEREempno=7788;

ELSIF

v_sal<1500THEN UPDATEempSETsal=sal*1.3WHEREempno=7788; ELSE UPDATEempSETsal=sal*1.0WHEREempno=7788; ENDIF; COMMIT;END;/<条件>是一个布尔型变量或表达式，取值只能是TRUE/FALSE/NULLCASE语句CASE WHENgrade=‘A’THENdbms_output.put_line(‘excellent’); WHENgrade=‘B’THENdbms_output.put_line(‘verygood’); WHENgrade=‘C’THENdbms_output.put_line(‘good’); WHENgrade=‘D’THENdbms_output.put_line(‘fair’); WHENgrade=‘F’THENdbms_output.put_line(‘poor); ELSEdbms_output.put_line(‘nosuchgrade’);ENDCASE;LOOP语句DECLARE

v_counternumber:=1;BEGIN LOOP

INSERTINTOtemp_tableVALUES(v_counter,’loopindex’);

v_counter:=v_counter+1; IFv_counter>50THEN exit ENDIF; ENDLOOP;END;/WHILE语句DECLARE

v_counternumber:=1;BEGIN WHILEv_counter<=50LOOP

INSERTINTOtemp_tableVALUES(v_counter,‘loop_index’);

v_counter:=v_counter+1; ENDLOOP;END;/FOR语句DECLARE

v_counternumber:=1;BEGIN FORv_counterIN1..50LOOP

INSERTINTOtemp_tableVALUES(v_counter,’loopindex’); ENDLOOP;END;/ FORv_counterINREVERSE1..50LOOPGOTO语句DECLARE

v_vonternumber:=1;BEGIN LOOP INSERTINTOtemp_tableVALUES(v_counter,‘loopcount’);

v_counter:=v_counter+1; IFv_counter>50THEN

GOTOENDOFLOOP; ENDIF; ENDLOOP;

<<ENDOFLOOP>> INSERTINTOtemp_table(char_col)VALUES(‘done!’);END;GOTO语句对于块、循环或IF语句而言，想要从外层跳到内层是非法的从一个IF子句调转到IF的另一个子句中是非法的出错处理块不能实行跳转BEGINGOTO innerblock;BEGIN<<innerblock>>...END;GOTOinsideifIFx>3THEN...<<insideif>>INSERTINTO...ENDIF;END;DECLARE

v_room

room%rowtype;BEGINSELECT*INTOv_roomFROMroomsWHERErowid=1;<<insert>>INSERTINTOtemp_table(char_col)VALUES(‘foundarow’);EXCEPTIONWHENno_data_foundTHENGOTOinsert;END;异常处理一个PL/SQL块的出错处理（EXCEPTION）部分包含程序处理多个错误的代码。当一个错误发生时，程序控制离开PL/SQL块的执行部分转移到出错处理部分...BEGIN...EXCEPTION WHEN错误1[OR错误2]THEN语句序列1; WHEN错误3[OR错误4]THEN语句序列2; ... WHENOTHERSTHEN语句序列3;END;异常处理错误描述处理方法系统预定义错误在PL/SQL中经常出现的25个系统定义的错误不必定义，允许服务器隐式地触发它们，只需要在出错处理部分处理它们用户自定义错误开发者认为是非正常的一个条件必须在说明部分定义，在执行部分显式触发它们，在在出错处理部分处理它们异常代码异常名称说明ORA-01403NO_DATA_FOUND查询没有返回数据ORA-01422TOO_MANY_ROWSSELECT…INTO语句返回多行结果ORA-00001DUP_VAL_ON_INDEX试图更新或插入重复记录ORA-01001INVALID_CURSOR非法游表操作ORA-01722INVALID_NUMBER字符串向数字转换失败ORA-06502VALUE_ERROR出现数字、数据转换、字符串或限制型错误ORA-01476ZERO_DIVIDE被零除异常处理DECLARE

v_comm

m%type;BEGIN SELECT comm INTO v_comm FROM emp WHERE empno=7788;EXCEPTION WHENno_data_foundTHEN

dbms_output.put_line(‘nodata!’); WHENtoo_many_rowsTHEN

dbms_output.put_line(‘toomanyrow!’); WHENothersTHEN

null;END;自定义异常用户自定义的错误是程序定义的一个错误，并不一定是Oracle错误，它可能是与业务相关的一个错误，比如违反了业务规则处理用户自定义的错误有三个步骤：定义错误、触发错误、处理错误。DECLARE

e_toomanystudent exception;

/*定义错误*/

v_currentstudent number(3);

v_maxstudent number(3);

v_errorcode number;

v_errortext varchar2(200);BEGIN SELECTcurrent_student,max_studentsINTO

v_currentstudent,v_maxstudentFROMclasse WHEREdepartment=‘HIS’andcourse=101;IFv_currentstudent>v_maxstudentTHEN

RAISE e_toomanystudent

;

/*触发错误*/ENDIF;EXCEPTIONWHENno_data_foundortoo_many_rowsTHEN

dbms_output.putline(‘发生系统预定义错误’);WHENe_toomanystudentTHEN /*处理错误*/ INSERTINTOlog_table(info) VALUES(‘history101has’||v_currentstudent);WHENothersTHEN

v_errorcode=sqlcode;

v_errortext=substr(sqlerrm,1,200) INSERTINTOlog_table(code,message,info) VALUES(v_errorcode,v_errortext,’Oracleerroroccured’);END;/课堂作业查找classes表中信息系102课程的当前学生数与最大学生数，比较这两个数，如果当前学生数超过最大学生数是不允许的，就触发错误。当这种错误发生时，向表log_table中插入一条纪录，该条纪录记载出错信息和代码declare

v_num number；

e_toomany exception；begin selectcount(1)intov_numfromclasseswheredeptname=‘信息系’andclassid=102； ifv_num>MAX_NUMthen raise e_toomany; endif;exception whene_toomanythen INSERTINTOlog_table(code,message,info) VALUES(sqlcode,substr(sqlerrm,1,200),Toomanystudents!’);end;/课堂作业现有一个公司的部门及雇员的数据库，由下面三个表组成完成显示薪金最高的3位员工(SQL或者PL/SQL)求和其部门经理住址相同的员工的姓名及经理姓名删除部门工作年限为0的工作记录，若删除后有雇员没有在任何部门工作，删除其雇员记录Employee（empid，empname，age，address，sal）Department（deptno，deptname，mgr）Work（empid，deptno，worktime）游标游标是一个指向内存区域的指针，一个PL/SQL结构游标有两种类型：显式游标和隐式游标显式游标：是由程序员定义和命名的，并且在块的执行部分中通过特定语句操纵的内存工作区隐式游标：是由PL/SQL为DML语句和SELECT语句隐式定义的工作区ActivesetCurrentrowCursor7369 SMITH CLERK7566 JONES MANAGER7788 SCOTT ANALYST7876 ADAMS CLERK7902 FORD ANALYST显式游标的处理步骤定义游标打开游标将当前行结果提取到变量中判断数据行是否读取完关闭游标DECLAREOPENFETCHEMPTY?NoCLOSEYes如果没有读取完，就读取当前行显式游标的处理步骤Openthecursor.CursorPointerFetcharowfromthecursor.CursorPointerContinueuntilempty.CursorPointerClosethecursor.显式游标的处理游标是一个不含INTO子句的SELECT语句。SELECT语句允许带WHERE,ORDERBY,GROUPBY等子句DECLARECURSORemp_cursorISSELECTempno,ename FROMemp;CURSORdept_cursorISSELECT*FROMdeptWHEREdeptno=10;BEGIN...CURSOR游标名ISselect语句;显式游标的处理执行游标所对应的SELECT语句，将其查询结果放入工作区，并且指针指向工作区首部将当前行的数据存入到变量中变量数量和行纪录的数据项数量要相同，相应数据类型一致判断游标中是否还有行纪录OPEN游标名;FETCH

游标名

INTO

[变量１,变量２,...]|纪录名];显式游标的处理FETCHemp_cursorINTOv_empno,v_ename;DECLARE CURSORc_classesISSELECT*FROMclass;

v_classes

c_classes%rowtype;BEGIN ...OPENc_classes;FETCHc_classesINTOv_classes;...END;/显式游标的处理完成对行数据处理后，可以关闭游标。如果需要，还可以再次打开游标。关闭游标后，系统释放与该游标关联的资源，不能再对游标进行FETCH操作。CLOSE 游标名;CLOSE c_classes;显式游标的属性属性类型描述%ISOPENBoolean如果游标已打开，取值为TRUE，否则取值为FALSE%NOTFOUNDBoolean如果最近一次FETCH操作没有返回结果，则取值为TRUE,否则为FALSE%FOUNDBoolean如果最近一次FETCH操作没有返回结果，则取值为FALSE,否则为TRUE%ROWCOUNTNumber值是当前为止返回的纪录数，初值为0，每取一条纪录，该属性值加1显式游标的属性...IFemp_cursor%ISOPENTHEN FETCHemp_cursorINTOv_ename,v_sal;ELSE OPENemp_cursor;ENDIF;......LOOPFETCHemp_cursorINTOv_ename,v_sal;EXITWHENemp_cursor%ROWCOUNT>5oremp_cursor%NOTFOUND;...ENDLOOP;...显式游标的属性DECLARE

v_deptno

emp.deptno%type:=&p_deptno;

v_ename

emp.ename%type;

v_sal

emp.sal%type;

CURSOR

emp_cursorISSELECTename,salFROMemp WHEREdeptno=v_deptno;BEGIN

OPEN

emp_cursor;LOOP

FETCH

emp_cursorINTOv_ename,v_sal;EXITWHENemp_cursor%NOTFOUND;INSERTINTOtemp(ename,sal)VALUES(v_ename,v_sal);ENDLOOP;

CLOSE

emp_cursor;COMMIT;END;/游标的FOR循环使用游标的FOR循环，可以简化游标的操作步骤游标的FOR循环隐式（自动）地完成三个步骤：打开游标；（FETCH）取数据；关闭游标纪录名是系统隐式定义的游标名%ROWTYPE类型的纪录变量，不必事先定义FOR纪录名IN游标名LOOP

语句1；

语句2； ...ENDLOOP;游标的FOR循环DECLARE

v_deptno

emp.deptno%type:=&p_deptno;CURSORemp_cursorISSELECTename,salFROMemp WHEREdeptno=v_deptno;BEGINFORemp_recordINemp_cursorLOOPINSERTINTOtemp(ename,sal)VALUES(emp_record.ename,emp_record.sal);ENDLOOP;COMMIT;/*在本段程序中，没有打开游标、取数据、关闭游标语句*/END;/通过游标操纵数据库当用户从一张表或多张表中查询多条纪录时，必须使用一个显式游标要操纵数据库中数据，在定义游标的查询语句时，必须加上FORUPDATEOF从句,表示要对表加锁表加锁后，在UPDATE或DELETE语句中，加WHERECURRENTOF子句，即可以对锁定的数据进行修改CURSOR游标名

ISSELECT列1，列2…FORM表WHERE条件

FORUPDATE[OFcolumn][NOWAIT];通过游标操纵数据库带WHERECURRENTOF从句的UPDATE语句和DELETE语句DELETEFROM表WHERECURRENTOF

游标名;UPDATE表SET列1=值1，列2=值2...WHERECURRENTOF

游标名；通过游标操纵数据库DECLARE

v_deptno

emp.deptno%type:=&p_deptno;

v_empno

emp.empno%type;

v_job

emp.job%type;

v_sal

emp.sal%type;CURSORemp_cursorISSELECTename,job,salFROMemp WHEREdeptno=v_deptnoFORUPDATEOFsal;BEGINFORemp_recordINemp_cursorLOOPIFemp_record.sal<800THENUPDATEempSETsal=800WHERECURRENTOFemp_cursor;ENDIF;

dbms_output.put_line(emp_record.empno||emp_record.sal);ENDLOOP;COMMIT;END;查询emp表某部门的雇员情况，如果某雇员的工资小于800，则将其工资改成800DECLARE

emp_numnumber:=0;s_sal

emp.sal%type;

e_sal

emp.sal%type;e_empno

emp.empno%type;CURSORc1ISSELECTempno,salFROMempORDERBYsal

FORUPDATEOFsal;BEGINOPENc1;SELECTsum(sal)INTOs_salFROMemp;WHILEs_sal<500000LOOPFETCHc1INTOe_empno,e_sal;EXITWHENc1%NOTFOUND;UPDATEempSETsal=sal*1.1WHERECURRENTOFc1;

s_sal:=s_sal+e_sal*0.1;

emp_num:=emp_num+1;ENDLOOP;CLOSEc1;INSERTINTOmsgVALUES(emp_num,s_sal);COMMIT;END;为职工增加10％的工资，从最低工资开始长，增加后工资总额限制在50万以内带参数的游标在定义参数时，只定义参数类型，不定义长度此参数只能在游标的查询语句中使用只能向游标传递参数数值，不能通过参数带出结果可以有选择地给参数提供一个默认值CURSOR游标名(参数1数据类型[{:=|DEFAULT}值]

[,参数2数据类型[{:=|DEFAULT]值]...]ISSELECT语句;带参数的游标参数可以是具体的值，也可以是一个已经赋值的变量不同的参数值，返回不同的结果OPEN游标名(&参数1,&参数2...);DECLARECURSORemp_cursor

(p_deptnoNUMBER,p_jobVARCHAR2)ISSELECT empno,ename FROM empWHERE deptno=p_deptno

AND job=p_job;BEGINOPENemp_cursor(10,'CLERK');...DECLARECURSORdept_cursorISSELECTdeptnoFROMdept;

v_ename

emp.ename%type;

v_sal

emp.sal%type;CURSORemp_cursor(v_deptnonumber)ISSELECTename,salFROMempWHEREdeptno=v_deptno;BEGINFORdept_recordINdept_cursorLOOPEXITWHENdept_cursor%NOTFOUND;OPENemp_cursor(dept_record.deptno);LOOPFETCHemp_cursorINTOv_ename,v_sal;EXITWHENemp_cursor%NOTFOUND;

dbms_output.put_line(dept_record.deptno||’’||’’||v_sal);ENDLOOP;CLOSEemp_cursor;ENDLOOP;COMMIT;END;首先查询DEPT表取出所有的部门号，然后根据DEPT表中返回的每一个部门号，从emp表查询该部门的雇员信息隐式游标隐式游标的特点由系统定义，不需要用户定义用来处理INSERT,UPDATE,DELETE和单行的SELECT…INTO语句游标名为SQL游标的属性存储有关最近一次SQL命令的状态信息与显式游标的属性类似，也有四个属性含义和类型与显式游标的属性一致隐式游标属性属性值DELETEUPDATEINSERTSELECTSQL%FOUNDTRUE成功成功成功返回一行SQL%FOUNDFALSE失败失败失败没结果SQL%NOTFOUNDTRUE失败失败失败没结果SQL%NOTFOUNDFALSE成功成功成功返回一行SQL%ROWCOUNT行数删除的行数修改的行数插入的行数成功1,失败0SQL%ISOPENFALSEFALSEFALSEFALSEFALSEBEGINUPDATEempSETsal=10000,deptno=30WHEREempno=9998；

IFSQL%NOTFOUNDTHEN

INSERTINTOemp(empno,sal,deptno)VALUES(9998,10000,30);ENDIF;COMMIT;END;隐式游标VS.显式游标隐式游标显式游标当执行插入、更新、删除及查询只有一条纪录时，由PL/SQL内部管理，自动打开和关闭游标在程序中显式地定义、打开和关闭。游标有一个名字游标的属性前缀是SQL游标的属性前缀是游标名游标属性%ISOPEN总是FALSE，因为当语句执行完后立即关闭隐式游标%OPEN属性有一个有效值，依赖游标状态SELECT…INTO语句只能执行一行可以处理任何行课堂练习通过带参数游标，完成下述功能：通过查询学生成绩表Score取出成绩不及格的学生学号，然后根据不及格学生学号，从学生档案表Student中查询该学生的信息存储过程ORREPLACE是一个可选的关键字，表示替代原有的过程IS或AS后面是一个完整的PL/SQL块的三部分（说明部分、执行部分、异常处理部分）IN表示输入变量，OUT表示输出变量，INOUT表示输入输出变量,缺省表示INCREATE[ORREPLACE]PROCEDURE过程名

[(参数名[IN|OUT|INOUT]数据类型,...)]{IS|AS} [说明部分]BEGIN

语句序列

[EXCEPTION出错处理]END[过程名];CREATEORREPLACEPROCEDUREmodetest(

p_inparaIN number,

p_outpareOUTnumber,

p_inoutparaINOUTnumber)IS

v_localnumber;BEGIN

v_local :=p_inpara;/*

IN类型参数不能出现在:=的左边*/

p_outpara :=7;/*OUT类型参数不能出现在:=的右边*/

v_local :=p_inoutpara;

p_inoutpara :=7;END;CREATEPROCEDUREraise_salary(emp_id

interger,v_increaseinteger)IS

/*缺省IN*/BEGINUPDATEempSETsal=sal+v_increase

WHEREempno=emp_id;COMMIT;END;存储过程如果INOUT参数的值在过程中没有被更改，则它返回到调用环境时值不变如果INOUT参数是字符型，不能指定长度。长度由调用环境决定CREATEORREPLACEPROCEDUREquery_emp(

v_emp_noINemp.empno%TYPE,

v_emp_nameOUTemp.ename%TYPE,

v_emp_salOUTemp.sal%TYPE,

v_emp_commOUTm%TYPE)ISBEGINSELECTename,sal,commINTOv_emp_name,v_emp_sal,v_emp_comm

FROMempWHEREempno=v_emp_no;ENDquery_emp;函数参数都是IN类型，存储函数必须返回并且只返回一个结果函数体的可执行部分必须有RETURN语句（RETURN表达式）表达式的数值类型与RETURN子句中定义要一致CREATE[ORREPLACE]FUNCTION函数名

[(参数名[IN]数据类型,...)]RETURN数据类型{IS|AS} [说明部分]BEGIN

语句序列；

RETURN（表达式）；[EXCEPTION出错处理]END[函数名];CREATEORREPLACEFUNCTIONget_sal(p_emp_noINempno%TYPE)RETURNNUMBERIS

v_emp_sal

emp.sal%TYPE:=0;BEGIN SELECTsalINTOv_emp_salFROMempWHEREempno=p_emp_no；

RETURN(v_emp_sal）；EXCEPTIONWHENno_data_foundortoo_many_rowsTHEN

dbms_output.put_line(‘SystemError’)；

WHENothersTHEN

dbms_output.put_line(sqlerrm);ENDget_sal;/函数CREATEORREPLACEFUNCTIONaverage_sal(v_nINnumber(3))RETURNNUMBERIS CURSORc_empISSELECTempno,salFROMemp;

v_total_sal

emp.sal%TYPE;

v_counternumber;

v_emp_no

emp.empno%TYPE;BEGINFORr_empINc_empLOOPEXITWHENc_emp%ROWCOUNT>v_nORc_emp%NOTFOUND;

v_total_sal:=v_total_sal+r_emp.sal;

v_counter:=c_emp%ROWCOUNT;

v_emp_no:=r_emp.empno;

dbms_output.putline(‘loop=’||v_counter||’;empno=’||v_emp_no);ENDLOOP;RETURN(v_total_sal/v_counter);ENDaverage_sal;/过程VS.函数参数类型不同：函数只有IN类型参数，而存储过程有IN、OUT、INOUT三个类型参数返回值的方法不同：函数返回只有一个值，而存储过程返回值由OUT参数带出来调用方法不同：过程名（实际参数１，实际参数２…）；变量名:＝函数名（实际参数１，实际参数２…）；CREATEORREPLACEPROCEDUREfire_emp(

p_emp_noINemp.empno%TYPE)IS

invalid_employeeEXCEPTION; /*定义错误*/BEGINDELETEFROMempWHEREempno=p_emp_no;IFSQL%NOTFOUNDTHENRAISEinvalid_employee;/*触发错误*/ENDIF;EXCEPTIONWHENinvalid_employeeTHENROLLBACK; INSERTINTOexception_table(line_nr,line) VALUES(1,’employeedoesnotexist.’);WHENothersTHEN

dbms_output.putline(sqlerrm);ENDfire_emp;/过程/函数中的异常处理在SQL*Plus中使用过程/函数在SQL*Plus中，用VARIABLE定义的变量在引用时，必须前面加冒号（:）。用ACCEPT接受的变量在引用时，前面加&符号SETSERVEROUTPUTONACCEPT

p_emp_noPROMPT‘pleaseentertheemployeenumber:’VARIABLE

v_emp_namevarchar2(14);VARIABLEv_emp_salnumber;VARIABLEv_emp_commnumber;EXECUTEquery_emp(&p_emp_no,:v_emp_name,:v_emp_sal,:v_emp_comm);EXECUTEdbms_output.putline(‘Infomationforemployee:’||to_char(&p_emp_no));EXECUTEdbms_output.putline(‘Thenameis:’||:v_emp_name);EXECUTEdbms_output.putline(‘Thesalaryis:’||to_char(:v_emp_sal));EXECUTEdbms_output.putline(Thecommissionis:’||to_char(:v_emp_comm));Package包是一个可以将相关对象存储在一起的PL/SQL结构它包含了两个分离的组成部分：包说明和包主体包的组成可以将相关的若干程序单元组织到一块，用一个包名来标识这个集合。程序单元描述过程（procedure）带有参数的程序函数（function）带有参数的程序,该程序只返回一个值变量（variable）用于存储变化值的存储单元游标（cursor）指向一个SQL语句的指针类型（type）定义一个结构类型常量（constant）指向常数的指针出错情况（exception）标志一个出错情况包中可以包含的程序单元CREATE[ORREPLACE]PACKAGE包名｛IS|AS｝公共变量的定义 |

公共类型的定义 |

公共出错处理的定义 |

公共游标的定义 |

函数说明 |

过程说明END;/CREATEPACKAGEsal_packageISPROCEDUREraise_sal(v_empno

emp.empno%TYPE,

v_sal_increment

emp.sal%TYPE);PROCEDUREreduce_sal(v_empno

emp.empno%TYPE,

v_sal_reduce

emp.sal%TYPE);

v_raise_sal

emp.sal%TYPE:=0;

v_reduce_sal

emp.sal%TYPE:=0;END;/PackageCREATE[ORREPLACE]PACKAGEBODY包名｛IS|AS｝私有变量的定义 |

私有类型的定义 |

私有出错处理的定义 |

私有游标的定义 |

函数定义 |

过程定义END;/PackageCREATEPACKAGEBODYsal_packageISPROCEDUREraise_sal(v_empno

emp.empno%TYPE,

v_sal_increment

emp.sal%TYPE)ISBEGINUPDATEempSETsal=sal+v_sal_incrementWHEREempno=v_empno;COMMITWORK;

v_raise_sal:=v_raise_sal+v_sal_increment;END;PROCEDUREreduce_sal(v_empno

emp.empno%TYPE,

v_sal_reduce

emp.sal%TYPE)ISBEGINUPDATEempSETsal=sal–v_sal_reduce

WHEREempno=v_empno;COMMITWORK;

v_reduce_sal:=v_reduce_sal+v_sal_reduce;END;END;常用Package所有提供的Package由SYS所拥有，对于不是SYS的用户，必须拥有EXCEUTE权限才能调用创建数据字典的catproc.sql脚本也会创建这些Package所有Oracle提供的程序包都是以DBMS_或UTL_开头系统包功能DBMS_OUTPUT从存储过程中输出信息DBMS_MAIL将Oracle系统与Oracle*Mail连接起来DBMS_LOCK进行复杂的锁机制管理DBMS_ALEART标识数据库中发生的某个警告事件（报警）DBMS_PIPE在不同的会话间传递信息（管道通信）DBMS_JOB管理作业队列中的作业（已被DBMS_SCHEDULER代替）DBMS_LOB操纵大对象（CLOB,BLOB,BFILE等类型的值）DBMS_JAVA用来和JAVA存储过程一起工作DBMS_SQL动态SQL语句（通过该包可在PL/SQL中执行DDL命令）（EXECUTE

IMMEDIATE）DBMS_UTILITY一个有用过程的“大杂烩”集合UTL_FILE允许PL/SQL在服务器上读写文件UTL_HTTP从PL/SQL内提供对HTTP协议的访问UTL_SMTP从PL/SQL内提供对SMTP协议的访问常用Package10g引入dbms_scheduler来替代先前的DBMS_JOB，比DBMS_JOB提供了更强大的功能和更灵活的机制Program：提供了scheduler将要运行什么，包含programname，programtype(PLSQL块、存储过程、外部程序)，programaction(具体的程序实体，比如说一个PLSQL块、或者是存储过程的名称、或者是外部程序的路径加名称)Schedules：指定何时何种频率来运行job，可以被不同的job重复的利用。比如有两个job都要求在每天的凌晨4点运行，那么两个job可以同时指定相同的scheduleJobs：具体的作业的名称，依赖于那个program、schedule。也可以不指定program、schedule，而直接写入program、schedule的相应参数Chains：把一组program像一个单独的实体连接在一起，作为一个对象。Job可以指向Chains代替单一的program。这样就能完成类似如下功能：运行A，然后运行B，当A、B都运行成功则运行C，否则运行Ddbms_schedulerCREATEORREPLACEPROCEDUREP_INSSYSDATEAS

IDNUMBER;

BEGIN

SELECTCOUNT(*)INTOIDFROMSCHEDULER_TEST;

INSERTINTOSCHEDULER_TESTVALUES(ID,SYSDATE);

COMMIT;

END;BEGIN

SYS.DBMS_SCHEDULER.CREATE_SCHEDULE(

schedule_name=>'SCOTT.EVERY_MINUTE’,

start_date=>NULL,

repeat_interval=>‘FREQ=MINUTELY‘,

end_date=>NULL,

comments=>NULL );

END;BEGIN

SYS.DBMS_SCHEDULER.CREATE_PROGRAM(

program_name=>‘SCOTT.PRG_INSSYSDATE‘,

program_type=>’STORED_PROCEDURE‘,

program_action=>’p_insSysdate‘,

number_of_arguments=>0,

enabled=>TRUE,

comments=>NULL);

END;BEGIN

SYS.DBMS_SCHEDULER.CREATE_JOB(

job_name=>‘SCOTT.JOB_INSSYSDATE’

schedule_name=>‘SCOTT.EVERY_MINUTE’,

program_name=>‘SCOTT.PRG_INSSYSDATE’,

comments=>NULL);

END;BEGINFORIIN1..100LOOPBEGINEXECUTEIMMEDIATE'DROPTABLET'||I;EXCEPTIONWHENOTHERSTHENNULL;END;EXECUTEIMMEDIATE'CREATETABLET'||I||'(DUMMYCHAR(1))';EXECUTEIMMEDIATE'INSERTINTOT'||I||'VALUES(''X'')';ENDLOOP;END;/DynamicSQLdeclare

t_vararray

dbms_utility.lname_array;

vc_stringlistvarchar2(4000);

n_idx

binary_integer;begin

--commatotable

vc_stringlist:='dkf,dddl,fewe,klkj';

dbms_ma_to_table(vc_stringlist,n_idx,t_vararray);

dbms_output.put_line('TotalNum:'||to_char(n_idx));

foriin1..n_idxloop

dbms_output.put_line(t_vararray(i));

t_vararray(i):='['||t_vararray(i)||']';endloop;

--tabletocomma

dbms_utility.table_to_comma(t_vararray,n_idx,vc_stringlist);

dbms_output.put_line('');

dbms_output.put_line(vc_stringlist);

end;将字符串每个逗号字符用方括号进行封装TotalNum:4dkfdddlfeweklkj[dkf],[dddl],[fewe],[klkj]DECLARE

l_tempNUMBER;BEGIN

l_temp:=DBMS_UTILITY.get_time;

dbms_output.put_line('before='||l_temp);

dbms_lock.sleep(5);

l_temp:=DBMS_UTILITY.get_time;

dbms_output.put_line('after='||l_temp);END;/DECLARE

l_tempNUMBER;BEGIN

l_temp:=DBMS_UTILITY.get_cpu_time;

dbms_output.put_line('before='||l_temp);

dbms_lock.sleep(5);

l_temp:=DBMS_UTILITY.get_cpu_time;

dbms_output.put_line('after='||l_temp);END;/before=59955835after=59956335before=10after=10触发器触发器由说明部分、语句执行部分和出错处理部分三部分组成的PL/SQL有名块（类似于存储过程和函数）触发器不接受参数，不能在程序中调用当触发事件发生时隐式地（自动地）执行触发事件包括：在数据库表上执行如下操作INSERTUPDATEDELETE触发器维护不可能在表创建时通过说明约束进行的复杂的完整性约束限制通过纪录所进行的修改及谁做了修改等信息对表进行审计当表被修改的时候，自动给需要执行操作的程序发信号触发器CREATEORREPLACETRIGGERcheck_sal

BEFOREINSERTORUPDATEOFsal,jobONempFOREACHROWWHEN(new.job<>‘PRESIDENT’)DECLAER

v_minsal

sal_guide.minsal%TYPE;

v_maxsal

sal_guide.maxsal%TYPE;

e_sal_out_of_rangeEXCEPTION;BEGINSELECTminsal,maxsalINTOv_minsal,v_maxsal

FROMsal_guideWHEREjob=:new.job;IF(:new.sal<v_minsal)OR(:new.sal>v_maxsal)THENRAISEe_sal_out_of_range;ENDIF;EXCEPTIONWHENe_sal_out_rangeTHENINSERTINTOaudit_message(line_nr,line)VALUES(1,’salary’||to_char(:new.sal)||‘isoutofrangeforemployee’||to_char(:new.empno));END;将超过某工种工资范围的员工信息纪录到aduit_message表中。sal_guide表纪录了每一工种的工资范围触发器触发器类型：语句级和行级触发事件：表的插入、更新、删除触发时间：BEFORE和AFTER组成部分描述可能值触发时间与触发事件的时间次序BEFORE,AFTER触发事件触发触发器的数据类型INSERT,UPDATE,DELETE触发器类型触发器体被执行的次数STATEMENT,ROW触发器体该触发器要执行的动作完整的PL/SQL块触发器触发器分语句级触发器和行级触发器两个级别行级触发器与语句触发器的区别主要在于触发次数不同如果该DML语句只影响一行，则语句级和行级触发器效果一样如果该DML语句影响多行，则行级触发器触发的次数比语句级触发器触发的次数多触发器下面DML语句对语句级和行级触发器效果一样。因为这些语句只影响一行下列DML语句的行级与语句级触发器效果不同。因为这些语句只影响多行SQL>INSERTINTOdept(deptno,dname)VALUES(50,’education’);SQL>UPDATEdeptSETloc=‘MAUI’WHEREdeptno=50;SQL>DELETEFROMdeptWHEREdeptno=50;SQL>INSERTINTOemp(empno,dname)SELECTempno,enameFROMemp_backup;SQL>UPDATEdeptSETsal=sal*1.1WHEREdeptno=10;触发器触发时间级别描述BEFORE语句级在触发SQL语句之前执行一次BEFORE行级在触发SQL语句影响的每条纪录被修改、删除或插入之前执行一次AFTER行级在触发SQL语句影响的每条纪录被修改、删除或插入之后执行一次AFTER语句级在触发SQL语句之后执行一次触发器假如一张表同时定义了这四种DML触发器，他们的优先级别如下执行SQL语句（插入、更新、删除一条或多条记录）语句之前触发行之前触发插入、更新、删除一条纪录行之后触发语句之后触发触发器VS.存储过程触发器存储过程当某类数据操纵DML语句发生时隐式地调用从一个应用或过程中显式地调用在触发器体内禁止使用COMMIT,ROLLBACK语句在存储过程体内可以使用COMMIT,ROLLBACK语句不能接受参数输入可以接受参数输入触发器事件指明触发事件的数据操纵语句，有三种可能的值：INSERT,UPDATE,UPDATEOF列名1[,列名2…],DELETE该触发器在一个数据操作语句发生时只触发一次CREATE[ORREPLACE]TRIGGER触发器名{BEFORE|AFTER}事件1[OR事件2...]ON表名PL/SQL块；触发器创建一个BEFORE型语句级触发器。限制一周内向emp表插入数据的时间，如果是周六、周日，或晚上6点到第二天早上8点之间插入，则中断操作，并提示用户不允许在此时间向emp表插入CREATEORREPLACETRIGGERsecure_empBEFOREINSERTONempBEGINIF(TO_CHAR(sysdate,’DY’)IN(’SAT’,’SUN’)OR(TO_CHAR(sysdate,’HH24’)NOTBETWEEN’8’AND’18’)THENRAISE_APPLICATION_ERROR(-20500,’youmayonlyinsertempduringnormalhours.’);ENDIF;END; 触发器使用触发器谓词（INSERTING、UPDATING、DELETING）触发器可以包含多个触发事件，在触发器体中使用谓词判断是哪个触发了触发器谓词行为和值INSERTING如果触发事件是INSERT,则谓词的值为TRUE，否则为FALSEUPDATING如果触发事件是UPDATE,则谓词的值为TRUE，否则为FALSEDELETING如果触发事件是DELETE,则谓词的值为TRUE，否则为FALSE触发器CREATEORREPLACETRIGGERsecure_empBEFOREDELETEORINSERTORUPDATEONempBEGINIF(TO_CHAR(sysdate,’DY’)IN(’SAT’,’SUN’)OR(TO_CHAR(sysdate,’HH24’)NOTBETWEEN’8’AND’18’)THEN

IFDELETINGTHEN RAISE_APPLICATION_ERROR(-20502,’youmayonlydeleteempduringnormalhours.’);

ELSIFINSERTINGTHENRAISE_APPLICATION_ERROR(-20500,’youmayonlyinsertempduringnormalhours.’);ELSETHENRAISE_APPLICATION_ERROR(-20504,’youmayonlyupdateempduringnormalhours.’);ENDIF;ENDIF;END; 触发器通过在CREATETRIGGER语句中指定FOREACHROW子句创建一个行触发器CREATE[ORREPLACE]TRIGGER触发器名{BEFORE|AFTER}事件1[OR事件2...]ON表名FOREACHROW[WHEN限制条件]PL/SQL块；触发器将每个用户对数据库emp表进行数据操纵（插入、更新、删除）