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()

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/133835/viewspace-923338/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/133835/viewspace-923338/