数据库系统实现-实验指导书

1、数据库系统实现实验指导书（Oracle版本）湖北汽车工业学院电气与信息工程学院计算机工程系2011年10月说明数据库系统实现课程主要介绍基于J2EE平台的数据库系统开发技术，实验涉及三个方面：（1）数据库应用高级编程技术。涉及PL/SQL编程知识（数据类型、程序结构）、存储过程（包括存储函数）、触发器和包，以及Oracle的事务处理与并发控制等内容。（2）数据库中间件应用技术。涉及Oracle的JDBC编程，包括驱动类型选择、驱动运行环境配置、驱动应用编程步骤、LOB处理、事务处理、存储过程调用等内容。（3）数据库应用系统架构。涉及C/S架构、B/S架构和数据库系统综合应用等内容。由于课程及实

2、验涉及的知识点多，内容跨度大，要求学生上课认真听讲，课后注意归纳总结，实验前做好准备。除了最后一个综合应用实验外，前面的实验均要求在如下表结构上进行。（1）学生表：有学生学号、学生姓名、学生性别、学生出生日期、学生所属系部、学生平均成绩、学生照片等列。期中学生学号为9位字符或数字编码，有主码约束，学生姓名有非空约束，学生性别有“男”或“女”的取值约束，学生的平均成绩保留一位小数，与学生的选课成绩一致。表名用Student，各列依次用SNo（char(9)）、SName（varchar2(30)）、SSex（char(3)）、SBirthday（Date）、SDepartment（varchar

3、2（60）、SAvgGrade（number(4,1)）、SPicture（blob）表示。（2）课程表：有课程号、课程名、先导课程课程号、课程学分等列。期中课程号为4位数字或字符编码，有主码约束，先导课程课程号外码引用课程号，课程名有非空和唯一性约束，学分为大于0小于20的数，保留一位小数。表名用Course，各列依次用CNo（char(4)）、CName（varcahr2（60）、CPno（char(4)）、CCredit（number(2,1)）表示。（3）选课表：有学生学号、课程号、成绩等列。期中学生好学号和课程号组成联合主码，成绩取不超过100的整数。表名用SC，各列依次用SNo（c

4、har(9)）、CNo（char(9)）、Grade（number（3）。实验作业一并在实验报告上完成，作业成绩纳入实验考核成绩。本实验指导书由彭彬老师写，由于时间仓促，再加上实践经验的限制，错误在所难免，欢迎各位老师、学生赐教（）。 目录实验环境介绍一、Oracle PL/SQL编程环境标准SQL语言是一次一集合的非过程化的查询语言，不利于对数据库中的数据作基于记录的过程化处理。Oracle的PL/SQL对标准SQL进行扩充，成为一个完整的编程语言：如数据类型、变量与表达式、结构化程序的结构等。这里提供三种PL/SQL编程环境。SQL*PLUSSQL*Plus是一个交互式批处理查询工具，在安

5、装Oracle数据库服务器时一并安装。它有三种用户界面：命令行用户界面， Windows图形用户界面（GUI）和基于Web的用户界面（iSQL * Plus）。也有单独运行的SQL*PLUS即时客户端，它需要单独安装，并可以访问其它数据库服务器。SQL*plus中可以运行SQL、PL/SQL、SQL*PLUS、操作系统命令，供于业务技术人员和系统管理员执行以下任务：开发和运行批处理脚本，处理查询结果，验证数据库对象的定义以及管理数据库。要注意SQL*plus是一个数据库应用成程序，它与Oracle数据库服务器构成一个两层或三层的应用架构。期中命令行用户界面的SQL*plus属于两层架构，SQL

6、*plus处于客户端的地位，程序以命令行的形式输出用户程序或命令的处理结果。SQL*PLUS需要相关服务的支持。查看系统服务，确保OracleServiceXE服务已经启动（如图1所示），也可以在DOS窗口运行“net start OracleServiceXE”启动两个服务”。图1.1-1 Oracle系统服务以下介绍命令行界面。在Windows平台选择“开始”“运行”，输入“sqlplus”，或者在Windows的DOS窗口输入“sqlplus”，即进入命令行界面（如图1.1-2所示）。处于安全考虑，Oracle对登录的应用程序要进行“用户名”和“密码”验证，所以SQL*plus要登录到服

7、务器，需要依次输入用户名、密码。如“sys/sys as sysdba”，“drp/drp”。如果系统安装有Oracle 10g Express，则可选择“开始”“程序”“Oracle Database 10g Express Edition”“运行SQL命令”，即进入命令行界面（如图1.1-3所示）。也可以用“SQLPLUS USERNAME/USERPASSSERVICE_NAME”直接登录到数据库服务器。当想以SYS用户登录，但又不知道用户密码时，可以用“SQLPLUS / AS SYSDBA”登录数据库，立即修改SYS用户的登录密码。在命令行窗口，键入exit，即可退出命令行界面。图1

8、.1-2 SQl*PLUS命令行界面图1.1-3 通过Oracle 10g Express的快捷菜单进入的SQL*PLUS命令行界面Express版本缺省创建了一个用户方案HR，期中有表、视图、存储过程、触发器、包等数据库对象，以供初学者学习使用。但HR用户是锁定的，需要管理员解锁。（1）、登录到数据库服务器 Connect username/userpassword as sysdba（2）、环境参数显式环境参数：SHOW ALL|参数;设置环境参数：SET 参数名 部分参数：ARRAYSIZE: 设置SQL*PLUS从数据库提取的行数AUTOCOMMIT:自动提交DML语句LINESIZE

9、:每页的宽度PAGESIZE:设置页的行数SERVEROUTPUT:控制PL/SQL程序的结果是否在SQL*PLUS中输出。（3）、用户管理创建用户：CREATE USER USER_NAME IDENTIFIED BY USER_PASSWORD;修改用户：ALTER USER USER_NAME IDENTIFILED BY NEW_PASS_WORD; ALTER USER USER_NAME ACCOUNT LOCK; ALTER USER USER_NAME ACCOUNT UNLOCK; ALTER USER USER_NAME PASSWORD EXPIRE显示当前用户：SHOW

10、 USER删除用户：DROP USER USER_NAME查看用户信息：SELECT * FROM USER_USERS; ORACLE有一个特殊的用户PUBLIC，请自己查阅相关信息。（4）、权限管理拥有CREATE SESSION权限的用户才能登录到数据库。可以直接把权限授给用户，也可以先建立角色，再赋予用户该角色，以实现权限分配授权：GRANT 权限|角色 on 数据库对象 TO USER_NAME WITH ADMIN OPTION 系统预定义的角色有：CONNECT、RESOURCE、DBA 如：GRANT CREATE SESSION TO USER DRP; GRANT CONN

11、ECT TO USER DRP; GRANT SELECT ON HR.EMP TO DRP; GRANT CREATE PROCEDUE TO USER DRP; GRANT CREATE ANY PROCEDURE TO USER DRP;回收：REVOKE 权限|角色 ON 数据库对象 FROM USER_NAME;查看用户当前权限：SELECT * FROM USER_SYS_PRIVS; SELECT * FROM USER_TAB_PRIVS;（5）、查看数据库对象信息 DESC 数据库对象;1.2、PLSQL DEVELOPERPL/SQL Developer是一个可为Oracl

12、e数据库开发存储程序单元的集成开发环境(IDE)，使用 PL/SQL Developer 可以方便地查看、编辑、编译、纠正、测试、调试、优化和查询数据库对象。（1）启动PLSQL DEVELOPERPLSQL Developer启动窗口如图1.2-1所示，输入用户名、密码、数据库名，选择合适的登录方式后，程序进入系统主窗口，如图1.2-2所示。图1.2-1 PLSQL DEVELOPER启动窗口对象浏览器窗口图1.2-2 PLSQL DEVELOPER 主窗口（2）、执行SQL语句在File的下拉菜单中选New（如图1.2-3）SQL WINDOW（如图1.2-4），进入SQL Window窗

13、口，在此窗口可输入SQL语句，并查看执行结果。图1.2-3 NEW窗口SQL 命令编辑区，执行结果出现在本窗口下方；如果执行PL/SQL块，则需要切换到OUTPUT查看输出。命令执行按钮，同F8图1.2-4 SQL Window窗口在File的下拉菜单中选NewCOMMAND WINDOW，进入COMMAND Window窗口，此窗口类似SQLPLUS的GUI界面，也可执行SQL命令。（3）创建数据库对象可以在SQL WINDOW窗口或COMMAND WINDOW窗口中执行创建数据库对象的语句。也可以用PLSQL DEVELOPER提供的模版创建。此处以创建表为例加以说明。在File的下拉菜单

14、中选New，再选择Table；或者直接在对象浏览器中的Tables目录上点右键选New，出现如图1.2-5所示界面。输入表名A，再切换到Columns页面，增加number类型的字段a，和varchar2(100)类型的字段b，如图1.2-6所示。再切换到Indexes页面，增加一个包含a字段的唯一索引IDX_A，如图1.2-7所示。点View SQL，可以看到生成的SQL语句，如图1.2-8所示。点Apply，即创建表A。这时可在右边的对象浏览器中查看刚刚创建的表。在对象浏览器窗口查找到刚创建的表A。右键点击A，在快捷菜单中选择Edit data，进入数据修改窗口，如图1.2-9所示。点击修

15、改按钮，进入修改状态；点击追加、删除按钮，可以分别追加一行和删除一行。要修改数据，则修改相应的列即可。修改数据后，记住点击“记住修改”按钮。可以多次修改。要提交修改，则需点击提交按钮COMMIT，要回滚修改，则需要点击Rollback回滚按钮ROLLBACK。图1.2-5 Table模版一般信息图1.2-6 Table模版列定义图1.2-7 Table模版索引图1.2-8 Table模版SQL语句在File的下拉菜单中选NewPROGRAM WINDOWPROCEDURE（如图1.2-10所示），进入存储过程模版向导窗口，输入过程名和参数后，点击“OK”进入编程窗口，在此可编辑存储过程。编辑完

16、成后点击F8，系统对程序进行编译，如果出现编译错误，会给出错误信息，并把光标定位到相应的行。存储函数、包的编辑、编译同上。Insert编辑切换Delete记入改变追加CommitRollback图1.2-9 表的编辑修改图1.2-10 程序编辑菜单（4）调试存储函数/存储过程首先打开调试窗口。打开一个空白的SQL窗口，输入函数或存储过程名，如EXISTOBJ，在对象名上点右键，再选择Test（如图1.2-11所示）；或在对象浏览器中展开函数或存储过程目录，选择所需调试的函数或存储过程，如EXISTOBJ，点右键，再选择Test（如图1.2-12所示）可进入调试窗口。图1.2-11 进入调试窗口

17、图1.2-12 进入调试窗口图1.2-13 调试窗口选择了Test以后打开的调试窗口如图1.2-13所示。其中的result是返回的结果参数，其他是需要传入的参数。具体调试过程，请查阅相关资料。1.3、APPLICATION EXPRESSAPEX是一个基于Oracle数据库的Web应用程序快速开发工具，它提供三大Oracle应用开发工具：应用程序构建器（APPLICATION BUILDER）。创建数据库驱动的动态WeB应用程序。SQL 工作室(SQL WORKSHOP)。浏览数据库对象，运行SQL查询、PL/SQL程序及图形化的查询构建器。使用程序(UTILITIES)。允许从纯文本和电子

18、表格上传和下载数据（数据的导入与导出）。用户可以使用其中的SQL 工作室，浏览数据库的用户、表、存储过程、存储函数、触发器、包等数据库对象，并完成各种数据库对象的创建工作。（1）APEX主窗口选择“开始”“程序”“Oracle Database 10g Express Edition”“转至数据库主页”，即进入APEX登录也卖弄，如图1.3-1所示。图1.3-1 APEX登录窗口输入用户名SYS，密码SYS，即以SYS用户登录到APEX主窗口，如图1.3-2所示。当前用户注销登录图1.3-2 APEX主窗口上述主窗口有管理、对象浏览器、SQL、实用程序四个选项。选择管理，进入管理窗口，依次可以

19、查看系统存储选项、查看内存使用信息、管理用户、监视服务器运行、查看服务器各种参数，如图1.3-3所示。图1.3-3 APEX管理窗口进入对象浏览器，可以浏览表、视图、索引、序列、类型、包、过程、函数、触发器、数据库链接、同义词等各种对象；查看这些对象的结构、代码；修改对象、创建对象，如图1.3-4所示。1.3-4 APEX对象浏览窗口进入SQL，可以执行SQL命令、运行SQL脚本、使用查询构建器构建查询。进入实用程序可以加载下载数据、创建DLL链接库、创建各种对象报表。1.4 小结建议实用APEX运行、测试各种代码；用PLSQL DEVELOPER调试、修改包、函数、过程；简单的管理功能实用S

20、QL*PLUS。二、J2EE应用编程环境NetBeans是SUN公司推荐的一个免费的J2EE开发环境，期中版本对UML的支持较好，建议选用。2.1、J2EE项目类型启动NetBeans，即进入项目开发平台。依据系统安装内容的不同，实用NetBeans所能开发的项目也有所不同。选择“文件”“新建项目”，即进入如图2.11所示的项目类型选择窗口。（1）Java选择“Java”可创建各种标准的Java SE应用程序（包括控制台程序、窗口界面程序）。图2.1-1 项目选择窗口选择“Java桌面程序”可创建基于 Swing 的应用程序。NetBeans提供一个基于JSwing 的桌面应用程序构建框架，相

21、应的模板提供基本的应用程序基础结构，如菜单栏、永久保持窗口状态以及状态栏。利用此模板，还可以生成访问数据库的 GUI 界面代码。如果用户理解数据库系统架构的知识，了解Jswing，实用该框架可大大简化企业级C/S架构应用的开发。选择“基于现有源代码的Java项目”可以打开一个现有的Java项目，比如基于Eclips开发的Java项目。下面介绍创建Java SE项目的过程。选择Java应用程序，进入新建Java应用程序窗口，如图2.2所示。注意，最好不要在各选项中图2.1-2 Java SE应用项目设置窗口输入中文字符。如果选择了“使用专用文件夹存储库”，则选购目将有一个专门的文件夹来存放导入的

22、第三方库。如果选择了“创建主类”，则将创建一个项目的主类（如Main.Java）。如果你选择运行项目就会自动加载该类（即通过该类运行项目）。Java SE项目的项目浏览窗口如图2.1-3所示。节点“源包”下按照package的层次要求依次存放系统的Java类。如果要在某个包下添加一个类，只须右键点击相应包节点，在随后的快捷菜单中选择即可。节点“库”下存放第三方库。如果要写数据库应用程序，就需要把驱动程序加到“库”节点下。右键点击“库”，选择“添加jar/文件夹”后按照提示，即可把Oracle的JDBC驱动加载到项目中来。图2.1-3 Java SE项目（2）Java Web选择“Java We

23、b”可创建基于B/S架构的应用程序，如图2.1-4所示。选择“Web应用程序”进入Web应用程序项目设置窗口，如图2.1-5所示。输入项目名称、项目位置、设置相关选项后，点击“下一步”，进入项目服务器设置窗口，如图2.1-6所示。图2.1-4 Java Web项目选择窗口“服务器”选项设置将用于项目测试的Web服务器软件，一般选择Apache tomcat。“Java EE版本”设置Java EE的版本（影响Servlet、JSP等的版本），“上下文路径”设置项目的上下文。如图设置为“Database2”，则访问该项目的路径就为http:/localhost:8080/Database2/*。

24、点击“下一步”进入框架设置窗口，如图2.1-6所示。如果你向在项目中使用Struts、Spring、Hibernate、JSF等框架，就勾选相应项。点击“完成”，将结束Java Web项目的创建工作。图2.1-5 Web项目设置窗口图2.1-5 服务器设置窗口图2.1-6 框架设置窗口Java Web项目的项目浏览窗口如图2.1-7所示。节点“Web页”下存放页面程序，如HTML文件、JSP文件。在此节点下还可根据项目的需要，创建基本不同的文件夹，一个文件夹对应一个功能模块。节点“源包”下存放项目的各种Java文件，比如JavaBean、Servlet、领域模型、实体类等。节点“配置文件”下的

25、“Web.xml”文件是最重要的配置文件，用户开发的Sevlet均需要在此配置。节点“库”下存放第三方库。如果要写数据库应用程序，同样需要把驱动程序加到“库”节点下。图2.1-7 Java Web项目2.2、一个可访问数据库的Java控制台项目新建Java项目Database1，在项目的“源包”节点添加一个“JdbcHelper”包，在改包下新建一个Java类“JdbcUtil.java”，该类使用单例模式涉及，提供创建链接对象，释放相关资源功能，代码如下。 在“Database1”包下创建“LobTest.java”，该类可访问数据库的大对象，代码如下：2.3、一个Java Web项目新建W

26、eb项目Database2，在“Web页”节点下创建文件夹“login”，专门存放项目“登录”模块的页面程序。在“login”文件夹下创建一个Login.jsp程序，该程序提交一个表单LoginForm，该表单能够接受用户输入的登录信息，并把该信息提交给服务器上的Servlet程序LoginServlet.java。LoginServlet.java提取接收到的信息，把它们封装到一个LoginUser对象，然后提交给一个DAO（数据访问）对象LoginUserDaoImp去处理，LoginUserDaoImp查询数据库，并返回结果给LoginServlet，LoginServlet根据返回结果

27、确定用户登录结果。2.3.1、创建LoginUser.java该类封装登录用户信息，属于三层架构的业务逻辑层的领域模型。在“源包”节点创建包“”，在该包中新建一个Java类LoginUser.java。如图2.3-1所示。在代码编辑区，右击类LoginUser的内部区域，在出现的快捷菜单中选择“插入代码”“添加属性”，出现属性添加窗口，如图2.3-2所示，完成属性userName的编码。同法完成userPass的编码，所得结果如图2.3-3所示。、创建LoginUserDao.java和LoginUserDaoImp.java这两个类属于三层架构中数据层的数据访问类，前者是一个接口，后者是实现

28、类。图2.3-1 LoginUser.java编辑窗口图2.3-2 创建属性图2.3-3 LoginUser.javaLoginUserDao.java和LoginUserDaoImp.java的代码如下：、LoginServlet.javaLoginServlet.java属于三层架构的表示层。新建包“”，右击该包，选择新建Servlet，输入“LoginServlet”，窗口如图2.3-4所示。接着需要确定该Servlet在Web.xml中的配置，如图2.3-5。点击“完成”，NetBeans将创建LoginServlet类的框架，并在配置文件添加相应信息。图2.3-4 新建Servlet

29、窗口图2.3-5 Servlet配置窗口Web.xml文件内容如下：LoginServlet类继承自HttpServlet类，NetBeans自动重载了DoGet（）和DoPost()，并把上述方法的调用都转向了processRequest（）方法，所以我们只需完成该方法即可，代码如下。这里用到了J2EE平台的两种重定向技术、以及用Session追踪用户的技术。、Login.jsp与LoginSuccess.jsp在“Web页”节点下创建“login”文件夹，在该文件下创建Login.jsp，如图2.3-6所示。图2.3-6 Login.jsp编辑窗口把“Meta数据”标签拖到在上述窗口的当前

30、光标指定位置，添加控制页面缓存的语句，得到head部分如下：在body部分，把表单标签、表格标签、文本输入标签、按钮标签依次拖到合适的位置，最后得到如下代码：LoginSuccess.jsp中先对访问用户进行省份识别，如果当前session中没有存储USER对象，说明来访用户没有经过登录验证，系统被重定向到登录页面，代码如下：项目的结构如图2.3-7所示。项目运行的登录窗口如图2.3-8所示。图2.3-7项目结构 图2.3-8 项目运行登录窗口三、数据库表结构本课程的实验一、实验二、实验三、实验四所使用的表结构均可用如下的SQL语句创建。CREATE TABLE Student( SNO CH

31、AR (9) PRIMARY KEY,Sname VARCHAR(20) not null,SSex CHAR(3) CHECK(Ssex in(男,女),Sage NUMBER(2,0),Sdept varCHAR2(60),SAvgGrade number(4,1),SPicture BLOB)CREATE TABLE Course( Cno CHAR(4) PRIMARY KEY, Cname varCHAR2(60) unique not null,Cpno CHAR(4),Ccredit number(2,1), FOREIGN KEY (Cpno) REFERENCES Cours

32、e(Cno)CREATE TABLE SC(Sno CHAR(9),Cno CHAR(4),Grade number(3,0),PRIMARY KEY(Sno,Cno),FOREIGN KEY (Sno) REFERENCES Student(Sno),FOREIGN KEY (Cno) REFERENCES Course(Cno)实验一 存储子程序1、实验目的、掌握存储过程的概念，学会编写简单的存储过程及其使用。、了解存储过程调用。2、实验性质验证性实验3、实验导读3.1、PL/SQL基础知识（1）数据类型PL/SQL有四种数据类型：标量类型、引用类型、复合类型和大对象类型。标量类型只能存储

33、单值的数据，其中：存储字符/字符串的有CHAR, CHARACTER, LONG, LONG RAW, NCHAR, NVARCHAR2, RAW, ROWID, STRING, UROWID, VARCHAR, VARCHAR2。LONG和LONG RAW不推荐使用。存储数字的有BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, DEC, DECIMAL, DOUBLE PRECISION, FLOAT, INT, INTEGER, NATURAL, NATURALN, NUMBER,NUMERIC, PLS_INTEGER, POSITIVE, POS

34、ITIVEN, REAL, SIGNTYPE, SMALLINT。存储布尔值的有BOOLEAN。存储日期时间的有DATE、TIMESTAMP、TIMESTAMP WITH TIMEZONE、TIMESTAMP WITH LOCAL、INTERVAL。存储二进制的有RAW、LONG RAW。引用类型有REF CURSOR和REF。大对象数据类型有BLOB、CLOB、BFILE。复合类型需要用户自己定义。如果需要在一个结构中存储不同类型的数据，就用RECORD（记录）。如果需要在一个结构中存储相同类型的数据（对象），就用PL/SQL的集合类型。PL/SQL中集合类型分为：变长数组VARRAYS能保

35、存固定数量的元素(但可以在运行时改变它的大小)，使用有序数字作为下标，与JAVA语言中的数组类似。定义语法为TYPE type_nameISVARRAY|VARYINGARRAY(size_limit)OFelement_typeNOTNULL;嵌套表可容纳任意个数的元素，使用有序数字作下标，与JAVA语言中的SET类似。定义语法为TYPEtype_nameISTABLEOFelement_typeNOTNULL；索引表，也叫关联数组，可容纳元素，但使用关键字存取，与JAVA语言中的Hash表类似。定义语法为TYPEtype_nameISTABLEOFelement_typeNOTNULL I

36、NDEXBYBINARY_INTEGER|PLS_INTEGER|VARCHAR2(size_limit);集合类型提供几个属性或方法方便对其中元素的存取：COUNT属性，返回集合中元素的个数DELETE方法，DELETE删除最后整个集合，DELETE(X)删除集合中的第X个元素，DELETE(X,Y)删除集合中从X到Y的元素。EXISTS(x)方法，判断位于位置x处的元素是否存在，如果存在则返回TRUE，如果x大于集合的最大范围，则返回FALSE。EXTEND方法，没带参数的EXTEND将一个NULL元素添加到集合的末端，EXTEND(x)将x个NULL元素添加到集合的末端，EXTEND(x

37、,y)将x个位于y的元素添加到集合的末端。FIRST属性，返回集合第一个元素的位置。LAST属性，返回集合最后一个元素的位置。NEXT(x)属性，返回位置为x处的元素后面的那个元素。PRIOR(x)属性，返回x处的元素前面的那个元素。TRIM方法，没有参数的TRIM是从集合中末端删除一个元素。 TRIM(x)从集合末端删除x个元素，其中x要小于集合的COUNT数，否则系统会提示出错。集合类中数据的便利可用下面的代码段：v_Count:=v_Pwd.FIRST; WHILE v_Count=v_Pwd.LAST LOOP DBMS_OUTPUT.PUT_LINE(v_Pwd(v_Count);

38、v_Count:=v_Pwd.NEXT(v_Count); END LOOP;编程过程中可引用数据库表的列的数据类型：表名.列%TYPE（注意，变量名%TYPE也可用）；也可以引用数据库表记录的数据类型：表名%ROWTYPE；还可引用游标返回的结果集的记录类型：游标名ROWTYPE。（2）运算符及其运算级别幂运算（*）、符号运算（、）、乘除运算（*、/）、加减及连接运算（、|）、比较运算（=, , =, , !=, =, =, IS NULL, LIKE, BETWEEN, IN）、逻辑运算（NOT、AND、OR）（3）程序控制结构三种控制结构如下：IFTHEN语句：DECLARE sales

39、 NUMBER(8,2) := 10100; quota NUMBER(8,2) := 10000; bonus NUMBER(6,2); emp_id NUMBER(6) := 120;BEGIN IF sales (quota + 200) THEN bonus := (sales - quota)/4; UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id; END IF;END;IFTHEN.ELSE语句：DECLARE sales NUMBER(8,2) := 12100; quota NUMB

40、ER(8,2) := 10000; bonus NUMBER(6,2); emp_id NUMBER(6) := 120;BEGIN IF sales (quota + 200) THEN bonus := (sales - quota)/4; ELSE bonus := 50; END IF; UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id;END;IF嵌套语句：DECLARE sales NUMBER(8,2) := 12100; quota NUMBER(8,2) := 10000; bonu

41、s NUMBER(6,2); emp_id NUMBER(6) := 120;BEGIN IF sales (quota + 200) THEN bonus := (sales - quota)/4; ELSE IF sales quota THEN bonus := 50; ELSE bonus := 0; END IF; END IF; UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id;END;IFTHENELSIF语句：DECLARE grade CHAR(1);BEGIN grade := B

42、; IF grade = A THEN DBMS_OUTPUT.PUT_LINE(Excellent); ELSIF grade = B THEN DBMS_OUTPUT.PUT_LINE(Very Good); ELSIF grade = C THEN DBMS_OUTPUT.PUT_LINE(Good); ELSIF grade = D THEN DBMS_OUTPUT. PUT_LINE(Fair); ELSIF grade = F THEN DBMS_OUTPUT.PUT_LINE(Poor); ELSE DBMS_OUTPUT.PUT_LINE(No such grade); END

43、 IF;END;CASEWHEN语句：DECLARE grade CHAR(1);BEGIN grade := B; CASE grade WHEN A THEN DBMS_OUTPUT.PUT_LINE(Excellent); WHEN B THEN DBMS_OUTPUT.PUT_LINE(Very Good); WHEN C THEN DBMS_OUTPUT.PUT_LINE(Good); WHEN D THEN DBMS_OUTPUT.PUT_LINE(Fair); WHEN F THEN DBMS_OUTPUT.PUT_LINE(Poor); ELSE DBMS_OUTPUT.PUT

44、_LINE(No such grade); END CASE;END;SERACHED CASE语句：DECLARE grade CHAR(1);BEGIN grade := B; CASE WHEN grade = A THEN DBMS_OUTPUT.PUT_LINE(Excellent); WHEN grade = B THEN DBMS_OUTPUT.PUT_LINE(Very Good); WHEN grade = C THEN DBMS_OUTPUT.PUT_LINE(Good);WHEN grade = D THEN DBMS_OUTPUT.PUT_LINE(Fair); WHE

45、N grade = F THEN DBMS_OUTPUT.PUT_LINE(Poor); ELSE DBMS_OUTPUT.PUT_LINE(No such grade); END CASE;END;LOOP循环：DECLARE credit_rating NUMBER := 0;BEGIN LOOP credit_rating := credit_rating + 1;EXIT WHEN credit_rating3 IF credit_rating 3 THEN EXIT; - exit loop immediately END IF; END LOOP; - control resume

46、s here DBMS_OUTPUT.PUT_LINE (Credit rating: | TO_CHAR(credit_rating); IF credit_rating 3 THEN RETURN; - use RETURN not EXIT when outside a LOOP END IF; DBMS_OUTPUT.PUT_LINE (Credit rating: | TO_CHAR(credit_rating); END;WHILE循环：done := FALSE;WHILE NOT done LOOPsequence_of_statementsdone := boolean_ex

47、pression;END LOOP;FOR循环：DECLARE p NUMBER := 0;BEGIN FOR k IN 1.500 LOOP - calculate pi with 500 terms p := p + ( ( (-1) * (k + 1) ) / (2 * k) - 1) ); END LOOP;p := 4 * p; DBMS_OUTPUT.PUT_LINE( pi is approximately : | p ); - print resultEND;BEGIN FOR i IN REVERSE 1.3 LOOP - assign the values 1,2,3 to

48、 i DBMS_OUTPUT.PUT_LINE (TO_CHAR(i); END LOOP;END;（4）几个系统函数用于异常处理的SQLCODE、SQLERRM，用于类型转换的HEXTORAW、RAWTOHEX、TO_CHAR、TO_DATE、TO_NUMBER，用于NULL处理的NVL、NULLIF。（5）伪列用于序列的CURRVAL和NEXTVAL、用于树形结构查询的LEVEL、用于追踪行物理地址的ROWID、用于追踪行号的ROWNUM。3.2、游标游标是DBMS为用户提供的一种特殊控制结构，由一个内存缓冲区和一个游标指针组成；其中缓冲区存放SQL语句的执行结构；用户可通过游标指针获取缓

49、冲区内的每一条记录。一般要先声明游标，然后才能使用游标。如果游标声明时已经与SQL语句绑定，则称为静态游标，否则称为动态游标。静态游标又分为隐式游标和显式游标。ORACLE用游标属性反映最近一次被执行的DML、DDL语句（INSERT, UPDATE, DELETE, SELECT INTO, COMMIT, ROLLBACK）的结果信息，它们是%FOUND（标识DML操作是否改变了行记录）, %ISOPEN（标识游标是否打开），%NOTFOUND（标识DML操作是否没有改变行记录）, %ROWCOUNT（标识DML语句影响的行数）。显式游标用“游标名%*”格式获取游标属性，隐式游标用“SQL

50、%*”格式获取游标属性。显式游标仅用于查询处理。使用显式游标需要经过声明、打开、推进并获取数据、关闭四个步骤。例1、分别处理两个游标DECLARE v_jobid employees.job_id%TYPE; - variable for job_id v_lastname employees.last_name%TYPE; - variable for last_name CURSOR c1 IS SELECT last_name, job_id FROM employees WHERE REGEXP_LIKE (job_id, SHT_CLERK); v_employees employe

51、es%ROWTYPE; - record variable for row CURSOR c2 is SELECT * FROM employees WHERE REGEXP_LIKE (job_id, ACADFIMKSA_MANGR);BEGIN OPEN c1; - open the cursor before fetching LOOP FETCH c1 INTO v_lastname, v_jobid; - fetches 2 columns into variables EXIT WHEN c1%NOTFOUND; DBMS_OUTPUT.PUT_LINE( RPAD(v_last

52、name, 25, ) | v_jobid ); END LOOP; CLOSE c1; DBMS_OUTPUT.PUT_LINE( ); OPEN c2; LOOP FETCH c2 INTO v_employees; - fetches entire row into the v_employees record EXIT WHEN c2%NOTFOUND; DBMS_OUTPUT.PUT_LINE( RPAD(v_employees.last_name, 25, ) | v_employees.job_id ); END LOOP; CLOSE c2;END;例2、从游标中一次读取数据D

53、ECLARE TYPE IdsTab IS TABLE OF employees.employee_id%TYPE; TYPE NameTab IS TABLE OF employees.last_name%TYPE; ids IdsTab; names NameTab; CURSOR c1 IS SELECT employee_id, last_name FROM employees WHERE job_id = ST_CLERK;BEGIN OPEN c1; FETCH c1 BULK COLLECT INTO ids, names; CLOsE c1;- Here is where yo

54、u process the elements in the collections FOR i IN ids.FIRST . ids.LAST LOOP IF ids(i) 140 THEN DBMS_OUTPUT.PUT_LINE( ids(i) ); END IF; END LOOP; FOR i IN names.FIRST . names.LAST LOOP IF names(i) LIKE %Ma% THEN DBMS_OUTPUT.PUT_LINE( names(i) ); END IF; END LOOP;例3、用FOR循环处理游标DECLARE CURSOR c1 IS SEL

55、ECT last_name, job_id FROM employees WHERE job_id LIKE %CLERK% AND manager_id 120;BEGIN FOR item IN c1 LOOP DBMS_OUTPUT.PUT_LINE(Name = | item.last_name | , Job = | item.job_id); END LOOP;END;例4、动态游标DECLARE TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE; emp_cv empcurtyp; emp_rec employees%RO

56、WTYPE;BEGIN OPEN emp_cv FOR SELECT * FROM employees WHERE employee_id w3,v1=w1);-按照形参名称传入实参，没有传入的使用缺省值 dbms_output.put_line(w1=|w1| w3=|w3);END;SQLP*LUS调用SQLVAR w1 number;SQLVAR w3 number;SQLEXECUTE :w1 :=10;SQLEXECUTE testCall(:w1,250,:w3);用CALL testCall(:w1,250,:w3);也可SQLPRINT :w1;SQL PRINT :w3;SQ

57、LEXECUTE testCall(v1=:w1,v3=:w3);-不能用CALL testCall(v1=:w1,v3=:w3);PL/SQL中不能存储函数不能直接调用函数，函数必须出现在SQL表达式或PL/SQL表达式中例2、存储函数调用CREATE OR REPLACE FUNCTION testFunc (v1 in out number, v2 in number:=100,v3 out number) RETURN numberAS Result number; BEGIN v1:=v2+v1; v3:=v2+55; Result:=v1+v2+v3; return(Result)

58、;END TESTFUNC;PL/SQL调用DECLARE w1 number:=10; w3 number; t1 number; t2 number;BEGIN t1:=testFunc(w1,250,w3);-依次传入 dbms_output.put_line(w1=|w1| w3=|w3| t1=|t1); t2:=testFunc(v3=w3,v1=w1); dbms_output.put_line(w1=|w1| w3=|w3| t1=|t2);END;SQLP*LUS调用SQL var w1 number;SQL var w2 number;SQL var t1 number;S

59、QL var t2 number;SQL execute :w1:=10;SQL execute :t1:=testFunc(:w1,250,:w3);/函数调用SQL execute :t1:=testFunc(v1=:w1,v3=:w3);SQL call testFunc(v1=:w1,v3=:w3) into :t2;/调用出错SQL call testFunc(:w1,250,:w3) into :t2;、存储子程序的调试（1）检索用户的子程序或触发器SELECT object_name FROM user_objects WHERE object_type=PROCEDURE|FU

60、NCTION；（2）当提交子程序时，系统会对代码进行编译，可用SHOW ERRORS查看出错信息。建议使用PL/SQL DEVELOPER调试。（3）查看子程序的代码SQLCOLUMN LINE FORMAT 9999SQLCOLUMN TEST FORMAT A70SQLSELECT line,text FROM user_source WHERE name=子程序名；、删除子程序SQLDROP PROCEDURE|FUNCTION 子程序名3.4、包包是一个独立存储的数据库对象，由包规范和包体组成，用来分类组织和管理子程序和数据项。用户可以使用包来组织自己的应用，系统也以包的形式提供PL/