dba经典辅助学习13a过程sql语言-pl

1、过程语言过程语言 PL/SQL第一节第一节PL/SQL简介简介 第二节第二节PL/SQL块的组成块的组成第三节第三节PL/SQL流程控制流程控制第四节第四节 PL/SQL的异常处理的异常处理第五节第五节PL/SQL示例示例过程语言过程语言 PL/SQL(一）一）第一节第一节PL/SQL简介简介 第二节第二节PL/SQL块的组成块的组成第三节第三节PL/SQL流程控制流程控制第一节PL/SQL简介SQL: 指的是标准结构化查询语言；指的是标准结构化查询语言；PL/SQL语言语言: 是是Oracle RDBMS的一个组成部分的一个组成部分,其中其中PL指的指的是过程化语言（是过程化语言（Proce

2、dure Language），），PL/SQL语言是在语言是在SQL语言中结合了结构化语言成分的准第四代编程语言语言中结合了结构化语言成分的准第四代编程语言,是对是对SQL语言的扩展。语言的扩展。PL/SQL语句块中可以嵌入语句块中可以嵌入SQL的数据操纵及数据查询语句的数据操纵及数据查询语句 使用使用PL/SQL的目的：由于大多数的目的：由于大多数PL/SQL是在服务器是在服务器端运行，这样可减少由客户端运行程序时所需的网络数端运行，这样可减少由客户端运行程序时所需的网络数据流量。据流量。 使用使用PL/SQL的地方：的地方： PL/SQL可以单独进行程序的编写，完成一般的处可以单独进行程序

3、的编写，完成一般的处理功能；理功能； 在高级语言中可嵌入在高级语言中可嵌入PL/SQL块；块； 在第在第3代语言代语言(如如C语言等语言等)中可以嵌入中可以嵌入PL/SQL块；块；PL/SQL的优点的优点集成性集成性PL/SQL的优点的优点应用应用其它其它 DBMS应用应用Oracle PL/SQL提高性能提高性能PL/SQL 的体系结构的体系结构SQL 语句执行器语句执行器主程序或主程序或 ORACLE 工具工具匿名匿名 PL/SQL 块块匿名匿名 PL/SQL 块块过程语句过程语句执行器执行器ORACLE 服务器服务器PL/SQL 引擎引擎过程语句过程语句SQL语句语句 PL/SQL 的体

4、系结构的体系结构 PL/SQL 引擎是主要组件，它驻留在 Oracle 服务器中 PL/SQL 引擎 处理 PL/SQL 块并将其分离为 SQL 语句及过程语句 将过程语句发送到过程语句执行器以进行处理 将 SQL 语句发送到 SQL 语句执行器以进行处理过程语言过程语言 PL/SQL(一）一）第一节第一节PL/SQL简介简介 第二节第二节PL/SQL块的组成块的组成第三节第三节PL/SQL流程控制流程控制第二节程序块结构语言第二节程序块结构语言PL/SQL的组成的组成P L / S Q L块的类型：命名块和匿名块。块的类型：命名块和匿名块。PL/SQL块的组成块的组成PL/SQL语言以块为单

5、位，块中可以嵌套子块。语言以块为单位，块中可以嵌套子块。 一个基本的一个基本的PL/SQL块由块由3部分组成：定义部分（部分组成：定义部分（DECLARE），可执行部分（），可执行部分（BEGIN），异常处理部分），异常处理部分（EXCEPTION）。）。 PL/SQL 语句块结构语句块结构DECLARE 可选项可选项块的定义部分，定义块的定义部分，定义PL/SQL变量、游标、用户定义异变量、游标、用户定义异常常BEGIN 必须的必须的是块中最重要的部分，必须至少包含一条下列语句是块中最重要的部分，必须至少包含一条下列语句SQL语句语句PL/SQL 语句语句EXCEPTION 可选可选当错误发

6、生时的处理动作当错误发生时的处理动作END; 必须有的必须有的示例示例DECLARE v_date_of_joining DATE;BEGIN SELECT hiredate INTO v_date_of_joining FROM emp WHERE empno = 7698; EXCEPTION WHEN no_data_found THEN DBMS_OUTPUT.PUT_LINE (No data found);END;块类型块类型匿名匿名 过程过程 函数函数一、定义部分一、定义部分变量的定义：是一个存储空间，是在程序中取出数据或变量的定义：是一个存储空间，是在程序中取出数据或传入数据给

7、程序。传入数据给程序。变量的名称：必须是合法的标识符变量的名称：必须是合法的标识符变量的作用变量的作用:存放暂时的数据存放暂时的数据操纵存储的值操纵存储的值可重用可重用简化维护简化维护变量的名称变量的名称变量的名称：必须是合法的标识符变量的名称：必须是合法的标识符标识符：标识符：必须以字母开头必须以字母开头可以由一个或多个字母、数字、或特殊字符（可以由一个或多个字母、数字、或特殊字符（$、_）组成）组成长度不超过长度不超过30个字符个字符名内不能有空格名内不能有空格定义变量名定义变量名不可以为保留字不可以为保留字有意义有意义PL/SQL变量的习惯格式变量的习惯格式V_VariableName

8、程序变量程序变量E_ExceptionName 用户自定义异常用户自定义异常T_TypeName 用户自定义类型变量用户自定义类型变量P_ParameterName 存储过程或函数参数变量存储过程或函数参数变量C_ConstantValue 用用CONSTANT关键字限制的变量关键字限制的变量变量类型变量类型PL/SQL变量变量:标量标量记录记录参照变量参照变量LOB(大对象大对象)非非PL/SQL变量变量:绑定变量绑定变量在在 PL/SQL中操纵变量中操纵变量在声明部分声明和创建变量在声明部分声明和创建变量.在可执行部分为变量赋值在可执行部分为变量赋值.通过参数为通过参数为 PL/SQL块传

9、送值块传送值.通过输出变量查看结果通过输出变量查看结果.标量标量格式：格式： CONSTANT NOT NULL DEFAULT|:= ;说明说明在在PL/SQL中使用的变量、常量、游标和异常处理的名中使用的变量、常量、游标和异常处理的名字都必须先定义后使用。字都必须先定义后使用。定义部分是包括在关键字定义部分是包括在关键字DECLARE和和BEGIN之间的部之间的部分，每条语句后用分，每条语句后用；结束。结束。每行只能定义一个标识符。每行只能定义一个标识符。如果加上关键字如果加上关键字CONSTANT,则表示所定义的标识符为则表示所定义的标识符为一个常量，必须为它赋初值。一个常量，必须为它赋

10、初值。标识符的命名规则与标识符的命名规则与SQL的规则基本相同，即每个标识的规则基本相同，即每个标识符必须以字母开头，而且不分大小写。如果定义的标识符必须以字母开头，而且不分大小写。如果定义的标识符不能为空，则必须加上关键字符不能为空，则必须加上关键字NOT NULL,并赋初值并赋初值。为标识符赋值时，使用赋值符号为标识符赋值时，使用赋值符号：=，默认值为空，默认值为空。Oracle 中常用的基本数据类型中常用的基本数据类型数据类型数据类型子类型及备注子类型及备注标量标量类型类型数值数值BINARY_INTEGERNATURAL,POSITIVENUMBERDEC,DECIMAL,DOUBLE

11、 PRECISION,PLOAT,INTEGER,INT,NUMERIC,REAL,SMALLINT 字符字符CHARCHARACTER,STRING/固定长度字符数据固定长度字符数据VARCHAR2VARCHAR/变长字符类型最大为变长字符类型最大为32767，列值，列值LONG可变长字符数据最大长度为可变长字符数据最大长度为32760LONG RAWRAW固定长度的二进制数据固定长度的二进制数据RAWID伪列，用于存储行标识符伪列，用于存储行标识符逻辑逻辑BOOLEANTRUE，FALSE，NULL日期日期DATE4712年年组合组合类型类型记录记录RECORD可定义记录里域的名字、类型、

12、初始值可定义记录里域的名字、类型、初始值表表TABLE定义表的类型和索引类型定义表的类型和索引类型常用的杂符号常用的杂符号示例示例identifier CONSTANT datatype NOT NULL := | DEFAULT expr;Declare v_hiredateDATE DEFAULT SYSDATE; v_deptnoNUMBER(2) NOT NULL := 10; v_locationVARCHAR2(13) := Atlanta; mCONSTANT NUMBER := 1400; 声明声明PL/SQL 变量变量为一个变量赋值为一个变量赋值v_ename := Madu

13、ro;v_hiredate := 31-DEC-98;identifier := expr;变量初始化和关键字变量初始化和关键字使用方法使用方法:赋值操作符赋值操作符 (:=)DEFAULT关键字关键字NOT NULL 约束约束记录类型与记录型变量记录类型与记录型变量记录类型定义的一般格式：记录类型定义的一般格式：TYPE IS RECORD( NOT NULLDEFAULT|:= , )；记录型变量记录型变量 说明说明标识符标识符 是定义的记录类型名是定义的记录类型名;定义记录型变量，定义方法与前面标量型变量定义一样定义记录型变量，定义方法与前面标量型变量定义一样。记录类型变量的属性引用方法

14、是记录类型变量的属性引用方法是.引用。引用。示例示例1、声明记录类型和记录类型变量、声明记录类型和记录类型变量DECLARE TYPE student IS RECORD (id NUMBER(4) NOT NULL default 0, name CHAR(10) , sex BOOLEAN NOT NULL default true, birthdate DATE, physics NUMBER(3), chemistry NUMBER(3);-下面定义一个下面定义一个student类型的变量类型的变量 student1 student;BEGIN END绑定变量绑定变量二、可执行部分二、

15、可执行部分 组成语句：组成语句：变量赋值语句；变量赋值语句；流程控制语句；流程控制语句；数据查询、数据操纵和事务控制语句；数据查询、数据操纵和事务控制语句； 游标语句；游标语句；SQLSQL语句的使用语句的使用在可执行部分，可以使用在可执行部分，可以使用SQL语句，但是不是所有的语句，但是不是所有的S Q L 语 句 都 可 以 使 用 。 可 以 使 用 的 主 要 有 ：语 句 都 可 以 使 用 。 可 以 使 用 的 主 要 有 ：SELECT,INSERT,UPDATE,DELETE,COMMIT,ROLLBACK等数据查询、数据操纵或事务控制命令，不能使用等数据查询、数据操纵或事务

16、控制命令，不能使用CREATE,ALTER,DROP,GRANT,REVOKE等数据定义等数据定义和数据控制命令。和数据控制命令。说明说明在在PL/SQL中，中，SELECT语句必须与语句必须与INTO子句相配合，子句相配合，在在INTO子句后面跟需要赋值的变量。子句后面跟需要赋值的变量。在使用在使用SELECT INTO时，结果只能有一条，如果返时，结果只能有一条，如果返回了多条数据或没有数据，则将产生错误。（对于多条回了多条数据或没有数据，则将产生错误。（对于多条记录的遍历，可以使用游标）记录的遍历，可以使用游标）在在PL/SQL中，中，SQL语句的语法和交互命令时是一样的语句的语法和交互

17、命令时是一样的。 示例示例11查询查询EMP中中EMPNO=7876的雇员的信息。的雇员的信息。SET SERVEROUTPUT ONDECLARE-定义一个记录类型变量定义一个记录类型变量EMP_VALUE EMP%ROWTYPE;BEGINSELECT * INTO emp_valueFROM empWHERE empno=7876;-输出变量的值输出变量的值DBMS_OUTPUT.PUT_LINE(emp_value.ename);END;/示例示例3删除员工名字为删除员工名字为KING的员工信息。的员工信息。DECLARE v_ename VARCHAR2(10) := KING;BE

18、GIN DELETE FROM emp WHERE ename = v_ename;END;嵌入块嵌入块主块主块子块子块示例示例DECLARE a CHAR(15);BEGIN a := Hello World DECLARE b NUMBER(5,2); BEGIN b := 50; dbms_output.put_line(b); END; dbms_output.put_line(a);END;过程语言过程语言 PL/SQL(一）一）第一节第一节PL/SQL简介简介 第二节第二节PL/SQL块的组成块的组成第三节第三节PL/SQL流程控制流程控制第三节第三节PL/SQLPL/SQL的流程

19、控制的流程控制 条件控制条件控制循环控制循环控制跳转控制跳转控制4.3.14.3.1条件控制条件控制IF_THEN_ELSEIF_THEN_ELSE语句语句语法格式：语法格式：IF IF THEN THEN 1ELSEELSE 2END IF;END IF;功能：功能：如果条件成立，将执行如果条件成立，将执行 1，否，否则执行则执行 2，执行完后，转到下，执行完后，转到下一条语句执行。一条语句执行。说明：说明： 是一个布尔型变量或表达式，是一个布尔型变量或表达式，取值只能是取值只能是TRUETRUE，FALSEFALSE，NULLNULL。执行流程执行流程IF_THEN_ELSIFIF_THE

20、N_ELSIF语句语句语法格式：语法格式：IF IF THEN1 THEN 1ELSIF ELSIF THEN2 THEN 2ELSIF ELSIF THENn THEN nELSEELSE n+1END IF;END IF;功能：功能：如果如果 1成立，将执行成立，将执行 1；否则判断；否则判断 2，如果成立执行，如果成立执行 2；否则，判断；否则，判断 3，如此，如此循环，直到判断循环，直到判断 n，如果都不成，如果都不成立，则执行执行立，则执行执行 n+1，执行完，执行完后，转到下一条语句执行后，转到下一条语句执行 。说明：说明：ELSIFELSIF不要误写为不要误写为ELSEIFELS

21、EIF。执行流程执行流程 示例示例11根据表根据表empemp中中DEPTNODEPTNO字段的值，为姓名为字段的值，为姓名为SMITHSMITH的雇员修改工资的雇员修改工资；若部门号为；若部门号为1010，则工资加，则工资加100100；若部门号为；若部门号为2020，则工资加，则工资加300300；否则工资加；否则工资加400400。 declaredeclareincrement emp.sal%type;increment emp.sal%type;fandept emp.deptno%type;fandept emp.deptno%type;beginbeginselect dept

22、no into fandept from emp where ename = select deptno into fandept from emp where ename = SMITH;SMITH;if fandept = 10 then if fandept = 10 then increment :=200;increment :=200;elsif fandept = 20 thenelsif fandept = 20 thenincrement :=300;increment :=300;else else increment :=400;increment :=400;end i

23、f;end if;update emp set sal = sal +increment where ename = update emp set sal = sal +increment where ename = SMITH;SMITH;commit;commit;end;end;/ /示例示例2DECLARE v_empno emp.empno%TYPE := &p_empno; v_deptno emp.deptno%TYPE ;BEGIN SELECT deptno INTO v_deptno FROM emp WHERE empno = v_empno; IF v_dept

24、no = 10 THEN UPDATE emp SET mgr = 7782 WHERE empno = v_empno; END IF;END;示例示例3ACCEPT p_empno PROMPT Please enter the employee number: DECLARE v_empno emp.empno%TYPE := &p_empno; v_job emp.job%TYPE ;BEGIN SELECT job INTO v_job FROM emp WHERE empno = v_empno; IF v_job = CLERK THEN UPDATE emp SET s

25、al = sal + (sal * 0.5) WHERE empno = v_empno; ELSE UPDATE emp SET sal = sal + (sal * 0.1) WHERE empno = v_empno; END IF;END;示例示例4DECLARE v_empno emp.empno%TYPE := &p_empno; v_deptno emp.deptno%TYPE ; v_bonus number (5,2);BEGIN SELECT deptno INTO v_deptno FROM emp WHERE empno = v_empno; IF v_dept

26、no= 10 THEN v_bonus := 500; ELSIF v_deptno = 20 THEN v_bonus := 800; ELSE v_bonus := 1000; END IF;END;4.3.2循环控制语句循环控制语句基本循环基本循环FOR循环循环WHILE循环循环基本循环基本循环(直到型）直到型）FOR循环循环语法格式：语法格式：FOR IN REVERSE LOOPEND LOOP;说明：说明：循环变量是控制循环的变量，它不需要显式的在变量定义部分循环变量是控制循环的变量，它不需要显式的在变量定义部分进行定义。系统隐含地将它看成一个整型变量。进行定义。系统隐含地将它看成

27、一个整型变量。系统默认时，计数器从下界往上界递增计数，如果使用系统默认时，计数器从下界往上界递增计数，如果使用REVERSE关键字，则表示计数器从下界到上界递减计数。关键字，则表示计数器从下界到上界递减计数。循环变量只能在循环体中使用，不能在循环体外使用。循环变量只能在循环体中使用，不能在循环体外使用。示例示例DECLARE v_ordid item.ordid%type:=601; BEGIN FOR v_counter IN 1.10 LOOP INSERT I(NTO item (ordid,itemid) VALUES(v_ordid, v_counter); END LOOP;END

28、;WHILEWHILE循环循环语法格式：语法格式：WHILE LOOPEND LOOP;执行过程：执行过程：先判断先判断，如果条件为真，则执行循环体，如果条件为真，则执行循环体，继续循环，否则结束循环。，继续循环，否则结束循环。说明：说明：当型循环的循环体可能一次也不执行。当型循环的循环体可能一次也不执行。示例示例DECLARE v_ordid item.ordid%type:=601; v_counter number(2) :=1; BEGIN WHILE v_counter =10 LOOP INSERT I(NTO item (ordid,itemid) VALUES(v_ordid,

29、 v_counter); v_counter:=v_counter+1; END LOOP;END;4.3.2循环控制语句循环控制语句循环的嵌套循环的嵌套三种基本的循环语句可以进行嵌套。即一三种基本的循环语句可以进行嵌套。即一个循环可以作为一个普通的语句，完全地个循环可以作为一个普通的语句，完全地嵌套到另一个循环之中。嵌套到另一个循环之中。需要注意的是，嵌套的时候，一个循环必需要注意的是，嵌套的时候，一个循环必须完整的嵌套在另一个循环里，否则是错须完整的嵌套在另一个循环里，否则是错误的。误的。示例示例DECLAREt1 number(10):=0;t2 number(10):=0;t3 num

30、ber(10):=0;j number(10):=0;beginwhile(j100) loopj:=j+1;for i in 1.500 loopt1:=dbms_random.value(0,10000);t2:=dbms_random.value(0,10000);t3:=dbms_random.value(0,10000);insert into tt values(t1,t2,t3);commit;end loop;end loop;end;4.3.3标号和跳转语句标号和跳转语句语句标号的使用语句标号的使用在循环中的语法格式：在循环中的语法格式：标号标号LOOPEXIT 标号标号 W

31、HEN ；END LOOP 标号标号 ;GOTO语句语句语法格式：语法格式：标号标号GOTO 标号；标号；说明：说明：块内可以跳转，内层块可以跳到外层块，但外层块不能跳到内层。块内可以跳转，内层块可以跳到外层块，但外层块不能跳到内层。IF语句不能跳入。不能从循环体外跳入循环体内。不能从子程序外部跳到子程序中。语句不能跳入。不能从循环体外跳入循环体内。不能从子程序外部跳到子程序中。由于由于goto语句的缺点，建议尽量少用甚至不用语句的缺点，建议尽量少用甚至不用goto语句。语句。 4.3.3标号和跳转语句标号和跳转语句示例示例 采用采用GOTO求求1100的和。的和。 variable summ

32、 numberdeclareI number(3):=100;begin:summ:=0;:summ:=:summ + i;I:= I - 1;if I0 then goto label;end if;end;/ print sum;过程语言过程语言 PL/SQL第一节第一节PL/SQL简介简介 第二节第二节PL/SQL块的组成块的组成第三节第三节PL/SQL流程控制流程控制第四节第四节 PL/SQL的异常处理的异常处理第五节第五节PL/SQL示例示例过程语言过程语言 PL/SQL（二）（二）第四节第四节 PL/SQL的异常处理的异常处理第五节第五节PL/SQL示例示例第四节第四节PL/SQL

33、中的异常处理中的异常处理 异常是什么异常是什么?PL/SQL 在执行的过程当中所产生的标识符在执行的过程当中所产生的标识符。 它是怎样产生的它是怎样产生的? 出现出现Oracle 错误错误 使用使用raise语句，来明确地产生语句，来明确地产生. 怎样对它进行处理怎样对它进行处理? 使用处理句柄来捕获它使用处理句柄来捕获它. 向调用时的环境进行传递向调用时的环境进行传递.PL/SQL中的异常处理中的异常处理预定义异常预定义异常对于对于Oracle预定义的异常，当预定义的情况发生时，系统将预定义的异常，当预定义的情况发生时，系统将自动触发。自动触发。用户自定义的异常用户自定义的异常需要程序员自己定义代码，对异常情况进行处理。需要程序员自己定义代码，对异常情况进行处理。 异常处理的一般格式：异常处理的一般格式：DECLARE ;BEGIN ;EXCEPTION WHEN 异常情况异常情况1 OR 异常情况异常情况2 THEN ; WHEN异常情况异常情况3 OR 异常情况异常情况4 THEN ； WHEN OTHERS THEN ；END;4.4.1预定义的异常处理预定义的异常处理 异常情况名异常情况名错误代码错误代码描述描述DUP_VAL_ON_INDEXORA-00001试图更新或插入重复记录试图更新或插入重复记录INVALID_CURSORORA-01001非法游标操作