《PL／SQL程序设计》PPT课件.ppt

PL/SQL程序设计讲师：张浒滨,主要内容,3.1PL/SQL的优点：3.2运行PL/SQL程序3.3PL/SQL块结构3.4PL/SQL基本语法3.5PL/SQL处理流程3.6异常处理3.7游标3.8存储过程和函数3.9触发器,认识PL/SQLPL/SQL（procedurelanguage/sql）是oracle在标准的sql语言上的扩展，PL/SQL不仅允许嵌入SQL语言，还可以定义变量和常量，允许使用条件控制语句和循环语句，允许使用例外处理语句处理各种异常，这样使得它的功能强大,3.1PL/SQL的优点,有利于客户/服务器环境应用的运行使用PL/SQL进行编程，将大量数据处理的应用放在服务器端来执行，省去了数据在网上的传输时间。适合于客户环境由于PL/SQL分为数据库PL/SQL部分和工具PL/SQL。对于客户端来说，PL/SQL可以嵌套到相应的工具中，客户端程序可以执行本地包含PL/SQL部分，也可以向服务发SQL命令或激活服务器端的PL/SQL程序运行。,3.3PL/SQL块结构,PL/SQL程序由三个块组成，即声明部分、执行部分、异常处理部分。PL/SQL块的结构如下：Declare/*声明部分:在此声明PL/SQL用到的变量,类型*/Begin/*执行部分:过程及SQL语句,即程序的主要部分*/Exception/*执行异常部分:错误处理*/End;,其中执行部分是必须的。而END则是PL/SQL块的结束标记。需要注意的是DECLARE，BEGIN，EXCEPTION后面没有分号（；），而END后则必须要带有分号。PL/SQL标识符的命名规则：标识符的最大长度是30个字符，包括字母、数字、$、_、#；不可包含保留字；要以字来打头；不能和同一块中的表中的字段名一样，标识符不区分大小写，TYPE与type是完全一样的,【案例】：编写一个程序块,从emp表中显示名为“SMITH”的雇员的薪水和职位。declarev_empemp%rowtype;emp表中所有字段的类型beginselect*intov_empfromempwhereename=SMITH;dbms_output.put_line(v_emp.job|-|v_emp.sal);/(员工的工作是：|v_emp.job)end;执行用/,【例3-1】只包含执行部分的PL/SQL块SQLsetserveroutputon每次打开服务器都要打开SQLbegin2dbms_output.put_line(Welcome!);3end;4/Welcome!PL/SQL过程已成功完成。注意：当使用dbms_output.包输出数据或消息时，必须要将SQL*Plus的环境变量serveroutput设置为on.,【例3-2】包含定义部分和执行部分的PL/SQL块程序案例：请编写一段pl/sql程序，用于查询指定用户的薪水，要求员工编号从键盘输入，显示员工名称和对应薪水declarev_enamevarchar2(10);v_salnumber(7,2);beginselectename,salintov_ename,v_salfromempwhereempno=/,输入no的值:7788PL/SQL过程已成功完成。注意：该例中当执行该PL/SQL时，会根据输入的学号显示雇员名字和薪水。为了临时存放雇员编号，就必须先定义变量。常量包括后面的变量名都必须以字母开头，不能有空格，不能超过30个字符长度，同时不能和保留字同名，常（变）量名称不区分大小写，在字母后面可以带数字或特殊字符。括号内的notnull为可选参数，若选用，表明该常（变）量不能为空值。,【例3-4】常量定义declareaconstantnumber(10):=123;begindbms_output.put_line(a);end;/PL/SQL过程已成功完成,执行结果为：,3.4.2基本数据类型变量,PL/SQL中常用的基本数据类型,常用：varchar2可变长度字符串。number可变长度数值。char固定长度字符串。boolean布尔数据类型，支持true/false值。非常用：clob字符大对象，可用来保存多达4GB的字符数据。date存储全部日期的固定长度字符串。long可变长度字符串。integer表示整数raw二进制数据的可变长度字符串。blob二进制大对象，可以用来保存图像和文档等二进制数据。,3.4.3基本数据类型变量的定义方法,变量名类型标识符notnull:=值;【例3-5】程序定义了名为age的数字型变量，长度为3，初始值为26declarev_agenumber(3):=26;begincommit;dbms_output.put_line(v_age);end;/PL/SQL过程已成功完成。,3.4.4复合数据类型变量,使用%type定义变量为了让PL/SQL中变量的类型和数据表中的字段的数据类型一致，Oracle9i提供了%type定义方法。这样当数据表的字段类型修改后，PL/SQL程序中相应变量的类型也自动修改。,【案例】该程序定义了两个变量，其类型和emp表中的字段类型是一致的。declarev_enameemp.ename%type;v_salemp.sal%type;beginselectename,salintov_ename,v_salfromempwhereempno=,自定义记录类型变量很多结构化程序设计语言都提供了记录类型的数据类型，在PL/SQL中，也支持将多个基本数据类型捆绑在一起的记录数据类型。,记录类型定义的一般格式：定义一个记录类型的数据(类似C语言中的结构体，也类似JAVA中的类，用于存放多个变量)：declaretypeemp_record_typeisrecord(/可以取不同表中的字段nameemp.ename%type,salaryemp.sal%type,titleemp.job%type);sp_recordemp_record_type;beginselectename,sal,jobintosp_recordfromempwhereempno=7788;dbms_output.put_line(员工姓名为：|sp_record.name);end;说明标识符emp_record_type是定义的记录类型名;要定义记录型变量，定义方法与前面标量型变量定义一样。记录类型变量的属性引用方法是.引用。,使用%rowtype属性定义记录型变量使用%type可以使变量获得字段的数据类型，使用%rowtype可以使变量获得整个记录的数据类型。该属性可以基于表或视图定义记录变量。为了简化表或视图所有列数据的处理，应该使用该属性定义记录变量。【案例】执行下列PL/SQL程序，程序定义了名为myrecord的复合类型变量，与emp表结构相同，案例用于从键盘得到员工编号，然后输出姓名、工资、奖金及雇佣日期,declaremyrecordemp%rowtype;beginselect*intomyrecordfromempwhereempno=,ORACLE编写的程序一般分为两类：存储过程：是可以完成一定功能的程序叫存储过程；函数：是在使用时给出一个或多个值，处理完后返回一个或多个结果的程序叫函数；这两类程序都存放在Oracle数据库字典中。,3.8存储过程和函数,与其它的数据库系统一样，Oracle的存储过程是用PL/SQL语言编写的能完成一定处理功能的存储在数据库字典中的程序。创建过程语法:CREATEORREPLACEPROCEDURE过程名(参数名IN|INOUT数据类型.)IS|ASPL/SQL块,3.8.1存储过程,创建一个存储过程，用于实现向表中添加数据先创建一张mytest表createtablemytest(idnumber);createorreplaceproceduresp_pro1(v_idinnumber)is/number(5,2)编译错误/enameinvarchar(2000)begininsertintomytestvalues(v_id);end;,【案例一】建立有参存储过程,建立无参数的存储过程【案例二】以下过程用于输出当前系统日期和时间。createorreplaceproceduresp_pro1isbegindbms_output.put_line(systimestamp);end;/过程已创建。,建立了存储过程sp_pro1之后，就可以调用该过程。在SQL*Plus环境中调用过程：使用execute命令调用过程SQLsetserveroutputon;SQLexecsp_pro1;call19-3月-1207.23.59.265000000下午+08:00PL/SQL过程已成功完成。,建立带有IN参数的存储过程建立存储过程时，可以通过使用输入参数，将应用程序的数据传递到过程中，如果不指定参数模式，则默认是输入参数，可以使用IN关键字显示地定义输入参数。【案例】创建一个过程，能向dept表中添加一个新记录.（in参数）首先创建一张和dept表一样的表deptcp表,createorreplaceprocedureinsert_dept(dept_noinnumber,dept_nameinvarchar2,dept_locinvarchar2)isbegininsertintodeptcpvalues(dept_no,dept_name,dept_loc);end;调用该存储过程：begininsert_dept(50,技术部,武汉);end;或者直接execinsert_dept(50,技术部,武汉);,建立OUT参数的存储过程过程不仅可以用来执行特定操作，而且也可以用于输出数据，在过程中输出数据是使用OUT参数来完成的，当定义输出参数时，必须提供OUT关键字。请看下面的案例，利用in和out参数传递的方式调用一个存储过程【案例】创建一个过程，从emp表中带入雇员的姓名，返回该雇员的薪水值。（out参数，然后调用过程，请同学们先编写这个过程,createorreplaceprocedurefind_emp3(emp_nameinvarchar2,emp_saloutnumber)isv_salnumber(5);beginselectsalintov_salfromempwhereename=emp_name;emp_sal:=v_sal;exceptionwhenno_data_foundthenemp_sal:=0;end;存储过程调用：declarev_salnumber(5);beginfind_emp3(ALLEN,v_sal);dbms_output.put_line(v_sal);end;,建立带INOUT参数的存储过程定义过程时，不仅可以指定IN和OUT参数，也可以指定INOUT参数。INOUT参数也称为输入输出参数，当使用这种参数时，在调用过程之前需要通过变量给该参数传递数据。在调用结束后，Oracle会通过该变量将过程结果传递给应用程序。练习：创建一个存储过程，用于实现将empcp表中的SCOTT用户的工资修改4547，首先要创建一张和emp表一样的empcp表，再创建过程，并调用执行,-创建存储过程：createorreplaceproceduresp_pro3(spnamevarchar2,newsalnumber)isbeginupdateempcpsetsal=newsalwhereename=spname;end;-执行存储过程：execsp_pro3(SCOTT,4547);,Oracle的函数是一个独有的对象，它也是由PL/SQL语句编写而成。与存储过程不同的是函数必须返回某些值，而存储过程可以不返回任何值。创建函数的语法如下：CREATEORREPLACEFUNCTION函数名(argmentin|inoutTYPE,argmentin|out|inoutTYPE,.RETURNdatatypeIS|ASPL/SQL块；,3.8.2函数,建立无参数的函数当建立函数时，函数可以带有参数，也可以不带参数。【例3-26】建立用于显示当前数据库用户的函数。（不带任何参数）CREATEORREPLACEFUNCTIONget_userreturnvarchar2ASv_uservarchar2(100);BEGINselectusernameintov_userfromuser_users;returnv_user;END;函数已创建。,建立带IN参数的函数案例:创建一个函数，它以部门号作为参数传递并且使用函数显示那个部门名称与位置,然后调用此函数。createorreplacefunctionfind_dept(dept_nonumber)returndept%rowtypeisv_deptdept%rowtype;beginselect*intov_deptfromdeptwheredeptno=dept_no;returnv_dept;end;,调用函数：declarev_deptdept%rowtype;beginv_dept:=find_dept(30);dbms_output.put_line(v_dept.dname|-|v_dept.loc);end;,提高数据的安全性与完整性利用安全性的权限来控制那些没有足够权限用户对数据库的间接访问。通过把相关联的表的操作集中到一起，保证对这些相关联的表执行一致的操作，或者任何操作都不做。改善操作性能多个用户使用同一个SQL语句时，只需做一次语法分析，只在编译时进行语法分析，运行时不再重做，可以直接调用缓存中的编译代码。,3.8.4过程和函数的优点,3.2运行PL/SQL程序,PL/SQL程序的运行是通过Oracle中的一个引擎来进行的。这个引擎可能在Oracle的服务器端，也可能在Oracle应用开发的客户端。引擎执行PL/SQL中的过程性语句，然后将SQL语句发送给数据库服务器来执行，再将结果返回给执行端。例如，如果应用程序需要取得学生的成绩，那么可以建立函数实现该项功能。,3.5PL/SQL处理流程,在PL/SQL程序中，要使程序能按照逻辑进行处理，除了有些语句是SQL语句外，还必须有能进行逻辑控制的语句。PL/SQL也不例外，它不仅可以嵌入SQL语句，而且还支持条件分支语句（IF，CASE）、循环语句（LOOP）。,格式：ifthenPL/SQL和SQL语句;else其它语句;elsifthen其它语句;Endif;,3.5.1条件分支语句,【案例】判断两个整数变量的大小，输出不同的结果。setserveroutputondeclarenumber1integer:=80;number2integer:=90;beginifnumber1=number2thenifnumber1=number2thendbms_output.put_line(number1等于number2);elsedbms_output.put_line(number1小于number2);endif;elsedbms_output.put_line(number1大于number2);endif;end;/,思考：如何判断从键盘得到两个数的大小，上述程序该如何修改？,-从键盘得到两个数，程序用于判断这两个数的大小declarev_number1number;v_number2number;beginv_number1:=,-if语句的使用：-编写一个存储过程，可以输入一个雇员名称，若该雇员的工资低于2000，就给该雇员的工资增加10%：createorreplaceproceduresp_pro6(v_enamevarchar2)isv_salempcp.sal%type;beginselectsalintov_salfromempcpwhereename=v_ename;ifv_sal=2000thenupdateempcpsetsal=sal*1.1whereename=v_ename;endif;end;,-二重条件分支-编写一个存储过程，可以输入一个雇员名称，若该雇员的津贴不是0就在原来的基础上增加100，如果津贴是0就把津贴设置为100；createorreplaceproceduresp_pro6(v_enamevarchar2)isv_m%type;beginselectcommintov_commfromempcpwhereename=v_ename;ifv_comm0thenupdateempcpsetcomm=comm+100whereename=v_ename;elseupdateempcpsetcomm=comm+100whereename=v_ename;endif;end;,多重条件分支：编写一个存储过程，可以输入一个雇员编号，如果该雇员的职位是PRESIDENT就给他的工资增加1000，如果该雇员的职位是MANAGER就个他的工资-增加500，其他职位的员工增加200：createorreplaceproceduresp_pro6(v_empnonumber)isv_jobempcp.job%type;beginselectjobintov_jobfromempcpwhereempno=v_empno;ifv_job=PRESIDENTthenupdateempcpsetsal=sal+1000whereempno=v_empno;elsifv_job=MANAGERthenupdateempcpsetsal=sal+500whereempno=v_empno;elseupdateempcpsetsal=sal+200whereempno=v_empno;endif;end;,从Oracle9i开始，不仅可以使用IF语句，也可以使用CASE语句来执行多重条件分支操作。使用CASE语句更加简捷，而且执行效率也更好。在CASE语句中使用单一选择符进行等值比较格式：CASEWHENTHEN语句1；WHENTHEN语句1；WHENTHEN语句1；ELSE语句n+1;ENDCASE;,3.5.2CASE语句,在CASE语句中使用多种条件比较格式：CASEWHENTHEN语句1；WHENTHEN语句1；WHENTHEN语句1；ELSE语句n+1;ENDCASE;练习：请编写一段程序，从键盘得到一个用户名，据此用户名给该用户实现加薪政策,createorreplaceproceduresp_pro1(v_enameemp.ename%type)isv_deptnoemp.deptno%type;beginselectdeptnointov_deptnofromempwhereename=v_ename;casev_deptnowhen10thenupdateempsetsal=sal+10whereename=v_ename;when20thenupdateempsetsal=sal+20whereename=v_ename;elseupdateempsetsal=sal+30whereename=v_ename;endcase;commit;end;,另外一种case方式：程序如下:createorreplaceproceduresp_pro1(v_enameemp.ename%type)isv_deptnoemp.deptno%type;beginselectdeptnointov_deptnofromempwhereename=v_ename;casewhenv_deptno=10thenupdateempsetsal=sal+10whereename=v_ename;whenv_deptno=20thenupdateempsetsal=sal+20whereename=v_ename;elseupdateempsetsal=sal+30whereename=v_ename;endcase;commit;end;,查询中的case语句：即case表达式，如以下查询selectempno,ename,sal,casedeptnowhen10then财务部when20then研发部when30then销售部else未知部门end部门fromemp;DECODE()函数和case表达式类似，decode()函数也用于实现多路分支结构selectempno,ename,sal,decode(deptno,10,财务部,20,研发部,30,销售部,未知部门)部门fromemp;,基本循环Loop要执行的语句;exitwhencondition;endloop;当使用基本循环时，无论是否满足条件，语句至少会被执行一次。当condition为TURE时，会退出循环，并执行ENDLOOP后的相应操作。,3.5.3循环语句,-编写一个过程，可以输入用户名，并自动循环添加10个用户到users表中：1、loop循环实现createorreplaceproceduresp_pro6(unamevarchar2)isv_idnumber:=1;beginloopinsertintousersvalues(v_id,uname);exitwhenv_id=10;v_id:=v_id+1;endloop;end;,WHILE循环格式：whileloop要执行的语句;endloop;只有条件为真时，才会执行循环体内的语句。,while.loop循环实现：createorreplaceproceduresp_pro6(unamevarchar2)isv_idnumber:=1;beginwhilev_id5000thenv_sal:=5000;endif;updateempsetsal=v_salwhereempno=empInfo.empno;endloop;end;,所有的SQL语句在上下文区内部都是可执行的，因此都有一个游标指向上下文区，此游标就是所谓的SQL游标(SQLcursor)，即隐式游标。与显式游标不同，SQL游标不被程序打开和关闭。当一个DML语句执行时，PL/SQL内部打开一个游标，语句的结果被保存在4个游标属性中。SQL%FOUNDSQL%NOTFOUNDSQL%ROWCOUNTSQL%ISOPEN游标是一块包含有查询信息的内存空间。在执行DML语句，游标被打开；当语句完成时，游标被关闭。,3.7.2隐式游标,【案例】更新dept表，将部门号为80的部门名称修改为总经办公室，如果没有这条记录，则向dept表写入该条记录declarebeginupdatedeptsetdname=总经办公室wheredeptno=80;ifsql%notfoundtheninsertintodeptvalues(80,总经办公室,南昌);endif;commit;end;,异常处理,在程序运行的时候出现的错误就称为异常，发生异常后，语句将停止执行，PL/SQL引擎立即将控制权转到PL/SQL块的异常处理部分，异常处理机制简化了代码的错误检测，PL/SQL中任何异常出现时，每个异常都应该对应一个异常码和异常信息，比如：declarev_salemp.sal%type;beginselectsalintov_salfromemp;end;ORA-01422:实际返回的行数超出请求的行数,编写一个程序用于显示emp表中前五行数据记录：declarecursorcur_sisselect*fromemp;empinfoemp%rowtype;connumber(3);beginforempinfoincur_sloopcon:=cur_s%rowcount;ifconbetween1and5thendbms_output.put_line(con|-|empinfo.ename);endif;endloop;end;,异常处理,一个优秀的程序都应该能够正确处理各种出错情况，并尽可能从错误中恢复。Oracle提供异常情况(EXCEPTION)和异常处理(EXCEPTIONHANDLER)来实现错误处理。虽然在PL/SQL编程中，异常处理不是必须的，但建议编程人员要养成在PL/SQL编程中指定相应的异常。,异常处理是用来处理正常执行过程中未预料的事件，异常处理包括预定义的错误和自定义错误。PL/SQL程序块一旦产生异常而没有指出如何处理时,程序就会自动终止整个程序运行。EXCEPTIONwhenexception1thenstatement1;whenexception2thenstatement2;.whenothersthenstatement;END;其中：异常处理可以按任意次序排列,但Others必须放在最后。,3.6.1异常处理概念,两种类型的异常：用户定义(user_define)异常和预定义(predefined)异常。为了方便oracle开发和维护，在oracle异常中，为常见的异常码定义了对应的异常名称，称为预定义异常当使用预定义异常处理时，应该了解PL/SQL块的常见运行错误，并掌握与之相关的预定义异常处理。,3.6.2预定义的异常处理,案例：异常处理：declarev_salemp.sal%type;beginselectsalintov_salfromemp;exceptionwhentoo_many_rowsthendbms_output.put_line(返回的记录太多);whenothersdbms_output.put_line(未知异常);end;,自定义异常除了预定义异常外，用户还可以自定义异常，自定义异常可以让用户采用与PL/SQL引擎处理错误相同的方式进行处理，用户自定义异常的两个关键点：异常定义：在PL/SQL块的声明部分采用exception关键字来声明异常，定义方法与定义变量相同，比如声明一个myexception的异常方法是：myexceptionexception;异常引发：在程序可执行区域，使用raise关键字来进行引发，比如引发myexception的方法是：raisemyexception;,案例：自定义异常declarev_salemp.sal%type;myexpexception;beginselectsalintov_salfromempwhereename=JAMES;ifv_sal5000thenraisemyexp;endif;exceptionwhenno_data_foundthendbms_output.put_line(没有记录发现!);whenmyexpthendbms_output.put_line(工资太少了，怎么向老婆交叉!);end;,引发应用程序异常：在oracle开发中，遇到的系统异常都有对应的异常码，在应用系统开发中，用户自定义的异常也可以指定一个异常码和异常信息，oracle系统为用户预留了自定义的异常码，其范围是-20000到-20999之间的负整数，其语法是：raise_application_error（异常码，异常信息）,请看案例：declarev_salemp.sal%type;myexpexception;beginselectsalintov_salfromempwhereename=JAMES;ifv_sal2000;grantalltoxiaomeionscott.emp;,删除同义词使用的语法是：语法结构：删除同义词droppublicsysonymschema.sysnonym_name查询同义词信息：select*fromuser_synonyms;,3.序列序列(Sequence)是用来生成连续的整数数据的对象。序列常常用来作为主键中增长列，序列中的可以升序生成，也可以降序生成。创建序列的语法是：createsequencemyseq1startwith1minvalue1incrementby2nocyclecache30;2使用ALTERSEQUENCE可以修改序列，在修改序列时有如下限制：1.不能修改序列的初始值。2.最小值不能大于当前值。3.最大值不能小于当前值。使用dropsequence命令可以删除一个序列对象。,序列的查看：可以使用数据字典来查看序列的信息；DBA_SEQUENCES：DBA视图描述数据库的所有序列ALL_SEQUENCES：ALL视图描述数据库的所有序列USERS_SEQUENCES：USER视图描述用户拥有的序列4.视图视图（View）实际上是一张或者多张表上的预定义查询，这些表称为基表。从视图中查询信息与从表中查询信息的方法完全相同。只需要简单的SELECTFROM即可。视图具有以下优点：1.可以将复杂的查询保存为视图。可以对最终用户屏蔽一定的复杂性。3.限制某个视图只能访问基表中的部分列或者部分行的特定数据。这样可以实现一定的安全性。,4.从多张基表中按一定的业务逻辑抽出用户关心的部分，形成一张虚拟表。语法结构：创建视图CREATEORREPLACEFORCE|NOFORCEVIEWview_nameASSELECT查询WITHREADONLYCONSTRAINT语法解析：1.ORREPLACE：如果视图已经存在，则替换旧视图。2.FORCE：即使基表不存在，也可以创建该视图，但是该视图不能正常使用，当基表创建成功后，视图才能正常使用。3.NOFORCE：如果基表不存在，无法创建视图，该项是默认选项。,4.WITHREADONLY：默认可以通过视图对基表执行增删改操作，但是有很多在基表上的限制（比如：基表中某列不能为空，但是该列没有出现在视图中，则不能通过视图执行insert操作），WITHREADONLY说明视图是只读视图，不能通过该视图进行增删改操作。现实开发中，基本上不通过视图对表中的数据进行增删改操作。案例3：基于EMP表和DEPT表创建视图createorreplaceviewempdetailasselectempno,ename,job,hiredatefromempwithreadonly;,Oracle索引什么是做引？一种用户提升查询效率的数据库对象通过快速定位数据的方法，减少磁盘I/O操作索引信息与表独立存放oracle数据库自动使用和维护做引索引分类唯一性索引非唯一性索引创建索引的方式自动创建：在定义主键或唯一性约束的时候系统会自动在相应的字段上创建唯一索引手动创建：用户可以在其他列上创建索引，以加速查询速度,可以使用createindex语句手动创建索引createindex索引名on表名(列名,列名)删除索引使用dropindex语句删除索引下列情况下可以创建索引字段取值分布范围很广字段中包含大量的空值字段经常出现在where子句或者连接条件中表经常被访问、数据量很大，且通常每次访问的数据量小于记录总量的2%-4%下列情况可以不用创建索引表很小字段不经常出现在where子句中每次访问的数据量大于记录总数的2%-4%表经常更新被索引的字段作为表达式的一部分被引用,查询用户字典视图user_indexes（sys用户）可得到用户的所有索引查询用户字典视图user_ind_columns（sys用户）可以获知索引建立在哪些字段上,表空间,表空间是ORACLE最大的逻辑组成部分。ORACLE数据库由一个或多个表空间组成。一个表空间由一个或多个数据文件组成，但一个数据文件只能属于一个表空间。数据库表等对象是存放在表空间中的。一个数据库有：SYSTEM、SYSAUX、TEMP三个默认表空间,创建表空间：createtablespacetestdatafilec:temptest.dbfsize2M;注意要用拥有createtablespace权限的用户，比如sys扩展表空间：当数据超过表空间大小时，要对表空间进行扩充。扩充有三种方式：（1）添加数据文件（2）改变数据文件大小（3）允许数据文件自动扩展添加数据文件：-为test表空间添加一个3M的数据文件altertablespacetestadddatafilec:temptest1.dbfsize2048k;改变数据文件大小：-为test表空间的数据文件增加4Malterdatabasedatafilec:temptest.dbfresize4096k;,-将表空间test改为test1altertablespacetestrenametotest1;-删除表空间test1droptablespacetest1includingcontentsanddatafiles;如果没有anddatafiles选项，则不删除物理文件必须具有droptablesapce系统权限的用户才可以执行，比如：sys。,39触发器,一、什么是触发器？数据库触发器是一个存储的PL/SQL程序块，当在表上执行特定的数据库维护（插入、删除、更新这三种操作）时，隐含地执行一个PL/SQL程序块。二、触发器的作用：防止非法的数据库操纵、维护数据库安全对数据库的操作进行审计，存储历史数据完成数据库初始化处理控制数据库的数据完整性进行相关数据的修改完成数据复制自动完成数据库统计计算限制数据库操作的时间、权限等，控制实体的安全性。,三、触发器的组成：1、触发时间：触发器事件的时间次序（before,after)2、触发事件：什么SQL语句会引起触发器触发(insert,delete,update)3、触发子体：触发器触发时要执行的操作(一个完整的PL/SQL程序)4、触发类型：触发器被执行的次数(语句级、行级）,四、创建触发器注意事项：1、在触发器中可以调用存储过程；在存储过程中不得调用触发器。2、在触发器中不得使用commit,rollback,savepoint语句。3、在触发器中不得间接调用含有commit,rollback,savepoint的语句的存储过程及函数。五、创建语句级触发器：语句级触发器：该触发器在数据库操作时只执行一次。说明：update中的of是可选项，用于指定语句要修改的列要创建的触发器已经存在时，使用replace选项,创建触发器的一般语法是：CREATEORREPLACETRIGGER触发器名BEFORE|AFTEReventONtable_referenceFOREACHROWWHENtrigger_conditiontrigger_body;,3.9.2DML触发器,创建一个行级别触发器，停止用户删除雇员名为SMITH的记录。createorreplacetriggerdelete_smithbeforedeleteonempforeachrowwhen(old.ename=SMITH)beginraise_application_error(-20001,不能删除该条信息!);end;,/例2：After型触发器：createorreplacetriggerinsertempafterinsertonempdeclarev_empcountnumber(7);beginselectcount(*)intov_empcountfromemp;dbms_output.put_line(目前员工总数达到：|v_empcount|名。);end;,条件谓词：当在触发器中包含了多个触发事件的组合时，为了分别针对不同的事件进行不同的处理，需要使用oracle提供的如下条件谓词：inserting：当触发事件是insert时，取值为true，否则为false；updating：当触发事件是update时，取值为true，否则为false；deleting：当触发事件是delete时，取值为true，否则为false；请看下面的案例：,createorreplacetriggertri_empbeforeinsertorupdateordeleteonempbeginifto_char(sysdate,DY,nls_date_language=AMERICAN)IN(SAT,SUN)thencasewheninsertingthenraise_application_error(-20001,不能在休息日增加雇员);whenupdatingthenraise_application_error(-20001,不能在休息日更新雇员);whendeletingthenraise_application_error(-20001,不能在休息日更新雇员);endcase;endif;End;,练习：,请创建一个触发器实现：当dept表中的deptno字段发生变化时，emp表中相应的deptno也会发生变化，请编写代码：createorreplacetriggerupdate_cascade_empafterupdateordeleteondeptforeachrowbeginupdateempsetemp.deptno=:new.deptnowhereemp.deptno=:old.deptno;end;,3.10小结,PL/SQL程序由三个块组成，包括声明部分、执行部分、异常处理部分。当编写PL/SQL应用程序时，只能直接嵌入SELECT语句、DML语句和事务控制语句，不能直接嵌入DDL语句和DCL语句。PL/SQL支持条件分支语句（IF，CASE）、循环语句（LOOP）和顺序语句（GOTO，NULL）。为了让PL/SQL中变量的类型和数据表中的字段的数据类型一致，Oracle9i提供了%type定义方法。,PL/SQL集合类型类似于高级语言数组的一种复合数据类型，集合类型包括索引表（PL/SQL表）、嵌套表和变长数组等三种类型。异常情况处理(EXCEPTION)是用来处理正常执行过程中未预料的事件，包括预定义的错误和自定义错误。PL/SQL程序块一旦产生异常而没有指出如何处理时，程序就会自动终止。有两种类型的异常情态：用户定义(user_define)和预定义(predefined)。,对于数据操纵语句和单行SELECTINTO语句来说，Oracle会为它们分配隐含游标。Oracle编写的程序一般分为存储过程和函数，都存放在Oracle数据库字典中。存储过程是用PL/SQL语言编写的能完成一定处理功能的程序，函数是一个独有的对象，它也是由PL/SQL语句编写而成，但函数必须返回某些值，而存储过程可以不返回任何值。,触发器在数据库里以独立的对象存储，它与存储过程不同的是，存储过程通过其它程序来启动运行或直接启动运行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行，触发器不能接收参数。在Oracle里，触发器事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作。,