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

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

1. 定长和变长数据类型的赋值和空间分配

SQL> edit
Wrote file afiedt.buf

  1  DECLARE
  2     lv_fixed   CHAR (40) := 'Something not quite long.';
  3     lv_variable   VARCHAR2 (40) := 'Something not quite long.';
  4  BEGIN
  5     DBMS_OUTPUT.put_line ('Fixed Length [' || LENGTH (lv_fixed) || ']');
  6     DBMS_OUTPUT.put_line (
  7     'Varying Length [' || LENGTH (lv_variable) || ']');
  8* END;
SQL> /
Fixed Length [40]
Varying Length [25]

PL/SQL procedure successfully completed.

2. 日期

SQL> edit
Wrote file afiedt.buf

  1  DECLARE
  2     lv_date_1   DATE := '28-APR-75';
  3     lv_date_2   DATE := '29-APR-1975';
  4     lv_date_3   DATE := TO_DATE ('19750430', 'YYYYMMDD');
  5  BEGIN
  6     DBMS_OUTPUT.put_line ('Implicit [' || lv_date_1 || ']');
  7     DBMS_OUTPUT.put_line ('Implicit [' || lv_date_2 || ']');
  8     DBMS_OUTPUT.put_line ('Explicit [' || lv_date_3 || ']');
  9     DBMS_OUTPUT.put_line ('Explicit [' || to_char(lv_date_3,'YYYY-MM-DD') || ']');
 10* END;
SQL> /
Implicit [28-APR-75]
Implicit [29-APR-75]
Explicit [30-APR-75]
Explicit [1975-04-30]

PL/SQL procedure successfully completed.

3. 非受限、受限Number数值的隐式转换

SQL> /* Formatted on 2018/11/26 17:46:25 (QP5 v5.256.13226.35538) */
SQL> DECLARE
  2     lv_number1   NUMBER;
  3     lv_number3   NUMBER(4,1);
  4     lv_number2   NUMBER (4, 2) := 99.99;
  5  BEGIN
  6     lv_number1 := lv_number2;
  7     lv_number3 := lv_number2;
  8     DBMS_OUTPUT.put_line (lv_number1);
  9     DBMS_OUTPUT.put_line (lv_number3);
 10  END;
 11  /
99.99
100

PL/SQL procedure successfully completed.

4. 可见列及不可见列的锚定

SQL> /* Formatted on 2018/11/26 17:52:25 (QP5 v5.256.13226.35538) */
SQL> CREATE TABLE dwarves
  2  (
  3     dwarves_id   NUMBER GENERATED AS IDENTITY,
  4     name         VARCHAR2 (20),
  5     allegiance   VARCHAR2 (20) INVISIBLE
  6  );

Table created.

SQL> /* Formatted on 2018/11/26 17:54:06 (QP5 v5.256.13226.35538) */
SQL> DECLARE
  2     /* Anchor to a table with an invisible column. */
  3     dwarf   dwarves%ROWTYPE;
  4  BEGIN
  5          /* Select all columns into a local variable. */
  6          SELECT *
  7            INTO dwarf
  8            FROM dwarves
  9     FETCH FIRST 1 ROWS ONLY;
 10
 11     /* Print the invisible column. */
 12     DBMS_OUTPUT.put_line (
 13        '[' || dwarf.name || '][' || dwarf.allegiance || ']');
 14  END;
 15  /
      '[' || dwarf.name || '][' || dwarf.allegiance || ']');
                                         *
ERROR at line 13:
ORA-06550: line 13, column 42:
PLS-00302: component 'ALLEGIANCE' must be declared
ORA-06550: line 12, column 4:
PL/SQL: Statement ignored

星号（*）只映射所有可见列，而不是所有列（可见列和不可见列）。启发：在生产query中，最好不要使用 * 这种语法。

SQL> insert into dwarves (name, allegiance) values ('Bravo','high');

1 row created.

SQL> edit
Wrote file afiedt.buf

  1  DECLARE
  2     /* Create a cursor to unhide an invisible column. */
  3     CURSOR dwarf_cursor
  4     IS
  5        SELECT dwarves_id, name, allegiance FROM dwarves;
  6     /* Anchor to a table with an invisible column. */
  7     dwarf   dwarf_cursor%ROWTYPE;
  8  BEGIN
  9          /* Select all columns into a local variable. */
 10          SELECT dwarves_id, name, allegiance
 11            INTO dwarf
 12            FROM dwarves
 13     FETCH FIRST 1 ROWS ONLY;
 14     /* Print the invisible column. */
 15     DBMS_OUTPUT.put_line ( '[' || dwarf.dwarves_id || ']'||
 16        '[' || dwarf.name || '][' || dwarf.allegiance || ']');
 17* END;
SQL> /
[1][Bravo][high]

PL/SQL procedure successfully completed.

5. SQL UDT 是一种对象类型

SQL UDT This can hold a data structure. Two implementations are possible: an object type only implementation, which supports a SQL-level record structure, and both an object type and body implementation, which supports a class instance.

SQL> /* Formatted on 2018/11/26 18:09:16 (QP5 v5.256.13226.35538) */
SQL> CREATE OR REPLACE TYPE hobbit IS OBJECT
  2  (
  3     name VARCHAR2 (20),
  4     CONSTRUCTOR FUNCTION hobbit
  5        RETURN SELF AS RESULT,
  6     CONSTRUCTOR FUNCTION hobbit (name VARCHAR2)
  7        RETURN SELF AS RESULT,
  8     MEMBER FUNCTION get_name
  9        RETURN VARCHAR2,
 10     MEMBER FUNCTION set_name (name VARCHAR2)
 11        RETURN hobbit,
 12     MEMBER FUNCTION to_string
 13        RETURN VARCHAR2
 14  )
 15     INSTANTIABLE NOT FINAL;
 16  /

Type created.

SQL> /* Formatted on 2018/11/26 18:15:49 (QP5 v5.256.13226.35538) */
SQL> CREATE OR REPLACE TYPE BODY hobbit
  2  IS
  3     /* Default (no parameter) constructor. */
  4     CONSTRUCTOR FUNCTION hobbit
  5        RETURN SELF AS RESULT
  6     IS
  7        lv_hobbit   HOBBIT := hobbit ('Sam Gamgee');
  8     BEGIN
  9        self := lv_hobbit;
 10        RETURN;
 11     END hobbit;
 12
 13     /* Override signature. */
 14     CONSTRUCTOR FUNCTION hobbit (name VARCHAR2)
 15        RETURN SELF AS RESULT
 16     IS
 17     BEGIN
 18        self.name := name;
 19        RETURN;
 20     END hobbit;
 21
 22     /* Getter for the single attribute of the object type. */
 23     MEMBER FUNCTION get_name
 24        RETURN VARCHAR2
 25     IS
 26     BEGIN
 27        RETURN self.name;
 28     END get_name;
 29
 30     /* Setter for a new copy of the object type. */
 31     MEMBER FUNCTION set_name (name VARCHAR2)
 32        RETURN hobbit
 33     IS
 34        lv_hobbit   HOBBIT;
 35     BEGIN
 36        lv_hobbit := hobbit (name);
 37        RETURN lv_hobbit;
 38     END set_name;
 39
 40     /* Prints a salutation of the object type's attribute. */
 41     MEMBER FUNCTION to_string
 42        RETURN VARCHAR2
 43     IS
 44     BEGIN
 45        RETURN 'Hello ' || self.name || '!';
 46     END to_string;
 47  END;
 48  /

Type body created.

SQL> COLUMN salutation FORMAT A20
SQL>
SQL> SELECT hobbit ().to_string () AS Salutation FROM DUAL;

SALUTATION
--------------------
Hello Sam Gamgee!

SQL> SELECT hobbit ().set_name ('Bilbo Baggins').to_string () AS Salutation
  2    FROM DUAL;

SALUTATION
--------------------
Hello Bilbo Baggins!

SQL> SELECT hobbit ('Bilbo Baggins').to_string () AS Salutation from dual;

SALUTATION
--------------------
Hello Bilbo Baggins!

6. PL/SQL记录类型


SQL> /* Formatted on 2018/11/26 18:21:04 (QP5 v5.256.13226.35538) */
SQL> DECLARE
  2     -- Declare a local user-defined record structure.
  3     TYPE title_record IS RECORD
  4     (
  5     title   VARCHAR2 (60),
  6     subtitle   VARCHAR2 (60)
  7     );
  8
  9     -- Declare a variable that uses the record structure.
 10     lv_title_record   TITLE_RECORD;
 11  BEGIN
 12     -- Assign values to the record structure.
 13     lv_title_record.title := 'Star Trek';
 14     lv_title_record.subtitle := 'Into Darkness';
 15     -- Print the elements of the structure.
 16     DBMS_OUTPUT.put_line (
 17     '['
 18     || lv_title_record.title
 19     || ']'
 20     || '['
 21     || lv_title_record.subtitle
 22     || ']');
 23  END;
 24  /
[Star Trek][Into Darkness]

PL/SQL procedure successfully completed.

7. SQL集合之ADT集合

SQL> CREATE OR REPLACE TYPE string_table IS TABLE OF VARCHAR2(30);
  2  /

Type created.

SQL> desc string_table
 string_table TABLE OF VARCHAR2(30)

SQL> CREATE OR REPLACE TYPE string_varray IS VARRAY(3) OF VARCHAR2(30);
  2  /

Type created.

SQL> DESC string_varray
 string_varray VARRAY(3) OF VARCHAR2(30)

SQL> edit
Wrote file afiedt.buf

  1  DECLARE
  2     -- Declare and initialize a collection of grains.
  3     lv_string_table    STRING_TABLE
  4                           := string_table ('Corn',
  5                                            'Wheat',
  6                                            'Rye'
  7  );
  8  --,
  9                                           -- 'Barley');
 10     lv_string_varray   STRING_VARRAY;
 11  --:= string_varray ();
 12  BEGIN
 13     -- Print the first item in the array.
 14     FOR i IN 1 .. lv_string_table.COUNT
 15     LOOP
 16        lv_string_varray.EXTEND;
 17        lv_string_varray (i) := lv_string_table (i);
 18     END LOOP;
 19     FOR i IN 1 .. lv_string_varray.COUNT
 20     LOOP
 21        dbms_output.put_line(i||':'||lv_string_varray (i));
 22     END LOOP;
 23* END;
 24  /
DECLARE
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 16

SQL> edit
Wrote file afiedt.buf

  1  DECLARE
  2     -- Declare and initialize a collection of grains.
  3     lv_string_table    STRING_TABLE
  4                           := string_table ('Corn',
  5                                            'Wheat',
  6                                            'Rye'
  7  );
  8  --,
  9                                           -- 'Barley');
 10     lv_string_varray   STRING_VARRAY
 11  := string_varray ();
 12  BEGIN
 13     -- Print the first item in the array.
 14     FOR i IN 1 .. lv_string_table.COUNT
 15     LOOP
 16        lv_string_varray.EXTEND;
 17        lv_string_varray (i) := lv_string_table (i);
 18     END LOOP;
 19     FOR i IN 1 .. lv_string_varray.COUNT
 20     LOOP
 21        dbms_output.put_line(i||':'||lv_string_varray (i));
 22     END LOOP;
 23* END;
SQL> /
1:Corn
2:Wheat
3:Rye

PL/SQL procedure successfully completed.