study_note.txt

介绍Oracle PL SQL编程

COL first_name FORMAT A12
VARIABLE x REFCURSOR
EXEC authors_sel(:x)
PIRNT x

4、游标子查询
游标子查询（有时也被称为是嵌套游标表达式）
游标子查询在SQL的SELECT语句内部使用游标表达式，它们可以与之前定义的各种类型的游标一起使用——隐式游标除外。返回类型总是REF CURSOR。
SET SERVEROUTPUT ON ESCAPE OFF

DECLARE

   cv_author SYS_REFCURSOR;
   v_title BOOKS.TITLE%TYPE;
   v_author AUTHORS%ROWTYPE;
   v_counter PLS_INTEGER := 0;

   CURSOR book_cur
   IS
      SELECT b.title, 
         CURSOR (SELECT *
                 FROM authors a
                 WHERE a.id = b.author1
                 OR a.id = b.author2
                 OR a.id = b.author3)
      FROM books b
      WHERE isbn = '78824389';

BEGIN

   DBMS_OUTPUT.ENABLE(1000000);

   OPEN book_cur;

   LOOP
      FETCH book_cur INTO v_title, cv_author;
      EXIT WHEN book_cur%NOTFOUND;

      v_counter := 0;

      DBMS_OUTPUT.PUT_LINE('Title from the main cursor: '||v_title);

      LOOP
         FETCH cv_author INTO v_author;
         EXIT WHEN cv_author%NOTFOUND;
         
         v_counter := v_counter + 1;

         DBMS_OUTPUT.PUT_LINE('Author'||v_counter||': '
                              ||v_author.first_name||' '
                              ||v_author.last_name);
      END LOOP;
   END LOOP;

   CLOSE book_cur;

END;
/

显示可以同时打开的游标数量：
user sys:
grant select on v_$parameter to plsql;
alter system set open_cursors=10;
user plsql:
show parameter open_cursors
运行上面的游标子查询
报错：
ORA-01000: 超出打开游标的最大数
ORA-06512: 在 line 25
user sys改回去
alter system set open_cursors=300;

PL/SQL直接支持DML，但不直接只是DDL，要使用DDL必须使用动态SQL
在游标使用FOR UPDATE OF column_list时UPDATE/DELETE的WHERE语句可以使用CURRENT OF cursor_name指定当前行。

   CURSOR inventory_cur
   IS
      SELECT isbn, amount
      FROM inventory
      WHERE status = 'IN STOCK'
      AND isbn IN (SELECT isbn
                   FROM books
                   WHERE price > 40)
      FOR UPDATE OF amount;
............

      UPDATE inventory
      SET amount = v_amount
      WHERE CURRENT OF inventory_cur;


虚列ROWID和ROWNUM
ROWID是为数据库中每一条记录自动产生的一个唯一性标识
AAANKJAAEAAACTUAAp
AAANKJ    1-6位：数据库段
AAE           7-9为：数据文件号
AAACTU    10-15位：数据块号
AAp            16-18位：块行号

ROWID最大的优势就是用它引用记录时，可以获得最好的执行性能。
ROWNUM返回的值是记录的行编号（select 出来动态的行编号）

内嵌视图（Inline View）是FROM子句的子查询，在运行的时候，这个子查询像视图一样，内嵌视图并不是存储在数据库中的命名视图。

错误函数：
SQLCODE, SQLERRM


==============================
第5章 记录
==============================
记录为我们提供了定义程序设计结构体的一些方法。程序设计结构体就是不同类型数据的一个集合，这些不同类型的数据被捆绑在一起，
并作为一个整体单元进行管理。记录类型与结构体的存储定义成镜像。

在PL/SQL程序设计中，有3种定义记录类型的方法：
1、%ROWTYPE属性隐式定义
individual individuals%ROWTYPE;
2、PL/SQL程序的声明部分显示定义记录类型
TYPE individual_type IS RECORED
(
..............................
);
individual individual_type;
3、将记录类型定义为数据库结构或对象类型


==============================
第6章 集合
==============================
记录是管理单行数据所必须的结构体，而集合是管理多行数据所必须的结构体。
集合就是列表，可能有序也可能无序。有序列表的索引是唯一性的数字下标；而无序列表的索引是唯一的标识符，
这些标识符可以是数字、散列值，也可以是一些字符名。
1、联合数组（index-by表）
稀疏数组，支持唯一的数字下标和字符串下标，大小为动态分配
2、嵌套表
可以存储在永久性的数据包中，可以使用SQL进行访问，也可以进行动态扩展。数字序列，动态分配
3、varrays
密集数组，存储在永久性的表中，可以通过SQL语句访问。在创建是他们都有一个固定的大小，而且这个大小不能改变。

集合操作符：
MULTISET EXCEPT
从一个集合中删除另一个集合，类时于SQL的MINUS操作符
MULTISET INTERSECT
比较判断两个集合的值，并返回一个集合。该返回集合的元素是同时出现在这两个集合中的元素，类似于SQL的INTERSECT（交集）操作符
MULTISET UNION
该操作的功能是合并两个集合的值，返回一个集合。类似于UNION ALL操作符
可以使用DISTINCT操作符来清除集合中重复元素。DISTINCT经常跟在MULTISET INTERSECT后面，类时SQL的UNION操作符。
SET
该操作符从集合中删除重复元素，类时SQL的DISTINCT
collection_variable := SET(collection_variable);

集合类型的选择使用原则：
当集合的物理大小是静态的且集合可能要存储在表中时，一般使用varray类型的集合。
由于运行时存在的变化，集合的物理大小未知，并且集合可能要存储在表中时，一般使用嵌套表。
由于运行时存在的变化，集合的物理大小未知，但是集合肯定不会存储在表中时，一般使用联合数组

1、varray集合的使用方法
(1)定义varrays并将其用作PL/SQL的程序构造段
在PL/SQL程序单元中定义一个varray的语法如小：
TYPE type_name IS {VARRAY | VARYING VARRAY} (size_limit)
OF element_type [NO NULL];

TYPE integer_varray IS VARRAY(3) OF integer;
varray_integer INTEGER_VARRAY := integer_varray(NULL, NULL, NULL);       --定义并初始化
varray_integer INTEGER_VARRAY := integer_varray();                                      --初始化
varray变量必须初始化，否则会抛异常
API方法EXTEND：分配空间，然后再将空间分配给varray中最大数目的可能元素。
每次使用EXTEND方法分配空间以后，就立即为varray中的每个元素分配一个值。

(2)将varrays定义和用作PL/SQL中的对象类型
CREATE OR REPLACE TYPE type_name
AS {VARRAY | VARYING ARRAY} (size_limit)
OF element_type [NOT NULL]
定义varray对象类型的好处是：它可以从任何有权使用它的程序中进行引用。但是PL/SQL的varray类型的结构仅限于在该程序单元内使用。

CREATE OR REPLACE TYPE integer_varray
  AS VARRAY(100) OF INTEGER;
/

DECLARE
  -- Declare and initialize a null set of rows.
  --这个地方初始化了第一个元素
  varray_integer INTEGER_VARRAY := integer_varray(NULL);
BEGIN
  -- Loop through all records to print the varray contents.
  --因为已经初始化了第一个元素，这个地方应该是varray_integer.LIMIT - 1，不然EXTEND最后一个会报“ORA-06532: 下标超出限制”
  FOR i IN 1..varray_integer.LIMIT LOOP
    -- Initialize row.
    varray_integer.EXTEND;
  END LOOP;
    -- Print to console how many rows are initialized.
    dbms_output.put     ('Integer Varray Initialized ');
    dbms_output.put_line('['||varray_integer.COUNT||']');
END;
/

不允许为空值的情况：
CREATE OR REPLACE TYPE integer_varray
  AS VARRAY(100) OF INTEGER NOT NULL;
/

DECLARE
  -- Declare and initialize a null set of rows.
  varray_integer INTEGER_VARRAY := integer_varray();
BEGIN
  -- Loop through all records to print the varray contents.
  --在这个位置varray_integer.COUNT为0
  FOR i IN 1..varray_integer.LIMIT LOOP
    -- Initialize row.
    varray_integer.EXTEND;
  END LOOP;
    -- Print to console how many rows are initialized.
    dbms_output.put     ('Integer Varray Initialized ');
    dbms_output.put_line('['||varray_integer.COUNT||']');
END;
/

(3)将varray定义和用作表列的数据类型
(4)在数据库表中定义varrays
(5)在数据库的表中使用varrays
INSERT
INTO     addresses
VALUES
(11, 11, address_varray('Office of Senator McCain', '450 West Paseo Redondo', 'Suite 200')
,'Tucson', 'AZ', '85701', 'USA');
address_varray为构造函数名称

3、嵌套表的使用方法
嵌套表是具有Oracle 10g数据类型或用户自定义的记录/对象类型的一维结构体。
可以在表、记录和对象的定义中使用嵌套表。可以使用SQL，也可以使用PL/SQL访问嵌套表。
(1)将对象类型的嵌套表定义为PL/SQL的程序构造块
CREATE OR REPLACE TYPE type_name
AS TABLE OF element_type [NOT NULL];

(2)将嵌套表类型定义和用作PL/SQL的对象类型
CREATE OR REPLACE TYPE type_name
AS TABLE OF element_type [NOT NULL]

CREATE OR REPLACE TYPE card_table
AS TABLE OF VARCHAR2(25 CHAR) NOT NULL;
/

cards CARD_TABLE := card_table();    --这样只是定义并出示化了0个元素，以后需要用EXTEND分配空间
cards CARD_TABLE := card_table(NULL, NULL, NULL); --这样初始化了3个元素，后面如果要增加元素也用EXTEND，COUNT是已经初始化的元素个数。


14 章   对象概览
CREATE [OR REPLACE] TYPE [schema.] type_name
  [AUTHID {CURRENT_USER|DEFINER}] AS OBJECT(
  attribute1 datatype,
  [attribute2 datatype,]
  [method1]
  [method2]);
/
CURRENT_USER：是调用该方法的用户，DEFINER是该对象类型的所有者
1、属性
属性的声明与对象声明相似。但是，属性的声明有一些限制
（1）属性的声明必须出现在方法的声明之前
（2）数据类型可以是任意数据库类型，但是不能包括ROWID、UROWID、LONG、LONG RAW、
NCHAR、NCLOB、NVARCHAR2类型，以及PL/SQL的专用类型，或另一个对象类型。
（3）不能使用那些只能在PL/SQL中使用而不能在数据库中使用的数据类型。这些类型包括
BINARY_INTEGER、BOOLEAN、PLS_INTEGER、RECORD和REF CURSOR。
（4）不能使用NOT NULL约束，但是可以通过在对象的实例上定义一个数据库触发器达到类似的效果
（5）属性列表中至少必须有一个属性
（6）不能使用默认值
（7）不能直接在属性或者对象类型上使用%TYPE和%ROWTYPE，但是可以在对性实例的属性上使用他们。
2、方法
方法就是过程或函数，它们是在属性声明之后进行声明的。
[STATIC | MEMBER] PROCEDURE procedure_step,
[STATIC | MEMBER] CONSTRUCTOR] FUNCTION function_spec,
[MAP | ORDER] MEMBER FUNCTION function_spec,
pragma_declaration.

MEMBER方法
成员方法是机遇对象实例调用的，而不是机遇对象类型调用的。
STATIC方法
静态方法独立于对象实例，也不能在对象类型主体中引用这个对象的属性。
调用静态方法，可以使用这个对象类型的名称，而无需使用它实例名称。
CONSTRUCTOR 方法
构造函数一直都是系统定义的函数，函数返回一个实例化的对象，并带有一些参数，
这些参数就是相应对象的属性值。Oracle为每一个对象类型都定义了一个构造函数
这个方法的名称和属性与对象类型相同。
CREATE OR REPLACE TYPE discount_price_obj AS OBJECT (
  discount_rate   NUMBER(10, 4),
  price                 NUMBER(10, 2),
  CONSTRUCTOR FUNCTION discount_price_obj (
     price              NUMBER) RETURN SELF AS RESULT)
INSTANTIABLE
FINAL;
/

3、声明和初始化对象
如果以下面这种方式实例化对象
v_price DISCONT_PRICE_OBJ;
则v_price为NULL，引用他的任何属性都会引发异常。
应该这样实例化对象：
v_price DISCOUNT_PRICE_OBJ := discount_price_obj(null, null);

对象主体：
CREATE [OR REPLACE] TYPE BODY [schema.]type_name {IS|AS}
    [STATIC | MEMBER] PROCEDURE procedure_body
    [STATIC | MEMBER | CONSTRUCTOR] FUNCTION function_body
    [MAP | ORDER] MEMBER function_body
END;

1、SELF参数
关键字SELF引用当前对象实例，可以通过它引用当前对象的方法或属性
SELF被自动声明为MEMBER方法的第一个参数，如果没有显示的定义，
在MEMBER FUNCTION中这个参数的模式为IN
MEMBER PROCEDURE     IN OUT。不能将其声明为OUT模式，另外SELF必须与原始数据类型相同。
在用户自定义的构造函数必须在返回子句中使用RETURN SELF AS RESULT
静态方法不能使用SELF关键字。因为静态方法独立与对象实例

2、MAP和ORDER方法
MAP和ORDER方法不但可以比较两个对象，还可以将数据库中存储的对象排序。
（1）每个对象上最多只能允许有一个MAP或ORDER方法
（2）不能在同一个对象上既定义MAP方法，又定义ORDER方法
（3）MAP和ORDER可以比较程序代码中的两个对象，如果不使用他们，比较程序代码中的两个对象就会引发异常。

MAP方法：
在使用大群对象的时候，MAP方法就比ORDER方法更加有效，因为它将整个对象集转换为一种更简单的类型，然后再对这种类型进行排序。
MAP方法只带一个SELF参，返回类型是DATE、NUMBER、VARCHAR2、CHAR或REAL的标量类型。
CREATE OR REPLACE TYPE book_obj AS OBJECT (
    isbn           CHAR(10),
    title           VARCHAR2(100),
    num_page NUMBER,
    MAP MEMBER FUNCTION return_isbn RETURN CHAR
);
/

CREATE OR REPLACE TYPE BODY book_obj AS
    MAP MEMBER FUNCTION return_isbn RETURN CHAR IS
    BEGIN
        RETURN SELF.isbn;
    END return_isbn;
END;
/

ORDER方法
ORDER方法返回类型只能是NUMBER类型。
CREATE OR REPLACE TYPE book_obj AS OBJECT (
   isbn        CHAR (10),
   title       VARCHAR2 (100),
   num_pages   NUMBER,
   ORDER MEMBER FUNCTION compare_book (p_isbn IN BOOK_OBJ)
      RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY book_obj
AS
   ORDER MEMBER FUNCTION compare_book (p_isbn IN BOOK_OBJ)
      RETURN NUMBER
   IS
   BEGIN
      IF p_isbn.isbn < SELF.isbn
      THEN
         RETURN 1;
      ELSIF p_isbn.isbn > SELF.isbn
      THEN
         RETURN -1;
      ELSE
         RETURN 0;
      END IF;
   END compare_book;
END;
/

对象类型继承
NOT INSTANTIABLE该对象类型只能作为父类型或基类型使用，不能实例化
如果没有指定关键字，INSTANTIABLE就是默认值
Final就是该对象类型下面不再有子类型，没有其他对象类型会继承改对象类型。