PL/SQL学习笔记(一)

1.基本结构块

        HEADER

        IS

                DECLARE SECTION

        BEGIN

                EXECUTION SECTION

        EXCEPTION

                EXCEPTION SECTION

        END;

 

2.匿名块

[ DECLARE

   ... declaration statements ...]

BEGIN

   ... one or more executable statements ...

[ EXCEPTION

   ... exception handler statements ...]

END;

 

 

3.所有简单和复合符号

	Semicolon: terminates declarations and statements
%	Percent sign: attribute indicator (cursor attributes like %ISOPEN and indirect declaration attributes like %ROWTYPE); also used as multi-byte wildcard symbol with the LIKE condition
_	Single underscore: single-character wildcard symbol in LIKE condition
@	At-sign: remote location indicator
:	Colon: host variable indicator, such as :block.item in Oracle Forms
**	Double asterisk: exponentiation operator
< > or != or ^= or ~=	Ways to denote the "not equal" relational operator
||	Double vertical bar: concatenation operator
<< and >>	Label delimiters
<= and >=	Less than or equal to, greater than or equal to relational operators
:=	Assignment operator
=>	Association operator for positional notation
..	Double dot: range operator
--	Double dash: single-line comment indicator
/* and */	Beginning and ending multi-line comment block delimiters

 

 

4.声明变量和常量

Declare

 name datatype [NOT NULL] [default_assignment];

name CONSTANT datatype [NOT NULL] := | DEFAULT default_value;

 

5.控制语句

IF condition1 THEN ELSIF condition2 THEN

ELSE

END IF;

CASE

    WHEN condition 1 THEN

       Execution 1

    WHEN condition 2 THEN

       Execution 2;

    ELSE

       Execution 3;

    END CASE;

 

6.循环语句

1 简单循环

BEGIN

       LOOP

          EXIT WHEN l_current_year > end_year_in;

          display_total_sales (l_current_year);

          l_current_year :=  l_current_year + 1;

       END LOOP;

    END display_multiple_years;

2 FOR LOOP 循环

BEGIN

       FOR l_current_year IN start_year_in .. end_year_in

       LOOP

          display_total_sales (l_current_year);

       END LOOP;

    END display_multiple_years;

FOR l_current_year IN (

          SELECT * FROM sales_data

           WHERE year BETWEEN start_year_in AND end_year_in)

       LOOP

          -- This procedure is now accepted a record implicitly declared

          -- to be of type sales_data%ROWTYPE...

          display_total_sales (l_current_year);

END LOOP;

3 WHILE 循环

WHILE (l_current_year <= end_year_in)

LOOP

          display_total_sales (l_current_year);

          l_current_year :=  l_current_year + 1;

END LOOP;

 

7.异常处理

Procedure  list_my_default Is

Declare

        Too_many_default Exception;--定义一个异常

Begin

        --异常嵌套

        Execution before new block

        Begin

                …;

        Exception

                When NO_DATA_FOUND THEN

                        …;

        End;

--抛出异常

        Raise Too_many_default;

Exception

        When Too_many_default then

                …;

End;

 

8. Record 类型

1 Table-based record

DECLARE

       one_book books%ROWTYPE;

2 Cursor-based record

DECLARE

   CURSOR my_books_cur IS

      SELECT * FROM books

       WHERE author LIKE '%FEUERSTEIN%';

   one_SF_book my_books_cur%ROWTYPE;

3 Programmer-defined record

DECLARE

   TYPE book_info_rt IS RECORD (

      author books.author%TYPE,

      category VARCHAR2(100),

      total_page_count POSITIVE);

   steven_as_author book_info_rt;

4 record  类型可支持行级的赋值比较操作，只需比较的两个record 有相同的参数个数和可相互转换的数据类型。

5 字段级的操作

[schema_name.][package_name.]record_name.field_name

 

9. Collections类型

Collections在数据结构上类似于一维数组或List;有三种类型的Collections：

1 Associative arrays

        无边界长度，稀疏存储，支持Varchar2和PLS_INTEGER的索引

      1  DECLARE

      2     TYPE list_of_names_t IS TABLE OF person.first_name%TYPE

      3        INDEX BY PLS_INTEGER;

      4     happyfamily   list_of_names_t;

      5     l_row PLS_INTEGER;

      6  BEGIN

      7     happyfamily (2020202020) := 'Eli';

      8     happyfamily (-15070) := 'Steven';

      9     happyfamily (-90900) := 'Chris';

     10     happyfamily (88) := 'Veva';

     11

     12     l_row := happyfamily.FIRST;

     13

     14     WHILE (l_row IS NOT NULL)

     15     LOOP

     16        DBMS_OUTPUT.put_line (happyfamily (l_row));

     17        l_row := happyfamily.NEXT (l_row);

     18     END LOOP;

     19* END;

 

2 Nested tables

REM Section A

    SQL> CREATE TYPE list_of_names_t IS TABLE OF VARCHAR2 (100);

      2  /

    Type created.

 

    REM Section B

    SQL>

      1  DECLARE

      2     happyfamily   list_of_names_t := list_of_names_t (  );

      3     children      list_of_names_t := list_of_names_t (  );

      4     parents       list_of_names_t := list_of_names_t (  );

      5  BEGIN

      6     happyfamily.EXTEND (4);

      7     happyfamily (1) := 'Eli';

      8     happyfamily (2) := 'Steven';

      9     happyfamily (3) := 'Chris';

     10     happyfamily (4) := 'Veva';

     11

     12     children.EXTEND;

     13     children (1) := 'Chris';

     14     children.EXTEND;

     15     children (2) := 'Eli';

     16

     17     parents := happyfamily MULTISET EXCEPT children;

     18

     19     FOR l_row IN parents.FIRST .. parents.LAST

     20     LOOP

     21        DBMS_OUTPUT.put_line (parents (l_row));

     22     END LOOP;

     23* END;

 

    SQL> /

    Steven

    Veva

 

 

 

 

 

3 VARRAYs 需要指定长度

REM Section A

    SQL> CREATE TYPE first_names_t IS VARRAY (2) OF VARCHAR2 (100);

      2  /

    Type created.

    SQL> CREATE TYPE child_names_t IS VARRAY (1) OF VARCHAR2 (100);

      2  /

    Type created.

    REM Section B

    SQL> CREATE TABLE family (

      2     surname VARCHAR2(1000)

      3   , parent_names first_names_t

      4   , children_names child_names_t

      5   );

    Table created.

    REM Section C

    SQL>

      1  DECLARE

      2     parents    first_names_t := first_names_t (  );--创建实例

      3     children   child_names_t := child_names_t (  );

      4  BEGIN

      5     parents.EXTEND (2);

      6     parents (1) := 'Samuel';

      7     parents (2) := 'Charina';

      8     --

      9     children.EXTEND;

     10     children (1) := 'Feather';

     11

     12     --

     13     INSERT INTO family

     14                 (surname, parent_names, children_names

     15                 )

     16          VALUES ('Assurty', parents, children

     17                 );

     18  END;

    SQL> /

    PL/SQL procedure successfully completed.

    SQL> SELECT * FROM family