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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-765918/