study_note.txt.bak

介绍Oracle PL SQL编程

/
自治事务内部必须显示地COMMIT或ROLLBACK

查看数据字典中的代码：
select line, text from user_source where name = 'LOGGING_INS_ERROR';
重新编译
ALTER package/type object_name COMPILE body;

LIKE语句匹配，字符：下划线（"_"），字符串："%"

游标：
游标在OPEN是形成一个快照，数据到PGA
游标分为：显示游标，隐式游标，游标变量，游标子查询
游标的属性
%BULK_EXCEPTIONS, %BULK_ROWCOUNT, %FOUND, %ISOPEN, %NOTFOUND, %ROWCOUNT
1、显示游标
申明
CURSOR cursor_name[(parameter_list)] [RETURN return_type]
IS query
[FOR UPDATE [OF (column_list)] [NOWAIT];
打开
OPEN cursor_name[(parameter_list)]
记录获取
FETCH cursor_name INTO variable_name(s) | PL/SQL_record;
游标关闭
CLOSE cursor_name
使用LOOP遍历游标
LOOP
    FETCH....
    EXIT WHEN cursor_name%NOTFOUND;
END LOOP;

WHILE cursor_name%FOUND LOOP
    FETCH....
END LOOP;

FOR cur_variable IN cursor_name LOOP
    不用FETCH，直接使用cur_variable变量，该变量也不用申明
END LOOP;

2、隐式游标
隐式游标又Oracle自动打开和关闭，Oracle执行的每一个DML的SQL语句都会自动在PGA中占用一个上下文区域，并一次拥有一个游标。
隐式游标的名称为SQL，也可以使用游标的所有属性。

3、游标变量
游标变量的值在运行时而不是在编译时确定，并且在同一个代码块中，一个游标变量可以被多条SELECT语句打开。
CREATE OR REPLACE PROCEDURE authors_sel(cv_results IN OUT SYS_REFCURSOR)
IS
BEGIN
    OPEN cv_results FOR SELECT id, first_name, last_name FROM authors;
END;

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个元素