Liny_@NotePad

沉迷ACG中

PL/SQL课堂练习(匿名块)

YOYO posted @ 2009年3月15日 22:48 in 【数据库】 with tags PL/SQL 匿名块 oracle 练习 , 4166 阅读

1、普通变量声明
 1）、声明3个变量:full_name varchar2(20),salary number(9,2),fdate date
 2）、用Select into语句从employees表为上面3个变量赋值，条件是rownum <= &_rownum
 3）、用DBMS_OUTPUT.PUT_LINE()打印三个变量值，结果如：Mickel.Jordon 's salary is $4680, Joined in 1987-07-12
 4）、另外声明常量PI，NUMBER(2,7)=3.1415926，声明变量flag BOOLEAN，默认值=FALSE，声明变量age NUMBER，无默认值
 5）、打印上述变量

  2.  
  3.    full_name VARCHAR2(20);
  4.    salary NUMBER(9,2);
  5.    fdate DATE;
  6.    pi CONSTANT NUMBER(8,7) := 3.1415926;
  7.    flag BOOLEAN := FALSE;
  8.    age NUMBER;
  9.  
  11.  
  12.    SELECT first_name||'.'||last_name, salary, hire_date
  13.    INTO full_name, salary, fdate
  14.    FROM employees
  15.    WHERE ROWNUM <= &_rownum;
  16.  
  17.    DBMS_OUTPUT.PUT_LINE(full_name||' ''s salary IS $'||salary||', Joined in '||to_char(fdate,'yyyy-MM-dd'));
  18.  
  19.    DBMS_OUTPUT.PUT_LINE('pi:'||pi);
  20.  
  21.    IF flag THEN
  22.       DBMS_OUTPUT.PUT_LINE('true');
  23.    ELSE
  24.       DBMS_OUTPUT.PUT_LINE('false');
  25.    END IF;
  26.  
  27.    DBMS_OUTPUT.PUT_LINE('age:'||age);
  28.  


2、游标使用
 2.1  静态游标定义
 
 1）、声明3个变量full_name varchar2(20),salary number(9,2),fdate date
 2）、声明一个静态游标，取出3个对应上面变量的值
 3）、打开游标
 4）、循环取值打印，格式如上题（可用for循环 和 loop exit循环分别实现）
 5）、关闭游标

  1. ----用 LOOP EXIT循环实现
  3.  
  4.    TYPE RECORDTYPE IS RECORD(
  5.       full_name VARCHAR2(20),
  6.       salary NUMBER(9,2),
  7.       fdate DATE
  8.    );
  9.    tmp RECORDTYPE;
  10.    CURSOR my_cur IS
  11.       SELECT first_name||''''||last_name, salary, hire_date
  12.       FROM employees;
  13.  
  15.  
  16.    IF NOT my_cur%ISOPEN THEN
  17.       OPEN my_cur;
  18.    END IF;
  19.  
  20.    LOOP
  21.         FETCH my_cur INTO tmp;
  22.         DBMS_OUTPUT.PUT_LINE(tmp.full_name||' '||tmp.salary||' '||tmp.fdate);
  23.         EXIT WHEN my_cur%notfound;
  24.    END LOOP;
  25.  
  26.    CLOSE my_cur;
  27.  
  1. ----用FOR循环实现
  3.  
  4.    CURSOR my_cur IS
  5.       SELECT first_name||''''||last_name full_name, salary, hire_date fdate
  6.       FROM employees;
  7.  
  9.  
  10.    FOR emp IN my_cur LOOP
  11.         DBMS_OUTPUT.PUT_LINE(emp.full_name||', '||emp.salary||', '||emp.fdate);
  12.    END LOOP;
  13.  

 2.2  动态游标定义

 1）、声明3个变量full_name varchar2(20),salary number(9,2),fdate date
 2）、声明一个动态游标变量
 3）、动态游标赋值并打开，指向select first_name ||'.'||last_name,salary,fire_date from employees
 4）、循环取值，打印，格式如上题
 5）、关闭游标

  2.  
  3.    TYPE ref_cur_type IS REF CURSOR;
  4.    
  5.    mycur ref_cur_type;
  6.    
  7.    TYPE employee IS RECORD(
  8.         full_name VARCHAR2(20),
  9.         salary NUMBER,
  10.         fdate DATE);
  11.        
  12.    emp employee;
  13.    
  15.  
  16.    OPEN mycur FOR SELECT first_name ||'.'||last_name,salary,hire_date FROM employees;
  17.    
  18.    LOOP
  19.         FETCH mycur INTO emp;
  20.         DBMS_OUTPUT.PUT_LINE(emp.full_name||','||emp.salary||','||emp.fdate);
  21.    
  22.         EXIT WHEN mycur%notfound;
  23.    END LOOP;
  24.    
  25.    CLOSE mycur;
  26.  

3、控制结构
 3.1 IF..ELSIF ..ELSE..END IF
 编程统计Employees表的各个薪水等级的人数，输出：
       <3000:  ***人
 [3000,6000]:  ***人
 [6000,9000]:  ***人
 [9000,12000]: ***人
 >12000      : ***人

  2.   count1 NUMBER := 0 ;
  3.   count2 NUMBER := 0 ;
  4.   count3 NUMBER := 0 ;
  5.   count4 NUMBER := 0 ;
  6.   count5 NUMBER := 0 ;
  7.   salary NUMBER ;
  8.  
  9.   TYPE cur_type IS REF CURSOR;
  10.   mycur cur_type;
  11.  
  13.  
  14.   OPEN mycur FOR SELECT salary FROM employees;
  15.  
  16.   LOOP
  17.        FETCH mycur INTO salary;
  18.        
  19.        IF salary < 3000 THEN
  20.           count1 := count1 + 1;
  21.        ELSIF salary < 6000 THEN
  22.              count2 := count2 + 1;
  23.        ELSIF salary < 9000 THEN
  24.              count3 := count3 + 1;
  25.        ELSIF salary < 12000 THEN
  26.              count4 := count4 + 1;
  27.        ELSE
  28.              count5 := count5 + 1;
  29.        END IF;
  30.  
  31.        EXIT WHEN mycur%notfound;
  32.   END LOOP;
  33.  
  34.   CLOSE mycur;
  35.  
  36.   DBMS_OUTPUT.PUT_LINE('      <3000:  '|| LPAD(count1,3,' ') || '人');
  37.   DBMS_OUTPUT.PUT_LINE('[3000,6000]:  '|| LPAD(count2,3,' ') || '人');
  38.   DBMS_OUTPUT.PUT_LINE('[6000,9000]:  '|| LPAD(count3,3,' ') || '人');
  39.   DBMS_OUTPUT.PUT_LINE('[9000,12000]: '|| LPAD(count4,3,' ') || '人');
  40.   DBMS_OUTPUT.PUT_LINE('>12000      : '|| LPAD(count5,3,' ') || '人');
  41.  

 3.2 CASE..WHEN..END CASE
 为全球各个区域的每个员工调整薪水，考虑各区域的通货膨胀率不同，拟：
 1 Europe   +10%
 2 Americas  +13%
 3 Asia   +18%
 4 Middle East and Africa +5%

 请逐行按以下格式打印：
  序号  员工全名 所在区域 原薪水 调整后薪水
  1     M*.J*       Europe 1000 1100
  2     .......

  2.   no NUMBER := 0 ;
  3.  
  4.   TYPE employee IS RECORD(
  5.        full_name VARCHAR2(20),
  6.        region regions.region_name%TYPE,
  7.        salary NUMBER
  8.   );
  9.   emp employee;
  10.  
  11.   salary_new NUMBER;
  12.  
  13.   TYPE cur_type ISIS REF CURSOR;
  14.   mycur cur_type;
  15.  
  17.  
  18.   OPEN mycur FOR
  19.                 SELECT SUBSTR(first_name,1,1)||'*.'||substr(last_name,1,1)||'*', region_name, salary
  20.                 FROM employees emp, departments dept, locations loc, countries cty, regions re
  21.                 WHERE emp.department_id = dept.department_id AND dept.location_id = loc.location_id
  22.                       AND loc.country_id = cty.country_id AND cty.region_id = re.region_id;
  23.  
  24.   DBMS_OUTPUT.PUT_LINE('序号  员工全名     所在区域     原薪水 调整后薪水');
  25.  
  26.   LOOP
  27.        FETCH mycur INTO emp;
  28.        
  29.        no := no + 1;
  30.        
  31.       CASE emp.region
  32.      
  33.       WHEN 'Europe' THEN
  34.            salary_new := emp.salary * 1.1;
  35.       WHEN 'Americas' THEN
  36.            salary_new := emp.salary * 1.13;     
  37.       WHEN 'Asia' THEN
  38.            salary_new := emp.salary * 1.18;
  39.       WHEN 'Middle East and Africa' THEN
  40.            salary_new := emp.salary * 1.05;
  41.        
  42.       END CASE;
  43.      
  44.       DBMS_OUTPUT.PUT_LINE(RPAD( no, 6, ' ')||rpad(emp.full_name, 11, ' ')||rpad(emp.region,14,' ')||rpad(emp.salary,6,' ')||salary_new);
  45.  
  46.        EXIT WHEN mycur%notfound;
  47.   END LOOP;
  48.  
  49.   CLOSE mycur;
  50.  

4、异常处理
 4.1 内置异常处理
 1）、 由外部输入值作为 select * from departments where department_id = &_depid的条件
 2）、 捕获NO_DATA_FOUND异常，并在控制台输出SQLCODE 和 SQLERRM，
  并打印出“您指定的ID为？的部门不存在！”，其中问号为你在控制台上输入的数值
 3）、若输入的部门号存在，应打印出部门的具体信息

  2.  
  3.   department departments%ROWTYPE;
  4.  
  6.  
  7.   SELECT *
  8.   INTO department
  9.   FROM departments
  10.   WHERE department_id = &_depid;
  11.  
  12.   DBMS_OUTPUT.PUT_LINE( department.department_name );
  13.  
  15.  
  17.        DBMS_OUTPUT.PUT_LINE(SQLCODE||': '||SQLERRM);
  18.  

 4.2 自定义异常处理
 1）、自定义一个异常MY_EXCEPTION                   【可考虑用pragma exception_init对异常进行正式包装(EMP_NO_DEPT,-20080801)】
 2）、遍历employees 表，打印出“ 员工ID，姓名，部门名字”
 3）、如果发现一个员工没有从属部门，raise上面的自定义的异常
 4）、在Exception段处理自定义的MY_EXCEPTION，输出“****属于无组织无纪律员工！”
 5）、在Exception段处理OTHERS，输出SQLCODE 和 SQLERRM

  2.  
  3.   emp_no_dept EXCEPTION;
  4.  
  5.   PRAGMA EXCEPTION_INIT( emp_no_dept, -1989 );
  6.  
  7.   TYPE employee IS RECORD(
  8.        eno employees.employee_id%TYPE,
  9.        ename VARCHAR2(20),
  10.        deptname departments.department_name%TYPE
  11.      );
  12.   emp employee;
  13.  
  14.   CURSOR mycur IS
  15.          SELECT employee_id, first_name||'.'||last_name, department_name
  16.          FROM employees, departments
  17.          WHERE employees.department_id = departments.department_id;
  18.  
  20.  
  21.   DBMS_OUTPUT.PUT_LINE(' 员工ID   '||'       姓名    '||'      部门名字       ');
  22.  
  23.   IF NOT mycur%ISOPEN THEN
  24.      OPEN mycur;
  25.   END IF;
  26.  
  27.   LOOP
  28.  
  29.          FETCH mycur INTO emp;
  30.          
  31.          IF emp.deptname IS NULL THEN
  32.             RAISE emp_no_dept;
  33.          END IF;
  34.          
  35.          DBMS_OUTPUT.PUT_LINE( '  '|| RPAD(emp.eno,5) || ' '|| RPAD(emp.ename,20) ||' '|| emp.deptname );
  36.  
  37.          EXIT WHEN mycur%NOTFOUND;
  38.  
  39.   END LOOP;
  40.  
  41.   CLOSE mycur;
  42.  
  44.  
  45.   WHEN emp_no_dept THEN
  46.        DBMS_OUTPUT.PUT_LINE( emp.ename || '属于无组织无纪律员工！！' );
  47.  
  49.        DBMS_OUTPUT.PUT_LINE( SQLCODE || ': ' || SQLERRM );
  50.        

5、复合变量类型使用
 5.1 Record类型
  1）、定义一个Record类型（id,fullname,jobtitle,salary），并用其声明一个变量
  2）、定义游标，SQL对应上述4个值
  3）、循环取值、打印Record变量的值

  2.  
  3.    TYPE employee IS RECORD(
  4.         id employees.employee_id%TYPE,
  5.         fullname VARCHAR2(20),
  6.         jobtitle jobs.job_title%TYPE,
  7.         salary employees.salary%TYPE
  8.    );
  9.    emp employee;
  10.    
  11.    CURSOR mycur IS
  12.           SELECT employee_id, first_name||'.'||last_name, job_title, salary
  13.           FROM employees, jobs
  14.           WHERE employees.job_id = jobs.job_id;
  15.          
  17.  
  18.    IF NOT mycur%ISOPEN THEN
  19.       OPEN mycur;
  20.    END IF;
  21.    
  22.    LOOP
  23.    
  24.        FETCH mycur INTO emp;
  25.        
  26.        DBMS_OUTPUT.PUT_LINE( emp.id ||' ['||emp.jobtitle||'] '||emp.fullname||': '||emp.salary);
  27.    
  28.        EXIT WHEN mycur%NOTFOUND;
  29.    
  30.    END LOOP;
  31.    
  32.    CLOSE mycur;
  33.  

 5.2 TABLE类型
  1）、定义一个BINARY_INTEGER TABLE类型(ID,VARCHAR2(20))，并用其声明一个变量
  2）、定义游标，Select first_name|| ''.'' ||last_name name from employees
  3）、循环取值，并为Table增加元素
  4）、输出Table的几个属性：first,last,Count、Limit
  5）、遍历Table，并输出Table的所有元素
  6）、删除Table的所有元素
  7）、再次输出Table的count

  2.  
  3.    TYPE employee IS TABLE OF VARCHAR2(20)
  5.    
  6.    emp employee;
  7.          
  9.  
  10.    SELECT first_name||'.'||last_name name
  11.    BULK COLLECT INTO emp
  12.    FROM employees;
  13.    
  14.    DBMS_OUTPUT.PUT_LINE( 'FIRST: ' || emp.FIRST );
  15.    DBMS_OUTPUT.PUT_LINE( 'LAST: ' || emp.LAST );
  16.    DBMS_OUTPUT.PUT_LINE( 'COUNT: ' || emp.COUNT );
  17.    DBMS_OUTPUT.PUT_LINE( 'LIMIT: ' || emp.LIMIT );
  18.    
  19.    FOR i IN 1 .. emp.COUNT LOOP
  20.        
  21.        DBMS_OUTPUT.PUT_LINE( i || ' ' || emp(i) );
  22.        
  23.    END LOOP;
  24.    
  25.    emp.DELETE();
  26.    
  27.    DBMS_OUTPUT.PUT_LINE( 'COUNT: ' || emp.COUNT );
  28.  

5.3 使用%ROWTYPE
  1）、用%ROWTYPE类型声明一个变量
  2）、用select * into 为该变量赋值（注意返回值应是单条记录）
  3）、输出这个变量的若干列的值

  2.  
  3.    emp employees%ROWTYPE;
  4.    
  5.    CURSOR mycur IS
  6.           SELECT *
  7.           FROM employees;
  8.          
  10.  
  11.    IF NOT mycur%ISOPEN THEN
  12.       OPEN mycur;
  13.    END IF;
  14.    
  15.    LOOP
  16.    
  17.        FETCH mycur INTO emp;
  18.        
  19.        DBMS_OUTPUT.PUT_LINE( emp.employee_id ||' '||emp.first_name||'.'||emp.last_name||': '||emp.salary);
  20.    
  21.        EXIT WHEN mycur%NOTFOUND;
  22.    
  23.    END LOOP;
  24.    
  25.    CLOSE mycur;
  26.  
Small_feed 评论 (0)

登录 *


loading captcha image...
(输入验证码)
or Ctrl+Enter