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

《【教学课件】第三讲PLSQL编程基础.ppt》由会员分享，可在线阅读，更多相关《【教学课件】第三讲PLSQL编程基础.ppt（67页珍藏版）》请在三一办公上搜索。

1、第三讲 PL/SQL编程基础,本章学习目标 本章将讲述PL/SQL基础语法，结构和组件、以及如何设计并执行一个PL/SQL程序。,本章内容安排,3.1 PL/SQL简介3.2 PL/SQL语法3.3 游标,3.1 PL/SQL简介,PL/SQL，即模块式的过程化SQL模块化结构定义标识符用过程化语言控制结构进行程序设计错误处理高性能可移植,PL/SQL块4.2.2 常量与变量4.2.3 数据类型 4.2.4 运算符4.2.5 流程控制4.2.6 异常处理,3.2 PL/SQL语法,3.2.1 PL/SQL块,声明部分（Declaration section）由关键字DECLARE开始，包含了变

2、量和常量的数据类型和初始值。这部分可选执行部分（Executable section）由关键字BEGIN开始，执行部分是PL/SQL块中的指令部分，所有的可执行语句都放在这一部分。异常处理部分（Exception section）由关键字Exception开始，处理异常或错误，这一部分是可选的,头部 程序单元的名称、类型、参数DECLARE-可选 变量、常量、游标、用户定义的异常BEGINSQL语句PL/SQL控制语句EXCEPTION-可选发生错误时的处理END；,PL/SQL工作流程,PL/SQL Engine,SQL命令执行器,PL/SQL块,PL/SQL块,过程化命令执行器,SQL,P

3、L/SQL,PL/SQL程序形式,匿名块-动态构造，只执行一次存储过程/函数-可以接收参数，重复调用包-由一组相关的过程、函数和标识符组成触发器-发生数据库操作时触发的事件,PL/SQL块中的每一条语句都必须以分号结束，SQL语句可以多行，但分号表示该语句的结束。一行中可以有多条SQL语句，他们之间以分号分隔。每一个PL/SQL块由BEGIN或DECLARE开始，以END结束。单行注释由-标示。多行注释：以/*开始，以*/结束,SQL*PLUS中块的执行,匿名PL/SQL块的执行是在PL/SQL块后输入/来执行。命名的程序与匿名程序的执行不同，执行命名的程序块必须使用EXECUTE关键字。,3

4、.2.2 常量与变量,变量常量标识符,变量,声明变量 Variable_name CONSTANT datatype NOT NULL:=|DEFAULT expression 例：emp_id varchar2(10)注意：可以在声明变量的同时给变量强制性的加上NOT NULL约束条件，此时变量在初始化时必须赋值。,变量赋值,变量名:表达式 b:=a*3-b;SELECT 列名 INTO 变量名 FROM select avg(salary),sum(salary)into avg_sal,sum_sal from s_emp;FETCH 游标名 INTO 变量名1,变量名2.;,常量,常量

5、的值在程序内部不能改变，常量的值在定义时赋予，声明与变量相似，但必须包括关键字CONSTANT。常量和变量都可被定义为SQL和用户定义的数据类型。,标识符,不能超过个字符；第一个字符必须为字母；其余字符可以是字母、数字、_或；不区分大小写形式；如果用双引号括起来，则字符顺序可以任意排列，并区分大小写形式；不能是保留字。,3.2.3 数据类型,标量类型复合类型引用类型,标量类型,数值类型 BINARY_INTEGER,NUMBER字符类型 CHAR,VARCHAR2日期类型 DATE布尔类型 BOOLEAN大数据类型 BLOB,CLOB,BFILE,复合类型,记录 TYPE record_typ

6、e IS RECORD(field1 type1NOT NULL:=exp1,field2 type2NOT NULL:=exp2,.field3type3NOT NULL:=exp3);,表 TYPE table_type IS TABLE OF type INDEX BY BINARY_INTEGER;表的主要属性有：COUNTPL/SQL表中元素数DELETE删除表中元素EXISTS元素存在与否FIRST第一个元素的索引LAST最后元素的索引NEXT后继元素的索引PRIOR前一元素的索引,引用类型,游标变量对象引用类型,3.2.4 运算符,算术运算符+，-，*，/关系运算符=，!=，=，

7、=逻辑运算符 AND，OR，NOT,3.2.5 流程控制,条件结构循环控制GOTO语句,条件结构,IF 条件 THEN 语句END IF；IF 条件 THEN 语句1ELSE 语句2END IF；,IF 条件1 THEN 语句1ELSIF 条件2 THEN 语句2ELSE 语句3END IF；,循环控制,loopexitend循环控制语句 LOOP 一组语句 END LOOP;这种循环语句是没有终止的，可以通过加入EXIT语句来终结该循环。,例：x:=0;loop x:=x+1;if x=100 then exit;endif;endloop;,WHILE.LOOP循环控制语句 WHILE 条

8、件 LOOP 一组语句END LOOP；,FOR.LOOP循环控制语句 FOR 循环变量 IN REVERSE 初值.终值 LOOP 一组语句 END LOOP；,GOTO语句,x:=5;loop x:=x*1.5;if x200 then goto end_loop;endif;NULL;endloop;,标号后面必须要有可执行的语句。标号必须在同一组命令中，或是同一块中使用。不能跳转到IF语句、LOOP语句或子块中。不能跳出子程序。不能从意外跳转到当前块。,3.2.6 异常处理,PL/SQL的异常预定义异常Oracle server 未定义的异常自定义异常,PL/SQL的异常,异常的定义

9、PL/SQL块中定义，可在执行过程中被引发异常的引发Oracle的错误造成用户由命令引发异常的处理用户捕获并处理将异常传递到外部调用环境，交互处理,(DECLARE)BEGINEXCEPTIONEND;,异常处理,捕获异常并处理,传递异常,(DECLARE)BEGINEXCEPTIONEND;,异常引发,异常捕获并处理,异常引发,异常未处理,异常传播到调用环境,异常传播,当异常情态在块的执行部分引发（1）若当前块对该异常情态设置了处理器，则执行它并成功完成该块的执行，然后控制转给包含块。（2）若当前块没有该处理器，则通过在包含块中引发它来传播异常情态。然后对包含块执行PL/SQL的异常操作。无

10、论是在声明部分引发了一个异常情态，还是在异常处理部分引发，则该异常情态将立即传播给包含块。即使在当前块设置了OTHERS处理器也不会被执行。,捕获异常:语法,EXCEPTION WHEN exception1 OR exception2.THEN statement1;statement2;.WHEN exception3 OR exception4.THEN statement1;statement2;.WHEN OTHERS THEN statement1;statement2;.,预定义异常,Oracle自动引发的预定义异常大约24种：NO_DATA_FOUNDTOO_MANY_ROWS

11、INVALID_CURSORZERO_DIVIDEDUP_VAL_ON_INDEXCURSOR_ALREADY_OPEN,预定义异常处理:实例,PROCEDURE elim_inventory(v_product_id IN s_product.id%TYPE)IS v_ids_product.id%TYPE;BEGINSELECTid INTOv_idFROMs_productWHEREid=v_product_id;DELETE FROMs_inventoryWHEREproduct_id=v_product_id;COMMIT;.,.EXCEPTIONWHEN NO_DATA_FOUND

12、 THENROLLBACK;DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_product_id)|is invalid.);WHEN TOO_MANY_ROWS THENROLLBACK;DBMS_OUTPUT.PUT_LINE(data corruption in S_PRODUCT.);WHEN OTHERS THENROLLBACK;DBMS_OUTPUT.PUT_LINE(Other error occurred.);END elim_inventory;,Oracle server 未定义的异常,标准的Oracle错误用户在定义部分定义，Oracle自动引发,处理 O

13、racle Server 未定义的异常,Declare,Associate,Reference,定义部分,异常处理部分,命名异常,异常对应代码 pragma EXCEPTION_INIT,处理被引发的异常,未定义异常:实例,捕获 Oracle Server-2292 号错误，为其设定错误提示信息为“非法的一致性错误”,DECLARE e_products_remainingEXCEPTION;PRAGMA EXCEPTION_INIT(e_products_remaining,-2292);.BEGIN.EXCEPTION WHEN e_products_remaining THEN DBMS

14、_OUTPUT.PUT_LINE(Referential integrityconstraint violated.);.END;,1,2,3,用户定义的异常,程序执行中出现的编程人员认为的非正常情况在程序中显式定义并引发,处理用户定义的异常,Declare,Raise,Reference,定义部分,异常处理部分,命名异常,使用RAISE语句引发异常,处理被引发的异常,执行部分,用户定义的异常处理：实例,DECLARE e_amount_remaining EXCEPTION;.BEGIN.RAISE e_amount_remaining;.EXCEPTION WHEN e_amount_re

15、maining THEN DBMS_OUTPUT.PUT_LINE(There is still an amount in stock.);.END;,1,2,3,SQLCODE和SQLERRM,Oracle内置函数描述错误代码和信息在OTHERS异常处理器中特别有用，可以告知用户何种内部错误被引发,异常处理的函数:实例,转存未处理的异常的错误代码及提示信息,.v_error_code NUMBER;v_error_message VARCHAR2(255);BEGIN.EXCEPTION.WHEN OTHERS THEN ROLLBACK;v_error_code:=SQLCODE;v_er

16、ror_message:=SQLERRM;TEXT_IO.PUT_LINE(TO_CHAR(v_error_code)|:|v_error_message);END;,实例,DECLARE salary emp.sal%TYPE:=0;last_name emp.ename%TYPE;starting_empno emp.empno%TYPE:=7499;BEGIN WHILE salary=2500 LOOP SELECT sal,ename INTO salary,last_name FROM emp WHERE empno=starting_empno;ENDLOOP;INSERT IN

17、TO temp VALUES(salary,last_name);COMMIT;EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO temp VALUES(NULL,Not found);COMMIT;END;,3.3 游标,Oracle游标是一种用于轻松地处理多行数据的机制。它包含一个跟踪当前访问的记录的指针，这使程序能够一次处理多条记录。,显式游标和隐式游标,显式游标 程序员声明及定义的游标隐式游标 系统为所有DML和 SELECT命令预定义的游标，Oracle隐式的打开SQL游标、处理SQL游标、然后再关闭该游标。,显示游标的控制,声明,提取,打开

18、,关闭,定义游标,激活活动游标集合,提取游标集合中的当前记录,判断游标是否为空如果游标集合中仍有记录，则可以执行提取命令,释放活动集合所占用的空间,No,Yes,是否为空,显式游标的处理,打开游标,Pointer,从游标中获取当前记录,处理游标中的记录直至集合为空,Pointer,Pointer,Cursor,Cursor,Cursor,声明游标,声明游标的语句格式如下：DECLARE cursor_name IS SELECT statement声明游标完成了下面两个目的：（1）给游标命名；（2）将一个查询与游标关联起来。注意：在游标定义中不可以使用INTO子句,定义游标：实例,逐条提取记录

19、值,DECLARE.v_ord_ids_item.ord_id%TYPE;v_product_ids_item.product_id%TYPE;v_item_totalNUMBER(11,2);CURSOR item_cursor IS SELECTproduct_id,price*quantity FROMs_item WHEREord_id=v_ord_id;BEGIN.,打开游标,打开游标的语句格式如下：OPEN cursor_name;OPEN命令激活查询并识别活动集，但并没有真正取回记录。OPEN命令初始化游标指针，使其指向活动集的第一条记录。游标被打开后，直到关闭之前，取回到活动集

20、的所有数据都是静态的。要刷新活动集，只需关闭并重新打开游标即可。,FETCH命令的语句格式如下：FETCH cursor_name INTO variable1,variable2,.;执行FETCH命令后，活动集中的结果被取回到PL/SQL变量中。每取回一条记录，游标的指针就移向活动集的下一条记录。,提取数据,关闭游标,集合中所有记录处理完后应关闭游标CLOSE cursor_name;再次使用前，重新打开游标游标关闭之后不可以提取其中的数据,显式游标的属性,属性类型描述%ISOPENBoolean 游标打开后返回值为TRUE%NOTFOUNDBoolean 最后一次提取游标没有获取相应值，

21、则返回TURE%FOUNDBoolean 提取游标命令获得数据则返回TRUE%ROWCOUNTNumber返回已提取的游标中的记录数,可以使用游标属性获取游标的状态信息,%ISOPEN,游标只有打开后才可以提取其中的记录。在提取记录前，可以使用%ISOPEN属性来判断游标是否已打开,IF item_cursor%ISOPEN THEN FETCH item_cursor INTO v_quantity,v_price;ELSE OPEN item_cursor;END IF;,%NOTFOUND 和%ROWCOUNT,可以使用%ROWCOUNT 获取已提取的记录数可以使用%NOTFOUND 决

22、定何时退出对游标处理的循环,LOOP FETCH item_cursor INTO v_product_id,v_item_total;EXIT WHEN item_cursor%ROWCOUNT 5OR item_cursor%NOTFOUND;v_order_total:=v_order_total+v_item_total;.END LOOP;,隐式游标的属性,SQL%ISOPEN 返回值总为FALSESQL%NOTFOUND 如果DML操作没有影响任何行,或select操作没有获得数据，则返回TURESQL%FOUND 如果DML操作影响若干行，或select操作获得数据，则返回TRU

23、ESQL%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;FOR j IN 1.5 LOOP FETCH c1 INTO my_ename,my_empno,my_sal;EXIT WHEN c1%NOTFOUND;INSERT INTO temp VALUES(my

24、_sal,my_empno,my_ename);COMMIT;END LOOP;CLOSE c1;END;,游标和记录,可以将活动集合中的记录提取到 PL/SQL 的记录中,CURSOR emp_cursor IS SELECTid,salary,start_date,rowid FROMs_emp WHEREdept_id=41;emp_recordemp_cursor%ROWTYPE;BEGIN OPEN emp_cursor;.FETCH emp_cursor INTO emp_record;,游标中的FOR循环,简化对显式游标的处理游标的打开、提取及关闭是系统隐含完成的FOR循环中使用

25、记录代替循环变量，并且是隐含声明,FOR record_name IN cursor_name LOOP statement1;statement2;.END LOOP;,游标中的参数,在打开游标时，可以将参数值传递到带参的查询上在打开游标时，参数值不同可以生成不同的游标集合,CURSOR cursor_name(parameter_name datatype,.)IS select_statement;,游标中的参数：实例,可以在打开游标时传递DEPT_ID和TITLE，从而生成不同的游标集合,CURSOR emp_cursor(v_dept NUMBER,v_job VARCHAR2)IS

26、 SELECTlast_name,salary,start_date FROMs_emp WHEREdept_id=v_dept AND 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;,