oracle高级3

游标的使用:
第一种类型:浏览
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;

第二种类型:修改数据
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．删除数据
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;

4采用游标FOR循环
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;

5参数游标
 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;

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;

1普通触发器
CREATE OR REPLACE TRIGGER change_teacher
      BEFORE INSERT OR UPDATE OR DELETE ON teachers
    BEGIN
      IF (TO_CHAR(SYSDATE, 'HH24') NOT BETWEEN '8' AND '17' )
      THEN
        RAISE_APPLICATION_ERROR(-20000, '在非工作时间不能改变教师信息。');
      END IF;
    END change_teacher;

2禁用触发器change_teacher。
ALTER TRIGGER change_teacher DISABLE;

3启用触发器change_teacher。
ALTER TRIGGER change_teacher ENABLE;

4删除触发器change_teacher。
DROP TRIGGER change_teacher;

．单一触发事件的DML触发器
CREATE TABLE students_grade_change(
  student_id NUMBER(5),
  course_id NUMBER(5),
  oldscore NUMBER(4,1),
  newscore NUMBER(4,1),
  time_change DATE
);

5建立触发器s_g_change
   CREATE OR REPLACE TRIGGER s_g_change
     AFTER UPDATE OF score ON students_grade
     FOR EACH ROW
   BEGIN
     INSERT INTO students_grade_change
       VALUES(:old.student_id,
         :old.course_id, :old.score, :new.score, SYSDATE);
   END s_g_change;

6多个触发事件的DML触发器
CREATE OR REPLACE TRIGGER change_teacher
      BEFORE INSERT OR UPDATE OR DELETE ON teachers
    BEGIN
      IF (TO_CHAR(SYSDATE, 'HH24') NOT BETWEEN '8' AND '17')
      THEN
        CASE
          WHEN INSERTING THEN
            RAISE_APPLICATION_ERROR
              (-20001, '在非工作时间不能增加教师信息。');
          WHEN UPDATING THEN
            RAISE_APPLICATION_ERROR
              (-20002, '在非工作时间不能修改教师信息。');
          WHEN DELETING THEN
            RAISE_APPLICATION_ERROR
              (-20003, '在非工作时间不能删除教师信息。');
        END CASE;        
      END IF;
    END change_teacher;