Oracle PL/SQL语言基础

Oracle PL/SQL语言基础

函数
　　函数是命名了的、存储在数据库中的PL/SQL程序块。函数接受零个或多个输入参数，有一个返回值，返回值的数据类型在创建函数时定义。定义函数的语法如下：

FUNCTION name [{parameter[,parameter,...])] RETURN datatypes IS [local declarations] BEGIN execute statements [EXCEPTION exception handlers] END [name]

过程
　　存储过程是一个PL/SQL程序块，接受零个或多个参数作为输入(INPUT)或输出(OUTPUT)、或既作输入又作输出(INOUT)，与函数不同，存储过程没有返回值，存储过程不能由SQL语句直接使用，只能通过EXECUT命令或PL/SQL程序块内部调用，定义存储过程的语法如下：

PROCEDURE name [(parameter[,parameter,...])] IS [local declarations] BEGIN execute statements [EXCEPTION exception handlers ] END [name]

 

 

 

 

 

 

 

 

 

 

包(package)
　　包其实就是被组合在一起的相关对象的集合，当包中任何函数或存储过程被调用，包就被加载入内存中，包中的任何函数或存储过程的子程序访问速度将大大加快。
包由两个部分组成：规范和包主体(body),规范描述变量、常量、游标、和子程序，包体完全定义子程序和游标。
触发器(trigger)
　 触发器与一个表或数据库事件联系在一起的，当一个触发器事件发生时，定义在表上的触发器被触发。变量和常量
　　变量存放在内存中以获得值，能被PL/SQL块引用。你可以把变量想象成一个可储藏东西的容器，容器内的东西是可以改变的。
声明变量
　　变量一般都在PL/SQL块的声明部分声明，PL/SQL是一种强壮的类型语言，这就是说在引用变量前必须首先声明，要在执行或异常处理部分使用变量，那么变量必须首先在声明部分进行声明。
　　声明变量的语法如下：

Variable_name [CONSTANT] databyte [NOT NULL][:=|DEFAULT expression]

　　注意:可以在声明变量的同时给变量强制性的加上NOT NULL约束条件，此时变量在初始化时必须赋值。
　　给变量赋值
　　给变量赋值有两种方式：
　　. 直接给变量赋值
　　　X:=200;

　 Y:=Y+(X*20);
　　. 通过SQL SELECT INTO 或FETCH INTO给变量赋值

SELECT SUM(SALARY),SUM(SALARY*0.1) INTO TOTAL_SALARY,TATAL_COMMISSION FROM EMPLOYEE WHERE DEPT=10;

常量
　　常量与变量相似，但常量的值在程序内部不能改变，常量的值在定义时赋予，，他的声明方式与变量相似，但必须包括关键字CONSTANT。常量和变量都可被定义为SQL和用户定义的数据类型。

ZERO_VALUE CONSTANT NUMBER:=0;

　　这个语句定了一个名叫ZERO_VALUE、数据类型是NUMBER、值为0的常量。

标量(scalar)数据类型
　　标量(scalar)数据类型没有内部组件，他们大致可分为以下四类：
　　　. number
　　　. character
　　　. date/time
　　　. boolean

　　表1显示了数字数据类型；表2显示了字符数据类型；表3显示了日期和布尔数据类型。

表1 Scalar Types:Numeric

Datatype	Range	Subtypes	description
BINARY_INTEGER	-214748-2147483647	NATURAL NATURAL NPOSITIVE POSITIVEN SIGNTYPE	用于存储单字节整数。 要求存储长度低于NUMBER值。 用于限制范围的子类型(SUBTYPE): 　NATURAL:用于非负数 　POSITIVE:只用于正数 　NATURALN:只用于非负数和非NULL值 　POSITIVEN:只用于正数，不能用于NULL值 　SIGNTYPE:只有值:-1、0或1.
NUMBER	1.0E-130-9.99E125	DEC DECIMAL DOUBLE PRECISION FLOAT INTEGERIC INT NUMERIC REAL SMALLINT	存储数字值，包括整数和浮点数。可以选择精度和刻度方式，语法： number[（[,]）]。 缺省的精度是38,scale是0.
PLS_INTEGER	-2147483647-2147483647		与BINARY_INTEGER基本相同，但采用机器运算时，PLS_INTEGER提供更好的性能 。

　　表2 字符数据类型

datatype	rang	subtype	description
CHAR	最大长度32767字节	CHARACTER	存储定长字符串，如果长度没有确定，缺省是1
LONG	最大长度2147483647字节		存储可变长度字符串
RAW	最大长度32767字节		用于存储二进制数据和字节字符串，当在两个数据库之间进行传递时，RAW数据不在字符集之间进行转换。
LONGRAW	最大长度2147483647		与LONG数据类型相似，同样他也不能在字符集之间进行转换。
ROWID	18个字节		与数据库ROWID伪列类型相同，能够存储一个行标示符，可以将行标示符看作数据库中每一行的唯一键值。
VARCHAR2	最大长度32767字节	STRINGVARCHAR	与VARCHAR数据类型相似，存储可变长度的字符串。声明方法与VARCHAR相同

　　表3 DATE和BOOLEAN

datatype	range	description
BOOLEAN	TRUE/FALSE	存储逻辑值TRUE或FALSE,无参数
DATE	01/01/4712 BC	存储固定长的日期和时间值，日期值中包含时间

LOB 数据类型
　　LOB(大对象,Large object) 数据类型用于存储类似图像，声音这样的大型数据对象，LOB数据对象可以是二进制数据也可以是字符数据，其最大长度不超过4G。LOB数据类型支持任意访问方式，LONG只支持顺序访问方式。LOB存储在一个单独的位置上，同时一个"LOB定位符"(LOB locator)存储在原始的表中，该定位符是一个指向实际数据的指针。在PL/SQL中操作LOB数据对象使用ORACLE提供的包 DBMS_LOB.LOB数据类型可分为以下四类：
　　. BFILE
　　. BLOB
　　. CLOB
　　. NCLOB

操作符
　　与其他程序设计语言相同，PL/SQL有一系列操作符。操作符分为下面几类：
　　. 算术操作符
　　. 关系操作符
　　. 比较操作符
　　. 逻辑操作符
　　算术操作符如表4所示

operator	operation
+	加
-	减
/	除
*	乘
**	乘方

　　关系操作符主要用于条件判断语句或用于where子串中，关系操作符检查条件和结果是否为true或false,表5是PL/SQL中的关系操作符

operator	operation
<	小于操作符
<=	小于或等于操作符
>	大于操作符
>=	大于或等于操作符
=	等于操作符
!=	不等于操作符
<>	不等于操作符
:=	赋值操作符

　　表6 显示的是比较操作符

operator	operation
IS NULL	如果操作数为NULL返回TRUE
LIKE	比较字符串值
BETWEEN	验证值是否在范围之内
IN	验证操作数在设定的一系列值中

　　表7.8显示的是逻辑操作符

operator	operation
AND	两个条件都必须满足
OR	只要满足两个条件中的一个
NOT	取反

执行部分
　　执行部分包含了所有的语句和表达式,执行部分以关键字BEGIN开始，以关键字EXCEPTION结束，如果EXCEPTION不存在，那么将以关键字 END结束。分号分隔每一条语句，使用赋值操作符:=或SELECT INTO或FETCH INTO给每个变量赋值，执行部分的错误将在异常处理部分解决，在执行部分中可以使用另一个PL/SQL程序块，这种程序块被称为嵌套块
　　所有的SQL数据操作语句都可以用于执行部分，PL/SQL块不能再屏幕上显示SELECT语句的输出。SELECT语句必须包括一个INTO子串或者是游标的一部分，执行部分使用的变量和常量必须首先在声明部分声明，执行部分必须至少包括一条可执行语句，NULL是一条合法的可执行语句，事物控制语句 COMMIT和ROLLBACK可以在执行部分使用，数据定义语言(Data Definition language)不能在执行部分中使用，DDL语句与EXECUTE IMMEDIATE一起使用或者是DBMS_SQL调用。
执行一个PL/SQL块
　　SQL*PLUS中匿名的PL/SQL块的执行是在PL/SQL块后输入/来执行，如下面的例子所示

全面探讨PL/SQL的复合数据类型

PL/SQL有两种复合数据结构：记录和集合。记录由不同的域组成，集合由不同的元素组成。在本文中我们将讨论记录和集合的类型、怎样定义和使用记录和集合。
　　PL/SQL 记录
　　记录是PL/SQL的一种复合数据结构，scalar数据类型和其他数据类型只是简单的在包一级进行预定义，但复合数据类型在使用前必须被定义，记录之所以被称为复合数据类型是因为他由域这种由数据元素的逻辑组所组成。域可以是scalar数据类型或其他记录类型，它与c语言中的结构相似，记录也可以看成表中的数据行，域则相当于表中的列，在表和虚拟表（视图或查询）中非常容易定义和使用，行或记录中的每一列或域都可以被引用或单独赋值，也可以通过一个单独的语句引用记录所有的域。在存储过程或函数中记录也可能有参数。

　　创建记录
　　在PL/SQL中有两种定义方式：显式定义和隐式定义。一旦记录被定义后，声明或创建定义类型的记录变量，然后才是使用该变量。隐式声明是在基于表的结构或查询上使用%TYPE属性，隐式声明是一个更强有力的工具，这是因为这种数据变量是动态创建的。
　　显式定义记录
　　显式定义记录是在PL/SQL程序块中创建记录变量之前在声明部分定义。使用type命令定义记录，然后在创建该记录的变量。语法如下：

TYPE record_type IS RECORD (field_definition_list);

　　field_definition_list是由逗号分隔的列表。

　　域定义的语法如下：

field_name data_type_and_size [NOT NULL][{:=|DEFAULT} default_value]

　　域名必须服从与表或列的命名规则相同的命名规则。下面我们看一个例子：

DELCARE TYPE stock_quote_rec IS RECORD (symbol stock.symbol%TYPE ,bid NUMBER(10,4) ,ask NUMBER(10,4) ,volume NUMBER NOT NULL:=0 ,exchange VARCHAR2(6) DEFAULT 'NASDAQ' ); real_time_quote stock_quote_rec; variable

　　域定义时的%TYPE属性用于引用数据库中的表或视图的数据类型和大小,而在此之前程序不知道类型和大小。在上面的例子中记录域在编译时将被定义为与列SYMBOL相同的数据类型和大小,当代码中要使用来自数据库中的数据时，在变量或域定义中最好使用%TYPE来定义。

隐式定义记录
　　隐式定义记录中，我们不用描述记录的每一个域。这是因为我们不需要定义记录的结构，不需要使用TYPE语句，相反在声明记录变量时使用%ROWTYPE命令定义与数据库表，视图，游标有相同结构的记录，与TYPE命令相同的是它是一种定义获得数据库数据记录的好方法。

DECLARE accounter_info accounts%ROWTYPR; CURSOR xactions_cur(acct_no IN VARCHAR2) IS SELECT action,timestamp,holding FROM portfolios WHERE account_nbr='acct_no' ; xaction_info xactions_cur%ROWTYPE; variable

有一些PL/SQL指令在使用隐式定义记录时没有使用%ROWTYPE属性，比如游标FOR循环或触发器中的:old和:new记录。

DELCARE CURSOR xaction_cur IS SELECT action,timeamp,holding FROM portfolios WHERE account_nbr='37' ; BEGIN FOR xaction_rec in xactions_cur LOOP IF xactions_rec.holding='ORCL' THEN notify_shareholder; END IF; END LOOP;

使用记录
　　用户可以给记录赋值、将值传递给其他程序。记录作为一种复合数据结构意味作他有两个层次可用。用户可以引用整个记录，使用select into或fetch转移所有域，也可以将整个记录传递给一个程序或将所有域的值赋给另一个记录。在更低的层次，用户可以处理记录内单独的域，用户可以给单独的域赋值或者在单独的域上运行布尔表达式，也可以将一个或更多的域传递给另一个程序。
　　引用记录
　　记录由域组成，访问记录中的域使用点（.）符号。我们使用上面的例子看看

DELCARE TYPE stock_quote_rec IS RECORD (symbol stock.symbol%TYPE ,bid NUMBER(10,4) ,ask NUMBER(10,4) ,volume NUMBER NOT NULL:=0 ,exchange VARCHAR2(6) DEFAULT 'NASDAQ' ); TYPE detailed_quote_rec IS RECORD (quote stock_quote_rec ,timestamp date ,bid_size NUMBER ,ask.size NUMBER ,last_tick VARCHAR2(4) ); real_time_detail detail_quote_rec; BEGIN real_time_detail.bid_size:=1000; real_time_detail.quote.volume:=156700; log_quote(real_time_detail.quote);

　　给记录赋值
　　给记录或记录中的域赋值的方法有几种，可以使用SELECT INTO或FETCH给整个记录或单独的域赋值， 可以将整个记录的值赋给其他记录，也可以通过给每个域赋值来得到记录，以下我们通过实例讲解每一种赋值方法。
　　1、使用SELECT INTO
　　使用SELECT INTO给记录赋值要将记录或域放在INTO子串中，INTO子串中的变量与SELECT中列的位置相对应。
　　例：

DECLARE stock_info1 stocks%ROWTYPE; stock_info2 stocks%ROWTYPE; BEGIN SELECT symbol,exchange INTO stock_info1.symbol,stock_info1.exchange FROM stocks WHERE symbol='ORCL'; SELECT * INTO stock_info2 FROM stocks WHERE symbol='ORCL';

　　2、使用FETCH
　　如果SQL语句返回多行数据或者希望使用带参数的游标，那么就要使用游标，这种情况下使用FETCH语句代替INSTEAD INTO是一个更简单、更有效率的方法，但在安全性较高的包中FETCH的语法如下：

FETCH cursor_name INTO variable;

　　我们改写上面的例子：

DECLARE CURSOR stock_cur(symbol_in VARCHAR2) IS SELECT symbol,exchange,begin_date FROM stock WHERE symbol=UPPER(symbol_in); stock_info stock_cur%ROWTYPE BEGIN OPEN stock_cur('ORCL'); FETCH stock_cur INTO stock_info;

　　使用赋值语句将整个记录复制给另一个记录是一项非常有用的技术，不过记录必须精确地被声明为相同的类型，不能是基于两个不同的TYPE语句来获得相同的结构。
　　例：

DECLARE TYPE stock_quote_rec IS RECORD (symbol stocks.symbol%TYPE ,bid NUMBER(10,4) ,ask number(10,4) ,volume NUMBER ); TYPE stock_quote_too IS RECORD (symbol stocks.symbol%TYPE ,bid NUMBER(10,4) ,ask number(10,4) ,volume NUMBER ); --这两个记录看上去是一样的，但实际上是不一样的 stock_one stocks_quote_rec; stock_two stocks_quote_rec; --这两个域有相同的数据类型和大小 stock_also stock_rec_too；--与stock_quote_rec是不同的数据类型 BEGIN stock_one.symbol:='orcl'; stock_one.volume:=1234500; stock_two：=stock_one;--正确 syock_also：=stock_one;--错误，数据类型错误 stock_also.symbol:=stock_one.symbol; stock_also.volume:=stock_one.volume;

　　记录不能用于INSERT语句和将记录直接用于比较，下面两种情况是错误的：

　　　INSERT INTO stocks VALUES (stock_record);

　　和
　　　IF stock_rec1>stock_rec2 THEN
　　要特别注意考试中试题中有可能用%ROWTYPE来欺骗你，但这是错误的，记住这一点。还有可能会出现用记录排序的情况，ORACLE不支持记录之间的直接比较。对于记录比较，可以采用下面的两个选择：
　　. 设计一个函数，该函数返回scalar数据类型，使用这个函数比较记录，如

IF sort_rec(stock_one)>sort_rec(stock_two) THEN

　　. 可以使用数据库对象，数据库对象可以使用order或map方法定义，允许oracle对复合数据类型进行比较。关于数据库对象的讨论已经超越了本文的范围，要详细了解数据库对象，可以查阅oracle手册。

PL/SQL集合
　　集合与其他语言中的数组相似，在ORACLE7.3及以前的版本中只有一种集合称为PL/SQL表，这种类型的集合依然保留，就是索引（INDEX_BY）表，与记录相似，集合在定义的时候必须使用TYPE语句，然后才是创建和使用这种类型的变量。
　　集合的类型
　　PL/SQL有三种类型的集合
　　. Index_by表
　　. 嵌套表
　　. VARRAY
　　这三种类型的集合之间由许多差异，包括数据绑定、稀疏性(sparsity)、数据库中的存储能力都不相同。绑定涉及到集合中元素数量的限制， VARRAY集合中的元素的数量是有限，Index_by和嵌套表则是没有限制的。稀疏性描述了集合的下标是否有间隔，Index_by表总是稀疏的，如果元素被删除了嵌套表可以是稀疏的，但VARRAY类型的集合则是紧密的，它的下标之间没有间隔。
　　Index_by表不能存储在数据库中，但嵌套表和VARRAY可以被存储在数据库中。
　　虽然这三种类型的集合有很多不同之处，但他们也由很多相似的地方：
　　　. 都是一维的类似数组的结构
　　　. 都有内建的方法
　　　. 访问由点分隔
　　Index_by表
　　Index_by表集合的定义语法如下：

TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY BINARY_INTERGET;

　　这里面重要的关键字是INDEX BY BINARY_INTERGET，没有这个关键字，那么集合将是一个嵌套表，element_type可以是任何合法的PL/SQL数据类型，包括： PLS/INTEGER、SIGNTYPE、和BOOLEAN。其他的集合类型对数据库的数据类型都有限制，但Index_by表不能存储在数据库中，所以没有这些限制。
　　一旦定义了index_by表，就可以向创建其他变量那样创建index_by表的变量：

DECLARE TYPE symbol_tab_typ IS TABLE OF VARCHAR2(5) INDEX BY BINARY_INTEGER; symbol_tab symbol_tab_typ; BEGIN

　　嵌套表

　　嵌套表非常类似于Index_by表，创建的语法也非常相似。使用TYPE语句，只是没有INDEX BY BINARY_INTEGER子串。
　　TYPE type_name IS TABLE OF element_type [NOT NULL]
　　NOT NULL选项要求集合所有的元素都要有值，element_type可以是一个记录，但是这个记录只能使用标量数据类型字段以及只用于数据库的数据类型（不能是PLS_INTEGER,BOOLEAN或SIGNTYPE）。
　　嵌套表和VARRAY都能作为列存储在数据库表中，所以集合自身而不是单个的元素可以为NULL,ORACLE称这种整个集合为NULL的为"自动设置为NULL(atomically NULL)"以区别元素为NULL的情况。当集合为NULL时，即使不会产生异常，用户也不能引用集合中的元素。用户可以使用IS NULL操作符检测集合是否为NULL。
　　存储在一个数据库中的嵌套表并不与表中的其它数据存放在同一个数据块中，它们实际上被存放在第二个表中。正如没有order by子句select语句不能保证返回任何有顺序的数据，从数据库中取回的嵌套表也不保证元素的顺序。由于集合数据是离线存储的，对于大型集合嵌套表是一个不错的选择。

VARRAY
　　VARRAY或数据变量都有元素的限制。想起他集合一样VARRAY定义仍然使用TYPE语句，但关键字VARRAY或VARRYING ARRAY告诉ORACLE这是一个VARRAY集合。

TYPE type_name IS [VARRAY|VARYING ARRAY] (max_size) OF element_type [NOT NULL]

　　max_size是一个整数，用于标示VARRAY集合拥有的最多元素数目。VARRAY集合的元素数量可以低于max_size,但不能超过max_size。element_type是一维元素的数据类型，如果element_type是记录，那么这个记录只能使用标量数据字段（与嵌套标相似）。NOT NULL子串表示集合中的每一个元素都必须有值。
　　与嵌套表相似，VARRAY能够自动为NULL,可以使用IS NULL操作符进行检测。与嵌套表不同的是，当VARRAY存储在数据库中时与表中的其他数据存放在同一个数据块中。正象列的排序保存在表的 SELECT*中一样元素的顺序保存在VARRAY中。同样由于集合是在线存储的，VARRAY很适合于小型集合。

使用集合
　　象记录一样，集合可以在两个层面上使用：
　　　. 操作整个集合
　　　. 访问集合中的单个元素
　　第一种情况使用集合名，第二种情况使用下标：
　　　collection(subscript)
　　index_by表的下标是两为的整数，可以为正也可以为负，范围是：-2147483647--2147483647。嵌套表和VARRAY表示元素在集合中的位置，用户很难灵活设计下标，这是因为：
　　　. 嵌套表开始是紧密的（相对于疏松）
　　　. VARRAY始终保持紧密
　　　. 这两种集合的下标都由1开始
　　初始化、删除、引用集合
　　使用集合之前必须要初始化，对于Index_by表初始化是自动进行的，但是对于嵌套表和VARRAY就必须使用内建的构造函数。如果重新调用，嵌套表和VARRAY自动置NULL，这不只是元素置NULL，而是整个集合置NULL。给集合内的元素赋值需要使用下标符号。将一个集合的值赋给另一个集合，只需要简单的使用赋值操作符。
　　Index_by集合初始化是最简单的，只要涉及其中的一个元素集合就被初始化了。

　　例：　

DECLARE TYPE symbol_tab_typ IS TABLE OF VARCHAR2(5) INDEX BY BINARY_INTEGER; TYPE account_tab_typ IS TABLE OF account%ROWTYPE INDEX BY BINARY_INTEGER; symbol_tab symbol_tab_typ; account_tab account_tab_typ; new_acct_tab account_tab_typ; BEGIN --初始化集合元素147和-3 SELECT * INTO account_tab(147) FROM accounts WHERE account_nbr=147; SELECT * INTO account_tab(-3) FROM accounts WHERE account_nbr=3003; IF account_tab(147).balance<500 THEN chang_maintnce_fee(147); END IF new_acct_tab:=account_tab; symbol_tab(1):="ORCL"; symbol_tab(2):="CSCO"; symbol_tab(3):="SUNM"; publish_portfolio(symbol_tab);

嵌套表和VARRAY由构造函数初始化，构造函数和集合的名字相同，同时有一组参数，每个参数对应一个元素，如果参数为NULL,那么对应的元素就被初始化为NULL,如果创建了元素，但没有填充数据，那么元素将保持null值，可以被引用，但不能保持数据。如果元素没有初始化，那么就不能引用该元素。
　　例：

DECLARE TYPE stock_list IS TABLE OF stock.symbol%TYPE; TYPE top10_list IS VARRAY (10) OF stocks.symbol%TYPE; biotech_stocks stock_list; tech_10 top10_list; BEGIN --非法，集合未初始化。 biotech_stocks(1):='AMGN'; IF biotech_stocks IS NULL THEN --初始化集合 biotech_stocks：=('AMGN','BGEN',IMCL','GERN',CRA'); END IF; tech_10:=top10_list('ORCL',CSCO','MSFT','INTC','SUNW','IBM',NULL,NULL); IF tech_10(7) IS NULL THEN tech_10(7):='CPQ'; END tech_10(8):='DELL';

　　在这个例子中，嵌套表BIOTECH_STOCKS初始化有5个元素，VARRAY tech_10集合最多能有10 个元素，但构造函数只创建了8个元素，其中还有两个元素是NULL值，并程序中给他们赋值。
　　初始化基于记录的集合，就必须将记录传递给构造函数，注意不能只是简单的将记录的域传递给构造函数。

例：

DECLARE TYPE stock_quote_rec IS RECORD (symbol stock.symbol%TYPE ,bid NUMBER(10,4) ,ask NUMBER(10,4) ,volume NUMBER NOT NULL:=0 ); TYPE stock_tab_typ IS TABLE OF stock_quote_rec; quote_list stock_tab_typ; single_quote stock_quote_rec; BEGIN single_quote.symbol:='OPCL'; single_quote.bid:=100; single_quote.ask:=101; single_quote.volume:=25000; --合法 quote_list:=stock_tab_typ(single_quote); --不合法 quote_list:=stock_tab_typ('CSCO',75,76,3210000); DBMS_OUTPUT.LINE(quote_list(1).bid);

集合的方法
　　除了构造函数外,集合还有很多内建函数，这些函数称为方法。调用方法的语法如下：
　　　collection.method
　　下表中列出oracle中集合的方法

方法	描述	使用限制
COUNT	返回集合中元素的个数
DELETE	删除集合中所有元素
DELETE()	删除元素下标为x的元素，如果x为null,则集合保持不变	对VARRAY非法
DELETE(,)	删除元素下标从X到Y的元素，如果X>Y集合保持不变	对VARRAY非法
EXIST()	如果集合元素x已经初始化，则返回TRUE, 否则返回FALSE
EXTEND	在集合末尾添加一个元素	对Index_by非法
EXTEND()	在集合末尾添加x个元素	对Index_by非法
EXTEND(,)	在集合末尾添加元素n的x个副本	对Index_by非法
FIRST	返回集合中的第一个元素的下标号，对于VARRAY集合始终返回1。
LAST	返回集合中最后一个元素的下标号, 对于VARRAY返回值始终等于COUNT.
LIMIT	返回VARRY集合的最大的元素个数，对于嵌套表和对于嵌套表和Index_by为null	Index_by集合无用
NEXT()	返回在元素x之后及紧挨着它的元素的值，如果该元素是最后一个元素，则返回null.
PRIOR()	返回集合中在元素x之前紧挨着它的元素的值，如果该元素是第一个元素，则返回null。
TRI M	从集合末端开始删除一个元素	对于index_by不合法
TRIM()	从集合末端开始删除x个元素	对index_by不合法

　　关于集合之间的比较
　　集合不能直接用于比较，要比较两个集合，可以设计一个函数，该函数返回一个标量数据类型。

IF stock_list1>stock_list2 ----非法 IF sort_collection(stock_list1)>sort_collection(stock_list2) THEN --合法

　　但可以比较在集合内的两个元素。

PL/SQL单行函数和组函数详解

函数是一种有零个或多个参数并且有一个返回值的程序。在SQL中Oracle内建了一系列函数，这些函数都可被称为SQL或PL/SQL语句，函数主要分为两大类：

　　单行函数
　　组函数
　　本文将讨论如何利用单行函数以及使用规则。
　　SQL中的单行函数
　　SQL和PL/SQL中自带很多类型的函数，有字符、数字、日期、转换、和混合型等多种函数用于处理单行数据，因此这些都可被统称为单行函数。这些函数均可用于SELECT,WHERE、ORDER BY等子句中，例如下面的例子中就包含了TO_CHAR,UPPER,SOUNDEX等单行函数。

SELECT me,TO_CHAR(hiredate,'day,DD-Mon-YYYY') FROM emp Where UPPER(me) Like 'AL%' ORDER BY SOUNDEX(me)

　　单行函数也可以在其他语句中使用，如update的SET子句，INSERT的VALUES子句，DELET的WHERE子句,认证考试特别注意在SELECT语句中使用这些函数，所以我们的注意力也集中在SELECT语句中。

　　NULL和单行函数

　　在如何理解NULL上开始是很困难的，就算是一个很有经验的人依然对此感到困惑。NULL值表示一个未知数据或者一个空值，算术操作符的任何一个操作数为NULL值，结果均为提个NULL值,这个规则也适合很多函数，只有CONCAT,DECODE,DUMP,NVL,REPLACE在调用了NULL参数时能够返回非NULL值。在这些中NVL函数时最重要的，因为他能直接处理NULL值，NVL有两个参数：NVL(x1,x2),x1和x2都式表达式，当x1为null时返回X2,否则返回x1。

　　下面我们看看emp数据表它包含了薪水、奖金两项，需要计算总的补偿

column name emp_id salary bonus key type pk nulls/unique nn,u nn fk table datatype number number number length 11.2 11.2

不是简单的将薪水和奖金加起来就可以了，如果某一行是null值那么结果就将是null，比如下面的例子：

update emp set salary=(salary+bonus)*1.1

　　这个语句中，雇员的工资和奖金都将更新为一个新的值，但是如果没有奖金，即 salary + null,那么就会得出错误的结论，这个时候就要使用nvl函数来排除null值的影响。
所以正确的语句是：

update emp set salary=(salary+nvl(bonus,0)*1.1

单行字符串函数
　　单行字符串函数用于操作字符串数据，他们大多数有一个或多个参数，其中绝大多数返回字符串
　　ASCII()
　　c1是一字符串，返回c1第一个字母的ASCII码，他的逆函数是CHR()

SELECT ASCII('A') BIG_A,ASCII('z') BIG_z FROM emp BIG_A BIG_z 65 122

　　CHR(＜i＞)[NCHAR_CS]
　　i是一个数字，函数返回十进制表示的字符

select CHR(65),CHR(122),CHR(223) FROM emp CHR65 CHR122 CHR223 A z B

　　CONCAT(,)
　　c1,c2均为字符串，函数将c2连接到c1的后面，如果c1为null,将返回c2.如果c2为null,则返回c1，如果c1、c2都为null，则返回null。他和操作符||返回的结果相同

select concat('slobo ','Svoboda') username from dual username slobo Syoboda

INITCAP()
　　c1为一字符串。函数将每个单词的第一个字母大写其它字母小写返回。单词由空格，控制字符，标点符号限制。

select INITCAP('veni,vedi,vici') Ceasar from dual Ceasar Veni,Vedi,Vici

　　INSTR(,[,＜i＞[,]])
　　c1,c2均为字符串，i,j为整数。函数返回c2在c1中第j次出现的位置，搜索从c1的第i个字符开始。当没有发现需要的字符时返回0,如果i为负数，那么搜索将从右到左进行，但是位置的计算还是从左到右，i和j的缺省值为1.

select INSTR('Mississippi','i',3,3) from dual INSTR('MISSISSIPPI','I',3,3) 11 select INSTR('Mississippi','i',-2,3) from dual INSTR('MISSISSIPPI','I',3,3) 2

　　INSTRB(,[,i[,j])
　　与INSTR（）函数一样，只是他返回的是字节，对于单字节INSTRB()等于INSTR()
　　LENGTH()
　　c1为字符串，返回c1的长度，如果c1为null，那么将返回null值。

select LENGTH('Ipso Facto') ergo from dual ergo 10

　　LENGTHb()
　　与LENGTH()一样，返回字节。
　　lower()
　　返回c的小写字符，经常出现在where子串中

select LOWER(colorname) from itemdetail WHERE LOWER(colorname) LIKE '%white%' COLORNAME Winterwhite

　　LPAD(,＜i＞[,])
　　c1,c2均为字符串，i为整数。在c1的左侧用c2字符串补足致长度i,可多次重复，如果i小于c1的长度，那么只返回i那么长的c1字符，其他的将被截去。c2的缺省值为单空格，参见RPAD。

select LPAD(answer,7,'') padded,answer unpadded from question; PADDED UNPADDED Yes Yes NO NO Maybe maybe

　　LTRIM(,)
　　把c1中最左边的字符去掉，使其第一个字符不在c2中，如果没有c2，那么c1就不会改变。

select LTRIM('Mississippi','Mis') from dual LTR ppi

　　RPAD(,＜i＞[,])
　　在c1的右侧用c2字符串补足致长度i,可多次重复，如果i小于c1的长度，那么只返回i那么长的c1字符，其他的将被截去。c2的缺省值为单空格,其他与LPAD相似
　　RTRIM(,)
　　把c1中最右边的字符去掉，使其第后一个字符不在c2中，如果没有c2，那么c1就不会改变。
　　REPLACE(,[,])
　　c1,c2,c3都是字符串，函数用c3代替出现在c1中的c2后返回。

select REPLACE('uptown','up','down') from dual REPLACE downtown

　　STBSTR(,＜i＞[,])
　　c1为一字符串，i,j为整数，从c1的第i位开始返回长度为j的子字符串，如果j为空，则直到串的尾部。

select SUBSTR('Message',1,4) from dual SUBS Mess

　　SUBSTRB(,＜i＞[,])
　　与SUBSTR大致相同，只是I,J是以字节计算。
　　SOUNDEX()
　　返回与c1发音相似的词

select SOUNDEX('dawes') Dawes SOUNDEX('daws') Daws, SOUNDEX('dawson') from dual Dawes Daws Dawson D200 D200 D250

　　TRANSLATE(,,)
　　将c1中与c2相同的字符以c3代替

select TRANSLATE('fumble','uf','ar') test from dual TEXT ramble

　　TRIM([[]] from c3)
　　将c3串中的第一个，最后一个，或者都删除。

select TRIM(' space padded ') trim from dual TRIM space padded

　　UPPER()
　　返回c1的大写，常出现where子串中

select name from dual where UPPER(name) LIKE 'KI%' NAME KING

单行数字函数
　　单行数字函数操作数字数据，执行数学和算术运算。所有函数都有数字参数并返回数字值。所有三角函数的操作数和值都是弧度而不是角度，oracle没有提供内建的弧度和角度的转换函数。
　　ABS()
　　返回n的绝对值
　　ACOS()
　　反余玄函数，返回-1到1之间的数。n表示弧度

select ACOS(-1) pi,ACOS(1) ZERO FROM dual PI ZERO 3.14159265 0

　　ASIN()
　　反正玄函数，返回-1到1，n表示弧度
　　ATAN()
　　反正切函数，返回n的反正切值，n表示弧度。
　　CEIL()
　　返回大于或等于n的最小整数。
　　COS()
　　返回n的余玄值，n为弧度
　　COSH()
　　返回n的双曲余玄值，n 为数字。

select COSH(<1.4>) FROM dual COSH(1.4) 2.15089847

　　EXP()
　　返回e的n次幂，e=2.71828183.
　　FLOOR()
　　返回小于等于N的最大整数。
　　LN()
　　返回N的自然对数，N必须大于0

　　LOG(,)
　　返回以n1为底n2的对数
　　MOD()
　　返回n1除以n2的余数，
　　POWER(,)
　　返回n1的n2次方
　　ROUND(,)
　　返回舍入小数点右边n2位的n1的值，n2的缺省值为0，这回将小数点最接近的整数，如果n2为负数就舍入到小数点左边相应的位上，n2必须是整数。

select ROUND(12345,-2),ROUND(12345.54321,2) FROM dual ROUND(12345,-2) ROUND(12345.54321,2) 12300 12345.54

　　SIGN()
　　如果n为负数，返回-1,如果n为正数，返回1，如果n=0返回0.
　　SIN（)
　　返回n的正玄值,n为弧度。
　　SINH()
　　返回n的双曲正玄值,n为弧度。

SQRT()
　　返回n的平方根,n为弧度
　　TAN（)
　　返回n的正切值,n为弧度
　　TANH()
　　返回n的双曲正切值,n为弧度
　　TRUNC(,)
　　返回截尾到n2位小数的n1的值，n2缺省设置为0，当n2为缺省设置时会将n1截尾为整数，如果n2为负值，就截尾在小数点左边相应的位上。
　　单行日期函数
　　单行日期函数操作DATA数据类型，绝大多数都有DATA数据类型的参数，绝大多数返回的也是DATA数据类型的值。
　　ADD_MONTHS(,＜i＞)
　　返回日期d加上i个月后的结果。i可以使任意整数。如果i是一个小数，那么数据库将隐式的他转换成整数，将会截去小数点后面的部分。
　　LAST_DAY()
　　函数返回包含日期d的月份的最后一天
　　MONTHS_BETWEEN(,)
　　返回d1和d2之间月的数目,如果d1和d2的日的日期都相同，或者都使该月的最后一天，那么将返回一个整数，否则会返回的结果将包含一个分数。
　　NEW_TIME(,,)
　　d1是一个日期数据类型，当时区tz1中的日期和时间是d时，返回时区tz2中的日期和时间。tz1和tz2时字符串。
　　NEXT_DAY(,)
　　返回日期d后由dow给出的条件的第一天，dow使用当前会话中给出的语言指定了一周中的某一天，返回的时间分量与d的时间分量相同。

select NEXT_DAY('01-Jan-2000','Monday') "1st Monday",NEXT_DAY('01-Nov-2004','Tuesday')+7 "2nd Tuesday") from dual; 1st Monday 2nd Tuesday 03-Jan-2000 09-Nov-2004

　　ROUND([,])
　　将日期d按照fmt指定的格式舍入，fmt为字符串。
　　SYADATE
　　函数没有参数，返回当前日期和时间。
　　TRUNC([,])
　　返回由fmt指定的单位的日期d.

　单行转换函数
　　单行转换函数用于操作多数据类型，在数据类型之间进行转换。
　　CHARTORWID()
　　c 使一个字符串，函数将c转换为RWID数据类型。

SELECT test_id from test_case where rowid=CHARTORWID('AAAA0SAACAAAALiAAA')

　　CONVERT(,[,])
　　c尾字符串，dset、sset是两个字符集，函数将字符串c由sset字符集转换为dset字符集，sset的缺省设置为数据库的字符集。
　　HEXTORAW()
　　x为16进制的字符串，函数将16进制的x转换为RAW数据类型。
　　RAWTOHEX()
　　x是RAW数据类型字符串，函数将RAW数据类转换为16进制的数据类型。
　　ROWIDTOCHAR()
　　函数将ROWID数据类型转换为CHAR数据类型。
　　TO_CHAR([[,)
　　x是一个data或number数据类型，函数将x转换成fmt指定格式的char数据类型，如果x为日期nlsparm= NLS_DATE_LANGUAGE 控制返回的月份和日份所使用的语言。如果x为数字nlsparm=NLS_NUMERIC_CHARACTERS 用来指定小数位和千分位的分隔符，以及货币符号。

NLS_NUMERIC_CHARACTERS ="dg", NLS_CURRENCY="string"

TO_DATE([,[,）
　　c表示字符串，fmt表示一种特殊格式的字符串。返回按照fmt格式显示的c,nlsparm表示使用的语言。函数将字符串c转换成date数据类型。
　　TO_MULTI_BYTE()
　　c表示一个字符串，函数将c的担子截字符转换成多字节字符。
　　TO_NUMBER([,[,)
　　c表示字符串，fmt表示一个特殊格式的字符串，函数返回值按照fmt指定的格式显示。nlsparm表示语言，函数将返回c代表的数字。
　　TO_SINGLE_BYTE()
　　将字符串c中得多字节字符转化成等价的单字节字符。该函数仅当数据库字符集同时包含单字节和多字节字符时才使用
　　其它单行函数
　　BFILENAME( ,)
　　dir是一个directory类型的对象，file为一文件名。函数返回一个空的BFILE位置值指示符，函数用于初始化BFILE变量或者是BFILE列。
　　DECODE(,,[,,,[])
　　x是一个表达式，m1是一个匹配表达式，x与m1比较，如果m1等于x，那么返回r1,否则,x与m2比较，依次类推m3,m4,m5....直到有返回结果。
　　DUMP(,[,[,[,]]])
　　x是一个表达式或字符，fmt表示8进制、10进制、16进制、或则单字符。函数返回包含了有关x的内部表示信息的VARCHAR2类型的值。如果指定了n1,n2那么从n1开始的长度为n2的字节将被返回。
　　EMPTY_BLOB()
　　该函数没有参数，函数返回 一个空的BLOB位置指示符。函数用于初始化一个BLOB变量或BLOB列。
　　EMPTY_CLOB()
　　该函数没有参数，函数返回 一个空的CLOB位置指示符。函数用于初始化一个CLOB变量或CLOB列。
　　GREATEST()
　　exp_list是一列表达式，返回其中最大的表达式，每个表达式都被隐含的转换第一个表达式的数据类型，如果第一个表达式是字符串数据类型中的任何一个，那么返回的结果是varchar2数据类型， 同时使用的比较是非填充空格类型的比较。
　　LEAST()
　　exp_list是一列表达式，返回其中最小的表达式，每个表达式都被隐含的转换第一个表达式的数据类型，如果第一个表达式是字符串数据类型中的任何一个，将返回的结果是varchar2数据类型， 同时使用的比较是非填充空格类型的比较。
　　UID
　　该函数没有参数，返回唯一标示当前数据库用户的整数。
　　USER
　　返回当前用户的用户名
　　USERENV()
　　基于opt返回包含当前会话信息。opt的可选值为：
　　ISDBA　 　　会话中SYSDBA脚色响应，返回TRUE
　　SESSIONID 　返回审计会话标示符
　　ENTRYID 　　返回可用的审计项标示符
　　INSTANCE　　在会话连接后，返回实例标示符。该值只用于运行Parallel 服务器并且有 多个实例的情况下使用。
　　LANGUAGE　　返回语言、地域、数据库设置的字符集。
　　LANG　　　　返回语言名称的ISO缩写。
　　TERMINAL　　为当前会话使用的终端或计算机返回操作系统的标示符。

　　VSIZE()
　　x是一个表达式。返回x内部表示的字节数。

　　ASIN()
　　反正玄函数，返回-1到1，n表示弧度
　　ATAN()
　　反正切函数，返回n的反正切值，n表示弧度。
　　CEIL()
　　返回大于或等于n的最小整数。
　　COS()
　　返回n的余玄值，n为弧度
　　COSH()
　　返回n的双曲余玄值，n 为数字。

select COSH(<1.4>) FROM dual COSH(1.4) 2.15089847

　　EXP()
　　返回e的n次幂，e=2.71828183.
　　FLOOR()
　　返回小于等于N的最大整数。
　　LN()
　　返回N的自然对数，N必须大于0

　　LOG(,)
　　返回以n1为底n2的对数
　　MOD()
　　返回n1除以n2的余数，
　　POWER(,)
　　返回n1的n2次方
　　ROUND(,)
　　返回舍入小数点右边n2位的n1的值，n2的缺省值为0，这回将小数点最接近的整数，如果n2为负数就舍入到小数点左边相应的位上，n2必须是整数。

select ROUND(12345,-2),ROUND(12345.54321,2) FROM dual ROUND(12345,-2) ROUND(12345.54321,2) 12300 12345.54

　　SIGN()
　　如果n为负数，返回-1,如果n为正数，返回1，如果n=0返回0.
　　SIN（)
　　返回n的正玄值,n为弧度。
　　SINH()
　　返回n的双曲正玄值,n为弧度。

SQRT()
　　返回n的平方根,n为弧度
　　TAN（)
　　返回n的正切值,n为弧度
　　TANH()
　　返回n的双曲正切值,n为弧度
　　TRUNC(,)
　　返回截尾到n2位小数的n1的值，n2缺省设置为0，当n2为缺省设置时会将n1截尾为整数，如果n2为负值，就截尾在小数点左边相应的位上。
　　单行日期函数
　　单行日期函数操作DATA数据类型，绝大多数都有DATA数据类型的参数，绝大多数返回的也是DATA数据类型的值。
　　ADD_MONTHS(,＜i＞)
　　返回日期d加上i个月后的结果。i可以使任意整数。如果i是一个小数，那么数据库将隐式的他转换成整数，将会截去小数点后面的部分。
　　LAST_DAY()
　　函数返回包含日期d的月份的最后一天
　　MONTHS_BETWEEN(,)
　　返回d1和d2之间月的数目,如果d1和d2的日的日期都相同，或者都使该月的最后一天，那么将返回一个整数，否则会返回的结果将包含一个分数。
　　NEW_TIME(,,)
　　d1是一个日期数据类型，当时区tz1中的日期和时间是d时，返回时区tz2中的日期和时间。tz1和tz2时字符串。
　　NEXT_DAY(,)
　　返回日期d后由dow给出的条件的第一天，dow使用当前会话中给出的语言指定了一周中的某一天，返回的时间分量与d的时间分量相同。

select NEXT_DAY('01-Jan-2000','Monday') "1st Monday",NEXT_DAY('01-Nov-2004','Tuesday')+7 "2nd Tuesday") from dual; 1st Monday 2nd Tuesday 03-Jan-2000 09-Nov-2004

　　ROUND([,])
　　将日期d按照fmt指定的格式舍入，fmt为字符串。
　　SYADATE
　　函数没有参数，返回当前日期和时间。
　　TRUNC([,])
　　返回由fmt指定的单位的日期d.

　单行转换函数
　　单行转换函数用于操作多数据类型，在数据类型之间进行转换。
　　CHARTORWID()
　　c 使一个字符串，函数将c转换为RWID数据类型。

SELECT test_id from test_case where rowid=CHARTORWID('AAAA0SAACAAAALiAAA')

　　CONVERT(,[,])
　　c尾字符串，dset、sset是两个字符集，函数将字符串c由sset字符集转换为dset字符集，sset的缺省设置为数据库的字符集。
　　HEXTORAW()
　　x为16进制的字符串，函数将16进制的x转换为RAW数据类型。
　　RAWTOHEX()
　　x是RAW数据类型字符串，函数将RAW数据类转换为16进制的数据类型。
　　ROWIDTOCHAR()
　　函数将ROWID数据类型转换为CHAR数据类型。
　　TO_CHAR([[,)
　　x是一个data或number数据类型，函数将x转换成fmt指定格式的char数据类型，如果x为日期nlsparm= NLS_DATE_LANGUAGE 控制返回的月份和日份所使用的语言。如果x为数字nlsparm=NLS_NUMERIC_CHARACTERS 用来指定小数位和千分位的分隔符，以及货币符号。

NLS_NUMERIC_CHARACTERS ="dg", NLS_CURRENCY="string"

TO_DATE([,[,）
　　c表示字符串，fmt表示一种特殊格式的字符串。返回按照fmt格式显示的c,nlsparm表示使用的语言。函数将字符串c转换成date数据类型。
　　TO_MULTI_BYTE()
　　c表示一个字符串，函数将c的担子截字符转换成多字节字符。
　　TO_NUMBER([,[,)
　　c表示字符串，fmt表示一个特殊格式的字符串，函数返回值按照fmt指定的格式显示。nlsparm表示语言，函数将返回c代表的数字。
　　TO_SINGLE_BYTE()
　　将字符串c中得多字节字符转化成等价的单字节字符。该函数仅当数据库字符集同时包含单字节和多字节字符时才使用
　　其它单行函数
　　BFILENAME( ,)
　　dir是一个directory类型的对象，file为一文件名。函数返回一个空的BFILE位置值指示符，函数用于初始化BFILE变量或者是BFILE列。
　　DECODE(,,[,,,[])
　　x是一个表达式，m1是一个匹配表达式，x与m1比较，如果m1等于x，那么返回r1,否则,x与m2比较，依次类推m3,m4,m5....直到有返回结果。
　　DUMP(,[,[,[,]]])
　　x是一个表达式或字符，fmt表示8进制、10进制、16进制、或则单字符。函数返回包含了有关x的内部表示信息的VARCHAR2类型的值。如果指定了n1,n2那么从n1开始的长度为n2的字节将被返回。
　　EMPTY_BLOB()
　　该函数没有参数，函数返回 一个空的BLOB位置指示符。函数用于初始化一个BLOB变量或BLOB列。
　　EMPTY_CLOB()
　　该函数没有参数，函数返回 一个空的CLOB位置指示符。函数用于初始化一个CLOB变量或CLOB列。
　　GREATEST()
　　exp_list是一列表达式，返回其中最大的表达式，每个表达式都被隐含的转换第一个表达式的数据类型，如果第一个表达式是字符串数据类型中的任何一个，那么返回的结果是varchar2数据类型， 同时使用的比较是非填充空格类型的比较。
　　LEAST()
　　exp_list是一列表达式，返回其中最小的表达式，每个表达式都被隐含的转换第一个表达式的数据类型，如果第一个表达式是字符串数据类型中的任何一个，将返回的结果是varchar2数据类型， 同时使用的比较是非填充空格类型的比较。
　　UID
　　该函数没有参数，返回唯一标示当前数据库用户的整数。
　　USER
　　返回当前用户的用户名
　　USERENV()
　　基于opt返回包含当前会话信息。opt的可选值为：
　　ISDBA　 　　会话中SYSDBA脚色响应，返回TRUE
　　SESSIONID 　返回审计会话标示符
　　ENTRYID 　　返回可用的审计项标示符
　　INSTANCE　　在会话连接后，返回实例标示符。该值只用于运行Parallel 服务器并且有 多个实例的情况下使用。
　　LANGUAGE　　返回语言、地域、数据库设置的字符集。
　　LANG　　　　返回语言名称的ISO缩写。
　　TERMINAL　　为当前会话使用的终端或计算机返回操作系统的标示符。

　　VSIZE()
　　x是一个表达式。返回x内部表示的字节数。

Oracle数据库数据对象分析

　　Oracle数据库数据对象中最基本的是表和视图，其他还有约束、序列、函数、存储过程、包、触发器等。对数据库的操作可以基本归结为对数据对象的操作,理解和掌握Oracle数据库对象是学习Oracle的捷径。
　　表和视图
　　Oracle中表是数据存储的基本结构。ORACLE8引入了分区表和对象表，ORACLE8i引入了临时表，使表的功能更强大。视图是一个或多个表中数据的逻辑表达式。本文我们将讨论怎样创建和管理简单的表和视图。
　　管理表
　　表可以看作有行和列的电子数据表，表是关系数据库中一种拥有数据的结构。用CREATE TABLE语句建立表，在建立表的同时，必须定义表名，列，以及列的数据类型和大小。例如：

CREATE TABLE products 　　( PROD_ID NUMBER(4), 　　　PROD_NAME VAECHAR2(20), 　　　STOCK_QTY NUMBER(5,3) 　　);

　　这样我们就建立了一个名为products的表， 关键词CREATE TABLE后紧跟的表名，然后定义了三列，同时规定了列的数据类型和大小。
　　在创建表的同时你可以规定表的完整性约束，也可以规定列的完整性约束，在列上普通的约束是NOT NULL,关于约束的讨论我们在以后进行。
　　在建立或更改表时，可以给表一个缺省值。缺省值是在增加行时，增加的数据行中某一项值为null时，oracle即认为该值为缺省值。
　　下列数据字典视图提供表和表的列的信息

　　建立视图
　　CREATE VIEW命令创建视图，定义视图的查询可以建立在一个或多个表，或其他视图上。查询不能有FOR UPDATE子串，在早期的ORACLE8i版本中不支持ORDER BY子串，现在的版本中CREATE VIEW可以拥有ORDER BY子串。
　　例：

SQL> CREATE VIEW TOP_EMP AS SELECT empno EMPLOYEE_ID,me EMPLOYEE_NAME,salary FROM emp WHERE salary >2000

　　用户可以在创建视图的同时更改列名，方法是在视图名后立即加上要命名的列名。重新定义视图需要包含OR REPLACE子串。

SQL> CREATE VIEW TOP_EMP （EMPLOYEE_ID，EMPLOYEE_NAME，SALARY) AS SELECT empno ,me ,salary FROM emp WHERE salary >2000

　　如果在创建的视图包含错误在正常情况下，视图将不会被创建。但如果你需要创建一个带错误的视图必须在CREATE VIEW语句中带上FORCE选项。如：

CREATE FORCE VIEW ORDER_STATUS AS SELECT * FROM PURCHASE_ORDERS WHERE STATUS='APPPOVE'; SQL>/ warning :View create with compilation errors

　　这样将创建了一个名为ORDER_STATUS的视图，但这样的视图的状态是不合法的，如果以后状态发生变化则可以重新编译，其状态也变成合法的。
　　从视图中获得数据
　　从视图中获得数据与从表中获得数据基本一样，用户可以在连接和子查询中使用视图，也可以使用SQL函数，以及所有SELECT语句的字串。
　　插入、更新、删除数据
　　用户在一定的限制条件下可以通过视图更新、插入、删除数据。如果视图连接多个表，那么在一个时间里只能更新一个表。所有的能被更新的列可以在数据字典USER_UPDATETABLE_COLUMNS中查到。
　　用户在CREATE VIEW中可以使用了WITH子串。WITH READ ONLY子串表示创建的视图是一个只读视图，不能进行更新、插入、删除操作。WITH CHECK OPTION表示可以进行插入和更新操作，但应该满足WHERE子串的条件。这个条件就是创建视图WHERE子句的条件，比如在上面的例子中用户创建了一个视图TOP_EMP,在这个视图中用户不能插入salary小于2000的数据行。
　　删除视图
　　删除视图使用DROP VIEW命令。同时将视图定义从数据字典中删除，基于视图的权限也同时被删除，其他涉及到该视图的函数、视图、程序等都将被视为非法。
　　例：

DROP VIEW TOP_EMP;

完整性约束
　　完整性约束用于增强数据的完整性，Oracle提供了5种完整性约束：
　　　 Check
　　　 NOT NULL
　　　 Unique
　　　 Primary
　　　 Foreign key
　　完整性约束是一种规则，不占用任何数据库空间。完整性约束存在数据字典中，在执行SQL或PL/SQL期间使用。用户可以指明约束是启用的还是禁用的，当约束启用时，他增强了数据的完整性，否则，则反之，但约束始终存在于数据字典中。
　　禁用约束，使用ALTER语句

ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;

　　或

ALTER TABLE policies DISABLE CONSTRAINT chk_gender

　　如果要重新启用约束：

ALTER TABLE policies ENABLE CONSTRAINT chk_gender

　　删除约束

ALTER TABLE table_name DROP CONSTRAINT constraint_name

　　或

ALTER TABLE policies DROP CONSTRAINT chk_gender;

　　Check 约束
　　在数据列上Check 约束需要一个特殊的布尔条件或者将数据列设置成TRUE，至少一个数据列的值是NULL，Check约束用于增强表中数据内容的简单的商业规则。用户使用 Check约束保证数据规则的一致性。Check约束可以涉及该行同属Check约束的其他数据列但不能涉及其他行或其他表，或调用函数SYSDATE, UID,USER,USERENV。如果用户的商业规则需要这类的数据检查，那么可以使用触发器。Check约束不保护LOB数据类型的数据列和对象、嵌套表、VARRY、ref等。单一数据列可以有多个Check约束保护，一个Check约束可以保护多个数据列。
　　创建表的Check约束使用CREATE TABLE语句，更改表的约束使用ALTER TABLE语句。
　　语法：

CONSTRAINT [constraint_name] CHECK (condition);

　　Check约束可以被创建或增加为一个表约束，当Check约束保护多个数据列时，必须使用表约束语法。约束名是可选的并且如果这个名字不存在，那么oracle将产生一个以SYS_开始的唯一的名字。
　　例：

CREATE TABLE policies (policy_id NUMBER, holder_name VARCHAR2(40), gender VARCHAR2(1) constraint chk_gender CHECK (gender in ('M','F'), marital_status VARCHAR2(1), date_of_birth DATE, constraint chk_marital CHECK (marital_status in('S','M','D','W')) );

　　NOT NULL约束
　　NOT NULL约束应用在单一的数据列上，并且他保护的数据列必须要有数据值。缺省状况下，ORACLE允许任何列都可以有NULL值。某些商业规则要求某数据列必须要有值，NOT NULL约束将确保该列的所有数据行都有值。

　　例：

CREATE TABLE policies (policy_id NUMBER, holder_name VARCHAR2(40) NOT NULL, gender VARCHAR2(1), marital_status VARCHAR2(1), date_of_birth DATE NOT NULL );

　　对于NOT NULL的ALTER TABLE语句与其他约束稍微有点不同。

ALTER TABLE policies MODIFY holder_name NOT NULL

唯一性约束(Unique constraint)
　　唯一性约束可以保护表中多个数据列，保证在保护的数据列中任何两行的数据都不相同。唯一性约束与表一起创建，在唯一性约束创建后，可以使用ALTER TABLE语句修改。
　　语法：

column_name data_type CONSTRAINT constraint_name UNIQUE

　　如果唯一性约束保护多个数据列，那么唯一性约束要作为表约束增加。语法如下：

CONSTRAINT constraint_name (column) UNIQUE USING INDEX TABLESPACE (tablespace_name) STORAGE (stored clause)

　　唯一性约束由一个B-tree索引增强，所以可以在USING子串中为索引使用特殊特征，比如表空间或存储参数。CREATE TABLE语句在创建唯一性约束的同时也给目标数据列建立了一个唯一的索引。

CREATE TABLE insured_autos (policy_id NUMBER CONSTRAINT pk_policies PRIMARY KEY, vin VARCHAR2(10), coverage_begin DATE, coverage_term NUMBER, CONSTRAIN unique_auto UNIQUE (policy_id,vin) USING INDEX TABLESPACE index STORAGE (INITIAL 1M NEXT 10M PCTINCREASE 0) );

　　用户可以禁用未以性约束，但他仍然存在，禁用唯一性约束使用ALTER TABLE 语句

ALTER TABLE insured_autos DISABLE CONSTRAIN unique_name;

　　删除唯一性约束，使用ALTER TABLE....DROP CONSTRAIN语句

ALTER TABLE insured_autos DROP CONSTRAIN unique_name;

　　注意用户不能删除在有外部键指向的表的唯一性约束。这种情况下用户必须首先禁用或删除外部键（foreign key)。
　　删除或禁用唯一性约束通常同时删除相关联的唯一索引，因而降低了数据库性能。经常删除或禁用唯一性约束有可能导致丢失索引带来的性能错误。要避免这样错误，可以采取下面的步骤：
　　1、在唯一性约束保护的数据列上创建非唯一性索引。
　　2、添加唯一性约束

主键(Primary Key)约束
　　表有唯一的主键约束。表的主键可以保护一个或多个列，主键约束可与NOT NULL约束共同作用于每一数据列。NOT NULL约束和唯一性约束的组合将保证主键唯一地标识每一行。像唯一性约束一样，主键由B-tree索引增强。
　　创建主键约束使用CREATE TABLE语句与表一起创建，如果表已经创建了，可以使用ALTER TABLE语句。

CREATE TABLE policies (policy_id NUMBER CONSTRAINT pk_policies PRIMARY KEY, holder_name VARCHAR2(40), gender VARCHAR2(1), marital_status VARCHAR2(1), date_of_birth DATE );

　　与唯一性约束一样，如果主键约束保护多个数据列，那么必须作为一个表约束创建。

CREATE TABLE insured_autos (policy_id NUMBER, vin VARCHAR2(40), coverage_begin DATE, coverage_term NUMBER, CONSTRAINT pk_insured_autos PRIMARY KEY (policy_id,vin) USING INDEX TABLESPACE index STORAGE (INITIAL 1M NEXT 10M PCTINCREASE 0) );

　　禁用或删除主键必须与ALTER TABLE 语句一起使用

ALTER TABLE policies DROP PRIMARY KEY;

　　或

ALTER TABLE policies DISABLE PRIMARY KEY;

　　外部键约束（Foreign key constraint)
　　外部键约束保护一个或多个数据列，保证每个数据行的数据包含一个或多个null值，或者在保护的数据列上同时拥有主键约束或唯一性约束。引用（主键或唯一性约束）约束可以保护同一个表，也可以保护不同的表。与主键和唯一性约束不同外部键不会隐式建立一个B-tree索引。在处理外部键时，我们常常使用术语父表（parent table)和子表（child table),父表表示被引用主键或唯一性约束的表，子表表示引用主键和唯一性约束的表。
　　创建外部键使用CREATE TABLE语句，如果表已经建立了，那么使用ALTER TABLE语句。

CREATE TABLE insured_autos (policy_id NUMBER CONSTRAINT policy_fk REFERENCE policies(policy_id ON DELETE CASCADE, vin VARCHAR2(40), coverage_begin DATE, coverage_term NUMBER, make VARCHAR2(30), model VARCHAR(30), year NUMBER, CONSTRAIN auto_fk FROEIGN KEY (make,model,year) REFERENCES automobiles (make,model,year) ON DELETE SET NULL );

　　ON DELETE子串告诉ORACLE如果父纪录（parent record)被删除后，子记录做什么。缺省情况下禁止在子记录还存在的情况下删除父纪录。
　　外部键和NULL值
　　在外部键约束保护的数据列中NULL值的处理可能产生不可预料的结果。ORACLE 使用ISO standar Match None规则增强外部键约束。这个规则规定如果任何外部键作用的数据列包含有一个NULL值，那么任何保留该键的数据列在父表中没有匹配值。
　　比如，在父表AUTOMOBILES中，主键作用于数据列MAKE，MODEL，YEAR上，用户使用的表INSURED_AUTOS有一个外部约束指向AOTOMOBILES，注意在INSURES_AUTOS中有一数据行的MODEL列为NULL值，这一行数据已经通过约束检查，即使MAKE列也没有显示在父表AUTOMOBILES中，如下表：

　　表1 AUTOMOBILES

MAKE	MODEL	YEAR
Ford	Taurus	2000
Toyota	Camry	1999

　　表2 INSURED_AUTOS

POLICY_ID	MAKE	MODEL	YEAR
576	Ford	Taurus	2000
577	Toyota	Camry	1999
578	Tucker	NULL	1949

　　延迟约束检验（Deferred Constraint Checking）
　　约束检验分两种情况，一种是在每一条语句结束后检验数据是否满足约束条件，这种检验称为立即约束检验（immediately checking）,另一种是在事务处理完成之后对数据进行检验称之为延迟约束检验。在缺省情况下Oracle约束检验是立即检验（immediately checking),如果不满足约束将先是一条错误信息，但用户可以通过SET CONSTRAINT语句选择延迟约束检验。语法如下：

SET CONSTRAINT constraint_name|ALL DEFEERRED|IMMEDIATE --;

序列（Sequences）
　　Oracle序列是一个连续的数字生成器。序列常用于人为的关键字，或给数据行排序否则数据行是无序的。像约束一样，序列只存在于数据字典中。序列号可以被设置为上升、下降，可以没有限制或重复使用直到一个限制值。创建序列使用SET SEQUENCE语句。

CREATE SEQUENCE [schema] sequence KEYWORD

　　KEYWORD包括下面的值：

KEYWORD	描述
START WITH	定义序列生成的第一个数字，缺省为1
INCREMENT BY	定义序列号是上升还是下降，对于一个降序的序列INCREMENT BY为负值
MINVALUE	定义序列可以生成的最小值，这是降序序列中的限制值。缺省情况下该值为NOMINVALUE,NOMINVALUE，对于升序为1，对于降序为-10E26.
MAXVALUE	序列能生成的最大数字。这是升序序列中的限制值，缺省的MAXVALUE为NOMAXVALUE,NOMAXVALUE，对于升序为10E26，对于降序为-1。
CYCLE	设置序列值在达到限制值以后可以重复
NOCYCLE	设置序列值在达到限制值以后不能重复，这是缺省设置。当试图产生MAXVALUE+1的值时，将会产生一个异常
CACHE	定义序列值占据的内存块的大小，缺省值为20
NOCACHE	在每次序列号产生时强制数据字典更新，保证在序列值之间没有间隔当创建序列时，START WITH值必须等于或大于MINVALUE。

　　删除序列使用DROP SEQUENCE语句

DROP SEQUENCE sequence_name

　　索引(INDEXES)
　　索引是一种可以提高查询性能的数据结构，在这一部分我们将讨论索引如何提高查询性能的。ORACLE提供了以下几种索引：

　　 B-Tree、哈希（hash)、位图（bitmap)等索引类型
　　 基于原始表的索引
　　 基于函数的索引
　　 域（Domain）索引

　　实际应用中主要是B-Tree索引和位图索引，所以我们将集中讨论这两种索引类型。
　　B-Tree索引
　　B-Tree索引是最普通的索引，缺省条件下建立的索引就是这种类型的索引。B-Tree索引可以是唯一或非唯一的，可以是单一的（基于一列）或连接的（多列）。B-Tree索引在检索高基数数据列（高基数数据列是指该列有很多不同的值）时提供了最好的性能。对于取出较小的数据B-Tree索引比全表检索提供了更有效的方法。但当检查的范围超过表的10%时就不能提高取回数据的性能。正如名字所暗示的那样，B-Tree索引是基于二元树的，由枝干块（branch block)和树叶块（leaf block)组成,枝干块包含了索引列（关键字）和另一索引的地址。树叶块包含了关键字和给表中每个匹配行的ROWID。
　　位图索引
　　位图索引主要用于决策支持系统或静态数据，不支持行级锁定。位图索引可以是简单的（单列）也可以是连接的（多列），但在实践中绝大多数是简单的。位图索引最好用于低到中群集（cardinality)列，在这些列上多位图索引可以与AND或OR操作符结合使用。位图索引使用位图作为键值，对于表中的每一数据行位图包含了TRUE（1）、FALSE（0）、或NULL值。位图索引的位图存放在B-Tree结构的页节点中。B-Tree结构使查找位图非常方便和快速。另外，位图以一种压缩格式存放，因此占用的磁盘空间比B-Tree索引要小得多。
　　同义词（Synonyms）
　　对另一个数据对象而言同义词是一个别名。public同义词是针对所有用户的，相对而言private同义词则只针对对象拥有者或被授予权限的账户。在本地数据库中同义词可以表示表、视图、序列、程序、函数或包等数据对象，也可以通过链接表示另一个数据库的对象。
　　创建同义词语法如下：

CREATE [PUBLIC] SYNONYM synonym_name FOR [schema.] object[@db_link];

　　
　　例：

CREATE PUBLIC SYNONYM policies FOR poladm.policies@prod; CREATE SYNONYM plan_table FOR system.plan_table;

过程和函数
　　过程和函数都以编译后的形式存放在数据库中，函数可以没有参数也可以有多个参数并有一个返回值。过程有零个或多个参数，没有返回值。函数和过程都可以通过参数列表接收或返回零个或多个值，函数和过程的主要区别不在于返回值，而在于他们的调用方式。过程是作为一个独立执行语句调用的：
pay_involume(invoice_nbr,30,due_date);
　　函数以合法的表达式的方式调用：
order_volumn:=open_orders(SYSDATE,30);
　　创建过程的语法如下：

CREATE [ OR REPLACE] PROCEDURE [schema.]procedure_name [parameter_lister] {AS|IS} declaration_section BEGIN executable_section [EXCEPTION exception_section] END [procedure_name]

　　每个参数的语法如下：
paramter_name mode datatype [(:=|DEFAULT) value]
　　mode有三种形式：IN、OUT、INOUT。
　　IN表示在调用过程的时候，实际参数的取值被传递给该过程，形式参数被认为是只读的，当过程结束时，控制会返回控制环境，实际参数的值不会改变。
　　OUT在调用过程时实际参数的取值都将被忽略，在过程内部形式参数只能是被赋值，而不能从中读取数据，在过程结束后形式参数的内容将被赋予实际参数。
　　INOUT这种模式是IN和OUT的组合；在过程内部实际参数的值会传递给形式参数，形势参数的值可读也可写，过程结束后，形势参数的值将赋予实际参数。
　　创建函数的语法和过程的语法基本相同，唯一的区别在于函数有RETUREN子句

CREATE [ OR REPLACE] FINCTION [schema.]function_name [parameter_list] RETURN returning_datatype {AS|IS} declaration_section BEGIN executable_section [EXCEPTION] exception_section END [procedure_name]

　　在执行部分函数必须有哟个或多个return语句。
　　在创建函数中可以调用单行函数和组函数，例如：

CREATE OR REPLACE FUNCTION my_sin(DegreesIn IN NUMBER) RETURN NUMBER IS pi NUMBER=ACOS(-1); RadiansPerDegree NUMBER; BEGIN RadiansPerDegree=pi/180; RETURN(SIN(DegreesIn*RadiansPerDegree)); END

包
　　包是一种将过程、函数和数据结构捆绑在一起的容器；包由两个部分组成：外部可视包规范，包括函数头，过程头，和外部可视数据结构；另一部分是包主体(package body),包主体包含了所有被捆绑的过程和函数的声明、执行、异常处理部分。
　　打包的PL/SQL程序和没有打包的有很大的差异，包数据在用户的整个会话期间都一直存在，当用户获得包的执行授权时，就等于获得包规范中的所有程序和数据结构的权限。但不能只对包中的某一个函数或过程进行授权。包可以重载过程和函数，在包内可以用同一个名字声明多个程序，在运行时根据参数的数目和数据类型调用正确的程序。
　　创建包必须首先创建包规范，创建包规范的语法如下：

CREATE [OR REPLACE] PACKAGE package_name {AS|IS} public_variable_declarations | public_type_declarations | public_exception_declarations | public_cursor_declarations | function_declarations | procedure_specifications END [package_name]

　　创建包主体使用CREATE PACKAGE BODY语句：

CREATE [OR REPLACE] PACKAGE BODY package_name {AS|IS} private_variable_declarations | private_type_declarations | private_exception_declarations | private_cursor_declarations | function_declarations | procedure_specifications END [package_name]

　　私有数据结构是那些在包主体内部，对被调用程序而言是不可见的。
　　触发器(Triggers)
　　触发器是一种自动执行响应数据库变化的程序。可以设置为在触发器事件之前或之后触发或执行。能够触发触发器事件的事件包括下面几种：

　　DML事件
　　DDL事件
　　数据库事件

　　DML事件触发器可以是语句或行级触发器。DML语句触发器在触发语句之前或之后触发DML行级触发器在语句影响的行变化之前或之后触发。用户可以给单一事件和类型定义多个触发器，但没有任何方法可以增强多触发器触发的命令。下表列出了用户可以利用的触发器事件：

事件	触发器描述
INSERT	当向表或视图插入一行时触发触发器
UPDATE	更新表或视图中的某一行时触发触发器
DELETE	从表或视图中删除某一行时触发触发器
CREATE	当使用CREATE语句为数据库或项目增加一个对象时触发触发器
ALTER	当使用ALTER语句为更改一个数据库或项目的对象时触发触发器
DROP	当使用DROP语句删除一个数据库或项目的对象时触发触发器
START	打开数据库时触发触发器，在事件后触发
SHUTDOWN	关闭数据库时触发，事件前触发
LOGON	当一个会话建立时触发，事件前触发
LOGOFF	当关闭会话时触发，事件前触发
SERVER	服务器错误发生时触发触发器，事件后触发

　　创建触发器的语法如下：

CREATE [OR REPLACE] TRIGGER trigger_name {before|after|instead of} event ON {table_or_view_name|DATABASE} [FOR EACH ROW[WHEN condition]] trigger_body

　　只有DML触发器（INSERT、UPDATE、DELETE)语句可以使用INSTEAD OF触发器并且只有表的DML触发器可以是BEFORE或AFTER触发器。
　　象约束一样触发器可以被设置为禁用或启用来关闭或打开他们的执行体(EXECUTE),将触发器设置为禁用或启用使用ALTER TRIGGER语句：

ALTER TRIGGER trigger_name ENABLE; ALTER TRIGGER trigger_name DISABLE;

　　要禁用或启用表的所有触发器，使用ALTER TABLE语句

ALTER TRIGGER table_name DISABLE ALL TRIGGER; ALTER TRIGGER table_name ENABLE ALL TRIGGER;

　　删除触发器使用DROP TRIGGER

DROP TRIGGER trigger_name;

　　数据字典

　　Oracle数据字典包含了用户数据库的元数据。带下划线的表名称中带OBJ$、UET$、SOURCE$，这些表是在执行CREATE DATABASE语句期间由sql.bsq脚本创建的，一般情况下用户很少访问这些表。脚本catalog.sql（通常位于$ oracle_home/rdbms/admin）在CREATE DATABASE语句之后立即运行，创建数据字典视图。
　　数据字典视图大致可以分为三类：
　　.前缀为USER_的数据字典视图，包含了用户拥有的对象的信息。
　　.前缀为ALL_的数据字典视图，包含了用户当前可以访问的全部对象和权限的信息。
　　.前缀为DBA_的数据字典视图，包含了数据库拥有的所有对象和权限的信息。
　　在绝大多数数据字典视图中都有象DBA_TABLES,ALL_TABLES和USER_TABLES这样的视图家族。Oracle中有超过100个视图家族，所以要全面介绍这些视图家族是单调乏味的而且没有多大的意义。在下表中列出了最重要和最常用的视图家族，需要注意的是每个视图家族都有一个 DBA_,一个ALL_一个USER_视图。

 

视图家族(View Family)	描述
COL_PRIVS	包含了表的列权限，包括授予者、被授予者和权限
EXTENTS	数据范围信息，比如数据文件，数据段名(segment_name)和大小
INDEXES	索引信息，比如类型、唯一性和被涉及的表
IND_COLUMNS	索引列信息，比如索引上的列的排序方式
OBJECTS	对象信息，比如状态和DDL time
ROLE_PRIVS	角色权限，比如GRANT和ADMIN选项
SEGMENTS	表和索引的数据段信息，比如tablespace和storage
SEQUECNCES	序列信息，比如序列的cache、cycle和ast_number
SOURCE	除触发器之外的所有内置过程、函数、包的源代码
SYNONYMS	别名信息，比如引用的对象和数据库链接db_link
SYS_PRIVS	系统权限，比如grantee、privilege、admin选项
TAB_COLUMNS	表和视图的列信息，包括列的数据类型
TAB_PRIVS	表权限，比如授予者、被授予者和权限
TABLES	表信息，比如表空间(tablespace),存储参数(storage parms)和数据行的数量
TRIGGERS	触发器信息，比如类型、事件、触发体(trigger body)
USERS	用户信息，比如临时的和缺省的表空间
VIEWS	视图信息，包括视图定义

　　在Oracle中还有一些不常用的数据字典表，但这些表不是真正的字典家族，他们都是一些重要的单一的视图。

 

 

VIEW NAME	描述
USER_COL_PRIVS_MADE	用户授予他人的列权限
USER_COL_PRIVS_RECD	用户获得的列权限
USER_TAB_PRIVS_MADE	用户授予他人的表权限
USER_TAB_PRIVS_RECD	用户获得的表权限

　　其他的字典视图中主要的是V$视图，之所以这样叫是因为他们都是以V$或GV$开头的。V$视图是基于X$虚拟视图的。V$视图是SYS用户所拥有的，在缺省状况下，只有SYS用户和拥有DBA系统权限的用户可以看到所有的视图，没有DBA权限的用户可以看到USER_和ALL_视图，但不能看到 DBA_视图。与DBA_,ALL,和USER_视图中面向数据库信息相反，这些视图可视的给出了面向实例的信息。
　　　在大型系统上化几周时间手工输入每一条语句
　　　手工输入带用户名变量的语句，然后再输入每一个用户名，这需要花好几个小时的时间
　　　写一条SQL语句，生成需要的ALTER USER语句，然后执行他，这只需要几分钟时间
　　很明显我们将选择生成SQL的方法：
　　例：

 

 

SELECT ''ALTER USER''||username|| ''TEMPORARY TABLESPACE temp;'' FROM DBA_USERS WHERE username<>''SYS'' AND temporary_tablespace<>''TEMP'';

　　这个查询的结果将被脱机处理到一个文件中，然后在执行：

 

 

ALTER USER SYSTEM TEMPORARY TABLESPACE temp; ALTER USER OUTLN TEMPORARY TABLESPACE temp; ALTER USER DBSNMP TEMPORARY TABLESPACE temp; ALTER USER SCOTT TEMPORARY TABLESPACE temp; ALTER USER DEMO TEMPORARY TABLESPACE temp;

 

Oracle数据操作和控制语言详解

SQL 语言共分为四大类：数据查询语言DQL，数据操纵语言DML， 数据定义语言DDL，数据控制语言DCL。其中用于定义数据的结构，比如创建、修改或者删除数据库；DCL用于定义数据库用户的权限；在这篇文章中我将详细讲述这两种语言在Oracle中的使用方法。
　　DML语言
　　DML是SQL的一个子集，主要用于修改数据，下表列出了ORACLE支持的DML语句。

语句	用途
INSERT	向表中添加行
UPDATE	更新存储在表中的数据
DELETE	删除行
SELECT FOR UPDATE	禁止其他用户访问DML语句正在处理的行。
LOCK TABLE	禁止其他用户在表中使用DML语句

　　插入数据
　　INSERT语句常常用于向表中插入行，行中可以有特殊数据字段，或者可以用子查询从已存在的数据中建立新行。

　　列目录是可选的，缺省的列的目录是所有的列名，包括comlumn_id,comlumn_id可以在数据字典视图ALL_TAB_COLUMNS,USER_TAB_COLUMNS,或者DBA_TAB_COLUMNS中找到。
　　插入行的数据的数量和数据类型必须和列的数量和数据类型相匹配。不符合列定义的数据类型将对插入值实行隐式数据转换。NULL字符串将一个NULL值插入适当的列中。关键字NULL常常用于表示将某列定义为NULL值。
　　下面的两个例子是等价的。

INSERT INTO customers(cust_id,state,post_code) VALUE('Ariel',NULL,'94501');

　　或

INSERT INTO customers(cust_id,state,post_code) VALUE('Ariel',,'94501');

　　更新数据
　　UPDATE命令用于修改表中的数据。

UPDATE order_rollup SET(qty,price)=(SELECT SUM(qty),SUM(price) FROM order_lines WHERE customer_id='KOHL' WHERE cust_id='KOHL' AND order_period=TO_DATE('01-Oct-2000')

　　删除数据
　　DELETE语句用来从表中删除一行或多行数据，该命令包含两个语句：
　　　1、关键字DELETE FROM后跟准备从中删除数据的表名。
　　　2、WHERE后跟删除条件

DELETE FROM po_lines WHERE ship_to_state IN ('TX','NY','IL') AND order_date

　　清空表
　　如果你想删除表中所有数据，清空表，可以考虑使用DDL语言的TRUNCATE语句。TRUNCATE就像没有WHERE子句的DELETE命令一样。TRUNCATE将删除表中所有行。TRUNCATE不是DML语句是DDL语句，他和DELETE右不同的特点。

TRUNCATE TABLE (schema)table DROP(REUSE) STORAGE

　　STORAGE子串是可选的，缺省是DROP STORAGE。当使用DROP STORAGE时将缩短表和表索引，将表收缩到最小范围，并重新设置NEXT参数。REUSE STORAGE不会缩短表或者调整NEXT参数。
　　TRUNCATE和DELETE有以下几点区别

　　1、TRUNCATE在各种表上无论是大的还是小的都非常快。如果有ROLLBACK命令DELETE将被撤销，而TRUNCATE则不会被撤销。
　　2、TRUNCATE是一个DDL语言，向其他所有的DDL语言一样，他将被隐式提交，不能对TRUNCATE使用ROLLBACK命令。
　　3、TRUNCATE将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时，经过TRUNCATE操作后的表比DELETE操作后的表要快得多。
　　4、TRUNCATE不能触发任何DELETE触发器。
　　5、不能授予任何人清空他人的表的权限。
　　6、当表被清空后表和表的索引讲重新设置成初始大小，而delete则不能。
　　7、不能清空父表。
　　SELECT FOR UPDATE
　　select for update语句用于锁定行，阻止其他用户在该行上修改数据。当该行被锁定后其他用户可以用SELECT语句查询该行的数据，但不能修改或锁定该行。

　　锁定表
　　LOCK语句常常用于锁定整个表。当表被锁定后，大多数DML语言不能在该表上使用。LOCK语法如下：

LOCK schema table IN lock_mode

　　其中lock_mode有两个选项：
　　　share 共享方式
　　　exclusive 唯一方式

　　例：

LOCK TABLE intentory IN EXCLUSIVE MODE

　　死锁
　　当两个事务都被锁定，并且互相都在等待另一个被解锁，这种情况称为死锁。
　　当出现死锁时，ORACLE将检测死锁条件，并返回一个异常。

事务控制
　　事务控制包括协调对相同数据的多个同步的访问。当一个用户改变了另一个用户正在使用的数据时，oracle使用事务控制谁可以操作数据。
　　事务
　　事务表示工作的一个基本单元，是一系列作为一个单元被成功或不成功操作的SQL语句。在SQL和PL/SQL中有很多语句让程序员控制事务。程序员可以：
　　　1、显式开始一个事物，选择语句级一致性或事务级一致性
　　　2、设置撤销回滚点，并回滚到回滚点
　　　3、完成事务永远改变数据或者放弃修改。
　　事务控制语句

语句	用途
Commit	完成事务，数据修改成功并对其他用户开放
Rollback	撤销事务，撤销所有操作
rollback to savepoint	撤销在设置的回滚点以后的操作
set transaction	响应事务或语句的一致性；特别对于事务使用回滚段

　　例：

BEGIN UPDATE checking SET balance=balance-5000 WHERE account='Kieesha'; INSERT INTO checking_log(action_date,action,amount) VALUES (SYSDATE,'Transfer to brokerage',-5000); UPDATE brokerage SET cash_balance=cash_balance+5000 WHERE account='Kiesha'; INSERT INTO brokerage_log(action_date,action,amount) VALUES (SYSDATE,'Tracfer from checking',5000) COMMIT EXCEPTION WHEN OTHERS ROLLBACK END

　　Savepoint 和 部分回滚(Partial Rollback)
　　在SQL和PL/SQL中Savepoint是在一事务范围内的中间标志。经常用于将一个长的事务划分为小的部分。保留点Savepoint可标志长事务中的任何点，允许可回滚该点之后的操作。在应用程序中经常使用Savepoint；例如一过程包含几个函数，在每个函数前可建立一个保留点，如果函数失败，很容易返回到每一个函数开始的情况。在回滚到一个Savepoint之后，该Savepoint之后所获得的数据封锁被释放。为了实现部分回滚可以用带TO Savepoint子句的ROLLBACK语句将事务回滚到指定的位置。
　例

BEGIN INSERT INTO ATM_LOG(who,when,what,where) VALUES ('Kiesha',SYSDATE,'Withdrawal of $100','ATM54') SAVEPOINT ATM_LOGGED; UPDATE checking SET balance=balance-100 RETURN balance INTO new_balance; IF new_balance<0 THEN ROLLBACK TO ATM_LOGGED; COMMIT RAISE insufficient_funda; END IF END

　　关键字SAVEPOINT是可选的，所以下面两个语句是等价的：

ROLLBACK TO ATM_LOGGED; ROLLBACK TO SAVEPOINT ATM_LOGGED;

　　一致性和事务
　　一致性是事物控制的关键慨念。掌握了oracle 的一致性模型，能使您更好的，更恰当的使用事务控制。oracle通过一致性保证数据只有在事务全部完成后才能被用户看见和使用。这项技术对多用户数据库有巨大的作用。
　　oracle常常使用语句级（state-level)一致性，保证数据在语句的生命期之间是可见的但不能被改变。事务由多个语句组成，当使用事务时，事物级（transaction-level)一致性在整个事务生命期中保证数据对所有语句都是可见的。
　　oracle通过SCN(syatem change number)实施一致性。一个SCN是一个面向时间的数据库内部键。SCN只会增加不会减少，SCN表示了时间上的一个点，每个数据块都有一个SCN,通过比较这个点实施操作。
　　事务级一致性
　　SET TRANSACTION 的一个作用是确保事务级一致或语句级一致中有一个实施。ORACLE使用这些术语：
　　　ISOLATION LEVEL READ COMMIT 表示语句级一致
　　　ISOLATION LEVEL SERIALIZABLE 表示事务级一致。

　　例：

SET TRANSACTION ISOLATION LEVEL READ COMMIT; SET TRANSACTION ISOLATION LEVEL READ COMMIT

　　下面的语句也能确保事务级一致：

SET TRANSCATION READ ONLY

　　任何企图在只读（READ ONLY）事务中修改数据的操作都会抛出一个异常。但是，READ ONLY事务只能在下列语句中使用：

SELECT(没有FOR UPDATE子句） LOCK TABLE SET ROLE ALTER SYSTEM ALTER ALARM

　　即使没有改变任何数据，READ ONLY事务依然必须使用一个COMMIT或ROLLBACK以结束整个事务。
　　SET TRANSCTION的另外一个应用是在回滚时直接使用回滚段（ROLLBACK SEGMENT）。回滚段是ORACLE的一个特殊的数据对象，回滚段的头部包含正在使用该回滚段事务的信息。当用户回滚事务（ROLLBACK）时， ORACLE将会利用回滚段中的数据前影像来将修改的数据恢复到原来的值。oracle用round-robin给事务随机分配回滚段。一个大的事务可以分配任何回滚段，这也许会导致回滚段的大小变得很大。因此要避免让大的事务随机分配回滚段。
　　事务以SET TRANSACTION开始,象下面这样：

SET TRANSACTION USE ROLLBACK SEGMENT rb_large;

　　rb_large是一个大的回滚段的名称，现在就给一个大的事务分配了一个大的回滚段，其他的小的回滚段将不由动态空间管理，这样就更有效率。
　　下面我们看一个例子.我们有一个回滚段表空间大小是2G，在高峰时期需要10个回滚段以满足用户的需要，这些高峰在线用户只有小的事务。一周我们连续运行了4个大的事务，这些事务需要删除和加载数据，每一个撤销需要1G，回滚段的大小如下：

rb_large(initial 100M minextenta 2) rb1 (initial 1M next minextents 5) rb2 (initial 1M next minextents 5) rb3 (initial 1M next minextents 5) rb4 (initial 1M next minextents 5) rb5 (initial 1M next minextents 5) rb6 (initial 1M next minextents 5) rb7 (initial 1M next minextents 5) rb8 (initial 1M next minextents 5) rb9 (initial 1M next minextents 5) rb10 (initial 1M next minextents 5)

　　所有的都非常恰当的安排在2G的表空间中，如果我们缺省的round-robin给事务分配回滚段，4个大事务将有4个独立的回滚段，每个回滚段的大小将是1G,如果这样我们的2G表空间就不够，而数据库管理员就不得不在夜晚2点起来工作，每个事务都由以下面的语句开始：

SET TRANSACTION USE ROLLBACK SEGMENT rb_large

　　现在 4个事务重用相同的表空间，保正4个回滚段的表空间在2G以内。数据库管理员可以睡到天亮。
建立和修改用户
　　CREATE USER 语句将建立一个用户。当一个用户连接到ORACLE数据库时，它必须被验证。ORACLE中验证有三种类型：

　　　Database
　　　external
　　　Global
　　缺省是数据库验证，当用户连接到数据库时，oracle将检测用户是否是数据库的合法用户，并且要提供正确的password.external验证， oracle将只检测用户是否是合法用户，password已经被网络或系统验证了。global验证也是只检测是否是合法用户，password由 oraclesecurity server验证。
　　Database验证用户账号
　　数据库验证账号是张好的缺省类型，也是最普通的类型。建立一个账号是piyush,口令是welcome的账号，只需执行下面的命令：

CREATE USE piyush IDENTIFIED BY welcome

　　piyush可以通过下面的语句将口令改变为saraswatt:

ALTER USER piyush IDENTIFIED BY saraswati;

　　外部验证用户账号
　　用户账号进入数据库时可以不提供口令，这种情况下代替数据库识别口令的是客户端操作系统。外部验证账号有时也叫OPS$账号，当他们最初在 oracle6开始介绍时，oracle账号都有关键字前缀OPS$,这也就是为什么init.ora 参数os_authent_prefix是OPS$--默认特征与oracle6保持一致。os_authent_prefix定义的字符串必须被预处理为用于Oracle外部识别账号的操作系统账号名。创建操作系统用户appl的语句是：

CREATE USER ops$appl IDENTIFIED EATERNALLY

　　但在通常情况下，os_authent_prefix将被设置为空，像下面这样：

CREATE USER appl IDENTIFIED EATERNALLY

　　这样效果是一样的,关键字IDENTIFIED EXTERNALLY告诉ORACLE这是一个外部识别账号。

　　GLOBAL用户账号
　　GLOBAL类型的用户账号数据库不检测口令，而是由X.509目录服务器检测口令。创建一个GLOBAL类型的用户账号的方法是：

CREATE USER scott IDENTIFIED GLOBALLY AS "CN=scott,OU=divisional,O=sybex,C=US"

　　关键字IDENTIFIED GLOBALLY AS表示建立的是一个GLOBAL类型的用户账号.
　　创建和更改用户账号
　　CREATE USER 用于建立用户账号和给用户账号的属性赋值。ALTER USER用于更改用户账号和属性。但CREATE USER语句必须包括用户名和口令。
　　有部分属性能用CREATER USER和ALTER USER语句设置，下面对是这些的属性具体描述：
　　给用户分配缺省表空间

　　表空间（tablespace)是放置表、索引、丛等用户对象的。如果在create user语句中没有包含表空间，那么缺省的是系统表空间。

CREATE USER piyush IDENTIFIED BY saraswati DEFAULTE TABLESPACE user_data; ALTER USER manoj DEFAULTE TABLESPACE dev1_data;

　　给用户分配临时表空间
　　临时表空间，顾名思义是临时存放表、索引等用户对象的临时段。建立方法一样

CREATE USER piyush IDENTIFIED BY saraswati Temporary TABLESPACE user_data; ALTER USER manoj Temporary TABLESPACE dev1_data;

　　给用户分配表空间的使用定额
　　使用定额限制用户在表空间中使用磁盘的数量。定额可以按字节、千字节、兆字节或者无限制来制定。

CREATE USER piyush IDENTIFIED BY saraswati DEFAULT TABLESPACE user_data QUOTA UNLIMITED ON user_data QUOTA 20M ON tools; ALTER USER manoj QUOTA 2500K ON tools;

　　给用户分配一个简表
　　简表可以限制用户在会话时消耗的资源。这些资源包括：连接数据库的时间，空闲时间，每次会话的逻辑读数据的数量等等，缺省的简表对资源无限制。

CREATE USER piyush IDENTIFIED BY saraswati PROFILE TABLESPACE user_data; ALTER USER manoj Temporary TABLESPACE dev1_data;

　　为用户响应指定角色
　　这个属性只能由ALTER USER语句设置，试图用CREATE USER语句设置将回返回一个例外。

ALTER USER manoj DEFAULT ROLE ALL EXCEPT salary_adm;

　　为用户的password设定到期时间以便在用户下次登录时更改
　　当用户的password到期，在下一次登录时将强迫修改password，oracle提示用户输入旧的password,然后输入新的password。这项功能常用于新用户，当新用户用缺省的password登录时必须修改立即修改password.

ALTER USER manoj IDENTIFIED BY welcome; ALTER USER manoj PASSWORD EXPIRE;

　　锁定账号，是用户不能登录

ALTER USER ql AC COUNT LOCK

　　对账号解锁，以便用户能登录数据库

ALTER USER ql ACCOUNT UNLOCK

　　权限和角色
　　权限允许用户访问属于其它用户的对象或执行程序，ORACLE系统提供三种权限：
　　　Object 对象级
　　　System 系统级
　　　Role 角色级
　　这些权限可以授予给用户、特殊用户public或角色，如果授予一个权限给特殊用户"Public"（用户public是oracle预定义的，每个用户享有这个用户享有的权限）,那么就意味作将该权限授予了该数据库的所有用户。
　　对管理权限而言，角色是一个工具，权限能够被授予给一个角色，角色也能被授予给另一个角色或用户。用户可以通过角色继承权限，除了管理权限外角色服务没有其它目的。权限可以被授予，也可以用同样的方式撤销。

　　建立和使用角色
　　如前所诉，角色存在的目的就是为了使权限的管理变得轻松。建立角色使用CREATE ROLE语句，他的语法如下：

CREATE ROLE role_name IDENTIFIED BY password CREATE ROLE role_name IDENTIFIED EXTERNALLY CREATE ROLE role_name IDENTIFIED GLOBALLY

　　缺省情况下建立的角色没有password或者其他的识别。如果使用IDENTIFIED BY 子句建立，那么角色不会自动响应，必须用SET ROLE激活。

SET ROLE role_name IDENTIFIED BY password

　　 EXTERNALLY和GLOBALLY类型的角色由操作系统和ORACLE Service server验证。通常用户需要权限修改应用程序中使用的表单中的数据，但是只有在应用程序运行时而不是在使用ad hoc工具时，这种上下文敏感安全可以通过有PASSWORD的角色来实现。当用户在应用程序内部连结数据库时，代码将执行SET ROLE命令，通过安全验证。所以用户不需要知道角色的password,也不需要自己输入SET ROLE命令。

　　对象权限
　　对象权限就是指在表、视图、序列、过程、函数或包等对象上执行特殊动作的权利。有九种不同类型的权限可以授予给用户或角色。如下表：

权限	ALTER	DELETE	EXECUTE	INDEX	INSERT	READ	REFERENCE	SELECT	UPDATE
Directory	no	no	no	no	no	yes	no	no	no
function	no	no	yes	no	no	no	no	no	no
procedure	no	no	yes	no	no	no	no	no	no
package	no	no	yes	no	no	no	no	no	no
DB Object	no	no	yes	no	no	no	no	no	no
Libary	no	no	yes	no	no	no	no	no	no
Operation	no	no	yes	no	no	no	no	no	no
Sequence	yes	no	no	no	no	no	no	no	no
Table	yes	yes	no	yes	yes	no	yes	yes	yes
Type	no	no	yes	no	no	no	no	no	no
View	no	yes	no	no	yes	no	no	yes	yes

　　对象由不止一个权限，特殊权限ALL可以被授予或撤销。如TABLE的ALL权限就包括：
　　　SELECT,INSERT,UPDATE和DELETE,还有INDEX,ALTER,和REFERENCE。
　　如何看这个表我们以ALTER权限为例进行说明
　　ALTER权限
　　允许执行ALTER TABLE和LOCK TABLE操作，ALTER TABLE可以进行如下操作：
　　　　. 更改表名
　　　　. 增加或删除列
　　　　. 改变列的数据类型或大小
　　　　. 将表转变为分区表
　　在SEQUENCE上的ALTER权限允许执行ALTER Sequence语句，重新给sequence分配最小值、增量和缓冲区大小。
　　系统权限
　　系统权限需要授予者有进行系统级活动的能力，如连接数据库，更改用户会话、建立表或建立用户等等。你可以在数据字典视图 SYSTEM_PRIVILEGE_MAP上获得完整的系统权限。对象权限和系统权限都通过GRANT语句授予用户或角色。需要注意的是在授予对象权限时语句应该是WITH GRANT OPTION子句，但在授予系统权象时语句是WITH ADMIN OPTION，所以在你试图授予系统权限时，使用语句WITH GRANT OPTION系统会报告一个错误：ONLY ADMIN OPTION can be specified。在考试中要特别注意这个语法和错误信息。
　　角色和角色权限
　　角色权限就是将属于用户的权限授予一个角色。任何权限都可以授予给一个角色。授予系统权限给被授予者必须使用WITH_ADMIN_OPTION子句，在会话期间通过SET ROLE语句授予或撤销角色权限。然而，角色权限不能依靠存储在SQL中的权限。如果函数、程序、包、触发器或者方法使用另一个计划拥有的对象，那么就必须直接给对象的拥有者授权，这是因为权限不会在会话之间改变。
　　授予和撤销权限
　　　给用户或者角色授予权限使用GRANT 语句,GRANT语句的语法如下：

GRANT ROLE（或system privilege） TO user(role,Public) WITH ADMIN OPTION（可选）

　　对象权限被授予 WITH GRANT OPTION，

　　权限和数据字典
　　数据字典是ORACLE存储有关数据库结构信息的地方，数据本身存放在其他地方，数据字典由表和视图组成。在考试中关于数据字典最容易考的内容是：查看那一类权限已经被授予。比如DBA_TAB_PRIV包含了用户授予给另一用户的对象权限和在授予时是否带有WITH GRANT OTPION子串的信息。注意DBA_TAB_PRIV不仅仅包含了对表的权限的关系，他还包括函数、包、队列等等上的权限的关系。下表列出了所有的权限和角色的数据字典视图：
　　表： 权限的数据字典视图

视图	作用
ALL_COL_PRIVS	表示列上的授权，用户和PUBLIC是被授予者
ALL_COL_PRIVS_MADE	表示列上的授权，用户是属主和被授予者
ALL_COL_RECD	表示列上的授权，用户和PUBLIC是被授予者
ALL_TAB_PRIVS	表示对象上的授权，用户是PUBLIC或被授予者或用户是属主
ALL_TAB_PRIVS_MADE	表示对象上的权限，用户是属主或授予者
ALL_TAB_PRIVS_RECD	表示对象上的权限, 用户是PUBLIC或被授予者
DBA_COL_PRIVS	数据库列上的所有授权
DBA_ROLE_PRIVS	显示已授予用户或其他角色的角色
DBA_SYS_PRIVS	已授予用户或角色的系统权限
DBA_TAB_PRIVS	数据库对象上的所有权限
ROLE_ROLE_PRIVS	显示已授予用户的角色
ROLE_SYS_PRIVS	显示通过角色授予用户的系统权限
ROLE_TAB_PRIVS	显示通过角色授予用户的对象权限
SESSION_PRIVS	显示用户现在可利用的所有系统权限
USER_COL_PRIVS	显示列上的权限，用户是属主、授予者或被授予者
USER_COL_PRIVS_MADE	显示列上已授予的权限，用户是属主或授予者
USER_COL_PRIVS_RECD	显示列上已授予的权限，用户是属主或被授予者
USER_ROLE_PRIVS	显示已授予给用户的所有角色
USER_SYS_PRIVS	显示已授予给用户的所有系统权限
USER_TAB_PRIVS	显示已授予给用户的所有对象权限
USER_TAB_PRIVS_MADE	显示已授予给其他用户的对象权限，用户是属主
USER_TAB_PRIVS_RECD	显示已授予给其他用户的对象权限，用户是被授予者