b10807（PLSQL User s Guide and Reference）.txt

判斷語句的使用例項：
-- available online in file 'examp2'
DECLARE
acct_balance NUMBER(11,2);
acct CONSTANT NUMBER(4) := 3;
debit_amt CONSTANT NUMBER(5,2) := 500.00;
BEGIN
SELECT bal INTO acct_balance FROM accounts
WHERE account_id = acct
FOR UPDATE OF bal;
IF acct_balance >= debit_amt THEN
UPDATE accounts SET bal = bal - debit_amt
WHERE account_id = acct;
ELSE
INSERT INTO temp VALUES
(acct, acct_balance, 'Insufficient funds');
-- insert account, current balance, and message
END IF;
COMMIT;
END;

-- This CASE statement performs different actions based
-- on a set of conditional tests.
CASE
WHEN shape = 'square' THEN area := side * side;
WHEN shape = 'circle' THEN
BEGIN
area := pi * (radius * radius);
DBMS_OUTPUT.PUT_LINE('Value is not exact because pi is irrational.');
END;
WHEN shape = 'rectangle' THEN area := length * width;
ELSE
BEGIN
DBMS_OUTPUT.PUT_LINE('No formula to calculate area of a' || shape);
RAISE PROGRAM_ERROR;
END;
END CASE;

-- available online in file 'examp3'
DECLARE
salary emp.sal%TYPE := 0;
mgr_num emp.mgr%TYPE;
last_name emp.ename%TYPE;
starting_empno emp.empno%TYPE := 7499;
BEGIN
SELECT mgr INTO mgr_num FROM emp
WHERE empno = starting_empno;
WHILE salary <= 2500 LOOP
SELECT sal, mgr, ename INTO salary, mgr_num, last_name
FROM emp WHERE empno = mgr_num;
Overview of PL/SQL 1-9
END LOOP;
INSERT INTO temp VALUES (NULL, salary, last_name);
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO temp VALUES (NULL, NULL, 'Not found');
COMMIT;
END;

PROCEDURE award_bonus (emp_id NUMBER) IS
bonus REAL;
comm_missing EXCEPTION;
BEGIN -- executable part starts here
SELECT comm * 0.15 INTO bonus FROM emp WHERE empno = emp_id;
IF bonus IS NULL THEN
RAISE comm_missing;
ELSE
UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id;
END IF;
EXCEPTION -- exception-handling part starts here
WHEN comm_missing THEN
...
END award_bonus;

CREATE TYPE Bank_Account AS OBJECT (
acct_number INTEGER(5),
balance REAL,
status VARCHAR2(10),
MEMBER PROCEDURE open (amount IN REAL),
MEMBER PROCEDURE verify_acct (num IN INTEGER),
MEMBER PROCEDURE close (num IN INTEGER, amount OUT REAL),
MEMBER PROCEDURE deposit (num IN INTEGER, amount IN REAL),
MEMBER PROCEDURE withdraw (num IN INTEGER, amount IN REAL),
MEMBER FUNCTION curr_bal (num IN INTEGER) RETURN REAL
);
Object Types
PL/SQL supports object-oriented programming through object types. An object type
encapsulates a data structure along with the functions and procedures needed to
manipulate the data. The variables that form. the data structure are known as
attributes. The functions and procedures that manipulate the attributes are known as
methods.
Object types reduce complexity by breaking down a large system into logical entities.
This lets you create software components that are modular, maintainable, and
reusable.
Object-type definitions, and the code for the methods, are stored in the database.
Instances of these object types can be stored in tables or used as variables inside
PL/SQL code.

 

 

=============================================
關於單引號的呼叫
To represent an apostrophe within a string, you can write two single quotes, which is
not the same as writing a double quote:
'I''m a string, you''re a string.'
Doubling the quotation marks within a complicated literal, particularly one that
represents a SQL statement, can be tricky. You can also use the following notation to
define your own delimiter characters for the literal. You choose a character that is not
present in the string, and then do not need to escape other single quotation marks
inside the literal:
-- q'!...!' notation lets us use single quotes inside the literal.
string_var := q'!I'm a string, you're a string.!';
-- To use delimiters [, {, , and ).
-- Here we pass a string literal representing a SQL statement
-- to a subprogram, without doubling the quotation marks around
-- 'INVALID'.
func_call(q'[select index_name from user_indexes where status = 'INVALID']');
-- For NCHAR and NVARCHAR2 literals, use the prefix nq instead of q.
where_clause := nq'#where col_value like '%é'#';
PL/SQL is case sensitive within string literals. For example, PL/SQL considers the
following literals to be different:
'baker'
'Baker'

試驗了一下， 用q'! xxxxxxx  !';的格式，內容中必須成對出現單引號，否則報錯。

=============================================
宣告時間時，可以採用格式：
DECLARE
d1 DATE := DATE ’1998-12-25’;
t1 TIMESTAMP := TIMESTAMP ’1997-10-22 13:01:01’;
t2 TIMESTAMP WITH TIME ZONE := TIMESTAMP ’1997-01-31 09:26:56.66 +02:00’;
-- Three years and two months
-- (For greater precision, we would use the day-to-second interval)
i1 INTERVAL YEAR TO MONTH := INTERVAL ’3-2’ YEAR TO MONTH;
-- Five days, four hours, three minutes, two and 1/100 seconds
i2 INTERVAL DAY TO SECOND := INTERVAL ’5 04:03:02.01’ DAY TO SECOND;

You can also specify whether a given interval value is YEAR TO MONTH or DAY TO
SECOND. For example, current_timestamp - current_timestamp produces a
value of type INTERVAL DAY TO SECOND by default. You can specify the type of the
interval using the formats:
■ (interval_expression) DAY TO SECOND
■ (interval_expression) YEAR TO MONTH

=================================================================================

DECLARE
-- %ROWTYPE can include all the columns in a table...
emp_rec employees%ROWTYPE;
-- ...or a subset of the columns, based on a cursor.
CURSOR c1 IS
SELECT department_id, department_name FROM departments;
dept_rec c1%ROWTYPE;
-- Could even make a %ROWTYPE with columns from multiple tables.
CURSOR c2 IS
SELECT employee_id, email, employees.manager_id, location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;//此處將一個選擇的結果作為rowtype的來源
join_rec c2%ROWTYPE;
BEGIN
-- We know EMP_REC can hold a row from the EMPLOYEES table.
SELECT * INTO emp_rec FROM employees WHERE ROWNUM < 2;
-- We can refer to the fields of EMP_REC using column names
-- from the EMPLOYEES table.
IF emp_rec.department_id = 20 AND emp_rec.last_name = 'JOHNSON' THEN
emp_rec.salary := emp_rec.salary * 1.15;
END IF;
END;
/

 

DECLARE
FUNCTION dept_name (department_id IN NUMBER)
RETURN departments.department_name%TYPE
IS
department_name departments.department_name%TYPE;
BEGIN
-- DEPT_NAME.DEPARTMENT_NAME specifies the local variable
-- instead of the table column
SELECT department_name INTO dept_name.department_name
FROM departments
WHERE department_id = dept_name.department_id;
RETURN department_name;
END;
BEGIN
FOR item IN (SELECT department_id FROM departments)
LOOP
dbms_output.put_line('Department: ' || dept_name(item.department_id));
END LOOP;
END;
/

---------------------------------------------------------------------

關於null的說明

Handling Null Values in Comparisons and Conditional Statements
When working with nulls, you can avoid some common mistakes by keeping in mind
the following rules:
■ Comparisons involving nulls always yield NULL
■ Applying the logical operator NOT to a null yields NULL
■ In conditional control statements, if the condition yields NULL, its associated
sequence of statements is not executed
■ If the expression in a simple CASE statement or CASE expression yields NULL, it
cannot be matched by using WHEN NULL. In this case, you would need to use the
searched case syntax and test WHEN expression IS NULL.

 

---------------------------
NULLs and the NOT Operator
Recall that applying the logical operator NOT to a null yields NULL. Thus, the following
two statements are not always equivalent:
IF x > y THEN  | IF NOT x > y THEN
high := x;  | high := y;
ELSE   | ELSE
high := y;  | high := x;
END IF;  | END IF;

The sequence of statements in the ELSE clause is executed when the IF condition
yields FALSE or NULL. If neither x nor y is null, both IF statements assign the same
value to high. However, if either x or y is null, the first IF statement assigns the value
of y to high, but the second IF statement assigns the value of x to high.

在語句返回false或null時執行else子句。
如果x和y都非空，兩條if語句能返回相同的最大值。
如果x或y為空，第一個if語句把y作為最大值，而第二條語句把x作為最大值

-------------------------------------------
PLSQL 嵌入函式包括以下幾類：
error reporting
number
character
datatype conversion
date
object reference
miscellaneous

Except for the error-reporting functions SQLCODE and SQLERRM, you can use all the
functions in SQL statements. Also, except for the object-reference functions DEREF,
REF, and VALUE and the miscellaneous functions DECODE, DUMP, and VSIZE, you can
use all the functions in procedural statements.

Error： 
SQLCODE
SQLERRM

Number：
ABS
ACOS
ASIN
ATAN
ATAN2
BITAND
CEIL
COS
COSH
EXP
FLOOR
LN
LOG
MOD
POWER
REMAIND
ER
ROUND
SIGN
SIN
SINH
SQRT
TAN
TANH
TRUNC

 Character：
ASCII
ASCIISTR
CHR
COMPOSE
CONCAT
DECOMPOSE
INITCAP
INSTR
INSTR2
INSTR4
INSTRB
INSTRC
LENGTH
LENGTH2
LENGTH4
LENGTHB
LENGTHC
LOWER
LPAD
LTRIM
NCHR
NLS_INITCAP
NLS_LOWER
NLSSORT
NLS_UPPER
REGEXP_INSTR
REGEXP_LIKE
REGEXP_REPLACE
REGEXP_SUBSTR
REPLACE
RPAD
RTRIM
SOUNDEX
SUBSTR
SUBSTR2
SUBSTR4
SUBSTRB
SUBSTRC
TRANSLATE
TRIM
UNISTR
UPPER

Conversion：
CHARTOROWID
CONVERT
HEXTORAW
RAWTOHEX
RAWTONHEX
ROWIDTOCHAR
TO_BINARY_DOUBLE
TO_BLOB
TO_BINARY_FLOAT
TO_CHAR
TO_CLOB
TO_DATE
TO_MULTI_BYTE
TO_NCHAR
TO_NCLOB
TO_NUMBER
TO_SINGLE_BYTE

 Date：
ADD_MONTHS
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
DBTIMEZONE
EXTRACT
FROM_TZ
LAST_DAY
LOCALTIMESTAMP
MONTHS_BETWEEN
NEW_TIME
NEXT_DAY
NUMTODSINTERVAL
NUMTOYMINTERVAL
ROUND
SESSIONTIMEZONE
SYS_EXTRACT_UTC
SYSDATE
SYSTIMESTAMP
TO_DSINTERVAL
TO_TIME
TO_TIME_TZ
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
TRUNC
TZ_OFFSET

 Obj Ref ：
DEREF
REF
TREAT
VALUE

Misc:
BFILENAME
COALESCE
DECODE
DUMP
EMPTY_BLOB
EMPTY_CLOB
GREATEST
LEAST
NANVL
NLS_CHARSET_DECL_LEN
NLS_CHARSET_ID
NLS_CHARSET_NAME
NULLIF
NVL
SYS_CONTEXT
SYS_GUID
UID
USER
USERENV
VSIZE

待讀 第三章 pl/sql datatypes
資料型別
scalar標量型別  沒有內部元件，儲存一個值，比如數字或字串
composite複合資料型別  有內部元件能夠分開操作，如陣列元素
reference參照資料型別  儲存的值成為指標，指向其他的程式單元
LOB型別儲存的值是大資料 指向大物件的位置，比如文字塊或圖片，是與其他資料分開儲存的

關於資料型別，詳見76頁圖

BINARY_INTEGER values require less storage than NUMBER values. Arithmetic
operations on BINARY_INTEGER values are also faster than NUMBER arithmetic.
BINARY_INTEGER and PLS_INTEGER both have these advantages. Because PLS_
INTEGER was faster in earlier releases, you might use it instead of BINARY_INTEGER
in code that will run on older databases.

You use the PLS_INTEGER datatype to store signed integers. Its magnitude range is
-2**31 .. 2**31. PLS_INTEGER values require less storage than NUMBER values. Also,
PLS_INTEGER operations use machine arithmetic, so they are faster than NUMBER and
BINARY_INTEGER operations, which use library arithmetic. For efficiency, use PLS_
INTEGER for all calculations that fall within its magnitude range.

When a PLS_INTEGER calculation overflows, an
exception is raised. However, when a BINARY_INTEGER calculation overflows, no
exception is raised if the result is assigned to a NUMBER variable.

If you do not specify a maximum size, it defaults to 1. If you specify the maximum size
in bytes rather than characters, a CHAR(n) variable might be too small to hold n
multibyte characters. To avoid this possibility, use the notation CHAR(n CHAR)so that
the variable can hold n characters in the database character set, even if some of those
characters contain multiple bytes.

Small VARCHAR2 variables are optimized for performance, and larger ones are
optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2
that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to
hold the actual value. For a VARCHAR2 variable that is shorter than 2000 bytes,
PL/SQL preallocates the full declared length of the variable. For example, if you
assign the same 500-byte value to a VARCHAR2(2000 BYTE) variable and to a
VARCHAR2(1999 BYTE) variable, the former takes up 500 bytes and the latter takes
up 1999 bytes.

 

Although PL/SQL character variables can be relatively long, you cannot insert
VARCHAR2 values longer than 4000 bytes into a VARCHAR2 database column.

 

You can define your own subtypes in the declarative part of any PL/SQL block,
subprogram, or package using the syntax
SUBTYPE subtype_name IS base_type[(constraint)] [NOT NULL];

第四章  using pl/sql control structures

loop中的exit和return：
Remember, the EXIT statement must be placed inside a loop. To complete a PL/SQL
block before its normal end is reached, you can use the RETURN statement

When you nest labeled loops, use ending label names to improve readability.
With either form. of EXIT statement, you can complete not only the current loop, but
any enclosing loop. Simply label the enclosing loop that you want to complete. Then,
use the label in an EXIT statement, as follows:
<>
LOOP
...
LOOP
...
EXIT outer WHEN ... -- exit both loops
END LOOP;
...
END LOOP outer;
Every enclosing loop up to and including the labeled loop is exited.

 

<>
FOR i IN 1..5 LOOP
...
FOR j IN 1..10 LOOP
FETCH c1 INTO emp_rec;
EXIT outer WHEN c1%NOTFOUND; -- exit both FOR loops
...
END LOOP;
END LOOP outer;
-- control passes here
在裡層迴圈中使用exit+外層迴圈的名稱（outer），將跳出外層迴圈
若exit，預設是提前結束裡層迴圈，而不向外擴充套件

The label end_loop in the following example is not allowed because it does not
precede an executable statement:
DECLARE
done BOOLEAN;
BEGIN
FOR i IN 1..50 LOOP
IF done THEN
GOTO end_loop;
END IF;
<> -- not allowed
END LOOP; -- not an executable statement
END;
To correct the previous example, add the NULL statement::
FOR i IN 1..50 LOOP
IF done THEN
GOTO end_loop;
END IF;
...
<>
NULL; -- an executable statement
END LOOP;

Some possible destinations of a GOTO statement are not allowed. Specifically, a GOTO
statement cannot branch into an IF statement, CASE statement, LOOP statement, or
sub-block.
For example, the following GOTO statement is not allowed:
BEGIN
GOTO update_row; -- can't branch into IF statement
IF valid THEN
<>
UPDATE emp SET ...
END IF;
END;

 

 

If you already have code or business logic that uses some other language, you can
usually translate that language's array and set types directly to PL/SQL collection
types.
■ Arrays in other languages become varrays in PL/SQL.
■ Sets and bags in other languages become nested tables in PL/SQL.
■ Hash tables and other kinds of unordered lookup tables in other languages
become associative arrays in PL/SQL.

 

集合型別的定義：
Nested Tables巢狀表
To define a PL/SQL type for nested tables, use the syntax:
TYPE type_name IS TABLE OF element_type [NOT NULL];
type_name is a type specifier used later to declare collections. For nested tables
declared within PL/SQL, element_type is any PL/SQL datatype except:REF CURSOR

Varrays變長陣列
To define a PL/SQL type for varrays, use the syntax:
TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit)
OF element_type [NOT NULL];

Associative Arrays索引表
Associative arrays (also known as index-by tables) let you insert elements using
arbitrary key values. The keys do not have to be consecutive. They use the syntax:
TYPE type_name IS TABLE OF element_type [NOT NULL]
INDEX BY [PLS_INTEGER | BINARY_INTEGER | VARCHAR2(size_limit)];
INDEX BY key_type;

例：
DECLARE
TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE
INDEX BY PLS_INTEGER;
emp_tab EmpTabTyp;
BEGIN
SELECT * INTO emp_tab(100) FROM emp WHERE empno = '7788';
dbms_output.put_line(emp_tab(100).empno);
END;
/

是將結果插入到了emp_tab(100)這個位置，而不管0~99是什麼。
實際上，我們如果執行
DECLARE
TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE
INDEX BY PLS_INTEGER;
emp_tab EmpTabTyp;
BEGIN
SELECT * INTO emp_tab(100) FROM emp WHERE empno = '7788';
dbms_output.put_line(emp_tab(1).empno);
END;
/
會得到報錯資訊，由此可以推斷，emp_tab這個集合中其他元素是“未知”，而不是null

巢狀表相當於沒有上界的一維陣列。
無需儲存

--以下通過《精通oracle 10g PLSQL程式設計》總結
索引表：
下標可以是負值，而且元素個數無限制，不能作為表列的資料型別使用
巢狀表：
下標從1開始，元素值可以是稀疏的，可以作為表列的資料型別使用
巢狀表必須先初始化，再使用
變長陣列：
下標從1開始，有最大個數限制
type type_name is varray(size_limit) of element_type [not null];

 

DECLARE
 --1.索引表
 TYPE index_table_tye IS TABLE OF emp%rowtype INDEX BY PLS_INTEGER;
 index_table index_table_typ;
 --2.巢狀表
 TYPE nest_table_typ IS TABLE OF VARCHAR2;
 nest_table nest_table_typ;
 --3.變長陣列
 TYPE var_array_typ IS VARRAY(10) OF VARCHAR2;
 var_array var_array_typ;
BEGIN
……
END;
/

集合的方法：
exists：
集合元素存在則返回true
如：if nest_table.EXISTS(1) THEN
……
索引表中可以直接使用，而巢狀表和可變陣列中需要先初始化元素，否則會報錯。

count：
返回當前集合變數中元素總個數。不包含null元素

limit：
返回集合元素的最大個數。只用於可變陣列（其他兩種沒有最大限制）

first和last：
返回第一個和最後一個元素的下標

prior和next：
返回前一個和後一個元素的下標
如：index_table.prior(4)

extend：
擴充套件集合變數尺寸，用於巢狀表和可變陣列
EXTEND（n，i）為集合變數新增n個元素，值為i

trim：
與extend相反，是從集合尾部刪除n個元素

delete：
刪除集合元素，用於巢狀表和索引表，不適用於varray
DELETE（m，n）刪除集合變數中從m到n之間的所有元素

set方法用於去掉巢狀表中的重複值
multiset union 取得兩個巢狀表的並集，其中包含重複值
multiset union distinct取得兩個巢狀表的並集，並取消重複結果
multiset intersect 取得兩個巢狀表的交集
multiset except 取得兩個巢狀表的差集

比較

檢測集合是否為null：
xxxx is null 適用於巢狀表或varray
xxxx is empty 適用於巢狀表

比較巢狀表是否相同：
=和！= 只用於巢狀表

在巢狀表上使用集合操作符
（1）cardinality返回巢狀表變數的元素個數
格式為cardinality（巢狀表名）

（2）submultiset of確定一個巢狀表是否為另一個巢狀表的子集
A  submultiset of B  結果是boolean型

（3）member of 檢測特定資料是否為巢狀表的元素
a member of A

（4）is a set 檢測巢狀表是否包含重複元素值

關於三種集合型別的選擇：
Arrays in other languages become varrays in PL/SQL.
Sets and bags in other languages become nested tables in PL/SQL.
Hash tables and other kinds of unordered lookup tables in other languages
become associative arrays in PL/SQL.

索引表和巢狀表間的選擇：
1）巢狀表可以被存到資料庫中的一行，而巢狀表（associative arrays）不能；
當在一個大表中條件一行資料時，可以使用巢狀表。

2）巢狀表更適合於相對較小的查詢表，其中的資料收集是在每次儲存過程被呼叫或包初始化時。
巢狀表優勢在於收集那些以前不知道的列的資訊，由於它沒有長度限制。
從資料庫中傳遞集合的最搞笑方法就是建立巢狀表，然後使用forall、bulk collect等語句。

巢狀表和變長陣列之間的選擇：
1）變長陣列適用範圍是：
預先知道元素的長度；
元素通常按照順序排列。

每個變長陣列都是以單個物件來儲存的，如果長度小於4KB，就儲存在表列中，如果大於4KB，就存在同一個表空間中。
必須同時更新或恢復變長陣列的所有元素。
但在對大量元素用此種方式進行更新或恢復是不切實際的。

2）巢狀表適用範圍：
索引值不連續；
沒有預定義的最大索引值限制；
需要一次性刪除或更新一些元素，但不是全部；
巢狀表資料儲存在單獨的表中，由系統生成。當訪問巢狀表時，資料庫再去連線。這使得巢狀表適合於隻影響集合中部分元素的查詢和更新操作

巢狀表的順序是不可靠的。

變長陣列、巢狀表使用時需要初始化，對未初始化的物件進行呼叫會報錯，而不會賦值為null；
例：
DECLARE
-- In the varray, we put an upper limit on the number of elements.
TYPE Colors IS VARRAY(10) OF VARCHAR2(16);
rainbow Colors;
BEGIN
-- Since COLORS is declared as VARRAY(10), we can put up to 10
-- elements in the constructor.
rainbow := Colors('Red','Orange','Yellow','Green','Blue','Indigo','Violet');
dbms_output.put_line(rainbow(2));
END;
/
SQL>

Orange

PL/SQL procedure successfully completed

DECLARE
-- In the varray, we put an upper limit on the number of elements.
TYPE Colors IS VARRAY(10) OF VARCHAR2(16);
rainbow Colors;
BEGIN
-- Since COLORS is declared as VARRAY(10), we can put up to 10
-- elements in the constructor.
rainbow := Colors('Red','Orange','Yellow','Green','Blue','Indigo','Violet');
dbms_output.put_line(rainbow(9));
END;
/

ORA-06533: ？？？？？？
ORA-06512: ？ line 9

IF my_colors IS NULL THEN
dbms_output.put_line('After initialization, the varray is null.');
ELSE
dbms_output.put_line('After initialization, the varray is not null.');
dbms_output.put_line('It has ' || my_colors.COUNT || ' elements.');
END IF;
END;
/

SQL>

Before initialization, the varray is null.
After initialization, the varray is not null.
It has 0 elements.

PL/SQL procedure successfully completed

SQL>

DECLARE
TYPE last_name_typ IS VARRAY(3) OF VARCHAR2(64);
TYPE surname_typ IS VARRAY(3) OF VARCHAR2(64);
-- These first two variables have the same datatype.
group1 last_name_typ := last_name_typ('Jones','Wong','Marceau');
group2 last_name_typ := last_name_typ('Klein','Patsos','Singh');
-- This third variable has a similar declaration, but is not the same type.
group3 surname_typ := surname_typ('Trevisi','Macleod','Marquez');
BEGIN
-- Allowed because they have the same datatype
group1 := group2;
-- Not allowed because they have different datatypes
-- group3 := group2;
END;

記住了多少？
pl/sql中的集合主要包括索引表、巢狀表和變長陣列三種型別。
索引表範圍和起始點沒有限制，適合於處理預先不知道長度的集合；
巢狀表下標從1開始，但沒有上限。巢狀表可能是不連續的，我們可以刪除中間的某個元素，使其為null（exist方法返回false）
變長陣列下標從1開始，上限固定，但可以通過extend方法來擴充套件

集合在使用前需要初始化，否則會報錯。
同種型別的集合可以做=和！=的比較，但不存在關係。
集合中中還可以巢狀集合

147頁  第五章讀完 主要講了三種集合的使用，後半段主要將其在資料庫中如何高效地插入、更改資料。

 

第六章  在pl/sql中使用sql

pl/sql中可以使用絕大多數的sql語句，除了explain plan。
在動態語句中可以執行資料定義、資料控制和會話控制語句。

遊標的屬性：
遊標屬性的值是根據最近一次sql語句執行的結果產生的。
%bulk_rowcount是為forall設計的；
%found可以用於查詢上一個語句是否查詢到了匹配的物件。如果是對null進行插入等操作會報錯，但對一個執行為空的結果集執行刪除操作就不報錯。
%isopen 判斷遊標是否是開啟的
%notfound 是%found的反義
%rowcount 顯示有多少行受到影響  如果select into返回多行，將報錯too_many_rows，但%rowcount=1

在inset或update語句中可以使用pl/sql記錄：
DECLARE
emp_rec emp%ROWTYPE;
BEGIN
emp_rec.eno := 1500;
emp_rec.ename := 'Steven Hill';
emp_rec.sal := '40000';
-- A %ROWTYPE value can fill in all the row fields.
INSERT INTO emp VALUES emp_rec;--insert的內容不需要具體制定了
-- The fields of a %ROWTYPE can completely replace the table columns.
UPDATE emp SET ROW = emp_rec WHERE eno = 100;--update的內容是記錄
END;
/

需要注意：不能在動態sql語句中使用pl/sql記錄作為繫結變數

使用bulk collect語句在時間上的是很高效的，但也是很耗記憶體的。
可以通過以下途徑改善：
如果只需要對結果集迴圈執行一次，可以使用for loop結構避免記憶體中的排序；
如果是需要對結果集進行篩選或瀏覽，應該儘量在語句中增加where、intersect、minus等；

引數遊標的例子：
BEGIN
FOR item IN
(
SELECT object_name, status FROM user_objects WHERE object_type = 'INDEX'
AND object_name NOT LIKE '%$%'
)
LOOP
dbms_output.put_line('Index = ' || item.object_name ||
', Status = ' || item.status);
END LOOP;
END;
/

當需要對同一個查詢進行多次操作時，可以顯示宣告遊標：
DECLARE
CURSOR c1 IS
SELECT object_name, status FROM user_objects WHERE object_type = 'TABLE'
AND object_name NOT LIKE '%$%';
BEGIN
FOR item IN c1 LOOP
dbms_output.put_line('Table = ' || item.object_name ||
', Status = ' || item.status);
END LOOP;
END;
/

顯式遊標相對於隱式遊標程式碼更多，但優點在於靈活。可以並行進行查詢和開啟遊標的操作，可以一次執行多行，跳過行，將一個過程分割成多個。

DECLARE
CURSOR c1 (low INTEGER DEFAULT 0,
high INTEGER DEFAULT 99) IS SELECT ...
Cursor parameters can be referenced only within the query specified in the cursor
declaration. The parameter values are used by the associated query when the cursor is
opened.

可以將同一個遊標插入不同的into列表，如：
DECLARE
CURSOR c1 IS SELECT last_name FROM employees ORDER BY last_name;
name1 employees.last_name%TYPE;
name2 employees.last_name%TYPE;
name3 employees.last_name%TYPE;
BEGIN
OPEN c1;
FETCH c1 INTO name1; -- this fetches first row
FETCH c1 INTO name2; -- this fetches second row
FETCH c1 INTO name3; -- this fetches third row
CLOSE c1;
END;
/

讀至172頁

遊標變數使用存在的限制：
不能在包宣告中宣告遊標變數
不能將遊標變數傳入由資料庫連結呼叫的儲存過程中
如果是在將本地呼叫遊標變數傳到pl/sql，不能將其插入到伺服器端，除非在服務端也開啟了相同的遊標
不能對其進行大小比較（=、！=、is null）
不能給遊標標量傳遞空值
資料庫列中無法儲存遊標變數的值。表建立時沒有響應的資料型別
不能再集合型別中儲存遊標變數
Cursors and cursor variables are not interoperable; that is, you cannot use one
where the other is expected. For example, you cannot reference a cursor variable in
a cursor FOR loop.

---------------------------------------

插播：
CREATE OR REPLACE PACKAGE plch_pkg
IS
   TYPE numbers_t IS TABLE OF NUMBER;
END;
/

DECLARE
   l_numbers   plch_pkg.numbers_t
     := plch_pkg.numbers_t (5, 4, 3, 2, 6,1);
   l_index     PLS_INTEGER;
BEGIN
   DBMS_OUTPUT.put_line ('Countdown');
 dbms_output.put_line(l_numbers.first);
   l_index := l_numbers.LAST;
   dbms_output.put_line(l_index);
   WHILE (l_index IS NOT NULL)
   LOOP
      DBMS_OUTPUT.put_line (l_numbers (l_index));
      l_index := l_numbers.PRIOR (l_index);
   END LOOP;
END;
/

集合的last方法返回的是集合的計數值，而不是最後一個元素的值
如：以上的的語句返回結果為
SQL>

Package created

Countdown
1
6
1
6
2
3
4
5

-----------------------------------------

巢狀遊標：

CURSOR c1 IS SELECT
department_name,
-- The 2nd item in the result set is another result set,
-- which is represented as a ref cursor and labelled "employees".
CURSOR
(
SELECT e.last_name FROM employees e
WHERE e.department_id = d.department_id
) employees
FROM departments d
WHERE department_name like 'A%';

關於事務完整性
The optional COMMENT clause lets you specify a comment to be associated with a
distributed transaction. If a network or machine fails during the commit, the state of
the distributed transaction might be unknown or in doubt. In that case, Oracle stores
the text specified by COMMENT in the data dictionary along with the transaction ID. The
text must be a quoted literal up to 50 characters long:
COMMIT COMMENT 'In-doubt order transaction; notify Order Entry';

事務應該顯式提交，否則出現會話結束、執行其他dcl、ddl操作，都會導致隱式提交。

The SET TRANSACTION statement must be the first SQL statement in a read-only
transaction and can only appear once in a transaction. If you set a transaction to READ
ONLY, subsequent queries see only changes committed before the transaction began.
The use of READ ONLY does not affect other users or transactions.

如下例，只讀會話隻影響子會話的資料，不影響其他使用者或會話。

 

Autonomous Transactions
自治事務：由另一個事務啟動的獨立的事務。自治事務中進行的提交回滾等操作不影響主事務。

可以使用pragma：autonomous_transaction來定義自治事務。
一般在宣告的開頭編寫自治事務的pragma

In the following example, you mark a packaged function as autonomous:
CREATE PACKAGE banking AS
...
FUNCTION balance (acct_id INTEGER) RETURN REAL;
END banking;
CREATE PACKAGE BODY banking AS
...
FUNCTION balance (acct_id INTEGER) RETURN REAL IS
PRAGMA AUTONOMOUS_TRANSACTION;
my_bal REAL;
BEGIN
...
END;
END banking;

 

限制：
不能將包中的所有子程式都宣告為自治事務；
不能將巢狀的pl/sql塊標記為自治事務；

 

和普通觸發器不同，自治的觸發器中可以包含commit和rollback語句。

巢狀事務和自治事務的比較：
自治事務與主事務不共享資源；
不依賴於主事務（如：主事務回滾了，巢狀事務也回滾，而自治事務不回滾）；
巢狀事務的提交操作需要主事務完成後才能被其他事務檢視到；而自治事務的提交操作立即可見；
自治事務中的異常導致十五級的回滾，而不是語句級回滾。

Transaction Context
The main transaction shares its context with nested routines, but not with autonomous
transactions. When one autonomous routine calls another (or itself recursively), the
routines share no transaction context. When an autonomous routine calls a
non-autonomous routine, the routines share the same transaction context.

自治事務提交後其他事務可以看到，主事務對提交是否可見取決於事務級別：
read committed級別的自治事務提交後在主事務可見
serializable級別的自治事務提交後在主事務不可見（設定方法：set transaction isolation level serializable;）\

主事務中的設定隻影響其自身，不影響自治事務

常見錯誤：
自治事務中使用主事務的資源，會發生死鎖；
初始化引數transactions指定了併發事務的最大值，包含自治事務；
如果在未提交的情況下退出自治事務，將引發異常；如果不能解決，事務將被回滾

由於自治事務相對主事務獨立，可以將其定義在觸發器中，進行日誌記錄等操作。當主事務發生問題時，日誌不會減少。
自治觸發器中還可以包含動態語句。

 

第七章   動態sql的使用

可以使用numeric,character,string literals作為繫結變數，不能使用boolean型別。
動態語句支援所有的sql資料型別，不支援pl/sql特有的資料型別，但支援pl/sql record。

使用動態sql主要作用：
1.執行ddl操作，如create、grant或會話控制語句alter session等
2.提高語句執行效率，可能根據不同的where條件產生不同的select語句
3.在進行查詢前，預先不知道列名、資料型別、數量等資訊

如果動態語句中出現了using……和into……子句，處理相同的資料，可以改寫為into……using……

如下列：
DECLARE
sql_stmt VARCHAR2(200);
plsql_block VARCHAR2(500);
emp_id NUMBER(4) := 7566;
salary NUMBER(7,2);
dept_id NUMBER(2) := 50;
dept_name VARCHAR2(14) := ’PERSONNEL’;
location VARCHAR2(13) := ’DALLAS’;
emp_rec emp%ROWTYPE;
BEGIN
EXECUTE IMMEDIATE ’CREATE TABLE bonus (id NUMBER, amt NUMBER)’;
sql_stmt := ’INSERT INTO dept VALUES (:1, :2, :3)’;
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
sql_stmt := ’SELECT * FROM emp WHERE empno = :id’;
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
plsql_block := ’BEGIN emp_pkg.raise_salary(:id, :amt); END;’;
EXECUTE IMMEDIATE plsql_block USING 7788, 500;
sql_stmt := ’UPDATE emp SET sal = 2000 WHERE empno = :1
RETURNING sal INTO :2’;
EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;
EXECUTE IMMEDIATE ’DELETE FROM dept WHERE deptno = :num’
USING dept_id;
EXECUTE IMMEDIATE ’ALTER SESSION SET SQL_TRACE TRUE’;
END;

 

DECLARE
plsql_block VARCHAR2(500);
new_deptno NUMBER(2);
new_dname VARCHAR2(14) := 'ADVERTISING';
new_loc VARCHAR2(13) := 'NEW YORK';
BEGIN
plsql_block := 'BEGIN create_dept(:a, :b, :c); END;';
EXECUTE IMMEDIATE plsql_block
USING IN OUT new_deptno, new_dname, new_loc;
IF new_deptno > 90 THEN ...
END;
/

bulk collect語句可以和以下三個結構連用：execute immediate、fetch、forall
1.You can use the RETURNING BULK COLLECT INTO clause with the EXECUTE IMMEDIATE statement to store the results of an INSERT, UPDATE, or DELETE statement in a set of collections.
2.You can use the BULK COLLECT INTO clause with the FETCH statement to store values from each column of a cursor in a separate collection.
3.You can put an EXECUTE IMMEDIATE statement with the RETURNING BULK COLLECT INTO inside a FORALL statement. You can store the results of all the INSERT, UPDATE, or DELETE statements in a set of collections.

DECLARE
TYPE NameList IS TABLE OF VARCHAR2(15);
enames NameList;
bonus_amt NUMBER := 500;
sql_stmt VARCHAR(200);
BEGIN
sql_stmt := 'UPDATE emp SET bonus = :1 RETURNING ename INTO :2';
EXECUTE IMMEDIATE sql_stmt
USING bonus_amt RETURNING BULK COLLECT INTO enames;
END;
/

DECLARE
TYPE NumList IS TABLE OF NUMBER;
TYPE NameList IS TABLE OF VARCHAR2(15);
empnos NumList;
enames NameList;
BEGIN
empnos := NumList(1,2,3,4,5);
FORALL i IN 1..5
EXECUTE IMMEDIATE
'UPDATE emp SET sal = sal * 1.1 WHERE empno = :1
RETURNING ename INTO :2'
USING empnos(i) RETURNING BULK COLLECT INTO enames;
...
END;
/

分號的使用：（語句不用，子程式用）
BEGIN
EXECUTE IMMEDIATE 'dbms_output.put_line(''No semicolon'')';
EXECUTE IMMEDIATE 'BEGIN dbms_output.put_line(''semicolons''); END;';
END;

如果動態語句中多次呼叫一個變數，using子句可以使用兩種方式：
sql_stmt := 'INSERT INTO payroll VALUES (:x, :x, :y, :x)';
EXECUTE IMMEDIATE sql_stmt USING a, a, b, a;
或EXECUTE IMMEDIATE sql_stmt USING a, b;

在動態語句中的using子句中不能直接使用null，可以用一個未初始化的變數來代替
DECLARE
a_null CHAR(1); -- set to NULL automatically at run time
BEGIN
EXECUTE IMMEDIATE 'UPDATE emp SET comm = :x' USING a_null;
END;
/

可以利用資料庫連結遠端呼叫函式：
DECLARE
emp_count INTEGER;
BEGIN
emp_count := row_count@chicago('employees');
END;
/

呼叫端的函式需要遵守控制端的規則。為了檢驗規則是否合法，可以使用pragma restrict_references 。
使用此句後，函式不會讀寫資料庫表和包中的變數。

包含增刪改動態語句的函式中違反了規則‘write no database state’（WNDS）和‘read no database state’（RNDS）。
pl/sql不能檢測到這些，由於動態sql是在執行時才檢查，而不是在編譯時。
execute immediate語句中只有into子句在編譯時檢查到違反RNDS規則

為避免死鎖，不要在程式中修改或刪除自身。（使用靜態語句時編譯就報錯了，但動態語句不會）

已讀完 第七章

 

第八章 pl/sql子程式

子程式包括儲存過程和函式，儲存過程一般進行一系列操作，函式通常計算值。
類似自治塊，子程式也有如下特點：
含有宣告部分，宣告的變數在子程式結束時終止；
執行部分
異常處理部分

儲存過程中的資料變數若未賦值，將返回空

當儲存過程中引數數量、順序或資料型別不同時，可以同名。

讀完

第十章 異常資訊的處理
相同名字的異常可以分別定義在塊中和其子塊中，兩者互不影響。
如果子塊中沒有宣告而直接呼叫母塊中的異常，將報出母塊異常的資訊。
如：
DECLARE
  past_due EXCEPTION;
  acct_num NUMBER;
BEGIN
  DECLARE ---------- sub-block begins
 /* past_due EXCEPTION; -- this declaration prevails*/
  acct_num NUMBER;
  due_date DATE := SYSDATE - 1;
  todays_date DATE := SYSDATE;
  BEGIN
  IF due_date < todays_date THEN
  RAISE past_due; -- this is not handled
  END IF;
  END; ------------- sub-block ends
EXCEPTION
  WHEN past_due THEN -- does not handle RAISEd exception
  dbms_output.put_line('Handling PAST_DUE exception.');
  WHEN OTHERS THEN
  dbms_output.put_line('Could not recognize PAST_DUE_EXCEPTION in this
  scope.');
END;
/

Handling PAST_DUE exception.

PL/SQL procedure successfully completed

SQL>

將子塊中異常定義啟用，則結果為：
Could not recognize PAST_DUE_EXCEPTION in this
  scope.

PL/SQL procedure successfully completed

 

例外的使用：
DECLARE
out_of_stock EXCEPTION;
number_on_hand NUMBER := 0;
BEGIN
IF number_on_hand < 1 THEN
RAISE out_of_stock; -- raise an exception that we defined
END IF;
EXCEPTION
WHEN out_of_stock THEN
-- handle the error
dbms_output.put_line('Encountered out-of-stock error.');
END;
/

由於出現異常後會跳出當前的塊，一般就結束整個儲存過程了。
我們可以通過在子塊中呼叫異常的方式，並在異常部分進行適當處理，使母塊中的操作繼續進行。如：
DECLARE
pe_ratio NUMBER(3,1);
BEGIN
DELETE FROM stats WHERE symbol = 'XYZ';
BEGIN ---------- sub-block begins
SELECT price / NVL(earnings, 0) INTO pe_ratio FROM stocks
WHERE symbol = 'XYZ';
EXCEPTION
WHEN ZERO_DIVIDE THEN
pe_ratio := 0;
END; ---------- sub-block ends
INSERT INTO stats (symbol, ratio) VALUES ('XYZ', pe_ratio);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/

 

自定義的異常名稱不能和預定義異常名稱相同。

1.異常名稱需要在宣告部分說明，若在異常處理部分是直接輸出，可以不指定異常編號等，如：
DECLARE
past_due EXCEPTION;
acct_num NUMBER;
BEGIN
IF '1' < '2' THEN
RAISE past_due;
END IF;
EXCEPTION
WHEN past_due THEN -- does not handle RAISEd exception
dbms_output.put_line('Handling PAST_DUE exception.');
WHEN OTHERS THEN
dbms_output.put_line('Could not recognize PAST_DUE_EXCEPTION in this
scope.');
END;
/

2.如果需要指定異常的編號等資訊，宣告時還要指定編號。如：
DECLARE
deadlock_detected EXCEPTION;
PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
BEGIN
null; -- Some operation that causes an ORA-00060 error
EXCEPTION
WHEN deadlock_detected THEN
null; -- handle the error
END;
/

3.自定義錯誤提示資訊
使用raise_application_Error：
raise_application_error(error_number, message[, {TRUE | FALSE}]);
解釋：error_number is a negative integer in the range -20000 .. -20999 and message
is a character string up to 2048 bytes long. If the optional third parameter is TRUE, the
error is placed on the stack of previous errors.
如：
DECLARE
num_tables NUMBER;
BEGIN
SELECT COUNT(*) INTO num_tables FROM USER_TABLES;
IF num_tables < 1000 THEN
/* Issue your own error code (ORA-20101) with your own error message. */
raise_application_error(-20101, 'Expecting at least 1000 tables');
ELSE
NULL; -- Do the rest of the processing (for the non-error case).
END IF;
END;
/

內部異常和使用者自定義的異常是隱式觸發的，而其他的使用者定義異常需要用raise語句顯式觸發。
如：
DECLARE
out_of_stock EXCEPTION;
number_on_hand NUMBER := 0;
BEGIN
IF number_on_hand < 1 THEN
RAISE out_of_stock; -- raise an exception that we defined
END IF;
EXCEPTION
WHEN out_of_stock THEN
-- handle the error
dbms_output.put_line('Encountered out-of-stock error.');
END;
/

如果內層的異常沒有被捕獲，將擴散到外層，如果最外層都沒有異常處理語句，將報出oracle給的錯誤提示。

異常處理部分，可以使用insert、update等語句將相應的資訊記錄到表中

另外，在宣告部分出現的異常不能被有效捕獲，將報出oracle給的錯誤資訊。

使用goto語句可以跳到異常部分，但從異常部分無法跳出來。

若發生異常時想要程式繼續進行，可以通過程式拆分，將異常定義到子程式的異常處理部分，使子程式能夠返回一個可處理的值，主程式正常執行。如：
DECLARE
pe_ratio NUMBER(3,1);
BEGIN
DELETE FROM stats WHERE symbol = 'XYZ';
BEGIN ---------- sub-block begins
SELECT price / NVL(earnings, 0) INTO pe_ratio FROM stocks
WHERE symbol = 'XYZ';
EXCEPTION
WHEN ZERO_DIVIDE THEN
pe_ratio := 0;
END; ---------- sub-block ends
INSERT INTO stats (symbol, ratio) VALUES ('XYZ', pe_ratio);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/

事務的重試：當出現異常時，可以重複執行幾次。方法為：
將事務寫入到子塊中；
將子塊放入loop迴圈中，替代事務；
在開始事務之前儲存檢查點，如果事務成功執行並提交，則繼續；如果事務異常，返回到檢查點。

DECLARE
name VARCHAR2(20);
ans1 VARCHAR2(3);
ans2 VARCHAR2(3);
ans3 VARCHAR2(3);
suffix NUMBER := 1;
BEGIN
FOR i IN 1..10 LOOP -- try 10 times
BEGIN -- sub-block begins
SAVEPOINT start_transaction; -- mark a savepoint
/* Remove rows from a table of survey results. */
DELETE FROM results WHERE answer1 = 'NO';
/* Add a survey respondent's name and answers. */
INSERT INTO results VALUES (name, ans1, ans2, ans3);
-- raises DUP_VAL_ON_INDEX if two respondents have the same name
COMMIT;
EXIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK TO start_transaction; -- undo changes
suffix := suffix + 1; -- try to fix problem
name := name || TO_CHAR(suffix);
END; -- sub-block ends
END LOOP;
END;
/

oracle的編譯警告：
為了使程式碼更加強壯，可以開啟警告檢查。
為了檢視pl/sql警告資訊，可以使用plsql_warnings初始化引數、dbms_warning包和user/dba/all_plsql_object_settings檢視

pl/sql警告分為以下幾類：
Service伺服器問題 如同義詞引數
Performance效能問題  如insert語句中將一個varchar2資料傳遞到number列
Informational訊息型 對於效能和正確性沒有影響，但可能需要修改程式碼，使其更易管理。例如不可能指定到的死程式碼。

通過設定plsql_warnings引數，可以允許或禁止警告的分類、特定資訊程式碼，將特定的警告作為錯誤進行處理等。
此引數可以在系統級和會話級設定。
如：
ALTER SYSTEM SET PLSQL_WARNINGS='ENABLE:ALL'; -- For debugging during development.
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:PERFORMANCE'; -- To focus on one aspect.
ALTER PROCEDURE hello COMPILE PLSQL_WARNINGS='ENABLE:PERFORMANCE'; -- Recompile with extra checking.
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL'; -- To turn off all warnings.
-- We want to hear about 'severe' warnings, don't want to hear about 'performance'
-- warnings, and want PLW-06002 warnings to produce errors that halt compilation.
ALTER SESSION SET
PLSQL_WARNINGS='ENABLE:SEVERE','DISABLE:PERFORMANCE','ERROR:06002';

使用dbms_warning包可以控制警告資訊

CREATE OR REPLACE PROCEDURE dead_code
AS
x number := 10;
BEGIN
if x = 10 then
x := 20;
else
x := 100; -- dead code (never reached)
end if;
END dead_code;
/
-- By default, the preceding procedure compiles with no errors or warnings.
-- Now enable all warning messages, just for this session.
CALL DBMS_WARNING.SET_WARNING_SETTING_STRING('ENABLE:ALL' ,'SESSION');
-- Check the current warning setting.
select dbms_warning.get_warning_setting_string() from dual;
-- When we recompile the procedure, we will see a warning about the dead code.
ALTER PROCEDURE dead_code COMPILE;

********************************************************************************

說明：在開發環境中應該開啟警告提示的功能，以便發現不合理的程式碼，儘早改正；
而在生產環境下，為了保證系統最大限度的可用，應該關閉警告。

按照pl/sql reference中的方法進行實驗：

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott
//注：9i中是沒有這些設定的

SQL> set serveroutput on;

SQL> select dbms_warning.get_warning_setting_string from dual;

GET_WARNING_SETTING_STRING
--------------------------------------------------------------------------------
DISABLE:ALL

 

CREATE OR REPLACE PROCEDURE dead_code
AS
x number := 10;
BEGIN
if x = 10 then
x := 20;
elsif 10=20 then
x:=200;
else
x := 100; -- dead code (never reached)
end if;
END dead_code;
/

SQL>

Procedure created

SQL> show error
No errors for PROCEDURE SCOTT.DEAD_CODE

可以看到，在警告資訊提示關閉的情況下，沒有提示
接下來開啟警告提示：
SQL> call dbms_warning.set_warning_setting_string('ENABLE:ALL','SESSION');

Method called

SQL> select dbms_warning.get_Warning_setting_string() from dual;

DBMS_WARNING.GET_WARNING_SETTI
--------------------------------------------------------------------------------
ENABLE:ALL

SQL> alter procedure dead_code compile;

Procedure altered

SQL> show error
Errors for PROCEDURE SCOTT.DEAD_CODE:

LINE/COL ERROR
-------- ----------------------------------------------------------------------
1/1      PLW-05018: ？？ DEAD_CODE ？？？？？？ AUTHID ？？; ？？？？？ DEFINER
7/9      PLW-06002: ？？？？？？？

可以看到出現了警告資訊

由於我們設定的是在當前會話中生效，關閉後仍恢復原樣。

******************************************************************************

第十一章 pl/sql的效能調優

10g之前，pl/sql編譯器將使用者的程式碼翻譯為機器語言，不會進行調優。
現在，pl/sql優化了編譯器，可以重組程式碼以提高效能。

這個特性自動開啟。極少情況下，編譯非常大的應用會花費很長時間，這時可以通過初始化引數plsql_optimize_level由預設的2改為1來降低優化。
更壞的情況，可以將該引數調整為0，不進行程式碼重排。

SQL> show parameter plsql_optimize

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
plsql_optimize_level                 integer     2

 

一、什麼時候需要調優
做大量數學計算的程式，需要檢查pls_integer\binary_float\binary_double資料型別；

由pl/sql呼叫的函式（可能需要被執行上萬次）。

需要花費大量時間進行DML增刪改操作的程式

可能不支援新特性的舊程式碼

嵌入式的程式碼

二、避免效能問題的關注點
通常包括sql語句太差、程式執行慢、未關注pl/sql基礎、共享記憶體的誤用

1.避免cpu過載

保證sql語句儘可能高效：
1)有必要的索引
2)有最新的統計資訊
3)分析執行計劃  explain plan語句、sql trace工具、oracle的跟蹤工具
4)使用forall、bulk collect等批量處理語句

保證函式呼叫高效：
1)如果在sql查詢中包含了函式呼叫，可以通過建立函式索引並將函式值快取的方式提高效率。雖然建立索引花費了一定時間，但查詢更快了。
2)如果sql查詢的列被傳遞給函式，此列上的查詢不能使用常規索引，每次呼叫一行時都要呼叫一個表（可能很大）。
可以考慮巢狀查詢，在內層產生一個較小的結果集，外層只處理函式。

書中出現瞭如下的例子：
BEGIN
-- Inefficient, calls my_function for every row.
FOR item IN (SELECT DISTINCT(SQRT(department_id)) col_alias FROM employees)
LOOP
dbms_output.put_line(item.col_alias);
END LOOP;
-- Efficient, only calls function once for each distinct value.
FOR item IN
( SELECT SQRT(department_id) col_alias FROM
( SELECT DISTINCT department_id FROM employees)
)
LOOP
dbms_output.put_line(item.col_alias);
END LOOP;
END;
/

實驗如下：
SQL> conn scott/scott@testdb_192.168.56.130
已連線。
SQL> set autotrace on
SQL> alter system flush shared_pool
  2  ;

系統已更改。

SQL> SELECT DISTINCT(SQRT(deptno)) col_alias FROM emp;

 COL_ALIAS
----------
3.16227766
5.47722558
4.47213595

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=4 Card=3 Bytes=9)
   1    0   HASH (UNIQUE) (Cost=4 Card=3 Bytes=9)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=15 Byt
          es=45)

 

 

Statistics
----------------------------------------------------------
        242  recursive calls
          0  db block gets
         32  consistent gets
          0  physical reads
          0  redo size
        418  bytes sent via SQL*Net to client
        495  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          3  rows processed

SQL> alter system flush shared_pool;

系統已更改。

SQL> SELECT SQRT(deptno) col_alias FROM ( SELECT DISTINCT deptno FROM emp);

 COL_ALIAS
----------
5.47722558
4.47213595
3.16227766

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=4 Card=3 Bytes=39)
   1    0   VIEW (Cost=4 Card=3 Bytes=39)
   2    1     HASH (UNIQUE) (Cost=4 Card=3 Bytes=9)
   3    2       TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=15 B
          ytes=45)

 

 

Statistics
----------------------------------------------------------
        242  recursive calls
          0  db block gets
         32  consistent gets
          0  physical reads
          0  redo size
        418  bytes sent via SQL*Net to client
        495  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          3  rows processed

保證loop高效：
1）將初始化和計算放到迴圈外
2）如果處理一條語句，用forall替換loop
3）用bulk collect將查詢結果一次放入記憶體
4）如果要在迴圈中執行多次查詢，嘗試用union、intersect、minus等連線為一條語句
5）使用子查詢

使用oracle內部函式，如replace、instr、regexp_substr，而不是自定義的函式

重排語句的連線順序，將不昂貴的操作放在開頭（put the least expensive first）

最小化資料型別的轉換
在語句中儘量不要進行資料轉換，如果迫不得已，最好使用顯式轉換。如下說明：
DECLARE
n NUMBER;
c CHAR(5);
BEGIN
n := n + 15; -- converted implicitly; slow
n := n + 15.0; -- not converted; fast
c := 25; -- converted implicitly; slow
c := TO_CHAR(25); -- converted explicitly; still slow
c := '25'; -- not converted; fast
END;
/

使用pls_integer或binary_integer進行整數運算
pls_integer比integer、number佔用空間小，且運算高效（PLS_INTEGER values require less storage than
INTEGER or NUMBER values, and PLS_INTEGER operations use machine arithmetic.）

Avoid constrained subtypes such as INTEGER, NATURAL, NATURALN, POSITIVE,
POSITIVEN, and SIGNTYPE in performance-critical code. Variables of these types
require extra checking at run time, each time they are used in a calculation

在浮點運算中使用binary_float和binary_double，比number高效且省空間

2.避免記憶體過載
1）宣告varchar2變數時，分配多一點兒。在賦值時才會分配。
2）將相關子程式放入包中，避免額外磁碟I/O
可以利用dbms_shared_pool包，將經常使用的包永久放入共享記憶體池，使其不被LRU演算法剔除

 

pl/sql程式的跟蹤
1.使用profiler API：dbms_profiler包
用於收集和儲存執行時間資料。資訊存入資料庫表。
使用方法：
執行profiler，開啟跟蹤會話，執行應用足夠長時間來獲取充足程式碼覆蓋範圍，清空資料庫中收集的資料，停止跟蹤會話。
分析收集的效能資料，找到瓶頸並提高效能

2.使用profiler API：dbms_trace包

 

If the DML statement affects four or more database rows, the use of bulk SQL can improve performance considerably.
sql語句傳給pl.sql的變數叫做繫結變數。包括三類：
in-bind ：pl/sql變數或本地變數通過insert或update存到資料庫中；
out-bind ：通過insert、updatge、delete語句的returning將變數傳遞給pl/sql
define：通過select或fetch語句傳遞給pl/sql

bulk sql使用pl/sql集合將大量資料傳給pl/sql。這個過程叫做bulk binding。

關於forall、indices of的用法，官方文件中給出瞭如下例子：

-- Create empty tables to hold order details
CREATE TABLE valid_orders (cust_name VARCHAR2(32), amount NUMBER(10,2));
CREATE TABLE big_orders AS SELECT * FROM valid_orders WHERE 1 = 0;
CREATE TABLE rejected_orders AS SELECT * FROM valid_orders WHERE 1 = 0;
DECLARE
-- Make collections to hold a set of customer names and order amounts.
SUBTYPE cust_name IS valid_orders.cust_name%TYPE;
TYPE cust_typ IS TABLe OF cust_name;
cust_tab cust_typ;
SUBTYPE order_amount IS valid_orders.amount%TYPE;
TYPE amount_typ IS TABLE OF NUMBER;
amount_tab amount_typ;
-- Make other collections to point into the CUST_TAB collection.
TYPE index_pointer_t IS TABLE OF PLS_INTEGER;
big_order_tab index_pointer_t := index_pointer_t();
rejected_order_tab index_pointer_t := index_pointer_t();
PROCEDURE setup_data IS BEGIN
-- Set up sample order data, including some invalid orders and some 'big'
orders.
cust_tab := cust_typ('Company 1','Company 2','Company 3','Company 4',
'Company 5');
amount_tab := amount_typ(5000.01, 0, 150.25, 4000.00, NULL);
END;
BEGIN
setup_data();
dbms_output.put_line('--- Original order data ---');
FOR i IN 1..cust_tab.LAST LOOP
dbms_output.put_line('Customer #' || i || ', ' || cust_tab(i) || ': $' ||
amount_tab(i));
END LOOP;
-- Delete invalid orders (where amount is null or 0).
FOR i IN 1..cust_tab.LAST LOOP
IF amount_tab(i) is null or amount_tab(i) = 0 THEN
cust_tab.delete(i);
amount_tab.delete(i);
END IF;
END LOOP;
dbms_output.put_line('--- Data with invalid orders deleted ---');
FOR i IN 1..cust_tab.LAST LOOP
IF cust_tab.EXISTS(i) THEN
dbms_output.put_line('Customer #' || i || ', ' || cust_tab(i) || ': $' ||
amount_tab(i));
END IF;
END LOOP;
-- Since the subscripts of our collections are not consecutive, we use
-- FORALL...INDICES OF to iterate through the actual subscripts, rather than
1..COUNT.
FORALL i IN INDICES OF cust_tab
INSERT INTO valid_orders(cust_name, amount) VALUES(cust_tab(i),
amount_tab(i));
-- Now let's process the order data differently. We'll extract 2 subsets
-- and store each subset in a different table.
setup_data(); -- Initialize the CUST_TAB and AMOUNT_TAB collections again.
FOR i IN cust_tab.FIRST .. cust_tab.LAST LOOP
IF amount_tab(i) IS NULL OR amount_tab(i) = 0 THEN
rejected_order_tab.EXTEND; -- Add a new element to this collection.
rejected_order_tab(rejected_order_tab.LAST) := i; -- And record the
subscript. from the original collection.
END IF;
IF amount_tab(i) > 2000 THEN
big_order_tab.EXTEND; -- Add a new element to this collection.
big_order_tab(big_order_tab.LAST) := i; -- And record the subscript. from
the original collection.
END IF;
END LOOP;
-- Now it's easy to run one DML statement on one subset of elements, and another
DML statement on a different subset.
FORALL i IN VALUES OF rejected_order_tab
INSERT INTO rejected_orders VALUES (cust_tab(i), amount_tab(i));
FORALL i IN VALUES OF big_order_tab
INSERT INTO big_orders VALUES (cust_tab(i), amount_tab(i));
COMMIT;
END;
/
-- Verify that the correct order details were stored.
SELECT cust_name "Customer", amount "Valid order amount" FROM valid_orders;
SELECT cust_name "Customer", amount "Big order amount" FROM big_orders;
SELECT cust_name "Customer", amount "Rejected order amount" FROM rejected_orders;
DROP TABLE valid_orders;
DROP TABLE big_orders;
DROP TABLE rejected_orders;

用in indices of前先對巢狀表中的資料進行了清理，只保留需要的資料，這時候巢狀表中的序列號是不連續的，使用forall xx in indices of xxx，可以只對含有的資料進行新增，已經刪除的序列號就不會處理了。

對於insert語句 %bulk_rowcount=1，由於每次只插入一行資料
insert …… select結構，%bulk_rowcount返回的是插入的行數
%rowcount返回總共插入的行數

forall語句也是可以進行錯誤處理的。當某一天資料出錯時，使用forall的save exceptions選項可以繼續執行而不報錯，用%bulk_exceptions可以檢視錯誤。

讀至275頁

在儲存過程中定義的引數，可以指定nocopy。預設呼叫時，入參和出參是在子程式執行之前複製的。在子程式執行過程中，臨時變數儲存引數輸出。
如果子程式正常退出，這些值就被賦給當前引數；如果子程式出錯退出，原引數不變。
當引數代表了龐大的資料結構，如集合、記錄、物件例項時，這個備份延緩了執行，耗用記憶體。

By default, OUT and IN OUT parameters are passed by value. The values of any IN
OUT parameters are copied before the subprogram is executed. During subprogram
execution, temporary variables hold the output parameter values. If the subprogram
exits normally, these values are copied to the actual parameters. If the subprogram
exits with an unhandled exception, the original parameters are unchanged.
When the parameters represent large data structures such as collections, records, and
instances of object types, this copying slows down execution and uses up memory. In
particular, this overhead applies to each call to an object method: temporary copies are
made of all the attributes, so that any changes made by the method are only applied if
the method exits normally.

To avoid this overhead, you can specify the NOCOPY hint, which allows the PL/SQL
compiler to pass OUT and IN OUT parameters by reference. If the subprogram exits
normally, the behavior. is the same as normal. If the subprogram exits early with an
exception, the values of OUT and IN OUT parameters (or object attributes) might still
change. To use this technique, ensure that the subprogram handles all exceptions.

為了避免這種情況，可以指定nocopy，允許pl/sql編譯器傳遞引數。子程式正常退出時，行為和正常時相同。如果子程式出錯，入參和出參可能也會改變。
使用這種技術確保了子程式傳遞出所有的異常資訊。

下面的過程載入了25000條記錄，傳到兩個什麼都不做的儲存過程中。使用nocopy的儲存過程花費時間更少。

DECLARE
TYPE EmpTabTyp IS TABLE OF employees%ROWTYPE;
emp_tab EmpTabTyp := EmpTabTyp(NULL); -- initialize
t1 NUMBER;
t2 NUMBER;
t3 NUMBER;
PROCEDURE get_time (t OUT NUMBER) IS
BEGIN t := dbms_utility.get_time; END;
PROCEDURE do_nothing1 (tab IN OUT EmpTabTyp) IS
BEGIN NULL; END;
PROCEDURE do_nothing2 (tab IN OUT NOCOPY EmpTabTyp) IS
BEGIN NULL; END;
BEGIN
SELECT * INTO emp_tab(1) FROM employees WHERE employee_id = 100;
emp_tab.EXTEND(49999, 1); -- copy element 1 into 2..50000
get_time(t1);
do_nothing1(emp_tab); -- pass IN OUT parameter
get_time(t2);
do_nothing2(emp_tab); -- pass IN OUT NOCOPY parameter
get_time(t3);
dbms_output.put_line('Call Duration (secs)');
dbms_output.put_line('--------------------');
dbms_output.put_line('Just IN OUT: ' || TO_CHAR((t2 - t1)/100.0));
dbms_output.put_line('With NOCOPY: ' || TO_CHAR((t3 - t2))/100.0);
END;
/

使用nocopy的限制：
使用此引數增加了引數混淆的可能性。
以下情況下，程式會傳遞值而不是引數，但不報錯：
（1）當前引數是索引表associative array的一個元素。如果引數時整個的索引表將沒有此限制。
（2）當前引數受限制，如範圍或非空。此限制不適用長度受限的字串；不包含限制元素或混合資料型別。
（3）當前和前面的引數時記錄，一個或兩個記錄是以%type或%rowtype宣告的，在同一個欄位限制不同。
（4）當前的和上一個引數是記錄，當前引數被飲食宣告為for loop的序列，在同一個欄位限制不同。
（5）傳遞當前引數需要隱式資料型別轉換
（6）子程式是通過資料庫連結或外部儲存過程呼叫的。

 

 

將pl/sql程式碼編譯為本地執行

可以通過將pl/sql儲存過程編譯為原生程式碼放入共享池，使儲存過程被編譯為C程式碼，並編譯到當前使用的C編譯器中，連線到oracle。
通過此技術可以使編譯的儲存過程在所有伺服器環境都可用。
第一次使用前，需要在測試伺服器測試後再連線生產。
配置pl/sql本地應用前需要備份資料庫。
確認c編譯器的安裝位置，並確認￥oracle_home/plsql/spnc_commands下正確的存放。一般來講，不需要做修改，只要確認步驟正確就可以。
本地編譯為計算量大的程式提供了最大的效能提升。比如在資料倉儲中應用伺服器端事務，最高可提高30%的速度。

如果決定通過本地編譯pl/sql來提高效能，oracle建議整個資料庫都是用本地編譯。

 

表函式的使用
表函式能夠產生一個集合，這個集合可以像物理表那樣查詢或放入pl/sql集合變數中。
可以在from子句或select列表中使用表函式。

表函式返回的行集可以被管道化。流控制、管道化和並行執行的表函式能夠提高效能。

 

第十二章 pl/sql物件型別

物件導向的程式設計很適合與建立可重用的元件和複雜應用。
pl/sql的物件型別在用於和java或其他物件導向的語言互動時非常有用。

物件型別將大系統劃分為了邏輯單元。使建立的軟體元件模組化、可維護、可重用並能夠支援團隊使用。

物件型別中必須有屬性，方法是可選的。
屬性的資料型別可以是除long、long raw、rowid、urowid及pl/sql特有資料型別（binary_integer等）之外的其他資料型別。
屬性不能初始化為非空、預設值等，由於物件型別最後用在資料庫表中，可以在表上定義這些約束。

物件宣告中所有屬性要在方法使用前進行說明。如果物件型別中只有屬性，就不需要物件體了。
不能再物件體中宣告屬性。
所有在物件宣告中的宣告都對外可見。

在c和java程式中可以呼叫oracle的物件型別方法。

讀至299頁  需要了解物件資料型別的具體使用方法，不光是讀

關於type的定義和使用，未深入研究。可以參考F:\Oracle_knowledge\sql_plsql\文件\已讀[itpub.net]PLSQL筆記(EFRIOO@Hotmail.com).doc

第十三章  pl/sql語句的元素

關於自治事務：不能和主事務分享資源，結束後需要提交，否則將報錯或回滾。

宣告自治事務的方法：宣告部分pragma autonomous_transaction;

 

函式中，nocopy選項可以使pl/sql編譯時傳遞引數，而不是傳遞值。這樣函式執行更快，但如果函式中有未處理的異常，會影響結果。

在包中定義的函式，如果在包宣告中未說明，則只能供該包內的儲存過程呼叫。外部不可見。

系統提供了timestamp_to_scn、scn_to_timestamp函式，用於得到scn或timestamp。

 

附錄

需要注意char的使用
比如：
name1 VARCHAR2(10) := 'STAUB';
name2 CHAR(10) := 'STAUB';
兩個字串是不相等的，由於name2長度是10，後面隱含著空位。
當用name2和字面量STAUB比較時，是相等的。

資料加密：
可以使程式碼不在user_source\all_source\dba_source中顯示。

當對包進行加密時，只是包體加密了，包宣告不加密。

sql和pl/sql在名稱查詢順序上有差異：
如scott.foo
sql的匹配順序是：現在scott中找物件，然後再到當前schema中找包、型別、表、檢視。
pl/sql中，首先在當前schema中找叫做scott的包、型別、表、檢視，然後再去scott中找。

在通常使用的nls_sort引數後面加“_CI”，可以是查詢變為大小寫敏感。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26451536/viewspace-765918/，如需轉載，請註明出處，否則將追究法律責任。