老二牛车第六章上机课游标管理

老二 牛车 教育 Oracle数据库应用 上机 课 游标管理 目标： 能够使用游标 第一部分指导 本阶段重点介绍如何使用游标来编写程序，还将介绍如何声明、打开、提取游标中的记录以及最终关闭游标，最后介绍创建显式游标、 REF 游标和循环游标的方法。 阶段一 ：隐式游标的工作原理 需求说明： 公司的经理想根据职员编号查看其所在的部门名称，部门名称存储在 dept_details 表中。请编写一段代码以接收职员编号并显示相应的部门名称。 实现思路： 此代码需要声明两个 VARCHAR2 类型的变量。用户输入 empno，该值与 employee 表的 empno一致 。然后将此 deptcode 与 dept_details 表的 deptcode 列相比较，如果二者一致，则将 dept_details表的相应 deptname 返回给用户。 参考步骤： （ 1） 启动“ SQL Plus”。 （ 2） 在 SQL* Plus 中输入下列代码。 SQLSET SERVEROUTPUT ON SQLDECLARE deptnm VARCHAR2(25)； dept_code VARCHAR2(10)； BEGIN SELECT deptcode INTO dept_code FROM employee WHERE empno=&Employee_Number； SELECT deptname INTO deptnm FROM dept_details WHERE deptcode = dept_code； DBMS_OUTPUT.PUT_LINE(部门名称： )； DBMS_OUTPUT.PUT_LINE(-)； DBMS_OUTPUT.PUT_LINE(deptnm)； EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(职员不存在 )； END； 如果输入的 empno 在表中存在，则练习 1 的输出结果如图 6.1 所示。 老二 牛车 教育 Oracle数据库应用 上机 课 如果输入的 empno 在表中不存在，则练习 1 的输出结果如图 6.2 所示。 阶段二 ：显式游标的工作原理 需求说明： 公司的助理销售经理想将总净价大于 2000 元的项目的项目编号和总价值单独存储在 totalworth表中。要求编写一段代码以显示总净价大于 2000 元的项目的项目编号和总价值。 实现思路： 此代码需要声明一个游标以检索结果集的行，还需声明一个 %ROWTYPE 类型的变量。 Itemcursor游标从 itemfile 表中选择和提取所有行。将 itemrate 和 qty-hand 列中的值相乘，并检查积是否大于 2000。如果积大于 2000，则将这两列对应的项目和产品插入 totalworth 表。 参考步骤： （ 1） 启动“ SQL Plus”。 （ 2） 在 SQL* Plus 中输入下列代码。 DECLARE itemrec itemfile%ROWTYPE； CURSOR itemcursor IS SELECT * FROM itemfile； BEGIN OPEN itemcursor； LOOP FETCH itemcursor INTO itemrec； EXIT WHEN itemcursor%NOTFOUND； IF itemrec.itemrate * itemrec.qty_hand 2000 THEN INSERT INTO totalworth VALUES (itemrec.itemcode,itemrec.itemrate * itemrec.qty_hand)； -Dbms_Output.put_line(itemrec.itemrate * itemrec.qty_hand)； END IF； END LOOP； COMMIT； CLOSE itemcursor； END； / 老二 牛车 教育 Oracle数据库应用 上机 课 如果以上程序执行成功，则将显示消息“ PL/SQL 过程已成功完成”。 可以使用 SELECT 语句检索插入到 totalworth 表的记录。 SQL SELECT*FROM totalworth 练习 2 的输出结果如图 6.3 所示。 阶段三 ：循环游标的工作原理 需求说明： 公司的总经理想要查知各个部门的所有职员的姓名。要求编写一段代码以显示部门名称和此部门的所有职员姓名 实现思路： 在此代码中，需自动声明、打开和关闭游标。由于要检索所有部门，因此使用循环游标来检索行非常有效。从 dept_details 表中选择 deptcode 和 deptname 列，并将 deptcode 列于 employee 表的 deptcode 列相比较。如果符合条件，则向用户返回相应的职员姓名。 参考步骤： （ 1） 启动“ SQL Plus”。 （ 2） 在 SQL* Plus 中输入下列代码。 SQLSET SERVEROUTPUT ON SQL BEGIN FOR dept_details IN (SELECT deptcode, deptname FROM dept_details) LOOP DBMS_OUTPUT.PUT_LINE(部门 )； DBMS_OUTPUT.PUT_LINE(-)； DBMS_OUTPUT.PUT_LINE(dept_details.DEPTNAME)； FOR employee in (SELECT empno, ename FROM employee WHERE DEPTCODE = DEPT_DETAILS.DEPTCODE) LOOP DBMS_OUTPUT.PUT_LINE(职员： |employee.ENAME)； END LOOP； END LOOP； END； / 练习 3 的输出结果如图 6.4 所示。 老二 牛车 教育 Oracle数据库应用 上机 课 阶段四 ： REF 游标的工作原理 需求说明： 公司的总经理想输入部门代码以检索职员姓名。要求编写一段代码，通过在表中输入部门代码来检索该部门的职员 实现思路： 需要声明一个 REF 游标类型的游标，一个 %ROWTYPE 类型的变量，还需要声明一个 optionval变量以接收部门代码。如果 deptcode与用户输入的值相符，则从 employee表中检索相应的 ename。 参考步骤： （ 1） 启动“ SQL Plus”。 （ 2） 在 SQL* Plus 中输入下列代码 。 SQLSET SERVEROUTPUT ON SQL DECLARE TYPE cursor_type IS REF CURSOR； cursor_ex cursor_type； empTable employee%ROWTYPE； optionval VARCHAR2(10)； BEGIN optionval:=&Department_Code； OPEN cursor_ex FOR SELECT * FROM employee WHERE deptcode=optionval； LOOP FETCH cursor_ex INTO empTable； EXIT WHEN cursor_ex%NOTFOUND； DBMS_OUTPUT.PUT_LINE( )； DBMS_OUTPUT.PUT_LINE(职员姓名： |empTable.ename)； END LOOP； 老二 牛车 教育 Oracle数据库应用 上机 课 CLOSE cursor_ex； END； / 练习 4 的输出结果如图 6.5 所示。 第 二 部分 练习 1 vendor-master 表包含有关供应商的信息，详细信息包括 vencode、 venname、 venadd1、 venadd2、venadd3 和 tel-no 。 order-master 表包含 orderno、 odate 、 ostatus、 vencode 和 del-date 等消息。编写一段程序以显示在 vendor-master 表中其 ostatus 为“ p”的记录 venname 。如果 ostatus 为“ c”，则显示消息“订单已确认”。如果 orderno 不存在，则使用显式游标显示消息“数据不存在 ”。 2 使用显式游标编写一个程序，以显示 vendor-master 和 order-master 表中的 orderno、 odate、 del-date和 vencode。 作业 1 以 SCOTT 用户身份登录，编写一段代码，提示用户输入要查看哪个表中的记录，如果用户输入“ E”，则显示 EMP 表中的 EMPNO、 ENAME、 JOB 和 SAL 列的值；如果用户输入“ D”，则显示 DEPT 表中的 DEPTNO、 DNAME 和 LOC 列的值。 提示：使用 REF 游标和循环游标。 2 em