PL/SQL

PL/SQL

SELECT * FROM v$version

匿名语句块的语法
DECLARE
   /* Declarative section is here */
BEGIN
   /* Executable section is here */
EXCEPTION
   /* Exception section is here */
END;

嵌套块
一个语句块可嵌套在一个外部语句块的执行部分或异常处理部分
PL/SQL不区分大小写。由引号引起来的字符串除外

引证标示符
如何想要标识符成为区分大小写的，可以将该标识符用双引号括起来

--单行注释
/* */ 多行注释

在语句块的声明部分对变量进行声明。声明一个变量的语法是：
variable_name type [CONSTANT] [NOT NULL] [:=value];
variable_name是该变量的名字，type是类型，而value是该变量的初始值

如果指定NOT NULL,必须赋初值
如何制定CONSTANT,必须赋初值且不能改变，只读的变量

可以使用DEFAULT替换":="
在声明部分，每一行只能声明一个变量

NUMBER 此类型可存储数字值，包括整数或浮点数
NUMBER(P,S)
P是精度，S是刻度范围

子类型
DEC,DECIMAL,NUMERIC
DOUBLE PRECISION,FLOAT
REAL
INTERGER,INT,SMALLINT

BINARY_INTERER
PLS_INTERGR

VARCHAR2(L),
L是变量的最大长度。这个长度是必需的--没有缺省值
Oracle9i，VARCHAR2变量的语法课扩展为：
VARCHAR2(L[CHAR|BYTE])

CHAR[(L)]
L是最大长度，以字节为单位，L可选，没有指定的话，缺省值就是1
Oracle9i，VARCHAR2变量的语法课扩展为：
CHAR(L[CHAR|BYTE])
LONG
NCHAR,NVARCHAR2

RAW(L)
L是以字节为单位的变量的长度，RAW用来存储定长的二进制数据。
LONG RAW

DATE
此类型用来存储时间和日期信息
TIMESTAMP[(P)]
P是秒字段的小数部分的精度，缺省是6

TIMESTAMP[(P)] WITH TIME ZONE
P是秒字段的小数部分的精度

INTERVAL INTERVAL类型用于存储两个时间戳之间的时间间隔

INTERVAL YEAR[(P)] TO MONTH
INTERVAL DAY[(DP)] TO SECOND[(SP)]

ROWID 与数据库的ROWID伪列式相同的
UROWID

BOOLEAN

MLSLABEL

引用类型 REF CURSOR ,这种类型，也即游标变量

LOB类型用于存储大型的对象

%TYPE
这个属性被其他在表列引用或者另一个变量上

DECLARE
  v_FirstName students.first_name%TYPE;

用户定义子类型
SUBTYPE new_type IS original_type;

对应隐藏掉的变量可以使用标号引用

表达式和运算符

赋值
variable:=expression

右值将被读出，而左值将被写入，所有的左值也都是右值
在一个特定PL/SQL语句中。仅能有一个赋值。

仅当其操作符是NULL的时候，IS NULL操作符才返回TRUE.
不能使用关系运算符对NULL进行测试，因为使用NULL操作符的所有关系表达式都返回NULL.
LIKE运算符用来对字符串进行模式匹配，下划线字符将精确地匹配一个字符，百分号将匹配零个或者多个字符

控制结构

IF THEN
ELSIF THEN
ELSE
END IF;

CASE
WHEN
WHEN
ELSE
END CASE;

LOOP

END LOOP;

WHILE condition    LOOP

END LOOP;

FOR loop_counter IN [REVERSE] low_bound..high_bound LOOP

END LOOP;

REVERSE,循环索引从最大值向最小值进行迭代

GOTO label;

PL/SQL对GOTO的使用有一些限制。从外层跳转到内层语句块，循环或者IF语句是非法的。
从一个异常处理语句块内部跳转到当前语句块也是非法的。

NULL语句，占位符

PL/SQL记录

TYPE record_type IS RECORD(

);

引用记录中的一个字段
record name.field name

如果要将一个记录赋值给另一个记录，这两个记录必须是相同类型的。


%ROWTYPE将返回一个基于表定义的类型

在PL/SQL中可直接使用的SQL语句只有DML和事务控制语句。

有一种方法可在PL/SQL中使用所有有效地SQL语句，包括DDL,即动态SQL

FORALL,批绑定
RETURINING子句

表引用
[schema.]table[@dblink]

数据库链接
CREATE DATABASE LINK link_name
CONNECT TO username IDENTIFIED BY password
USING sqlnet_string;

同义词
CREATE SYNONYM synonym_name FOR reference;

序列
CURRVAL NEXTVAL
CREATE SEQUENCE

LEVEL

ROWID伪列用在查询的选择列表中。它返回行特定的行标识符
ROWNUM在查询中返回当前的行号。它用来限制要处理的行的总数。

GRANT
用于对象权限的GRANT基本语法
GRANT privilege ON object To grantee [WITH GRANT OPTION];
privilege是需要的权限，object是被授予权限可访问的对象，而grantee是接受权限的用户
WITH GRANT OPTION,可以赋予其他用户权限。

用于系统权限的GRANT基本语法
GRANT privilege To grantee [WITH ADMIN OPTION];

REVOKE

用于对象权限的REVOKE基本语法
REVOKE privilege ON object FROM grantee [CASCADE CONSTRAMINTS] [FORCE];
用于系统权限的REVOKE基本语法
REVOKE privilege FROM grantee

角色
本质上一个权限的集合

GRANT privilege TO PUBLIC;

COMMIT[WORK];
ROLLBACK[WORK];

保存点
SAVEPOINT name;


ROLLBACK[WORK] TO SAVEPOINT name;

自治事务
PRAGMA AUTONOMOUS_TRANSACTION

SQL函数可以分为组函数和单行函数
PL/SQL过程性语句中。不能使用组函数，组函数仅可用于选择列表或查询的HAVING子句中

返回字符值的字符函数

CHR(x[USING NCHAR_CS])
返回数据库字符集中与x值相等的字符

CHR 和ASCII互为反函数.CHR返回给点字符编码的相应字符，而ASCII返回给定字符的字符编码

CONCAT(string1,string2)
返回连接上的string2的string1.

INITACP(string)
以字符串中每个单词第一个字符大写而单词中其余字母小写的形式返回string。

LOWER(string)
以小写形式返回string。

LPAD(string1,x[,string2])
将string2中长为x的字符填充在string1左边，然后返回string1.
LPAD和RPAD相似，差别是在左边而不是在右边进行填充。

LTRIM(string1,string2)
从string1最左边算起，删除出现在string2中的字符，然后返回string1.
LTRIM和RTRIM相似，除了它是从左边而不是从右边开始整理。

REPLACE(string,search_str[,replace_str])
返回用seach_str替换所有出现的replace_str后的string。

RPAD(string1,x,[,string2])
RTRIM(string1,[,string2])

SOUNDEX(string)
返回string的语音表示形式。

SUBSTR(string,a[,b])
返回string的一部分，a和b是以字符为单位。

SUBSTRB(string,a[,b])
返回string的一部分，a和b是以字节为单位。

SUBSTRC(string,a[,b])
返回string的一部分，a和b是以Unicode完全字符为单位。

SUBSTR2(string,a[,b])
返回string的一部分，a和b是以UCS2完全字符为单位。

SUBSTR4(string,a[,b])
返回string的一部分，a和b是以UCS4完全字符为单位。

TRANSLATE(string,from_str,to_str)
返回将所有出现的from_str中每个字符替代为to_str中相应字符后的string。

TRIM([{{LEADING|TRAILING|BOTH}
[trim_char|trim_char]}FROM]string)
返回删除了在开头，结尾或开头结尾都出现的trim_char之后的string。

UPPER(string)
以大写形式返回string。

ASCII(string)
返回数据库字符集中string的首字节的十进制表示形式

INSTR(string1,string2[,a][,b])
返回string1中包含string2的位置，a和b都是以字节为单位。

INSTRB(string1,string2[,a][,b])
INSTRC(string1,string2[,a][,b])
INSTR2(string1,string2[,a][,b])
INSTR4(string1,string2[,a][,b])

LENGTH(string)
返回以字符为单位的string的长度。

LENGTHB(string)
LENGTHC(string)
LENGTH2(string)
LENGTH4(string)

CONVERT(string,dest_charset[,source_charset])
将输入string转换为指定字符集dest_charset.

NCHR(x)
返回数据库国家字符集中值为x的字符

NLS_CHARSET_DECL_LEN(byte_width,charset)
返回一个NCHAR值的声明宽度（以字符为单位）。

NLS_CHARSET_ID(charset_name)
返回指定字符集charset_name的数字ID.

NLS_CHARSET_NAME([charset_ID])
返回指定字符集ID charset_ID的名字。

NLS_INITCAP(string[,nlsparams])
以字符串中每个单词第一个字符大写而单词中其余子母小写的形式返回string。

NLS_LOWER(string[,nlsparams])
以小写形式返回string。

NLS_UPPER(string[,nlsparams])
以大写形式返回string。

NLSSORT(string[,nlsparams])
返回用于排序string的字符串字节。

TRANSLATE(stirng USING
{CHAR_CSINCHAR_CS})
TRANSLATE...USING将输入string参数转换为数据库字符集（指定CHAR_CS）或数据库国家字符集（指定NCHAR_CS）。

UNISTR(s)
返回转换为数据库Unicode字符集的字符串s.

ABS(x),ACOS(x),ASIN(x),ATAN(x),ATAN2(x,y),BITAND(x,y),CEIL(x),COS(x),COSH(x),EXP(x),FLOOR(x),LN(x),MOD(x,y),POWER(x,y),ROUND(x,[,y]),SIGN(x),SIN(x),SINH(x),SQRT(x),TAN(x),TANH(x),TRUNC(x,[y]),WIDTH_BUCKET(x,min,max,num_buckets)

ADD_MONTHS(d,x)
返回加上x月后的日期d的值。

CURRENT_DATE
以DATE类型返回会话时区当前的日期。

CURRENT_TIMESTAMP[(precision)]
以TIMESTAMP WITH TIMEZONE类型返回会话时区当前的日期。

DBTIMEZONE
返回数据库的时区

EXTRACT({YEAR,MONTH,....)


LAST_DAY(d)
返回d所在月份的最后一天。

LOCALTIMESTAMP[(precision)]
以TIMESTAMP类型返回会话时区的当前日期。

MONTHS_BETWEEN(date1,date2)
返回date1和date2之间月的数目。

NEW_TIME(d,zone1,zone2)
当时区zone1中的日期和时间是d时，返回时区zone2的日期和时间。

NEXT_DAY(d,string)
返回在日期d之后由string限定的第一天。

ROUND(d,[,format])
将日期d按照由format指定的格式进行舍入。

SESSIONTIMEZONE
返回当前会话的时区

SYS_EXTRACT_UTC(dotetime)
从提供的datetime中以UTC返回时间。

SYSDATE
以DATE类型返回当前日期和时间。

SYSTIMESTAMP
以TIMESTAMP WITH TIMEZONE 类型返回当前日期和时间。

TRUNC(d[,format])
返回截尾到有format指定的单位的日期上。

TZ_OFFSET(timezone)
以字符字符串返回提供的timezone和UTC之间的偏移量。

.........


游标是指向上下文区的句柄或者指针。

以下是在PL/SQL中处理显式游标所必须得四个步骤
1）声明游标
2）为查询打开游标。
3）取得结果放入PL/SQL变量中。
4）关闭游标。

声明游标
CURSOR cursor_name IS select_statement;

cursor_name是游标名，select_statement是使用该游标处理的查询。

OPEN cursor_name;

用游标检索值
FETCH cursor_name INTO list_of_variables;
FETCH cursor_name INTO PL/SQL_record;

CLOSE cursor_name;


SELECT FOR UPDATE游标
WHERE CURRENT OF


游标变量是一种引用类型。
REF type_name IS REF CURSOR[RETURN return_type];
type是已经定义的类型。关键字REF指出新类型是指向已定义类型的指针。游标变量的类型为REF CURSOR.
type_name是新引用类型的名字，return_type是说明最终被该游标变量返回的选择列表类型的记录类型。


游标变量是引用类型的，所以声明时并不分配存储空间。在使用之前，它必须指向有效地内容区域。

客户端分配

SQL_CURSOR v_CursorVar;
EXEC SQL ALLOCATE:v_CursorVar;


为查询打开游标变量
OPEN cursor variable FOR select_statement;


处理异常

EXCEPTION
WHEN exception_name THEN
sequence_of_statements1;
WHEN exception_name THEN
sequence_of_statements2;
[WHEN OTHERS THEN
sequence_of_statements3;]
END;

PRAGMA EXCEPTION_INIT(exception_name,oracle_error_number);
exception_name是先于该编译指示声明的异常的名字。oracle_error_number是预计于此异常有关的错误代码。

RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors]);
error_number是一个在-20000和-20999之间的值，error_message是于此错误相关的文本，keep_error是一个布尔值。


PL/SQL有两种集合类型：记录和集合。记录允许你将多个变量当做一个整体进行处理。类似于c语言中的struct(结构)。集合石组合相同类型的变量，类似于c语言中的数组。一共三种集合类型，index-by表，嵌套表和可变数组。


index-by表
TYPE tabletype IS TABLE OF type INDEX BY BINARY_INTEGER;
tabletype是所定义新类型的类型名，type是一个预定义的类型，或通过%TYPE或%ROWTYPE对一个类型的引用。

定义
tablename tabletype;
引用
tablename(index);
如果是一个记录或对象，引用其中的元素可以使用：
table(index).field;

嵌套表
TYPE table_name is TABLE OF table_type [NOT NULL];
table_name是新类型的类型字，table_type是嵌套表中每一个元素的类型。table_type可以是用户定义的对象类型，也可以是使用%TYPE的表达式。但是它不可以是BOOLEAN,NCHAR,NCLOB,NVARCHAR2或REF CURSOR.

index-by表与嵌套表之间的唯一句法区别为是否存在INDEX BY BINARY_INTEGER子句。如果这个子句不存在，该类型是嵌套表类型。如果这个子句存在，该类型则为index-by表类型。


可变数组
元素在内存中连续存储。
TYPE type_name IS {VARRY | VARYING ARRAR} (maximum_size) OF element_type[NOT NULL];
type_name是新可变数组类型的类型名，mamximum_size是一个指定可变数组中元素最大数目的一个整数，element_type是一个PL/SQL标量，记录或对象类型。

SQL表操作符
TABLE(subquery)
subquery是一个返回嵌套表列的查询。

select department,course,column_value
from class_material,TABLE(required_reading);


集合方法
collection_instance.method_or_attribute;

NEXT和PRIOR用来增加和减少集合的关键字值
NEXT(n)
PRIOR(n)

NEXT(n)返回紧接在位置n处元素后面的那个元素的关键字
PRIOR(n)返回紧接在位置n处元素前面的那个元素的关键字

主要的PL/SQL语句块有两种：匿名块和命名块。匿名块（以DECLARE或BEGINk开始）在每次被提交时都被编译。匿名块也不存储在数据库中，而且不能从其他PL/SQL语句块直接调用。 命名快可以存储在数据库中，并在适当的时候运行。

创建过程
CREATE[OR REPLACE] PROCEDURE procedure_name
[(argument[{IN|OUT|IN OUT}]type,
...
argument[{IN|OUT|IN OUT}]type)] {IS|AS}
procedure_body

procedure_name是将要创建的过程的过程名，argument是一个过程参数的名称，type是相关参数的类型，precedure_body是组成该过程代码的PL/SQL语句块。

过程调用本身就是一个PL/SQL语句，而函数调用是作为表达式的一部分被调用的。函数本身不能作为一个语句。

创建函数
CREATE[OR REPLACE] FUNCTION function_name
[(argument[{IN|OUT|IN OUT}]type,
...
argument[{IN|OUT|IN OUT}]type)]
RETURN return_type {IS|AS}
function_body
function_name是该函数的函数名，其argument和type与过程的参数和类型一致。return_type是函数返回值的类型，function_body是包含该函数代码的PL/SQL语句块。

RETURN语句
RETURN expression;

删除过程和函数
DROP PROCEDURE procedure_name;
DROP FUNCTION function_name;

通过引用和通过数值传递参数
子程序参数可以用两种方式中的任意一种进行传递：或通过引用，或通过参数，当参数通过引用进行传递时，实际参数的指针被传递到相应的形式参数，相反，当参数通过数值传递时，该参数从实际参数中被复制到了形式参数中。
PL/SQL将通过引用来传递IN参数，而通过数值来传递IN OUT和OUT参数。

使用NCOPY
paramter_name[mode]NOCOPY datatype
paramter_name是参数的参数名，mode是参数模式（IN,OUT或IN OUT）,datatype是参数的数据类型。如何存在NOCOPY,PL/SQL编译器将试图通过引用传递参数，而不是通过数值传递。在IN参数上使用NOCOPY将产生编译错误，因为IN参数总是通过引用传递的，所以NOCOPY并不适用。


参数缺省值
paramter_name[mode]paramter_type
{:=|DEFAULT}initial_value
paramter_name是形式参数的名称，mode是参数的方式（IN,OUT或IN OUT），paramter_type是参数的类型，initial_value是缺省赋予形式参数的值。

CALL语句
CALL subprogram_name([argument_list])[INFO host_variable];
subprogram_name是一个独立的或打包的子程序。它也可以是对象类型的方法，而且可以位于远程数据库。argument_list是用逗号分割的参数列表，host_varibale是用来检索函数返回值的主机变量。

CALL是SQL语句，它在PL/SQL语句块中无效
CALL 必须带括号

包是PL/SQL中允许相关对象共同存储的构造。包有两个独立的部分：说明和包体。其中每一个部分都在数据字典中单独存储。

包的说明
包的说明（也叫做包头）包含关于包的内容的信息。
CREATE[OR REPLACE] PACKAGE package_name{IS|AS}
type_definition|
procedure_specification|
function_specification|
variable_declaration|
exception_declaration|
cursor_declaration|
pragma_declaration
END[pckage_name];
package_name是包的名称。


重载封装子程序
在保内，过程和函数可以重载，这意味着有多个具有相同名称但参数不同的过程或函数。

包的初始化
从封装子程序第一次调用时，或者对封装变量或类型进行引用时，就对包进行了实例化，这意味着把包从磁盘读取到存储器中，并运行被调用子程序的编译代码。
CREATE OR PRPLACE PACKAGE BODY package_nmae{IS|AS}
...
BEGIN
 initalization_code;
END[package_name];
package_name是包的名称，initialization_code是要运行的代码。


关于子程序的信息可以通过各种数据字典视图访问。user_objects视图包含当期用户所有的对象（包括存储子程序）的信息。这些信息包括：该对象是何时创建和最后修改的，对象的类型（表，序列，函数等），以及对象的有效性。user_source视图包含对象的原始源代码。user_errors视图包含关于编译错误的信息。

当一个存储过程或存储函数被编译时，它所引用的所有Oracle对象都记录在数据字典中。

人工重编译
ALTER PROCEDURE procedure_name COMPILE;
ALTER PROCEDURE function_name COMPILE;
ALTER PROCEDURE package_name COMPILE;
ALTER PROCEDURE package_name COMPILE SPECIFICATION;
ALTER PROCEDURE package_name COMPILE BODY;

可串行复用的包
  PRAGMA SERIALLY_REUSABLE;

EXECUTE特权
为了允许对一个表的访问，要使用SELECT,INSERT,UPDATE和DELETE对象特权。GRANT语句把这些特权给予数据库用户或任务。对于存储子程序和包，相关的特权是EXECUTE.

AUTHID语法
CREATE[OR REPLATE]FUNCTION function_name
[parameter_list]RETURN return_type
[AUTHID{CURRENT_USER|DEFINER}]{IS|AS}
function_body;

CREATE[OR REPLATE]PROCEDURE procedure_name
[parameter_list]RETURN return_type
[AUTHID{CURRENT_USER|DEFINER}]{IS|AS}
procedure_body;

CREATE[OR REPLATE]PACKAGE package_name
[parameter_list]RETURN return_type
[AUTHID{CURRENT_USER|DEFINER}]{IS|AS}
package_body;

函数的纯度级别
PRAGMA RESTRICT_REFERENCES(subprogram_or_package_name,
WNDS[,WNPS][,RNDS][,RNPS]);


PRAGMA RESTRICT_REFERENCES(DEFAULT,
WNDS[,WNPS][,RNDS][,RNPS]);

TRUST关键字

共享池是在子程序运行时包含已编译子程序伪代码及其他代码的SGA的一部分。第一次调用一个存储子程序时，把伪代码从磁盘中装载到共享池中。一旦对象不再被引用，它就被自由释放。对象使用最近最少使用(LRU)算法从共享池中被释放出来。

DEMS_SHARED_POOL.KEEP过程被用来在池中锁定对象。
PRGCEDURE KEEP(name VARCHAR2,
        flag CHAR DEFAULT 'P');

UNKEEP是从共享池中删除一个保持对象的唯一方法。

PRGCEDURE UNKEEP(name VARCHAR2,
        flag CHAR DEFAULT 'P');

SIZES将把共享池的内容响应到屏幕上。
PRGCEDURE SIZES(minsize NUMBER);

PROCEDURE ABORTED_REQUEST_THRESHOLD(threshold_size NUMBER);

创建触发器
CREATE[OR REPLACE]TRIGGER trigger_name
{BEFORE|AFTER|INSTEAD OF} triggering_event
[referencing_clause]
[WHEN rigger_condition]
[FOR EACH ROW]
trigger_body;
trigger_name是触发器的名称，triggering_event指定触发器的事件（很可能包括一个特定的表或视图），tigger_body是触发器的主要代码。referencing_clasue被用来引用在行中目前被一个不同的名称修改的数据。如何when子句中的trigger_condition存在，它首先被求值，而触发器的体只有在这个条件求值为TRUE时才会执行。

在行级别触发器中的相关标识符，:new，:old，:parent；

REFERENCING子句
REFERENCING [OLD AS old_name][NEW AS new_name]

WHEN子句
WHEN trigger_condition

创建系统触发器
CREATE[OR REPLACE]TRIGGER [schema.] trigger_name
{BEFORE|AFTER}
{ddl_event_list|database_event_list}
ON{DATABASE|[schema.]SCHEMA}
[when_clause]
trigger_body;

ddl_event_list是一个或多个DDL事件（通过OR关键字分开），database_event_list是一个或多个数据库事件（通过OR关键字分开）。

数据字典视图
当创建已给触发器时，其源代码被存储在数据字典视图user_triggers中。all_triggers包含当前用户可以访问（但可以被其他用户拥有）的触发器的信息。dba_triggers包含关于数据库中所有触发器的信息

DROP TRIGGER trigger_name;

ALTER TRIGGER triggername{DISABLE|ENABLE};


使用group by 子句
当使用group by时，未在group by 部分用到的表列在select部分出现时必须使用分组函数。

使用having子句
having子句允许用户指定对一个记录组的搜索条件。而通常的where查询条件只针对单记录，不针对记录组。

clear screen

物理数据库结构

数据文件
所有的数据均存储在数据文件之中。

数据文件的特性
一个数据文件只能与一个数据库有关；
具有自动扩展的特性；
一个或多个数据文件组成数据库的存储逻辑单元表空间；
数据文件中的数据在正常数据库操作中被读取并缓存到Oracle内存缓冲中。
更新或新的数据不需要马上写到数据文件中。为了减少磁盘访问提供性能，数据在内存中缓冲并一次性的写到适当的数据文件中，该操作由DBWn后台进程决定。

控制文件
控制文件保存有描述数据库物理结构的信息。
例如，控制文件包括以下信息：
数据库名称；
数据文件和重做日志文件的名称和位置；
数据库创建时间点；

重做日志文件
每一数据库有两个或多个重做日志文件集合。重做日志的主要功能是记录数据的全部改变。
重做日志文件中的信息只是用于从系统或介质崩溃中进行恢复

归档日志文件
归档模式下，oracle产生归档日志。

参数文件
包含一系列的配置参数，推荐使用spfile。

警告和跟踪日志文件

备份文件
用户管理备份和恢复需要实际的还原备份文件，而服务器管理的备份和恢复管理备份进程。

逻辑数据库结构