《Oracle PL/SQL开发指南》学习笔记31——源码调试——函数和过程（第九部分，过程 ）

 

1. 命名块过程的原型

[{EDITIONALBE | NONEDITIONALBE}] PROCEDURE procedure_name
( parameter1 [IN][OUT] [NOCOPY] sql_datatype | plsql_datatype
, parameter2 [IN][OUT] [NOCOPY] sql_datatype | plsql_datatype
, parameter(n+1) [IN][OUT] [NOCOPY] sql_datatype | plsql_datatype )
[ACCESSIBLE BY
( [{FUNCTION | PROCEDURE | PACKAGE | TYPE}] [schema.]unit_name)
[,[{FUNCTION | PROCEDURE | PACKAGE | TYPE}] [schema.]unit_name)]
[,... ]]])
[ AUTHID DEFINER | CURRENT_USER ] IS
declaration_statements
BEGIN
execution_statements
[EXCEPTION]
exception_handling_statements
END [procedure_name];
/

2. 按值传递过程

SQL> CREATE TABLE avatar
  2  (
  3     avatar_id     NUMBER GENERATED AS IDENTITY CONSTRAINT avatar_pk PRIMARY KEY,
  4     avatar_name   VARCHAR2 (30)
  5  );

表已创建。

SQL> /* Formatted on 2018/12/9 21:31:39 (QP5 v5.256.13226.35538) */
SQL> CREATE TABLE episode
  2  (
  3     episode_id     NUMBER
  4                       GENERATED AS IDENTITY
  5                       CONSTRAINT episode_pk PRIMARY KEY,
  6     avatar_id      NUMBER CONSTRAINT episode_nn1 NOT NULL,
  7     episode_name   VARCHAR2 (30),
  8     CONSTRAINT episode_fk1 FOREIGN KEY
  9        (avatar_id)
 10         REFERENCES avatar (avatar_id)
 11  );

表已创建。

SQL> /* Formatted on 2018/12/9 21:35:17 (QP5 v5.256.13226.35538) */
SQL> CREATE OR REPLACE PROCEDURE adding_avatar (pv_avatar_name     VARCHAR2,
  2                                             pv_episode_name    VARCHAR2)
  3  IS
  4     /* Declare local variable to manage IDENTITY column
  5     surrogate key. */
  6     lv_avatar_id   NUMBER;
  7  BEGIN
  8     /* Set a Savepoint. */
  9     SAVEPOINT all_or_none;
 10
 11     /* Insert row into avatar. */
 12     INSERT INTO avatar (avatar_name)
 13          VALUES (pv_avatar_name)
 14       RETURNING avatar_id
 15            INTO lv_avatar_id;
 16
 17     /* Insert row into avatar. */
 18     INSERT INTO episode (avatar_id, episode_name)
 19          VALUES (lv_avatar_id, pv_episode_name);
 20
 21     /* Save change inside its own transaction scope. */
 22     COMMIT;
 23  EXCEPTION
 24     WHEN OTHERS
 25     THEN
 26        ROLLBACK TO all_or_none;
 27  END;
 28  /

过程已创建。

SQL> ed
已写入 file afiedt.buf

  1  BEGIN
  2  adding_avatar('Airbender','Episode 1');
  3* END;
SQL> /

PL/SQL 过程已成功完成。

SQL> select * from episode;

EPISODE_ID  AVATAR_ID EPISODE_NAME
---------- ---------- ------------------------------
         1          1 Episode 1

3. 按引用传递过程

SQL> /* Formatted on 2018/12/9 21:40:36 (QP5 v5.256.13226.35538) */
SQL> CREATE OR REPLACE PROCEDURE adding_avatar (pv_avatar_name    IN     VARCHAR2,
  2                                             pv_episode_name   IN     VARCHAR2,
  3                                             pv_completion        OUT BOOLEAN)
  4  IS
  5     /* Declare local variable to manage IDENTITY column
  6     surrogate key. */
  7     lv_avatar_id   NUMBER;
  8  BEGIN
  9     /* Set completion variable. */
 10     pv_completion := FALSE;
 11
 12     /* Set a Savepoint. */
 13     SAVEPOINT all_or_none;
 14
 15
 16      /* Formatted on 2018/12/9 21:49:31 (QP5 v5.256.13226.35538) */
 17      INSERT INTO avatar (avatar_name)
 18           VALUES (pv_avatar_name)
 19        RETURNING avatar_id
 20             INTO lv_avatar_id;
 21
 22      /* Insert row into avatar. */
 23
 24      INSERT INTO episode (avatar_id, episode_name)
 25           VALUES (lv_avatar_id, pv_episode_name);
 26     /* Save change inside its own transaction scope. */
 27     COMMIT;
 28
 29     /* Set completion variable. */
 30     pv_completion := TRUE;
 31  EXCEPTION
 32     WHEN OTHERS
 33     THEN
 34        ROLLBACK TO all_or_none;
 35  END;
 36  /

过程已创建。

SQL> /* Formatted on 2018/12/9 21:50:40 (QP5 v5.256.13226.35538) */
SQL> DECLARE
  2     result   BOOLEAN;
  3  BEGIN
  4     adding_avatar ('Pandeng', 'Episode 2', result);
  5
  6     IF result
  7     THEN
  8        DBMS_OUTPUT.put_line ('new record inserted!');
  9     ELSE
 10        DBMS_OUTPUT.put_line ('not inserted!');
 11     END IF;
 12  END;
 13  /
new record inserted!

PL/SQL 过程已成功完成。

SQL> select * from avatar;

 AVATAR_ID AVATAR_NAME
---------- ------------------------------
         1 Airbender
         2 Pandeng

SQL> select * from episode;

EPISODE_ID  AVATAR_ID EPISODE_NAME
---------- ---------- ------------------------------
         1          1 Episode 1
         2          2 Episode 2