1 明细类的命名
约定是<父类名> _ D T L
概化类的命名
概化类是其他类的概化如果E M P L O Y E E是领薪水的雇员和按小时工作的雇员的概化，那么恰当的名字是E M P、E M P _ S A L A R I E D、D E M P _ H O U R LY，或者是这些词的恰当的缩写
2 值列表类
如果一个类满足以下一项或几项标准，我们就说它是一个值列表类：
1) 组成这个类的项不代表真实世界中任何特定的对象，如性别和三原色。
2) 这个类由一些合法值组成，而这些合法值实际上是一些选择项。如某个给定汽车制造
商所生产汽车的有效颜色。
3) 组成这个类的值完1全是系统外部的值，但代表的真实事物是可变的。我们可能只对这
些值的一部分感兴趣，如国家、州、省、邮政编码、电话局。
4) 该类由人为的、内部的值分组组成，且这些分组是相对稳定的。如地理区域，又例如
一个零售商店将某个外衣型号指定为“ S p r i n g”或“F a l l”。
值列表类的属性
■ 代码
■ 值名称
■ A C T I V E _ Y N
■ 序号，用于提供值的逻辑排序序号。
3 过载值列表类
可以用一个单域验证来验证不同对象类的多个属性值。这在状态表中经常出现
4 递归值列表类
许多情况下，你需要对带有域参照的某种对象类型进行分类。例如，所有工程（ P r o j e c t）
项目都有特定的类型，工程类型（ Project Ty p e）本身也可能要分类
5 非规范化技术概述
冗余总额域这将使代码变得不稳定，因此不推荐使用这种方法
通过冗余总额域来实现非规范化使你能够看到总额列，而无需在集合中执行连接。另一
个优点是可以对它进行索引。如果想要返还某个额度内的购货单或检索最大或最小定单，这
个方法是很有用的。避免全表扫描和计算每一个购货单的明细的唯一方法是创建一个冗余列
6 Project Build & Make
Build: The project items will just simply be compiled in the order.
Make: The project items which have been changed since they were compiled previously will be compiled, or the items are invalid.
7 Recalling SQL statement
Ctrl + E ► recalling statement
Ctrl + Shift + # ► set bookmark
Ctrl + # ► goto bookmarks
Alt + Shift + K ► cleanup bookmarks
Shift + F3 ► next bookmark
Shift + Alt + F3 ► previous bookmarks
Alt + T ► test procedure/function
8 code content
safari
9 Naming convention
Name Length:
Name could be as long as 30 characters
Global variable:
gn_, gs_
Collection, Record, Ref Cursor:
use prefix, suffix ( _t)
Because of ignorance of case-sensitive in PLSQL, so instead of using separators, underscore
Name can be only as long as 30 characters
Author:
Access information
Overview
Dependencies
Exceptions
Modification History
You are best off defining this header after the IS or AS keyword in your program, because it will put hem in USER_SOURCE
is
/*
… header text
*/
Name procedure with verb phrases
Don’t hard-coded VARCHAR2 length
SUBTYPE max_varchar_st is VARCHAR2(2000);
exceptionMessage max_varchar2_st;
Perform complex variable initialization in the executable section, for example
CREATE OR REPLACE my_package
IS
my_number number;
IS
my_number := 0;
…
END init;
BEGIN
init;
END my_package;
Replace complex expression with BOOLEAN or function
A boolean expression evaluates could be three states: TRUE, FALSE,NULL, for instance,
eligible_for_raise BOOLEAN := (total_sal BETWEEN 100 and 6000) AND …;
Avoid implicit datatype conversion, for instance:
my_birthdate DATE := ‘01-SEP-2004’; (wrong)
my_birthdate DATE := to_date(‘01-SEP-2004’,’DD-MON-YYYY’);
Because if you let implicit conversion, when upgrade version of database, some default value could be different, for example, NLS_DATE_FORMAT;
Centralize CONSTANT type together in package
Centralize Type definition in package
Declare all package-level data in package body and provide SET/GET function to access the value.
CREATE OR REPLACE PACKAGE BODY my_package
IS
package_var number;
BEGIN
PROCEDURE set_var(in_var number)
BEGIN
package_var := in_var;
END my_package;
IF, ELSIF compare
--(This is better than right side)
IF line = 1 THEN
…
…
…
…
…
…
Each if statement will be evaluated, so Th left side statement is better than right side.
Use single EXIT in simple loop
LOOP
IF (condition1 > 0) THEN
exit_flag := true;
exit_flag := true;
EXIT WHEN exit_flag; --( easy to debug and maintain)
END LOOP ;
Never declare the FOR loop index
FOR index IN (1..1000)
END LOOP ;
Index-By Table & nested table can be sparse, that means that the defined row need not be sequentially defined.
If the collection is sparse and you scan a collection. If you try to access the undefined row, it will raise NO_DATA_FOUND. but you use FIRST,NEXT,LAST, it will be fine.
Move static expression outside of the loops and SQL statement. for instance,
loop
my_test(var1, sysdate, is_errhandler); -- Can move outside
end loop;
Use anonymous block within IF statements to conserve resource. for example.
l_name VARCHAR2(2000);
l_accounts NUMBER;
BEGIN
use_account(l_accounts);
use_name(l_name);
END;
--No use of l_name or l_accounts
Verify preconditions using standardize assertion.
PROCEDURE assert_statement
(ib_condition IN BOOLEAN
,is_message IN VARCHAR2
,ib_raise IN BOOLEAN
,is_exception IN VARCHAR2 DEFAULT 'VALUE_ERROR')
AS
BEGIN
IF (NOT ib_condition) OR (ib_condition IS NULL) THEN
IF (ib_raise) THEN
DBMS_OUTPUT.PUT_LINE(is_message);
EXECUTE IMMEDIATE
'BEGIN RAISE ' || is_exception || '; end;';
END IF;
END IF;
END assert_statement;
Use named the constant to soft-code application-special error numbers and messages.
Define multi-row cursors in package so they can be used from multiple programs.
package book_pkg is
cursor allbooks is
WHERE …
This is dubious and lame excuse. (english)
Using returning to retrieve information about modified rows, for instance.
INSERT INTO patient (patient_id, last_name, first_name)
VALUES (patient_seq.NEXTVAL, 'FEUERSTEIN', 'STEVEN')
RETURNING patient_id INTO l_patient_id;
Use BULK COLLECT to improve performance
Use FORALL to improve performance, it is fit to large number record to update, for instance.
TYPE book_t IS TABLE OF borrower.user_id%TYPE;
books book_t := book_t();
TYPE isbn_t IS TABLE OF book.isdn%TYPE;
isbns isbn_t := isbn_t();
LOOP
books.EXTEND();
isbns.EXTEND();
books(books.LAST) := book_rec.userid;
isbns(isbns.LAST) := book_rec.isbn;
END LOOP ;
FORALL index IN books.FIRST..books.LAST
LOOP
UPDATE table
SET borrow_date = SYSDATE
, borrow_id = books(index)
WHERE isbn = isbns(index);
END LOOP ;
Use named notation to clarify.
PLGdoir.ins (driver_in => drv,
objtype_in => c_table,
attrname_in => c_global,
attrtype_in => c_global,
infotype_in => text_in,
info_in => v_info);
Use NOCOPY to minimize overhead when collections and records are in out parameter.
PLSQL default:
IN argument is passed by reference
OUT, IN OUT argument is passed by value
Limit the function to single EXIT
Never return NULL from Boolean function
(English) very cumbersome
Boolean variable default value is NULL
My-habit
10 Variable
| scope | description |
| g | global |
| v | local |
| c | cursor |
| variable type | description |
| s | char, varchar2 |
| n | number |
| i | integer, pls_integer |
| r | record |
| c | cursor |
| t | table |
11 Subtype
Built-in types are programmer-defined Types and Subtypes
A better use for subtypes would be something like SUBTYPE money IS stock_items.unit_price%TYPE.
11.1.1 Primary Identifier
Avoid abbreviations, unless they are already familiar terms.
We can follow that up with:
If the name is too long, think about it some more. You can do better
11.1.2 Collection type(PLSQL Table)
TYPE account_tt IS TABLE OF accounts%ROWTYPE;
v_accounts ACCOUNT_TT; ## Type Table (Better UpperCase)
11.1.3 Record Type
TYPE g_address_rectype IS RECORD (...); -- Formal
TYPE address_rectype IS RECORD (...); -- Informal
TYPE address IS RECORD (...); -- Chilled
11.1.4 Comment style
12 Explain WHY, not HOW
What is useful is to explain why you are doing something.
When you do enter comments, keep them short and to the point.
12.1.1 OUT parameters (and why I don't like them)
Function can return one variable.
Procedure can return more than one variables, but not recommended, instead of record type
12.1.2 Ensure Conditions in ELSIF Clauses are Exclusive
The implication of ELSIF clauses is that if one condition is fulfilled, all others would fail
THEN
...
ELSIF sal BETWEEN 10000 AND 20000
THEN
...
ELSIF sal BETWEEN 20000 AND 30000
THEN
...
...
THEN
...
THEN
...
THEN
...
...
Use Boolean Elements to Improve Readability
eligible_for_raise :=
total_sal BETWEEN 10000 AND 50000
AND emp_status(emp_rec.empno) = 'N'
AND MONTHS_BETWEEN(emp_rec.hiredate, SYSDATE) > 10;
THEN
GIVE_RAISE(emp_rec.empno);
Avoid IF when assigning values to Boolean Variables
IF hiredate < SYSDATE
THEN
date_in_past := TRUE;
date_in_past := FALSE;
date_in_past := hiredate < SYSDATE;
Some general guidelines:
Do not EXIT or RETURN out of a FOR or WHILE loop.16
A FOR loop should only be used when you want to execute the body a fixed number of times. Stay for the duration or use a different loop construct.
The loop should be terminated only when the condition in the boundary evaluates to FALSE.
The Cursor FOR loop is nearly always preferable to the longwinded and slower OPEN-FETCH-EXIT-CLOSE. As a rule you should only consider OPEN-FETCH-EXIT-CLOSE when:
You are working with a cursor variable. The compiler can't automatically declare a record of cursor%ROWTYPE for weak ref cursors (since the return type is unknown until runtime), although you would think it could manage with strongly-typed ones. Currently (Oracle 9.2) it does not.
You want to retain the record after the loop completes. However since a single-row query is better handled with SELECT INTO (simpler, faster, easier to follow), that only leaves the case where you need to loop through a number of records and only retain the final one, which must be a fairly unusual scenario.
Anonymous Cursor FOR loops (e.g. FOR r IN (SELECT cols FROM sometable)
LOOP are fine. Feuerstein & Pribyll warn against putting SQL in 'unexpected places' within your code, but surely right next to the processing is a pretty good place. The only downsides are:
You can't reuse the cursor.
You can't base record types on the cursor's %ROWTYPE.
Since the cursor is anonymous, you can't refer to its attributes. If you subsequently need its %ROWCOUNT, you will have to go back and re-code it as a Cursor FOR loop.
Remove all “magic numbers” and other literals (within reason) from your code. Instead, declare constants to hold those literal values. This aids maintenance by self-documenting (the name of the constant will suggest what it stands for) and by ensuring consistency in the event of the value changing.
Allow the value of that literal (now a constant) to be set in only one place in your code.
If you find that you have written a program in which a variable's value does not change, you should first determine whether that behavior is correct. If it is, you should then convert that variable to a constant.
If you do convert a variable to a constant, you should also change its name17. This will help to remind anyone reading the code that your identifier refers to a constant and cannot be changed.
Each variable you declare should have one purpose and one purpose only.
12.1.3 Name subtypes to self-document code
One of the most compelling reasons for creating your own subtypes is to provide application- or function-specific datatypes that automatically document your code.
To make it easiest for individual developers to be aware of and make use of standard variable declarations, consider creating a package that contains only standard subtypes, constants and variable declarations and any code necessary to initialize them.
SUBTYPE room_number IS rooms.room_number%TYPE;
...
-- A declaration using the subtype:
v_open_room ROOM_NUMBER;
12.1.4 Use %TYPE whenever possible
12.1.5 Use %TYPE to standardise non-database declarations
v_revenue NUMBER(20,2) DEFAULT 0;
v_total_revenue v_revenue%TYPE;
Note that v_revenue’s DEFAULT 0 is not inherited by v_total_revenue.
12.1.6 Use meaningful abbreviations for table and column aliases
FROM employees emp --better than a,b,c
, companies com
, profiles pro
, sales sal
WHERE com.com_id = emp.emp_com_id
AND pro.pro_com_id = com.com_id
AND sal.sal_com_id (+)= pro.pro_com_id
13 Session mode
· Multi session - Each Test Window, SQL Window and Command Window will have its own session, and another session will be used for compilations. This is the most flexible setting, and will obviously lead to the largest amount of database sessions. Another possible disadvantage is that updates in Window X will only be visible in Window Y after they are committed.
· Dual session - The Test Windows, SQL Windows and Command Windows will share one session, and another session will be used for compilations. Disadvantage of this mode is that only one window can execute at a time.
· Single session - All windows and all compilations use the same session, making transaction management difficult. The debugger is disabled in this mode. Only use this setting if you are restricted to using one database session.
14 Truncate table
You cannot individually truncate a table that is part of a cluster. You must either
truncate the cluster, delete all rows from the table, or drop and re-create the table.
You cannot truncate the parent table of an enabled referential integrity constraint. You must disable the constraint before truncating the table. (An exception is that you may truncate the table if the integrity constraint is self-referential.)
If table belongs to a hierarchy, then it must be the root of the hierarchy.
If a doman index is defined on table, then neither the index nor any index partitions can be marked IN_PROGRESS.
15 The "Template List" option from tools menu is not persistent
If you subsequently go to Window List, to use ‘Save layout’ to make this persistent.
16 PLSQL Developer configure parameter in regedit
//HCU/software/Allround Automations/...
17 Automatic logon
Note that you can also the following registry key to supply a default logon:
HKEY_CURRENT_USER/Software/Allround Automations/PL/SQL Developer/Logon
Here you can add a Username, Password and Database.
The last method has the advantage that it enables you to be automatically logged on after doubleclicking a PL/SQL Developer registered file.
18 PRAGMA
EXCEPTION_INIT
RESTRICT_REFERENCES
SERIALLY_REUSABLE
19 Transaction with DDL statement
A user runs a DDL statement such as CREATE, DROP, RENAME, or ALTER. If the current transaction contains any DML statements, Oracle first commits the transaction, and then runs and commits the DDL statement as a new, single statement transaction.
20 UTL_FILE new Feature
21 SQL error_code(4061)
ORA-04061 existing state of string has been invalidated
Cause: Attempt to resume the execution of a stored procedure using the existing state which has become invalid or inconsistent with the stored procedure because the procedure has been altered or dropped.
rset = pstmt.executeQuery();
} catch (SQLException e) {
if (e.getErrorCode() == 4061) {
rset = pstmt.executeQuery();
}
}
22 Anonymous blocks nested
IS
year_total NUMBER;
year_total := 0;
/* Beginning of nested block */
DECLARE
month_total NUMBER;
month_total := year_total / 12;
END set_month_total;
/* End of nested block */
In general, the advantage of nesting a block is that it gives you a way to control both scope and visibility in your code
IS
[1]salary NUMBER;
...
salary NUMBER;
[2]salary := calc_totals.salary;
...
It will always be resolved first against the declaration in the inner block, where that variable is visible without any qualification. If I wish to make reference to the procedure-wide salary variable inside the inner block, I must qualify that variable name with the name of the procedure (cal_totals.salary).
PROCEDURE remove_order (order_id IN NUMBER)
DELETE[B1] orders WHERE order_id = order_id; -- Oops!
PROCEDURE remove_order (order_id IN NUMBER)
DELETE orders WHERE order_id = remove_order[B2] .order_id;
PROCEDURE calc_totals (fudge_factor IN NUMBER)
/* Beginning of nested block (in this case a procedure). Notice
|we're completely inside the declaration section of calc_totals.
*/
PROCEDURE compute_running_total
IS
BEGIN
/* subtotal, declared above, is both in scope and visible */
subtotal[B4] := subtotal + subtotal * fudge_factor;
/* End of nested block */
BEGIN
FOR mth IN 1..12
LOOP
compute_running_total;
END LOOP ;
DBMS_OUTPUT.PUT_LINE('Fudged total for year: ' || subtotal);
Nested programs can make your program more readable and maintainable, and also allow you to reuse logic that appears in multiple places in the block.
23 Collection
23.1Associative Arrays(Index-by Tables)
Are sets of key-values pairs, where each key is unique, the key value can be an integer or a string.
For Instance:
TYPE population_type IS TABLE OF NUMBER [BX5] INDEX BY VARCHAR2[BX6] (64);
country_population population_type;
continent_population population_type;
howmany NUMBER;
which VARCHAR2(64);
country_population(' Greenland ') := 100000; -- Creates new entry
country_population(' Iceland ') := 750000; -- Creates new entry
-- Looks up value associated with a string
howmany := country_population(' Greenland ');
continent_population(' Australia ') := 30000000;
continent_population(' Antarctica ') := 1000; -- Creates new entry
continent_population(' Antarctica ') := 1001; -- Replaces previous value
-- Returns ' Antarctica ' as that comes first alphabetically.
which := continent_population.FIRST;
-- Returns ' Australia ' as that comes last alphabetically.
which := continent_population.LAST;
-- Returns the value corresponding to the last key, in this
-- case the population of Australia .
howmany := continent_population(continent_population.LAST);
You can think of them as one-dimensional arrays with no upper bound, you can model multi-dimensional arrays by creating nested tables.
Nested table are unbounded
Nested table might not have consecutive subscript
-- Constructor Nested Table
TYPE Colors IS TABLE OF VARCHAR2(16);
rainbow Colors;
rainbow := Colors('Red','Orange','Yellow','Green','Blue','Indigo','Violet');
-- Constructor Varray
TYPE Colors IS VARRAY(10) OF VARCHAR2(16);
rainbow Colors;
rainbow := Colors('Red','Orange','Yellow','Green','Blue','Indigo','Violet');
-- Combining Declaration and Constructor
TYPE Colors IS TABLE OF VARCHAR2(20);
my_colors Colors := Colors('Brown','Gray','Beige');
-- Reference Nested Table
TYPE Roster IS TABLE OF VARCHAR2(15);
names Roster := Roster('J Hamil', 'D Caruso', 'R Singh');
FOR i IN names.FIRST .. names.LAST
LOOP
IF names(i) = 'J Hamil' THEN
NULL;
END LOOP ;
-- Exception
Example 5-20 Possible Exceptions for Collection Assignments
Assigning a value to a collection element can cause various exceptions:
* If the subscript is null or is not convertible to the right datatype, PL/SQL raises the predefined exception VALUE_ERROR. Usually, the subscript must be an integer. Associative arrays can also be declared to have VARCHAR2 subscripts.
* If the subscript refers to an uninitialized element, PL/SQL raises SUBSCRIPT_BEYOND_COUNT.
* If the collection is atomically null, PL/SQL raises COLLECTION_IS_NULL.
LIMIT
FIRST and LAST
PRIOR and NEXT
EXTEND
TRIM
DELETE
FORALL i IN depts.FIRST..depts.LAST
DELETE FROM employees2 WHERE department_id = depts(i);
FORALL i IN 1..iterations -- use FORALL statement
INSERT INTO parts2 VALUES (pnums(i), pnames(i));
FORALL j IN 4..7 -- use only part of varray
DELETE FROM employees2 WHERE department_id = depts(j);
--Using FORALL with Non-Consecutive Index Values
FORALL i IN INDICES OF cust_tab
INSERT INTO valid_orders(cust_name, amount) VALUES(cust_tab(i), amount_tab(i));
24 PL/SQL Developer Auto Login
plsqldev.exe userid=bxu/j777v@JDBA
HKEY_CURRENT_USER/Software/Allround Automations/PL/SQL Developer/LogonHere you can add a Username, Password and Database.
There is params.ini in PLSQLDEVELOPER INSTALL folder
# nologon=1
# oraclehome=
# dontadjustpath=1
# nosplash=1
# noplugins=1
# library=
# prefpath=
25 NULLs and the Concatenation Operator
25.1Define
its value is unknown -- indeterminate. "Unknown" is very different from a blank or a zero or the Boolean value FALSE. "Unknown" means that the variable has no value at all and so cannot be compared directly with other variables.
The concatenation operator ignores null operands. For example, the expression
'apple' || NULL || NULL || 'sauce'
returns the following value:
'applesauce'
‘bxu’ || NULL // ‘bxu’
‘’ || NULL // NULL
‘ ‘ || NULL // ‘ ‘
1 + null ; null
· PL/SQL treats a string of zero-length as a NULL. A zero-length string is two single quotes without any characters in between. The following two assignments are equivalent:
my_string := NULL;
my_string := '';
A null string is usually indistinguishable from a literal of zero characters
str VARCHAR2(1) := '';
IF str IS NULL -- will be TRUE
flag CHAR(2)[B7] := ''; -- try to assign zero-length string to CHAR(2)
IF flag = ' ' ... -- will be TRUE
IF flag IS NULL ... -- will be FALSE
If you create a CHAR(n) variable in PL/SQL and assign a zero-length string to it, Oracle blank-pads the empty variable with space characters, making it not null
While NULL tends to behave as if its default datatype is VARCHAR2, Oracle will try to implicitly cast NULL to whatever type is needed for the current operation. Occasionally, you may need to make the cast explicit, using syntax such as TO_NUMBER(NULL) or CAST(NULL AS NUMBER).
Scalar expressions yield null if any operand is null, but nulls are ignored by aggregate functions. This behavior is specified by the SQL standard, but it can still lead to some very surprising and unintuitive query results.
Code Listing 5: SUM(A+B) is not the same as SUM(A)+SUM(B)
SELECT SUM(SAL+COMM), SUM(SAL)+SUM(COMM) FROM EMP_M;
SUM(SAL+COMM) SUM(SAL)+SUM(COMM)
__________________ ________________________
2650 19900
Why the difference? It's because the result of SAL + COMM is non-null only for those rows in which both SAL and COMM are non-null. Thus, only those rows contribute to the result of SUM(SAL+COMM). The result of SUM(SAL)+SUM(COMM), on the other hand, manages to include all non-null values from both columns.
no raise ‘NO_DATA_FOUND’ in aggregation query
SELECT SUM(1+2)
INTO vn_sum
FROM DUAL
WHERE 1=2;
SELECT COUNT(1)
INTO vn_sum
FROM DUAL
WHERE 1=2;
26 Don’t Use & Use
WHERE SUBSTR(ACCOUNT_NAME,1,7) = 'CAPITAL';
WHERE ACCOUNT_NAME LIKE 'CAPITAL%';
27 Embedding Single Quotes Inside a Literal String
| Literal (default delimiter) | Actual value |
| 'There''s no business like show business.' '"Hound of the Baskervilles"' '''' '''hello''' ''''''
|
There's no business like show business. "Hound of the Baskervilles" ' 'hello' ''
|
Oracle Database 10g Release 1 introduced user-defined delimiters . Start the literal with "q" to mark your delimiter, and surround your delimited expression with single quotes.
| Literal (delimiters highlighted) | Actual value |
| q'(There's no business like show business.)' q'{"Hound of the Baskervilles"}' q'[']' q'!'hello'!' q'|''|'
|
There's no business like show business. "Hound of the Baskervilles" ' 'hello' ''
|
28 Maximum Varchar2
max = 32767 // internal maximum limit
29 Display all warning
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
30 Constraint Type
Decode(CONSTRAINT_TYPE, 'C','Check',
'P','Primary Key',
'U','Unique',
'R','Foreign Key',
'V','With Check Option') type,
31 BETWEEN … AND …
FROM dual
WHERE var BETWEEN 1 AND 10
is equal
32 PL/SQL Developer Debug
32.1View collection variable
Right click the collection variable duration debugging and select View Collection from pop menu.
32.2Breakpoint condition
· Use Message – The checkbox enables or disables the message. When the breakpoint line is
reached, and if the conditions are met, the message will be placed on the output page.
· Don’t Break – This checkbox is only useful if you also define a message. When it is checked,
execution is never halted on this breakpoint line. This way you can define breakpoints that only
generate messages on the output page.
· Use Pass Count – This checkbox enables or disables the pass count, which defines how many
times the breakpoint line must be passed before execution is halted. If, for example, you define a
pass count of 10, execution will halt every 10th time the breakpoint line is reached.
33 PL/SQL Test
(Þ) at the upper-left of the variables list can be used to quickly copy the variables from the source.
After execution, any variable whose value is changed will be displayed with yellow.
33.1Viewing result sets(?)
In a Test Script you are not limited to PL/SQL blocks. You can also execute single SQL statements,
including select statements. Executing a select statement will display an additional Results tab page,which displays all selected rows.
34 PLSQLDoc
34.1Document Tags
34.1.1 %Param
-- Lock the emp record.
-- %param p_empno The number of the emp record.
-- %param p_nowait Indicates whether or not the procedure should wait
-- for other locks to be released.
procedure lock_record(p_empno in emp.empno%type,
p_nowait in boolean);
Tags must be placed at the start of a comment line, except for the {%link} and {*} tags, which can appear anywhere in the comment section. The text of a tag finishes when a new tag is encountered, or at the end of the comment section. Leading spaces and returns will be ignored in the text. To include a return, use an HTML tag like
or
. For /* */ comments sections, leading and trailing asterisks will be ignored on each line.
Other HTML tags that you can use are:
to specify the font.
, , and to use bold, italic and underlined text.
to insert a horizontal line.
to insert example code using the courier font.
for preformatted text so that spaces and returns are preserved.
-
and
- to include a bullet list.
The following table describes all tags:
,
and
to include a table.
Tag
Description
%param
The description of a function or procedure parameter. Subsequent %param tags will be placed in one table.
%return
A description of the return value of a function.
%value
A possible value for a package variable or object type attribute. Subsequent %value tags will be placed in one table.
%raises
A list of exceptions that can be raised by a program unit. Subsequent %raises tags will be placed in one table.
%see
See also hyperlink. Subsequent %see tags will be placed in one table. To link to a specific overloading of a program unit, follow the name by a semi-colon and the overload index (1-based). For example:
%see employee
%see department.dname;2
%usage
A text to describe the usage of the element.
%author
The author of the object.
%version
The version of the object.
{%link
[description]}
or
{%link
[description]}
An explicit hyperlink. To link to a specific overloading of a program unit, follow the name by a semi-colon and the overload index (1-based). The optional description will be placed in the document. For example:
The {%link department.name department name function} is...
Will result in:
The department name function is…
{*}
A bullet of a bullet list. Useful to describe possible values of a parameter or return value. For example:
{*} 0 The function returned successfully
{*} 1 The employee does not exist
{*} 2 The employee record is locked
{%skip}
Skip this comment block for documentation.
The complete example in the next chapter demonstrates most of these tags.
35 Explain Plan, Profiler Install in own schema
Run TOAD, go to Tools > Server side install > Install in individual schema
36 PRAGMA Keyword
A programming notion that is truly derived from Greek is pragma, which means "deed" or, by implication, an "action, It's like an option that you give the compiler; it can result in different runtime behavior for the program, but it doesn't get translated directly into byte code.
37 Exception
An exception that is defined by Oracle and is usually raised by the PL/SQL runtime engine when it detects an error condition. Some system exceptions have names, such as NO_DATA_FOUND, while many others simply have numbers and descriptions.
An exception that is defined by the programmer and is therefore specific to the application at hand. You can associate exception names with specific Oracle errors using the EXCEPTION_INIT pragma (a compiler directive, requesting a specific behavior), or you can assign a number and description to that error using RAISE_APPLICATION_ERROR.
37.1RAISE_APPLICATION_ERROR
In addition, a developer can raise exceptions using RAISE_APPLICATION_ERROR (covered later under "Raising Exceptions) that consist of nothing more than an error number (between -20000 and -20999) and an error message.
When this procedure is run, execution of the current PL/SQL block halts immediately, and any changes made to OUT or IN OUT arguments (if present) will be reversed. Changes made to global data structures, such as packaged variables, and to database objects (by executing an INSERT, UPDATE, or DELETE) will not be rolled back. You must execute an explicit ROLLBACK in your exception section to reverse the effect of DML operations.
Here's the header for this procedure (defined in package DBMS_STANDARD):
PROCEDURE RAISE_APPLICATION_ERROR (
num binary_integer,
msg varchar2,
keeperrorstack boolean default FALSE);
EXCEPTION_INIT is a compile-time command or pragma used to associate a name with an internal error code. EXCEPTION_INIT instructs the compiler to associate an identifier, declared as an EXCEPTION, with a specific error number. Once you have made that association, you can then raise that exception by name and write an explicit WHEN handler that traps the error.
It cannot be -1403 (one of the two error codes for NO_DATA_FOUND). If for some reason you want to associate your own named exception with this error, you need to pass 100 to the EXCEPTION_INIT pragma.
It cannot be 0 or any positive number besides 100.
It cannot be a negative number less than -1000000.
invalid_table_name EXCEPTION;
PRAGMA EXCEPTION_INIT (invalid_table_name, -903);
invalid_column_name EXCEPTION;
PRAGMA EXCEPTION_INIT (invalid_column_name, -904);
Table 6-1. Some of the predefined exceptions in PL/SQL
Name of exception Oracle error/SQLCODE
Description
CURSOR_ALREADY_OPEN ORA-6511 SQLCODE=-6511
You tried to OPEN a cursor that was already OPEN. You must CLOSE a cursor before you try to OPEN or re-OPEN it.
DUP_VAL_ON_INDEX ORA-00001 SQLCODE= -1
Your INSERT or UPDATE statement attempted to store duplicate values in a column or columns in a row that is restricted by a unique index.
INVALID_CURSOR ORA-01001 SQLCODE=-1001
You made reference to a cursor that did not exist. This usually happens when you try to FETCH from a cursor or CLOSE a cursor before that cursor is OPENed.
INVALID_NUMBER ORA-01722 SQLCODE =-1722
PL/SQL executes a SQL statement that cannot convert a character string successfully to a number. This exception is different from the VALUE_ERROR exception because it is raised only from within a SQL statement.
LOGIN_DENIED ORA-01017 SQLCODE= -1017
Your program tried to log into the Oracle RDBMS with an invalid username-password combination. This exception is usually encountered when you embed PL/SQL in a 3GL language.
NO_DATA_FOUND ORA-01403 SQLCODE= +100
This exception is raised in three different scenarios: (1) You executed a SELECT INTO statement (implicit cursor) that returned no rows. (2) You referenced an uninitialized row in a local PL/SQL table. (3) You read past end-of-file with the UTL_FILE package.
NOT_LOGGED ON ORA-01012 SQLCODE= -1012
Your program tried to execute a call to the database (usually with a DML statement) before it had logged into the Oracle RDBMS.
PROGRAM_ERROR ORA-06501 SQLCODE= -6501
PL/SQL encounters an internal problem. The message text usually also tells you to "Contact Oracle Support."
STORAGE_ERROR ORA-06500 SQLCODE= -6500
Your program ran out of memory, or memory was in some way corrupted.
TIMEOUT_ON_RESOURCE ORA-00051 SQLCODE=-51
A timeout occurred in the RDBMS while waiting for a resource.
TOO_MANY_ROWS ORA-01422 SQLCODE= -1422
A SELECT INTO statement returned more than one row. A SELECT INTO can return only one row; if your SQL statement returns more than one row, you should place the SELECT statement in an explicit CURSOR declaration and FETCH from that cursor one row at a time.
TRANSACTION_BACKED_OUT ORA-00061 SQLCODE= -61
The remote part of a transaction is rolled back, either with an explicit ROLLBACK command or as the result of some other action (such as a failed SQL/DML on the remote database).
VALUE_ERROR ORA-06502 SQLCODE= -6502
PL/SQL encountered an error having to do with the conversion, truncation, or invalid constraining of numeric and character data. This is a very general and common exception. If this type of error is encountered in a SQL DML statement within a PL/SQL block, then the INVALID_NUMBER exception is raised.
ZERO_DIVIDE ORA-01476 SQLCODE= -1476
Your program tried to divide by zero.
38 Built-in Error Functions
SQLCODE returns the error code of the most recently raised exception in your block.
SQLERRM is a function that returns the error message for a particular error code
DBMS_UTILITY.FORMAT_ERROR_STACK
This built-in function, like SQLERRM, returns the message associated with the current error (i.e., the value returned by SQLCODE). It differs from SQLERRM in two ways:
Its length is not restricted; it will return the full error message string.
You cannot pass an error code number to this function; it cannot be used to return the message for a "random" error code
DBMS_UTILITY.FORMAT_CALL_STACK
DBMS_UTILITY.FORMAT_CALL_STACK returns a formatted string showing the execution call stack inside your PL/SQL application. Its usefulness is not restricted to error management; you will also find it handy for tracing the execution of your code. If, however, you are not yet running Oracle Database 10g, you can include this function in your exception section and at least see the sequence of program calls that got you to the exception handler.
Useful applications of SQLERRM
As mentioned earlier in this chapter, SQLERRM will return the error message for an error code. If, however, you pass SQLERRM a code that is not valid, it does not raise an exception. Instead, it returns a string in one of the following two forms:
If the number is negative:
ORA-NNNNN: Message NNNNN not found; product=RDBMS; facility=ORA
If the number is positive:
-NNNNN: non-ORACLE exception
PL/SQL returns the error that raised the unhandled exception all the way back to the application environment from which PL/SQL was run, in the case of SQL*Plus, a ROLLBACK of any DML changes from within that top-level block's logic is automatically performed
How can this be so? Because all user-defined exceptions have the same error number and error message: 1 and "User-defined error", respectively. Not very informative. Only the exception's name is unique. But outside of local_block, that name, that exception, no longer exists.
Decide on Your Error Management Strategy
Linked tightly to these very high-level questions are more concrete issues, such as:
Should I include an exception-handling section in every one of my PL/SQL blocks?
Should I have an exception-handling section only in the top-level or outermost blocks?
How should I manage my transactions when errors occur?
It depends at least in part on the application architecture and the way it is used (batch process versus user-driven transactions, for example). However you answer these questions for your application, I strongly suggest that you "codify" the strategy and rules for error handling within a standardized package. I address this topic in a later section.
Here are some general principles you may want to consider:
When an error occurs in your code, obtain as much information as possible about the context in which the error was raised. You are better off with more information than you really need, rather than with less. You can then propagate the exception to outer blocks, picking up more information as you go.
Avoid hiding errors with handlers that look like WHEN error THEN NULL;. There may be a good reason for you to write code like this, but do make sure it is really what you want and document the usage so that others will be aware of it.
Rely on the default error mechanisms of PL/SQL whenever possible. Avoid writing programs that return status codes to the host environment or calling blocks. The only time you will want to use status codes is if the host environment cannot gracefully handle Oracle errors (in which case, you might want to consider switching your host environment!).
Notice that I am writing to a database table for my log. This means that the log entry has become a part of my logical transaction. If I need to roll back that transaction, I lose my error log.
39 Internet Datatypes
The XMLType allows you to query and store XML data in the database using functions like SYS_XMLGEN and the DBMS_XMLGEN package. It also allows you to use native operators in the SQL language to perform searching with the XPath language.
The URI-related types, including URIType and HttpURIType, are all part of an object type inheritance hierarchy and can be used to store URLs to external web pages and files, as well as to refer to data within the database
40 "Any" Datatypes
We write more generic kinds of code. For those situations, the "Any" datatypes might come in very handy.
The "Any" datatypes include AnyType, AnyData, and AnyDataSet.
41 Anchored Declarations
variable name type attribute%TYPE [optional default value assignment];
variable name table_name | cursor_name%ROWTYPE [optional default value
assignment];
41.1Anchoring to Cursors and Tables
SELECT author, title FROM book
WHERE isbn = '1-56592-335-9'
l_book book_cur%ROWTYPE;
OPEN book_cur;
FETCH book_cur INTO l_book;
process_book (l_book);
Benefits of Anchored Declarations
Synchronization with database columns
Normalization of local variables
42 Anchoring to NOT NULL Datatypes
If you use a NOT NULL variable in a %TYPE declaration, the new variable must have a default value provided. The same is not true, however, for variables declared with %TYPE where the source is a database column defined as NOT NULL. A NOT NULL constraint from a database table is not automatically transferred to a variable.
43 Programmer-Defined Subtypes
There are two kinds of subtypes, constrained and unconstrained
SUBTYPE POSITIVE IS BINARY_INTEGER RANGE 1 .. 2147483647;
SUBTYPE FLOAT IS NUMBER;
SUBTYPE big_string IS VARCHAR2(32767);
In other words, an unconstrained subtype provides an alias or alternate name for the original datatype
44 The VARCHAR2 Datatype
you must also specify a maximum length for the string, which can range from 1 to 32767 bytes.
If you need to work with strings greater than 4000 bytes in length (or 2000 prior to Oracle8i Database), consider storing those strings in CLOB (character large object) columns
feature_name VARCHAR2(100 BYTE); -- 100 byte string
emp_name VARCHAR2(30 CHAR); -- 30 character string
If you leave off the BYTE or CHAR qualifier, the results will depend on the setting of the NLS_LENGTH_SEMANTICS parameter.
45 Working with Strings
If you want to embed a single quote within a string constant, you can do so by typing the single quote twice:
'Aren''t you glad you''re learning PL/SQL?'
Another way to embed single quotes within string constants is to specify your own delimiter. Do this using the q prefix (uppercase Q may also be specified). For example:
q'!Aren't you glad you're learning PL/SQL?!'
or:
q'{Aren't you glad you're learning PL/SQL?}'
you may find yourself faced with the need to explicitly specify a string constant to be represented in the national character set. You can do so using the n prefix:
n'This string will be represented using the national character set.'
If you need a string in the national character set, and you also want to specify some characters by their Unicode code point, you can use the u prefix:
u'Thi/0073 /0073tring will be repre/0073ented u/0073ing ...'
45.1Using Nonprintable Characters
SELECT name || chr(10)
|| address1 || chr(10)
|| address2 || chr(10)
|| address3 || chr(10)
|| address4 || chr(10)
|| city || ', ' || state || ' ' || zipcode
FROM company;
46 Concatenating Strings
CONCAT (string1, string2)
CONCAT always appends string2 to the end of string1
CONCAT ('abc', 'defg') --> 'abcdefg'
CONCAT (NULL, 'def') --> 'def'
CONCAT ('ab', NULL) --> 'ab'
CONCAT (NULL, NULL) --> NULL
x := 'abc' || 'def' || 'ghi';
You can see that the || operator not only is much easier to use than CONCAT, but also results in much more readable code.
47 Trimming
What LPAD and RPAD giveth, LTRIM and RTRIM taketh away. For example:
a VARCHAR2(40) := 'This sentence has too many periods......';
b VARCHAR2(40) := 'The number 1';
DBMS_OUTPUT.PUT_LINE( RTRIM(a,'.') );
LTRIM(b,'ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz')
);
And the output is:
This sentence has too many periods
1
x VARCHAR2(30) := '.....Hi there!.....';
DBMS_OUTPUT.PUT_LINE( TRIM(LEADING '.' FROM x) );
DBMS_OUTPUT.PUT_LINE( TRIM(TRAILING '.' FROM x) );
DBMS_OUTPUT.PUT_LINE( TRIM(BOTH '.' FROM x) );
--The default is to trim from both sides
DBMS_OUTPUT.PUT_LINE( TRIM('.' FROM x) );
--The default trim character is the space:
DBMS_OUTPUT.PUT_LINE( TRIM(x) );
The output is:
Hi there!.....
.....Hi there!
Hi there!
Hi there!
.....Hi there!.....
comma_delimited := REGEXP_LIKE(names,'^([a-z A-Z]*,)+([a-z A-Z]*){1}$');
j_location := REGEXP_INSTR(names, 'A[^,]*[^aeiou],|A[^,]*[^aeiou]$')
REGEXP_SUBSTR(names,
'A[^,]*[^aeiou],|A[^,]*[^aeiou]$',
1, name_counter);
48 Working with Empty Stings
The following code demonstrates Oracle's behavior:
/* File on web: empty_is_null.tst */
DECLARE
empty_varchar2 VARCHAR2(10) := '';
empty_char CHAR(10) := '';
IF empty_varchar2 IS NULL THEN
DBMS_OUTPUT.PUT_LINE('empty_varchar2 is NULL');
DBMS_OUTPUT.PUT_LINE(''''' is NULL');
DBMS_OUTPUT.PUT_LINE('empty_char is NULL');
The output is:
empty_varchar2 is NULL
'' is NULL
user_entered_name VARCHAR2(30);
name_from_database VARCHAR2(30);
...
...
IF user_entered_name <> name_from_database THEN
...
If the user entered an empty string instead of a name, the IF condition shown in this example would never be TRUE. That's because a NULL is never not-equal, nor equal, to any other value. One alternative approach to this IF statement is the following:
49 Variable-to-database conversion
When you INSERT or UPDATE a CHAR variable into a VARCHAR2 database column, the SQL kernel does not trim the trailing blanks before performing the change. When the following PL/SQL is executed, the company_name in the new database record is set to "ACME SHOWERS········" (where · indicates a space). It is, in other words, padded out to 20 characters, even though the default value was a string of only 12 characters.
comp_id# NUMBER;
comp_name CHAR(20) := 'ACME SHOWERS';
INTO comp_id#
FROM dual;
INSERT INTO company (company_id, company_name)
VALUES (comp_id#, comp_name);
50 String comparisons
company_name CHAR(30) := 'Feuerstein and Friends';
char_parent_company_name CHAR(35) := 'Feuerstein and Friends';
varchar2_parent_company_name VARCHAR2(35) := 'Feuerstein and Friends';
--Compare two CHARs, so blank-padding is used
IF company_name = char_parent_company_name THEN
DBMS_OUTPUT.PUT_LINE ('first comparison is TRUE');
DBMS_OUTPUT.PUT_LINE ('first comparison is FALSE');
--Compare a CHAR and a VARCHAR2, so nonblank-padding is used
IF company_name = varchar2_parent_company_name THEN
DBMS_OUTPUT.PUT_LINE ('second comparison is TRUE');
DBMS_OUTPUT.PUT_LINE ('second comparison is FALSE');
The output is:
first comparison is TRUE
second comparison is FALSE
The first comparison is between two CHAR values, so blank-padding is used: PL/SQL blank-pads the shorter of the two values out to the length of the longer value. It then performs the comparison. In this example, PL/SQL adds five spaces to the end of the value in company_name and then performs the comparison between company_name and char_parent_company_name. The result is that both strings are considered equal. Note that PL/SQL does not actually change the company_name variable's value. It copies the value to another memory structure and then modifies this temporary data for the comparison.
The second comparison involves a VARCHAR2 value, so PL/SQL performs a non-blank-padding comparison. It makes no changes to any of the values, uses the existing lengths, and performs the comparison. In this case, the first 22 characters of both strings are the same, "Feuerstein and Friends", but the fixed-length company_name is padded with eight space characters, whereas the variable-length varchar2_company_name is not. Because one string has trailing blanks and the other does not, the two strings are not considered equal.
51 String Function
GREATEST(string1, string2, ...)
Takes one or more strings as input, and returns the string that would come last (i.e., that is the greatest) if the inputs were sorted in ascending order. Also see the LEAST function , which is the opposite of GREATEST.
LEAST(string1, string2, ...)
Takes one or more strings as input and returns the string that would come first (i.e., that is the least) if the inputs were sorted in ascending order. Also see GREATEST, which is the opposite of LEAST.
TRANSLATE (string1, search_set, replace_set)
Replaces every instance in string1 of a character from search_set with the corresponding character from replace_set. For example:
TRANSLATE ('abcd', 'ab', '12') --> '12cd'
TRIM (FROM string1)
Returns a version of string1 that omits any leading and trailing spaces. Variations include:
Explicitly specifies the default behavior of trimming both leading and trailing spaces.
TRIM(...trim_character FROM string1)
Removes occurrences of trim_character, which may be any one character that you want to specify.
52 Number
52.1The PLS_INTEGER Type
The PLS_INTEGER datatype is available in PL/SQL Release 2.3 (Oracle7 Database Release 3) and above and stores signed integers in the range -2,147,483,647 through 2,147,483,647. Values are represented using your hardware platform's native integer format.
The PLS_INTEGER datatype was designed for speed
The semantics of this datatype have changed from Oracle9i Database Release 2 to Oracle Database 10g Release 1. Beginning in Oracle Database 10g Release 1, BINARY_INTEGER is equivalent to PLS_INTEGER
53 TO_CHAR
SELECT TO_CHAR(123456789.01,'L999G999G999D99') FROM DUAL;
Result:
$123,456,789.01
SELECT TO_CHAR(0,'LB000G000G009D99') FROM DUAL;
SELECT TO_CHAR (SYSDATE, 'DY') FROM DUAL;
Result: MON
SELECT TO_CHAR (SYSDATE, 'DAY') FROM DUAL;
Result: MONDAY
54 Using CAST
a NUMBER := -123.45;
a1 VARCHAR2(30);
b VARCHAR2(30) := '-123.45';
b1 NUMBER;
b2 BINARY_FLOAT;
b3 BINARY_DOUBLE;
a1 := CAST (a AS VARCHAR2);
b1 := CAST (b AS NUMBER);
b2 := CAST (b AS BINARY_FLOAT);
b3 := CAST (b AS BINARY_DOUBLE);
DBMS_OUTPUT.PUT_LINE(a1);
DBMS_OUTPUT.PUT_LINE(b1);
DBMS_OUTPUT.PUT_LINE(b2);
DBMS_OUTPUT.PUT_LINE(b3);
The output is:
-123.45
-123.45
-1.23449997E+002
-1.2345E+002
55 Rounding and Truncation Functions
Function
Summary
CEIL
Returns the smallest integer that is greater than or equal to the specified value. This integer is the "ceiling" over your value.
FLOOR
Returns the largest integer that is less than or equal to the specified value. This integer is the "floor" under your value.
ROUND
Performs rounding on a number. You can round with a positive number of decimal places (the number of digits to the right of the decimal point) and also with a negative number of decimal places (the number of digits to the right of the decimal point).
TRUNC
Truncates a number to the specified number of decimal places. TRUNC simply discards all values beyond the decimal places provided in the call.
56 Datetime Datatypes
Stores a date and time, resolved to the second. Does not include time zone.
Stores date and time without respect to time zone. Except for being able to resolve time to the billionth of a second, TIMESTAMP is the equivalent of DATE
Stores the time zone along with each date and time value
TIMESTAMP WITH LOCAL TIME ZONE
Stores a date and time that is assumed to be in the local time zone. For database columns of this type, the "local" time zone is the database time zone. For PL/SQL variables of this type, the "local" time zone is the session time zone. Values that move between database columns and PL/SQL variables are converted from one time zone to the other.
Function
Time zone
Datatype returned
CURRENT_DATE
Session
DATE
CURRENT_TIMESTAMP
Session
TIMESTAMP WITH TIME ZONE
LOCALTIMESTAMP
Session
TIMESTAMP
SYSDATE
Server
DATE
SYSTIMESTAMP
Server
TIMESTAMP WITH TIME ZONE
57 Interval Datatypes
Oracle supports two interval datatypes
INTERVAL YEAR TO MONTH
Allows you to define an interval of time in terms of years and months.
Allows you to define an interval of time in terms of days, hours, minutes, and seconds (including fractional seconds).
The reason for having two INTERVAL types with a dividing line at the month level is that months are the only datetime component for which the length of time in question varies. Think about having an interval of 1 month and 30 days. How long is that, really? Is it less than two months? The same as two months? More than two months? If the one month is January, then 30 days gets you past February and into March, resulting in a 61-day interval that is a bit more than "two months" long. If the one month is February, then the interval is exactly two months (but only 59 or 60 days). If the one month is April, then the interval is slightly less than two months, for a total of 60 days.
var_name INTERVAL YEAR [(year_precision)] TO MONTH
or:
var_name INTERVAL DAY [(day_precision)] TO SECOND [(frac_sec_prec)]
/* File on web: interval_between.sql */
DECLARE
start_date TIMESTAMP;
end_date TIMESTAMP;
service_interval INTERVAL YEAR TO MONTH;
years_of_service NUMBER;
months_of_service NUMBER;
--Normally, we would retrieve start and end dates from a database.
start_date := TO_TIMESTAMP('20-NOV-2005','dd-mon-yyyy');
end_date := TO_TIMESTAMP ('26-DEC-2006','dd-mon-yyyy');
--Determine and display years and months of service:
service_interval := (end_date - start_date) YEAR TO MONTH;
DBMS_OUTPUT.PUT_LINE(service_interval);
--Use the new EXTRACT function to grab individual
--year and month components.
years_of_service := EXTRACT(YEAR FROM service_interval);
months_of_service := EXTRACT(MONTH FROM service_interval);
DBMS_OUTPUT.PUT_LINE(years_of_service || ' years and '
|| months_of_service || ' months');
Result:
+01-01
1 years and 1 months
A INTERVAL YEAR TO MONTH;
A := INTERVAL '40-3' YEAR TO MONTH;
EXTRACT(YEAR FROM A) || ' Years and '
|| EXTRACT(MONTH FROM A) || ' Months'
);
The output is:
40 Years and 3 Months
SELECT INTERVAL '10-3' YEAR TO MONTH FROM DUAL;
58 CAST and EXTRACT
a TIMESTAMP WITH TIME ZONE;
b VARCHAR2(40);
c TIMESTAMP WITH LOCAL TIME ZONE;
a := CAST ('24-Feb-2002 09.00.00.00 PM US/Eastern'
b := CAST (a AS VARCHAR2);
c := CAST (a AS TIMESTAMP WITH LOCAL TIME ZONE);
DBMS_OUTPUT.PUT_LINE(a);
DBMS_OUTPUT.PUT_LINE(b);
DBMS_OUTPUT.PUT_LINE(c);
The output is:
24-FEB-02 09.00.00.000000 PM US/EASTERN
24-FEB-02 09.00.00.000000 PM US/EASTERN
24-FEB-02 09.00.00.000000 PM
EXTRACT (component_name, FROM {datetime | interval})
IF EXTRACT (MONTH FROM SYSDATE) = 11 THEN
DBMS_OUTPUT.PUT_LINE('It is November');
DBMS_OUTPUT.PUT_LINE('It is not November');
Name
Description
ADD_MONTHS
Adds a specified number of months to a DATE. See the section "Adding and Subtracting Intervals to/from Datetimes" for an example.
CAST
Converts between datatypesfor example, between DATE and the various TIMESTAMP datatypes. See the section "CAST and EXTRACT."
EXTRACT
Returns the value of a specific datetime element, such as the hour, the minute, the day, the year, etc. See the section "CAST and EXTRACT."
LAST_DAY
Returns the last day in the month containing a specified input DATE.
MONTHS_ BETWEEN
Calculates the number of months between two DATEs. See the section"Computing the Interval Between Two Datetimes" for an example.
NEXT_DAY
Returns the DATE of the first weekday specified that is later than an input DATE.
NUMTOYMINTERAL
Converts a number representing some number of years or months (your choice) to a value of type INTERVAL YEAR TO MONTH.
ROUND
Returns a DATE rounded to a specified level of granularity.
59 Records in PL/SQL
my_book books%ROWTYPE;
INTO my_book
FROM books
WHERE title = 'Oracle PL/SQL Programming, 4th Edition';
IF my_book.author LIKE '%Feuerstein%'
THEN
DBMS_OUTPUT.put_line ('Our newest ISBN is ' || my_book.isbn);
Create corresponding cursors and records
SELECT * FROM books
WHERE author LIKE '%FEUERSTEIN%';
one_SF_book my_books_cur%ROWTYPE;
one_book books%ROWTYPE;
author books.author%TYPE,
category VARCHAR2(100),
total_page_count POSITIVE);
steven_as_author book_info_rt;
TYPE book_rc IS REF CURSOR RETURN books%ROWTYPE;
book_cv book_rc;
one_book book_cv%ROWTYPE;
Declaring programmer-defined record TYPEs
The datatype of a record's field can be any of the following:
Hardcoded, scalar datatype (VARCHAR2, NUMBER, etc.).
Programmer-defined SUBTYPE.
Anchored declarations using %TYPE or %ROWTYPE attributes. In the latter case, we have created a nested record one record inside another.
PL/SQL collection type; a field in a record can be a list or even a collection.
REF CURSOR, in which case the field contains a cursor variable.
prev_customer_sales_rec customer_sales_rectype;
top_customer_rec customer_sales_rectype;
60 Examples of programmer-defined record declarations
DECLARE SUBTYPE long_line_type IS VARCHAR2(2000);
SELECT name, SUM (order_amount) total_sales
FROM company c, order o
WHERE c.company_id = o.company_id;
TYPE employee_ids_tabletype IS
TABLE OF employee.employee_id%TYPE
INDEX BY BINARY_INTEGER;
TYPE mishmash_rectype IS RECORD
(emp_number NUMBER(10) NOT NULL,
paragraph_text long_line_type,
company_nm company.name%TYPE,
total_sales company_sales.total_sales%TYPE := 0,
new_hires_tab employee_ids_tabletype,
prefers_nonsmoking_fl BOOLEAN := FALSE,
new_company_rec company_rectype
);
Field-level operations with nested records
(intl_prefix VARCHAR2(2),
area_code VARCHAR2(3),
exchange VARCHAR2(3),
phn_number VARCHAR2(4),
extension VARCHAR2(4)
);
-- Each field is a nested record...
TYPE contact_set_rectype IS RECORD
(day_phone# phone_rectype,
eve_phone# phone_rectype,
fax_phone# phone_rectype,
home_phone# phone_rectype,
cell_phone# phone_rectype
);
auth_rep_info_rec contact_set_rectype;
Reference:
auth_rep_info_rec.fax_phone#.area_code :=
auth_rep_info_rec.home_phone#.area_code;
61 Trigger Pseudo-Records
When you are writing code inside database triggers for a particular table, Oracle makes available to you two structures, OLD and NEW, which are pseudo-records . These structures have the same format as table-based records declared with %ROWTYPE: they have a field for every column in the table:
CREATE OR REPLACE TRIGGER check_raise AFTER UPDATE OF salary ON employee FOR EACH ROW
WHEN (OLD.salary != NEW.salary) OR
(OLD.salary IS NULL AND NEW.salary IS NOT NULL) OR
(OLD.salary IS NOT NULL AND NEW.salary IS NULL)
IF :NEW.salary > 100000 THEN ...
62 Collections
Emulate bidirectional or random-access cursors
Improve performance of lookups
Cache database information
Nested table multiset operations
Oracle Database 10g Release 1 added a number of MULTISET operators that give us the ability to manipulate the contents of nested tables as sets (union, intersection, minus, etc.). You can also compare two nested tables for equality.
These are single-dimensional, unbounded, sparse collections of homogeneous elements that are available only in PL/SQL
They are initially dense but can become sparse and unbounded through deletions.
Like the other two collection types, VARRAYs (variable-sized arrays) are also single-dimensional collections of homogeneous elements. However, they are always bounded and never sparse.
Regardless of the particular type or usage, however, a collection is at its core a single-dimensional list of homogeneous elements.
All collections support the ability to reference a row via the row number, an integer value
Starting with Oracle9i Database Release 2, it is possible to index an associative array by string values (currently up to 32K in length) instead of by numeric row numbers. This feature is not available for nested tables or VARRAYs.
any of the following INDEX BY clauses are now accepted:
INDEX BY table.column%TYPE;
INDEX BY cursor.column%TYPE;
INDEX BY package.variable%TYPE;
INDEX BY package.subtype;
2 TYPE list_of_names_t IS TABLE OF person.first_name%TYPE
3 INDEX BY PLS_INTEGER;
4 happyfamily list_of_names_t;
5 l_row PLS_INTEGER;
7 happyfamily (2020202020) := 'Eli';
8 happyfamily (-15070) := 'Steven';
9 happyfamily (-90900) := 'Chris';
10 happyfamily (88) := 'Veva';
11
12 l_row := happyfamily.FIRST;
13
14 WHILE (l_row IS NOT NULL)
15
LOOP
16 DBMS_OUTPUT.put_line (happyfamily (l_row));
17 l_row := happyfamily.NEXT (l_row);
18 END
LOOP
;
19* END;
Steven
Veva
Eli
REM Section A
SQL> CREATE TYPE list_of_names_t IS TABLE OF VARCHAR2 (100);
2 /
Type created.
SQL>
1 DECLARE
2 happyfamily list_of_names_t := list_of_names_t ( );
3 children list_of_names_t := list_of_names_t ( );
4 parents list_of_names_t := list_of_names_t ( );
6 happyfamily.EXTEND (4);
7 happyfamily (1) := 'Eli';
8 happyfamily (2) := 'Steven';
9 happyfamily (3) := 'Chris';
10 happyfamily (4) := 'Veva';
11
12 children.EXTEND;
13 children (1) := 'Chris';
14 children.EXTEND;
15 children (2) := 'Eli';
16
17 parents := happyfamily MULTISET EXCEPT children;
18
19 FOR l_row IN parents.FIRST .. parents.LAST
20
LOOP
21 DBMS_OUTPUT.put_line (parents (l_row));
22 END
LOOP
;
23* END;
Veva
REM Section A
SQL> CREATE TYPE first_names_t IS VARRAY (2) OF VARCHAR2 (100);
2 /
Type created.
SQL> CREATE TYPE child_names_t IS VARRAY (1) OF VARCHAR2 (100);
2 /
Type created.
SQL> CREATE TABLE family (
2 surname VARCHAR2(1000)
3 , parent_names first_names_t
4 , children_names child_names_t
5 );
Table created.
REM Section C
SQL>
1 DECLARE
2 parents first_names_t := first_names_t ( );
3 children child_names_t := child_names_t ( );
5 parents.EXTEND (2);
6 parents (1) := 'Samuel';
7 parents (2) := 'Charina';
8 --
9 children.EXTEND;
10 children (1) := 'Feather';
11
12 --
13 INSERT INTO family
14 (surname, parent_names, children_names
15 )
16 VALUES ('Assurty', parents, children
17 );
PL/SQL procedure successfully completed.
2 /
CHILDREN_NAMES
--------------------------------------------
FIRST_NAMES_T('Samuel', 'Charina')
CHILD_NAMES_T('Feather')
Collections as datatypes of a function's return value
CREATE FUNCTION true_colors (whose_id IN NUMBER) RETURN Color_tab_t
AS
l_colors Color_tab_t;
SELECT favorite_colors INTO l_colors
FROM personality_inventory
WHERE person_id = whose_id;
RETURN l_colors;
THEN
Option #1 is easy. Notice, by the way, that this is another circumstance where you don't have to initialize the collection variable explicitly:
color_array Color_tab_t;
color_array := true_colors (8041);
With Option #2, we actually give the function call a subscript. The general form is:
variable_of_element_type := function() (subscript);
Or, in the case of the true_colors function:
one_of_my_favorite_colors VARCHAR2(30);
one_of_my_favorite_colors := true_colors (whose_id=>8041) (1);
As a PL/SQL developer, I find myself leaning toward using associative arrays as a first instinct. Why is this? They involve the least amount of coding. You don't have to initialize or extend them. They have historically been the most efficient collection type (although this distinction will probably fade over time). However, if you want to store your collection within a database table, you cannot use an associative array. The question then becomes: nested table or VARRAY?
Characteristic
Associative array
Nested table
VARRAY
Dimensionality
Single
Single
Single
Usable in SQL?
No
Yes
Yes
Usable as column datatype in a table?
No
Yes; data stored "out of line" (in separate table)
Yes; data stored "in line" (in same table)
Uninitialized state
Empty (cannot be null); elements undefined
Atomically null; illegal to reference elements
Atomically null; illegal to reference elements
Initialization
Automatic, when declared
Via constructor, fetch, assignment
Via constructor, fetch, assignment
In PL/SQL, elements
referenced via
BINARY_INTEGER (-2,147,483,647 .. 2,147,483,647)
VARCHAR2 (Oracle9i Database Release 2 and above)
Positive integer between 1 and 2,147,483,647
Positive integer between 1 and 2,147,483,647
Sparse?
Yes
Initially, no; after deletions, yes
No
Bounded?
No
Can be extended
Yes
Can assign value to any
element at any time?
Yes
No; may need to EXTEND first
No; may need to EXTEND first, and cannot EXTEND past upper bound
Means of extending
Assign value to element with a new subscript
Use built-in EXTEND procedure (or TRIM to condense), with no predefined
maximum
EXTEND (or TRIM), but only up to declared maximum size
Can be compared for equality?
No
Yes, in Oracle Database 10g
No
Can be manipulated with set operators
No
Yes, in Oracle Database 10g
No
Retains ordering and subscripts when stored in and retrieved from database?
N/A
No
Yes
Operation
Limit
Suitable: varray
Exception: COLLECTION_IS_NULL
Prior, Next
Suitable: nested table, associative arrays, varray
Exception: COLLECTION_IS_NULL
Note: Applied to collections that have no elements, it returns NULL
Trim
Suitable: nested table, varray
Exception: SUBSCRIPT_BEYOND_COUNT, COLLECTION_IS_NULL
Count
Suitable: associative array, nested table, varray
Exception: COLLECTION_IS_NULL
Delete
Suitable: associative array, nested table, varray
Exception: COLLECTION_IS_NULL
Note: delete without argument removes all of the elements of collection.
Exists
Suitable: nested tables, associative array, varray
Exception: VALUE_ERROR
Note: always return TRUE/FALSE
Extend
Suitable: nested table, varray
Exception: COLLECTION_IS_NULL, SUBSCRIPT_BEYOND_LIMIT
First, Last
Suitable: nested table, associative array, varray
Exception: COLLECTION_IS_NULL (uninitialized)
There are two ways to create user-defined collection types :
You can declare the collection type within a PL/SQL program within BLOCK using the TYPE statement
You can define a nested table type or VARRAY type as a schema-level object within the Oracle database by using the CREATE TYPE command .
Declaring a nested table or VARRAY
CREATE [ OR REPLACE ] TYPE type_name AS | IS
TABLE OF element_datatype [ NOT NULL ];
CREATE [ OR REPLACE ] TYPE type_name AS | IS
VARRAY (max_elements) OF element_datatype [ NOT NULL ];
CREATE TYPE list_t AS VARRAY(10) OF VARCHAR2(80);
/
ALTER TYPE list_t MODIFY ELEMENT TYPE VARCHAR2(100) CASCADE;
/
2 TYPE name_t IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
3 old_names name_t;
4 new_names name_t;
6 /* Assign values to old_names table */
7 old_names(1) := 'Smith';
8 old_names(2) := '
Harrison
';
9
10 /* Assign values to new_names table */
11 new_names(111) := 'Hanrahan';
12 new_names(342) := 'Blimey';
13
14 /* Transfer values from new to old */
15 old_names := new_names;
16
17 /* This statement will display 'Hanrahan' */
18 DBMS_OUTPUT.PUT_LINE (
19 old_names.FIRST || ': ' || old_names(old_names.FIRST));
20* END;
111: Hanrahan
A collection-level assignment completely replaces the previously defined rows in the collection. In the preceding example, rows 1 and 2 in old_names are defined before the last, aggregate assignment.
After the assignment, only rows 111 and 342 in the old_names collection have values
Assigning rows from a relational table
TYPE emp_copy_t IS TABLE OF emp%ROWTYPE;
l_emps emp_copy_t := emp_copy_t( );
l_emps.EXTEND;
INTO l_emps (1)
FROM emp
WHERE empno = 7521;
TYPE emp_copy_t IS TABLE OF emp%ROWTYPE INDEX BY PLS_INTEGER;
l_emps emp_copy_t;
FOR emp_rec IN (SELECT * FROM emp)
[3] l_emps (emp_rec.empno) := emp_rec;
END
LOOP
;
TYPE emp_copy_nt IS TABLE OF emp%ROWTYPE;
l_emps emp_copy_nt;
SELECT * BULK COLLECT INTO l_emps FROM emp;
Here are some final notes on the collection caching technique:
This technique is a classic tradeoff between CPU and memory. Each session has its own copy of the collection (this is program data and is stored in the PGA). If you have 10,000 users, the total memory required for these 10,000 small caches could be considerable.
Consider using this technique with any of the following scenarios: small, static tables in a multiuser application; large, static tables in which a given user will access only a small portion of the table; manipulation of large tables in a batch process (just a single connect taking up possibly a lot of memory).
Well, if the data isn't changing especially during a user session then why would I want to keep querying the information from the database? Even if the data is cached in the System Global Area ([4]SGA) , I still need to visit the SGA, confirm that the query has already been parsed, find that information in the data buffers and finally return it to the session program area (the Program Global Area, or P[5]GA).
Unnamed nested collections: multidimensional arrays
/* Files on web: multdim.pkg, multdim.tst, multdim2.pkg */
CREATE OR REPLACE PACKAGE multdim
IS
TYPE dimX_t IS TABLE OF VARCHAR2 (32767) INDEX BY PLS_INTEGER;
TYPE dimY_t IS TABLE OF dimX_t INDEX BY PLS_INTEGER;
TYPE dimZ_t IS TABLE OF dimY_t INDEX BY PLS_INTEGER;
array_in IN OUT dimZ_t,
dimX_in PLS_INTEGER,
dimY_in PLS_INTEGER,
dimZ_in PLS_INTEGER,
value_in IN VARCHAR2
)
array_in(dimZ_in )(dimY_in )(dimX_in) := value_in;
array_in IN dimZ_t,
dimX_in PLS_INTEGER,
dimY_in PLS_INTEGER,
dimZ_in PLS_INTEGER
)
BEGIN
RETURN array_in(dimZ_in )(dimY_in )(dimX_in);
array_in IN dimZ_t,
dimX_in PLS_INTEGER,
dimY_in PLS_INTEGER,
dimZ_in PLS_INTEGER
)
l_value varchar2(32767);
l_value := array_in(dimZ_in )(dimY_in )(dimX_in);
THEN
Working with Collections in SQL
CAST
Maps a collection of one type to a collection of another type. This can encompass mapping a VARRAY to a nested table.
Maps a database table to a collection. With MULTISET and CAST, you can actually retrieve rows from a database table as a collection-typed column.
Maps a collection to a database table. This is the inverse of MULTISET.
The UROWID and ROWID Datatypes
Enter the UROWID datatype . The U in UROWID stands for Universal, and a UROWID variable can contain any type of rowid from any type of table.
We recommend the use of UROWID for all new development involving rowids. The ROWID type provides backward compatibility but can't accommodate all types of rowids now encountered in an Oracle database. UROWID is safer because it accommodates any type of rowid.
employee_rowid UROWID;
employee_salary NUMBER;
--Retrieve employee information that we might want to modify
SELECT rowid, salary INTO employee_rowid, employee_salary
FROM employee
WHERE last_name='Grubbs' AND first_name='John';
[6]An often better way to achieve the same effect as using ROWID in an UPDATE or DELETE statement is to use an explicit cursor to retrieve data, and then use the WHERE CURRENT OF CURSOR clause to modify or delete it. See Chapter 15 for detailed information on this technique.
Only if you delete a row and reinsert itas you might when doing an export followed by an importwill you get a new rowid for a row.
63 The LOB Datatypes
LOB: --- large object datatypes
Binary file. Declares a variable that holds a file locator pointing to an operating-system file outside the database. Oracle treats the data in the file as binary data.
Binary large object. stored inside the database
Character large object, stored inside the database
National Language Support (NLS) character large object, stored inside the database.
[7]LOBs can be categorized as internal or external. Internal LOBs (BLOBs, CLOBs, and NCLOBs) are stored in the database and can participate in a transaction in the database server. External LOBs cannot participate in transactions; in other words, you cannot commit or roll back changes to a BFILE. Instead, you must rely on the underlying filesystem for data integrity.
64 Working with LOBs
falls_name VARCHAR2(80),
falls_photo BLOB,
falls_directions CLOB,
falls_description NCLOB,
falls_web_page BFILE);
Fundamental to working with LOBs is the concept of a LOB locator. A LOB locator is a pointer to large object data in a database.
to retrieve the binary photo data from the falls_photo BLOB column used in the previous example, you would go through the following steps:
Issue a SELECT statement to retrieve the LOB locator for the photo you wish to display.
Open the LOB via a call to DBMS_LOB.OPEN.
Make a call to DBMS_LOB.GETCHUNKSIZE to get the optimal chunk size to use when reading (and writing) the LOB's value.
Make a call to DBMS_LOB.GETLENGTH to get the number of bytes or characters in the LOB value.
Make multiple calls to DBMS_LOB.READ in order to retrieve the LOB data.
Close the LOB.
INSERT INTO waterfalls
(falls_name,falls_directions)
VALUES ('
Munising
Falls
',EMPTY_CLOB( ));
/* File on web: munising_falls_01.sql */
DECLARE
directions CLOB;
amount BINARY_INTEGER;
offset INTEGER;
first_direction VARCHAR2(100);
more_directions VARCHAR2(500);
--Delete any existing rows for '
Munising
Falls
' so that this
--example can be executed multiple times
FROM waterfalls
WHERE falls_name='
Munising
Falls
';
--Insert a new row using EMPTY_CLOB( ) to create a LOB locator
(falls_name,falls_directions)
VALUES ('
Munising
Falls
',EMPTY_CLOB( ));
--Retrieve the LOB locator created by the previous INSERT statement
INTO directions
FROM waterfalls
WHERE falls_name='
Munising
Falls
';
--Open the LOB; not strictly necessary, but best to open/close LOBs.
DBMS_LOB.OPEN(directions, DBMS_LOB.LOB_READWRITE);
--Use DBMS_LOB.WRITE to begin
first_direction := 'Follow I-75 across the Mackinac Bridge.';
amount := LENGTH(first_direction); --number of characters to write
offset := 1; --begin writing to the first character of the CLOB
DBMS_LOB.WRITE(directions, amount, offset, first_direction);
--Add some more directions using DBMS_LOB.WRITEAPPEND
more_directions := ' Take US-2 west from St. Ignace to
Blaney
Park
.'
|| ' Turn north on M-77 and drive to Seney.'
|| ' From Seney, take M-28 west to Munising.';
DBMS_LOB.WRITEAPPEND(directions,
LENGTH(more_directions), more_directions);
--Add yet more directions
more_directions := ' In front of the paper mill, turn right on H-58.'
Washington Street. Veer left onto'
|| '
Washington Street. You''ll find the Munising'
|| ' Falls visitor center across from the hospital at'
|| ' the point where
Washington Streetbecomes'
|| '
Sand Point Road.';
DBMS_LOB.WRITEAPPEND(directions,
LENGTH(more_directions), more_directions);
--Close the LOB, and we are done.
From within PL/SQL and Oracle in general, you can only read BFILEs. Oracle does not allow you to write BFILE data. You must generate the external filesto which BFILE locators pointcompletely outside of the Oracle database.
If you have the CREATE ANY DIRECTORY privilege, you can create a directory alias and grant access to it as follows:
CREATE DIRECTORY bfile_data AS 'c:/PLSQL Book/Ch12_Misc_Datatypes';
GRANT READ ON DIRECTORY bfile_data TO gennick;
web_page BFILE;
--Delete row for
Tannery
Falls
so this example can
--be executed multiple times
DELETE FROM waterfalls WHERE falls_name='
Tannery
Falls
';
--Invoke BFILENAME to create a BFILE locator
web_page := BFILENAME('BFILE_DATA','Tannery Falls.htm');
--Save our new locator in the waterfalls table
INSERT INTO waterfalls (falls_name, falls_web_page)
VALUES ('
Tannery
Falls
',web_page);
65 Transaction Management
The default behavior in a PL/SQL program is that there is one transaction per session, and all changes that you make are a part of that transaction. By using a feature called autonomous transactions , however, you can create nested transactions within the main, session-level transaction. This feature was added to PL/SQL in Oracle8i Database and is covered near the end of this chapter in the section "Autonomous Transactions."
65.1Implicit SQL cursor attributes
Name
Description
SQL%FOUND
Returns TRUE if one or more rows were modified (created, changed, removed) successfully
SQL%NOTFOUND
Returns TRUE if no rows were modified by the DML statement
SQL%ROWCOUNT
Returns number of rows modified by the DML statement
SQL%ISOPEN
Always returns FALSE for implicit cursors because Oracle opens and closes implicit cursors automatically
Use SQL%FOUND to determine if your DML statement affected any rows
CREATE OR REPLACE PROCEDURE change_author_name (
old_name_in IN books.author%TYPE,
new_name_in IN books.author%TYPE,
changes_made_out OUT BOOLEAN)
UPDATE books
SET author = new_name_in
WHERE author = old_name_in;
changes_made_out := SQL%FOUND;
rename_count_out := SQL%ROWCOUNT;
With the RETURNING clause, you can reduce network round trips, consume less server CPU time, and minimize the number of cursors opened and managed in the application
UPDATE employee
SET salary = new_compensation (rec)
WHERE employee_id = rec.employee_id
RETURNING salary, last_name INTO mysal, myname;
The returning clause specifies the values return from DELETE, EXECUTE IMMEDIATE, INSERT, and UPDATE statements. You can retrieve the column values into individual variables or into collections. You cannot use the RETURNING clause for remote or parallel deletes. If the statement does not affect any rows, the values of the variables specified in the RETURNING clause are undefined.
66 DML and Exception Handling
CREATE OR REPLACE PROCEDURE empty_library (
pre_empty_count OUT PLS_INTEGER)
-- The tabCount function returns the number
-- of rows in the specified table, using
-- Native Dynamic SQL. See Chapter 16 for details.
pre_empty_count := tabcount ('books');
DELETE FROM books;
table_count NUMBER := -1;
INSERT INTO books VALUES (...);
empty_library (table_count);
THEN
DBMS_OUTPUT.put_line (tabcount ('books'));
DBMS_OUTPUT.put_line (table_count);
The output is:
-1
[8]Here are some things to keep in mind
If your block is an autonomous transaction (described later in this chapter), then you must perform a rollback or commit (usually a rollback) when an exception is raised.
You can use savepoints to control the scope of a rollback. In other words, you can roll back to a particular savepoint and thereby preserve a portion of the changes made in your session. Savepoints are also explored later in this chapter
If an exception propagates past the outermost block (i.e., it goes "unhandled"), then in most host execution environments for PL/SQL like SQL*Plus, an unqualified rollback is automatically executed, reversing any outstanding changes
67 DML and Records
New to Oracle9i Database Release 2, you can now [9]use records in INSERT and UPDATE statements. Here is an example:
CREATE OR REPLACE PROCEDURE set_book_info(book_in IN books%ROWTYPE)
INSERT INTO books VALUES book_in;
THEN
UPDATE books SET ROW = book_in
WHERE isbn = book_in.isbn;
my_book books%ROWTYPE;
my_book.isbn := '1-56592-335-9';
my_book.title := '
ORACLE PL/SQL PROGRAMMING';
my_book.summary := 'General user guide and reference';
my_book.author := 'FEUERSTEIN, STEVEN AND BILL PRIBYL';
my_book.page_count := 1000;
INSERT INTO books VALUES my_book;
Notice that you do not include parentheses around the record specifier. If you use this format:
INSERT INTO books VALUES (my_book); -- With parentheses, INVALID!
[10]You can also use a record based on a programmer-defined record TYPE to perform the INSERT, but that record type must be 100% compatible with the table %ROWTYPE definition. You may not, in other words, INSERT using a record that covers only a subset of the table's columns
my_book books%ROWTYPE;
my_book.isbn := '1-56592-335-9';
my_book.title := '
ORACLE PL/SQL PROGRAMMING';
my_book.summary := 'General user guide and reference';
my_book.author := 'FEUERSTEIN, STEVEN AND BILL PRIBYL';
my_book.page_count := 980; -- new page count for 3rd edition
SET ROW = my_book
WHERE isbn = my_book.isbn;
There are some [11]restrictions on record-based updates:
You must update an entire row with the ROW syntax. You cannot update a subset of columns (although this may be supported in future releases).
You cannot perform an update using a subquery
68 Bulk DML with the FORALL Statement
Oracle introduced a significant enhancement to PL/SQL's DML capabilities for Oracle8i Database and above with the FORALL statement, allow you to bulk together multiple context switches into a single switch,
TYPE employee_aat IS TABLE OF employee%ROWTYPE
l_employees employee_aat;
FORALL l_index IN l_employees.FIRST .. l_employees.LAST
INSERT INTO employee (employee_id, last_name)
VALUES (l_employees (l_index).employee_id
, l_employees (l_index).last_name
);
[12]FORALL indx IN INDICES OF l_top_employees
EXECUTE IMMEDIATE
'INSERT INTO ' || l_table || ' VALUES (:emp_pky, :new_salary)
USING l_new_salaries(indx).employee_id,
l_new_salaries(indx).salary;
Oracle deposits in the Nth element in this collection the number of rows processed by the Nth execution of the FORALL's INSERT, UPDATE, or DELETE. If no rows were affected, the Nth row will contain a zero value
SQL%BULK_ROWCOUNT
Returns a collection that tells you the number of rows processed by each corresponding SQL statement executed via FORALL. Note: when %BULK_ROWCOUNT(i) is zero, %FOUND and %NOTFOUND are FALSE and TRUE, respectively
TYPE isbn_list IS TABLE OF VARCHAR2(13);
my_books isbn_list := isbn_list (
'1-56592-375-8',
'0-596-00121-5',
'1-56592-849-0',
'1-56592-335-9',
'1-56592-674-9',
'1-56592-675-7',
'0-596-00180-0',
'1-56592-457-6'
);
my_books.FIRST..my_books.LAST
UPDATE books
SET page_count
WHERE isbn = my_books (book_index);
-- Did I update the total number of books I expected?
'We are missing a book!');
-- Did the 4th UPDATE statement affect any rows?
'What happened to Oracle PL/SQL Programming?');;
The FORALL statement and %BULK_ROWCOUNT use the same subscripts or row numbers in the collections. For example, if the collection passed to FORALL has data in rows 10 through 200, then the %BULK_ROWCOUNT pseudo-collection will also have rows 10 through 200 defined and populated. Any other rows will be undefined.
What happens when one of those DML statements fails? Here is the default behavior:
The FORALL statement stops executing. It isn't possible to request that the FORALL skip over the offending statement and continue on to the next row in the collection.
The DML statement being executed is rolled back to an implicit savepoint marked by the PL/SQL engine before execution of the statement.
Any previous DML operations in that FORALL statement that already executed without error are not rolled back.
Continuing Past Exceptions with FORALL
SAVE EXCEPTIONS, which can be used inside a FORALL statement. By including this clause, you instruct Oracle to continue processing even when an error has occurred. Oracle will then "save the exception" (or multiple exceptions, if more than one error occurs). When the DML statement completes, it will then raise the ORA-24381 exception.
/* File on web: bulkexc.sql */
1 CREATE OR REPLACE PROCEDURE bulk_exceptions (
2 whr_in IN VARCHAR2 := NULL)
3 IS
4 TYPE namelist_t IS TABLE OF VARCHAR2 (100);
5 enames_with_errors namelist_t := -- Max of 10 characters in emp.
6 namelist_t ('LITTLE', 'BIGBIGGERBIGGEST', 'SMITHIE', '');
7 bulk_errors EXCEPTION;
8 PRAGMA EXCEPTION_INIT ( bulk_errors, -24381 );
9 BEGIN
10 FORALL indx IN
11 enames_with_errors.FIRST ..
12 enames_with_errors.LAST
13 SAVE EXCEPTIONS
14 EXECUTE IMMEDIATE
15 'UPDATE emp SET ename = :newname'
16 USING enames_with_errors(indx);
17 EXCEPTION
18 WHEN bulk_errors
19 THEN
20 FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
21
LOOP
22 DBMS_OUTPUT.PUT_LINE (
23 'Error ' || indx || ' occurred during ' ||
24 'iteration ' || SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX ||
25 ' updating name to ' ||
26 enames_with_errors (
27 SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX));
28 DBMS_OUTPUT.PUT_LINE (
29 'Oracle error is ' ||
30 SQLERRM (-1 * SQL%BULK_EXCEPTIONS(indx).ERROR_CODE));
31 END
LOOP
;
32 END;
SQL> EXEC bulk_exceptions
Error 1 occurred during iteration 2 updating name to BIGBIGGERBIGGEST
Oracle error is ORA-01401: inserted value too large for column
Error 2 occurred during iteration 4 updating name to
Oracle error is ORA-01407: cannot update () to NULL
In other words, Oracle encountered two exceptions as it processed the DML for the names collection. It did not stop with the first exception, but continued on, cataloging a third
PL/SQL now offers the INDICES OF and VALUES OF clauses, both of which allow you to specify the portion of the binding array to be processed by FORALL.
‘INDICES OF’ Example:
/* File on web: 10g_indices_of.sql */
1 DECLARE
2 TYPE employee_aat IS TABLE OF employee.employee_id%TYPE
3 INDEX BY PLS_INTEGER;
4
5 l_employees employee_aat;
6
7 TYPE boolean_aat IS TABLE OF BOOLEAN
8 INDEX BY PLS_INTEGER;
9
10 l_employee_indices boolean_aat;
11 BEGIN
12 l_employees (1) := 7839;
13 l_employees (100) := 7654;
14 l_employees (500) := 7950;
15 --
16 l_employee_indices (1) := TRUE;
17 l_employee_indices (500) := TRUE;
18 --
19 FORALL l_index IN INDICES OF l_employee_indices
20 UPDATE employee
21 SET salary = 10000
22 WHERE employee_id = l_employees (l_index);
23 END;
24 /
Result:
SQL> SELECT employee_id FROM employee WHERE salary = 10000;
-----------
7839
7950
/* File on web: 10g_values_of.sql */
1 DECLARE
2 TYPE employee_aat IS TABLE OF employee.employee_id%TYPE
3 INDEX BY PLS_INTEGER;
4
5 l_employees employee_aat;
6
7 TYPE indices_aat IS TABLE OF PLS_INTEGER
8 INDEX BY PLS_INTEGER;
9
10 l_employee_indices indices_aat;
11 BEGIN
12 l_employees (-77) := 7820;
13 l_employees (13067) := 7799;
14 l_employees (99999999) := 7369;
15 --
16 l_employee_indices (100) := -77;
17 l_employee_indices (200) := 99999999;
18 --
19 FORALL l_index IN VALUES OF l_employee_indices
20 UPDATE employee
21 SET salary = 10000
22 WHERE employee_id = l_employees (l_index);
23 END;
24 /
Result:
SQL> SELECT employee_id FROM employee WHERE salary = 10000;
-----------
7369
7820
69 Transaction Management
In a read-only transaction, all subsequent queries see only those changes that were committed before the transaction began
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE | READ COMMITTED
When you specify SERIALIZABLE, a data manipulation statement (UPDATE, INSERT, DELETE) that attempts to modify a table already modified in an uncommitted transaction will fail.
If you specify READ COMMITTED[13], a DML statement that requires row-level locks held by another transaction will wait until those row locks are released
The default isolation level is READ COMMITTED, which means that as soon as changes are committed, they are visible to the main transaction
LOCK TABLE table_reference_list IN lock_mode MODE [NOWAIT];
Lock Mode:
SHARE UPDATE
SHARE
SHARE ROW EXCLUSIVE
EXCLUSIVE
1) Locking a table never stops other users from querying or reading the table.
2) If you specify the NOWAIT keyword , Oracle will not wait for the lock if the table has already been locked by another user.
That block becomes an independent transaction that is started by another transaction, referred to as the main transaction.
PRAGMA AUTONOMOUS_TRANSACTION;
These statements have a different scope of impact and visibility when executed within an autonomous transaction, and you will need to include a COMMIT or ROLLBACK in your program.
Rules and Restrictions on Autonomous Transactions
If an autonomous transaction attempts to access a resource held by the main transaction (which has been suspended until the autonomous routine exits), a deadlock can occur in your program.
/* File on web: autondlock.sql */
CREATE OR REPLACE PROCEDURE
update_salary (dept_in IN NUMBER)
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT empno FROM emp
WHERE deptno = dept_in
FOR UPDATE NOWAIT;
LOOP
UPDATE emp SET sal = sal * 2
WHERE empno = rec.empno;
END
LOOP
;
[14]UPDATE emp SET sal = sal * 2;
update_salary (10);
The results are not pretty:
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
To exit without errors from an autonomous transaction program that has executed at least one INSERT, UPDATE or DELETE, you must perform an explicit commit or rollback.
The COMMIT and ROLLBACK statements end the active autonomous transaction, but they do not force the termination of the autonomous routine. You can, in fact, have multiple COMMIT and/or ROLLBACK statements inside your autonomous block
You must indicate autonomous transactions explicitly in each program(procedure)
You can roll back only to savepoints marked in the current transaction. When you are in an autonomous transaction, you cannot roll back to a savepoint set in the main transaction
When to Use Autonomous Transactions
Logging mechanism
This is probably the most common motivation for PL/SQL developers to use autonomous transactions and is explored at the end of this section.
Perform commits and rollbacks in your database triggers
Suppose that you want to keep track of each action against a table, whether or not the action completed. You might even want to be able to detect which actions failed.
Reusable application components
As we move more and more into the dispersed, multilayered world of the Internet, it becomes ever more important to be able to offer standalone units of work (also known as cartridges) that get their job done without any side effects on the calling environment
Call user-defined functions in SQL that modify tables
Oracle lets you call your own functions inside a SQL statement, you define your function as an autonomous transaction, you will then be able to insert, update or delete inside that function as it is run from within a query.
Retry counter.
Suppose that you want to let a user try to get access to a resource N times before an outright rejection
Building an Autonomous Logging Mechanism
/* File on web: log.pkg */
CREATE TABLE logtab (
code INTEGER,
text VARCHAR2(4000),
created_on DATE,
created_by VARCHAR2(100),
changed_on DATE,
changed_by VARCHAR2(100),
machine VARCHAR2(100),
program VARCHAR2(100)
);
/* File on web: log.pkg */
CREATE OR REPLACE PACKAGE BODY log
IS
PROCEDURE putline (
code_in IN INTEGER, text_in IN VARCHAR2)
INSERT INTO logtab
code_in,
text_in,
SYSDATE,
USER,
SYSDATE,
USER
);
code_in IN INTEGER, text_in IN VARCHAR2)
PRAGMA AUTONOMOUS_TRANSACTION;
putline (code_in, text_in);
EXCEPTION WHEN OTHERS THEN ROLLBACK;
With this package in place, my error handler shown earlier can be as simple as this:
log.saveline (SQLCODE, SQLERRM);
70 Data Retrieval
Parse
Bind
Open
Execute
Fetch
Close
71 Choosing Between Explicit and Implicit Cursors
In some cases, explicit cursors will be more efficient. You should test your critical, often-executed queries in both formats to see which will be better in that particular situation
Explicit cursors offer much tighter programmatic control. If a row is not found, for example, Oracle will not raise an exception, instead forcing the execution block to shut down
I suggest that the question to answer is not "implicit or explicit?," but rather, "encapsulate or expose?" And the answer is (new wisdom revealed):
You should always encapsulate your single-row query, hiding the query behind a function interface, and passing back the data through the RETURN clause.
Don't worry about explicit versus implicit. Instead, worry about how you can tune and maintain your code if single-row queries are duplicated throughout your code. Take the time to encapsulate them behind functions, preferably package-based functions.
72 Declaring cursors in packages
By defining cursors in packages, you can more easily reuse those queries and avoid writing the same logical retrieval statement over and over again throughout your application
You will also realize some performance gains by minimizing the number of times your queries will need to be parsed.
If you declare cursors in packages for reuse, you need to be aware of one important factor. Data structures, including cursors, that are declared at the "package level" (not inside any particular function or procedure) maintain their values or persist for your entire session. This means that a packaged cursor will stay until you explicitly close it or until your session ends. Cursors declared in local blocks of code close automatically when that block terminates execution.
CREATE OR REPLACE PACKAGE book_info
IS
CURSOR books_cur (title_filter_in IN book.title%TYPE)
RETURN book%ROWTYPE
CREATE OR REPLACE PACKAGE BODY book_info
IS
CURSOR books_cur (title_filter_in IN book.title%TYPE)
RETURN book%ROWTYPE
WHERE title LIKE title_filter_in;
1) Hide information
2) Minimize recompilation
If the SELECT statement in your cursor uses a FOR UPDATE clause, all the rows identified by the query are locked when the cursor is opened.
If you have opened a SELECT FOR UPDATE query, it is especially important to close the cursor as soon as you are done because this query causes row-level locks to be applied.
CURSOR company_cur is SELECT ...;
company_rec company_cur%ROWTYPE;
OPEN company_cur;
FETCH company_cur INTO company_rec;
You should always fetch into a record that was defined with %ROWTYPE against the cursor; avoid fetching into lists of variables. Fetching into a record usually means that you write less code and have more flexibility to change the select list without having to change the FETCH statement.
CREATE OR REPLACE PACKAGE bookinfo_pkg
IS
CURSOR bard_cur
IS SELECT title, date_published
FROM books
WHERE UPPER(author) LIKE 'SHAKESPEARE%';
END bookinfo_pkg;
bard_rec bookinfo_pkg.bard_cur%ROWTYPE;
-- Check to see if the cursor is already opened.
-- This is possible, since it is a packaged cursor.
-- If so, first close it and then re-open it to
-- ensure a "fresh" result set.
IF bookinfo_pkg.bard_cur%ISOPEN
THEN
CLOSE bookinfo_pkg.bard_cur;
-- Fetch each row, but stop when I've displayed the
-- first five works by Shakespeare or when I have
-- run out of rows.
FETCH bookinfo_pkg.bard_cur INTO bard_rec;
EXIT WHEN bookinfo_pkg.bard_cur%NOTFOUND
OR bookinfo_pkg.bard_cur%ROWCOUNT = 6;
bcur%ROWCOUNT
|| ') '
|| rec.title
|| ', published in '
|| TO_CHAR (rec.date_published, 'YYYY')
);
END
LOOP
;
CLOSE bard_cur;
PROCEDURE explain_joke (main_category_in IN joke_category%TYPE)
/*
|| Cursor with parameter list consisting of a single
|| string parameter.
*/
CURSOR joke_cur (category_in VARCHAR2)
SELECT name, category, last_used_date
FROM joke
WHERE category = UPPER (category_in);
joke_rec joke_cur%ROWTYPE;
/* Now when I open the cursor, I also pass the argument */
OPEN joke_cur (main_category_in);
FETCH joke_cur INTO joke_rec;
OPEN joke_cur (jokes_pkg.category);
OPEN joke_cur ('husband');
OPEN joke_cur ('politician');
OPEN joke_cur (jokes_pkg.relation || ' IN-LAW');
The most common place to use a parameter in a cursor is in the WHERE clause, but you can make reference to it anywhere in the SELECT statement, as shown here
With the restriction that a cursor parameter can be an IN parameter only. You cannot specify OUT or IN OUT modes for cursor parameters
73 BULK COLLECT
Example-1:
IS
SELECT name, mileage
FROM transportation
WHERE TYPE = 'AUTOMOBILE'
AND mileage < 20;
names name_varray;
mileages number_varray;
FOR bad_car IN major_polluters
LOOP
names.EXTEND;
names (major_polluters%ROWCOUNT) := bad_car.NAME;
mileages.EXTEND;
mileages (major_polluters%ROWCOUNT) := bad_car.mileage;
END
LOOP
;
-- Now work with data in the collections
END;
You can use BULK COLLECT with both dynamic and static SQL
You can use BULK COLLECT keywords in any of the following clauses: SELECT INTO, FETCH INTO, and RETURNING INTO
Example-2:
TYPE transportation_aat IS TABLE OF transportation%ROWTYPE
FROM transportation
BULK COLLECT INTO l_transportation
WHERE TYPE = 'AUTOMOBILE'
AND mileage < 20;
-- Now work with data in the collections
END;
Example-3:
SELECT name, mileage
FROM transportation
WHERE TYPE = 'AUTOMOBILE'
AND mileage < 20;
names name_varray;
mileages number_varray;
OPEN major_polluters;
FETCH major_polluters BULK COLLECT INTO names, mileages;
CLOSE major_polluters;
-- Now work with data in the collections
Example-4:
CURSOR allrows_cur IS SELECT * FROM EMPLOYEE;
TYPE employee_aat IS TABLE OF allrows_cur%ROWTYPE
l_employees employee_aat;
l_row PLS_INTEGER;
OPEN allrows_cur;
FETCH allrows_cur BULK COLLECT INTO l_employees
LIMIT 100;
EXIT WHEN l_employees.COUNT = 0;
-- Process the data by scanning through the collection.
l_row := l_employees.FIRST;
WHILE (l_row IS NOT NULL)
upgrade_employee_status (l_employees(l_row).employee_id);
l_row := l_employees.NEXT (l_row);
END
LOOP
;
END
LOOP
;
CLOSE allrows_cur;
74 Bulk Fetching of Multiple Columns(*)
This feature became available in Oracle9i Database Release 2.
-- Declare the type of collection
TYPE VehTab IS TABLE OF transportation%ROWTYPE;
-- Instantiate a particular collection from the TYPE.
gas_guzzlers VehTab;
BULK COLLECT INTO gas_guzzlers
FROM transportation
WHERE mileage < 20;
...
75 RETURNING Clause with Bulk Operations
big_bucks NUMBER := salforexec ('CEO');
min_sal NUMBER := big_bucks / 50;
names name_varray;
old_salaries number_varray;
new_salaries number_varray;
CURSOR affected_employees (ceosal IN NUMBER)
SELECT name, salary + bonus old_salary
FROM compensation
WHERE title != 'CEO'
AND ((salary + bonus < ceosal / 50)
OR (salary + bonus > ceosal / 10)) ;
OPEN affected_employees (big_bucks);
FETCH affected_employees
BULK COLLECT INTO names, old_salaries;
FORALL indx IN names.FIRST .. names.L*
UPDATE compensation
SET salary =
GREATEST (min_sal, salary),
min_sal, min_sal,
salary / 5),
min_sal )
WHERE name = names (indx)
RETURNING salary BULK COLLECT INTO new_salaries;
76 SELECT ... FOR UPDATE
Oracle automatically obtains exclusive row-level locks on all the rows identified by the SELECT statement, holding the records "for your changes only" as you move through the rows retrieved by the cursor. No one else will be able to change any of these records until you perform a ROLLBACK or a COMMIT but other sessions can still read the data.
SELECT name, manufacturer, preference_level, sell_at_yardsale_flag
FROM my_sons_collection
WHERE hours_used = 0
FOR UPDATE;
SELECT task, expected_hours, tools_required, do_it_yourself_flag
FROM winterize
WHERE year = TO_CHAR (SYSDATE, 'YYYY')
FOR UPDATE OF [15]task;
If you simply state FOR UPDATE in the query and do not include one or more columns after the OF keyword, the database will then lock all identified rows across all tables listed in the FROM clause.
You can append the optional keyword NOWAIT to the FOR UPDATE clause ,Without the NOWAIT clause, your process will block until the table is available
SET set_clause
WHERE CURRENT OF cursor_name;
WHERE CURRENT OF cursor_name;
77 REF CURSOR Types
TYPE company_curtype IS REF CURSOR [16]RETURN company%ROWTYPE;
TYPE generic_curtype IS [17]REF CURSOR;
Oracle provides a predefined weak REF CURSOR type named SYS_REFCURSOR
my_cursor SYS_REFCURSOR;
OPEN cursor_name FOR select_statement;
TYPE emp_curtype IS REF CURSOR;
emp_curvar emp_curtype;
OPEN emp_curvar FOR SELECT * FROM emp;
OPEN emp_curvar FOR SELECT employee_id FROM emp;
OPEN emp_curvar FOR SELECT company_id, name FROM company;
If the cursor variable is of the weak REF CURSOR type, the PL/SQL compiler cannot perform the same kind of check it performs for a strong REF CURSOR type
TYPE building_curtype IS REF CURSOR;
(address_in IN VARCHAR2,
site_cur_inout IN OUT building_curtype)
home_type CONSTANT INTEGER := 1;
commercial_type CONSTANT INTEGER := 2;
/* A static cursor to get building type. */
CURSOR site_type_cur IS
SELECT site_type FROM property_master
WHERE address = address_in;
site_type_rec site_type_cur%ROWTYPE;
/* Get the building type for this address. */
OPEN site_type_cur;
FETCH site_type_cur INTO site_type_rec;
CLOSE site_type_cur;
/* Now use the site type to select from the right table.*/
IF site_type_rec.site_type = home_type
THEN
/* Use the home properties table. */
OPEN site_cur_inout FOR
SELECT * FROM home_properties
WHERE address LIKE '%' || address_in || '%';
ELSIF site_type_rec.site_type = commercial_type
THEN
/* Use the commercial properties table. */
OPEN site_cur_inout FOR
SELECT * FROM commercial_properties
WHERE address LIKE '%' || address_in || '%';
TYPE curvar_type IS REF CURSOR;
curvar1 curvar_type;
curvar2 curvar_type;
story fairy_tales%ROWTYPE;
OPEN curvar1 FOR SELECT * FROM fairy_tales;
curvar2 := curvar1;
FETCH curvar1 INTO story;
FETCH curvar2[MSOffice8] INTO story;
CLOSE curvar2;
FETCH curvar1[MSOffice9] INTO story;
/* Define weak REF CURSOR type, cursor variable
and local variable */
TYPE curvar_type IS REF CURSOR;
curvar1 curvar_type;
do_you_get_it VARCHAR2(100);
/*
|| Nested block which creates the cursor object and
|| assigns it to the curvar1 cursor variable.
*/
curvar2 curvar_type;
OPEN curvar2 FOR SELECT punch_line FROM jokes;
curvar1 := curvar2;
/*
|| The curvar2 cursor variable is no longer active,
|| but "the baton" has been passed to curvar1, which
|| does exist in the enclosing block. I can therefore
|| fetch from the cursor object, through this other
|| cursor variable.
*/
FETCH curvar1 INTO do_you_get_it;
CLOSE curvar1;
Passing Cursor Variables as Arguments
IS
/* Define the REF CURSOR type. */
TYPE curvar_type IS REF CURSOR RETURN company%ROWTYPE;
END package;
PROCEDURE open_company (curvar_out [18]OUT company.curvar_type) IS
...
CREATE OR REPLACE PROCEDURE emp_report (p_locid NUMBER)
-- The query returns only 2 columns, but the second column is
-- a cursor that lets us traverse a set of related information.
SELECT l.city,
CURSOR (SELECT d.department_name,
FROM employees e
WHERE e.department_id =
d.department_id)
FROM departments d
WHERE l.location_id = d.location_id) AS dname
FROM locations l
WHERE l.location_id = p_locid;
departments_cur refcursor;
employees_cur refcursor;
v_city locations.city%TYPE;
v_dname departments.department_name%TYPE;
v_ename employees.last_name%TYPE;
FETCH all_in_one_cur INTO v_city, departments_cur;
EXIT WHEN all_in_one_cur%NOTFOUND;
-- Now I can loop through departments and I do NOT need to
-- explicitly open that cursor. Oracle did it for me.
FETCH departments_cur INTO v_dname, employees_cur;
EXIT WHEN departments_cur%NOTFOUND;
-- Now I can loop through employees for that department.
-- Again, I do not need to open the cursor explicitly.
FETCH employees_cur INTO v_ename;
EXIT WHEN employees_cur%NOTFOUND;
DBMS_OUTPUT.put_line (
v_city
|| ' '
|| v_dname
|| ' '
|| v_ename
);
END
LOOP
;
END
LOOP
;
END
LOOP
;
CLOSE all_in_one_cur;
78 Dynamic SQL
/* File on web: tabcount_nds.sf */
CREATE OR REPLACE FUNCTION tabcount (
tab IN VARCHAR2, whr IN VARCHAR2 := NULL)
RETURN PLS_INTEGER AUTHID CURRENT_USER
str VARCHAR2 (32767) := 'SELECT COUNT(*) FROM ' || tab;
retval PLS_INTEGER;
str := str || ' WHERE ' || whr;
EXECUTE IMMEDIATE str INTO retval;
'TABCOUNT ERROR: ' || DBMS[B10] _UTILITY.FORMAT_ERROR_STACK);
DBMS_OUTPUT.put_line (str);
/
/* File on web: updnval.sf */
CREATE OR REPLACE FUNCTION updNVal (
col IN VARCHAR2,
val IN NUMBER,
start_in IN DATE,
end_in IN DATE)
'UPDATE employee SET ' || col || ' = :the_value
WHERE hire_date BETWEEN :lo AND :hi'
USING val, start_in, end_in;
/* File on web: run9am.sp */
CREATE OR REPLACE PROCEDURE run_9am_procedure (
id_in IN employee.employee_id%TYPE,
hour_in IN INTEGER)
v_apptCount INTEGER;
v_name VARCHAR2(100);
'BEGIN ' || TO_CHAR (SYSDATE, 'DAY') ||
'_set_schedule (:id, :hour, :name, :appts); END;'
USING IN id_in, IN hour_in, OUT v_name, OUT v_apptCount;
'Employee ' || v_name || ' has ' || v_apptCount ||
' appointments on ' || TO_CHAR (SYSDATE));
.sp */
CREATE OR REPLACE PROCEDURE showcol (
tab IN VARCHAR2,
col IN VARCHAR2,
whr IN VARCHAR2)
TYPE cv_type IS REF CURSOR;
cv cv_type;
val VARCHAR2(32767);
PROCEDURE display_header_info ... END;
/* Construct the very dynamic query and open the cursor. */
OPEN cv FOR
'SELECT ' || col ||
' FROM ' || tab ||
' WHERE ' || whr;
/* Fetch the next row, and stop if no more rows. */
FETCH cv INTO val;
EXIT WHEN cv%NOTFOUND;
/* Display the data, with a header before the first row. */
IF cv%ROWCOUNT = 1
THEN
display_header_info (tab, col);
DBMS_OUTPUT.PUT_LINE (val);
END
LOOP
;
CLOSE cv; --All done, so clean up!
'SELECT ' || col ||
' FROM ' || tab ||
' WHERE ' || dtcol ||
Wrong way to using null, because NULL is not data type.
'UPDATE employee SET salary = :newsal
WHERE hire_date IS NULL'
USING NULL;
You will, however, get this error:
PLS-00457: in USING clause, expressions have to be of SQL types
Hide the NULL value behind a variable façade
DECLARE
/* Default initial value is NULL */
no_salary_when_fired NUMBER;
'UPDATE employee SET salary = :newsal
WHERE hire_date IS NULL'
USING no_salary_when_fired;
Use a conversion function to convert the NULL value to a typed value explicitly
BEGIN
'UPDATE employee SET salary = :newsal
WHERE hire_date IS NULL'
Replace Repetitive Code with Dynamic Block
CREATE OR REPLACE PROCEDURE process_line (line IN INTEGER)
IF line = 1 THEN process_line1;
ELSIF line = 2 THEN process_line2;
...
ELSIF line = 514 THEN process_line514;
...
ELSIF line = 2057 THEN process_line2057;
CREATE OR REPLACE PROCEDURE process_line (line IN INTEGER)
[B11] 'BEGIN process_line' || line || '; END;';
CREATE OR REPLACE PROCEDURE get_rows (
table_in IN VARCHAR2
, where_in IN VARCHAR2
)
'SELECT * FROM ' || table_in || ' WHERE ' || where_in
INTO l_row;
CREATE OR REPLACE PROCEDURE get_rows (
table_in IN VARCHAR2
, where_in IN VARCHAR2
)
l_row ' || table_in || '%ROWTYPE;
FROM ' || table_in || ' WHERE ' || where_in || ';
Use Binding Rather Than Concatenation
Table 6
Binding
Concatenation
EXECUTE IMMEDIATE
'UPDATE ' ||
tab 'SET sal = :new_sal' USING v_sal;
EXECUTE IMMEDIATE
'UPDATE ' ||
tab 'SET sal = ' ||
v_sal;
1) Binding is usually faster
2) Binding is easier to write and maintain
3) Binding helps avoid implicit conversions
4) Binding negates the chance of code injection
Avoid Code Injection with Binding
BEGIN
get_rows ('EMPLOYEE'
,'employee_id=7369;
EXECUTE IMMEDIATE ''CREATE TABLE nasty_data (mycol NUMBER)''' );[19]
79 Benefits of Local Modularization
1) To reduce the size of the module by stripping it of repetitive code
2) To improve the readability of your code
PROCEDURE calc_percentages (tot_sales_in IN NUMBER)
l_profile sales_descriptors%ROWTYPE;
/* Define a function right inside the procedure! */
FUNCTION pct_stg (val_in IN NUMBER) RETURN VARCHAR2
IS
BEGIN
RETURN TO_CHAR ((val_in/tot_sales_in ) * 100, '$999,999');
l_profile.food_sales_stg := pct_stg (sales_pkg.food_sales);
l_profile.service_sales_stg := pct_stg (sales_pkg.service_sales);
l_profile.toy_sales_stg := pct_stg (sales_pkg.toy_sales);
The big difference between local modules and package modules is their scope. Local modules can be called only from within the block in which they are defined; package modules can not a minimumbe called from anywhere in the package. If the package modules are also listed in the package specification, they can be called by any other program in your application.
80 Advanced Topics
80.1Calling Your Function Inside SQL
All of the function's parameters must use the IN mode. Neither IN OUT nor OUT
The stored function may not modify database tables. It cannot execute an INSERT, DELETE, or UPDATE statement
The stored function may not call another module (stored procedure or function) that breaks any of the preceding rules
80.2Replacing DECODEs with IF statements
SELECT FC.year_number,
SUM (DECODE (GREATEST (ship_date, FC.q1_sdate),
ship_date,
DECODE (LEAST (ship_date, FC.q1_edate),
ship_date, 1,
0),
0)) Q1_results,
SUM (DECODE (GREATEST (ship_date, FC.q2_sdate),
ship_date,
DECODE (LEAST (ship_date, FC.q2_edate),
ship_date, 1,
0),
0)) Q2_results,
SUM (DECODE (GREATEST (ship_date, FC.q3_sdate),
ship_date,
DECODE (LEAST (ship_date, FC.q3_edate),
ship_date, 1,
0),
0)) Q3_results,
SUM (DECODE (GREATEST (ship_date, FC.q4_sdate),
ship_date,
DECODE (LEAST (ship_date, FC.q4_edate),
ship_date, 1,
0),
0)) Q4_results
FROM orders O,
fiscal_calendar FC
GROUP BY year_number;
(ship_date_in IN DATE, sdate_in IN DATE, edate_in IN DATE)
IF ship_date_in BETWEEN sdate_in AND edate_in
THEN
RETURN 1;
RETURN 0;
SELECT FC.year_number,
SUM (incr_in_range (ship_date, q1_sdate, q1_edate)) Q1_results,
SUM (incr_in_range (ship_date, q2_sdate, q2_edate)) Q2_results,
SUM (incr_in_range (ship_date, q3_sdate, q3_edate)) Q3_results,
SUM (incr_in_range (ship_date, q4_sdate, q4_edate)) Q4_results
FROM orders O,
fiscal_calendar FC
GROUP BY year_number;
Section 17.8
A function is called deterministic if it returns the same result value whenever it is called with the same values for its arguments.
CREATE OR REPLACE FUNCTION betwnStr (
string_in IN VARCHAR2,start_in IN INTEGER, end_in IN INTEGER
) RETURN VARCHAR2 DETERMINISTIC IS
string_in, start_in, end_in - start_in + 1));
When I next call the function with those arguments, it can return the result without executing the function!
81 Package
81.1Execute complex initialization logic
If the assignment of the default value raises an exception, that exception cannot be trapped within the package: it will instead propagate out unhandled. This issue is covered in more detail in the section, "When initialization fails."
81.2When initialization fails
/* File on web: valerr.pkg */
CREATE OR REPLACE PACKAGE valerr
IS
FUNCTION get RETURN VARCHAR2;
END valerr;
/
CREATE OR REPLACE PACKAGE BODY valerr
IS
-- A package-level, but private global variable
v VARCHAR2(1) := 'ABC';
IS
BEGIN
RETURN v;
DBMS[B12] _OUTPUT.PUT_LINE ('Before I show you v...');
DBMS_OUTPUT.PUT_LINE ('Trapped the error!');
END valerr;
Suppose that I connect to SQL*Plus and try to run the valerr.get function (for the first time in that session). This is what I see:
SQL> exec DBMS_OUTPUT.PUT_LINE (valerr.get)
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
In other words, my attempt in the declaration of the v variable to assign a value of "ABC" caused a VALUE_ERROR exception. The exception section at the bottom of the package did not trap the error; it can only trap errors raised in the initialization section itself. And so the exception goes unhandled. Notice, however, that when I call that function a second time in my session, I do not get an error:
2 DBMS_OUTPUT.PUT_LINE ('V is set to ' || NVL (valerr.get, 'NULL'));
4 /
V is set to NULL
Cache static session information
If you want to make sure that the information is retrieved just once in your session, then the initialization section is an ideal, automatically managed way to get this to happen.
This is accomplished by moving the data closer to the user, into the Program Global Area of each user. If there are 1,000 distinct sessions, then there are 1,000 copies of the cached data. This agreement decreases the CPU usage, but takes up more memory.
CREATE OR REPLACE PACKAGE BODY company IS
BEGIN
/*
|| Initialization section of company_pkg updates the global
|| package data of a different package. This is a no-no!
*/
SELECT SUM (salary)
INTO employee_pkg.max_salary
FROM employee;
END company;
In the PL/SQL runtime architecture, package data structures persist (hold their values) for the duration of a session (rather than the duration of execution for a particular program).
If package data is declared inside the package body, then that data persists for the session but can be accessed only by elements defined in the package itself (private data).
They are accessible only within a single Oracle session or connection; package data is not shared across sessions.
If you need to share data between different Oracle sessions, you can use the DBMS_PIPE package or Oracle Advanced Queuing
Encapsulating Data Manipulation
Gain many of the advantages of data encapsulation without having to completely revamp your coding techniques. At a minimum, I suggest that you:
Hide all your single-row queries behind a function interface. That way, you can make sure that error handling is performed and can choose the best implementation (implicit or explicit cursors, for example).
Identify the tables that are most frequently and directly manipulated by developers and build layers of code around them
Create packaged programs to handle complex transactions. If "add a new order" involves inserting two rows, updating six others, and so on, make sure to embed this logic inside a procedure that handles the complexity
/* File on web: givebonus2.sp */
1 CREATE OR REPLACE PROCEDURE give_bonus (
2 dept_in IN employee_tp.department_id_t
3 , bonus_in IN employee_tp.bonus_t
4 )
5 IS
6 l_department department_tp.department_rt;
7 l_employees employee_tp.employee_tc;
8 l_row PLS_INTEGER;
9 l_rows_updated PLS_INTEGER;
10 BEGIN
11 l_department := department_qp.onerow (dept_in);
12 l_employees := employee_qp.ar_fk_emp_department (dept_in);
13
14 l_row := l_array.FIRST;
15
16 WHILE (l_row IS NOT NULL)
17
LOOP
18 IF employee_rp.eligible_for_bonus (rec)
19 THEN
20 employee_cp.upd_onecol_pky
21 (colname_in => 'salary'
22 , new_value_in => l_employees (l_row).salary
23 + bonus_in
24 , employee_id_in => l_employees (l_row).employee_id
25 , rows_out => l_rows_updated
26 );
27 END IF;
28
29 l_row := l_employees.NEXT (l_row);
30 END
LOOP
;
31
32 ... more processing with name and other elements
33 * END;
CREATE OR REPLACE PACKAGE config_pkg
IS
closed_status CONSTANT VARCHAR2(1) := 'C';
open_status CONSTANT VARCHAR2(1) := 'O';
active_status CONSTANT VARCHAR2(1) := 'A';
inactive_status CONSTANT VARCHAR2(1) := 'I';
min_difference CONSTANT PLS_INTEGER := 1;
max_difference CONSTANT PLS_INTEGER := 100;
earliest_date CONSTANT DATE := SYSDATE;
latest_date CONSTANT DATE := ADD_MONTHS (SYSDATE, 120);
BETWEEN config_pkg.min_difference and config_pkg.max_difference
adjust_line_item;
IF cust_status = config_pkg.closed_status
THEN
reopen_customer;
82 Packages and Object Types
Key differences between the two, including the following
An object type is a template for data; you can instantiate multiple object type instances (a.k.a. "objects") from that template
In Oracle9i Database and Oracle Database 10g, object types offer inheritance
With packages, you can create private, hidden data and programs. This is not supported in object types, in which everything is publicly declared and accessible (although you can still hide the implementation of methods in the object type body).
83 Trigger
By default, DML triggers participate in the transaction from which they were fired. This means that:
If a trigger raises an exception, that part of the transaction will be rolled back.
If the trigger performs any DML itself (such as inserting a row into a log table), then that DML becomes a part of the main transaction.
You cannot issue a COMMIT or ROLLBACK from within a DML trigger.
84 Recompiling Invalid Code
Oracle uses its dependency-related views (such as PUBLIC_DEPENDENCIES) to identify all objects that depend on the changed object. It then marks those dependent objects as INVALID, essentially throwing away any compiled code.
84.1Recompile individual program units
ALTER FUNCTION a_function COMPILE REUSE SETTINGS;
ALTER PACKAGE my_package COMPILE REUSE SETTINGS;
ALTER PACKAGE my_package COMPILE SPECIFICATION REUSE SETTINGS;
ALTER PACKAGE my_package COMPILE BODY REUSE SETTINGS;
BULK recompile:
SELECT 'ALTER ' || object_type || ' ' || object_name
|| ' COMPILE REUSE SETTINGS;'
FROM all_objects
WHERE status = 'INVALID';
The problem with this "bulk" approach is that as you recompile one invalid object, you may cause many others to be marked INVALID
The UTL_RECOMP built-in package offers two programs that you can use to recompile any invalid objects in your schema: RECOMP_SERIAL and RECOMP_PARALLEL
If you have multiple processors, the parallel version may help you complete your recompilations more rapidly
85 Optimizing PL/SQL Performance
Use BULK COLLECT when querying multiple rows
Use FORALL when modifying multiple rows
Use the NOCOPY hint when passing large structures
Use PLS_INTEGER for intensive integer computations
Use BINARY_FLOAT or BINARY_DOUBLE for floating-point arithmetic
Group together related programs in a package
Pin into shared memory large and frequently executed programs
Pin frequently accessed programs in the shared memory pool with the DBMS_SHARED_POOL.PIN procedure
86 Call Java
86.1LoadJava Commander:
loadjava {-user | -u} username/password[@database]
[option ...] filename [filename ]...
loadjava -user bxu/j777v@jdba -resolve C:/eclipse-3.1.1/workspace/ReportGroupItinerary/
INF/classes/com/jonview/report/JDBCConnect.class
86.2Publishing and Using Java in PLSQL
CREATE [OR REPLACE] --If standalone (not in a package)
Standard PL/SQL procedure/function header
{IS | AS} LANGUAGE JAVA
NAME 'method_fullname (java_type[, java_type]...)
[return java_type]';
/* File on web: fdelete.sf */
CREATE OR REPLACE FUNCTION fDelete (
file IN VARCHAR2)
NAME 'JDelete.delete (
java.lang.String)
return int';
dropjava {-user | -u} username/password[@database]
[option ...] filename [filename] ...
An object name in Oracle, whether the name of a database table or a Java class, cannot be longer than 30 characters. Java does not have this restriction; you can have much longer names. Oracle will allow you to load a Java class into Oracle with a name of up to 4,000 characters.
SELECT object_name, object_type, status, timestamp
FROM all_objects
WHERE (object_name NOT LIKE 'SYS_%'
AND object_name NOT LIKE 'CREATE$%'
AND object_name NOT LIKE 'JAVA$%'
AND object_name NOT LIKE 'LOADLOB%')
AND object_type LIKE 'JAVA %'
ORDER BY object_type, object_name;
87 Avoiding Boolean Flags
IF condition1 AND condition2 THEN
...
...
PL/SQL can stop evaluation of the expression when condition1 is FALSE or NULL
IF :customer.order_total > max_allowable_order
THEN
order_exceeds_balance := TRUE;
order_exceeds_balance := FALSE;
order_exceeds_balance := :customer.order_total > max_allowable_order;
my_boolean := condition1 AND condition2
Unlike the case with an IF statement, when condition1 is NULL, this expression will not short-circuit. Why not? Because the result could be either NULL or FALSE, depending on condition2. For an IF statement, NULL and FALSE both lead to the ELSE branch, so a short-circuit can occur. But for an assignment, the ultimate value must be known, and short-circuiting, in this case, can (and will) occur only when condition1 is FALSE.
IF low_CPU_condition AND high_CPU_condition
THEN
...
The low_CPU_condition is evaluated first, and if the result is enough to determine the end result of the AND operation (i.e., the result is FALSE), the more expensive condition will not be evaluated, and your application's performance is the better for that evaluation not happening.
THEN
IF high_CPU_condition
THEN
...
88 CASE statement
88.1Simple CASE Statements
WHEN result1 THEN
statements1
WHEN result2 THEN
statements2
...
statements_else
END CASE;
WHEN 'S' THEN
award_salary_bonus(employee_id);
award_hourly_bonus(employee_id);
award_commissioned_bonus(employee_id);
RAISE invalid_employee_type;
statements1
WHEN expression2 THEN
statements2
...
statements_else
END CASE;
WHEN salary >= 10000 AND salary <=20000 THEN
give_bonus(employee_id, 1500);
WHEN salary > 20000 AND salary <= 40000
give_bonus(employee_id, 1000);
WHEN salary > 40000
give_bonus(employee_id, 500);
give_bonus(employee_id, 0);
Execution ends once a sequence of statements has been executed. If more than one expression evaluates to TRUE, only the statements associated with the first such expression are executed
The ELSE clause is optional. If no ELSE is specified, and no expressions evaluate to TRUE, then a CASE_NOT_FOUND exception is raised
WHEN clauses are evaluated in order, from top to bottom
Not recommend:
give_bonus(employee_id, 500);
WHEN salary > 20000 THEN
give_bonus(employee_id, 1000);
WHEN salary >= 10000 THEN
give_bonus(employee_id, 1500);
give_bonus(employee_id, 0);
SELECT CASE
89 Cursor FOR Loops
1 DECLARE
2 CURSOR occupancy_cur IS
3 SELECT pet_id, room_number
4 FROM occupancy WHERE occupied_dt = TRUNC (SYSDATE);
5 occupancy_rec occupancy_cur%ROWTYPE;
6 BEGIN
7 OPEN occupancy_cur;
8
LOOP
9 FETCH occupancy_cur INTO occupancy_rec;
10 EXIT WHEN occupancy_cur%NOTFOUND;
11 update_bill
12 (occupancy_rec.pet_id, occupancy_rec.room_number);
13 END
LOOP
;
14 CLOSE occupancy_cur;
15 END;
SELECT pet_id, room_number
FROM occupancy WHERE occupied_dt = TRUNC (SYSDATE);
FOR occupancy_rec IN occupancy_cur
LOOP
update_bill(occupancy_rec.pet_id, occupancy_rec.room_number);
END
LOOP
;
90
Loop
Labels
<
>
FOR year_number IN 1800..1995
LOOP
<
>
FOR month_number IN 1 .. 12
LOOP
IF year_loop.year_number = 1900 THEN ... END IF;
END LOOP month_loop;
END LOOP year_loop;
You can, in fact, stop the execution of a specific named outer loop by adding a loop label after the EXIT keyword in the EXIT statement of a loop,
EXIT loop_label;
EXIT loop_label WHEN condition;
91 Use Understandable Names for
Loop
Indexes
FOR focus_account IN start_id .. end_id
LOOP
FOR day_in_week IN 1 .. 7
LOOP
FOR month_in_biyear IN 1 .. 24
LOOP
build_schedule(focus_account,day_in_week,month_in_biyear);
END
LOOP
;
END
LOOP
;
END
LOOP
;
92 Obtaining Information About FOR Loop Execution
book_count PLS_INTEGER;
FOR book_rec IN books_cur (author_in => 'FEUERSTEIN,STEVEN')
... process data ...
book_count := books_cur%ROWCOUNT;
END
LOOP
;
93 ORACLE TEMPORARY TABLES
Creates a temporary table which can be transaction specific or session specific.
Oriented transaction:
CREATE GLOBAL TEMPORARY TABLE my_temp_table (
column1 NUMBER,
column2 NUMBER
) ON COMMIT DELETE ROWS;
Oriented session:
CREATE GLOBAL TEMPORARY TABLE my_temp_table (
column1 NUMBER,
column2 NUMBER
) ON COMMIT PRESERVE ROWS;
If the TRUNCATE statement is issued against a temporary table, only the session specific data is trucated. There is no affect on the data of other sessions.
Data in temporary tables is automatically delete at the end of the database session, even if it ends abnormally.
Indexes can be created on temporary tables. The content of the index and the scope of the index is that same as the database session.
Views can be created against temporary tables and combinations of temporary and permanent tables.
Tempoarary tables can have triggers associated with them.
Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
There are a number of restrictions related to temporary tables but these are version specific.
Multi-session scenarios:
1- Two different users try to create a global temporary table with the same name
Both tables get created. Each user sees his/her own table. Table definition is oriented to the user.
2- Two sessions by a single user try to create a global temporary table with the same name.
Once the table has been created in one of the sessions, the table exists and an error is given to the second session when trying to create the table. This behavior occurs whether or not the table structure is defined to be the same in both sessions.
3- Two sessions by a single user insert rows into a global temporary table.
Each session can insert rows into the table, no matter which session created the table. The data is oriented to the session.
4- Two sessions by the same user select from a global temporary table.
A given session will only see the rows that it has inserted, as if the table was private to that session.
5- A session tries to alter a temporary table that another session is also using.
Columns can not be added/deleted/modified as long as the table is bound to a session.
Renaming, however, can be done even when another session has rows in the table.
6- A session attempts to rename a global temporary table in which another session has inserted some rows
The table gets renamed and rows are preserved. After the rename has taken place, both sessions must use the new name to refer to that table.
94 Schema-level collection type
Similarly to object types, you should translate any program-specific type requirement into something generic and reusable. As a starting point, the following should prove useful in any application:
CREATE OR REPLACE TYPE NUMBER_TT AS TABLE OF NUMBER(38,10)
/
CREATE OR REPLACE TYPE INTEGER_TT AS TABLE OF INTEGER
/
CREATE OR REPLACE TYPE DATE_TT AS TABLE OF DATE
/
CREATE OR REPLACE TYPE VARCHAR2_TT AS TABLE OF VARCHAR2(4000)
/
CREATE OR REPLACE TYPE TIMESTAMP_TT AS TABLE OF TIMESTAMP
95 Use Boolean Elements to Improve Readability
Boolean variables and functions can greatly improve readability of programs.
96 Native SQL Versus DBMS_SQL
1) DBMS_SQL is Supported in Client-Side Programs
The DBMS_SQL package is supported in client-side programs, but native dynamic SQL is not. Every call to the DBMS_SQL package from the client-side program translates to a PL/SQL remote procedure call (RPC); these calls occur when you need to bind a variable, define a variable, or execute a statement.
2) DBMS_SQL Supports DESCRIBE
The DESCRIBE_COLUMNS procedure in the DBMS_SQL package can be used to describe the columns for a cursor opened and parsed through DBMS_SQL. This feature is similar to the DESCRIBE command in SQL*Plus. Native dynamic SQL does not have a DESCRIBE facility.
3) DBMS_SQL Lets You Reuse SQL Statements
The PARSE procedure in the DBMS_SQL package parses a SQL statement once. After the initial parsing, you can use the statement multiple times with different sets of bind arguments.
Native dynamic SQL prepares a SQL statement each time the statement is used, which typically involves parsing, optimization, and plan generation. Although the extra prepare operations incur a small performance penalty, the slowdown is typically outweighed by the performance benefits of native dynamic SQL
97 authid current_user vs pragma authid Definer
A stored procedure runs either with the rights of the caller (authid current_user) or with the rights of the procedure's owner (authid definer). This behaviour is specified with the authid clause. This authid clause immediatly follows the create procedure, create function, create package or create type statement. It can be ommited, in which case the default authid definer is taken.
userA:
p_current_user: display ‘current_userA’
p_definer: display ‘definer userA’
Table(T,U) Procedure(p_current_user, p_definer)
userB:
p_current_user: display ‘current_userB’
p_definer: display ‘definer userB’
Table(T,U) Procedure(p_current_user,p_definer)
userA call userB.p_current_user: Result: display ‘current_userA’
userA call userB.p_definer: Result: display ‘definer userB’
Privilege
Code Schema
(grant run to User Scehma)
User Schema
(Exec code schema package)
Define
Run under Code schema
Current_User
Run under User schema
98 Oct convert Hex
8i version above
to_char(100,'XX')
to_number('4D','XX')
99 Retrieve from N to M rows
SELECT *
FROM (SELECT ROWNUM id,
t.*
FROM sp_output t
WHERE ROWNUM <= 30)
WHERE id > 20
ORDER BY res_seq;
100 Create Global temporary
create global temporary tablename(column list)
on commit preserve rows; --提交保留数据 会话临时表
on commit delete rows; --提交删除数据 事务临时表
临时表是相对于会话的，别的会话看不到该会话的数据。
100.1 Tricks
SELECT MIN (NVL (c.total_net_rate, 0)), MIN (c.travel_date)
FROM wrk_ap_ratedtl a,
wrk_ap_bkdate_ratedtl b,
wrk_ap_bkdate c
WHERE c.session_id = USERENV ('SESSIONID')
AND a.wrk_ap_ratedtl_seq = b.wrk_ap_ratedtl_seq
AND c.wrk_ap_bkdate_seq = b.wrk_ap_bkdate_seq
AND c.res_seq = ii_res_seq
AND c.pax_prod_group_num_seq = ii_pax_prod_group_seq
AND a.rate_type_cd != 'CHILD'
AND c.min_flag IS NULL;
Because wrk_ap_ratedtl, wrk_ap_bkdate_ratedtl, wrk_ap_bkdate is GLOBAL TEMPORARY, if want to link each other, we must do ‘COMMIT’ when inserting data, otherwise, the linkage will be failed.
101 How to exchange Row,Column
1、固定列数的行列转换
student subject grade
---------------------------
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
……
转换为
语文 数学 英语
student1 80 70 60
student2 90 80 100
……
语句如下：
select student,
sum(decode(subject,'语文', grade,null)) "语文",
sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语"
from table
group by student
102 SYS_CONTEXT
利用以下的查询，你就明白了
select
SYS_CONTEXT('USERENV','TERMINAL') terminal,
SYS_CONTEXT('USERENV','LANGUAGE') language,
SYS_CONTEXT('USERENV','SESSIONID') sessionid,
SYS_CONTEXT('USERENV','INSTANCE') instance,
SYS_CONTEXT('USERENV','ENTRYID') entryid,
SYS_CONTEXT('USERENV','ISDBA') isdba,
SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,
SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,
SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,
SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format,
SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,
SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,
SYS_CONTEXT('USERENV','CURRENT_USER') current_user,
SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid,
SYS_CONTEXT('USERENV','SESSION_USER') session_user,
SYS_CONTEXT('USERENV','SESSION_USERID') session_userid,
SYS_CONTEXT('USERENV','PROXY_USER') proxy_user,
SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid,
SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain,
SYS_CONTEXT('USERENV','DB_NAME') db_name,
SYS_CONTEXT('USERENV','HOST') host,
SYS_CONTEXT('USERENV','OS_USER') os_user,
SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name,
SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address,
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol,
SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id,
SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id,
SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') authentication_type,
SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_data
from dual
103 OUTPUT parameter by raise
When raise meets in executing, it will reset the output or in output parameter.
PROCEDURE raise_output_parameter(
in_char IN VARCHAR2,
is_errhandler IN VARCHAR2,
on_integer OUT INTEGER)
AS
BEGIN
on_integer := 100;
RAISE NO_DATA_FOUND;
EXCEPTION
WHEN NO_DATA_FOUND THEN
on[B13] _integer := pkg_com.cv_FAILED;
pkg_err.p_handle_code (vs_section||cs_OBJECT_NAME,
pkg_com.cn_NO_DATA_FOUND,
vs_exception_message,
is_errhandler);
IF (is_errhandler = pkg_err.cv_APP_ERR) THEN
RAISE;
END IF;
END raise_output_parameter;
104 FOR IN CURSOR tricky
CURSOR cur_pax(
in_cpo_res_seq cpo_res.cpo_res_seq%TYPE)
IS
SELECT pax.*
FROM cpo_pax pax
WHERE pax.cpo_res_seq = in_cpo_res_seq;
CURSOR cur_pax_pre(
in_cpo_res_seq cpo_res.cpo_res_seq%TYPE)
IS
SELECT pax.*
FROM cpo_pax pax
WHERE pax.cpo_res_seq = in_cpo_res_seq;
<<CUR_EXIT>>
FOR lp_cur_row IN cur_pax(ir_cur_cpo_res.cpo_res_seq)
LOOP
// must use different cursor name
FOR lp_pre_row IN cur_pax_pre(ir_pre_cpo_res.cpo_res_seq)
LOOP
NEXT
105 date DD-MON-YY vs DD-MON-RRRR
YY implies the current century while
RR implies the Previous century
For example: (assuming table=Test_table, column=date1)
insert into test_table (date1)
values (to_date('02/01/95','MM/DD/YY')); will insert Feb, 1, 2095 into the table while
insert into test_table (date1)
values (to_date('02/01/95','MM/DD/RR')); will insert Feb, 1, 1995
106 Order | Group By 1,2,3
Don’t use order by 1,2,3… because the column name could be shifted in the near future, it will be hard to search and refactoring.
107 The PL/SQL compiler
The PL/SQL compiler uses Diana (for Descriptive Intermediate Attributed Notation for Ada) for its internal representation, reflecting PL/SQL’s historical debt to the
Ada
language
107.1 The Front-end
It will check syntax and semantic correctness, if it fails, it will print out.
107.2 The Back-end compile(the code generator)
The compiler back-end consumes the Diana and generates an executable representation of the program in the machine code of the target machine, The terms Diana and MCode are both used occasionally in Oracle documentation
The compiler back-end plays a critical role in generating fast run-time code
107.3 The PLSQL virtual machine
Each of PL/SQL and Java are compiled to run on its own specific virtual machine. And this is for the same reason: to simplify the task of implementing the language on a new hardware platform.
107.4 Native compilation of PL/SQL
The back-end can generate its output code in two different representations. In both cases it consumes the Diana for the current PL/SQL unit and computes the MCode. In the interpreted mode, it simply stores the MCode in system-managed structures in the SYS schema. In the native mode, it translates the MCode into C source code with the same semantics. (The MCode is not retained in this mode.)
A PL/SQL unit will execute faster when compiled native than when compiled interpreted precisely because of the increased efficiency of the housekeeping tasks.
107.5 Run time considerations
The object code for a PL/SQL unit (whether MCode or a native DLL) is fetched on demand according to execution flow. The two flavors of object code are fully interoperable, but there is a performance cost for the context switch between the two execution modes. Thus the greatest benefits from native compilation will be seen when both the caller and the called PL/SQL unit are compiled native, even when the real work each or both does is trivial.
108 How to make PL/SQL programs run faster
- The best way to improve PL/SQL program performance is to choose the right algorithms and data structures in the first place!
- A second way to improve performance is to use the new language features provided in each release. For example, index-by-varchar2 associative arrays (new in Oracle9iR2) execute much more quickly than any alternative mechanisms that solve the same problems.
109 What changes were made in Oracle10g?
v the binary_float and binary_double datatypes (the IEEE datatypes).
v the regexp_like, regexp_instr, regexp_substr and regexp_replace builtins to support regular expression manipulation with standard POSIX syntax.
v multiset operations on nested table instances supporting operations like equals, union, intersect, except, member, and so on.
v The user-defined quote character.
v INDICES OF and VALUES OF syntax for FORALL.
v The distinction between binary_integer and pls_integer vanishes.
v plsql_optimize_level and has the allowed values 2, 1 and 0. Level 2 means apply most effort and it does therefore imply slightly longer compilation times. Nevertheless it is the default. You can check
SELECT * FROM ALL_PLSQL_OBJECT_SETTINGS
v In Oracle10g, the DLL is stored canonically in the database and is cached as a file on an operating system directory only on demand.
110 Update Multiple Tables
UPDATE messages
SET dbcode = (SELECT dbcode
FROM db_error_code
WHERE db_error_code.msgid = messages.msgid);
111 Dynamic IN Clause with Native Dynamic SQL
CREATE TABLE in_clause_tab (
ID INTEGER,
title VARCHAR2(100),
description VARCHAR2(2000));
CREATE TYPE in_clause_ot AS OBJECT (
ID INTEGER
, title VARCHAR2 (100)
, description VARCHAR2 (2000)
);
CREATE OR REPLACE TYPE in_clause_tab_nt IS TABLE OF in_clause_ot;
CREATE OR REPLACE TYPE pky_nt IS TABLE OF INTEGER;
FUNCTION nds_list(list_in IN VARCHAR2) RETURN SYS_REFCURSOR IS
retval SYS_REFCURSOR;
BEGIN
OPEN retval FOR 'SELECT * FROM in_clause_tab WHERE id IN (' || list_in || ')';
RETURN retval;
END nds_list;
FUNCTION nds_bulk_list(list_in IN VARCHAR2) RETURN in_clause_tab_nt IS
retval in_clause_tab_nt;
BEGIN
EXECUTE IMMEDIATE 'SELECT in_clause_ot (id, title, description)
FROM in_clause_tab WHERE id IN (' || list_in || ')' BULK COLLECT
INTO retval;
RETURN retval;
END nds_bulk_list;
FUNCTION nds_bulk_list2(list_in IN VARCHAR2) RETURN SYS_REFCURSOR IS
retval SYS_REFCURSOR;
BEGIN
OPEN retval FOR
SELECT ic.ID,
ic.title,
ic.description
FROM TABLE(nds_bulk_list(list_in)) ic;
RETURN retval;
END nds_bulk_list2;
FUNCTION dbms_sql_list(list_in IN VARCHAR2) RETURN in_clause_tab_nt
IS
l_query VARCHAR2 (32767)
:= 'SELECT * FROM in_clause_tab WHERE id IN (';
l_cur PLS_INTEGER := DBMS_SQL.open_cursor;
l_feedback PLS_INTEGER;
l_ids pky_nt := pky_nt ();
l_row PLS_INTEGER;
l_onerow in_clause_tab%ROWTYPE;
retval in_clause_tab_nt := in_clause_tab_nt ();
PROCEDURE string_to_list ... see file for details...
END string_to_list;
BEGIN
-- Parse the delimited list to the collection.
string_to_list(list_in, l_ids);
l_row := l_ids.FIRST;
-- Build the list of bind variables.
WHILE (l_row IS NOT NULL)
LOOP
l_query := l_query || ':bv' || l_row || ',';
l_row := l_ids.NEXT(l_row);
END
LOOP
;
l_query := RTRIM(l_query, ',') || ')';
-- Define the columns to be queried.
DBMS_SQL.parse(l_cur, l_query, DBMS_SQL.native);
DBMS_SQL.define_column(l_cur, 1, 1);
DBMS_SQL.define_column(l_cur, 2, 'a', 100);
DBMS_SQL.define_column(l_cur, 3, 'a', 2000);
-- Bind each variable in the provided list.
l_row := l_ids.FIRST;
WHILE (l_row IS NOT NULL)
LOOP
DBMS_SQL.bind_variable(l_cur, ':bv' || l_row, l_ids(l_row));
l_row := l_ids.NEXT(l_row);
END
LOOP
;
-- Execute and then fetch each row.
l_feedback := DBMS_SQL.EXECUTE(l_cur);
LOOP
l_feedback := DBMS_SQL.fetch_rows(l_cur);
EXIT WHEN l_feedback = 0;
-- Retrieve individual column values and move them to the nested table.
DBMS_SQL.column_value(l_cur, 1, l_onerow.ID);
DBMS_SQL.column_value(l_cur, 2, l_onerow.title);
DBMS_SQL.column_value(l_cur, 3, l_onerow.description);
retval.EXTEND;
retval(retval.LAST) := in_clause_ot(l_onerow.ID, l_onerow.title, l_onerow.description);
END
LOOP
;
DBMS_SQL.close_cursor(l_cur);
RETURN retval;
END dbms_sql_list;
112 SQL%ROWCOUNT
BEGIN
DBMS_OUTPUT.put_line ('SQL%ROWCOUNT inside CFL?');
FOR rec IN (SELECT * FROM otn_question)
LOOP
DBMS_OUTPUT.put_line (' SQL%ROWCOUNT = ' || NVL (TO_CHAR(SQL%ROWCOUNT), 'NULL'));
END
LOOP
;
END;
: SQL%ROWCOUNT inside CFL?
SQL%ROWCOUNT = NULL
SQL%ROWCOUNT = NULL
BEGIN
UPDATE otn_question SET description = UPPER (description);
DBMS_OUTPUT.put_line ('SQL%ROWCOUNT after update, before CFL = '||SQL%ROWCOUNT);
FOR rec IN (SELECT * FROM otn_question)
LOOP
DBMS_OUTPUT.put_line (' SQL%ROWCOUNT = ' || NVL (TO_CHAR(SQL%ROWCOUNT), 'NULL'));
END
LOOP
;
DBMS_OUTPUT.put_line ('SQL%ROWCOUNT after update, after CFL = '||SQL%ROWCOUNT);
END;
SQL%ROWCOUNT after update, before CFL = 2
SQL%ROWCOUNT = 2
SQL%ROWCOUNT = 2
SQL%ROWCOUNT after update, after CFL = 2
DECLARE
CURSOR otn_question_cur
IS
SELECT *
FROM otn_question;
BEGIN
DBMS_OUTPUT.put_line ('otn_question_cur%ROWCOUNT inside CFL');
FOR rec IN otn_question_cur
LOOP
DBMS_OUTPUT.put_line (' otn_question_cur%ROWCOUNT = ' || NVL (TO_CHAR(otn_question_cur%ROWCOUNT), 'NULL'));
END
LOOP
;
END;
:otn_question_cur%ROWCOUNT inside CFL
otn_question_cur%ROWCOUNT = 1
otn_question_cur%ROWCOUNT = 2
DECLARE
TYPE otn_question_tc IS TABLE OF otn_question%ROWTYPE
INDEX BY PLS_INTEGER;
l_otn_questions otn_question_tc;
BEGIN
DBMS_OUTPUT.put_line ('SQL%ROWCOUNT after BULK COLLECT');
SELECT *
BULK COLLECT INTO l_otn_questions
FROM otn_question;
DBMS_OUTPUT.put_line (' SQL%ROWCOUNT = ' || NVL (TO_CHAR(SQL%ROWCOUNT), 'NULL'));
DBMS_OUTPUT.put_line (' Collection count = ' || NVL (TO_CHAR(l_otn_questions.COUNT), 'NULL'));
END;
SQL%ROWCOUNT after BULK COLLECT
SQL%ROWCOUNT = 2
Collection count = 2
113 Why no CONTINUE command in PL/SQL?
<
>for ... loop
...
<
>for ... loop
...
-- exit both loops
exit outer when ...;
end loop inner;
...
end loop outer;
<
>for ... loop
...
<
>for ... loop
...
-- immediately start the next iteration of the outer loop
continue outer when ...;
end loop inner;
...
end loop outer;
114 prefix, suffix for type
t - type, subtype
rt - row type
tc - table collection
cc - column collection
115 build-in package
v DBMS_ASSERT (QUALIFIED_SQL_NAME, SQL_OBJECT_NAME, ENQUOTE_NAME, ENQUOTE_LITERAL)
v
SET SERVEROUTPUT ON SIZE UNLIMITED
116 Out Join Trick
SELECT DISTINCT ii.invoice_seq,
ii.invoice_item_seq,
ii.booking_item_seq,
ii.package_seq,
iic.invoice_item_comm_seq
FROM invoice_item ii,
invoice_item_comm iic
WHERE ii.invoice_seq = cn_invoice_seq
AND ii.invoice_item_seq = iic.invoice_item_seq(+)
AND NVL(ii.reversal_flag, 'N') = 'N'
AND NVL(iic.reversal_flag(+), 'N') = 'N'
ORDER BY ii.invoice_seq,
ii.invoice_item_seq,
iic.invoice_item_comm_seq;
Or
AND iic.reversal_flag(+) IS NULL
117 Inline functions
- PL/SQL within SQL
- Purity levels
- Restrictions
118 DBMS_SQL.VARCHAR2A
This collection type is defined in the DBMS_SQL package as follows:
type varchar2a is table of varchar2(32767)
index by binary_integer;
TYPE varchar2s IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
TYPE number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
type date_table IS TABLE OF DATE INDEX BY BINARY_INTEGER;
119 Cursor best practices
Asked by Ajendra Narayansamal from Unknown Location on Tuesday, March 8, 2005
Question:
I have two questions regarding best practices:
1. Which of the following methods is most efficient for a cursor:
a. Declaring a cursor
b. Using cursor FOR-LOOP
c. Bulk collect and then loop through the collection.
2. For best performance should I use capital letters for table names and keywords?
Answer:
Without a doubt, you should use BULK COLLECT whenever possible to query information from your tables. It will be significantly faster than either a cursor FOR loop or loop with explicit cursor. Be aware, however, that a BULK COLLECT of many rows can consume a large amount of memory. You can balance memory utilization against performance improvements by using the LIMIT clause with BULK COLLECT. Here's an example:
DECLARE
CURSOR allrows_cur IS
SELECT * FROM employee
;
TYPE employee_aat IS TABLE OF employee%ROWTYPE
INDEX BY BINARY_INTEGER;
l_employee employee_aat;
l_row PLS_INTEGER;
BEGIN
OPEN allrows_cur;
LOOP
FETCH allrows_cur BULK COLLECT INTO l_employee
LIMIT 100;
-- Remember: BULK COLLECT will NOT raise NO_DATA_FOUND if no rows
-- are queried. Instead, check the contents of the collection to
-- see if you have anything left to process.
EXIT WHEN l_employee.COUNT = 0;
-- Process the data, if any.
l_row := l_employee.FIRST;
WHILE (l_row IS NOT NULL)
LOOP
process_employee (l_employee(l_row));
l_row := l_employee.NEXT (l_row);
END
LOOP
;
END
LOOP
;
-- Clean up when done: close the cursor and delete everything
-- in the collection.
CLOSE allrows_cur;
l_employee.DELETE;
END;
As far as your question regarding the formatting for key words in your code: I feel strongly that it doesn't matter in the least what coding style you follow. In fact, write your code any old way you want—make a total mess of it! Then take advantage of the automatic formatting capabilities of your PL/SQL IDE to instantly reformat the code to fit whatever your preferences or group standards dictate. If your IDE does not offer a PL/SQL "beautifier" (aka, formatter, pretty-printer, etc.), then you should switch to a tool that does provide this feature. I can't imagine developing code without it.
One of the side effects of beautification is that SQL statements that are identical logically, but different physically, will be reformatted to the same, physical statement. As a consequence, when these statements are run, the parse operation will occur just once (a long-standing optimization of the Oracle RDBMS).
Fortunately, you don't need a beautifier to achieve this optimization in Oracle 10g PL/SQL and in Oracle9i Release 2 9.2.0.5 and later. The PL/SQL compilers in these release both automatically reformat static SQL into a standard or canonical form to avoid unnecessary parsing. (Oracle8i did this as well, but early Oracle9i releases did not.)
120 Oracle Built-In Functions
View an Alphabetical Listing of Functions
Character / String Functions:
Conversion Functions:
Advanced Functions:
Mathematical Functions:
Date Functions:
Error Functions:
121 DBMS_XPLAN package
function display(table_name varchar2 default 'PLAN_TABLE',
statement_id varchar2 default null,
format varchar2 default 'TYPICAL',
filter_preds varchar2 default null)
return dbms_xplan_type_table
pipelined;
-- display from V$SQL_PLAN (or V$SQL_PLAN_STATISTICS_ALL)
function display_cursor(sql_id varchar2 default null,
cursor_child_no integer default 0,
format varchar2 default 'TYPICAL')
return dbms_xplan_type_table
pipelined;
-- display from AWR
function display_awr(sql_id varchar2,
plan_hash_value integer default null,
db_id integer default null,
format varchar2 default 'TYPICAL')
return dbms_xplan_type_table
pipelined;
-- display from SQL tuning set
function display_sqlset(sqlset_name varchar2,
sql_id varchar2,
plan_hash_value integer default null,
format varchar2 default 'TYPICAL',
sqlset_owner varchar2 default null)
return dbms_xplan_type_table
pipelined;
-- private procedure, used internally
function prepare_records(plan_cur IN sys_refcursor,
i_format_flags IN binary_integer)
return dbms_xplan_type_table
pipelined;
For example:
1. Explain plain for select * from DUAL;
2. Select * from table(dbms_xplan.display());
Execute Plan Table:
C:/Oracle/product/10.2.0/client_1/RDBMS/ADMIN/utlxplp.sql
Create Plan Table:
C:/Oracle/product/10.2.0/client_1/RDBMS/ADMIN/catplan.sql
122 Function pipelined declare
PIPELINED specifies to return the results of a table function iteratively. A table function returns a collection type (a nested table or varray) with elements that are SQL datatypes. You can query table functions using the TABLE keyword before the function name in the FROM clause of a SQL query
FUNCTION f_return_sqltable
RETURN bxu_test3.TABLETYPE_RESERVATION[BX14] PIPELINED
AS
vt_res bxu_test3.TABLETYPE_RESERVATION;
vr_res bxu_test3.ROWTYPE_RESERVATION;
BEGIN
SELECT *
BULK COLLECT INTO vt_res
FROM reservation;
FOR i IN vt_res.first..vt_res.last
LOOP
vr_res.reservation_seq := vt_res(i).reservation_seq;
PIPE ROW(vr_res);
END
LOOP
;
RETURN;
END f_return_sqltable;
Usage:
SELECT *
FROM TABLE(package_name.f_return_sqltable())
123 UPDATE 时, 如何避免数据定位处理被阻塞
数据库PUBS中的authors表，想锁定CITY为aaa的记录，为什么执行下面的命令后，CITY为bbb的记录也被锁定了，无法进行UPDATE.
BEGIN TRANSACTION
SELECT * FROM authors
WITH (HOLDLOCK)
WHERE city='aaa'
应该不是被锁住，应该只是检索数据的时候，需要从aaa的记录扫描到bbb的记录，而aaa被锁住了，所以扫描无法往下进行，这样看起来似乎就是bbb也被锁住了
124 Oracle Server Character Sets
AL32UTF8 ( 4 byte)
Client Character Sets: WE8ISO8859P1
125 Obtain Oracle Domain Name
SELECT * FROM GLOBAL_NAME;
126 User Privilege & Role
1. oracle用户查看自己的权限和角色
select * from user_tab_privs;
select * from user_role_privs;
2. sys用户查看任一用户的权限和角色
select * from dba_tab_privs;
select * from dba_role_privs;
127 dbms_session set context
CREATE CONTEXT publishing_application Using my_package;
dbms_session.set_context('publishing_application', 'role_name', 'manager');
dbms_session.set_context('publishing_application', 'user_name', 'jsmith');
dbms_session.set_context('publishing_application', 'company', 'rampant_techpress');
dbms_session.set_context('publishing_application', 'role_name', 'author');
dbms_session.set_context('publishing_application', 'user_name', 'mault');
dbms_session.set_context('publishing_application', 'company', 'rampant_techpress');
128 Directory Object
Purpose:
Separate logical directory and physical directory
- DIRECTORY objects are owned by the SYS user, (even if another user creates the DIRECTORY)
- DIRECTORY names are unique across the database, (because all the directories are located in a single namespace, namely SYS).
- The DIRECTORY object permissions are NOT the same as OS permissions on the physical directory on the server file system.
It can be used by EXTERNAL TABLE and UTL_FILE package.
Usage:
CREATE OR REPLACE DIRECTORY test_files AS ‘E:/oracleWork’
GRANT READ ON DIRECTORY test_files TO PUBLIC;
CREATE TABLE emp_external
( emp_id NUMBER(4)
, ename VARCHAR2(12)
, job VARCHAR2(12)
, mgr_id NUMBER(4)
, hiredate DATE
, salary NUMBER(8)
, comm NUMBER(8)
, dept_id NUMBER(2))
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY TEST_FILES
ACCESS PARAMETERS (records delimited BY newline fields terminated BY ',')
LOCATION ('emp_load.dat')
);
DECLARE
BEGIN
/* UTL_FILE.FRENAME (
location IN VARCHAR2,
filename IN VARCHAR2,
dest_dir IN VARCHAR2,
dest_file IN VARCHAR2,
overwrite IN BOOLEAN DEFAULT FALSE);
*/
UTL_FILE.FRENAME('TEST_FILES','emp1.dat', 'TEST_FILES', 'employee_records.dat');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(substr(sqlcode,1,100));
END;
Inquiry:
ALL_DIRECTORIES - all directories accessible to the database user
129 Return DEFAULT when NO DATA FOUND
SELECT NVL((SELECT CASE
WHEN a.ar_order_num = 1 THEN
'FIRST'
WHEN a.ar_order_num = 2 THEN
'SECOND'
END
FROM fin_man_ext_data a
WHERE a.res_seq = 1),
'DEFAULT')
FROM DUAL;
130 SUBTYPE maxvarchar2_t
I don't want to hard-code that length, because I anticipate the day when Oracle will extend the maximize size of a string.
Instead, I create a subtype in a central, generic package:
PACKAGE plsql_limits
IS
/* Maximum size for VARCHAR2 in PL/SQL */
SUBTYPE maxvarchar2_t IS VARCHAR2 (32767);
/* Maximum size for VARCHAR2 in database */
SUBTYPE dbmaxvarchar2_t IS VARCHAR2 (4000);
END plsql_limits;
131 乐观锁（ Optimistic Locking ）悲观锁（ Pessimistic Locking ）
Pessimistic locking:
select * from account where name=”Erica” for update
乐观锁，大多是基于数据版本（ Version ）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于
数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据.
132 DQL, DDL, DML, DCL
DQL – query (select, )
DDL – definition (truncate, dynamic )
DML – manipulate (insert, delete, update, lock, select fro update)
DCL – control (if , while, loop …)
133 
Connect by, Level
资 产
期末余额
负债及所有者权益
期末余额
流动资产
4256.45
流动负债
7453.74
现金
2.00
短期存款
305.54
存放中央银行款项
160.77
财政性存款
411.80
存放同业款项
18.34
向中央银行借款
6485.05
短期贷款
4103.41
同业存放款项
2.15
其他流动资产
71.93
其他流动负债
249.20
长期资产
3287.75
长期负债
0.07
中长期贷款
3262.89
发行长期债券
减：贷款呆账准备
73.71
其他长期负债
0.07
固定资产净值
77.58
其他长期资产
20.99
无形、递延及其它资产
0.52
所有者权益
190.91
其中：实收资本
165.15
资产总计
7644.72
负债及所有者权益合计
7644.72
-- CREATE TABLE
CREATE TABLE balance_sheet (BS_ID INTEGER ,BS_PID INTEGER ,BS_NAME VARCHAR2(100) ,BS_VALUE NUMBER(10) );
-- INSERT
insert into balance_sheet values(1,0,'流动资产',4256.45);
insert into balance_sheet values(2,1,'现金',2.00);
insert into balance_sheet values(3,1,'存放中央银行款项',160.77);
insert into balance_sheet values(4,1,'存放同业款项',18.34);
insert into balance_sheet values(5,1,'短期贷款', 4103.41);
insert into balance_sheet values(6,1,'其他流动资产',71.93);
insert into balance_sheet values(7,0,'长期资产',3287.75);
insert into balance_sheet values(8,7,'中长期贷款', 3262.89);
insert into balance_sheet values(9,7,'减：贷款呆账准备',73.71);
insert into balance_sheet values(10,7,'固定资产净值',77.58);
insert into balance_sheet values(11,7,'其他长期资产',20.99);
insert into balance_sheet values(12,0,'无形、递延及其它资产',0.52);
insert into balance_sheet values(13,0,'资产总计',7644.72);
insert into balance_sheet values(14,0,'流动负债',7453.74);
insert into balance_sheet values(15,14,'短期存款',305.54);
insert into balance_sheet values(16,14,'财政性存款',411.80);
insert into balance_sheet values(17,14,'向中央银行借款',6485.05);
insert into balance_sheet values(18,14,'同业存放款项',2.15);
insert into balance_sheet values(19,14,'其他流动负债',249.20);
insert into balance_sheet values(20,0,'长期负债',0.07);
insert into balance_sheet values(21,20,'发行长期债券',null);
insert into balance_sheet values(22,20,'其他长期负债', 0.07);
insert into balance_sheet values(23,0,'所有者权益',190.91);
insert into balance_sheet values(24,23,'其中：实收资本',165.15);
insert into balance_sheet values(25,0,'负债及所有者权益合计',7644.72);
commit;
SELECT * FROM balance_sheet;
SELECT bs_name,
bs_value
FROM balance_sheet
CONNECT BY PRIOR bs_id = bs_pid
START WITH bs_pid = 0;
SELECT bs_name,
bs_value
FROM balance_sheet
CONNECT BY PRIOR bs_id = bs_pid
START WITH bs_pid = 1;
SELECT (CASE
WHEN LEVEL = 1 THEN
' ' || bs_name
WHEN LEVEL = 2 THEN
' ' || bs_name
END) bs_name,
bs_value
FROM balance_sheet
CONNECT BY PRIOR bs_id = bs_pid
START WITH bs_pid = 0;
流动资产
4256.45
现金
2
存放中央银行款项
160.77
存放同业款项
18.34
短期贷款
4103.41
其他流动资产
71.93
长期资产
3287.75
中长期贷款
3262.89
减：贷款呆账准备
73.71
固定资产净值
77.58
其他长期资产
20.99
无形、递延及其它资产
0.52
资产总计
7644.72
流动负债
7453.74
短期存款
305.54
财政性存款
411.8
向中央银行借款
6485.05
同业存放款项
2.15
其他流动负债
249.2
长期负债
0.07
发行长期债券
其他长期负债
0.07
所有者权益
190.91
其中：实收资本
165.15
负债及所有者权益合计
7644.72
SELECT LTRIM(sys_connect_by_path(BS_Name,
'|'),
'|') path,
(CASE
WHEN LEVEL = 1 THEN
' ' || bs_name
WHEN LEVEL = 2 THEN
' ' || bs_name
END) bs_name,
bs_value
FROM balance_sheet
CONNECT BY PRIOR bs_id = bs_pid
START WITH bs_pid = 0;
133.1 Level
SELECT ADD_MONTHS(TRUNC(SYSDATE,'mm'), -level)
FROM DUAL
CONNECT BY LEVEL <= 24;
SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 24;
134 Date Quarter
select to_char(sysdate,'YY"Q"Q') from dual
TO_C
----
08Q3
SELECT ntpact
, CASE WHEN to_number(to_char(NTPACT,'mm'))<4 THEN 'Q1'
WHEN to_number(to_char(NTPACT,'mm'))<7 THEN 'Q2'
WHEN to_number(to_char(NTPACT,'mm'))<10 THEN 'Q3'
ELSE 'Q4'
END quarter
FROM testtbl;135 GROUP BY ROLLUP, CUBE
Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP和CUBE语句。如果是ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。 grouping_id()可以美化效果：
SELECT channels.channel_desc, calendar_month_desc,
countries.country_iso_code,
TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$
FROM sales, customers, times, channels, countries
WHERE sales.time_id=times.time_id
AND sales.cust_id=customers.cust_id
AND customers.country_id = countries.country_id
AND sales.channel_id = channels.channel_id
AND channels.channel_desc IN ('Direct Sales', 'Internet')
AND times.calendar_month_desc IN ('2000-09', '2000-10')
AND countries.country_iso_code IN ('GB', '
US
')
GROUP BY
ROLLUP(channels.channel_desc, calendar_month_desc, countries.country_iso_code);
CHANNEL_DESC CALENDAR CO SALES$
-------------------- -------- -- --------------
Internet 2000-09 GB 16,569
Internet 2000-09
US
124,224
Internet 2000-09 140,793
Internet 2000-10 GB 14,539
Internet 2000-10
US
137,054
Internet 2000-10 151,593
Internet 292,387
Direct Sales 2000-09 GB 85,223
Direct Sales 2000-09 US 638,201
Direct Sales 2000-09 723,424
Direct Sales 2000-10 GB 91,925
Direct Sales 2000-10 US 682,297
Direct Sales 2000-10 774,222
Direct Sales 1,497,646
1,790,032
136 Intersect and Minus
q intersect运算返回查询结果中相同的部分.
q minus运算返回在第一个查询结果中与第二个查询结果不相同的那部分行记录.
137 TNS_ADMIN variable
Not sure about what you can do in the tool, but Windows has a TNS_ADMIN environment variable that you can set to give the location of a tnsnames.ora file. We use this to centralise the TNS entries on a shared location, so we are all working from the same one.
You can also set a TNS_ADMIN string value in the local machine registry of your Oracle Home.
TNS_ADMIN=c:/oracle/product/10.2.0/client_1/NETWORK/ADMIN
138 Show parameter
SQL> show parameter %TRA%
139 Check if the record exists
select 1
into found
from dual
where exists (select 1
from DISCOUNT
where STOR_ID = old_upd.STOR_ID);
140 Floor & Ceil Function
四舍五入ROUND(value, [scale]) ROUND(101.8) = 102 Round(123.37, 1) = 123.4
截取TRUNC(value, [scale]) Trunc(123.33) = 123 Trunc(123.567, 2) = 123.56
求最大整数FLOOR(value) Floor(128.3)=128 Floor(129.8) = 129 除了不能截取小数部分外，Floor几乎等同于Trunc函数。
求最小整数CELL(value) Cell(128.3)=129 Cell(129.8) = 130
141 By value, By reference
By default,
IN
by reference
OUT | IN OUT
by value
Keep in mind, however, that there is a downside to using NOCOPY. When you pass parameters to a procedure by reference then any modifications you perform on the parameters inside the procedure is done on the same memory location as the actual parameter, so the modifications are visible. In other words, there is no way to “undo” or “rollback” these modifications, even when an exception is raised midway. So if an exception is raised inside the procedure the value of the parameter is “undefined” and cannot be trusted.
142 display your Oracle session ID number
SELECT sys_context('USERENV',
'SID')
FROM DUAL;
SELECT TO_NUMBER(SUBSTR(dbms_session.unique_session_id,
1,
4),
'XXXX') mysid
FROM DUAL;
Optimizer method
1) nested loop (USER_NL)
outer table is large, inner table is small.
SELECT e.employee_id, e.salary
WHERE e.employee_id < 103
2) hash join (USE_HASH)
outer table is large, inner table is small and related.
SELECT o.customer_id, l.unit_price * l.quantity
WHERE l.order_id = o.order_id;o
3) sort merge (USE_MERGE)
outer table and inner table are both large.
Hash joins are far faster than nested loop joins in certain cases, often in cases where your SQL is joining a large table to a small table. According to this research using Oracle 10g, the hash join maximum is only to only 200 megabytes (5% of pga_aggregate_target).
143 NLS (native Language support)
To see the encoding used by Oracle to store text, using the following DUMP command.
SELECT DUMP('abc', 1016) FROM DUAL;
DUMP('ABC',1016)
------------------------------------------------------------------
Typ=96 Len=3 CharacterSet=ZHT16BIG5: 61,62,63
SQL>
If the character set defined at client is different from that at server, the conversion is usually done at client.
To display the settings on server, checking following command
SELECT * from NLS_SESSION_PARAMETERS;
SELECT * from NLS_INSTANCE_PARAMETERS;
SELECT * from NLS_DATABASE_PARAMETERS;
144 The different between DBA_, ALL_, USER_
DBA_OBJECTS: can see all ALL_OBJECTS, USER_OBJECTS
ALL_OBJECTS: can see own objects and any objects that ‘user grant to you’
USER_OBJECTS: can only see own objects
145 Using ‘translate’ function to instead ‘replace’
select translate ('abcde', 'ace', '123')
from dual;
-- 結果: 1b2d3
-- 只需一個 translate 即可.
select translate ('abcde', 'ace', '12')
from dual;
-- 結果: 1b2d
-- 因為沒有指定 e 的替換字元，所以 e 就不見了.
146 怎样看懂Oracle的执行计划
146.1 如何访问数据
q Full Table Scan (FTS) --全表扫描
q Index Lookup (unique & non-unique) --索引扫描（唯一和非唯一）
q Rowid --物理行id
146.2 执行计划层次关系
--采用最右最上最先执行的原则看层次关系，在同一级如果某个动作没有子ID就最先执行
146.2.1 层次的父子关系
PARENT1
**FIRST CHILD
****FIRST GRANDCHILD
**SECOND CHILD
Here the same principles apply, the FIRST GRANDCHILD is the initial operation then the FIRST CHILD followed by the SECOND CHILD and finally the PARENT collates the output.
146.3 表访问方式
146.3.1 Full Table Scan (FTS) 全表扫描
全表扫描模式下会读数据到表的高水位线（HWM即表示表曾经扩展的最后一个数据块），读取速度依赖于Oracle初始化参数db_block_multiblock_read_count
146.3.2 Index Lookup 索引扫描
index unique scan --索引唯一扫描
index range scan --索引局部扫描 Can be used for range operations (e.g. > < <> >= <= between) .
index full scan --索引全局扫描 want the data to be ordered in the index order.
index fast full scan --索引快速全局扫描 不带order by情况下常发生
index skip scan --索引跳跃扫描 where条件列是非索引的前导列情况下常发生
Rowid 物理ID扫描 --Rowid扫描是最快的访问数据方式
146.4 表连接方式
146.4.1 Sort Merge Join (SMJ)
--由于sort是非常耗资源的，所以这种连接方式要避免
SELECT STATEMENT [CHOOSE] Cost=17
**MERGE JOIN
****SORT JOIN
******TABLE ACCESS FULL EMP [ANALYZED]
****SORT JOIN
******TABLE ACCESS FULL DEPT [ANALYZED]
146.4.2 Nested Loops (NL)
--比较高效的一种连接方式
SELECT STATEMENT [CHOOSE] Cost=5
**NESTED LOOPS
****TABLE ACCESS FULL DEPT [ANALYZED]
****TABLE ACCESS FULL EMP [ANALYZED]
146.4.3 Hash Join
--最为高效的一种连接方式
SELECT STATEMENT [CHOOSE] Cost=3
**HASH JOIN
****TABLE ACCESS FULL DEPT
****TABLE ACCESS FULL EMP
Cartesian Product --卡迪尔积，不算真正的连接方式，sql肯定写的有问题
146.5 运算符
146.5.1 sort
--排序，很消耗资源
146.5.2 filter
--过滤，如not in、min函数等容易产生
146.5.3 view
--视图，大都由内联视图产生
146.5.4 partition view
--分区视图
146.6 选择最有效率的表名顺序(只在基于规则的优化器中有效)
Oracle的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表(基础表 driving table) 将被最先处理。在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。当Oracle处理多个表时，会运用排序及合并的方式连接它们。首先，扫描第一个表(FROM子句中最后的那个表)并对记录进行派序，然后扫描第二个表(FROM子句中最后第二个表)，最后将所有从第二个表中检索 出的记录与第一个表中合适记录进行合并。
146.7 WHERE子句中的连接顺序(只在基于规则的优化器中有效)
Oracle采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。
146.8 Avoiding sort
As rarely as use ‘‘DISTINCT’, ‘ORDER BY’’ possible, because it will cause sorting in DISK or MEMORY
147 CONTINUE in
Loop
BEGIN
FOR i IN 1 .. 3
LOOP
dbms_output.put_line('i=' || TO_CHAR(i));
CONTINUE WHEN (i+1) = val;
dbms_output.put_line('Did not jump to the top of the loop');
END LOOP;
END contwhen_test;
Continues processing at the top of the loop when the specified condition is met
148 Oracle Inline function
DECLARE
l_loops NUMBER := 10000000;
l_start NUMBER;
l_return NUMBER;
FUNCTION add_numbers (p_1 IN NUMBER, p_2 IN NUMBER) RETURN NUMBER AS
BEGIN
RETURN p_1 + p_2;
END add_numbers;
BEGIN
l_start := dbms_utility.get_time;
FOR i IN 1 .. l_loops LOOP
PRAGMA INLINE (add_numbers, 'YES');
l_return := add_numbers(1, i);
END LOOP;
dbms_output.put_line('Elapsed Time: ' || (dbms_utility.get_time - l_start) || ' hsecs');
END;
149 With Claus
WITH t AS (
SELECT 100 s FROM dual
UNION ALL
SELECT 7201 FROM dual)
SELECT s,TRUNC(s/60)||':'||mod(s,60) "M:S"
FROM t;
150 Like Claus
Multiple character: ‘%’
SELECT *
FROM wildcard
WHERE test LIKE '2%5';
Single character: ‘_’
SELECT *
FROM product_feature a
WHERE a.create_userid LIKE 'jv_is';
Find Records Containing Percentage Sign
SELECT *
FROM wildcard
WHERE test LIKE '%/%%' ESCAPE '/';
151 LNNVL(condition)
select * from products
where LNNVL(qty >= reorder_level);
it will return qty >= reorder_level OR reorder_level is NULL
This would return the following result:
PRODUCT_ID
QTY
REORDER_LEVEL
1000
20
NULL
3000
8
10
6000
4
5
152 Parallel PL/SQL function in SQL
FUNCTION f_parallel_fun (
ii_value INTEGER
) RETURN INTEGER PARALLEL_ENABLE;
PRAGMA RESTRICT_REFERENCES(f_parallel_fun, WNDS ,WNDS ,RNPS ,WNPS);
Example1:
SELECT /*+ full(r) parallel(r,4) */
parallel_fun.f_parallel_fun(r.res_seq) BULK COLLECT
INTO t_res
FROM reservation r;
Example2:
FOR i IN 1..t_res.count
LOOP
vn_res := parallel_fun.f_parallel_fun(t_res(i));
END
LOOP
;
Example1 is better than Example2, because function in SQL can be used ‘PARALLEL’ call, it is faster than Example2.
153 Using RETURN INTO Clause to improve performance
DELETE FROM sales
WHERE product_id = p_product_id
RETURNING sale_value BULK COLLECT INTO t_sale_value;
FOR i IN 1..t_sale_value.COUNT
LOOP
Total := total + t_sale_value(i);
END
LOOP
;
154 Tuning Tips
q Use Stored programs rather than anonymous blocks
q Use Packages rather than procedure
q Pin Frequently used packages in the shared pool
q Make use of the OF COLUMNs and WHEN clauses of the CREATE TRIGGER to ensure that the trigger only fires when necessary
q Use explicit cursor
q Caching with PL/SQL tables
BEGIN
l_product_value := product_val_table(p_product_id);
EXCEPTION
WHEN NO_DATA_FOUND THEN
OPEN product_csr (p_product_id);
FETCH product_csr INTO l_product_value;
CLOSE product_csr;
Product_val_table(p_product_id) := l_product_value;
END
q Use temporary tables
q CREATE SEQUENCE clause includes ORDER BY, it ensures that the sequence is in order.
q Use DECODE
155 Why missing sequence number
q A transaction obtain the sequence, for some of reason, it ROLLBACKs or FAILED.
q The server FAILED
q Sequence number in SGA is insufficient for use
156 NULL Values
q NULL values cannot be indexed
q The use of NULL values can reduce average row length
Don’t define a column as NULLable if it is expected that queries will be constructed that will search for NULL value. Instead, define NOT NULL with default value.
157 使用索引的注意事项
select,update,delete 语句中的子查询应当有规律地查找少于20%的表行.如果一个语句查找的行数超过总行数的20%,它将不能通过使用索引获得性能上的提高.
索引可能产生碎片,因为记录从表中删除时,相应也从表的索引中删除.表释放的空间可以再用, 而索引释放的空间却不能再用.
频繁进行删除操作的被索引的表,应当阶段性地重建索引,以避免在索引中造成空间碎片,影响性能.在许可的条件下, 也可以阶段性地truncate表,truncate 命令删除表中所有记录,也删除索引碎片.
158 Find TableSpace for one table
SELECT DISTINCT tablespace_name
FROM all_tables
WHERE table_name = 'YOURTABLENAME';
159 with admin option & with grant option
with admin option ：系统权限及角色授予他人时可以使用，权限接受者可以分发权限，但分发出去的权限无法级联回收；
with grant option
：对象权限授予他人时可以使用，权限接受者可以分发权限，分发出去的权限可以级联回收。
160 *PLSQL Developer view others package body
It's a little bit tricky.
If you want User 'B' to be able to view package body of a package owned by User 'A' but not able to alter/compile it. Grant select on dba_source to the user 'B'.
If you've PL/SQL developer, check tools>preferences>options>use DBA views if available.
Otherwise, create your own report based on dba_source table.
161 Over Funstion
over()函数是Oracle的分析函数，其语法如下：
函数名（ [ 参数 ] ） over（ [ 分区子句 ] [ 排序子句 [ 滑动窗口子句 ] ]）
161.1 剔除重复记录
表temp_b的记录：
执行如下SQL语句：
select * from (
select city_name, city_code, prov_id, row_number() over(partition by city_name order by 1) r from temp_a
) where r = 1;
剔除表temp_a中city_name相同的记录
161.2 分组排序
成绩表的所有记录如下：
执行如下SQL语句：
select student_name, class, score, dense_rank() over(partition by class order by score desc) 名次 from temp_b;
分班级按成绩排名次排序如下：
提示：
函数dense_rank()是连续排序，有两个第二名时仍然跟着第三名。
函数rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）
161.3 分组统计
成绩表的所有记录如下：
执行如下SQL语句：
select student_name, class, score, sum(score) over(partition by class order by score desc) 累计 from temp_b;
班级成绩累计（"连续"求和）结果如下：
提示：此功能用于统计某一段时间每天累计的收入非常有用。
执行如下SQL语句：
select student_name, class, score, sum(score) over(partition by class) 班级总分 from temp_b;
班级成绩总分统计如下：
162 WM_CONCAT
SQL> select * from t;
I A D
---------- ---------- -------------------
1 b 2008-03-27 10:55:42
1 a 2008-03-27 10:55:46
1 d 2008-03-27 10:55:30
2 z 2008-03-27 10:55:55
2 t 2008-03-27 10:55:59
要获得如下结果，注意字符串需要按照D列的时间排序：
1 d,b,a
2 z,t
select i,wmsys.wm_concat(a)
from (select * from t order by i,d)
group by i;
I WMSYS.WM_CONCAT(A)
---------- --------------------
1 d,b,a
2 z,t
SELECT client_seq,
wm_concat(client_loc_seq)
FROM (SELECT cl.client_loc_seq,
cl.client_seq
FROM client_location cl
ORDER BY cl.client_seq, cl.client_loc_seq )
GROUP BY client_seq;
163 Dropdown Value
SELECT r.res_seq,
(SELECT rs.descr_eng
FROM reservation_status rs
WHERE rs.reservation_status_cd = r.reservation_status_cd) status_descr
FROM reservation r
SELECT r.client_loc_seq,
(SELECT M.DESCR_ENG
FROM market m
WHERE m.market_cd = r.market_cd) market_descr
FROM client_location r
WHERE r.market_cd IS NULL;
It will still print out NULL description
164 Displaying Missing Record
SELECT NAME,
TYPE
NVL((SELECT sal
FROM missing_type t
WHERE t.NAME = t1.NAME
AND t.TYPE = t2.TYPE),
0) sal
FROM (SELECT DISTINCT NAME
FROM missing_type) t1,
(SELECT DISTINCT TYPE
FROM missing_type) t2
ORDER BY NAME,
TYPE;
Name Type Sal
------ ----------- -----
AA TYPE A 10
AA TYPE B 20
AA TYPE C 30
BB TYPE A 10
BB TYPE B 20
BB TYPE C 0
CC TYPE A 10
CC TYPE B 0
CC TYPE C 30
165 Error Logging
BEGIN
DBMS_ERRLOG.create_error_log (
dml_table_name => 'EMPLOYEES'
, skip_unsupported => TRUE);
END;
I will now find a table with the following name and structure in my schema:
TABLE ERR$_EMPLOYEES(
ORA_ERR_NUMBER$ NUMBER,
ORA_ERR_MESG$ VARCHAR2(2000 BYTE),
ORA_ERR_ROWID$ UROWID(4000),
ORA_ ERR_OPTYP$ VARCHAR2(2 BYTE),
ORA_ERR_TAG$ VARCHAR2(2000 BYTE),
EMPLOYEE_ID VARCHAR2(4000 BYTE),
FIRST_NAME VARCHAR2(4000 BYTE),
LAST_NAME VARCHAR2(4000 BYTE),
…
);
FORALL indx IN 1 .. employee_ids.COUNT()
UPDATE employees SET salary = salaries (indx) WHERE employee_id = employee_ids (indx)
LOG ERRORS REJECT LIMIT UNLIMITED;
SQL> exec dbms_errlog.create_error_log('T','T1',USER);
SQL> INSERT INTO T VALUES('X') LOG ERRORS INTO T1;
INSERT INTO T VALUES('X') LOG ERRORS INTO T1
ORA-01722: 无效数字
SQL> SELECT * FROM T1;
ORA_ERR_NUMBER$ ORA_ERR_MESG$ O ORA_E ORA_E X
--------------- ------------------------------ - ----- ----- ------
1722 ORA-01722: 无效数字 I X
166 Paginate display
SELECT b.rn,
b.*
FROM (SELECT a.*,
ROWNUM rn
FROM (SELECT *
FROM reservation r
ORDER BY r.res_seq) a
WHERE ROWNUM < 99) b
WHERE rn > 5;
SELECT *
FROM (SELECT cl.client_seq,
cl.client_loc_seq,
row_number() over(PARTITION BY cl.client_seq ORDER BY cl.client_loc_seq ASC) rn
FROM client_location cl)
WHERE rn <= 3
ORDER BY client_seq, client_loc_seq;
167 DBMS_UTIL
dbms_utility.comma_to_table(
dbms_utility.table_to_comma (
dbms_utility.compile_schema(schema IN VARCHAR2,
compile_all IN BOOLEAN DEFAULT TRUE,
reuse_settings IN BOOLEAN DEFAULT FALSE);
dbms_utility.format_call_stack RETURN VARCHAR2;
dbms_utility.format_error_backtrace RETURN VARCHAR2;
168 DBMS_ASSERT
DBMS_ASSERT.sql_object_name('dbms_assert')
169 REGEXP_LIKE, REGEXP_INSTR…
SELECT *
FROM grp_master a
WHERE regexp_like(a.group_id,
'(^G|^J)');
SELECT *
FROM grp_master a
WHERE regexp_like(a.group_id,
'^[[:digit:]]');
170 RegExp_Replace
SELECT regexp_replace(a.message,
'AC-240 Product Price for pax_prod_seq: '''||'([0-9]+)'||'''.*', '/1',1,1)
FROM APP_LOG A
WHERE TRUNC(A.DATE_TIME) = TRUNC(SYSDATE) - 2
AND A.USER_ID = 'BXU'
AND message LIKE '%not found%'
ORDER BY a.session_id,
a.date_time,
a.display_order;
Original:
AC-240 Product Price for pax_prod_seq: '3878667' and product seq: '57698' and product ID: 'CRCTMA' and currency cd: 'CAD' and room type cd: 'TWIN' and prod avail date: '23-JUN-2006' and single_currency_flag: 'Y' and price_cd: 'PP or PERPRD' not found.
Result:
3878667
171 BLOB compress
CREATE OR REPLACE TRIGGER document_insert_trigger
BEFORE INSERT ON document
FOR EACH ROW
DECLARE
l_compressed_blob BLOB := TO_BLOB('1'); -- Initial value just in case...
BEGIN
-- Compress the data.
UTL_COMPRESS.lz_compress (src => :new.document_binary_data,
dst => l_compressed_blob);
-- update the blob value
SELECT l_compressed_blob INTO :new.document_binary_data FROM dual;
-- *** this does not work either ***
--:new.document_binary_data := l_compressed_blob;
DBMS_LOB.FREETEMPORARY(l_compressed_blob);
end;
172 DBMS_PIPE FOR DEBUG
PROCEDURE p_message(msg VARCHAR2) AS
sta NUMBER;
BEGIN
dbms_pipe.pack_message(msg);
sta := dbms_pipe.send_message('MyDebug', 60); -- 把打包好的消息发送给名为MyDebug的管道，超时设置为60秒
END;
直接用这个p_message代替平时使用的dbms_output.put_line就可以了，在调试程序前打开PL/SQL Developer里Event Monitor，选择Event Type为DBMS_PIPE，Event Name填上MyDebug，然后按start开始监听。然后就可以像平常一样运行自己存储过程了，不同的是调试信息是实时显示出来的，而不是像 Output那样要等程序执行完才显示，感觉真是方便多了。
另外也可以使用DBMS_ALERT，它和DBMS_PIPE最大不同的是它基于事务的，消息要commit才会发送.
DBMS_PIPE和DBMS_ALERT这两个包都可以进行多进程间的通信，但两者任然是有一些区别：
1) 报警信号是同步的。报警信号直到会话发出Commit时才被发出；如果它所处的事务回滚，则该信号就不发送了。但是管道信号是异步的，而且其通信过程不受 事务提交和回滚的影响。
2) 沿着管道进行传递的消息只能被一个进程进行处理，其消息的接收方式是拷贝后删除，但是报警信号可以被多 个进程所获得，即消息的接收方式仅仅是拷贝。
3) 管道消息不仅可以传递文本信息，还可以传递其他信息，比如对象等，这一好处得益于其对消息的打包预处理；而报警消息只能是文本，不能是其他类型。
173 Condition Compiling
173.1 Tables
USER_PLSQL_OBJECT_SETTING
173.2 Statement
--
$IF $THEN
$ELSIF $THEN
$ELSE
$END
--
$ERROR
$END
ALTER PROCEDURE test_plsql_ccflags COMPILE PLSQL_CCFLAGS='debug_mode:false' REUSE SETTINGS;
CREATE OR REPLACE PROCEDURE test_plsql_ccflags
IS
BEGIN
$IF $$debug_mode $THEN DBMS_OUTPUT.PUT_LINE('DEBUGGING'); $END
NULL;
END test_plsql_ccflags;
/
173.3 Setting
PLSQL_WARNING
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE', 'DISABLE:PERFORMANCE';
PLSQL_OPTIMIZE_LEVEL
PLSQL_CCFLAGS
173.4 Packages
Dbms_db_version
Dbms_preprocessor
173.5 Example
ALTER PROCEDURE show_info COMPILE
PLSQL_CCFLAGS = 'current_user_type:0'
REUSE SETTINGS
CREATE OR REPLACE PROCEDURE show_info
IS
BEGIN
$IF $$current_user_type = user_types.administrator
$THEN
DBMS_OUTPUT.PUT_LINE ('Administrator!');
$ELSIF $$current_user_type = user_types.enduser
$THEN
DBMS_OUTPUT.PUT_LINE ('End user!');
$ELSE
$ERROR 'Curr user type of ' || $$current_user_type || ' is not known.'
$END
$END
END show_info;
174 Avoid Hard-Code
Scenario
What to do about it
Any literal value
Hide the value behind a constant or a function. The function is a bit slower than referencing a constant but you can hide the value.
Constrained declarations like VARCHAR2(10)
Whenever you declare a variable with a limited size, you run the risk of raising a VALUE_ERROR exception. Better instead to define a subtype and hide the constraint behind the name of the subtype. Then when you declare your variable, use the subtype as the type.
Fetch into a list of variables
This is a hard-coding because if the number or type of expressions in the select list ever changes, you have to change the fetch statement as well. Better instead to fetch into a record based on the cursor with %ROWTYPE.
Business rules and formulas
Rules change all the time and usually get more complex, so you should always hide rules and formulas behind a function, and then just call the function.
SQL statements
This is not obvious to most developers, but every time you write an SQL statement you are taking a "snapshot" of a small slice of your business model - and that model is also always in the process of changing. So....hide all your SQL statements behind procedures (to change data in tables) and functions (to return data) - a table or transaction API. The Quest CodeGen Utility can help you do this.
175 Open File with PLSQLDEV.exe
If using DDE, it will load .SQL files in the same instance.
Folder Option -> File Type -> New (Advanced)
176 PLSQL Standard Developed
q Always initialize variable after BEGIN, because exception can be raised by error.
q Give DEFAULT value to number, but default of string is NULL
q Never issue RETURN inside the loop
q Never EXIT from within any FOR loop
q Inner anonymous blocks (those with their own DECLARE section), are good to control when resource-intensive variables are allocated in memory, like large collections that are only needed during certain executions.
q Use the NOCOPY parameter mode hint with caution, It is a compiler hint, not a directive. The compiler may ignore you
177 ROWNUM & ORDER BY Issue
Wrong:
SELECT spo.*
INTO vr_sp_output
FROM sp_output spo,
wrk_sp_output wspo
WHERE wspo.session_id = gn_session_id
AND wspo.orig_sp_output_seq = spo.sp_output_seq
AND ROWNUM = 1
ORDER BY spo.sp_output_seq DESC;
Correct:
SELECT spo.*
INTO vr_sp_output
FROM sp_output spo
WHERE spo.sp_output_seq = (SELECT MAX(wspo.orig_sp_output_seq)
FROM wrk_sp_output wspo
WHERE wspo.session_id = gn_session_id);
178 DBMS_SQL.PARSE
-- dynamic fetch column name & column value
CREATE OR REPLACE PROCEDURE run_query(p_sql IN VARCHAR2) IS
v_v_val VARCHAR2(4000);
v_n_val NUMBER;
v_d_val DATE;
v_ret NUMBER;
c NUMBER;
d NUMBER;
col_cnt INTEGER;
f BOOLEAN;
rec_tab DBMS_SQL.DESC_TAB;
col_num NUMBER;
v_rowcount NUMBER := 0;
BEGIN
-- create a cursor
c := DBMS_SQL.OPEN_CURSOR;
-- parse the SQL statement into the cursor
DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
-- execute the cursor
d := DBMS_SQL.EXECUTE(c);
--
-- Describe the columns returned by the SQL statement
DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
--
-- Bind local return variables to the various columns based on their types
FOR j IN 1 .. col_cnt
LOOP
CASE rec_tab(j).col_type
WHEN 1 THEN
DBMS_SQL.DEFINE_COLUMN(c, j, v_v_val, 2000); -- Varchar2
WHEN 2 THEN
DBMS_SQL.DEFINE_COLUMN(c, j, v_n_val); -- Number
WHEN 12 THEN
DBMS_SQL.DEFINE_COLUMN(c, j, v_d_val); -- Date
ELSE
DBMS_SQL.DEFINE_COLUMN(c, j, v_v_val, 2000); -- Any other type return as varchar2
END CASE;
END
LOOP
;
--
-- Display what columns are being returned...
DBMS_OUTPUT.PUT_LINE('-- Columns --');
FOR j IN 1 .. col_cnt
LOOP
DBMS_OUTPUT.PUT_LINE(rec_tab(j).col_name || ' - ' || CASE rec_tab(j).col_type
WHEN 1 THEN
'VARCHAR2'
WHEN 2 THEN
'NUMBER'
WHEN 12 THEN
'DATE'
ELSE
'Other'
END);
END
LOOP
;
DBMS_OUTPUT.PUT_LINE('-------------');
--
-- This part outputs the DATA
LOOP
-- Fetch a row of data through the cursor
v_ret := DBMS_SQL.FETCH_ROWS(c);
-- Exit when no more rows
EXIT WHEN v_ret = 0;
v_rowcount := v_rowcount + 1;
DBMS_OUTPUT.PUT_LINE('Row: ' || v_rowcount);
DBMS_OUTPUT.PUT_LINE('--------------');
-- Fetch the value of each column from the row
FOR j IN 1 .. col_cnt
LOOP
-- Fetch each column into the correct data type based on the description of the column
CASE rec_tab(j).col_type
WHEN 1 THEN
DBMS_SQL.COLUMN_VALUE(c, j, v_v_val);
DBMS_OUTPUT.PUT_LINE(rec_tab(j).col_name || ' : ' || v_v_val);
WHEN 2 THEN
DBMS_SQL.COLUMN_VALUE(c, j, v_n_val);
DBMS_OUTPUT.PUT_LINE(rec_tab(j).col_name || ' : ' || v_n_val);
WHEN 12 THEN
DBMS_SQL.COLUMN_VALUE(c, j, v_d_val);
DBMS_OUTPUT.PUT_LINE(rec_tab(j).col_name || ' : ' || to_char(v_d_val,
'DD/MM/YYYY HH24:MI:SS'));
ELSE
DBMS_SQL.COLUMN_VALUE(c, j, v_v_val);
DBMS_OUTPUT.PUT_LINE(rec_tab(j).col_name || ' : ' || v_v_val);
END CASE;
END
LOOP
;
DBMS_OUTPUT.PUT_LINE('--------------');
END
LOOP
;
--
-- Close the cursor now we have finished with it
DBMS_SQL.CLOSE_CURSOR(c);
END;
/
179 RAISE vs RAISE_APPLICATION_ERROR
The RAISE_APPLICATION_ERROR built-in (defined in the DBMS_STANDARD package) should be used for just a single scenario: you need to communicate an application-specific error back to the user.
Suppose, for example, I have a rule for the employees table that the minimum salary allowed is $100,000 (ah, wouldn't that be nice?). I want to enforce that rule through a database trigger:
TRIGGER employees_minsal_tr
BEFORE INSERT OR
UPDATE
ON
employees
FOR EACH ROW
BEGIN
IF :new.salary < 1000000
THEN
/* communicate error */
NULL;
END IF;
END;
I can stop the DML from completing by issuing a RAISE statement, such as:
RAISE PROGRAM_ERROR;
But I would not be able to communicate back to the user what the actual problem was.
If, on the other hand, I use RAISE_APPLICATION_ERROR, I can specify the error number (of little interest to my users, but a good "identifier" for support) and, more importantly, the error message, as in:
TRIGGER employees_minsal_tr
BEFORE INSERT OR
UPDATE
ON
employees
FOR EACH ROW
BEGIN
IF :new.salary < 1000000
THEN
RAISE_APPLICATIONE_ERROR (-20000,
'Salary of '|| :new.salary ||
' is too low. It must be at least $100,000.');
END IF;
END;
And that, dear reader, is the motivation for using RAISE_APPLICATION_ERROR: the ability to communicate a custom, application-specific error message to your users.
Use RAISE when you want to raise an already-defined exception, whether one of Oracle's (such as NO_DATA_FOUND) or one of your definition, as in:
DECLARE
e_bad_value EXCEPTION;
BEGIN
RAISE e_bad_value;
END;
but if it is one of your own user-defined exceptions, it only makes sense to raise it this way if you are going to trap it inside the backend as well, and then do something in response to the error.
If you let the e_bad_value exception propagate out to your users, all they will know about the error is that the error code is 1 and the error message is "User-defined Exception". Not very helpful.
180 Use UNION ALL instead of UNION
UNION
eliminates all duplicated rows from select list, But UNION ALL return all records, so it is fast.
181 Subqueries with temporary tables or WITH clause
Original:
SELECT emp_id
FROM employee,
department
WHERE emp_id IN (SELECT emp_id
FROM emp_small
WHERE emp_dept < 'D')
AND dpt_manager IN (SELECT emp_id
FROM emp_small
WHERE emp_dept < 'D')
AND dpt_id = emp_dept
To:
WITH temp_emp AS (SELECT emp_id empid
FROM emp_small
WHERE emp_dept < 'D')
SELECT emp_id
FROM employee,
department
WHERE emp_id IN (SELECT empid
FROM temp_emp)
AND dpt_manager IN (SELECT empid
FROM temp_emp)
AND dpt_id = emp_dept
[1] Procedure scope
[2] Inner scope
[3] PRIMARY KEY as row number
[4] SGA - system global area
[5] PGA – program global area
[6] My Tips
[7] My Tips
[8] My Tips
[9] Keep in mind
[10] Keep in mind
[11] Keep in mind
[12] Keep in mind for dynamic SQL
[13] Oracle default value
[14] Resource is not released
[15] Column Name
[16] Strong type
[17] Weak type
[18] It can be ‘IN, OUT, IN OUT’ mode
[19] Injected code
[B1]It will delete all data, because it is always TRUE
[B2]Procedure identifier
[B3]It is visible in whole procedure
[B4]Procedure variable
[BX5]Values
[BX6]Key
[B7]Auto empty to CHR
[MSOffice8]Will locate second row
[MSOffice9]Will raise the INVALID_CURSOR exception
[B10]Trace error
[B11]Call procedure
[B12]Never executed
[B13]It won’t work, it will return null.
[BX14]collections
5540
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
