Oracle PL/SQL笔记

declare
  v_first_name varchar2(35);
  v_last_name varchar2(35);
  c_student_id constant number := 2223;
begin
  select first_name, last_name
  into v_first_name, v_last_name
  from student
  where student_id = c_student_id;
  
  dbms_output.put_line('Student name: ' || v_first_name || ' ' || v_last_name);
exception 
  when no_data_found then
       dbms_output.put_line('There is no student with student id '||c_student_id);
end;

BEGIN
   INSERT INTO ZIPCODE (zip, city, state, created_by, created_date,   
      modified_by, modified_date)
   VALUES ('10027', 'NEW YORK', 'NY', USER, SYSDATE, USER, SYSDATE);
   COMMIT;
EXCEPTION
   WHEN OTHERS THEN
      DECLARE
         v_err_code NUMBER := SQLCODE;
         v_err_msg VARCHAR2(100) := SUBSTR(SQLERRM, 1, 100);
      BEGIN
         DBMS_OUTPUT.PUT_LINE ('Error code: '||v_err_code);
         DBMS_OUTPUT.PUT_LINE ('Error message: '||v_err_msg);
      END;
END;

declare 
  --rowtype:行记录类型
  vr_student student%rowtype;
begin
  select *
  into vr_student
  from student
  where student_id = 156;
  
  --用“.”来访问行记录属性
  dbms_output.put_line(vr_student.first_name || ' ' || vr_student.street_address || ' has an ID of 156');
exception
  when no_data_found then
       raise_application_error(-20001,'the student is not in the database');
end;

declare 
       cursor c_zip is  -- 声明游标
       select * 
       from zipcode;
       
       vr_zip c_zip%rowtype; --游标行类型
begin
       open c_zip;  --打开游标
       loop
            fetch c_zip into vr_zip; --对游标集合内的每个数据行操作
            exit when c_zip%notfound;
            dbms_output.put_line(vr_zip.city);
       end loop; -- loop结束，自动关闭游标
end;


DECLARE
   v_sid      student.student_id%TYPE;
   CURSOR c_student IS
      SELECT student_id
        FROM student
       WHERE student_id < 110;
BEGIN
   OPEN c_student;
   LOOP
      FETCH c_student INTO v_sid;
      EXIT WHEN c_student%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('STUDENT ID : '||v_sid);
   END LOOP;
   CLOSE c_student;
 EXCEPTION
   WHEN OTHERS
   THEN
      IF c_student%ISOPEN
      THEN
         CLOSE c_student;
      END IF;
END;


DECLARE
   CURSOR c_student_enroll IS
      SELECT s.student_id, first_name, last_name,
             COUNT(*) enroll,
             (CASE  
                  WHEN count(*) = 1 Then ' class.'
                  WHEN count(*) is null then 
                               ' no classes.'
                  ELSE ' classes.'
              END) class                     
        FROM student s, enrollment e
       WHERE s.student_id = e.student_id
         AND s.student_id <110
       GROUP BY s.student_id, first_name, last_name;
   r_student_enroll    c_student_enroll%ROWTYPE;
BEGIN
   OPEN c_student_enroll;
   LOOP
      FETCH c_student_enroll INTO r_student_enroll;
      EXIT WHEN c_student_enroll%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Student INFO: ID '||
         r_student_enroll.student_id||' is '||
         r_student_enroll.first_name|| ' ' ||
         r_student_enroll.last_name||
         ' is enrolled in '||r_student_enroll.enroll||
         r_student_enroll.class);
   END LOOP;
   CLOSE c_student_enroll;
EXCEPTION
   WHEN OTHERS
   THEN
    IF c_student_enroll %ISOPEN
      THEN
    CLOSE c_student_enroll;
    END IF;
END;


DECLARE
   CURSOR c_group_discount IS
      SELECT DISTINCT s.course_no 
        FROM section s, enrollment e
       WHERE s.section_id = e.section_id
        GROUP BY s.course_no, e.section_id, s.section_id
       HAVING COUNT(*)>=8;
BEGIN
   FOR r_group_discount IN c_group_discount   LOOP
      UPDATE course
         SET cost = cost * .95
       WHERE course_no = r_group_discount.course_no;
   END LOOP;
   COMMIT;
END;

DECLARE
   CURSOR c_group_discount IS
      SELECT DISTINCT s.course_no 
        FROM section s, enrollment e
       WHERE s.section_id = e.section_id
        GROUP BY s.course_no, e.section_id, s.section_id
       HAVING COUNT(*)>=8;
   v_course_no c_group_discount%rowtype;
BEGIN
   open c_group_discount;
   LOOP
      fetch c_group_discount into v_course_no;
      UPDATE course
         SET cost = cost * .95
       WHERE course_no = v_course_no.course_no;
   END LOOP;
   COMMIT;
END;

select count(*) from student;
select * from student;
select * from student where created_by = 'xls';

create or replace trigger student_bi
before insert on student
for each row
declare
    v_student_id student.student_id%type;
begin
    select student_id_seq.nextval
    into v_student_id
    from dual;
    :new.student_id := v_student_id;
    :new.created_by := user;
    :new.created_date := sysdate;
    :new.modified_by := user;
    :new.modified_date := sysdate;
end;

INSERT INTO student (student_id, first_name, last_name, zip,  
   registration_date, created_by, created_date, modified_by,   
   modified_date)
VALUES (STUDENT_ID_SEQ.NEXTVAL, 'John', 'Smith', '00914', SYSDATE,  
   USER, SYSDATE, USER, SYSDATE);
   

INSERT INTO student (first_name, last_name, zip, registration_date)
VALUES ('John', 'Smith', '00914', SYSDATE);

create or replace trigger instructor_aud
after update or delete on instructor
declare
      v_type varchar2(10);
      pragma autonomous_transaction;
begin
      if UPDATING then
         v_type := 'UPDATE';
      elsif deleting then
         v_type := 'DELETE';
      end if;
      
      update statistics
      set transaction_user = user,
          transaction_date = sysdate
      where table_name = 'INSTRUCTOR'
            and transaction_name = v_type;
      
      if sql%notfound then
         insert into statistics 
         values('INSTRUCTOR', v_type, user, sysdate);
      end if;
      
      commit;
end;

UPDATE instructor
   SET phone = '7181234567'
 WHERE instructor_id = 101;

          
SELECT * 
  FROM statistics;