【教学课件】第三讲PLSQL编程基础.ppt

《【教学课件】第三讲PLSQL编程基础.ppt》由会员分享，可在线阅读，更多相关《【教学课件】第三讲PLSQL编程基础.ppt（67页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第三讲第三讲 PL/SQL PL/SQL编程基础编程基础本章学习目标本章学习目标 本章将讲述本章将讲述PL/SQL基础语法，结构和组基础语法，结构和组件、以及如何设计并执行一个件、以及如何设计并执行一个PL/SQL程程序。序。本章内容安排本章内容安排n3.1 PL/SQL简介简介n3.2 PL/SQL语法语法n3.3 游标游标3.1 PL/SQL简介简介 PL/SQL，即模块式的过程化，即模块式的过程化SQLn模块化结构模块化结构n定义标识符定义标识符n用过程化语言控制结构进行程序设计用过程化语言控制结构进行程序设计n错误处理错误处理n高性能高性能n可移植可移植n n PL/SQL块块n4.2

2、.2 4.2.2 常量与变量常量与变量n4.2.3 4.2.3 数据类型数据类型 n4.2.4 4.2.4 运算符运算符n4.2.5 4.2.5 流程控制流程控制n4.2.6 4.2.6 异常处理异常处理3.2 PL/SQL语法语法3.2.1 PL/SQL块块n声明部分（声明部分（Declaration section）由关键字由关键字DECLARE开始，包含了变量和常量开始，包含了变量和常量的数据类型和初始值。这部分可选的数据类型和初始值。这部分可选n执行部分（执行部分（Executable section）由关键字由关键字BEGIN开始，执行部分是开始，执行部分是PL/SQL块块中的指令部

3、分，所有的可执行语句都放在这一中的指令部分，所有的可执行语句都放在这一部分。部分。n异常处理部分（异常处理部分（Exception section）由关键字由关键字Exception开始，处理异常或错误，开始，处理异常或错误，这一部分是可选的这一部分是可选的头部头部头部头部 程序单元的名称、类型、参数程序单元的名称、类型、参数程序单元的名称、类型、参数程序单元的名称、类型、参数DECLAREDECLARE-可选可选可选可选 变量、常量、游标、用户定义的异常变量、常量、游标、用户定义的异常变量、常量、游标、用户定义的异常变量、常量、游标、用户定义的异常BEGINBEGINSQLSQL语句语句语句

4、语句PL/SQLPL/SQL控制语句控制语句控制语句控制语句EXCEPTION-EXCEPTION-可选可选可选可选发生错误时的处理发生错误时的处理发生错误时的处理发生错误时的处理ENDEND；PL/SQL工作流程工作流程 PL/SQL EnginePL/SQL EngineSQL命令执行器命令执行器PL/SQL块块PL/SQL块块过程化命令过程化命令执行器执行器SQLPL/SQLPL/SQL程序形式程序形式n匿名块匿名块 -动态构造，只执行一次动态构造，只执行一次n存储过程存储过程/函数函数 -可以接收参数，重复调用可以接收参数，重复调用n包包 -由一组相关的过程、函数和标识符组成由一组相关

5、的过程、函数和标识符组成n触发器触发器 -发生数据库操作时触发的事件发生数据库操作时触发的事件 nPL/SQL块中的每一条语句都必须以分号结束，块中的每一条语句都必须以分号结束，SQL语句可以多行，但分号表示该语句的结束。语句可以多行，但分号表示该语句的结束。n一行中可以有多条一行中可以有多条SQL语句，他们之间以分号语句，他们之间以分号分隔。分隔。n每一个每一个PL/SQL块由块由BEGIN或或DECLARE开始，开始，以以END结束。结束。n单行注释由单行注释由-标示。多行注释：以标示。多行注释：以/*开始，以开始，以*/结束结束SQL*PLUS中中块的执行块的执行n匿名匿名PL/SQL块

6、的执行是在块的执行是在PL/SQL块后块后输入输入/来执行。来执行。n命名的程序与匿名程序的执行不同，执命名的程序与匿名程序的执行不同，执行命名的程序块必须使用行命名的程序块必须使用EXECUTE关关键字。键字。3.2.2 常量与变量常量与变量n变量变量n常量常量n标识符标识符变量变量n声明变量声明变量 Variable_name CONSTANT datatype NOT NULL:=|DEFAULT expression 例：例：emp_id varchar2(10)n 注意：可以在声明变量的同时给变量强注意：可以在声明变量的同时给变量强制性的加上制性的加上NOT NULL约束条件，此时约

7、束条件，此时变量在初始化时必须赋值。变量在初始化时必须赋值。变量赋值变量赋值n变量名变量名:表达式表达式 b:=a*3-b;nSELECT 列名列名 INTO 变量名变量名 FROM select avg(salary),sum(salary)into avg_sal,sum_sal from s_emp;nFETCH 游标名游标名 INTO 变量名变量名1,变量名变量名2.;常量常量n常量的值在程序内部不能改变，常量的值在程序内部不能改变，n常量的值在定义时赋予，声明与变量相常量的值在定义时赋予，声明与变量相似，但必须包括关键字似，但必须包括关键字CONSTANT。n常量和变量都可被定义为常

8、量和变量都可被定义为SQL和用户定和用户定义的数据类型。义的数据类型。标识符标识符n不能超过个字符；不能超过个字符；n第一个字符必须为字母；第一个字符必须为字母；n其余字符可以是字母、数字、其余字符可以是字母、数字、_或；或；n不区分大小写形式；不区分大小写形式；n如如果果用用双双引引号号括括起起来来，则则字字符符顺顺序序可可以以任任意意排列，并区分大小写形式；排列，并区分大小写形式；n不能是保留字。不能是保留字。3.2.3 数据类型数据类型n标量类型标量类型n复合类型复合类型n引用类型引用类型标量类型标量类型n数值类型数值类型 BINARY_INTEGER,NUMBERn字符类型字符类型 C

9、HAR,VARCHAR2n日期类型日期类型 DATEn布尔类型布尔类型 BOOLEANn大数据类型大数据类型 BLOB,CLOB,BFILE复合类型复合类型n记录记录 TYPE record_type IS RECORD(field1 type1NOT NULL:=exp1,field2 type2NOT NULL:=exp2,.field3 type3NOT NULL:=exp3);n表表 TYPE table_type IS TABLE OF type INDEX BY BINARY_INTEGER;表的主要属性有：表的主要属性有：COUNTPL/SQL表中元素数表中元素数DELETE删除

10、表中元素删除表中元素EXISTS元素存在与否元素存在与否FIRST第一个元素的索引第一个元素的索引LAST最后元素的索引最后元素的索引NEXT后继元素的索引后继元素的索引PRIOR前一元素的索引前一元素的索引引用类型引用类型n游标变量游标变量n对象引用类型对象引用类型3.2.4 运算符运算符n算术运算符算术运算符 +，-，*，/n关系运算符关系运算符 =，!=，=，200 then goto end_loop;endif;NULL;endloop;n标号后面必须要有可执行的语句。标号后面必须要有可执行的语句。n标号必须在同一组命令中，或是同一块标号必须在同一组命令中，或是同一块中使用。中使用。

11、n不能跳转到不能跳转到IF语句、语句、LOOP语句或子块语句或子块中。中。n不能跳出子程序。不能跳出子程序。n不能从意外跳转到当前块。不能从意外跳转到当前块。3.2.6 异常处理异常处理nPL/SQL的异常的异常n预定义异常预定义异常nOracle server 未定义的异常未定义的异常n自定义异常自定义异常PL/SQL的异常的异常n异常的定义异常的定义 PL/SQL块中定义，可在执行过程中被引发块中定义，可在执行过程中被引发n异常的引发异常的引发Oracle的错误造成的错误造成用户由命令引发用户由命令引发n异常的处理异常的处理用户捕获并处理用户捕获并处理将异常传递到外部调用环境，交互处理将异

12、常传递到外部调用环境，交互处理(DECLARE)(DECLARE)BEGINBEGINEXCEPTIONEXCEPTIONEND;END;异常处理异常处理捕获异常并处理捕获异常并处理 传递异常传递异常(DECLARE)(DECLARE)BEGINBEGINEXCEPTIONEXCEPTIONEND;END;异常引发异常引发异常引发异常引发异常捕获并异常捕获并异常捕获并异常捕获并处理处理处理处理异常引发异常引发异常引发异常引发异常未处理异常未处理异常未处理异常未处理异常传播到调用环境异常传播到调用环境异常传播到调用环境异常传播到调用环境异常传播异常传播n当异常情态在块的执行部分引发当异常情态在块

13、的执行部分引发 （1）若当前块对该异常情态设置了处理器，则）若当前块对该异常情态设置了处理器，则执行它并成功完成该块的执行，然后控制转给执行它并成功完成该块的执行，然后控制转给包含块。包含块。（2）若当前块没有该处理器，则通过在包含块）若当前块没有该处理器，则通过在包含块中引发它来传播异常情态。然后对包含块执行中引发它来传播异常情态。然后对包含块执行PL/SQL的异常操作。的异常操作。n无论是在声明部分引发了一个异常情态，还是无论是在声明部分引发了一个异常情态，还是在异常处理部分引发，则该异常情态将立即传在异常处理部分引发，则该异常情态将立即传播给包含块。即使在当前块设置了播给包含块。即使在当

14、前块设置了OTHERS处理器也不会被执行。处理器也不会被执行。捕获异常捕获异常:语法语法EXCEPTIONEXCEPTION WHEN WHEN exception1exception1 OR OR exception2exception2.THEN.THEN statement1statement1;statement2 statement2;.WHEN WHEN exception3exception3 OR OR exception4exception4.THEN.THEN statement1 statement1;statement2 statement2;.WHEN OTHERS

15、THENWHEN OTHERS THEN statement1 statement1;statement2 statement2;.预定义异常预定义异常Oracle自动引发的预定义异常大约自动引发的预定义异常大约24种：种：nNO_DATA_FOUNDnTOO_MANY_ROWSnINVALID_CURSORnZERO_DIVIDEnDUP_VAL_ON_INDEXnCURSOR_ALREADY_OPEN预定义异常处理预定义异常处理:实例实例PROCEDURE elim_inventoryPROCEDURE elim_inventory(v_product_id IN s_product.id

16、%TYPE)IS(v_product_id IN s_product.id%TYPE)IS v_id v_ids_product.id%TYPE;s_product.id%TYPE;BEGINBEGINSELECTSELECTidid INTOINTOv_idv_idFROMFROMs_products_productWHEREWHEREid=v_product_id;id=v_product_id;DELETE FROMDELETE FROMs_inventorys_inventoryWHEREWHEREproduct_id=v_product_id;product_id=v_product

17、_id;COMMIT;COMMIT;.EXCEPTIONEXCEPTIONWHEN WHEN NO_DATA_FOUND THENTHENROLLBACK;ROLLBACK;DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_product_id)|is DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_product_id)|is invalid.);invalid.);WHEN WHEN TOO_MANY_ROWS THENTHENROLLBACK;ROLLBACK;DBMS_OUTPUT.PUT_LINE(data corruption in DBMS_OUTPUT

18、.PUT_LINE(data corruption in S_PRODUCT.);S_PRODUCT.);WHEN OTHERS THENWHEN OTHERS THENROLLBACK;ROLLBACK;DBMS_OUTPUT.PUT_LINE(Other error occurred.);DBMS_OUTPUT.PUT_LINE(Other error occurred.);END elim_inventory;END elim_inventory;Oracle server 未定义的异常未定义的异常n标准的标准的Oracle错误错误n用户在定义部分定义，用户在定义部分定义，Oracle自

19、动引发自动引发处理处理 Oracle Server 未定义的异常未定义的异常DeclareAssociateReference 定义部分定义部分异常处理部分异常处理部分 命名异常命名异常命名异常命名异常 异常对应代码异常对应代码异常对应代码异常对应代码 pragma pragma EXCEPTION_INITEXCEPTION_INIT处理被引发的处理被引发的处理被引发的处理被引发的异常异常异常异常未定义异常未定义异常:实例实例捕获捕获 Oracle Server-2292 号错误，为其设定号错误，为其设定错误提示信息为错误提示信息为“非法的一致性错误非法的一致性错误”DECLAREDECLA

20、RE e_products_remaininge_products_remainingEXCEPTION;EXCEPTION;PRAGMA EXCEPTION_INIT(PRAGMA EXCEPTION_INIT(e_products_remaining,-2292);e_products_remaining,-2292);.BEGINBEGIN.EXCEPTIONEXCEPTION WHENWHEN e_products_remaininge_products_remaining THENTHEN DBMS_OUTPUT.PUT_LINE(Referential integrityDBMS_

21、OUTPUT.PUT_LINE(Referential integrityconstraint violated.);constraint violated.);.END;END;123用户定义的异常用户定义的异常n程序执行中出现的编程人员认为的非正程序执行中出现的编程人员认为的非正常情况常情况n在程序中显式定义并引发在程序中显式定义并引发处理用户定义的异常处理用户定义的异常DeclareRaiseReference 定义部分定义部分异常处理部分异常处理部分 命名异常命名异常命名异常命名异常使用使用使用使用RAISERAISE语句引发异常语句引发异常语句引发异常语句引发异常处理被引发的异常处理

22、被引发的异常处理被引发的异常处理被引发的异常执行部分执行部分用户定义的异常处理：实例用户定义的异常处理：实例DECLAREDECLARE e_amount_remaining EXCEPTION;e_amount_remaining EXCEPTION;.BEGINBEGIN.RAISE e_amount_remaining;RAISE e_amount_remaining;.EXCEPTIONEXCEPTION WHENWHEN e_amount_remaininge_amount_remaining THENTHEN DBMS_OUTPUT.PUT_LINE(There is still

23、an amount DBMS_OUTPUT.PUT_LINE(There is still an amount in stock.);in stock.);.END;END;123SQLCODE和和SQLERRMnOracle内置函数内置函数n描述错误代码和信息描述错误代码和信息n在在OTHERS异常处理器中特别有用，可异常处理器中特别有用，可以告知用户何种内部错误被引发以告知用户何种内部错误被引发SQLCODE SQLERRM用户定义的异常用户定义的异常+1User-Defined ExceptionOracle定义错误定义错误错误代码错误代码同错误代码相连的信息同错误代码相连的信息没有异常

24、发生没有异常发生0ora-0000:normal,successful completion异常处理的函数异常处理的函数:实例实例转存未处理的异常的错误代码及提示信息转存未处理的异常的错误代码及提示信息.v_error_code NUMBER;v_error_code NUMBER;v_error_message VARCHAR2(255);v_error_message VARCHAR2(255);BEGINBEGIN.EXCEPTIONEXCEPTION.WHEN OTHERS THEN WHEN OTHERS THEN ROLLBACK;ROLLBACK;v_error_code:=v_

25、error_code:=SQLCODESQLCODE;v_error_message:=v_error_message:=SQLERRMSQLERRM;TEXT_IO.PUT_LINE(TO_CHAR(v_error_code)|TEXT_IO.PUT_LINE(TO_CHAR(v_error_code)|:|v_error_message);:|v_error_message);END;END;实例实例DECLARE salary emp.sal%TYPE:=0;last_name emp.ename%TYPE;starting_empno emp.empno%TYPE:=7499;BEGI

26、N WHILE salary 5 EXIT WHEN item_cursor%ROWCOUNT 5OR item_cursor%NOTFOUND;OR item_cursor%NOTFOUND;v_order_total:=v_order_total+v_item_total;v_order_total:=v_order_total+v_item_total;.END LOOP;END LOOP;隐式游标的属性隐式游标的属性SQL%ISOPEN 返回值总为返回值总为FALSESQL%NOTFOUND 如果如果DML操作没有影响任何行操作没有影响任何行,或或select操作没有操作没有获得数据，

27、则返回获得数据，则返回TURESQL%FOUND 如果如果DML操作影响若干行，或操作影响若干行，或select操作获得数据，操作获得数据，则返回则返回TRUESQL%ROWCOUNT返回返回DML操作影响的记录数，或操作影响的记录数，或select操作获得的操作获得的记录数记录数游标应用实例游标应用实例DECLARE my_ename VARCHAR2(10);my_empno NUMBER(4);my_sal NUMBER(7,2);CURSOR c1 is SELECT ename,empno,sal FROM emp ORDER BY sal DESC;BEGIN OPEN c1;FO

28、R j IN 1.5 LOOP FETCH c1 INTO my_ename,my_empno,my_sal;EXIT WHEN c1%NOTFOUND;INSERT INTO temp VALUES(my_sal,my_empno,my_ename);COMMIT;END LOOP;CLOSE c1;END;游标和记录游标和记录可以将活动集合中的记录提取到可以将活动集合中的记录提取到 PL/SQL 的记录中的记录中 CURSOR emp_cursor ISCURSOR emp_cursor IS SELECT SELECTid,salary,start_date,rowidid,salary

29、,start_date,rowid FROM FROMs_emps_emp WHERE WHEREdept_id=41;dept_id=41;emp_record emp_recordemp_cursor%ROWTYPE;emp_cursor%ROWTYPE;BEGINBEGIN OPEN emp_cursor;OPEN emp_cursor;.FETCH emp_cursor INTO emp_record;FETCH emp_cursor INTO emp_record;游标中的游标中的FOR循环循环n简化对显式游标的处理简化对显式游标的处理n游标的打开、提取及关闭是系统隐含游标的打开、提

30、取及关闭是系统隐含完成的完成的nFOR循环中使用记录代替循环变量，循环中使用记录代替循环变量，并且是隐含声明并且是隐含声明FOR FOR record_namerecord_name IN IN cursor_namecursor_name LOOP LOOP statement1statement1;statement2 statement2;.END LOOP;END LOOP;游标中的参数游标中的参数n在打开游标时，可以将参数值传递在打开游标时，可以将参数值传递到带参的查询上到带参的查询上n在打开游标时，参数值不同可以生在打开游标时，参数值不同可以生成不同的游标集合成不同的游标集合CUR

31、SOR CURSOR cursor_namecursor_name (parameter_name datatypeparameter_name datatype,.),.)IS IS select_statementselect_statement;游标中的参数：实例游标中的参数：实例可以在打开游标时传递可以在打开游标时传递DEPT_ID和和TITLE，从而生成不同的游标集合，从而生成不同的游标集合CURSOR emp_cursor CURSOR emp_cursor(v_dept NUMBER,v_job VARCHAR2)IS(v_dept NUMBER,v_job VARCHAR2)I

32、S SELECT SELECTlast_name,salary,start_datelast_name,salary,start_date FROM FROMs_emps_emp WHERE WHEREdept_id=v_dept dept_id=v_dept AND AND title=v_job;title=v_job;DECLARE CURSOR emp_cursor(v_dept NUMBER)IS SELECT sal FROM emp WHERE deptno=v_dept;total_wages NUMER(11,2):=0;high_paid NUMBER(4):=0;BEGIN For emp_record IN emp_cursor(20)LOOP total_wages:=total_wages+emp_record.sal;IF emp_record.sal2000.00 THEN high_paid:=high_paid+1;ENDIF;ENDLOOP;INSERT INTO temp VALUES(high_paid,Total Wages:|TO_CHAR(total_wages);COMMIt;END;