本文共 5656 字，大约阅读时间需要 18 分钟。

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;

转载地址：http://gnwob.baihongyu.com/