1、大型数据库概论 Introduction of Large Database第3章 PLSQL编程(2),学习目标1、理解游标的概念及分类。 2、掌握显式游标和隐式游标的使用。 3、掌握带参数游标的使用。 4、掌握游标的常见属性。 5、掌握SQL *Plus/Worksheet的使用。,游标（Cursor）：是Oracle9i的一种内存结构，用来存放SQL语句或程序执行后的结果。游标使用SELECT语句从基表或视图中取出数据并放入内存，最初游标指向查询结果的首部，随着游标的推进，就可以访问相应的记录。游标分为显式和隐式两种:前者需要用户定义，需要时打开，使用完后关闭；后者则完全是自动的，无需用
2、户干预。,游标,显式游标,显式游标：PL/SQL中处理显式游标需经过四个步骤：声明游标、打开游标、推进游标、关闭游标。1声明游标显式游标要在PL/SQL语句块的声明部分中定义，语法如下：CURSOR 游标名 IS SELECT语句;注意：该SELECT语句不应包含INTO子句。如CURSOR MYCURSOR IS select * from system.student;表示声明了一个游标MYCURSOR。,2打开游标语法是：OPEN 游标名;这里的游标名必须事先声明过，如OPEN MYCURSOR;3推进游标：指从游标中取出游标当前所指的数据行，然后使游标指针指向下一个数据行。语法是：FE
3、TCH 游标名 INTO 变量列表;或 FETCH 游标名 INTO 记录变量名;注意：游标指针只能向下移动，不能回退。使用FETCH语句之前，必须先打开游标。,4关闭游标当完成游标的处理后应释放与游标相关的资源。语法是：CLOSE 游标名;注意：游标一旦关闭，再使用它来检索数据就是非法 的。关闭一个已经关闭的游标也是非法的。,隐式游标：Oracle9i为每个不属于显式游标的SQL DML语句都创建了一个隐式游标。由于隐式游标没有名称，所以它也称为SQL游标。与显式游标不同，不能对一个隐式游标显式地执行OPEN、FETCH和CLOSE语句。Oracle9i隐式地打开、处理和关闭SQL游标。如O
4、racle9i为下列SQL语句隐式地创建了一个游标：UPDATE SYSTEM.STUDENT SET SAGE=SAGE+1;和显式游标一样，隐式游标也有下述四个属性，引用方法只要在属性前加上SQL即可。,隐式游标,游标的属性,游标的属性：游标的属性并非返回一个类型，而是返回可以在表达式中使用的值。游标有四个属性：%FOUND、%NOTFOUND、%ISOPEN和%ROWCOUNT。1%FOUND：若当前FETCH语句成功取出一行数据，则%FOUND返回TRUE；否则返回FALSE。该属性可以用来判断是否应关闭游标，在循环结构中常用该属性决定循环的结束。,2%NOTFOUND：与%FOUND
5、的意义正好相反。3%ISOPEN：当游标已经打开且尚未关闭时，%ISOPEN返回TRUE。该属性可以用来判断游标的状态。4%ROWCOUNT：%ROWCOUNT返回游标已检索的数据行个数。,例3.7 从scott.student表中选取所有学生的学号和姓名。DECLARECURSOR MYCURSOR ISSELECT Sno,Sname FROM scott.student;V_sno scott.student.sno%TYPE;V_sname scott.student.sname%TYPE;,BEGINOPEN MYCURSOR;FETCH MYCURSOR INTO V_sno,V_
6、sname;LOOPIF MYCURSOR%FOUND THENDBMS_OUTPUT.PUT_LINE(TO_CHAR(MYCURSOR%ROWCOUNT)|TO_CHAR(V_sno) |TO_CHAR(V_sname);FETCH MYCURSOR INTO V_sno,V_sname;ELSIF MYCURSOR%NOTFOUND THEN EXIT;END IF;END LOOP;IF MYCURSOR%ISOPEN THEN CLOSE MYCURSOR;END IF; END;,参数化游标根据参数的不同选取的数据行也不同，从而达到动态使用的目的。例3.8 使用带参数的游标，根据输
7、入的姓名从scott.student表中选取相应学生的学号和姓名。DECLARECURSOR MYCURSOR(p_name scott.student.sname%TYPE) ISSELECT Sno,Sname FROM scott.studentWHERE sname=p_name;V_sno scott.student.sno%TYPE;V_sname scott.student.sname%TYPE;,带参数的游标,BEGINOPEN MYCURSOR(李军);FETCH MYCURSOR INTO V_sno,V_sname;LOOPIF MYCURSOR%FOUND THENDB
8、MS_OUTPUT.PUT_LINE(TO_CHAR(V_sno)|TO_CHAR(V_sname);FETCH MYCURSOR INTO V_sno,V_sname;ELSIF MYCURSOR%NOTFOUND THEN EXIT;END IF;END LOOP;CLOSE MYCURSOR;END;,2PL/SQL程序的输入和执行SQL *Plus工作窗口中的“SQL”称为SQL提示符，其后可以输入并执行PL/SQL程序，执行方式有立即命令和程序文件两种。前者是指输入PL/SQL程序后立即执行；后者是指输入PL/SQL程序后将其保存为一个文件（扩展名通常为.sql），以后需要执行时加载
9、该文件即可执行。PL/SQL程序的执行命令有以下四种方式： 在命令行最后或最后一行输入;号。 在SQL提示符后输入/号。 在SQL提示符后输入RUN或R。 在SQL提示符后输入Start/ .sql文件。,3SQL *Plus常用命令 行编辑命令：SQL *Plus工作窗口是一个行编辑环境，有一个内存区域存储了在窗口中刚刚执行完的命令，这个内存区域称为缓冲区。缓冲区是可以编辑、修改或再次运行的。SQL *Plus提供了一组行编辑命令（也称缓冲区操作命令）。常用的行编辑命令如表5.2所示。, 文件操作命令：实际应用中，经常需要将缓冲区的内容写入磁盘或将磁盘上的文件调入缓冲区再次执行。为此，SQL
10、 *Plus提供了一些常用的文件操作命令。SQL *Plus中不直接支持对文件的编辑，但它可以调用系统的默认编辑器（Windows平台上的记事本）。常用的文件操作命令如表3.3所示。, 环境变量设置命令SQL *Plus中的SET命令可以设置环境变量，语法是SET 环境变量 值;常用的环境变量有：n SET NUMFORMAT：后面接数字格式（如$99,999），设置查询结果中数字显示的缺省格式。n SET PAGESIZE：后接数字，设置每页的行数。默认为14。n SET LINESIZE：后接数字，设置每行的字符数。默认值为80。,n SET PAUSE：后接ON，设置在每页的开始处停止，
11、按回车键后继续滚动。n SET SERVEROUTPUT ON|OFF SIZE n：PL/SQL本身没有输入或输出的功能，只有将SQL *Plus与DBMS_OUTPUT包集成在一起，才能向屏幕输出信息。设置为ON后可以使用DBMS_OUTPUT.PUT_LINE函数进行输出，SIZE为输出缓冲的字节数，默认值为2000。,例3.9 以下脚本可在屏幕上显示一个字符串和系统当前日期，运行结果如图3.4所示。SET SERVEROUTPUT ON;BEGINDBMS_OUTPUT.PUT_LINE(Hello,world!);DBMS_OUTPUT.PUT_LINE(Today is:|TO_C
12、HAR(SYSDATE,DD_MM_YYYY);END;,n SET AUTOCOMMIT ON|OFF|IMMEDIATE|N：设置Oracle9i何时提交数据库的修改。为ON时表示执行每个成功的INSERT、DELETE、UPDATE命令或PL/SQL块后自动提交，为OFF时表示需要手动提交，为IMMEDIATE时与ON相似，为N时表示成功执行了N次的INSERT、DELETE、UPDATE命令或PL/SQL块后自动提交。n DESCRIBE：显示数据库对象的结构信息。如DESC 表名;会显示包括构成该表各字段的名称及类型、长度及是否非空等信息。,4关闭SQL *Plus使用完SQL *P
13、lus后，一定要正确关闭，确保会话使用的数据库资源释放，以便其他用户访问。关闭SQL *Plus有两种方法：关闭SQL *Plus工作窗口或通过单击“文件”“结束”菜单项。在SQL提示符后输入并执行Exit或Quit命令。,3.4.2 SQL *Plus Worksheet：用于输入、编辑和执行PL/SQL程序，是一个全屏幕开发环境。1启动SQL *Plus Worksheet：两种方法： 直接启动：选择“开始”“所有程序”Oracle-OraHome92Application DevelopmentSQLPlus Worksheet，系统出现如图5.5所示的登录窗口。输入用户名为“syste
14、m”、口令为“system”、服务为“test”、连接身份为“SYSDBA”。,5.5 SQL *Plus Worksheet的登录窗口,单击”确定”按钮，打开SQL *Plus Worksheet工作窗口，如图5.6所示。,图5.6 SQL *Plus Worksheet的工作窗口, 从企业管理器中启动启动企业管理器（方法见第2章），选择菜单“工具”数据库应用程序” “SQL *Plus Worksheet”，打开SQL *Plus Worksheet窗口。,2使用SQL *Plus Worksheet工作窗口SQL *Plus Worksheet工作窗口的左侧是工具栏，包括以下五个工具按钮
15、：n 改变数据库连接：改变当前连接的数据库。n 执行：执行命令区输入的语句或程序块。n 命令历史记录：获取历史信息，避免重复输入。n 帮助：获得联机帮助信息。n 速成教学：提供速成教学文档，可浏览、查找相关信息。SQL *Plus Worksheet工作窗口的上部分是全屏幕编辑区域，可以输入或编辑PL/SQL程序及DBA管理命令，下部分是运行结果的显示区域。,3.4.3 SQL *Plus Worksheet与SQL *Plus的比较SQL *Plus Worksheet和SQL *Plus都是PL/SQL程序的开发环境。相比而言，SQL *Plus是行编辑环境，有一系列支持行编辑的命令。而S
16、QL *Plus Worksheet（工作单）是一个全屏幕编辑环境，使用起来更加直观、简洁、方便和高效。,编程实现：向学生表（student）中插入10个连续的学号。,Declaremaxrecords constant int:=10;i int:=1;Beginfor i in 1maxrecords loopinsert into scott.student(sno) values(95588 | TO_CHAR(i);end loop;Dbms_output.put_line(成功录入数据！);End; /,连续输出1到100，每行输出1个数字,DECLAREV_Counter BIN
17、ARY_INTEGER :=1;BEGINLOOPDbms_output.put_line(V_counter);V_Counter:=V_Counter+1;IF V_Counter100 THENEXIT;END IF;END LOOP ;END;,编写程序，输出 ”Hello,world! ”。,declarel_text varchar2(100); beginl_text:=Hello,World!;dbms_output.put_line(l_text); exceptionwhen others thendbms_output.put_line(引发了一个异常！);raise; end;,习题5 3. 简述PL/SQL游标的概念、属性和使用方法。,
