PL/SQL學習筆記-總結

PL/SQL學習筆記-總結

寫了個小總結

[@more@]

語句塊的結構：
DECLARE
...
BEGIN
...
EXCEPTION
...
END

條件語句：
IF ... THEN
...
ELSIF ... THEN
...
ELSE
...
END IF;

CASE語法:
[<>]
CASE [test_var]
WHEN value1 THEN sequence_of_statements1;
WHEN value2 THEN sequence_of_statements2;
...
WHEN valuen THEN sequence_of_statementsn;
[ELSE else_sequence;]
END CASE [MyCase];

簡單迴圈：
LOOP
..
EXIT [WHEN condition];
END LOOP;

WHILE迴圈：
WHILE condition LOOP
...
END LOOP;

數字式FOR迴圈
FOR loop_counter IN [REVERSE] low_bound..high_bound LOOP
sequence_of_statement;
END LOOP;

遊標：
CURSOR xxx IS SELECT ...; 普通遊標
CURSOR xxx(xxx xxx,xxx xxx) IS ....; 引數化遊標

簡單迴圈:
OPEN xxx;
LOOP
FETCH xxx INTO v_xxx;
EXIT WHEN xxx%NOTFOUND;
END LOOP;
CLOSE xxx;

WHILE迴圈
OPEN cursor_name;
FETCH cursor_name INTO xxx;
WHILE cursor_name%FOUND LOOP
...
FETCH cursor_name INTO xxx;
END LOOP;
CLOSE cursor_name;

遊標FOR迴圈：
FOR v_xxx IN ('xxx') LOOP
...
END LOOP;

遊標中的SQL：
SELECT ... FROM ... FOR UPDATE[ OF column_reference ][ NOWAIT|WAIT n ]
n為要等待的秒數。

遊標變數使用範例：
DECLARE
TYPE emp_cur_typ IS CURSOR;
emp_cur emp_cur_typ;
v_stmt VARCHAR2(200);
BEGIN
v_stmt := 'SELECT * FROM table_name';
OPEN emp_cur FOR v_stmt [USING ...];
...
...
END;

INSERT INTO test values(...) RETURNING rowid INTO v_rowid;
UPDATE test Set id=... RETURNING xx INTO v_xx;
DELETE test where name='...' RETURNING id INTO v_id;

使用者自定義型別：
TYPE t_StudentRecord IS RECORD(
FirstName VARCHAR2(10),
LastName VARCHAR2(10),
CurrentCredits NUMBER(3)
);
v_Student t_StudentRecord;

批繫結：
declare
v_num int;
type t_numbers is table of int index by binary_integer;
v_nums t_numbers;
begin
for v_count in 1..10000 loop
v_nums(v_count):=v_count;
end loop;
forall v_count in 1..10000
insert into test values(v_nums(v_count),'a');
end;

自治事務：
CREATE OR REPLACE PROCEDURE Autonomous AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
...
END;

異常
異常(1)
e_TooManyStudents EXCEPTION;
...
RAISE e_TooManyStudents;
...
WHEN e_TooManyStudents THEN
...
異常(2)
e_TooManyStudents EXCEPTION;
PRAGMA EXCEPTION_INIT(exception_name,oracle_error_number);
...
...
WHEN e_TooManyStudents THEN
...
異常(3)
...
RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors]);
...
處理異常
EXCPETION
WHEN exception_name THEN
sequence_of_statements1;
[WHEN OTHERS THEN
sequence_of_statements3;]
END;

註釋： -- /* */

變數宣告語法：
variable_name type [CONSTANT] [NOT NULL] [:=value];

SQLCODE
SQLERRM[(int)]
注意SQLCODE和SQLERRM的值先賦給本地變數，然後才用於SQL語句中。因為這些函式是過程化的，所以不能直接用於SQL語句。

index-by表
TYPE tabletype IS TABLE OF type INDEX BY BINARY_INTEGER;
巢狀表
TYPE table_name IS TALBE OF table_type [NOT NULL]
可變陣列
TYPE type_name IS {VARRY|VARYING ARRAY}(maximum_size) OF element_type[NOT NULL];

CREATE OR REPLACE TYPE BookList AS VARRAY(10) OF NUMBER(4);
CREATE TABLE class_material(
department char(3),
required_reading BookList
)

CREATE OR REPLACE TYPE StudentList AS TABLE OF NUMBER(5);
CREATE TABLE libary_catalog(
catalog_number NUMBER(4),
checked_out StudentList
)
NESTED TABLE checked_out STORE as co_tab;

PL/SQL中呼叫function和procedure的方法：
declare
v_result varchar2(100);
begin
callproc1('hello from pl/sql');
v_result:=callfunc('hello from pl/sql');
dbms_output.put_line(v_result);
end;

declare
v_result varchar2(100);
begin
execute immediate 'call callproc1(''hello from pl/sql'')';
execute immediate 'call callfunc(''hello from pl/sql'') into :v_result' using out v_result;
dbms_output.put_line(v_result);
end;

包的說明(也叫做包頭)。
CREATE [OR REPLACE] PACKAGE package_name {IS|AS}
type_definition|
procedure_specification|
function_specification|
variable_declaration|
exception_declaration|
cursor_declaration|
pragma_declaration
END [package_name];

包體的說明。
CREATE [OR REPLACE] PACKAGE BODY xxxxx [IS|AS]
...
BEGIN
initialization_code;
END ...
在包頭和包體的AS後面新增 pragma serially_reusable ，說明該包是可序列復用的包


建立DML觸發器
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} triggering_event
[referencing_clause]
[WHEN trigger_condition]
[FOR EACH ROW]
在觸發器裡面有INSERT、UPDATE和DELETE三個布林函式可以用來確定操作是什麼。
2個特殊變數 :new :old
建立系統觸發器
CREATE [OR REPLACE] TRIGGER [schema.]trigger_name
{BEFORE |AFTER}
{ddl_event_list|database_event_list}
ON {DATABASE | [schema.]SCHEMA}
[when_clause]
trigger_body;

execute immediate 可以執行 DDL、DML和匿名PL/SQL語句塊。
execute immediate 還可以用來執行帶有繫結變數的語句。
execute immediate 'insert into test values(:id)' [INTO v_xx] [USING xxx];

成批繫結
FORALL v_Count IN 1..100 [SAVE EXCEPTION]
....
SQL%BULK_EXCEPTIONS.COUNT 錯誤的個數
SQL%BULK_EXCEPTIONS(v_count).error_index 出錯的索引數
SQL%BULK_EXCEPTIONS(v_count).error_code 出錯的錯誤號
SQLERRM(0-SQL%BULK_EXCEPTIONS(v_count).error_code) 顯示錯誤資訊

BULK COLLECT子句
SELECT num_col,char_col BULK COLLECT INTO v_Numbers,v_Strings FROM temp_tarble
OPEN c_char; FETCH c_char BULK COLLECT INTO v_String2; CLOSE c_char;
DELETE FROM temp_table WHERE num_col=v_number RETURNING char_col BULK COLLECT INTO v_Strings;

物件型別
CREATE OR REPLACE TYPE Point AS OBJECT{
x NUMBER,
y NUMBER,
MEMBER FUNCTION ToString RETURN VARCHAR2,
MEMBER FUNCTION Distance(p IN Point DEFAULT Point(0,0)) RETURN NUMBER
}

PL/SQL型別
1.標量型別
1.1 數字系列
BINARY_INTEGER, DEC, DECIMAL, DOUBLE PRECISION, FLOAT, INT, INTEGER, NATURAL,
NATURALN, NUMBER, NUMERIC, PLS_INTEGER, POSITIVE, POSITIVEN, REAL, SIGNTYPE, SMALLINT.
1.2 字元系列
CHAR, CHARACTER, LONG, NCHAR, NVARCHAR2, STRING, VARCHAR, VARCHAR2
1.3 日期/區間系列
DATE, INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE
1.4 行標識系列
ROWID, UROWID
1.5 布林系列
BOOLEAN
1.6 受託系列
MLSLABEL
1.7 原始系列
RAW, LONG RAW
2.複合型別
RECORD, NESTD TABLE, INDEX-BY TABLE, VARRAY
3.LOB型別
BFILE, CLOB, LOB, NLOB
4.引用型別
REF CURSOR, REF object type
5.物件型別
object type, SYS.ANYTYPE, SYS.ANYDATA, SYS.ANYDATASET

VARCHAR2(L[CHAR|BYTE])

BINARY_INTEGER 是以二進位制儲存，如果僅用於計算，則用該型別，有利於提高效能。

運算子優先順序
**、NOT 求幕、邏輯非
+、- 正、負
*、/ 乘、除
+、-、|| 加、減、字串連線
=、!=、、<=、>=、IS NULL 邏輯比
LIKE、BETWEEN、IN
AND 邏輯與
OR 邏輯或


TRANSLATE(string.from_str,to_str)
WIDTH_BUCKET(x,min,max,num_buckets)
BIN_TO_NUM(num[,num]...) 當使用分組集合和GROUP BY子句時，該函式很有用
COUNT()