`
lilin745997
  • 浏览: 29237 次
  • 性别: Icon_minigender_1
  • 来自: 北京
最近访客 更多访客>>
社区版块
存档分类
最新评论

SQL语句 第10章 PLSQL编程基础

阅读更多

10.1  PL/SQL语言基础

10.1.1  PL/SQL块简介
例10.1_1 最简单的PL/SQL块A bare minimum anonymous block:
    SET SERVEROUTPUT ON
    BEGIN
       DBMS_OUTPUT.PUT_LINE('This a minimum anonymous block');
    END;
/
例10.1_2

    DECLARE
       v_sname VARCHAR2(10);
    BEGIN
       SELECT name INTO v_sname
         FROM Students WHERE student_id = 10318;
       DBMS_OUTPUT.PUT_LINE ('学生姓名:'||v_sname);
    END;
/
例10.1_3
    DECLARE
       v_sname VARCHAR2(10);
    BEGIN
       SELECT name INTO v_sname
         FROM Students WHERE student_id = &student_id;
       DBMS_OUTPUT.PUT_LINE ('学生姓名:'||v_sname);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
         DBMS_OUTPUT.PUT_LINE ('输入的学号不存在!');
    END;
/
10.2  在PL/SQL中执行SQL语句
10.2.1  执行SELECT语句
例10.2_1
   DECLARE
     v_id Departments.department_id%type;
     v_name Departments.department_name%type;
     v_address Departments.address%type;
   BEGIN
       SELECT * INTO v_id,v_name,v_address
         FROM Departments WHERE department_id = 101;
       DBMS_OUTPUT.PUT_LINE ('系部名称:'||v_name);
       DBMS_OUTPUT.PUT_LINE ('系部地址:'||v_address);
    END;
/

例10.2_2
   DECLARE
     v_student Students%ROWTYPE;
    BEGIN
       SELECT * INTO v_student
         FROM Students WHERE student_id = 10212;
       DBMS_OUTPUT.PUT_LINE ('姓名  性别  出生日期');
       DBMS_OUTPUT.PUT_LINE (v_student.name
         ||v_student.sex||v_student.dob);
    END;
/

例10.2_3
   DECLARE
      v_student students%ROWTYPE;
   BEGIN
      SELECT * INTO v_student FROM students WHERE name LIKE '王%';
      DBMS_OUTPUT.PUT_LINE ('姓名  性别  出生日期');
      DBMS_OUTPUT.PUT_LINE (v_student.name
        ||v_student.sex||v_student.dob);
   END;
/

例10.2_4
   DECLARE
      v_student students%ROWTYPE;
   BEGIN
      SELECT * INTO v_student
        FROM students WHERE dob = '31-12月-1989';
      DBMS_OUTPUT.PUT_LINE ('姓名  性别  出生日期');
      DBMS_OUTPUT.PUT_LINE (v_student.name
        ||v_student.sex||v_student.dob);
   END;
/

10.2.2  执行DML语句
1.执行INSERT语句
例10.2_5 使用常量
    BEGIN
       INSERT INTO students
         VALUES(10188,NULL,'王一', '女', '07-5月-1988','计算机');
    END;

例10.2_6 使用变量
 DECLARE
   v_id students.student_id%TYPE := 10199;
   v_monitorid students.monitor_id%TYPE := NULL;
   v_name students.name%TYPE:='张三';
   v_sex students.sex%TYPE:='女';
   v_dob students.dob%TYPE:='07-5月-1988';
   v_specialty students.specialty%TYPE:='计算机';
   BEGIN
     INSERT INTO students
       VALUES(v_id,v_monitorid,v_name,v_sex,v_dob,v_specialty);
   END;

例10.2_7 使用子查询
    BEGIN
       INSERT INTO students_computer
         (SELECT * FROM students WHERE specialty='计算机');
    END;

2.执行UPDATE语句
例10.2_8 使用常量   
    BEGIN
       UPDATE students
          SET student_id = 10288,
              dob = '07-5月-1988',
              specialty ='自动化'
       WHERE student_id = 10188;
    END;
例10.2_9 使用变量
DECLARE
   v_id students.student_id%TYPE := 10188;
   v_monitorid students.monitor_id%TYPE := NULL;
   v_dob students.dob%TYPE := '17-5月-1988';
   v_specialty students.specialty%TYPE := '计算机';
   BEGIN
     UPDATE students
          SET student_id = v_id,
              dob = v_dob,
              specialty = v_specialty
       WHERE student_id = 10288;
   END;

例10.2_10
BEGIN
  UPDATE teachers
    SET bonus =
        (SELECT AVG(bonus)
  FROM teachers)
  WHERE bonus IS NULL;
END;

3.执行DELETE语句
例10.2_11 使用常量
BEGIN
   DELETE FROM students
     WHERE student_id = 10188;
END;

例10.2_12 使用变量
DECLARE
   v_specialty students.specialty%TYPE := '计算机';
   BEGIN
     DELETE FROM students
       WHERE specialty = v_specialty;
   END;

例10.2_13 使用子查询
BEGIN
  DELETE FROM teachers
    WHERE wage >
      (SELECT 1.1*AVG(wage)
        FROM teachers);
END;

10.2.3  执行事物处理语句
例10.2_14
BEGIN
   INSERT INTO students
     VALUES(10101,NULL,'王晓芳', '女', '07-5月-1988','计算机');
   COMMIT;
   DELETE FROM students
     WHERE specialty = '计算机';
   ROLLBACK;
  UPDATE students
     SET student_id = 10288,
         dob = '07-5月-1988',
         specialty ='自动化'
   WHERE student_id = 10101;
   SAVEPOINT sp1;
   DELETE FROM students
     WHERE student_id = 10101;
   SAVEPOINT sp2;
   ROLLBACK TO sp1;
 COMMIT;
END;

10.3  PL/SQL程序控制结构
10.3.1  顺序结构
例10.3_1
   DECLARE
     v_student students%ROWTYPE;
    BEGIN
       SELECT * INTO v_student
         FROM students WHERE student_id = 10213;
       DBMS_OUTPUT.PUT_LINE ('姓名:'||v_student.name);
       DBMS_OUTPUT.PUT_LINE ('性别:'||v_student.sex);
       DBMS_OUTPUT.PUT_LINE ('出生日期:'||v_student.dob);
       DBMS_OUTPUT.PUT_LINE ('专业:'||v_student.specialty);
    END;
/
10.3.2  分支结构
1.IF语句
(1)IF—THEN—END IF;
例10.3_2
DECLARE
   v_id teachers.teacher_id%TYPE;
   v_title teachers.title%TYPE;
BEGIN
   v_id := &teacher_id;
   SELECT title INTO v_title
     FROM Teachers
     WHERE teacher_id = v_id;
   IF v_title = '讲师' THEN
      UPDATE Teachers
        SET wage = 1.1*wage
        WHERE teacher_id = v_id;
   END IF;
END;

(2)IF—THEN—ELSE—END IF;
例10.3_3
DECLARE
   v_id Teachers.teacher_id%TYPE;
   v_title Teachers.title%TYPE;
BEGIN
  v_id := &teacher_id;
  SELECT title INTO v_title
    FROM Teachers WHERE teacher_id = v_id;
  IF v_title = '教授' THEN
     UPDATE Teachers
       SET wage = 1.1*wage WHERE teacher_id = v_id;
  ELSE
     UPDATE Teachers
       SET wage = wage+100 WHERE teacher_id = v_id;
  END IF;
END;

(3)IF—THEN—ELSIF—THEN—ELSE—END IF;
例10.3_4
DECLARE
   v_id Teachers.teacher_id%TYPE;
   v_title Teachers.title%TYPE;
BEGIN
  v_id := &teacher_id;
  SELECT title INTO v_title
    FROM Teachers WHERE teacher_id = v_id;
  IF v_title = '教授' THEN
     UPDATE Teachers
       SET wage = 1.1*wage WHERE teacher_id=v_id;
  ELSIF v_title = '高工' OR v_title= '副教授' THEN
     UPDATE Teachers
       SET wage = 1.05*wage WHERE teacher_id = v_id;
  ELSE
     UPDATE Teachers
       SET wage = wage+100 WHERE teacher_id = v_id;
  END IF;
END;
2.CASE语句
(1)
例10.3_5
DECLARE
   v_id Teachers.teacher_id%TYPE;
   v_title Teachers.title%TYPE;
BEGIN
  v_id := &teacher_id;
  SELECT title INTO v_title
    FROM Teachers WHERE teacher_id = v_id;
  CASE v_title
    WHEN '教授' THEN
       UPDATE Teachers
         SET wage = 1.15*wage WHERE teacher_id = v_id;
    WHEN '高工' THEN
       UPDATE Teachers
         SET wage = 1.05*wage WHERE teacher_id = v_id;
    WHEN '副教授' THEN
       UPDATE Teachers
         SET wage = 1.1*wage WHERE teacher_id = v_id;
    ELSE
       UPDATE Teachers
         SET wage = wage+100 WHERE teacher_id = v_id;
  END CASE;
END;

(2)
例10.3_6
DECLARE
   v_id Teachers.teacher_id%TYPE;
   v_title Teachers.title%TYPE;
BEGIN
  v_id := &teacher_id;
  SELECT title INTO v_title
    FROM Teachers WHERE teacher_id = v_id;
  CASE
    WHEN v_title = '教授' THEN
       UPDATE Teachers
         SET wage = 1.1*wage WHERE teacher_id = v_id;
    WHEN v_title = '高工' OR v_title= '副教授' THEN
       UPDATE Teachers
         SET wage = 1.05*wage WHERE teacher_id = v_id;
    ELSE
       UPDATE Teachers
         SET wage = wage+100 WHERE teacher_id = v_id;
  END CASE;
END;

例10.3_7
DECLARE
   v_id teachers.teacher_id%TYPE;
   v_bonus teachers.bonus%TYPE;
   v_wage teachers.wage%TYPE;
   v_income NUMBER(7,2);
BEGIN
  v_id := &teacher_id;
  SELECT bonus, wage INTO v_bonus, v_wage
     FROM teachers WHERE teacher_id = v_id;
  v_income := v_bonus + v_wage;
  CASE
    WHEN v_income <= 1000 THEN
       DBMS_OUTPUT.PUT_LINE ('个人所得税:0');
    WHEN v_income >1000 AND v_income < 3000 THEN
       DBMS_OUTPUT.PUT_LINE ('个人所得税:'||v_income*0.03);
    WHEN v_income >= 3000 THEN
       DBMS_OUTPUT.PUT_LINE ('个人所得税:'||v_income*0.05);
  END CASE;
END;
10.3.3  循环结构
CREATE TABLE total(n INT,result INT);
1.LOOP循环
例10.3_8
DECLARE
   v_i INT:=1;
   v_sum INT:=0;
BEGIN
   LOOP 
     v_sum := v_sum + v_i;
     INSERT INTO TOTAL VALUES(v_i, v_sum);
     EXIT WHEN v_i = 10;
     v_i := v_i+1;
   END LOOP;
END;

2.WHILE循环
例10.3_9
DECLARE
   v_i INT:=1;
   v_sum INT:=0;
BEGIN
  WHILE v_i <= 10  LOOP
     v_sum := v_sum + v_i*v_i;
     INSERT INTO TOTAL VALUES(v_i,v_sum);
     v_i := v_i+1;
  END LOOP;
END;

3.FOR循环
例10.3_10
DECLARE
   v_i INT:=1;
   v_factorial INT:=1;
BEGIN
  FOR v_i IN 1..10 LOOP
     v_factorial := v_factorial*v_i;
     INSERT INTO TOTAL VALUES(v_i,v_factorial);
  END LOOP;
END;

10.3.4  顺序控制
1. GOTO
例10.3_11
SET SERVEROUTPUT ON
DECLARE
   v_i INT:=1;
   v_sum INT:=0;
BEGIN
   LOOP 
     v_sum := v_sum + v_i;
     INSERT INTO TOTAL VALUES(v_i,v_sum);
     IF v_i = 10 THEN
        GOTO output;
     END IF;   
     v_i := v_i+1;
   END LOOP;
<<output>>
DBMS_OUTPUT.PUT_LINE ('v_sum = '||v_sum);
END;

2. NULL
例10.3_12
DECLARE
   v_i INT:=1;
   v_sum INT:=0;
BEGIN
   LOOP 
     v_sum := v_sum + v_i;
     INSERT INTO TOTAL VALUES(v_i,v_sum);
     IF v_i = 10 THEN
        GOTO output;
     END IF;   
     v_i := v_i+1;
   END LOOP;
<<output>>
   NULL;
END;

10.4  异常处理

10.4.1  异常的基本概念

10.4.2  系统异常处理

1.预定义异常
例10.4_1
SET SERVEROUTPUT ON
DECLARE
   v_dividend NUMBER:=50;
   v_divisor NUMBER:=0;
   v_quotient NUMBER;
BEGIN
v_quotient := v_dividend/v_divisor;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE ('除数为零!');
END;

例10.4_2 NO_DATA_FOUND
    SET SERVEROUTPUT ON
    DECLARE
       v_id Students.student_id%TYPE;      
       v_sname Students.name%TYPE;
    BEGIN
       v_id := &student_id;
       SELECT name INTO v_sname FROM Students WHERE student_id=v_id;
       DBMS_OUTPUT.PUT_LINE ('学生姓名:'||v_sname);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
         DBMS_OUTPUT.PUT_LINE ('输入的学号不存在!');
    END;

例10.4_3 TOO_MANY_ROWS
SET SERVEROUTPUT ON   
DECLARE
       v_specialty Students.specialty%TYPE;      
       v_sname Students.name%TYPE;
    BEGIN
       v_specialty := '&specialty';
       SELECT name INTO v_sname
         FROM Students WHERE specialty=v_specialty;
       DBMS_OUTPUT.PUT_LINE ('学生姓名:'||v_sname);
    EXCEPTION
      WHEN TOO_MANY_ROWS THEN
         DBMS_OUTPUT.PUT_LINE ('返回的学生记录多于一行!');
    END;

例10.4_4 NO_DATA_FOUND TOO_MANY_ROWS
SET SERVEROUTPUT ON   
DECLARE
       v_specialty Students.specialty%TYPE;      
       v_sname Students.name%TYPE;
    BEGIN
       v_specialty := '&specialty';
       SELECT name INTO v_sname
         FROM Students WHERE specialty=v_specialty;
       DBMS_OUTPUT.PUT_LINE ('学生姓名:'||v_sname);
    EXCEPTION
      WHEN TOO_MANY_ROWS THEN
         DBMS_OUTPUT.PUT_LINE ('返回的学生记录多于一行!');
      WHEN NO_DATA_FOUND THEN
         DBMS_OUTPUT.PUT_LINE ('输入的专业不存在!');
    END;

2.非预定义异常
例10.4_5
SET SERVEROUTPUT ON   
DECLARE
       e_deptid  EXCEPTION;
       PRAGMA  EXCEPTION_INIT(e_deptid, -2292);
    BEGIN
       DELETE FROM Departments
         WHERE department_id = 101;
    EXCEPTION
      WHEN e_deptid THEN
        DBMS_OUTPUT.PUT_LINE ('在教师表中存在子记录!');
    END;

例10.4_6
SET SERVEROUTPUT ON   
DECLARE
       e_deptid  EXCEPTION;
       PRAGMA  EXCEPTION_INIT(e_deptid, -2291);
    BEGIN
      INSERT INTO Teachers
        VALUES(11101,'王彤', '教授', '01-9月-1990',1000,3000,999);
    EXCEPTION
      WHEN e_deptid THEN
        DBMS_OUTPUT.PUT_LINE ('插入记录的部门号在父表中不存在!');
    END;

例10.4_7
SET SERVEROUTPUT ON   
DECLARE
       e_studentid EXCEPTION;
       PRAGMA EXCEPTION_INIT(e_studentid, -0001);
    BEGIN
       INSERT INTO Students
         VALUES(10205,NULL,'王三', '男', '26-12月-1989','自动化');
    EXCEPTION
       WHEN e_studentid THEN
          DBMS_OUTPUT.PUT_LINE ('插入学生记录的学号在表中已存在!');
    END;

10.4.3  用户自定义异常处理
例10.4_8
SET SERVEROUTPUT ON   
DECLARE
       e_wage EXCEPTION;
       v_wage Teachers.wage%TYPE;
    BEGIN
       v_wage := &wage;
       INSERT INTO Teachers
         VALUES(10111,'王彤', '教授', '01-9月-1990',1000,v_wage,101);
       IF v_wage < 0 THEN
          RAISE e_wage;
       END IF;
    EXCEPTION
       WHEN e_wage THEN
          DBMS_OUTPUT.PUT_LINE ('教师工资不能为负值!');
          ROLLBACK;
    END;

例10.4_9 带WHEN OTHERS THEN可选项
SET SERVEROUTPUT ON
DECLARE
       e_wage EXCEPTION;
       v_wage Teachers.wage%TYPE;
       v_deptid Teachers.department_id%TYPE;
       v_bonus Teachers.bonus%TYPE;
    BEGIN
       v_wage := &wage;
       v_deptid := &department_id;
       INSERT INTO Teachers
         VALUES(10111,'王彤', '教授', '01-9月-1990',1000,v_wage,101);
       SELECT bonus INTO v_bonus
         FROM Teachers WHERE department_id = v_deptid;
       IF v_wage < 0 THEN
          RAISE e_wage;
       END IF;
    EXCEPTION
       WHEN e_wage THEN
          DBMS_OUTPUT.PUT_LINE ('教师工资不能为负值!');
          ROLLBACK;
       WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE ('查询教师奖金时出错!');
    END;

10.4.4  使用异常函数
例10.4_10 使用SQLCODE和SQLERRM异常函数
    SET SERVEROUTPUT ON   
    DECLARE
       e_wage EXCEPTION;
       v_wage Teachers.wage%TYPE;
       v_deptid Teachers.department_id%TYPE;
       v_bonus Teachers.bonus%TYPE;
    BEGIN
       v_wage := &wage;
       v_deptid := &department_id;
       INSERT INTO Teachers
         VALUES(10111,'王彤', '教授', '01-9月-1990',1000,v_wage,101);
       SELECT bonus INTO v_bonus
         FROM Teachers WHERE department_id = v_deptid;
       IF v_wage < 0 THEN
          RAISE e_wage;
       END IF;
    EXCEPTION
       WHEN e_wage THEN
          DBMS_OUTPUT.PUT_LINE ('教师工资不能为负值!');
          ROLLBACK;
       WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE ('错误代码:'||SQLCODE);
          DBMS_OUTPUT.PUT_LINE ('错误描述:'||SQLERRM);
    END;

10.5  游标
10.5.2  游标应用
1.浏览数据
例10.5_1
     SET SERVEROUTPUT ON  
     DECLARE
       v_specialty Students.specialty%TYPE;      
       v_sname Students.name%TYPE;
       v_dob Students.dob%TYPE;
       CURSOR Students_cur
       IS
          SELECT name,dob
            FROM Students
           WHERE specialty = v_specialty;
     BEGIN
        v_specialty := '&specialty';
        OPEN Students_cur;
        DBMS_OUTPUT.PUT_LINE ('学生姓名   出生日期');
        LOOP
           FETCH Students_cur INTO v_sname,v_dob;
           EXIT WHEN Students_cur%NOTFOUND;
           DBMS_OUTPUT.PUT_LINE (v_sname||'     '||v_dob);
        END LOOP;
        CLOSE Students_cur;
    END;

2.修改数据
例10.5_2
     DECLARE
       v_title Teachers.title%TYPE;
       CURSOR Teachers_cur
       IS
          SELECT title
            FROM Teachers
            FOR UPDATE;
     BEGIN
        OPEN Teachers_cur;
        LOOP
           FETCH Teachers_cur INTO v_title;
           EXIT WHEN Teachers_cur%NOTFOUND;
           CASE
             WHEN v_title = '教授' THEN
                UPDATE Teachers
                  SET wage = 1.1*wage WHERE CURRENT OF Teachers_cur;
             WHEN v_title = '高工' OR v_title= '副教授' THEN
                UPDATE Teachers
                  SET wage = 1.05*wage WHERE CURRENT OF Teachers_cur;
             ELSE
                UPDATE Teachers
                  SET wage = wage+100 WHERE CURRENT OF Teachers_cur;
           END CASE;
        END LOOP;
        CLOSE Teachers_cur;
    END;

3.删除数据
例10.5_3
     DECLARE
       v_specialty Students.specialty%TYPE;      
       v_sname Students.name%TYPE;
       CURSOR Students_cur
       IS
          SELECT name,specialty
            FROM Students
            FOR UPDATE;
     BEGIN
        OPEN Students_cur;
        FETCH Students_cur INTO v_sname,v_specialty;
        WHILE Students_cur%FOUND LOOP
           IF v_specialty = '计算机' THEN
              DELETE FROM Students WHERE CURRENT OF Students_cur;
           END IF;
           FETCH Students_cur INTO v_sname,v_specialty;
        END LOOP;
        CLOSE Students_cur;
     END;

10.5.3  游标FOR循环
1.语句格式一
例10.5_4
SET SERVEROUTPUT ON  
     DECLARE
     v_specialty Students.specialty%TYPE;
       CURSOR Students_cur
       IS
          SELECT name,dob
            FROM Students
           WHERE specialty = v_specialty;
     BEGIN
        v_specialty := '&specialty';
        DBMS_OUTPUT.PUT_LINE ('序号  学生姓名   出生日期');
        FOR Students_record IN Students_cur LOOP
           DBMS_OUTPUT.PUT_LINE (Students_cur%ROWCOUNT||'     '||Students_record.name||'     '||Students_record.dob);
        END LOOP;
     END;

2.语句格式二
例10.5_5
SET SERVEROUTPUT ON  
     DECLARE
     v_specialty Students.specialty%TYPE;
       CURSOR Students_cur
       IS
          SELECT name,dob
            FROM Students
           WHERE specialty = v_specialty;
     BEGIN
        v_specialty := '&specialty';
        DBMS_OUTPUT.PUT_LINE ('学生姓名   出生日期');
        FOR Students_record IN
           (SELECT name,dob FROM Students WHERE specialty = v_specialty) LOOP
           DBMS_OUTPUT.PUT_LINE (Students_record.name||'     '||Students_record.dob);
        END LOOP;
     END;
10.5.4  游标的复杂应用
1.参数游标
例10.5_6
     SET SERVEROUTPUT ON
     DECLARE
       v_dob Students.dob%TYPE;      
       v_sname Students.name%TYPE;
       CURSOR Students_cur(v_specialty Students.specialty%TYPE)
       IS
          SELECT name, dob
            FROM Students WHERE specialty = v_specialty;
     BEGIN
        OPEN Students_cur('机电工程');
        FETCH Students_cur INTO v_sname,v_dob;
        WHILE Students_cur%FOUND LOOP
           DBMS_OUTPUT.PUT_LINE (v_sname||'     '||v_dob);
           FETCH Students_cur INTO v_sname,v_dob;
        END LOOP;
        CLOSE Students_cur;
     END;


例10.5_7
     SET SERVEROUTPUT ON
     DECLARE
       v_tname Teachers.name%TYPE;
       v_wage Teachers.wage%TYPE;      
       CURSOR Teachers_cur(t_title VARCHAR2,t_wage NUMBER)
       IS
          SELECT name, wage
            FROM Teachers WHERE title = t_title AND wage > t_wage;
     BEGIN
        OPEN Teachers_cur('副教授',2000);
        FETCH Teachers_cur INTO v_tname,v_wage;
        WHILE Teachers_cur%FOUND LOOP
           DBMS_OUTPUT.PUT_LINE (v_tname||'     '||v_wage);
           FETCH Teachers_cur INTO v_tname,v_wage;
        END LOOP;
        CLOSE Teachers_cur;
     END;

2.游标变量
例10.5_8 游标变量无返回值。

     SET SERVEROUTPUT ON
     DECLARE
        TYPE Students_cur IS REF CURSOR;
        StuCursor Students_cur;
        Students_record Students%ROWTYPE;
     BEGIN
     IF NOT StuCursor%ISOPEN THEN
        OPEN StuCursor FOR SELECT * FROM Students;
     END IF;
     DBMS_OUTPUT.PUT_LINE ('学生姓名   出生日期');
     LOOP
       FETCH StuCursor INTO Students_record;
       EXIT WHEN StuCursor%NOTFOUND;
       DBMS_OUTPUT.PUT_LINE (Students_record.name||'     '||Students_record.dob);
     END LOOP;
     CLOSE StuCursor;
     END;

例10.5_9 游标变量具有返回值。
     SET SERVEROUTPUT ON
     DECLARE
        TYPE Students_record IS RECORD(
          StuName VARCHAR2(10),
          StuDOB DATE
        );
        StuRecord Students_record;
        TYPE Students_cur IS REF CURSOR RETURN Students_record;
        StuCursor Students_cur;
     BEGIN
        IF NOT StuCursor%ISOPEN THEN
           OPEN StuCursor FOR SELECT name,dob FROM Students;
        END IF;
        DBMS_OUTPUT.PUT_LINE ('学生姓名   出生日期');
        LOOP
          FETCH StuCursor INTO StuRecord;
          EXIT WHEN StuCursor%NOTFOUND;
          DBMS_OUTPUT.PUT_LINE (StuRecord.StuName||'     '||StuRecord.StuDOB);
        END LOOP;
     CLOSE StuCursor;
     END;

3.游标表达式
10.5_10 定义游标Departments_cur。在游标Departments_cur中使用了游标表达式CURSOR(SELECT name, title FROM Teachers WHERE department_id = d.department_id)。
     SET SERVEROUTPUT ON
     DECLARE
        v_tname Teachers.name%TYPE;
        v_title Teachers.title%TYPE;
        v_dname Departments.department_name%TYPE;        
        TYPE cursor_type IS REF CURSOR;
        CURSOR Departments_cur(dept_id NUMBER) IS
        SELECT d.department_name, CURSOR(SELECT name, title
        FROM Teachers WHERE department_id = d.department_id)
        FROM Departments d WHERE d.department_id = dept_id;
        Teachers_cur cursor_type;
     BEGIN
        OPEN Departments_cur('101');
        LOOP
          FETCH Departments_cur INTO v_dname, Teachers_cur;
          EXIT WHEN Departments_cur%NOTFOUND;
          DBMS_OUTPUT.PUT_LINE ('系部名称:'||v_dname);  
          DBMS_OUTPUT.PUT_LINE ('教师姓名   职称');
          LOOP
            FETCH Teachers_cur INTO v_tname, v_title;
            EXIT WHEN Teachers_cur%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE (v_tname||'     '||v_title);  
          END LOOP;
        END LOOP;
     END;

 

分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics