存儲過程

1.EXCEPTION:
DECLARE
   numerator   NUMBER;
   denominator NUMBER;
   the_ratio   NUMBER;
   lower_limit CONSTANT NUMBER := 0.72;
   samp_num    CONSTANT NUMBER := 132;
BEGIN
   SELECT x, y INTO numerator, denominator FROM result_table
      WHERE sample_id = samp_num;
   the_ratio := numerator/denominator;

   IF the_ratio > lower_limit THEN
      INSERT INTO ratio VALUES (samp_num, the_ratio);
   ELSE
      INSERT INTO ratio VALUES (samp_num, -1);
   END IF;
   COMMIT;
EXCEPTION
   WHEN ZERO_DIVIDE THEN
      INSERT INTO ratio VALUES (samp_num, 0);
      COMMIT;
   WHEN OTHERS THEN
      ROLLBACK;
END;

2.
CREATE OR REPLACE PROCEDURE
    get_student_major
        (v_student_id  IN  students.student_id%TYPE,
         v_name        OUT students.student_name%TYPE) IS
BEGIN
    SELECT student_name
      INTO v_name
      FROM students
     WHERE student_id = v_student_id;
exception
    WHEN TOO_MANY_ROWS THEN dbms_output.put_line('TMR error'); -- 行數 > 1
    WHEN NO_DATA_FOUND THEN dbms_output.put_line('NDF error'); -- 行數為 0
END;

3.
CREATE OR REPLACE FUNCTION
    update_salaries(new_sal IN professors.salary%TYPE) -- %TYPE 某一字段的類型作為類型
    RETURN NATURAL IS
BEGIN
    UPDATE professors2 SET salary = new_sal;
    RETURN SQL%ROWCOUNT; -- 判斷更新的行數
END update_salaries;

4.
CREATE OR REPLACE PROCEDURE
    add_student(rec IN OUT students%ROWTYPE) IS -- students%ROWTYPE作為表的某一行作為變量類型
BEGIN
    SELECT 'A'||students_pk_seq.nextval
      INTO rec.student_id
      FROM dual;

    INSERT INTO students (student_id, student_name,
        college_major, status, state, license_no)
    VALUES (rec.student_id, rec.student_name,
        rec.college_major, rec.status,
        rec.state, rec.license_no);
END add_student;

5.
DECLARE
   dept_rec1 dept%ROWTYPE;  --dept是表名,則dept_rec1 和 dept_rec2 可以相互賦值
   dept_rec2 dept%ROWTYPE;

   CURSOR c1 IS SELECT deptno, dname, loc FROM dept;
   dept_rec3 c1%ROWTYPE;
BEGIN
   ...
   dept_rec1 := dept_rec2;

However, because dept_rec2 is based on a table and dept_rec3 is based on a cursor, the following assignment is illegal:
dept_rec2 := dept_rec3;  -- illegal

6.
DECLARE
   CURSOR my_cursor IS
      SELECT sal + NVL(comm, 0) wages, ename FROM emp;
   my_rec  my_cursor%ROWTYPE;
BEGIN
   OPEN my_cursor;
   LOOP
      FETCH my_cursor INTO my_rec;
      EXIT WHEN my_cursor%NOTFOUND;

      IF my_rec.wages > 2000 THEN
         INSERT INTO temp VALUES (NULL, my_rec.wages, my_rec.ename);
      END IF;
   END LOOP;
   CLOSE my_cursor;
END;

7. *****
-- 包，函數，存儲過程
CREATE OR REPLACE PACKAGE BODY students_pkg IS

    FUNCTION add_student(rec IN students%ROWTYPE)
        RETURN students.student_id%TYPE
    IS
        ID students.student_id%TYPE;
    BEGIN
        SELECT 'A'||students_pk_seq.nextval INTO ID FROM dual;

        INSERT INTO students (student_id, student_name,
            college_major, status, state, license_no)
        VALUES (ID, rec.student_name, rec.college_major,
            rec.status, rec.state, rec.license_no);

        RETURN ID;
    END add_student;

    PROCEDURE add_vehicle(rec IN student_vehicles%ROWTYPE) IS
    BEGIN
        INSERT INTO student_vehicles (state, tag_no,
            vehicle_desc, student_id, parking_sticker)
        VALUES (rec.state, rec.tag_no,
            rec.vehicle_desc, rec.student_id, rec.parking_sticker);
    END add_vehicle;
END students_pkg;

-- 執行
DECLARE
    student students%ROWTYPE;
    vehicle student_vehicles%ROWTYPE;
BEGIN
    student.student_name  := 'Jack';
    student.college_major := 'HI';
    student.status        := 'Degree';
    student.state         := 'CA';
    student.license_no    := 'MV-232-14';

    student.student_id := students_pkg.add_student(student);

    vehicle.state           := 'CA';
    vehicle.tag_no          := 'CA-1234';
    vehicle.vehicle_desc    := 'Mustang';
    vehicle.parking_sticker := 'A-101';

    vehicle.student_id := student.student_id;

    students_pkg.add_vehicle(vehicle);
END;

posted on 2009-12-02 16:39 飛熊 閱讀(196) 評論(0)  編輯  收藏 所屬分類: ORACLE