《Oracle PL/SQL开发指南》学习笔记28——源码调试——PL/SQL基础知识（第三部分）

最新推荐文章于 2021-04-07 20:03:16 发布

预见未来to50

最新推荐文章于 2021-04-07 20:03:16 发布

阅读量245

点赞数

分类专栏：数据库（DB）

本文链接：https://blog.csdn.net/hpdlzu80100/article/details/84539488

版权

数据库（DB）专栏收录该内容

368 篇文章 11 订阅

订阅专栏

1. 变量赋值时（隐式）强制类型转换（让我想起了将近二十年前学C语言时的场景）

SQL> edit
Wrote file afiedt.buf

  1  DECLARE
  2  lv_input   INTEGER;
  3  BEGIN
  4  lv_input   :=      4.67;
  5  dbms_output.put_line('['||lv_input||']');
  6  EXCEPTION
  7  WHEN       OTHERS  THEN
  8  dbms_output.put_line('['||SQLERRM||']');
  9* END;
SQL> /
[5]

PL/SQL procedure successfully completed.

2. 嵌套匿名块中的变量的作用域（学C语言时就碰到过）

SQL> edit
Wrote file afiedt.buf

  1  /* Formatted on 2018/11/26 13:58:37 (QP5 v5.256.13226.35538) */
  2  DECLARE
  3     --    Declare    local    variable.
  4     lv_input   VARCHAR2 (30) DEFAULT 'OUTER';
  5  BEGIN
  6     --    Print    the    value    before    the    inner    block.
  7     DBMS_OUTPUT.put_line ('Outer    block    [' || lv_input || ']');
  8     --    Nested    block.
  9     BEGIN
 10        --    Print    the    value    before    the    assignment.
 11        DBMS_OUTPUT.put_line ('Inner    block    [' || lv_input || ']');
 12        --    Assign    new    value    to    variable.
 13        lv_input := 'INNER';
 14        --    Print    the    value    after    the    assignment.
 15        DBMS_OUTPUT.put_line ('Inner    block    [' || lv_input || ']');
 16     END;
 17     --    Print    the    value    after    the    nested    block.
 18     DBMS_OUTPUT.put_line ('Outer    block    [' || lv_input || ']');
 19  EXCEPTION
 20     WHEN OTHERS
 21     THEN
 22        DBMS_OUTPUT.put_line ('Exception    [' || SQLERRM || ']');
 23* END;
 24  /
Outer    block    [OUTER]
Inner    block    [OUTER]
Inner    block    [INNER]
Outer    block    [INNER]

PL/SQL procedure successfully completed.

3. 同名变量在内部块和外部块具有不同的“可见性” （这个可是当年大学C语言考试必类内容啊，笑！）

SQL> edit
Wrote file afiedt.buf

  1  /* Formatted on 2018/11/26 14:05:26 (QP5 v5.256.13226.35538) */
  2  DECLARE
  3     -- Declare local variable.
  4     lv_outer VARCHAR2 (30) DEFAULT 'OUTER';
  5     lv_active   VARCHAR2 (30) DEFAULT 'OUTER';
  6  BEGIN
  7     -- Print the value before the inner block.
  8     DBMS_OUTPUT.put_line (
  9     'Outer [' || lv_outer || '][' || lv_active || ']');
 10     -- Nested block.
 11     DECLARE
 12     -- Declare local variable.
 13     lv_active   VARCHAR2 (30) DEFAULT 'INNER';
 14     BEGIN
 15     -- Print the value before the assignment.
 16     DBMS_OUTPUT.put_line (
 17     'Inner [' || lv_outer || '][' || lv_active || ']');
 18     -- Assign new value to variable.
 19     lv_outer := 'INNER';
 20     -- Print the value after the assignment.
 21     DBMS_OUTPUT.put_line (
 22     'Inner [' || lv_outer || '][' || lv_active || ']');
 23     END;
 24     -- Print the value after the nested block.
 25     DBMS_OUTPUT.put_line (
 26     'Outer [' || lv_outer || '][' || lv_active || ']');
 27  EXCEPTION
 28     WHEN OTHERS
 29     THEN
 30     DBMS_OUTPUT.put_line ('Exception ' || SQLERRM || ']');
 31* END;
 32  /
Outer [OUTER][OUTER]
Inner [OUTER][INNER]
Inner [INNER][INNER]
Outer [INNER][OUTER]

PL/SQL procedure successfully completed.

4. 局部命名块 (Local Named Blocks) （这名字也真够唬人的，无非就是在匿名块中嵌套了一个命名的过程而已）

SQL> edit
Wrote file afiedt.buf

  1  /* Formatted on 2018/11/26 14:15:09 (QP5 v5.256.13226.35538) */
  2  DECLARE
  3     -- Declare local variable.
  4     lv_outer    VARCHAR2 (30) DEFAULT 'OUTER';
  5     lv_active   VARCHAR2 (30) DEFAULT 'OUTER';
  6     -- A local procedure without any formal parameters.
  7     PROCEDURE local_named
  8     IS
  9        -- Declare local variable.
 10        lv_active   VARCHAR2 (30) DEFAULT 'INNER';
 11     BEGIN
 12        -- Print the value before the assignment.
 13        DBMS_OUTPUT.put_line (
 14           'Inner [' || lv_outer || '][' || lv_active || ']');
 15        -- Assign new value to variable.
 16        lv_active := 'INNER';
 17        -- Print the value after the assignment.
 18        DBMS_OUTPUT.put_line (
 19           'Inner [' || lv_outer || '][' || lv_active || ']');
 20     END local_named;
 21  BEGIN
 22     -- Print the value before the inner block.
 23     DBMS_OUTPUT.put_line ('Outer ' || lv_outer || '][' || lv_active || ']');
 24     -- Call to the locally declared named procedure.
 25     local_named;
 26     -- Print the value after the nested block.
 27     DBMS_OUTPUT.put_line ('Outer [' || lv_outer || '][' || lv_active || ']');
 28  EXCEPTION
 29     WHEN OTHERS
 30     THEN
 31        DBMS_OUTPUT.put_line ('Exception [' || SQLERRM || ']');
 32* END;
SQL> /
Outer OUTER][OUTER]
Inner [OUTER][INNER]
Inner [OUTER][INNER]
Outer [OUTER][OUTER]

PL/SQL procedure successfully completed.

5. 在命名程序中放入代码逻辑的方法称为模块性，通常它能使你的代码结构更加清晰

6. PL/SQL是一个单步运行的解析过程，它从上至下一次性读取源代码。这意味着所有的标识符（如函数名和过程名）必须在调用前予以定义，否则会导致运行时错误。

SQL> /* Formatted on 2018/11/26 14:22:03 (QP5 v5.256.13226.35538) */
SQL> DECLARE
  2     PROCEDURE jack
  3     IS
  4     BEGIN
  5        DBMS_OUTPUT.put_line (hector || ' World!');
  6     END jack;
  7
  8     FUNCTION hector
  9        RETURN VARCHAR2
 10     IS
 11     BEGIN
 12        RETURN 'Hello';
 13     END hector;
 14  BEGIN
 15     jack;
 16  END;
 17  /
      DBMS_OUTPUT.put_line (hector || ' World!');
                            *
ERROR at line 5:
ORA-06550: line 5, column 29:
PLS-00313: 'HECTOR' not declared in this scope
ORA-06550: line 5, column 7:
PL/SQL: Statement ignored

7. 上述程序可如下修改

SQL> edit
Wrote file afiedt.buf

  1  DECLARE
  2     PROCEDURE jack;
  3     FUNCTION hector
  4        RETURN VARCHAR2;
  5     PROCEDURE jack
  6     IS
  7     BEGIN
  8        DBMS_OUTPUT.put_line (hector || ' World!');
  9     END jack;
 10     FUNCTION hector
 11        RETURN VARCHAR2
 12     IS
 13     BEGIN
 14        RETURN 'Hello';
 15     END hector;
 16  BEGIN
 17     jack;
 18* END;
 19  /
Hello World!

PL/SQL procedure successfully completed.

8. 存储命名块（模式级的函数或过程）

SQL> /* Formatted on 2018/11/26 14:28:38 (QP5 v5.256.13226.35538) */
SQL> CREATE OR REPLACE PROCEDURE local_named
  2  IS
  3     -- Declare local variable.
  4     lv_active   VARCHAR2 (30) DEFAULT 'INNER';
  5     lv_outer    VARCHAR2 (30) DEFAULT ' ';
  6  BEGIN
  7     -- Print the value before the assignment.
  8     DBMS_OUTPUT.put_line ('Inner [' || lv_outer || '][' || lv_active || ']');
  9
 10     -- Assign new value to variable.
 11     lv_outer := 'INNER';
 12
 13     -- Print the value after the assignment.
 14     DBMS_OUTPUT.put_line ('Inner [' || lv_outer || '][' || lv_active || ']');
 15  END local_named;
 16  /

Procedure created.

SQL> exec local_named;
Inner [ ][INNER]
Inner [INNER][INNER]

PL/SQL procedure successfully completed.

/* Formatted on 2018/11/26 14:32:14 (QP5 v5.256.13226.35538) */
DECLARE
   -- Declare local variable.
   lv_outer    VARCHAR2 (30) DEFAULT 'OUTER';
   lv_active   VARCHAR2 (30) DEFAULT 'OUTER';
BEGIN
   -- Print the value before the inner block.
   DBMS_OUTPUT.put_line ('Outer [' || lv_outer || '][' || lv_active || ']');

   -- Call to the locally declared named procedure.
   local_named;

   -- Print the value after the nested block.
   DBMS_OUTPUT.put_line ('Outer [' || lv_outer || '][' || lv_active || ']');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Exception [' || SQLERRM || ']');
END;

输出为：

Outer [OUTER][OUTER]
Inner [ ][INNER]
Inner [INNER][INNER]
Outer [OUTER][OUTER]

PL/SQL procedure successfully completed.

预见未来to50

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
《Oracle PL/SQL开发指南》学习笔记28——源码调试——PL/SQL基础知识（第三部分）

1. 变量赋值时（隐式）强制类型转换（让我想起了将近二十年前学C语言时的场景）SQL&gt; editWrote file afiedt.buf 1 DECLARE 2 lv_input INTEGER; 3 BEGIN 4 lv_input := 4.67; 5 dbms_output.put_line('['||lv_input||'...
复制链接

扫一扫