PLSQL编程.ppt

1、第7章 PL/SQL编程,本章学习目标 本章将讲述PL/SQL基础语法，结构和组件、以及如何设计并执行一个PL/SQL程序。,本章内容安排,7.1 PL/SQL简介,7.2 PL/SQL语法,7.3 存储过程,7.4 触发器,7.5 游标,7.6 包,7.7 函数,7.1 PL/SQL简介,7.1.1 PL/SQL特点,7.1.2 开发及运行环境,1. PL/SQL简介,PL/SQL(Procedure Language BEGINDELETE FROM emp WHERE empno=v_empno;COMMIT;END;,2块的命名和匿名 1) PL/SQL程序块可以是一个命名的程序块也可

2、以是一个匿名程序块。 2) 使用赋值操作符:=或SELECT INTO或FETCH INTO给每个变量赋值. 3) PL/SQL块不能在屏幕上显示SELECT语句的输出。 4)NULL是一条合法的可执行语句。,3块的执行 SQL*PLUS中匿名的PL/SQL块的执行是在PL/SQL块后输入/来执行。 命名的程序与匿名程序的执行不同，执行命名的程序块必须使用execute关键字:。 如果在另一个命名程序块或匿名程序块中执行这个程序，那么就不需要EXECUTE关键字。,1、变量 （1）声明变量 声明变量的语句格式如下： Variable_name CONSTANT databyte NOT NUL

3、L:=|DEFAULT expression 注意：可以在声明变量的同时给变量强制性的加上NOT NULL约束条件，此时变量在初始化时必须赋值。,（2）给变量赋值 给变量赋值有两种方式： 直接给变量赋值 通过SQL :SELECT INTO 或FETCH INTO给变量赋值,2、常量 常量的值在程序内部不能改变，常量的值在定义时赋予，声明方式与变量相似，但必须包括关键字CONSTANT。常量和变量都可被定义为SQL和用户定义的数据类型。,编程时使用%TYPE、%ROWTYPE方式声明变量，使变量声明的类型与表中的保持同步，随表的变化而变化，这样的程序在一定程度上具有更强的通用性。 %TYPE用

4、于基本数据类型 %ROWTYPE 用于记录类型和表结构 作用:都是将其修饰的某个类型施加于某个变量,从而使二者保持一致.,例: 在某个PL/SQL块的声明部分,如下定义: DECLARE Student_No INTEGER; Course_No Student_No%TYPE; 用户不知道Course_No的具体类型,但知道它是基于Student_No类型的,并随之变化.,再如: DECLESR BrowsePath BookInfo.StorePath%TYPE 表示: BrowsePath的数据类型是基于表BookInfo中的StorePath列的数据类型.,如: DECLEAR Inp

5、utStudent StudentInfo%ROWTYPE; 表明:变量InputStudent是与表StudentInfo的字段一致的记录变量,%TYPE属性与%ROWTYPE的区别： %TYPE属性用于基本数据类型； %ROWTYPE用于记录类型和表结构等,3、有效字符集 （1）所有的大写和小写英文字母； （2）数字0-9； （3）符号：0一*！一；:.，“？ PL/SQL标识符的最大长度是30个字符，并且不区分字母的大小写。但是适当地使用大小写，可以提高程序的可读性。,1、条件结构 1） If条件判断逻辑结构 If条件判断逻辑结构有三种表达方式。 表达式一： if condition t

6、hen Statement end if 该表达式的功能为：若条件为真，执行then后的语句；否则，跳出条件语句执行end if后的语句。,表达式二： if condition then Statements_1 else Statements_2 end if 该表达式的功能为：如果条件为真执行then后的语句，否则执行else后的语句。 表达式三： if condition1 then Statements_1 elseif condition2 then Statements_2 else Statements_3 end if 该表达式的功能为：如果if后的条件成立，执行then后面的

7、语句，否则判断elseif后面的条件，条件成立执行第二个then后面的语句，否则执行else后的语句。这是条件语句嵌套。 IF 可以嵌套，可以在IF 或IF .ELSE语句中使用IF或IFELSE语句。,下面给出一个完整的PL/SQL块的例子,说明了一个有许多elsif语句的if语句: SET SERVEROUTPUT ON DECLARE Press$No NUMBER(38):= BEGIN,捆绑变量指示器,DBMS_OUTPUT.enable; if Press$No=1 then DBMS_OUTPUT.put_line(书名: | BookName | 由 | Press$1 |出版

8、发行); elsif Press$No=2 then DBMS_OUTPUT.put_line(书名: | BookName | 由 | Press$2 | 出版发行); elsif Press$No=3 then DBMS_OUTPUT.put_line(书名: | BookName | 由 | Press$3 | 出版发行); elsif Press$No=4 then DBMS_OUTPUT.put_line(书名: | BookName | 由 | Press$4 | 出版发行); end if; End;,字符串连接,DBMS_OUTPUT.put与DBMS_OUTPUT.put_l

9、ine的区别？,declare begin DBMS_OUTPUT.put(put=);-不换行 DBMS_OUTPUT.put(put=);-不换行 DBMS_OUTPUT.put_line(putline=);-换行 DBMS_OUTPUT.put_line(putline=);-换行 DBMS_OUTPUT.put_line(putline=);-换行 end; 结果: put=put=putline= putline= putline=,SQL*Plus命令大全,1. 执行一个SQL脚本文件 SQLstart file_name SQL file_name 我们可以将多条sql语句保存

10、在一个文本文件中，这样当要执行这个文件中的所有的sql语句时，用上面的任一命令即可，这类似于dos中的批处理。 2. 对当前的输入进行编辑 SQLedit 3. 重新运行上一次运行的sql语句 SQL/ 4. 将显示的内容输出到指定文件 SQL SPOOL file_name 在屏幕上的所有内容都包含在该文件中，包括你输入的sql语句。 5. 关闭spool输出 SQL SPOOL OFF 只有关闭spool输出，才会在输出文件中看到输出的内容。,6显示一个表的结构 SQL desc table_name 7. COL命令： 主要格式化列的显示形式。 该命令有许多选项，具体如下： COLUMN

11、 column|expr option . Option选项可以是如下的子句: ALIAS alias CLEAR FOLD_AFTER FOLD_BEFORE FORMAT format HEADING text JUSTIFY LEFT|CENTER|CENTRE|RIGHT LIKE expr|alias NEWLINE NEW_VALUE variable NOPRINT|PRINT NULL text OLD_VALUE variable ON|OFF WRAPPED|WORD_WRAPPED|TRUNCATED 1). 改变缺省的列标题 COLUMN column_name HEA

12、DING column_heading For example: Sqlselect * from dept; DEPTNO DNAME LOC - - - 10 ACCOUNTING NEW YORK sqlcolLOC heading location sqlselect * from dept; DEPTNO DNAME location - - - 10 ACCOUNTING NEW YORK,get 文件名 调入存盘的sql文件 start 文件名 运行调入内存的sql文件,结果?, 书名:c+编程思想 由 机械工业出版社 出版发行 PL/SQL 过程已成功完成.,2）Case表达式

13、 Case语句的基本格式如下： Case 变量 WHEN 表达式1 then 值1 WHEN 表达式2 then 值2 WHEN 表达式3 then 值3 WHEN 表达式4 then值4 ELSE 值5 END; Case语句的功能：首先设定变量的值作为条件，然后顺序检查表达式，一旦从中找到与条件匹配的表达式值，就停止case语句的处理。,2、循环控制 （1）loopexitend循环控制语句 LOOP循环语句是其中最基本的一种。LOOP语句的格式如下： LOOP statements END LOOP; 这种循环语句是没有终止的，如果不人为控制的话，其中的statements将会无限地执行

14、。一般可以通过加入EXIT语句来终结该循环。 （2）WHILE.LOOP循环控制语句 WHILE.LOOP有一个条件与循环相联系，如果条件为TRUE，则执行循环体内的语句，如果结果为FALSE，则结束循环。,实例PL/SQL块:,set serveroutput on Declare ctr integer:=0; Begin dbms_output.enable; loop dbms_output.put(ctr | ); -不换行 Ctr:=ctr+1; exit when ctr=10; end loop; dbms_output.put_line(loop exited);-换行 En

15、d;,教材P118,（3）FOR.LOOP循环控制语句 FOR.LOOP循环控制语句的格式如下： FOR counter IN REVERSE start_range.end_range LOOP statements; END LOOP; LOOP和WHILE循环的循环次数都是不确定的，FOR循环的循环次数是固定的，counter是一个隐式声明的变量，初始值是start_range，第二个值是start_range+1，直到end_range，如果start_range等于end _range，那么循环将执行一次。如果使用了REVERSE关键字，那么范围将是一个降序。,3、GOTO语句 GO

16、TO语句的格式如下： GOTO LABEL; 执行GOTO语句时，控制会立即转到由标签标记的语句。PL/SQL中对GOTO语句有一些限制，对于块、循环、IF语句而言，从外层跳转到内层是非法的。,4、嵌套 程序块的内部可以有另一个程序块这种情况称为嵌套。嵌套要注意的是变量，定义在最外部程序块中的变量可以在所有子块中使用，如果在子块中定义了与外部程序块变量相同的变量名，在执行子块时将使用子块中定义的变量。子块中定义的变量不能被父块引用。同样GOTO语句不能由父块跳转道子块中，反之则是合法的。,1、PL/SQL的异常 异常(EXCEPTION)处理块中包含了与异常相关的错误发生以及当错误发生时要进行

17、执行和处理的代码。异常部分的语法一般如下： BEGIN EXCEPTION WHEN excep_name1 THEN -异常异常 WHEN excep_name2 THEN WHEN OTHERS THEN END; 异常:已命名的错误条件,例: 执行SELECT INTO时,可能出现两种异常情况: NO_DATA_FOUND(查询结果含有0行) 和 TOO_MANY_ROWS(查询结果多于一行).,下面的异常处理包括上述的两种异常: Exception when no_data_found then dbms_output.put_line(no data found for select

18、 into command); when too_many_rows then dbms_output.put_line(too many rows found for select into command); End;,注: 如果PL/SQL块中的语句发生了异常,而程序没有相应的异常处理,该程序便停止执行,并且返回该异常响应的错误数和消息给调用函数.,2、ORACLE预定义 ( Predefined )错误 ORACLE预定义的异常情况大约有24个。对这种异常情况的处理，无需在程序中定义，由ORACLE自动将其引发。,预定义说明的部分 ORACLE 异常错误错误号 异常错误信息名称 说明

19、1. ORA-0001 Dup_val_on_index 试图破坏一个唯一性限制2. ORA-0051 Timeout-on-resource 在等待资源时发生超时3 ORA-0061 Transaction-backed-out 由于发生死锁事务被撤消4. ORA-1001 Invalid-CURSOR 试图使用一个无效的游标5. ORA-1012 Not-logged-on 没有连接到ORACLE,6. ORA-1012 Not-logged-on 没有连接到ORACLE7. ORA-1017 Login-denied 无效的用户名/口令8. ORA-1403 No_data_found

20、SELECT INTO没有找到数据9. ORA-1422 Too_many_rows SELECT INTO 返回多行10. ORA-1476 Zero-divide 试图被零除11. ORA-1722 Invalid-NUMBER 转换一个数字失败12. ORA-6500 Storage-error 内存不够引发的内部错误,13. ORA-6501Program-error内部错误14. ORA-6502Value-error转换或截断错误 15. ORA-6504Rowtype-mismatchPL/SQL返回的游标变量和主游标不相匹配16. ORA-6511CURSOR-already-

21、OPEN试图打开一个已打开了的游标,17. ORA-6530Access-INTO-null试图为null 对象的属性赋值18.ORA-6531Collection-is-null试图将Exists 以外的集合( collection)方法应用于一个null pl/sql 表上或varray上19.ORA-6532Subscript-outside-limit对嵌套或varray索引得引用超出声明范围以外20.ORA-6533Subscript-beyond-count对嵌套或varray 索引得引用大于集合中元素的个数.,说明:,对这种异常情况的处理，只需在PL/SQL块的异常处理部分，直接

22、引用相应的异常情况名，并对其完成相应的异常错误处理即可。,例子:,例1：更新指定员工工资，如工资小于1500，则加100；,DECLAREv_empno emp.empno%TYPE :=ELSE,DBMS_OUTPUT.PUT_LINE(编码为|v_empno|员工工资已经超过规定值!);END IF;EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE(数据库中没有编码为|v_empno|的员工);WHEN TOO_MANY_ROWS THENDBMS_OUTPUT.PUT_LINE(程序运行错误!请使用游标);WHEN OTHERS T

23、HENDBMS_OUTPUT.PUT_LINE(发生其它错误!);END;,3 非预定义 ( Predefined )错误即其他标准的ORACLE错误。对这种异常情况的处理，需要用户在程序中定义，然后由ORACLE自动将其引发。,非预定义的异常处理对于这类异常情况的处理，首先必须对非定义的ORACLE错误进行定义。步骤如下：1. 在PL/SQL 块的定义部分定义异常情况： EXCEPTION;2. 将其定义好的异常情况，与标准的ORACLE错误联系起来，使用EXCEPTION_INIT语句：PRAGMA EXCEPTION_INIT(, )；3. 在PL/SQL 块的异常情况处理部分对异常情况

24、做出相应的处理。,例2：删除指定部门的记录信息，以确保该部门没有员工。INSERT INTO dept VALUES(50, FINANCE, CHICAGO);DECLAREv_deptno dept.deptno%TYPE :=/* -2292 是违反一致性约束的错误代码 */,BEGINDELETE FROM dept WHERE deptno=v_deptno;EXCEPTIONWHEN e_deptno_remaining THENDBMS_OUTPUT.PUT_LINE(违反数据完整性约束!);WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE(发生其它错误!

25、);END;,4、用户定义(User_define) 错误 异常不一定必须是oracle返回的系统错误，用户可以在自己的应用程序中创建可触发及可处理的自定义异常. 用户自定义的异常需在PL/SQL块的声明部分进行声明,其语法如下: 异常名称 EXCEPTION; 同时,在PL/SQL块的程序体内用RAISE命令对用户自定义异常进行显示检查。,当与一个异常错误相关的错误出现时，就会隐含触发该异常错误。用户定义的异常错误是通过显式使用 RAISE 语句来触发。当引发一个异常错误时，控制就转向到 EXCEPTION块异常错误部分，执行错误处理代码。,对于这类异常情况的处理，步骤如下：1 在PL/SQ

26、L 块的定义部分定义异常情况： EXCEPTION;2 RAISE ；3 在PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。,例3：更新指定员工工资，增加100；,DECLAREv_empno emp.empno%TYPE :=,EXCEPTIONWHEN no_result THENDBMS_OUTPUT.PUT_LINE(你的数据更新语句失败了!);WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE(发生其它错误!);END;,异常处理部分一般放在 PL/SQL 程序体的后半部,结构为:EXCEPTIONWHEN first_exception THEN

27、WHEN second_exception THEN WHEN OTHERS THEN END;异常处理可以按任意次序排列,但 OTHERS 必须放在最后.,PL/SQL可以生成自己的异常。如，用户可以利用触发器生成一段阻止SQL语句发生异常的异常处理代码。,例:表student_info是只读表,不得对它进行删除或更新. 下面建立一个触发器,阻止对该表进行删除操作. create or replace trigger protecttab before delete or update on student_info for each row begin raise_application_

28、error(-20006,表student_info是只读的,不得进行更新或删除操作!); end protecttab; /* RAISE_APPLICATION_ERROR 是将应用程序专有的错误从服务器端转达到客户端应用程序。 */,隐式调用，且不接收参数,过程raise_application_error(error_number,message); 用-2000-20999之间的任何数值来代替error_number,这个区间是专门为用户自定义的错误区. 可以用自己的一个文本信息来代替message. Message的字节数不超过2048字节. raise_application_e

29、rror过程将返回一个用户自定义错误号(error_number)和错误信息(message)给调用环境.,现在对表student_info执行删除操作,将得到如图结果.,7.3.2 存储过程的调用,7.3.3 存储过程的释放,7.3.4 实例,7.3.1 存储过程的创建,7.3 存储过程,PL/SQL块: 命名块、匿名块。 匿名块：没有自己的名字，以DECLARE或BEGIN开始，每次使用都要编译；不在数据库中存储；不能从其他的PL/SQL块中调用。 命名块：子程序、包和触发器。,子程序就是有名称的PL/SQL程序，包括过程与函数。 过程（procedure）是一个PL/SQL块，它存储在数

30、据字典中并可被应用程序调用。当执行一个过程时，其语句被作为一个整体执行。过程不将任何值返回调用程序。而函数可以把值返回调用程序。可以建立自己的函数。 过程调用本身是一个PL/SQL语句，而函数调用是作为表达式的一部分执行的。,注: 与过程相关数据字典(SYS用户)USER_SOURCE, ALL_SOURCE, DBA_SOURCE, USER_ERRORS相关的权限:CREATE ANY PROCEDUREDROP ANY PROCEDURE在SQL*PLUS 中，可以用DESCRIBE 命令查看过程的名字及其参数表。DESCRIBE Procedure_name;DROP PROCEDUR

31、E logexecution;DROP PROCEDURE delemp;DROP PROCEDURE proc_demo;DROP FUNCTION demo_fun;DROP FUNCTION get_salary;,创建存储过程语法: CREATE OR REPLACE PROCEDURE 存储过程名（参数名INOUTIN OUT数据类型） ISAS 说明部分 BEGIN 语句序列 EXCEPTION 例外处理 END 存储过程名;,注: 在SQL*Plus环境：运行存储过程的命令 语法格式： EXECUTE 存储过程名(参数); 在SQL*Plus环境：删除存储过程的命令 语法格式：

32、DROP PROCEDURE 存储过程名;,过程参数有以下三种类型： （1）in参数类型(缺省类型)：读入参数，主程序向过程传递参数值。 （2）out参数类型：读出参数，过程向主程序传递参数值。 （3）in out参数类型：双向参数，过程与主程序双向交流数据。,存储过程也可以被另外的PL/SQL块调用，调用的语句是： declare par1,par2; begin proc_name(par1,par2); end; 在调用前要声明变量par1，par2,6.3.4 例子：创建过程，用于打印当前时间： CREATE OR REPLACE PROCEDURE print_current_tim

33、e AS CURTIME VARCHAR2(20); BEGIN SELECT TO_CHAR(sysdate,yyyy/mm/dd hh24:mi:ss) INTO CURTIME FROM dual; -安装过程建立的表，适合所有用户 DBMS_OUTPUT.put_line(当前时间是: | chr(9) | curtime); END print_current_time; /,查询过程:,文本编辑区,执行过程: 创建过程并不会直接输出结果,只是和创建其他数据库对象一样,是一个数据定义命令. 要执行创建的过程,必须通过主程序来调用过程 .,在SQLPlus Worksheet中执行下列

34、PL/SQL程序,执行结果:,带参数过程的例子: CREATE OR REPLACE PROCEDURE print_parameter ( p_Parameter IN VARCHAR2 DEFAULT NULL ) AS BEGIN IF(p_Parameter is NULL) THEN DBMS_OUTPUT.put_line(你没有输入参数) ; ELSE DBMS_OUTPUT.put_line(你输入的参数是: | chr(9) | p_Parameter); END IF; END print_parameter; /,调用该过程:EXECUTE print_parameter

35、(abc);,实参,显式调用带参数过程： Set serveroutput on Begin sys.print_parameter(abc); End;,7.4.2 触发器的类型,7.4.3 触发器的创建,7.4.4 触发器的修改和删除,7.4.1 触发器的设计规则,7.4 触发器,7.4.5 实例,触发器（trigger）是一些过程，当发生一个特定的数据库事件时就执行这些过程。可以使用触发器来扩充引用完整性，实施附加的安全性或增强可用的审计选项。,可用于下列情况： 1。维护在表创建阶段通过声明限制无法实现的复杂完整性限制。 2。通过记录修改内容和修改者来审计表中的信息。 3。在表内容发生变

36、更时，自动通知其他程序采取相应的处理。 4。在订阅发布环境下，发布有关各种事件的信息。,触发器类似于函数和过程，它们都是具有声明部分、执行部分和异常处理部分的命名PL/SQL块。 过程是显式地通过过程调用从其他块中执行的 ，而且过程调用可以传递参数。与之相反，触发器是在事件发生时隐式地运行的，并且触发器不能接收参数。,2注意事项 （1）触发器可以声明为在对记录进行操作之前，在之前(检查约束之前和 INSERT，UPDATE 或 DELETE 执行前)或之后(在检查约束之后和完成 INSERT， UPDATE 或 DELETE 操作)触发.。 （2）一个 FOR EACH ROW 执行指定操作的

37、触发器为操作修改的每一行都调用一次。 （3）SELECT 并不更改任何行，因此不能创建 SELECT 触发器.,（4）触发器和某一指定的表格有关，当该表格被删除时，任何与该表有关的触发器同样会被删除。 （5）在一个表上的每一个动作只能有一个触发器与之关联。 （6）在一个单独的表上，最多只能创建三个触发器与之关联，一个INSERT触发器，一个DELETE触发器和一个UPDATE触发器。,触发条件即为当发生什么情况时执行触发器，由以下几部分组成 ： 触发时间：BEFORE、AFTER 触发事件：INSERT、UPDATE和DELETE 触发器类型 :DML、替代触发器、系统事件触发器和DDL触发器

38、,1.DML触发器,DML触发器是由DML语句激发的 触发器. 触发事件:INSERT、UPDATE和DELETE BEFORE:如检查这个删除操作是否允许执行 AFTER:记录表中数据发生的改动 语句触发器:对每一个触发语句触发一次,如对插入500行的 INSERT命令触发一次 行触发器:对受语句影响的每一个行触发一次,DML触发器例子,DML触发器 : 对表Table1进行DML操作(插入、更新和删除)之后,在日志表Tab1Log中进行记录.,CREATE OR REPLACE TRIGER WriteLog AFTER INSERT OR DELETE OR UPDATE ON Tabl

39、e1 FOR EACH ROW DECLARE StatementType CHAR(1); BEGIN if INSERTING then StatementType :=I; Else if UPDATTING then StatementType :=U; Else StatementType :=D; End if; INSERT INTO Tab1Log VALUES(StatementType,USER,SYSDATE); END WriteLog; /,2.替代触发器(INSTEAD_OF触发器),执行一个替代操作来代替触发器的操作. 与DML触发器不同,DML触发器是在DML操

40、作之外运行的,而替代触发器则代替激发它的DML语句运行. 替代触发器是行一级的.,3.系统事件触发器,在发生如数据库启动或关闭等系统事件时激发,而不是在执行DML语句时激发. 系统事件触发器共支持5种系统事件,系统事件触发器支持的系统事件： 系统事件名称 说明 SERVERERROR 当服务器发生错误的时候,可以生成触发器 LOGON 当用户对数据库登录后,可以生成触发器 LOGOFF 当用户从数据库注销前,可以生成触发器 STARTUP 数据库打开后,马上生成触发器 SHUTDOWN 不论实例在任何时候被关闭时,都可以生成触发器 注:BEFORE ,AFTER但不能同时.,4.DDL触发器,

41、是针对于对模式对象有影响的CREATE、ALTER 、 DELETE等语句的 . 可以在做这些操作之前或之后来定义DDL触发器 例子:如果想在创建对象后打上时间戳,则可以 采用AFTER型的 DDL触发器.,7.5.2 隐式游标,7.5.3 实例,7.5.1 显式游标,7.5 游标,Oracle游标是一种用于轻松的处理多行数据的机制，没有游标，Oracle开发人员必须单独地、显式地取回并管理游标查询选择的每一条记录。 游标的另一项功能是，它包含一个跟踪当前访问的记录的指针，这使程序能够一次处理多条记录。,游标是从数据表中提取出来的数据,以临时表的形式存放在内存中,在游标中 有一个数据指针,在初

42、始状态下指向的是首记录,利用fetch语句可以移动指针,从而对游标中数据进行各种操作,然后将操作结果写回数据表中.,游标可以返回一条或多条记录,或一条也不返回. 游标通常的操作顺序是: 1)声明游标,使用查询来定义游标的列和行. 2)打开游标,使用PL/SQL命令OPEN来打开一个声明的游标. 3)提取数据,从游标中重复提取每条记录到数据结构中,直到数据集合被提空. 4)光标下移：使用loop循环 5)关闭游标.,1. 定义游标 游标作为一种数据类型,首先必须进行定义,其语法格式如下： Cursor 游标名 is select 语句; 声明游标完成了下面两个目的： （1）给游标命名； （2）将

43、一个查询与游标关联起来。 注: cursor是定义游标的关键词,select是建立游标的数据表查询命令.,以scott用户连接数据库,在SQLPlus Worksheet中执行下列PL/SQL程序: 该程序定义: tempsal为与scott.emp数据表中sal字段类型相同的变量; mycursor为从scott.emp数据表中提取的sal大于tempsal的数据构成的游标 .,set serveroutput on declare tempsal scott.emp.sal%type; cursor mycursor is select * from scott.emp where sal

44、tempsal; begin tempsal:=800; open mycursor; end;,2打开游标 打开游标的语句格式如下： OPEN cursor_name; 打开游标的过程有以下两个步骤: 1)将符合条件的记录送入内存. 2)将指针指向第一条记录.,3从游标中取回数据 FETCH命令以每次一条记录的方式取回活动集中的记录。通常将FETCH命令和某种迭代处理结合起来使用，在迭代处理中，FETCH命令每执行一次，游标前进到活动集的下一条记录。,FETCH命令的语句格式如下： FETCH cursor_name INTO record_list; 执行FETCH命令后，活动集中的结果被

45、取回到PL/SQL变量中，以便在PL/SQL块中使用。每取回一条记录，游标的指针就移向活动集的下一条记录。,在SQLPlus Worksheet中执行下列PL/SQL程序,该程序定义cursorrecord变量是游标mycursor的记录行变量,在游标mycursor的结果中找到sal字段大于800的第一个记录,显示deptno字段的内容.,set serveroutput on declare tempsal scott.emp.sal%type; -TYPE用于基本数据类型 cursor mycursor is select * from scott.emp where saltempsa

46、l; cursorrecord mycursor%rowtype; -ROWTYPE用于记录类型和表结构,begin tempsal:=800; open mycursor; fetch mycursor into cursorrecord; dbms_output.put_line(to_char(cursorrecord.deptno); end;,4光标下移：使用loop循环 例： Declare v1 emp.empno%type; v2 emp.ename%type; v3 emp.sal%type; cursor c is select empno,ename,sal from e

47、mp; Begin open c; loop fetch c into v1,v2,v3; if v33000 then Begin update emp set sal=sal+100 where empno=v1; dbms_output.put_line(员工|v2|工资已经更新!); End; end if; exit when c%NOTFOUND; end loop; close c; End; /,4关闭游标 使用完游标之后应将其关闭,来释放占用的资源. CLOSE语句的格式： CLOSE cursor_name;,游标的属性操作: 游标包括4 个属性:%ISOPEN、%FOUN

48、D、%NOTFOUND及%ROWCOUNT。 属性使用的格式如下： 游标名属性名无空格 表5.4 游标属性,属性,返回值,说明,ISOPEN TRUE/FALSE 指出游标是关闭的还是打开的 FOUND TRUE/FALSE 指出是否发现一条记录 NOTFOUND TRUE/FALSE 指出是否没发现一条记录 ROWCOUNT NUMBER 每次提取记录的序数值（第一，第二，第三，）,如，在关闭一个游标前，想确认它是否处于打开状态，可用下列语句： If (student_list%ISOPEN) then CLOSE Student_list; End if;,例： Declare v1 em

49、p.empno%type; v2 emp.ename%type; v3 emp.sal%type; cursor c is select empno,ename,sal from emp; Begin open c; loop fetch c into v1,v2,v3; if v33000 then Begin update emp set sal=sal+100 where empno=v1; dbms_output.put_line(员工|v2|工资已经更新!); End; end if; exit when c%NOTFOUND; end loop; dbms_output.put_l

50、ine(光标处理的行数：|C%ROWCOUNT); close c; End;,隐式光标处理: 隐式光标是指在处理SQL时,不需定义光标， 所使用的SQL语句包括：INSERT ,UPDATE,DELETE子句。 隐式光标属性: SQL%FOUND SQL%NOTFOUND SQL%ROWCOUNT 隐式光标的使用: Declare v_empno emp.empno%type:=,7.6.2 实例,7.6.1 包的创建,7.6 包,什么是包？ 把一些相关的存储过程、函数、变量、光标、 例外等组合在一起形成的对象叫包。 包由包的说明部分及包体两部分组成。 定义在包说明部分的是公共元素，定义在包

51、体的部分是私有元素。,使用包的优点： 1、规范化程序设计 2、方便过程及函数的组织 3、便于管理：包的授权只需一次完成 4、优化系统性能：整个包作为一个整体一次调入内存。,包的开发步骤： 1、创建包的说明部分：Create package 2、创建包体部分：Create package body 3、在SQL*Plus中运行创建包 4、在存储过程、其它应用中调用。 在SQL*Plus中调用方法：SQL exec 包名.过程名; 在存储过程、函数、其它应用中调用方法：包名.过程名;,1. 包头的创建 创建包头的语句格式如下： CREATE PACKAGE包名 IS 变量、常量及数据类型定义； 游

52、标定义； 函数、过程定义和参数列表及返回类型； END包名；,2. 包主体部分 创建包主体部分的语句格式如下： CREATE PACKAGE BODY包名 AS 游标、函数、过程的具体定义； END包名；,1. 描述部分 CREATE PACKAGE znz_package/创建包头 IS man_num NUMBER; /定义变量 woman_num NUMBER; CURSOR 学生; /定义游标 CREATE FUNCTION f_count(in_sex IN 学生.sex%TYPE)/定义函数 RETURN NUMBER; /定义返回值类型 CREATE PROCEDURE p_co

53、unt /定义过程 (in_sex IN 学生.sex%TYPE， /过程参数 out_num OUT NUMBER); END znz_package; /包头结束,2. 包体部分 CREATE PACKAGE BODY znz_package/创建包体 AS CURSOR 学生 IS /游标具体定义 SELECT 学号，姓名 FROM 学生 WHERE 学号50; FUNCTION f_count/函数具体定义 (in_sex IN 学生.sex%TYPE) RETURN NUMBER/定义返回值类型 IS out_num NUMBER; BEGIN/函数体,IF in_sex=男 THE

54、N SELECT count(sex) INTO out_num FROM 学生 WHERE 性别男; ELSE SELECT count(sex) INTO out_num FROM 学生 WHERE 性别女; END IF; RETURN(out_num);/返回函数值 END f_count;/函数定义结束 PROCEDURE p_count/过程具体定义 (in_sex IN 学生.sex%TYPE， out_num OUT NUMBER),AS BEGIN /过程体 IF in_sex=男 THEN SELECT count(sex) INTO out_num FROM 学生 WHE

55、RE性别=男; ELSE SELECT count(sex) INTO out_num FROM 学生 WHERE 性别女; END IF; END P_count;/过程定义结束 END znz_package/包体定义结束,包的删除： SQL drop package 包名; SQL drop package body 包名;,7.7.2 自定义函数,7.7.1 Oracle系统函数,7.7 函数,ORACLE常用函数,ORACLE常用函数 ORACLE常用数值函数、转换函数、字符串函数介绍。 数值函数：abs(m)m的绝对值mod(m,n)m被n除后的余数power(m,n)m的n次方r

56、ound(m,n)m四舍五入至小数点后n位的值（n缺省为0）trunc(m,n)m截断n位小数位的值（n缺省为0）,字符函数：,字符函数： initcap(st)返回st将每个单词的首字母大写，所有其他字母小写lower(st)返回st将每个单词的字母全部小写upper(st)返回st将每个单词的字母全部大写concat(st1,st2)返回st为st2接st1的末尾（可用操作符|）lpad(st1,n,st2)返回右对齐的st,st为在st1的左边用st2填充直至长度为n,st2的缺省为空格rpad(st1,n,st2)返回左对齐的st,st为在st1的右边用st2填充直至长度为n,st2的

57、缺省为空格ltrim(st,set)返回st,st为从左边删除set中字符直到第一个不是set中的字符。缺省时，指的是空格,rtrim(st,set)返回st,st为从右边删除set中字符直到第一个不是set中的字符。缺省时，指的是空格replace(st,search_st,replace_st)将每次在st中出现的search_st用replace_st替换，返回一个st。缺省时，删除search_stsubstr(st,m,n)n=返回st串的子串，从m位置开始，取n个字符长。缺省时，一直返回到st末端length(st)数值，返回st中的字符数instr(st1,st2,m,n)数值，返回st1从第m字符开始，st2第n次出现的位置，m及n的缺省值为1,转换函数：nvl(m,n)如果m值为null,返回n,否则返回mto_char(m,fmt)m从一个数值