oracle上机练习5

oracle上机练习（五）

一.PL/SQL快速学习：

1.PL/SQL 是一项ORACLE 的技术,是ORACLE 的过程型语言，它由标准的SQL 语句和一系列可以让你在不同的情况下对SQL 语句的执行进行控制的命令组成。

2.PL/SQL 块的结构:

    PL/SQL 是一种块结构语言,也就是说PL/SQL 的程序可以分成逻辑块来写。块是PL/SQL 代码的逻辑单元，包括至少一个BEGIN 部分和可以选择的DECLARE 以及EXCEPTION 部分。

    PL/SQL 块的基本结构：

SYNTAX:

BEGIN -- optional, denotes beginning of block

DECLARE -- optional, variable definitions

BEGIN -- mandatory, denotes beginning of procedure section

EXCEPTION -- optional, denotes beginning of exception section

END -- mandatory, denotes ending of procedure section

END -- optional, denotes ending of block

    (1)在PL/SQL 的DECLARE 部分包括了变量、常量、指针和特殊数据类型的定义。

    (2) BEGIN部分是PL/SQL 语句块中的必须部分，在这一部分将会使用变量和用户指针来操作数据库中的数据。

    (3)EXCEPTION 部分在PL/SQL 语句块中是可以选择的，如果在这一部分被省略而遇到异常的时候该块就会终止了。

3.由基本的PL/SQL块所组成的PL/SQL程序，可组成不同的程序形式，它们的用途和适用性各不相同。程序形式大致有以下几种：

    (1)无名块：也就是没有命名的PL/SQL块，它可以是嵌入某一个应用之中的一个PL/SQL块。无名块在所有PL/SQL环境中都适用。

    (2)存储过程/函数（Procedure/Function）：命名的PL/SQL块，它可以接受参数，并且可       以重复的被调用。

    (3)包（Package）：命名的PL/SQL块，由一组相关的过程、函数和标识符组成。

    (4)数据库触发器（Triggers）：是与一个具体数据库表相关联的PL/SQL存储程序。每当一个SQL操作影响到该数据库表时，系统就自动执行相应的数据库触发器。

4.PL/SQL中的控制语句:

    (1)条件语句：IF...THEN

       语法：

       IF condition1 THEN

         statement1；

       ELSIF condition2 THEN（可选）

         statement2；

       ELSE（可选）

         statement3；

       END IF

    (2)循环语句：

       i.简单循环：

         LOOP

           statement1；

         END LOOP

       ii.FOR循环：

       iii.WHILE循环：

二.实例：

   1.使用的表为：PAY_TABLE，     结构：

         Name          Null?        Type

        

         NAME NOT      NULL         VARCHAR2(20)

         PAY_TYPE      NOT NULL     VARCHAR2(8)

         PAY_RATE      NOT NULL     NUMBER(8,2)

         EFF_DATE      NOT NULL     DATE

         PREV_PAY                   NUMBER(8,2)

    数据：

       NAME            PAY_TYPE      PAY_RATE      EFF_DATE     PREV_PAY

      

       SANDRA SAMUELS    HOURLY      12.50           01-JAN-04

       ROBERT BOBAY      HOURLY     11.50        15-MAY-03

       KEITH JONES      HOURLY      10.00         31-OCT-04

       SUSAN WILLIAMS    HOURLY      9.75         01-MAY-04

       CHRISSY ZOES      SALARY        50000.00     01-JAN-04

       CLODE EVANS       SALARY       42150.00     01-MAR-04

      JOHN SMITH         SALARY        35000.00     15-JUN-03

      KEVIN TROLLBERG    SALARY       27500.00    15-JUN-03

   2.操作要求：要给为你工作的时间超过了六个月的个人增加薪金。符合条件的钟点工的薪金增加4% 而符合条件的雇员的薪金需要增加5% 。

PL/SQL 的脚本：

     set serveroutput on

     BEGIN

       DECLARE

          UnknownPayType exception;

          cursor pay_cursor is

          select name, pay_type, pay_rate, eff_date,

          sysdate, rowid

          from pay_table;

          IndRec pay_cursor%ROWTYPE;

          cOldDate date;

          fNewPay number(8,2);

       BEGIN

         open pay_cursor;

         loop

           fetch pay_cursor into IndRec;

           exit when pay_cursor%NOTFOUND;

           cOldDate := sysdate - 180;

           if (IndRec.pay_type = 'SALARY') then

              fNewPay := IndRec.pay_rate * 1.05;

           elsif (IndRec.pay_type = 'HOURLY') then

              fNewPay := IndRec.pay_rate * 1.04;

          else

              raise UnknownPayType;

           end if;

           if (IndRec.eff_date < cOldDate) then

              update pay_table

              set pay_rate = fNewPay,

                  prev_pay = IndRec.pay_rate,

                  eff_date = IndRec.sysdate

               where rowid = IndRec.rowid;

               commit;

          end if;

         end loop;

      close pay_cursor;

      EXCEPTION

         when UnknownPayType then

         dbms_output.put_line('=======================');

         dbms_output.put_line('ERROR: Aborting program.');

         dbms_output.put_line('Unknown Pay Type for Name');

         when others then

         dbms_output.put_line('ERROR During Processing. See the DBA.');

       END;

   END;

/

三.练习：

   1.对PAY_TABLE表写一个查询所有记录的过程。

   2.对PAY_TABLE表写一个插入一条记录的过程，要求输入参数为一条记录。

   3.写一个过程实现如下要求：对工作时间超过8个月的职员，如果PAY_TYPE

     是HOURLY,则改为SALARY,并将PAY_RATE改为按每天7小时工作的年薪.                                     

 

oracle上机练习5答案

2009-03-12 17:10

练习五

PREPARATION:

[1]Create A Table

CREATE TABLE PAY_TABLE(NAME VARCHAR2(20) NOT NULL,PAY_TYPE VARCHAR2(8) NOT NULL,PAY_RATE NUMBER(8,2) NOT NULL,EFF_DATE DATE NOT NULL,PREV_PAY NUMBER(8,2));

[2]Insert Records

INSERT INTO PAY_TABLE VALUES('SANDRA SAMUELS','HOURLY',12.50,TO_DATE('2004-01-01','YYYY-MM-DD'),0);

INSERT INTO PAY_TABLE VALUES('ROBERT BOBAY','HOURLY',11.50,TO_DATE('2003-05-15','YYYY-MM-DD'),0);

INSERT INTO PAY_TABLE VALUES('KEITH JONES','HOURLY',10.00,TO_DATE('2004-10-31','YYYY-MM-DD'),0);

INSERT INTO PAY_TABLE VALUES('SUSAN WILLIAMS','HOURLY',9.75,TO_DATE('2004-05-01','YYYY-MM-DD'),0);

INSERT INTO PAY_TABLE VALUES('CHRISSY ZOES','SALARY',50000.00,TO_DATE('2004-01-01','YYYY-MM-DD'),0);

INSERT INTO PAY_TABLE VALUES('CLODE EVANS','SALARY',42150.00,TO_DATE('2004-03-01','YYYY-MM-DD'),0);

INSERT INTO PAY_TABLE VALUES('JOHN SMITH','SALARY',35000.00,TO_DATE('2003-06-15','YYYY-MM-DD'),0);

INSERT INTO PAY_TABLE VALUES('KEVIN TROLLBERG','SALARY',27500.00,TO_DATE('2003-06-15','YYYY-MM-DD'),0);

IMPLEMENTATION:

[1]对PAY_TABLE表写一个查询所有记录的过程。

SET SERVEROUTPUT ON

     BEGIN

       DECLARE

          UNKNOWNPAYTYPE EXCEPTION;

          CURSOR PAY_CURSOR IS

          SELECT NAME, PAY_TYPE, PAY_RATE, EFF_DATE,

          SYSDATE, ROWID

          FROM PAY_TABLE;

          INDREC PAY_CURSOR%ROWTYPE;

      

      BEGIN

         OPEN PAY_CURSOR;

         LOOP

           FETCH PAY_CURSOR INTO INDREC;

           EXIT WHEN PAY_CURSOR%NOTFOUND;

           DBMS_OUTPUT.PUT_LINE(INDREC.NAME||' '||INDREC.PAY_TYPE||' '||INDREC.PAY_RATE||' '||INDREC.EFF_DATE);

         END LOOP;

      CLOSE PAY_CURSOR;

      EXCEPTION

         WHEN UNKNOWNPAYTYPE THEN

        

         DBMS_OUTPUT.PUT_LINE('ERROR: ABORTING PROGRAM.');

         DBMS_OUTPUT.PUT_LINE('UNKNOWN PAY TYPE FOR NAME');

         WHEN OTHERS THEN

         DBMS_OUTPUT.PUT_LINE('ERROR DURING PROCESSING. SEE THE DBA.');

       END;

   END;

/         

[2]对PAY_TABLE表写一个插入一条记录的过程，要求输入参数为一条记录。

set serveroutput on

     BEGIN

       DECLARE

         UnknownPayType exception;

         var_name VARCHAR2(20);

         var_pay_type VARCHAR2(8);

         var_pay_rate NUMBER(8,2);

         var_eff_date DATE;

         var_prev_pay NUMBER(8,2);

         status INTEGER;

       BEGIN

         dbms_output.get_line(var_name,status);

         dbms_output.get_line(var_pay_type,status);

         dbms_output.get_line(var_pay_rate,status);

         dbms_output.get_line(var_eff_date,status);

         dbms_output.get_line(var_prev_pay,status);

         insert into pay_table values(var_name,var_pay_type,var_pay_rate,var_eff_date,var_prev_pay);

         commit;

       EXCEPTION

         when UnknownPayType then

         dbms_output.put_line('=======================');

         dbms_output.put_line('ERROR: Aborting program.');

         dbms_output.put_line('Unknown Pay Type for Name');

         when others then

         dbms_output.put_line('ERROR During Processing. See the DBA.');

       END;

   END;

/

[3]写一个过程实现如下要求：对工作时间超过8个月的职员，如果PAY_TYPE是HOURLY,则改为SALARY,并将PAY_RATE改为按每天7小时工作的年薪。

SET SERVEROUTPUT ON

     BEGIN

       DECLARE

          UNKNOWNPAYTYPE EXCEPTION;

          CURSOR PAY_CURSOR IS

          SELECT NAME, PAY_TYPE, PAY_RATE, EFF_DATE, SYSDATE, ROWID FROM PAY_TABLE;

          INDREC PAY_CURSOR%ROWTYPE;

          COLDDATE DATE;

        

       BEGIN

         OPEN PAY_CURSOR;

         LOOP

           FETCH PAY_CURSOR INTO INDREC;

           EXIT WHEN PAY_CURSOR%NOTFOUND;

           COLDDATE := SYSDATE - 240;

           IF(INDREC.EFF_DATE < COLDDATE) THEN

             IF(INDREC.PAY_TYPE='HOURLY') THEN

               UPDATE PAY_TABLE SET PAY_TYPE = 'SALARY' WHERE ROWID=INDREC.ROWID;

               UPDATE PAY_TABLE SET PAY_RATE=INDREC.PAY_RATE * 7 * 365 WHERE ROWID=INDREC.ROWID;

             ELSE

               IF(INDREC.PAY_TYPE<>'SALARY') THEN

                 RAISE UNKNOWNPAYTYPE;

               END IF;

             END IF;             

             COMMIT;

           END IF;

         END LOOP;

        CLOSE PAY_CURSOR;

         EXCEPTION

           WHEN UNKNOWNPAYTYPE THEN

           DBMS_OUTPUT.PUT_LINE('=======================');

           DBMS_OUTPUT.PUT_LINE('ERROR: ABORTING PROGRAM!');

           DBMS_OUTPUT.PUT_LINE('EXCEPTION: NO SUCH TYPE!');

           WHEN OTHERS THEN

           DBMS_OUTPUT.PUT_LINE('ERROR DURING PROCESSING. SEE THE DBA.');

         END;

   END;

/