plsql procedure

1.CREATE OR REPLACE PROCEDURE discount AS
    CURSOR c_group_discount IS
        SELECT DISTINCT s.course_no, c.description
          FROM section s, enrollment e, course c
         WHERE s.section_id = e.section_id
           AND c.course_no = s.course_no
         GROUP BY s.course_no, c.description, e.section_id, s.section_id
        HAVING COUNT(*) >= 8;
BEGIN
    FOR r_group_discount IN c_group_discount
    LOOP
        UPDATE course
           SET cost = cost * 0.95
         WHERE course_no = r_group_discount.course_no;
        dbms_output.put_line('A 5% discount has been given to ' ||
                             r_group_discount.course_no || ' ' ||
                             r_group_discount.description);
    END LOOP;
END;

查询过程状态：

SQL> select object_name,object_type,status from user_objects where object_name='DISCOUNT';

OBJECT_NAME OBJECT_TYPE STATUS
--------------- --------------- -------
DISCOUNT PROCEDURE VALID

查询该过程的源代码：

SQL> col text for a70
SQL> select to_char(line,99)||'>',text from user_source where name='DISCOUNT';

TO_C TEXT
---- ----------------------------------------------------------------------
  1> PROCEDURE discount AS
  2> CURSOR c_group_discount IS
  3>     SELECT DISTINCT s.course_no, c.description
  4>       FROM section s, enrollment e, course c
  5>      WHERE s.section_id = e.section_id
  6> AND c.course_no = s.course_no
  7>      GROUP BY s.course_no, c.description, e.section_id, s.section_
     id


  8>     HAVING COUNT(*) >= 8;
  9> BEGIN


TO_C TEXT
---- ----------------------------------------------------------------------
 10> FOR r_group_discount IN c_group_discount
 11> LOOP
 12>     UPDATE course
 13> SET cost = cost * 0.95
 14>      WHERE course_no = r_group_discount.course_no;
 15>     dbms_output.put_line('A 5% discount has been given to ' ||
 16>  r_group_discount.course_no || ' ' ||
 17>  r_group_discount.description);
 18> END LOOP;
 19> END;
19 rows selected.

编译过程：

SQL> alter procedure discount compile;
Procedure altered.

测试以上过程：

SQL> set serveroutput on;
SQL> execute discount;
A 5% discount has been given to 120 Intro to Java Programming
A 5% discount has been given to 140 Systems Analysis
A 5% discount has been given to 230 Intro to the Internet
A 5% discount has been given to 25 Intro to Programming
A 5% discount has been given to 122 Intermediate Java Programming
A 5% discount has been given to 240 Intro to the BASIC Language
PL/SQL procedure successfully completed.

2.过程传人和传出参数：

CREATE OR REPLACE PROCEDURE find_sname(i_student_id IN NUMBER,
                                       o_first_name OUT VARCHAR2,
                                       o_last_name  OUT VARCHAR2) AS
BEGIN
    SELECT first_name, last_name
      INTO o_first_name, o_last_name
      FROM student
     WHERE student_id = i_student_id;
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('Error in finding student_id: ' ||
                             i_student_id);
END find_sname;

测试以上过程：

DECLARE
    v_local_first_name student.first_name%TYPE;
    v_local_last_name  student.last_name%TYPE;
BEGIN
    find_sname(145, v_local_first_name, v_local_last_name);
    dbms_output.put_line('Student 145 is: ' || v_local_first_name || ' ' ||
                         v_local_last_name || '.');
END;

练习部分：

1.编写一个没有参数的过程。该过程能够告知当天是周六，还是周日。除此之外，还应该告知用户名和当前时间。还应该显示数据库中有多少有效和无效过程。

CREATE OR REPLACE PROCEDURE current_status AS
    v_day_type CHAR(1);
    v_user     VARCHAR2(30);
    v_valid    NUMBER;
    v_invalid  NUMBER;
BEGIN
    SELECT substr(to_char(SYSDATE, 'DAY'), 0, 1) INTO v_day_type FROM dual;
    IF v_day_type = 'S'
    THEN
        dbms_output.put_line('Today is a weekend.');
    ELSE
        dbms_output.put_line('Today is a weekday.');
    END IF;
    --***************
    dbms_output.put_line('The time is: ' || to_char(SYSDATE, 'HH:MI AM'));
    --****************
    SELECT USER INTO v_user FROM dual;
    dbms_output.put_line('The current user is ' || v_user);
    --**********************
    SELECT nvl(COUNT(*), 0)
      INTO v_valid
      FROM user_objects
     WHERE status = 'VALID'
       AND object_type = 'PROCEDURE';
    dbms_output.put_line('There are ' || v_valid || ' valid procedure.');
    --********************
    SELECT nvl(COUNT(*), 0)
      INTO v_invalid
      FROM user_objects
     WHERE status = 'INVALID'
       AND object_type = 'PROCEDURE';
    dbms_output.put_line('There are ' || v_invalid ||
                         ' invalid procedure.');
END;

QL> execute current_status;
Today is a weekend.
The time is: 10:58 AM
The current user is SYS
There are 123 valid procedure.
There are 2 invalid procedure.
PL/SQL procedure successfully completed.

2.编写一个过程：传人参数是邮政编码，城市和州，并且把这些信息插入到ZIPCODE表中。该过程已经检查所提供的邮政编码是否已经存在于数据库中。如果已经存在，则抛出一个异常，并显示一个错误信息。编写一个匿名语句块，使用这个过程来插入邮政编码记录。

CREATE OR REPLACE PROCEDURE insert_zip(i_zipcode IN zipcode.zip%TYPE,
                                       i_city    IN zipcode.city%TYPE,
                                       i_state   IN zipcode.state%TYPE) AS
    v_zipcode zipcode.zip%TYPE;
    v_city    zipcode.city%TYPE;
    v_state   zipcode.state%TYPE;
    v_dummy   zipcode.zip%TYPE;
BEGIN
    v_zipcode := i_zipcode;
    v_city    := i_city;
    v_state   := i_state;
    SELECT zip INTO v_dummy FROM zipcode WHERE zip = v_zipcode;
    --
    dbms_output.put_line('The zipcode ' || v_zipcode ||
                         ' is already in the database and cannot be' ||
                         ' reinserted.');
EXCEPTION
    WHEN no_data_found THEN
        INSERT INTO zipcode
        VALUES
            (v_zipcode, v_city, v_state, USER, SYSDATE, USER, SYSDATE);
    WHEN OTHERS THEN
        dbms_output.put_line('There was an unknown error ' ||
                             ' in insert_zip.');
END;

3.动态sql：

CREATE OR REPLACE PROCEDURE get_name_address(table_name_in  IN VARCHAR2,
                                             id_in          IN NUMBER,
                                             first_name_out OUT VARCHAR2,
                                             last_name_out  OUT VARCHAR2,
                                             street_out     OUT VARCHAR2,
                                             city_out       OUT VARCHAR2,
                                             state_out      OUT VARCHAR2,
                                             zip_out        OUT VARCHAR2) AS
    sql_stmt VARCHAR2(2000);
BEGIN
    sql_stmt := 'select a.first_name,a.last_name,a.street_address' ||
                ' ,b.city,b.state,b.zip' || ' from
' || table_name_in || ' a,zipcode b' ||
                ' where a.zip=b.zip' || ' and ' || table_name_in ||
                '_id=:1';
    --dbms_output.put_line(sql_stmt);
    EXECUTE IMMEDIATE sql_stmt
        INTO first_name_out, last_name_out, street_out, city_out, state_out, zip_out
        USING id_in;
END get_name_address;

测试上述过程：

DECLARE
    v_table_name VARCHAR2(20) := '&sv_table_name';
    v_id         NUMBER := &sv_id;
    v_first_name VARCHAR2(25);
    v_last_name  VARCHAR2(25);
    v_street     VARCHAR2(50);
    v_city       VARCHAR2(25);
    v_state      VARCHAR2(2);
    v_zip        VARCHAR2(5);
BEGIN
    get_name_address(v_table_name,
                     v_id,
                     v_first_name,
                     v_last_name,
                     v_street,
                     v_city,
                     v_state,
                     v_zip);
    dbms_output.put_line('First Name: ' || v_first_name);
    dbms_output.put_line('Last_name: ' || v_last_name);
    dbms_output.put_line('Street: ' || v_street);
    dbms_output.put_line('City: ' || v_city);
    dbms_output.put_line('State: ' || v_state);
    dbms_output.put_line('Zip Code: ' || v_zip);
   END;
   /
Enter value for sv_table_name: student
old   2:     v_table_name VARCHAR2(20) := '&sv_table_name';
new   2:     v_table_name VARCHAR2(20) := 'student';
Enter value for sv_id: 105
old   3:     v_id  NUMBER := &sv_id;
new   3:     v_id  NUMBER := 105;
First Name: Angel
Last_name: Moskowitz
Street: 320 John St.
City: Ft. Lee
State: NJ
Zip Code: 07024
PL/SQL procedure successfully completed.

修改以上过程，返回一个用户自定义的记录（创建一个包）：

CREATE OR REPLACE PACKAGE dynamic_sql_pkg AS
    --create user defined record type
    TYPE name_addr_rec_type IS RECORD(
        first_name VARCHAR2(25),
        last_name  VARCHAR2(25),
        street     VARCHAR2(50),
        city       VARCHAR2(25),
        state      VARCHAR2(2),
        zip        VARCHAR2(5));
    PROCEDURE get_name_address(table_name_in IN VARCHAR2,
                               id_in         IN NUMBER,
                               name_addr_rec OUT name_addr_rec_type);
END dynamic_sql_pkg;

CREATE OR REPLACE PACKAGE BODY dynamic_sql_pkg AS
    PROCEDURE get_name_address(table_name_in IN VARCHAR2,
                               id_in         IN NUMBER,
                               name_addr_rec OUT name_addr_rec_type) IS
        sql_stmt VARCHAR2(2000);
    BEGIN
        sql_stmt := 'select a.first_name,a.last_name,a.street_address' ||
                    ' ,b.city,b.state,b.zip' || ' from
' || table_name_in || ' a,zipcode b' ||
                    ' where a.zip=b.zip' || ' and ' || table_name_in ||
                    '_id=:1';
        EXECUTE IMMEDIATE sql_stmt
            INTO name_addr_rec
            USING id_in;
    END get_name_address;
END dynamic_sql_pkg;

测试包可用性：

DECLARE
    v_table_name  VARCHAR2(20) := '&sv_table_name';
    v_id          NUMBER := &sv_id;
    name_addr_rec dynamic_sql_pkg.name_addr_rec_type;
BEGIN
    dynamic_sql_pkg.get_name_address(v_table_name, v_id, name_addr_rec);
    dbms_output.put_line('First Name: ' || name_addr_rec.first_name);
    dbms_output.put_line('Last Name: ' || name_addr_rec.last_name);
    dbms_output.put_line('Street: ' || name_addr_rec.street);
    dbms_output.put_line('City: ' || name_addr_rec.city);
    dbms_output.put_line('State: ' || name_addr_rec.state);
    dbms_output.put_line('Zip Code: ' || name_addr_rec.zip);
END;
 /
Enter value for sv_table_name: student
old   2:     v_table_name  VARCHAR2(20) := '&sv_table_name';
new   2:     v_table_name  VARCHAR2(20) := 'student';
Enter value for sv_id: 105
old   3:     v_id   NUMBER := &sv_id;
new   3:     v_id   NUMBER := 105;
First Name: Angel
Last Name: Moskowitz
Street: 320 John St.
City: Ft. Lee
State: NJ
Zip Code: 07024
PL/SQL procedure successfully completed、

5.CREATE OR REPLACE PROCEDURE add_contact(member_id        NUMBER,

                                        contact_type     NUMBER,
                                        last_name        VARCHAR2,
                                        first_name       VARCHAR2,
                                        middle_name      VARCHAR2 := NULL,
                                        address_type     NUMBER := NULL,
                                        street_address   street_address_table := street_address_table(),
                                        city             VARCHAR2 := NULL,
                                        state_province   VARCHAR2 := NULL,
                                        postal_code      VARCHAR2 := NULL,
                                        country_code     VARCHAR2 := NULL,
                                        telephone_type   NUMBER := NULL,
                                        area_code        VARCHAR2 := NULL,
                                        telephone_number VARCHAR2 := NULL,
                                        created_by       NUMBER,
                                        creation_date    DATE := SYSDATE,
                                        last_updated_by  NUMBER,
                                        last_update_date DATE := SYSDATE) IS
    lv_address_id NUMBER;
BEGIN
    SAVEPOINT add_contact;
    INSERT INTO contact
    VALUES
        (contact_s1.nextval,
         member_id,
         contact_type,
         first_name,
         middle_name,
         last_name,
         created_by,
         creation_date,
         last_updated_by,
         last_update_date);
    IF address_type IS NOT NULL
       AND city IS NOT NULL
       AND state_province IS NOT NULL
       AND postal_code IS NOT NULL
    THEN
        INSERT INTO address
        VALUES
            (address_s1.nextval,
             contact_s1.currval,
             address_type,
             city,
             state_province,
             postal_code,
             created_by,
             creation_date,
             last_updated_by,
             last_update_date);
        lv_address_id := address_s1.currval;
        IF street_address.count > 0
        THEN
            FOR i IN 1 .. street_address.count
            LOOP
                INSERT INTO street_address
                VALUES
                    (street_address_s1.nextval,
                     lv_address_id,
                     street_address(i),
                     created_by,
                     creation_date,
                     last_updated_by,
                     last_update_date);
            END LOOP;
        END IF;
    END IF;
    IF telephone_type IS NOT NULL
       AND country_code IS NOT NULL
       AND area_code IS NOT NULL
       AND telephone_number IS NOT NULL
    THEN
        INSERT INTO telephone
        VALUES
            (telephone_s1.nextval,
             contact_s1.currval,
             lv_address_id,
             telephone_type,
             country_code,
             area_code,
             telephone_number,
             created_by,
             creation_date,
             last_updated_by,
             last_update_date);
    END IF;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO add_contact;
        raise_application_error(-20001, SQLERRM);
END add_contact;