Oracle第14章PLSQL语言基础

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,2007-9,东北大学东软信息学院计算机系,*,Oracle10g,数据库基础教程,Oracle10g,数据库基础教程,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,2007-9,东北大学东软信息学院计算机系,*,1,第,14,章,PL/SQL,语言基础,2,本章内容,PL/SQL,概述,PL/SQL,基础,控制结构,游标,异常处理,3,本章要求,掌握,PL/SQL,程序基本结构,掌握,PL/SQL,程序控制结构,掌握,PL/SQL,程序游标应用,掌握,PL/SQL,程序异常处理,机,机制,4,14.1PL/SQL,概述,PL/SQL,特点,PL/SQL,功能特性,PL/SQL,执行过程与开,发,发工具,5,14.1.1PL/SQL,特点,与,SQL,语言紧密集成,。,。,减小网络流量,，,，提高应用程,序,序的运行性能,。,。,模块化的程序,设,设计功能，提,高,高了系统可靠,性,性。,服务器端程序,设,设计，可移植,性,性好。,6,14.1.2PL/SQL,功能特性,语句块结构,异常处理,变量和类型,条件语句,循环结构,游标,过程、函数和,触,触发器,包,集合,动态,SQL,批绑定,对象特性,7,14.1.3PL/SQL,执行过程与开,发,发工具,PL/SQL,块,SQL,语句,客户端应用程,序,序,PL/SQL,引擎,数据库服务器,过程化语句执,行,行器,SQL,执行器,块中,SQL,语句,PL/SQL,执行过程,8,PL/SQL,开发工具,SQL *PLUS,Procedure Builder,OracleForm,、,OracleReports,PL/SQLDeveloper,9,14.2PL/SQL,基础,PL/SQL,程序结构,词法单元,数据类型,变量与常量,PL/SQL,记录,编译指示,PL/SQL,中的,SQL,语句,10,14.2.1,PL/SQL,程序结构,PL/SQL,块的组成,PL/SQL,块分类,11,（,1,）,PL/SQL,块的组成,PL/SQL,程序的基本单,元,元是语句块，,所,所有的,PL/SQL,程序都是由语,句,句块构成的,。,。,一个完整的,PL/SQL,语句块由,3,个部分组成。,12,声明部分,主要用于声明,变,变量、常量、,数,数据类型、游,标,标、异常处理,名,名称以及本地,（,（局部）子程,序,序定义等。,可执行部分,执行部分是,PL/SQL,块的功能实现,部,部分。该部分,通,通过变量赋值,、,、流程控制、,数,数据查询、数,据,据操纵、数据,定,定义、事务控,制,制、游标处理,等,等实现块的功,能,能。,异常处理部分,异常处理部分,用,用于处理该块,执,执行过程中产,生,生的异常。,13,注意：,执行部分是必,须,须的，而声明,部,部分和异常部,分,分是可选的,可以在一个块,的,的执行部分或,异,异常处理部分,嵌,嵌套其他的,PL/SQL,块；,所有的,PL/SQL,块都是以,“,END;,”,结束。,14,DECLARE,v_ename VARCHAR2(10);,BEGIN,SELECTenameINTOv_ename FROMemp,WHEREempno=7844;,DBMS_OUTPUT.PUT_LINE(v_ename);,EXCEPTION,WHEN NO_DATA_FOUNDTHEN,DBMS_OUTPUT.PUT_LINE(There isnot such aemployee);,END;,15,DECLARE,v_salNUMBER(6,2);,v_deptno NUMBER(2);,BEGIN,BEGIN,SELECTdeptno INTOv_deptno FROM emp,WHEREempno=7844;,END;,SELECTavg(sal) INTO v_sal FROM emp,WHEREdeptno=v_deptno;,DBMS_OUTPUT.PUT_LINE(v_sal);,END;,16,注意,若要在,SQL*Plus,环境中看到,DBMS_OUTPUT.PUT_LINE,方法的输出结,果,果，必须将环,境,境变量,SERVEROUTPUT,设置为,ON,。,SET SERVEROUTPUT ON,17,（,2,）,PL/SQL,块分类,匿名块,匿名块是指动,态,态生成，只能,执,执行一次的块,，,，不能由其他,应,应用程序调用,。,。,命名块,命名块是指一,次,次编译可多次,执,执行的,PL/SQL,程序，包括函,数,数、存储过程,、,、包、触发器,等,等。它们编译,后,后放在服务器,中,中，由应用程,序,序或系统在特,定,定条件下调用,执,执行。,18,命名块示例,CREATEOR REPLACEPROCEDURE showavgsal,(p_deptno NUMBER),AS,v_salNUMBER(6,2);,BEGIN,SELECTavg(sal) INTO v_sal FROM emp,WHEREdeptno=p_deptno;,DBMS_OUTPUT.PUT_LINE(v_sal);,END showavgsal;,19,14.2.2,词法单元,字符集,标识符,分隔符,常量值,注释,20,（,1,）字符集,PL/SQL,的字符集包,括,括：,大小写字母,：,：,AZ,，,az,数字：,09,空白：制表,符,符、空格和,回,回车,数字符号：,+ - */,=,标点符号：, ! # $% &*,（）,_ | ,？；：, .,“,注意,PL/SQL,字符集不区,分,分大小写。,21,（,2,）标识符,标识符用于,定,定义,PL/SQL,变量、常量,、,、异常、游,标,标名称、游,标,标变量、参,数,数、子程序,名,名称和其他,的,的程序单元,名,名称等。,在,PL/SQL,程序中，标,识,识符是以字,母,母开头的，,后,后边可以跟,字,字母、数字,、,、美元符号,（,（,$,）、井号（,#,）或下划线,（,（,_,），其最大,长,长度为,30,个字符，并,且,且所有字符,都,都是有效的,。,。,例如，,X,，,v_empno,，,v_$,等都是有效,的,的标识符，,而,而,X+y,，,_temp,则是非法的,标,标识符。,注意,如果标识符,区,区分大小写,、,、使用预留,关,关键字或包,含,含空格等特,殊,殊符号，则,需,需要用,“”,括起来，称,为,为引证标识,符,符。例如标,识,识符,“,my book,”,和,“,exception,”,。,22,（,3,）分隔符,+,-,*,/,=,:=,=,!=,=,=,(,),/*,*/,%,;,:,.,“,.,|,=,*,-,分隔符是指,有,有特定含义,的,的单个符号,或,或组合符号,23,（,4,）常量值,字符型文字,以单引号引,起,起来的字符,串,串，在字符,串,串中的字符,区,区分大小写,。,。如果字符,串,串中本身包,含,含单引号，,则,则用两个连,续,续的单引号,进,进行转义。,数字型文字,分为整数与,实,实数两类。,其,其中，整数,没,没有小数点,，,，如,123,；而实数有,小,小数点，如,123.45,。可以用科,学,学计数法表,示,示数字型文,字,字，如,123.45,可以表示为,1.2345E2,。,布尔型文字,预定义的布,尔,尔型变量的,取,取值，包括,TRUE,，,FALSE,，,NULL,三个值。,日期型文字,表示日期值,，,，其格式随,日,日期类型格,式,式不同而不,同,同。,24,（,5,）注释,单行注释,-,多行注释,以,“,/*,”,开始，以,“,*,/,”,结束。,DECLARE,v_department CHAR(10);,- variable toholdthe departmentname,BEGIN,/* query the department name which,departmentnumber is10 ouputthe department,nameintov_department*/,SELECT dname INTO v_departmentFROMdept,WHEREdeptno=10;,END;,25,14.2.3,数据类型,数字类型,字符类型,日期,/,区间类型,行标识类型,布尔类型,原始类型,LOB,类型,引用类型,记录类型,集合类型,%TYPE,与,%ROWTYPE,26,PL/SQL,中常用的基,本,本数据类型,分类,数据类型,数字类型,NUMBER,、,BINARY_NUMBER PLS_NUMBER,字符类型,VARCHAR2,、,CHAR,、,LONG,、,NCHAR,、,NVARCHAR,日期,/,区间类型,DATE,、,TIMESTAMP,、,INTERVAL,行标识类型,ROWID,、,UROWID,布尔类型,BOOLEAN,（,TRUE,、,FALSE,、,NULL,）,原始类型,RAW,、,LONG RAW,LOB,类型,CLOB,、,BLOB,、,NCLOB,、,BFILE,引用类型,REF CURSOR,，,REF object_type,。,记录类型,RECORD,集合类型,TABLE,、,VARRAY,27,数字类型,NUMBER,类型以十进制形,式,式存储整数和浮,点,点数，语法为,NUMBER,（,p,，,s,）。其中，,p,为精度，即所有,有,有效数字位数；,s,为刻度范围，即,小,小数位数。,p,的取值范围为,1,38,。,BINARY_INTEGER,类型用于表示从,-2147483647,+2147483647,之间的整数，以,二,二进制形式存储,。,。当发生溢出时,，,，将自动转换成,NUMBER,类型。,PLS_INTEGER,类型表示范围与,BINARY_INTEGER,相同，但发生溢,出,出时会产生错误,。,。,28,字符类型,PL/SQL,中的字符类型与,Oracle,数据库中的字符,类,类型类似，但是,允,允许字符串的长,度,度有所不同。,VARCHAR2,，,CHAR,主要用于存储来,自,自本地数据库字,符,符集的字符，而,NCHAR,，,NVARCHAR2,用于存储来自国,家,家字符集的字符,串,串。,类 型,PL/SQL,中最大字节数,Oracle,中最大字节数,VARCHAR2,32767,4000,NVARCHAR2,32767,4000,CHAR,32767,2000,NCHAR,32767,2000,LONG,32760,2GB,29,日期,/,区间类型,DATE,：与数据库中的,DATE,类型相同，存储,日,日期和时间信息,，,，包括世纪、年,、,、月、日、小时,、,、分和秒，不包,括,括秒的小数部分,。,。,TIMESTAMP,：与,DATE,类型相似，但包,括,括秒的小数部分,，,，有以下,3,种形式。,TIMESTAMP(,p,),：其中,p,为秒字段的小数,部,部分精度。,TIMESTAMP(,p,)WITHTIME ZONE,：返回当前时区,的,的时间戳。,TIMESTAMP(,p,)WITHLOACL TIME ZONE,：返回数据库时,区,区的时间戳。,30,INTERVAL,：用于存储两个,时,时间戳之间的时,间,间间隔，有下面,两,两种形式。,INTERVAL YEAR(,p,)TO MONTH,：两个时间戳相,差,差的年数和月数,。,。,INTERVAL DAY(dp) TOSECOND(sp),：两个时间戳相,差,差的天数和秒数,。,。,31,行标识类型,ROWID,表示行的物理地,址,址,UROWID,既可以表示行的,物,物理地址，也可,以,以表示行的逻辑,地,地址。,布尔类型（,BOOLEAN,）,只能在,PL/SQL,中使用，其取值,为,为逻辑值，包括,TRUE,、,FALSE,、,NULL,。,原始类型,与,Oracle,数据库中的原始,类,类型相似，但子,节,节数不同。,类 型,PL/SQL,中最大字节数,Oracle,中最大字节数,RAW,32767,2000,LONG RAW,32767,2G,32,LOB,类型,包括,BLOB,，,CLOB,，,NCLOB,和,BFILE,四种类型。其中,BLOB,存放二进制数据,，,，,CLOB,，,NCLOB,存放文本数据，,而,而,BFILE,存放指向操作系,统,统文件的指针。,LOB,类型变量可以存,储,储,4 GB,的数据量。,引用类型,引用类型类似于,其,其他高级语言中,的,的指针类型。在,PL/SQL,中，引用类型包,括,括游标的引用类,型,型和对象的引用,类,类型，即,REF CURSOR,和,REF object_type,。,33,记录类型,记录类型是复合,类,类型，类似于,C,语言中的结构体,，,，是一个包含若,干,干个成员分量的,复,复合类型。,在使用记录类型,时,时，需要先在声,明,明部分定义记录,类,类型和记录类型,的,的变量，然后在,执,执行部分引用该,记,记录类型变量或,其,其成员分量。,集合类型,集合类型是复合,类,类型，包括索引,表,表类型、嵌套表,类,类型和可变数组,类,类型。,集合类型与记录,类,类型的区别在于,，,，记录类型中的,成,成员分量可以是,不,不同类型的，类,似,似于结构体，而,集,集合类型中所有,的,的成员分量必须,具,具有相同的数据,类,类型，类似于数,组,组。,34,%TYPE,与,%ROWTYPE,如果要定义一个,类,类型与某个变量,的,的数据类型或数,据,据库表中某个列,的,的数据类型一致,（,（不知道该变量,或,或列的数据类型,）,）的变量，可以,利,利用,%TYPE,来实现。,如果要定义一个,与,与数据库中某个,表,表结构一致的记,录,录类型的变量，,可,可以使用,%ROWTYPE,来实现。,注意,变量的类型随参,照,照的变量类型、,数,数据库表列类型,、,、表结构的变化,而,而变化；,如果数据库表列,中,中有,NOT NULL,约束，则,%TYPE,与,%ROWTYPE,返回的数据类型,没,没有此限制。,35,DECLARE,v_sal emp.sal%TYPE;,v_emp emp%ROWTYPE;,BEGIN,SELECTsal INTO v_salFROM emp WHERE empno=7844;,SELECT* INTOv_emp FROM empWHEREempno=7900;,DBMS_OUTPUT.PUT_LINE(v_sal);,DBMS_OUTPUT.PUT_LINE(v_emp.ename|v_emp.sal);,END;,36,14.2.4,变量与常量,变量与常量的定,义,义,变量的作用域,37,变量声明,（,1,）变量与常量的,定,定义,变量定义的一般,格,格式,variable_nameCONSTANT datatype NOT NULL DEFAULT|:=expression;,说明,变量或常量名称,是,是一个,PL/SQL,标识符，应符合,标,标识符命名规范,；,；,每行只能定义一,个,个变量；,如果加上关键字,CONSTANT,，则表示所定义,的,的是一个常量，,必,必须为它赋初值,；,；,如果定义变量时,使,使用了,NOT NULL,关键字，则必须,为,为变量赋初值；,如果变量没有赋,初,初值，则默认为,NULL,；,使用,DEFAULT,或“,:=”,运算符为变量初,始,始化。,38,DECLARE,v1 NUMBER(4);,v2 NUMBER(4) NOT NULL:=10;,v3 CONSTANT NUMBER(4)DEFAULT 100;,BEGIN,IF v1 IS NULLTHEN,DBMS_OUTPUT.PUT_LINE(V1 ISNULL! );,END IF;,DBMS_OUTPUT.PUT_LINE(v2| |v3);,END;,39,（,2,）变量的作用域,变量的作用域是,指,指变量的有效作,用,用范围，从变量,声,声明开始，直到,块,块结束。,如果,PL/SQL,块相互嵌套，则,在,在内部块中声明,的,的变量是局部的,，,，只能在内部块,中,中引用，而在外,部,部块中声明的变,量,量是全局的，既,可,可以在外部块中,引,引用，也可以在,内,内部块中引用。,如果内部块与外,部,部块中定义了同,名,名变量，则在内,部,部块中引用外部,块,块的全局变量时,需,需要使用外部块,名,名进行标识。,40,DECLARE,v_enameCHAR(16);,v_outerNUMBER(5);,BEGIN,v_outer:=10;,DECLARE,v_enameCHAR(20);,v_innerDATE;,BEGIN,v_inner:=sysdate;,v_ename:=INNER V_ENAME;,OUTER.v_ename:=OUTERV_ENAME;,END;,DBMS_OUTPUT.PUT_LINE(v_ename);,END;,41,14.2.5PL/SQL,记录,用户定义记录类,型,型及变量,利用,%ROWTYPE,获取记录类型定,义,义变量,记录类型变量的,应,应用,在,SELECT,语句中使用记录,类,类型变量,在,INSERT,语句中使用记录,类,类型变量,在,UPDATE,语句中使用记录,类,类型变量,在,DELETE,语句中使用记录,类,类型变量,42,（,1,）用户定义记录,类,类型及变量,定义记录类型的,语,语法为,TYPE record_type IS RECORD(,field1datatype1 NOTNULLDEFAULT|:=expr1,field2datatype2 NOTNULLDEFAULT|:=expr2,fieldndatatypen NOTNULLDEFAULT|:=exprn);,注意：,相同记录类型的,变,变量可以相互赋,值,值；,不同记录类型的,变,变量，即使成员,完,完全相同也不能,相,相互赋值；,记录类型只能应,用,用于定义该记录,类,类型的,PL/SQL,块中，即记录类,型,型是局部的,。,43,利用记录类型以,及,及记录类型变量,，,，保存员工信息,。,。,DECLARE,TYPE t_emp ISRECORD(,empno NUMBER(4),ename CHAR(10),sal NUMBER(6,2);,v_emp t_emp;,BEGIN,SELECTempno,ename,sal INTOv_emp,FROM emp WHEREempno=7844;,DBMS_OUTPUT.PUT_LINE(v_emp.ename|v_emp.sal);,END;,44,（,2,）利用,%ROWTYPE,获取记,录,录类型,定,定义,变,变量,DECLARE,v_emp1emp%ROWTYPE;,v_emp2emp%ROWTYPE;,CURSORc_empISSELECT empno,ename FROMemp,WHEREdeptno=10;,v_emp10 c_emp%ROWTYPE;,BEGIN,SELECT*INTO v_emp1FROM empWHEREempno=7844;,OPEN c_emp;,LOOP,FETCHc_empINTO v_emp10;,EXIT WHENc_emp%NOTFOUND;,DBMS_OUTPUT.PUT_LINE(v_emp10.empno|,|,v_emp10.ename);,ENDLOOP;,CLOSEc_emp;,END;,45,（,3,）记录,类,类型变,量,量的应,用,用,在,SELECT,语句中,使,使用记,录,录类型,变,变量,在,SELECTINTO,语句中,使,使用记,录,录类型,变,变量,DECLARE,v_empemp%ROWTYPE;,BEGIN,SELECT*INTO v_emp FROMemp,WHEREempno=7844;,DBMS_OUTPUT.PUT_LINE(v_emp.empno|,v_emp.ename|v_emp.sal);,END;,注意,记录类,型,型变量,中,中分量,的,的个数,、,、顺序,、,、类型,应,应该与,查,查询列,表,表中列,的,的个数,、,、顺序,、,、类型,完,完全匹,配,配。,46,在,SELECT,语句中,使,使用记,录,录类型,变,变量,在,SELECTINTO,语句中,使,使用记,录,录类型,变,变量成,员,员,DECLARE,v_empemp%ROWTYPE;,BEGIN,SELECTempno,ename,sal INTOv_emp.empno,v_emp.ename,v_emp.salFROMemp,WHEREempno=7844;,DBMS_OUTPUT.PUT_LINE(v_emp.empno|,v_emp.ename|v_emp.sal);,END;,47,在,INSERT,语句中,使,使用记,录,录类型,变,变量,在,VALUES,子句中,使,使用记,录,录类型,变,变量,DECLARE,v_deptdept%ROWTYPE;,BEGIN,v_dept.deptno:=50;,v_dept.loc:=BEIJING;,V_dept.dname:=COMPUTER;,INSERTINTODEPT VALUESv_dept;,END;,注意,记录类,型,型变量,中,中分量,的,的个数,、,、顺序,、,、类型,应,应该与,表,表中列,的,的个数,、,、顺序,、,、类型,完,完全匹,配,配。,48,在,INSERT,语句中,使,使用记,录,录类型,变,变量,在,VALUES,子句中,使,使用记,录,录类型,变,变量成,员,员,DECLARE,v_empemp%ROWTYPE;,BEGIN,SELECT*INTO v_emp FROMemp,WHEREempno=7844;,INSERTINTOemp(empno,ename,mgr,sal),VALUES(1234,TOM,v_emp.mgr,v_emp. sal);,END;,49,在,UPDATE,语句,中,中使,用,用记,录,录类,型,型变,量,量,在,SET,子句,中,中使,用,用记,录,录类,型,型变,量,量（,使,使用,ROW,关键,字,字）,DECLARE,v_deptdept%ROWTYPE;,BEGIN,v_dept.deptno:=50;,v_dept.loc:=TIANJIN;,V_dept.dname:=COMPUTER;,UPDATEdeptSETROW=v_deptWHEREdeptno=50;,END;,注意,记录,类,类型,变,变量,中,中分,量,量的,个,个数,、,、顺,序,序、,类,类型,应,应该,与,与表,中,中列,的,的个,数,数、,顺,顺序,、,、类,型,型完,全,全匹,配,配。,50,在,UPDATE,语句,中,中使,用,用记,录,录类,型,型变,量,量,在,SET,子句,中,中使,用,用记,录,录类,型,型变,量,量成,员,员,DECLARE,v_empemp%ROWTYPE;,BEGIN,SELECT*INTOv_empFROMemp,WHEREempno=7844;,UPDATEempSETsal=v_emp.sal,comm=v_m,WHEREempno=7369;,END;,51,在,DELETE,语句,中,中使,用,用记,录,录类,型,型变,量,量,DECLARE,v_empemp%ROWTYPE;,BEGIN,SELECT*INTOv_empFROMemp,WHEREempno=7844;,DELETEFROMempWHEREdeptno=v_emp.deptno;,END;,52,14.2.6,编译,指,指示,编译,指,指示,是,是对,编,编译,程,程序,发,发出,的,的特,殊,殊指,令,令，,也,也称,为,为伪,指,指令,，,，不,会,会改,变,变程,序,序含,义,义。,它,它只,是,是向,编,编译,程,程序,传,传递,信,信息,，,，类,似,似于,嵌,嵌入,在,在,SQL,中的,注,注释,。,。,在,PL/SQL,中使,用,用,PRAGMA,关键,字,字通,知,知编,译,译程,序,序，,PL/SQL,语句,的,的剩,余,余部,分,分是,一,一个,编,编译,指,指示,或,或命,令,令。,编,编译,指,指示,在,在编,译,译时,被,被处,理,理，,而,而不,会,会在,运,运行,时,时被,执,执行,，,，类,似,似于,C,语言,中,中的,#define,。,53,PL/SQL,提供,以,以下,4,种编,译,译指,示,示,EXCEPTION_INIT,：告,诉,诉编,译,译程,序,序将,一,一个,特,特定,的,的错,误,误号,与,与程,序,序中,所,所声,明,明的,异,异常,标,标识,符,符关,联,联起,来,来。,RESTRICT_REFERENCES,：告,诉,诉编,译,译程,序,序打,包,包程,序,序的,纯,纯度,，,，即,对,对函,数,数中,可,可以,使,使用,的,的,SQL,语句,和,和包,变,变量,进,进行,限,限制,。,。,SERIALLY_REUSEABLE,：告,诉,诉,PL/SQL,运行,引,引擎,时,时，,在,在数,据,据引,用,用之,间,间不,要,要保,持,持包,级,级数,据,据。,AUTONOMOUS_TRANSACTION,：告,诉,诉编,译,译程,序,序，,该,该程,序,序块,为,为自,治,治事,务,务，,即,即该,事,事务,的,的提,交,交和,回,回滚,是,是独,立,立进,行,行的,。,。,54,14.2.7PL/SQL,中,SQL,语句,由于,PL/SQL,执行,采,采用,早,早期,绑,绑定,，,，即,在,在编,译,译阶,段,段对,变,变量,进,进行,绑,绑定,，,，识,别,别程,序,序中,标,标识,符,符的,位,位置,，,，检,查,查用,户,户权,限,限、,数,数据,库,库对,象,象等,信,信息,，,，因,此,此在,PL/SQL,中只,允,允许,出,出现,:,SELECT,DML(UPDATE,、,DELETE,、,INSERT),事务,控,控制,语,语句,（,（,COMMIT,、,ROLLBACK,、,SAVEPOINT,）,注意,DDL,语句,不,不可,以,以直,接,接使,用,用,55,通常,，,，利,用,用,SQL,语句,对,对数,据,据库,进,进行,操,操作,时,时，,各,各种,相,相关,量,量都,在,在代,码,码中,以,以常,量,量的,形,形式,指,指定,，,，而,在,在,PL/SQL,中可以通,过,过变量动,态,态指定各,种,种相关量,的,的值，从,而,而实现对,数,数据库的,动,动态操作,。,。,DECLARE,v_empnoNUMBER(4);,BEGIN,v_empno:=,UPDATE emp SET sal=sal+100 WHEREempno=v_empno;,END;,56,SELECT,语句,在,PL/SQL,程序中，,使,使用,SELECT,INTO,语句查询,一,一个记录,的,的信息。,其语法为,：,：,SELECT select_list_itemINTO,variable_list|record_variable,FROMtable,WHERE condition;,57,根据员工,名,名或员工,号,号查询员,工,工信息，,程,程序为：,DECLARE,v_emp emp%ROWTYPE;,v_enameemp.ename%type;,v_salemp.sal%type;,BEGIN,SELECT *INTO v_empFROMemp,WHERE ename=SMITH;,DBMS_OUTPUT.PUT_LINE(v_emp.empno|v_emp.sal);,SELECT ename,salINTO v_ename,v_salFROMemp,WHERE empno=7900;,DBMS_OUTPUT.PUT_LINE(v_ename|v_sal);,END;,58,注意：,SELECT,INTO,语句只能,查,查询一个,记,记录的信,息,息，如果,没,没有查询,到,到任何数,据,据，会产,生,生,NO_DATA_FOUND,异常；如,果,果查询到,多,多个记录,，,，则会产,生,生,TOO_MANY_ROWS,异常。,INTO,句子后的,变,变量用于,接,接收查询,的,的结果，,变,变量的个,数,数、顺序,应,应该与查,询,询的目标,数,数据相匹,配,配，也可,以,以是记录,类,类型的变,量,量。,59,用,SELECT,INTO,语句查询,10,号部门所,有,有员工信,息,息。,DECLARE,v_emp emp%ROWTYPE;,BEGIN,SELECT *INTO v_empFROMempWHERE deptno=10;,END;,/,*,ERROR,位于第,1,行,:,ORA-01422:,实际返回,的,的行数超,出,出请求的,行,行数,ORA-06512:,在,line4,60,DML,语句,PL/SQL,中,DML,语句对标,准,准,SQL,语句中的,DML,语句进行,了,了扩展，,允,允许使用,变,变量。,DECLARE,v_empnoemp.empno%TYPE :=7500;,BEGIN,INSERT INTOemp(empno,ename,sal,deptno),VALUES(v_empno,JOAN,2300,20);,UPDATE emp SET sal=sal+100,WHERE empno=v_empno;,DELETE FROMempWHERE empno=v_empno;,END;,61,WHERE,标识符的,区,区分,系统首先,查,查看,WHERE,子句中的,标,标识符是,否,否与表中,的,的列名相,同,同，如果,相,相同，则,该,该标识符,被,被解释为,列,列名；如,果,果没有同,名,名列，系,统,统检查该,标,标识符是,不,不是,PL/SQL,语句块的,变,变量。,字符串比,较,较,填充比较,：,：通过在,短,短字符串,后,后添加空,格,格，使两,个,个字符串,达,达到相同,长,长度，然,后,后根据每,个,个字符的,ASCII,码进行比,较,较。,非填充比,较,较：根据,每,每个字符,的,的,ASCII,码进行比,较,较，最先,结,结束的字,符,符串为小,。,。,62,那么何时,采,采用填充,比,比较，何,时,时采用非,填,填充比较,呢,呢？,PL/SQL,中规定，,对,对定长的,字,字符串（,CHAR,类型的字,符,符串和字,符,符串常量,）,）采用填,充,充比较；,如,如果比较,的,的字符串,中,中有一个,是,是变长字,符,符串（,VARCHAR2,类型的字,符,符串），,则,则采用非,填,填充比较,。,。,63,例如，已,知,知,emp,表中,ename,列类型为,VARCHAR2,（,10,），执行,下,下面的代,码,码。,DECLARE,v_enameCHAR(10):=TURNER;,-v_ename VARCHAR2(20);,-v_ename emp.ename%TYPE:=TURNER;,v_salemp.sal%TYPE;,BEGIN,SELECT sal INTOv_sal FROM emp WHEREename=v_ename;,dbms_output.put_line(v_sal);,END;,/,DECLARE,*,第,1,行出现错,误,误,:,ORA-01403:,未找到数,据,据,ORA-06512:,在,line6,64,产生错误,的,的原因是,VARCHAR2,（,10,）类型与,CHAR,（,10,）类型比,较,较时采用,非,非填充比,较,较，因此,无,无法查询,到,到员工名,为,为,“,TURNER,”,的员工。,可,可以将,v_ename,变量类型,修,修改为,VARCHAR2,（,10,）类型，,也,也可以直,接,接采用,emp.ename%TYPE,方式定义,。,。,因此，为,了,了保证程,序,序的正确,执,执行，一,定,定要使,PL/SQL,语句块中,的,的变量与,要,要比较的,数,数据库列,拥,拥有相同,的,的数据类,型,型，可以,使,使用,%TYPE,或,%ROWTYPE,来定义变,量,量。,65,RETURNING,如果要查,询,询当前,DML,语句操作,的,的记录的,信,信息，可,以,以在,DML,语句末尾,使,使用,RETURNING,语句返回,该,该记录的,信,信息。,RETURNING,语句的基,本,本语法：,RETURNING select_list_item INTOvariable_list|record_variable;,66,DECLARE,v_sal emp.sal%TYPE;,BEGIN,UPDATE emp SET sal=sal+100 WHEREempno=7844,RETURNING sal INTO v_sal;,DBMS_OUTPUT.PUT_LINE(v_sal);,END;,67,14.3,控制结构,选择结构,循环结构,跳转结构,68,14.3.1,选择结构,IF,语句,CASE,语句,69,（,1,）,IF,语句,语法,IF condition1THEN statements1;,ELSIFcondition2 THEN statements2;,ELSE else_statements;,END IF;,注意,条件是一个布尔,型,型变量或表达式,，,，取值只能是,TRUE,，,FALSE,，,NULL,。,70,例如，输入一个,员,员工号，修改该,员,员工的工资，如,果,果该员工为,10,号部门，工资增,加,加,100,；若为,20,号部门，工资增,加,加,160,；若为,30,号部门，工资增,加,加,200,；否则增加,300,。,71,DECLARE,v_deptno emp.deptno%type;,v_increment NUMBER(4);,v_empnoemp.empno%type;,BEGIN,v_empno:=,SELECTdeptnoINTO v_deptnoFROM emp,WHERE empno=v_empno;,IF v_deptno=10THEN v_increment:=100;,ELSIF v_deptno=20 THEN v_increment:=160;,ELSIF v_deptno=30 THEN v_increment:=200;,ELSE v_increment:=300;,END IF;,UPDATEemp SETsal=sal+v_increment,WHERE empno=v_empno;,END;,72,由于,PL/SQL,中的逻辑运算结,果,果有,TRUE,，,FALSE,和,NULL,三种，因此在进,行,行选择条件判断,时,时，要考虑条件,为,为,NULL,的情况。例如，,下,下面两个程序，,如,如果不考虑条件,为,为,NULL,的情况，则运行,结,结果是一致的，,但,但是若考虑条件,为,为,NULL,的情况，则结果,就,就不同了。,73,74,为了避免条件为,NULL,时出现歧义，应,该,该在程序中进行,条,条件是否为,NULL,的检查。,75,（,2,）,CASE,语句,基本语法,CASE,WHEN condition1 THENstatements1;,WHEN condition2 THENstatements2;,WHEN conditionn THENstatementsn;,ELSEelse_statements;,END CASE;,注意,在,CASE,语句中，当第一,个,个,WHEN,条件为真时，执,行,行其后的操作，,操,操作完后结束,CASE,语句。其他的,WHEN,条件不再判断，,其,其后的操作也不,执,执行。,76,根据输入的员,工,工号，修改该,员,员工工资。如,果,果该员工工资,低,低于,1000,，则工资增加,200,；如果工资在,1000,2000,之间，则增加,150,；如果工资在,2000,3000,之间，则增加,100,；否则增加,50,。,77,DECLARE,v_salemp.sal%type;,v_incrementNUMBER(4);,v_empno emp.empno%type;,BEGIN,v_empno:=,SELECTsal INTO v_sal FROM emp,WHEREempno=v_empno;,CASE,WHEN v_sal1000 THEN v_increment:=200;,WHEN v_sal2000 THEN v_increment:=150;,WHEN v_sal 50;,END LOOP;,END;,83,（,2,）,WHILE,循环,基本语法,WHILEcondition LOOP,sequence_of_statement;,END LOOP;,84,利用,WHILE,循环向,temp_table,表中插入,50,条记录。,DECLARE,v_counter BINARY_INTEGER:=1;,BEGIN,WHILEv_counter ,INSERTINTOtemp_table VALUES(v_counter,Loopindex);,v_counter :=v_Counter +1;,IF v_counter=50 THEN,GOTOLABEL;,END IF;,END;,89,14.4,游标,游标的概念及,类,类型,显式游标,隐式游标,游标变量,90,14.4.1,游标的,概念,及类型,游标的,概念,游标（,CURSOR,）是,Oracle,系统在内存中,开,开辟的一个工,作,作区，在其中,存,存放,SELECT,语句返回的查,询,询结果。,使用游标时，,SELECT,语句查询的结,果,果可以是单条,记,记录，多条记,录,录，也可以是,零,零条记录。,游标工作区中,，,，存在着一个,指,指针（,POINTER,）,在初始状态它,指,指向查询结果,的,的首记录。,91,游标的类型,显式游标,由用户定义、,操,操作，用于处,理,理返回多行数,据,据的,SELECT,查询。,隐式游标,由系统自动进,行,行操作，用于,处,处理,DML,语句和返回单,行,行数据的,SELECT,查询。,92,14.4.2,显式游标,显式游标的操,作,作,显式游标的属,性,性,参数化显式游,标,标,显式游标的检,索,索,利用游标更新,或,或删除数据,93,（,1,）显式游标的,操,操作,步骤,定义游标,打开,游,游标,检索,游,游标,关闭,游,游标,94,定义,游,游标,语法,CURSORcursor_nameISselect_statement;,说明,游标,必,必须,在,在,PL/SQL,块的,声,声明,部,部分,进,进行,定,定义,；,；,游标,定,定义,时,时可,以,以引,用,用,PL/SQL,变量,，,，但,变,变量,必,必须,在,在游,标,标定,义,义之,前,前定,义,义；,定义,游,游标,时,时并,没,没有,生,生成,数,数据,，,，只,是,是将,定,定义,信,信息,保,保存,到,到数,据,据字,典,典中,；,；,游标,定,定义,后,后，,可,可以,使,使用,cursor_name%ROWTYPE,定义,游,游标,类,类型,变,变量,。,。,95,打开,游,游标,语法,OPENcursor_name;,说明,检查,变,变量,的,的值,执行,游,游标,定,定义,时,时对,应,应的,SELECT,语句,，,，将,查,查询,结,结果,检,检索,到,到工,作,作区,中,中。,游标,指,指针,指,指向,第,第一,个,个元,组,组,一旦,游,游标,打,打开,，,，就,无,无法,再,再次,打,打开,，,，除,非,非先,关,关闭,如果,游,游标,定,定义,中,中的,变,变量,值,值发,生,生变,化,化，,则,则只,能,能在,下,下次,打,打开,游,游标,时,时才,起,起作,用,用。,96,检索,游,游标,语法,格,格式,FETCHcursor_nameINTOvariable_list|record_variable;,说明,在使,用,用,FETCH,语句,之,之前,必,必须,先,先打,开,开游,标,标,对游,标,标第,一,一次,使,使用,FETCH,语句,时,时，,游,游标,指,指针,指,指向,第,第一,条,条记,录,录，,因,因此,操,操作,的,的对,象,象是,第,第一,条,条记,录,录，,使,使用,后,后，,游,游标,指,指针,指,指向,下,下一,条,条记,录,录。,游标,指,指针,只,只能,向,向下,移,移动,，,，不,能,能回,退,退,INTO,子句,中,中的,变,变量,个,个数,、,、顺,序,序、,数,数据,类,类型,必,必须,与,与工,作,作区,中,中每,行,行记,录,录的,字,字段,数,数、,顺,顺序,以,以及,数,数据,类,类型,一,一一,对,对应,。,。,97,关闭,游,游标,语法,格,格式,CLOSEcursor_name;,说明,游标,所,所对,应,应的,内,内存,工,工作,区,区变,为,为无,效,效，,释,释放,与,与游,标,标相,关,关的,系,系统,资,资源,。,。,98,根据,输,输入,的,的部,门,门号,查,查询,某,某个,部,部门,的,的员,工,工信,息,息，,部,部门,号,号在,程,程序,运,运行,时,时指,定,定。,99,DECLARE,v_deptno emp.deptno%TYPE;,CURSORc_emp IS SELECT * FROM emp WHERE deptno=v_deptno;,v_emp c_emp%ROWTYPE;,BEGIN,v_deptno:=,OPEN c_emp;,LOOP,FETCH c_emp INTO v_emp;,EXIT WHEN c_emp%NOTFOUND;,DBMS_OUTPUT.PUT_LINE(v_emp.empno|,v_emp.ename|,v_emp.sal |,v_deptno);,END LOOP;,CLOSE c_emp;,END;,100,（,2,）显式游标的属,性,性,%ISOPEN,布尔型。如果游,标,标已经打开，返,回,回,TRUE,否则为,FALSE,。,%FOUND,布尔型，如果最,近,近一次使用,FETCH,语句，有返回结,果,果则为,TRUE,否则为,FALSE;,%NOTFOUND,布尔型，如果最,近,近一次使用,FETCH,语句,没有返回结果则,为,为,TRUE,否则为,FALSE;,%RO