DAF

PL/SQL复合数据类型(集合与记录)
PL/SQL有两种复合数据结构：记录和集合。记录由不同的域组成，集合由不同的元素组成。在本文中我们将讨论记录和集合的类型、怎样定义和使用记录和集合以及使用正确的循环控制。[zt]

[Ref: http://www.yesky.com/20020819/1625866.shtml ]
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_maintenance_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 --合法
　　但可以比较在集合内的两个元素。
使用正确的循环控制 [Ref: http://www.itgoogle.com/web/Show_Art.asp?ID=2008 ]
当你在 PL/SQL 中处理一个被索引的表时，很多情况下你不能确定表上是否存在索引，所以你不能使用最明显的 FOR 循环方式来循环表中的值。例如：

Declare
type my list_type is table of number index by pls_integer;
mylist mylist_type;
begin
-- start at 2, instead of 1
mylist(2) := 2;
mylist(3) := 3;
-- skip 4 and 5
mylist(6) := 6;
for i in 1..mylist.count loop
dbms_output.put_line(mylist(i));
end loop;
end;
/
前面的代码将返回 ORA-01403 错误：由于缺少索引1，没有发现数据。
如果你尝试使用 .FIRST/.LAST 修正这个问题，那么你将自动地从第一个索引开始循环，直到到达最后一个索引，如下：
Declare
type mylist_type is table of number index by pls_integer;
mylist mylist_type;
begin
mylist(2) := 2;
mylist(3) := 3;
mylist(5) := 6;
for i in mylist.first .. mylist.last loop
dbms_output.put_lin(mylist(i));
end loop;
end;
/
然而
然而，你依然得到一个 ORA-01403 错误：由于跳过值，没有发现数据。要进一步避免这种错误，你可以在访问索引前使用 EXISTS 方法测试它是否存在：

Declare
type mylist_type is table of number index by pls_integer;
mylist mylist_type;
begin
mylist(2) := 2;
mylist(3) := 3;
mylist(6) := 6;
for i in mylist.first..mylist.last loop
if (mylist.exists(i)) then
dbms_output.put_line(mylist(i));
end if;
end loop;
end;
/
或者，你可以通过.NEXT 属性使用常规的 LOOP 循环来迭代一个表上存在的索引——但是你必须声明循环计数器：
Declare
type mylist_type is table of number index by pls_integer;
mylist mylist_type;
i pls_integer;
begin
mylist(2) := 2;
mylist(3) := 3;
mylist(6) := 6;
i := mylist.first;
loop
dbms_output.put_line(mylist(i));
exit when i = mylist.last;
i := mylist.next(i);
end loop;
end;
/
当你不确定哪些值已经被删除或装载而导致缺少索引时，可以使用上面这两个有效的方法作为默认方法。但是对于使用 FORALL 语句的大量操作，这两种方法都不能正确运行，原因是 FORALL 语句并不是一个真正的循环，而且其语法只能带一个下加界，一个上边界和一个 SQL DML 语句。
然而，Oracle 10g中引入了两个新子句，这两个子句允许你避免这种局限性。向 FORALL 语法添加一个 INDICES OF 子句允许你自动地循环一个表中的所有值，而无须担心索引是否存在：
Declare
type mylist_type is table of number index by pls_integer;
mylist mylist_type;
begin
mylist(2) := 2;
mylist(3) := 3;
mylist(6) := 6;
foralli in indices of mylist
insert into mynumtable values(mylist(i));
end;
/
另外还有一个 valueS OF 子句，该子句使用你的嵌套表的值，或者被表索引的值作为循环的下标：
Declare
type mysubs_type is table of pls_integer index by pls_integer;
type mylist_type is table of number index by pls_integer;
mysubs mylist_type;
mylist mylist_type;
begin
mylist(2) := 2;
mysubs(10) := 2; -- point to mylist(2)
mylist(3) := 3;
mysubs(20) := 3; -- point to mylist(3)
mylist(6) := 6;
mysubs(30) := 6; -- point to mylist(6)
mysubs(40) := 3; -- point to mylist(3), again!
foralli in values ofmysubs
insert into mynumtable values(mylist(i));
end;
/
注意这个语法允许你控制对一个特定记录的顺序访问，甚至重复访问或者略过对它的访问。
===============================================================
create table t1 ( x int, y int );
INSERT INTO t1
SELECT rownum, rownum + 1
FROM all_users
WHERE rownum <= 5;

create table t2 ( x int, y int, z int );
ops$tkyte%ORA10GR2> select * from t2;
no rows selected
declare
type array is table of t1%rowtype;
l_data array;
begin
select * bulk collect into l_data from t1;
forall i in 1 .. l_data.count
insert into (select x, y from t2) values l_data(i);
end;
/
PL/SQL procedure successfully completed.
select * from t2;
X Y Z
---------- ---------- ----------
1 2
2 3
3 4
4 5
5 6

自治事务
无法回滚的审计
　　一般情况下利用触发器禁止某些对表的更新等操作时，若记录日志，则触发器最后抛出异常时会造成日志回滚。利用自治事务可防止此点。
　　避免变异表
　　即在触发器中操作触发此触发器的表
　　在触发器中使用DDL
　　写数据库
　　对数据库有写操作（INSERT、UPDATE、DELETE、CREATE、ALTER、COMMIT）的存储过程或函数是无法简单的用SQL来调用的，此时可以将其设为自治事务，从而避免ORA-14552（无法在一个查询或DML中执行DDL、COMMIT、ROLLBACK）、ORA- 14551（无法在一个查询中执行DML操作）等错误。需要注意的是函数必须有返回值，但仅有IN参数（不能有OUT或IN/OUT参数）。
　　开发更模块化的代码
　　在大型开发中，自治事务可以将代码更加模块化，失败或成功时不会影响调用者的其它操作，代价是调用者失去了对此模块的控制，并且模块内部无法引用调用者未提交的数据。
基于Oracle的高性能动态SQL程序开发
________________________________________
http://www.sina.com.cn 2004年10月22日 14:10 天极yesky

　　文/冯杰
    摘要：对动态SQL的程序开发进行了总结，并结合笔者实际开发经验给出若干开发技巧。

　　关键词：动态SQL，PL/SQL，高性能

　　1． 静态SQLSQL与动态SQL

　　Oracle编译PL/SQL程序块分为两个种：其一为前期联编（early binding），即SQL语句在程序编译期间就已经确定，大多数的编译情况属于这种类型；另外一种是后期联编（late binding），即SQL语句只有在运行阶段才能建立，例如当查询条件为用户输入时，那么Oracle的SQL引擎就无法在编译期对该程序语句进行确定，只能在用户输入一定的查询条件后才能
 

 
 
   

提交给SQL引擎进行处理。通常，静态SQL采用前一种编译方式，而动态SQL采用后一种编译方式。

　　本文主要就动态SQL的开发进行讨论，并在最后给出一些实际开发的技巧。

　　2． 动态SQL程序开发

　　理解了动态SQL编译的原理，也就掌握了其基本的开发思想。动态SQL既然是一种”不确定”的SQL，那其执行就有其相应的特点。Oracle中提供了Execute immediate语句来执行动态SQL，语法如下：

Excute immediate 动态SQL语句 using 绑定参数列表 returning into 输出参数列表;

　　对这一语句作如下说明：

　　1) 动态SQL是指DDL和不确定的DML（即带参数的DML）

　　2) 绑定参数列表为输入参数列表，即其类型为in类型，在运行时刻与动态SQL语句中的参数（实际上占位符，可以理解为函数里面的形式参数）进行绑定。

　　3) 输出参数列表为动态SQL语句执行后返回的参数列表。

　　4) 由于动态SQL是在运行时刻进行确定的，所以相对于静态而言，其更多的会损失一些系统性能来换取其灵活性。

　　为了更好的说明其开发的过程，下面列举一个实例：

　　设数据库的emp表，其数据为如下：
ID NAME SALARY
100 Jacky  5600
101 Rose  3000
102  John  4500

　　要求：

　　1．创建该表并输入相应的数据。

　　2．根据特定ID可以查询到其姓名和薪水的信息。

　　3．根据大于特定的薪水的查询相应的员工信息。

　　根据前面的要求，可以分别创建三个过程（均使用动态SQL）来实现：

　　过程一：
create or replace procedure create_table as
begin
execute immediate '
create table emp(id number,
name varchar2(10)
salary number; )'; --动态SQL为DDL语句
insert into emp
values (100,'jacky',5600);
insert into emp
values (101,'rose',3000);
insert into emp
values (102,'john',4500);
end create_table;

　　过程二：
create or replace procedure find_info(p_id number) as
v_name varchar2(10);
v_salary number;
begin
execute immediate '
select name,salary from emp
where id=:1'
using p_id
returning into v_name,v_salary; --动态SQL为查询语句
dbms_output.put_line(v_name ||'的收入为：'||to_char(v_salary))；
exception
when others then
dbms_output.put_line('找不到相应数据')；
end find_info;

　　过程三：
create or replace procedure find_emp(p_salary number) as
r_emp emp%rowtype;
type c_type is ref cursor;
c1 c_type;
begin
open c1 for '
select * from emp
where salary >:1'
using p_salary;
loop
fetch c1 into r_emp;
exit when c1%notfound;
dbms_output.put_line('薪水大于‘||to_char(p_salary)||’的员工为：‘);
dbms_output.put_line('ID为'to_char(r_emp)||' 其姓名为：'||r_emp.name);
end loop;
close c1;
end create_table;

　　注意：在过程二中的动态SQL语句使用了占位符“:1“，其实它相当于函数的形式参数，使用”：“作为前缀，然后使用using语句将p_id在运行时刻将:1给替换掉，这里p_id相当于函数里的实参。另外过程三中打开的游标为动态游标，它也属于动态SQL的范畴，其整个编译和开发的过程与execute immediate执行的过程很类似，这里就不在赘述了。
　3． 动态SQL语句开发技巧

　　前面分析到了，动态SQL的执行是以损失系统性能来换取其灵活性的，所以对它进行一定程度的优化也是必要的，笔者根据实际开发经验给出一些开发的技巧，需要指出的是，这里很多经验不仅局限于动态SQL，有些也适用于静态SQL，在描述中会给予标注。

　　技巧一：尽量使用类似的SQL语句，这样Oracle本身通过SGA中的共享池来直接对该SQL语句进行缓存，那么在下一次执行类似语句时就直接调用缓存中已解析过的语句，以此来提高执行效率。

　　技巧二：当涉及到集合单元的时候，尽量使用批联编。比如需要对id为100和101的员工的薪水加薪10％，一般情况下应该为如下形式：
declare
type num_list is varray(20) of number;
v_id num_list :=num_list(100,101);
begin
...
for i in v_id.first .. v_id.last loop
...
execute immediate 'update emp
set =salary*1.2
where id=:1 '
using v_id(i);
end loop;
end;

　　对于上面的处理，当数据量大的时候就会显得比较慢，那么如果采用批联编的话，则整个集合首先一次性的传入到SQL引擎中进行处理，这样比单独处理效率要高的多，进行批联编处理的代码如下：
declare
type num_list is varray(20) of number;
v_id num_list :=num_list(100,101);
begin
...
forall i in v_id.first .. v_id.last loop
...
execute immediate 'update emp
set =salary*1.2
where id=:1 '
using v_id(i);
end loop;
end;

　　这里是使用forall来进行批联编，这里将批联编处理的情形作一个小结：

　　1) 如果一个循环内执行了insert，delete，update等语句引用了集合元素，那么可以将其移 动到一个forall语句中。

　　2) 如果select into，fetch into 或returning into 子句引用了一个集合，应该使用bulk collect 子句进行合并。

　　3) 如有可能，应该使用主机数组来实现在程序和数据库服务器之间传递参数。

　　技巧三：使用NOCOPY编译器来提高PL/SQL性能。缺省情况下，out类型和in out类型的参数是由值传递的方式进行的。但是对于大的对象类型或者集合类型的参数传递而言，其希望损耗将是很大的，为了减少损耗，可以采用引用传递的方式，即在进行参数声明的时候引用NOCOPY关键字来说明即可到达这样的效果。比如创建一个过程：
create or replace procedure test(p_object in nocopy square)
...
end;

　　其中square为一个大的对象类型。这样只是传递一个地址，而不是传递整个对象了。显然这样的处理也是提高了效率。

　　4． 小结

　　本文对动态SQL的编译原理、开发过程以及开发技巧的讨论，通过本文的介绍后，相信读者对动态SQL程序开发有了一个总体的认识，为今后深入的工作打下一个良好的基础。

　　前面代码部分已经在下列环境中调试成功：

　　服务器端：UNIX+ORACLE9.2

　　客户端：WINDOWS2000 PRO+TOAD
Oracle 存储过程返回结果集
*过程返回记录集：
CREATE OR REPLACE PACKAGE pkg_test
AS
    TYPE myrctype IS REF CURSOR;
 
    PROCEDURE get (p_id NUMBER, p_rc OUT myrctype);
END pkg_test;
/
 
CREATE OR REPLACE PACKAGE BODY pkg_test
AS
    PROCEDURE get (p_id NUMBER, p_rc OUT myrctype)
    IS
       sqlstr   VARCHAR2 (500);
    BEGIN
       IF p_id = 0 THEN
          OPEN p_rc FOR
             SELECT ID, NAME, sex, address, postcode, birthday
               FROM student;
       ELSE
          sqlstr :=
             'select id,name,sex,address,postcode,birthday
            from student where id=:w_id';
          OPEN p_rc FOR sqlstr USING p_id;
       END IF;
    END get;
END pkg_test;
/
 
函数返回记录集:
建立带ref cursor定义的包和包体及函数：
CREATE OR REPLACE
package pkg_test as
/* 定义ref cursor类型
    不加return类型，为弱类型，允许动态sql查询，
    否则为强类型，无法使用动态sql查询;
*/
   type myrctype is ref cursor; 
  
--函数申明
   function get(intID number) return myrctype;
end pkg_test;
/
  
CREATE OR REPLACE
package body pkg_test as
--函数体
    function get(intID number) return myrctype is
      rc myrctype;  --定义ref cursor变量
      sqlstr varchar2(500);
    begin
      if intID=0 then
         --静态测试，直接用select语句直接返回结果
         open rc for select id,name,sex,address,postcode,birthday from
student;
      else
         --动态sql赋值，用:w_id来申明该变量从外部获得
         sqlstr := 'select id,name,sex,address,postcode,birthday from
student where id=:w_id';
         --动态测试，用sqlstr字符串返回结果，用using关键词传递参数
         open rc for sqlstr using intid;
      end if;
  
      return rc;
    end get;
  
end pkg_test;
UNION 和UNION ALL 的区别
2007-11-22 11:25:39 / 个人分类：mysql
      在数据库中，UNION和UNION ALL关键字都是将两个结果集合并为一个，但这两者从使用和效率上来说都有所不同。
UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。
实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：

select * from gc_dfys

union

select * from ls_jg_dfys

这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。

而UNION ALL只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。

从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用UNION ALL，如下：

select * from gc_dfys

union all

select * from ls_jg_dfys
NOCOPY是什么意思啊
请大家回答！又什么用，怎么用啊！

TOP
yesl
 
甲骨文五段
 
• 个人空间
• 发短消息
• 加为好友
• 当前离线  2# 大 中 小 发表于 2005-11-6 22:52  只看该作者
pl/sql中对out，in out参数使用的？默认形参会复制一份实参的副本，然后在内部传递，修改等，发生异常，不会赋值给实参，控制权交还调用环境，而实参值不变，还是调用前的值。而使用了nocopy后，形参将获得一个指向实参的指针，然后在内部传递，赋值都直接修改实参了，此时如果异常发生，控制权交还调用环境，但是实参已经被修改了。无法还原成调用前的值。

TOP
yesl
 
甲骨文五段
 
• 个人空间
• 发短消息
• 加为好友
• 当前离线  3# 大 中 小 发表于 2005-11-6 23:00  只看该作者
对参数I N使用nocopy将会产生编译错误，这是因为参数I N总是按引用传递.
NOCOPY的主要优点是可以提高程序的效率。当我们传递大型pl/sql表时，其优越性特别显著.使用NOCOPY的限制在某些情况下，NOCOPY将被编译器忽略，这时的参数仍将按值传递。这时，编译器不会报告编译错误。由于NOCOPY是一个提示项(Hint),编译器可以决定是否执行该项。在下列情况下，编译器将忽略NOCOPY项：
1.实参是索引表(index-by table)的成员时。如果该实参是全表，则该限制不起作用。
2.实参被强制指定精度，比例或NOT NULL时。该限制将不适用按最大长度强制的字符串参数。
3.实参和形参都是记录类型，二者是以隐含方式或使用了%ROWTYPE类型声明时，作用在对应字段的强制说明不一致。
4.传递实参需要隐式类型转换时。
5.子程序涉及到远程过程调用(PRC)。远程过程调用就是跨越数据库对远程服务器的过程调用。

TOP
flyandy2008
 
甲骨文初段
 
• 个人空间
• 发短消息
• 加为好友
• 当前离线  4# 大 中 小 发表于 2005-11-7 13:49  只看该作者
讲的好，学习中。。好贴子。。。

TOP
lypch
 
授权用户
 
• 个人空间
• 发短消息
• 加为好友
• 当前离线  5# 大 中 小 发表于 2005-11-7 17:59  只看该作者
3楼的能不能分别举例啊？谢谢！谢谢！

TOP
fandyfang
 
甲骨文初段
 
• 个人空间
• 发短消息
• 加为好友
• 当前离线  6# 大 中 小 发表于 2006-8-3 10:50  只看该作者
学习中 谢谢！

TOP
tomtomclub
 
甲骨文二段
 
• 个人空间
• 发短消息
• 加为好友
• 当前离线  7# 大 中 小 发表于 2006-9-7 14:43  只看该作者
我看书上面说
“PL/SQL中对IN参数都是用传引用的方式，对INOUT和OUT参数都是用传值的方式。。。。”
这样说对吗？
IN参数传引用的意义在于什么？ 效率？

TOP
tomtomclub
 
甲骨文二段
 
• 个人空间
• 发短消息
• 加为好友
• 当前离线  8# 大 中 小 发表于 2006-9-7 14:47  只看该作者
而书后一页中又说“在IN参数上使用NOCOPY时，会引发一个编译错误，因为IN参数总是以传递值的方式传递参数的，因此不允许使用编译器提示NOCOPY。”
自相矛盾了。。。。
不知道哪一个是真解！ 求高人指点。。。

注：清华大学出版社《Oracle Database 10g PL/SQL 程序设计》p279和p280两个地方。

TOP
tomtomclub
 
甲骨文二段
 
• 个人空间
• 发短消息
• 加为好友
• 当前离线  9# 大 中 小 发表于 2006-9-8 10:20  只看该作者
IN OUT和OUT参数如果传值的话，如何返回值呢？

TOP
oneyufun
 
甲骨文初段
 
• 个人空间
• 发短消息
• 加为好友
• 当前离线  10# 大 中 小 发表于 2007-10-31 15:52  只看该作者
学习了，谢谢！

TOP
zhan200012
 
甲骨文三段
 
• 个人空间
• 发短消息
• 加为好友
• 当前离线  11# 大 中 小 发表于 2007-11-1 14:30  只看该作者
跟C/C++里的传递指针地址一样

TOP
tekikesyo
 
甲骨文初段
 
• 个人空间
• 发短消息
• 加为好友
• 当前离线  12# 大 中 小 发表于 2007-11-1 16:02  只看该作者
回答得真好~~~

TOP
‹‹ 上一主题 | 下一主题 ››
Oracle 9i & 10g编程艺术数据库体系结构
2.1 定义数据库和实例 
http://book.csdn.net/ 2006-8-4 14:57:00
图书导读
当前章节:2.1 定义数据库和实例
•1.3.5 “怎么能让应用运行得更快?”
•1.3.6 DBA与开发人员的关系
•1.4 小结
•2.2 SGA和后台进程
•2.3 连接Oracle
•2.4 小结
《IT客》最佳IT管理者读物下载
IT管理不止是软件开发那么简单。让我们 来帮助你管好企业的所有IT资源吧
ad.cn.doubleclick.ne...
中低学历 挑战10万年薪
一般水平可不敢让你来试听！！
www.neworigin.net/
如何通过Blog进行学习？
CSDN Blog 三部曲之二 学习之道
live.csdn.netOracle被设计为一个相当可移植的数据库；在当前所有平台上都能运行，从Windows到UNIX再到大型机都支持Oracle。出于这个原因，在不同的操作系统上，Oracle的物理体系结构也有所不同。例如，在UNIX操作系统上可以看到，Oracle实现为多个不同的操作系统进程，实际上每个主要功能分别由一个进程负责。这种实现对于UNIX来说是正确的，因为UNIX就是以多进程为基础。不过，如果放到Windows上就不合适了，这种体系结构将不能很好地工作（速度会很慢，而且不可扩缩）。在Windows平台上，Oracle实现为一个多线程的进程。如果是一个运行OS/390和z/OS的IBM大型机系统，针对这种操作系统的Oracle体系结构则充分利用了多个OS/390地址空间，它们都作为一个Oracle实例进行操作。一个数据库实例可以配置多达255个地址空间。另外，Oracle还能与OS/390 工作负载管理器（Workload Manager，WLM）协作，建立特定Oracle工作负载相互之间的相对执行优先级，还能建立相对于OS/390系统中所有其他工作的执行优先级。尽管不同平台上实现Oracle所用的物理机制存在变化，但Oracle体系结构还是很有一般性，所以你能很好地了解Oracle在所有平台上如何工作。
这一章会从全局角度概要介绍这个体系结构。我们会分析Oracle服务器，并给出“数据库”和“实例”等术语的定义（这些术语通常很容易混淆）。这里还会介绍“连接”到Oracle时会发生什么，另外将从高层分析服务器如何管理内存。在后续3章中，我们还会详细介绍Oracle体系结构中的3大部分：
q  第3章将介绍文件，其中涵盖构成数据库的5大类文件：参数文件、数据文件、临时文件、控制文件和重做日志文件。我们还会介绍另外几类文件，包括跟踪文件、警告文件、转储文件（DMP）、数据泵文件（data pump）和简单的平面文件。这一章将谈到Oracle 10g 新增的一个文件区，称为闪回恢复区（Flashback Recovery Area），另外我们还会讨论自动存储管理（Automatic Storage Management，ASM）对文件存储的影响。
q  第4章介绍Oracle的一些内存结构，分别称为系统全局区（System Global Area，SGA）、进程全局区（Process Global Area，PGA）和用户全局区（User Global Area，UGA）。我们会分析这些结构之间的关系，并讨论共享池（shared pool）、大池（big pool）、Java池（Java pool）以及SGA中的其他一些组件。
q  第5章介绍Oracle的物理进程或线程。我们会讨论数据库上运行的3类不同的进程：服务器进程（server process）、后台进程（background process）和从属进程（slave process）。
先介绍哪一部分实在很难定夺。由于进程使用了SGA，所以如果在进程之前先介绍SGA可能不太合适。另一方面，讨论进程及其工作时，又会引用SGA。另外两部分的关系也很紧密：文件由进程处理，如果不先了解进程做什么，将很难把文件搞清楚。
正因如此，我会在这一章定义一些术语，对Oracle是什么提供一个一般性的概述（也许你会把它画出来）。有了这些准备，你就能深入探访各个部分的具体细节了。
2.1  定义数据库和实例
在Oracle领域中有两个词很容易混淆，这就是“实例”（instance）和“数据库”（database）。作为Oracle术语，这两个词的定义如下：
q  数据库（database）：物理操作系统文件或磁盘（disk）的集合。使用Oracle 10g的自动存储管理（Automatic Storage Management，ASM）或RAW分区时，数据库可能不作为操作系统中单独的文件，但定义仍然不变。
q  实例（instance）：一组Oracle后台进程/线程以及一个共享内存区，这些内存由同一个计算机上运行的线程/进程所共享。这里可以维护易失的、非持久性内容（有些可以刷新输出到磁盘）。就算没有磁盘存储，数据库实例也能存在。也许实例不能算是世界上最有用的事物，不过你完全可以把它想成是最有用的事物，这有助于对实例和数据库划清界线。
这两个词有时可互换使用，不过二者的概念完全不同。实例和数据库之间的关系是：数据库可以由多个实例装载和打开，而实例可以在任何时间点装载和打开一个数据库。实际上，准确地讲，实例在其整个生存期中最多能装载和打开一个数据库！稍后就会介绍这样的一个例子。
是不是更糊涂了？我们还会做进一步的解释，应该能帮助你搞清楚这些概念。实例就是一组操作系统进程（或者是一个多线程的进程）以及一些内存。这些进程可以操作数据库；而数据库只是一个文件集合（包括数据文件、临时文件、重做日志文件和控制文件）。在任何时刻，一个实例只能有一组相关的文件（与一个数据库关联）。大多数情况下，反过来也成立：一个数据库上只有一个实例对其进行操作。不过，Oracle的真正应用集群（Real Application Clusters，RAC）是一个例外，这是Oracle提供的一个选项，允许在集群环境中的多台计算机上操作，这样就可以有多台实例同时装载并打开一个数据库（位于一组共享物理磁盘上）。由此，我们可以同时从多台不同的计算机访问这个数据库。Oracle RAC能支持高度可用的系统，可用于构建可扩缩性极好的解决方案。
下面来看一个简单的例子。假设我们刚安装了Oracle 10g 10.1.0.3。我们执行一个纯软件安装，不包括初始的“启动”数据库，除了软件以外什么都没有。
通过pwd命令可以知道当前的工作目录（这个例子使用一个Linux平台的计算机）。我们的当前目录是dbs（如果在Windows平台上，则是database目录）。执行ls–l命令显示出这个目录为“空”。其中没有init.ora 文件，也没有任何存储参数文件（stored parameter file，SPFILE）；存储参数文件将在第3章详细讨论。
 
使用ps（进程状态）命令，可以看到用户ora10g运行的所有进程，这里假设ora10g是Oracle软件的所有者。此时还没有任何Oracle数据库进程。
 
然后使用ipcs命令，这个UNIX命令可用于显示进程间的通信设备，如共享内存、信号量等。目前系统中没有使用任何通信设备。
 
然后启动SQL*Plus（Oracle的命令行界面），并作为SYSDBA连接（SYSDBA账户可以在数据库中做任何事情）。连接成功后，SQL*Plus报告称我们连上了一个空闲的实例：
 
我们的“实例”现在只包括一个Oracle服务器进程，见以下输出中粗体显示的部分。此时还没有分配共享内存，也没有其他进程。
 
 
现在来启动实例：
 
这里提示的文件就是启动实例时必须要有的一个文件，我们需要有一个参数文件（一种简单的平面文件，后面还会详细说明），或者要有一个存储参数文件。现在就来创建参数文件，并放入启动数据库实例所需的最少信息（通常还会指定更多的参数，如数据库块大小、控制文件位置，等等）。
 
然后再回到SQL*Plus：
 
 这里对startup命令加了nomount选项，因为我们现在还不想真正“装载”数据库（要了解启动和关闭的所有选项，请参见SQL*Plus文档）。
注意    在Windows上运行startup命令之前，还需要使用oradim.exe实用程序执行一条服务创建语句。
现在就有了所谓的“实例”。运行数据库所需的后台进程都有了，如进程监视器（process monitor，PMON）、日志写入器（log writer，LGWR）等，这些进程将在第5章详细介绍。
 
再使用ipcs命令，它会首次报告指出使用了共享内存和信号量，这是UNIX上的两个重要的进程间通信设备：
 
注意，我们还没有“数据库”呢！此时，只有数据库之名（在所创建的参数文件中），而没有数据库之实。如果试图“装载”这个数据库，就会失败，因为数据库根本就不存在。下面就来创建数据库。有人说创建一个Oracle数据库步骤很繁琐，真是这样吗？我们来看看：
 
这里创建数据库就是这么简单。但在实际中，也许要使用一个稍有些复杂的CREATE DATABASE命令，因为可能需要告诉Oracle把日志文件、数据文件、控制文件等放在哪里。不过，我们现在已经有了一个完全可操作的数据库了。可能还需要运行$ORACLE_HOME/rdbms/admin/ catalog.sql脚本和其他编录脚本（catalog script）来建立我们每天使用的数据字典（这个数据库中还没有我们使用的某些视图，如ALL_OBJECTS），但不管怎么说，数据库已经有了。可以简单地查询一些Oracle V$视图（具体就是V$DATAFILE、V$LOGFILE和V$CONTROLFILE），列出构成这个数据库的文件：
 
Oracle使用默认设置，把所有内容都放在一起，并把数据库创建为一组持久的文件。如果关闭这个数据库，再试图打开，就会发现数据库无法打开：
 
 
一个实例在其生存期中最多只能装载和打开一个数据库。要想再打开这个（或其他）数据库，必须先丢弃这个实例，并创建一个新的实例。
重申一遍：
q  实例是一组后台进程和共享内存。
q  数据库是磁盘上存储的数据集合。
q  实例“一生”只能装载并打开一个数据库。
q  数据库可以由一个或多个实例（使用RAC）装载和打开。
前面提到过，大多数情况下，实例和数据库之间存在一种一对一的关系。可能正因如此，才导致人们很容易将二者混淆。从大多数人的经验看来，数据库就是实例，实例就是数据库。
不过，在许多测试环境中，情况并非如此。在我的磁盘上，可以有5个不同的数据库。测试主机上任意时间点只会运行一个Oracle实例，但是它访问的数据库每天都可能不同（甚至每小时都不同），这取决于我的需求。只需有不同的配置文件，我就能装载并打开其中任意一个数据库。在这种情况下，任何时刻我都只有一个“实例”，但有多个数据库，在任意时间点上只能访问其中的一个数据库。
所以，你现在应该知道，如果有人谈到实例，他指的就是Oracle的进程和内存。提到数据库时，则是说保存数据的物理文件。可以从多个实例访问一个数据库，但是一个实例一次只能访问一个数据库。
一般视图和物化视图的区别.
请教一般视图和物化视图的区别,谢谢.

TOP
fly115
 
超级版主
   
• 个人空间
• 发短消息
• 加为好友
• 当前离线  2# 大 中 小 发表于 2005-3-18 13:50  只看该作者
物化视图，可以理解成是一个表，但是这个表中的数据需要某种刷新机制才能更新里面的数据。
他通常用在数据仓库（olap相关的库）中，在响应时间做为第1位的前提下，对于很多报表分析中有很多复杂的计算等都可以集合在物化视图中，这样这个计算所消耗的时间和资源是被分布在刷新那一刻，而我们select 这个视图的时候，就直接可以取这个被刷新后的统计信息了，这样就相当于跟表一样拿来用，而且可以建立索引，响应时间自然快了。

TOP
zhpsam
 
授权用户
 
• 个人空间
• 发短消息
• 加为好友
• 当前离线  3# 大 中 小 发表于 2005-3-19 17:10  只看该作者
学习！

TOP
城市中的树
 
甲骨文二段
 
• 个人空间
• 发短消息
• 加为好友
• 当前离线  4# 大 中 小 发表于 2006-12-12 15:04  只看该作者
正在查这个东东,学习了.

TOP
loverbing1314
 
甲骨文四段
 
• 个人空间
• 发短消息
• 加为好友
• 当前离线  5# 大 中 小 发表于 2006-12-12 16:09  只看该作者
物化视图就是在数据库中存储查询的结果，与运行时确定结果的视图不同，物化视图结果会预先计算并存储。，如果一个表有很庞大的数据的话，用物化视图会大大缩减时间的
漂在上海

TOP
cby
 
甲骨文四段
 
• 个人空间
• 发短消息
• 加为好友
• 当前离线  6# 大 中 小 发表于 2007-8-9 14:57  只看该作者
我是否可以这样理解？
1、物化视图把SELECT的结果存放在某个地方，这个地方就是物化视图。它是占用空间的。在查询物化视图时时间节约很多。但如果主表的数据有更新，那么可能两者数据不同步。
而普通的视图只是保存一个SELECT语句的定义，并不占用空间。

2、建立了物化视图后，是否还有必要在物化视图上建立索引？

3、可否使用select table temptablename as select * from a,b;
     使用这个temptablename来替代物化视图？

[ 本帖最后由 cby 于 2007-8-9 15:02 编辑 ]
物化视图是已经将查询结果（即视图）生成好放在那里（物化）了，这个查询结果的更新方式是由物化视图的定义时给出的。而普通的视图仅仅是一个定义，在查询视图时才做真正的查询。
根本区别
物化试图的本质是表，或者说MV就是表，是可以有记录的
试图只是一个SQL语句，或者说只是一个SELECT语句，经过编译的SELECT语句，本身没有记录

分析函数是oracle816引入的一个全新的概念
1. 自动汇总函数rollup,cube,

　　2. rank 函数, rank,dense_rank,row_number

　　3. lag,lead函数

　　4. sum,avg,的移动增加,移动平均数

　　5. ratio_to_report报表处理函数

　　6. first,last取基数的分析函数
第一章
运行脚本
@E:/oracle/1.sql
start E:/oracle/1.sql

运行缓存中的命令
/
run

常用语句
setlinesize
cleanbuffer

grant付权语句

grant create session,create table,create view
to he;
--DBA赋予建表，视图等权限没有ON

grant select
on hr.employees
to he;
--HR赋予he搜索employees表的权限要用ON

grant select,insert
on departments
to he;

select * from hr.employees;
--he用户搜索hr的表要加前缀HR

revoke select
on employees
from he;

grant select on scott.emp to he with grant option;
--赋予he搜索EMP表并且传承能力,对象权限,回收
grant select on scott.emp to hr;
grant select on scott.emp to he with admin option;
--赋予he搜索EMP表并且传承能力,对象权限,不回收

revoke select on scott.emp from he;
--回收HE搜索EMP表，传承能力也消失,he赋予hr看scott的emp表的能力也连带消失

q分界符
select department_name||
q'<,it is assigned Manager Id:>'
||manager_id
as "department and manager"
from departments;

列别名

去处重复值（行的过滤）
select DISTINCT department_id
from employees;

第二章
替代变量
&&一次输入每次替换
&每次输入每次替换
select &&m from emp where &&m>1000;

select last_name,department_id,salary*12
from employees
where job_id='&&job_title';

define命令
是SQLPULS环境下的命令

between
闭区间，上下限不能交换

in
not

LIKE 转义字符(escape)
select employee_id,last_name,job_id
from employees where job_id like '%SA/_%' escape '/';

判断NULL
NULL IS NULL

列别名可以在哪些子句中？
select order by中，其他都不可以
表别名 在所有的子句中出现

日期型数据
number -  string -  date可以隐式转换
符合语言环境

优先级别（高到低）具体看PPT 2-18
算术（左到右） 条件 逻辑（右到左）

条件集合
any=some all
all 必须大于最大值
any 只要大于最小值

order by
不能出现聚合函数，可以是表达式，NULL在排序中NULL排在最后（升序时）

第三章（单行函数）

数值型
round（四舍五入）
trunc（截整）
mod(取余)
CEIL（向上取整）
FLOOR（向下取整）

常用
ABS
ASCII
ADD_MONTHS
AVG（平均）
CHARTOROWID（把字符串转为行号）
CONCAT（连接字符串）
COUNT
DECODE
FROM_TZ(合并成带时区的时间戳)
INITCAP（首字母大写）
INSTE（查找子串）
LENGTH
LOWER（小写）
LPAD（左填充）
LTRIM
NULLIF（查找空值）
NVL（处理空值）
NVL2（不能在PL/SQL中使用）
REGEXP_INSTR
REGEXP_REPLACE
REGEXP_SUBSTR
REGEXP_（LIKE）
SUM
TO_CHAR（DATETIME）
TO_YMINTERVAL（时间间隔）
UPER（转换成大写）

GROUPING（对cube或rollup的统计）
select deptno,empno,sum(sal)
from emp
group by cube(deptno,empno);
---cube比rollup多出纵向统计，它是旋转的

select deptno,empno,sum(sal),
           grouping(deptno) grp_dept,
           grouping(empno)  grp_emp
from emp
group by rollup(deptno,empno);
 ---grouping上出现的值是否由rollup或cube产生的，grouping的值只能是0和1
 
select deptno,empno,mgr,avg(sal)
from emp
group by grouping sets(deptno,empno,mgr);
--grouping sets是分组查询的多个并操作，它不旋转

select deptno,empno,mgr,sum(sal),
                   grouping(deptno) grp_dept,
           grouping(empno)  grp_emp,
           grouping(mgr)  grp_mgr
from emp
group by rollup(deptno,(empno,mgr));

select deptno,empno,mgr,sum(sal),
 grouping(deptno) grp_dept,
           grouping(empno)  grp_emp,
           grouping(mgr)  grp_mgr
from emp
group by rollup(deptno,empno,mgr);

EXTRACT（萃取日期）
select extract (year from sysdate)||
                                 'year'||extract(month from sysdate)|| 
                                 'month'||extract(day from sysdate)||
                                 'day'
from dual;

TO_CHAR日期转换
select employee_id,to_char(hire_date,'mm/yy') month_hired
from employees
where last_name='Higgins';

select last_name,
to_char(hire_date,'fmDdspth  Month YYYY fmHH:MI:SS:AM')as hiredate
from employees
where rownum<5;

select last_name,
to_char(hire_date,'dd  Month yyyy')as hiredate
from employees
where rownum<5;

select to_char(salary,'$99,999.00')salary
from employees
where last_name='Ernst';

select last_name,job_id,salary,
       case job_id when 'IT_PROG'  then 1.10*salary
                   when 'ST_CLERK' then 1.15*salary
                   when 'SA_REP'   then 1.20*salary
       ELSE    salary end  "revised_salary"
from employees
where job_id='ST_CLERK';  

select last_name,job_id,salary,
       decode(job_id,'IT_PROG',    1.10*salary,
                      'ST_CLERK' , 1.15*salary,
                       'SA_REP'  , 1.20*salary,
                     salary)
                    revised_salary   
from employees
where job_id='IT_PROG';

select last_name,salary,
       decode(TRUNC(salary/2000,  0),
                       0, 0.00,
                       1,0.09,
                       2,0.20,
                       3,0.30,
                       4,0.40,
                       5,0.42,
                       6,0.44,
                         0.45)TAX_RATE
from employees
where department_id=80;

第四章（聚合函数）
某些聚合函数可以用于非分组函数
比如:
select count(*)
from employees
where department_id=50;

select department_id,avg(salary)
from employees
group by department_id;

select department_id,max(salary)
from employees
group by department_id
having max(salary)>10000;

COUNT(*)包括空值
COUNT(列名)不包括空值

第5章

select e.ename,deptno,d.dname
from emp e  join dept d using(deptno);
--using用在自然链接(等值链接)中多个相同的列，而需找到一个列        （内链接）

select e.ename "员工姓名",e.hiredate "员工雇佣日期",e.empno "员工编号",
m.ename "领导姓名",m.hiredate "领导雇佣日期",m.empno "领导编号"
from emp e join emp m on e.mgr=m.empno
where e.hiredate<m.hiredate;
--自连接,显示所有比上级领导入职还要早的员工姓名及其上级领导的姓名，并在输出时标示适当的列名。

select ename,daname,sal from emp e,dept d where e.deptno=d.deptno
and e.deptno in(10,30) and hiredate like '%81';

select e.ename,e.sal,s.grade
from emp e join salgrade s
on e.sal between s.losal and s.hisal;
--非等值链接 用了between and   (内链接)

select e.ename,d.deptno,d.dname
from emp e right outer join dept d
on e.deptno=d.deptno
--右链接 关键字right   (外链接)          

select last_name,department_name
from employees
cross join departments;
--交叉链接 关键字cross join （全链接）(笛卡儿集)

第六章（子查询）
子查询可以在哪些中出现？
子查询可以在HAVING中出现
FROM，SELECT等所有地方
但是除ORDER BY(在特殊情况下ORDER BY也可以)

select last_name,salary
from employees
where salary>(select salary
               from employees
               where last_name='Abel');
              
select last_name,job_id,salary
from employees
where job_id=(select job_id
               from employees
               where employee_id=141); 
              
select last_name,job_id,salary
from employees
where salary>(select salary                        
               from employees
               where employee_id=143);
              
select last_name,job_id,salary
from employees
where salary=(select min(salary)
               from employees);

select department_id,min(salary)
from employees
group by   department_id
having   min(salary)> (select min(salary)
                         from employees
                          where department_id=50);
                         
select employee_id,last_name,job_id,salary
from employees
where salary<any(select salary
                   from employees                         
                   where job_id='IT_PROG')                      
and job_id<>'IT_PROG';                         
--<any是<其中一个就行             
select employee_id,last_name,job_id,salary
from employees
where salary<all(select salary
                   from employees                         
                   where job_id='IT_PROG')                         
and job_id<>'IT_PROG'; 
--<all是<所有的

第七章（集合操作符）
只有union all不过滤重复行，union,minus,intersect，都过滤重复行
结果集的排列按集合操作第一个集合中第一列的自然顺序排列
并集合
select employee_id,job_id
from employees
where department_id=100
union
select employee_id,job_id
from job_history
where employee_id=201
/
交集合
select employee_id
from employees
intersect
select employee_id
from job_history
/
差集
select employee_id,job_id
from employees
minus
select employee_id,job_id
from job_history
/

第八章（DML数据操作语言，增删改）
insert into departments(department_id,
          department_name,manager_id,location_id)
values(111,'Public Relation',100,1700);
--向4个列里都插入

insert into departments(department_id,
                        department_name)
values(323,'Purching');
--向选中的两个列里插入

insert into departments
values(5611,'Fico',null,null);
--向表中插入数据，前两个插入正常数据，后两个插入null

insert into departments(department_id,
                       department_name,location_id)
values(&department_id,'&department_name',&location);

exists

update employees
set department_id=70
where employee_id=113;
--修改数据

update    employees
set job_id= (select job_id
            from employees
             where employee_id=205)
where employee_id=114;

delete from  temp_dept
where deptno in(select deptno from dept where rownum<5)
/

事物的开始和结束
开始：自上次事物结束后的第一条DML语句
结束：commit,rollback,系统崩溃，结束会话，DDL语句
rollback to 保存点不代表事物的结束
保存点：savepoint

资源的锁定（资源的死锁）
DML都有行级排他锁；

第九章(DDL语句)
drop table hire_dates;
create table hire_dates
              (id     number(8),
              hire_date date default sysdate);

用USING子句指定索引

alter table hire_dates
add (name  varchar2(10));
--�%