《Oracle PL/SQL实例精讲》学习笔记20——过程

本章内容：

1. 创建过程

2. 传递过程的IN和OUT参数

基本概念：

1. 模块化代码的好处：可复用、易管理

A PL/SQL module is any complete logical unit of work. There are five types of PL/SQL  modules: (1) anonymous blocks that are run with a text script (the type you have used  until now), (2) procedures, (3) functions, (4) packages, and (5) triggers. There are two  main benefits to using modular code: (1) It is more reusable and (2) it is more manageable.

2. 块结构

The same block structure is used for all the module types. The block begins with a header  (for named blocks only), which consists of (1) the name of the module and (2) a parameter  list (if used).  The declaration section defines variables, cursors, and sub-blocks that will be needed in  the next section.  The main part of the module is the execution section, where all of the calculations and  processing are performed. This will contain executable code such as IF-THEN-ELSE  statements, loops, calls to other PL/SQL modules, and so on.  The last section of the module is an optional exception handler, which contains the code  to handle exceptions.

3. 匿名块

Until this chapter, you have written only anonymous blocks. Anonymous blocks are very  much like modules, except that anonymous blocks do not have headers. There are  important distinctions, though. As the name implies, anonymous blocks have no names  and, therefore, cannot be called by another block. They are not stored in the database and  must be compiled and then run each time the script is loaded.  The PL/SQL block in a subprogram is a named block that can accept parameters and  can be invoked from an application that can communicate with the Oracle database server.  A subprogram can be compiled and stored in the database. This allows the programmer to  reuse the program. It also provides for easier maintenance of code. Subprograms may be  either procedures or functions.

代码如下：

1. 创建第一个过程

SQL> CREATE OR REPLACE PROCEDURE Discount
  2  AS
  3    CURSOR c_group_discount
  4    IS
  5      SELECT distinct s.course_no, c.description
  6        FROM section s, enrollment e, course c
  7       WHERE s.section_id = e.section_id
  8         AND c.course_no = s.course_no
  9       GROUP BY s.course_no, c.description,
 10                e.section_id, s.section_id
 11       HAVING COUNT(*) >=8;
 12  BEGIN
 13     FOR r_group_discount IN c_group_discount
 14     LOOP
 15        UPDATE course
 16           SET cost = cost * .95
 17         WHERE course_no = r_group_discount.course_no;
 18        DBMS_OUTPUT.PUT_LINE
 19          ('A 5% discount has been given to '||
 20           r_group_discount.course_no||' '||
 21           r_group_discount.description
 22          );
 23     END LOOP;
 24  END;
 25  /

2. 查询数据字典获取过程的信息

SQL> SELECT object_name, object_type, status
  2    FROM user_objects
  3   WHERE object_name = 'DISCOUNT';

OBJECT_NAME          OBJECT_TYP STATUS
-------------------- ---------- ----------
DISCOUNT             PROCEDURE  VALID

 

SQL> SET PAGESIZE 500
SQL> /* Formatted on 2018/11/12 21:32:57 (QP5 v5.256.13226.35538) */
SQL> --输出自定义函数的内容
SQL>
SQL> SELECT DBMS_LOB.SUBSTR (
  2            DBMS_METADATA.GET_DDL ('PROCEDURE', 'DISCOUNT', 'C##STUDENT')) SCRIPT
  3    FROM DUAL;

SCRIPT
--------------------------------------------------------------------------------

  CREATE OR REPLACE EDITIONABLE PROCEDURE "C##STUDENT"."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 * .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;

3. 过程的出、入参

SQL> -- For Example  ch19_2.sql
SQL> CREATE OR REPLACE PROCEDURE find_sname
  2    (i_student_id IN NUMBER,
  3     o_first_name OUT VARCHAR2,
  4     o_last_name OUT VARCHAR2
  5     )
  6  AS
  7  BEGIN
  8    SELECT first_name, last_name
  9      INTO o_first_name, o_last_name
 10      FROM student
 11     WHERE student_id = i_student_id;
 12  EXCEPTION
 13    WHEN OTHERS
 14    THEN
 15      DBMS_OUTPUT.PUT_LINE('Error in finding student_id:
 16        '||i_student_id);
 17  END find_sname;
 18  /

SQL> --For Example ch19_3.sql
SQL> DECLARE
  2    v_local_first_name student.first_name%TYPE;
  3    v_local_last_name student.last_name%TYPE;
  4  BEGIN
  5    find_sname
  6      (145, v_local_first_name, v_local_last_name);
  7    DBMS_OUTPUT.PUT_LINE
  8      ('Student 145 is: '||v_local_first_name||
  9       ' '|| v_local_last_name||'.'
 10      );
 11  END;
 12  /
Student 145 is: Paul Lefkowitz.