Oracle开发参考集手册(FAQ)

Oraclefaq集更新人更新日期更新方式更新内容崔继才2007-6-10新建新建崔继才2007-12-20增加HYPERLINK[Q]如何给视图建立主键约束崔继才2007-12-20增加HYPERLINK[Q]如何将access中的数据导入到oracle中崔继才2008-4-16增加第六部分关于授权的问题崔继才2增加同步更新分布式数据库的BLOB字段崔继才2008-5-17增加增加oracle的全表检索的内容崔继才2008-5-19增加如何去掉字符串中的空格崔继才增加如何修改proceser见ORA-12516/12520:TNS部分崔继才2008-7-7新增增加了如何将blob转换成CLOB的方法见BLOB字段转换成CLOB的方法崔继才2008-7-17新增增加了关于处理Ora-12518的处理办法崔继才2008-7-29新增导出带大字段的错误处理（9i）EXP-00003崔继才2008-8-16新增增加了对于大字段全文检索的功能的介绍，并在小结中以实际使用的案例给出了使用的实例（本节最后一页）崔继才2009-7-11新增HYPERLINK新增如何移动数据文件崔继才2009-11-26新增HYPERLINK如何向一个含有BLOB字段的表中插入记录并且插入的BLOB的内容是VARCHAR型的崔继才2009-11-27新增HYPERLINKORACLE中LOB字段的使用和维护崔继才2009-11-27新增HYPERLINKORACLE中如何用UTL_FILE包读取本地数据然后存入数据库崔继才2010-1-19新增HYPERLINKOracle10gDBMS_SCHEDULER崔继才2010-1-21新增HYPERLINKSQLPLUS命令的使用大全崔继才2010-1-26新增HYPERLINK如何给视图增加整体说明的注释崔继才2010-1-27新增HYPERLINK如何使得在删除记录后表空间进行回收（修改表的参数）崔继才2010-1-27新增HYPERLINK如何回收表空间（SHRINK）崔继才2010-3-4新增HYPERLINK通过oracle如何校验身份证的合法崔继才2010-4-25新增HYPERLINK关于Oracle10g正则表达式使用崔继才2010-6-26新增HYPERLINK[Q]如何移动tab，IDX，LOB等的存储空间崔继才2010-7-21新增HYPERLINK[Q]如何定义和使用一维数组崔继才2010-7-23新增HYPERLINK[Q]oracle用户授权的脚本崔继才2010-8-24新增HYPERLINK[Q]如何将CLOB转换为Blob崔继才2010-9-11新增HYPERLINKORA-01536:超出表空间'表空间名'的空间限量或者是ORA-01536spacequotaexceededfortablespace崔继才2010-9-19新增HYPERLINK怎样恢复Delete的数据？崔继才2010-10-28新增HYPERLINK对于树形结构的数据表，如何查询并构建带连接线的查询结果？崔继才2010-10-28新增HYPERLINKUnix下如何查询数据库的状态崔继才2011-5-11新增HYPERLINK如何将对象放入keep和recycle池中崔继才2011-5-11新增HYPERLINKKEEPpool,recyclepool和DEFAULTpool的讲解崔继才2011-5-27新增HYPERLINK[Q]没有DBA的权限的前提下可以给自己授DBA的权限的脚步(ORACLE的bug)崔继才2011-6-9新增HYPERLINKOracle用户密码过期和用户被锁解决方法崔继才2011-06-28新增HYPERLINK更新丢失解决问题（更新冲突了崔继才2011-9-6新增HYPERLINK怎样删除表空间及其数据文件崔继才2011-9-13新增HYPERLINK[Q]VARCHAR2与NVARCHAR2类型共同查询（UNION）及转换第一部分、SQL&PL/SQL[Q]怎么样查询特殊字符，如通配符%与_[A]select*fromtablewherenamelike'A\_%'escape'\'怎样把“&”放入一条记录中？

[A]insertintoavalues(translate('at{&}t','at{}','at'));[Q]如何更改用户默认的表空间

alteruseruser_namedefaulttablespaceusers;--修改用户的默认表空间altertableflycategorymovetablespaceusers;--修改单个表的表空间select'alterindex'||t.index_name||'rebuildtablespacedis_tablespace;'FROMuser_indexestWHEREt.TABLESPACE_name='USERS'[Q]如何生成批量脚本的spoolc:\c.SQL--文件输出到什么位置--生成批量的语句(可以做其它的类型的操作，如deletedrop等等)SELECT'altertable'||t.table_name||'movetablespaceusers;'FROMdba_tablestWHEREt.owner='FLYWHEEL';spooloff在sql/plus中执行批量的内容：@c:\c.sql;[Q]如何获得数据库表中任意条记录SELECT* from(selectt.colum_namefromtable_nametorderbydbms_random.VALUE)kwhererownum<=n;[Q]如何插入单引号到数据库表中[A]可以用ASCII码处理，其它特殊字符如&也一样，如

insertintotvalues('i'||chr(39)||'m');

--chr(39)代表字符'

或者用两个单引号表示一个

orinsertintotvalues('I''m');

--两个''可以表示一个'[Q]怎样设置事务一致性

[A]settransaction[isolationlevel]readcommitted;

默认语句级一致性

settransaction[isolationlevel]serializable;

readonly;

事务级一致性[Q]怎么样利用游标更新数据[A]cursorc1is

select*fromtablename

wherenameisnullforupdate[ofcolumn]

……

updatetablenamesetcolumn=……

wherecurrentofc1;[Q]怎样自定义异常[A]pragma_exception_init(exception_name,error_number);

如果立即抛出异常

raise_application_error(error_number,error_msg,true|false);

其中number从-20000到-20999，错误信息最大2048B

异常变量

SQLCODE

错误代码

SQLERRM

错误信息[Q]十进制与十六进制的转换[A]8i以上版本：

to_char(100,'XX')

to_number('4D','XX')

8i以下的进制之间的转换参考如下脚本

createorreplacefunctionto_base(p_decinnumber,p_baseinnumber)

returnvarchar2

is

l_strvarchar2(255)defaultNULL;

l_numnumberdefaultp_dec;

l_hexvarchar2(16)default'0123456789ABCDEF';

begin

if(p_decisnullorp_baseisnull)then

returnnull;

endif;

if(trunc(p_dec)<>p_decORp_dec<0)then

raisePROGRAM_ERROR;

endif;

loop

l_str:=substr(l_hex,mod(l_num,p_base)+1,1)||l_str;

l_num:=trunc(l_num/p_base);

exitwhen(l_num=0);

endloop;

returnl_str;

endto_base;

/

createorreplacefunctionto_dec

(p_strinvarchar2,

p_from_baseinnumberdefault16)returnnumber

is

l_numnumberdefault0;

l_hexvarchar2(16)default'0123456789ABCDEF';

begin

if(p_strisnullorp_from_baseisnull)then

returnnull;

endif;

foriin1..length(p_str)loop

l_num:=l_num*p_from_base+instr(l_hex,upper(substr(p_str,i,1)))-1;

endloop;

returnl_num;

endto_dec;

/

[Q]能不能介绍SYS_CONTEXT的详细用法[A]利用以下的查询，你就明白了

select

SYS_CONTEXT('USERENV','TERMINAL')terminal,

SYS_CONTEXT('USERENV','LANGUAGE')language,

SYS_CONTEXT('USERENV','SESSIONID')sessionid,

SYS_CONTEXT('USERENV','INSTANCE')instance,

SYS_CONTEXT('USERENV','ENTRYID')entryid,

SYS_CONTEXT('USERENV','ISDBA')isdba,

SYS_CONTEXT('USERENV','NLS_TERRITORY')nls_territory,

SYS_CONTEXT('USERENV','NLS_CURRENCY')nls_currency,

SYS_CONTEXT('USERENV','NLS_CALENDAR')nls_calendar,

SYS_CONTEXT('USERENV','NLS_DATE_FORMAT')nls_date_format,

SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE')nls_date_language,

SYS_CONTEXT('USERENV','NLS_SORT')nls_sort,

SYS_CONTEXT('USERENV','CURRENT_USER')current_user,

SYS_CONTEXT('USERENV','CURRENT_USERID')current_userid,

SYS_CONTEXT('USERENV','SESSION_USER')session_user,

SYS_CONTEXT('USERENV','SESSION_USERID')session_userid,

SYS_CONTEXT('USERENV','PROXY_USER')proxy_user,

SYS_CONTEXT('USERENV','PROXY_USERID')proxy_userid,

SYS_CONTEXT('USERENV','DB_DOMAIN')db_domain,

SYS_CONTEXT('USERENV','DB_NAME')db_name,

SYS_CONTEXT('USERENV','HOST')host,

SYS_CONTEXT('USERENV','OS_USER')os_user,

SYS_CONTEXT('USERENV','EXTERNAL_NAME')external_name,

SYS_CONTEXT('USERENV','IP_ADDRESS')ip_address,

SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')network_protocol,

SYS_CONTEXT('USERENV','BG_JOB_ID')bg_job_id,

SYS_CONTEXT('USERENV','FG_JOB_ID')fg_job_id,

SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE')authentication_type,

SYS_CONTEXT('USERENV','AUTHENTICATION_DATA')authentication_data

fromdual

[Q]怎么获得今天是星期几，还关于其它日期函数用法[A]可以用to_char来解决，如

selectto_char(to_date('2002-08-26','yyyy-mm-dd'),'day')fromdual;

在获取之前可以设置日期语言，如

ALTERSESSIONSETNLS_DATE_LANGUAGE='AMERICAN';

还可以在函数中指定

selectto_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE=American')fromdual;

其它更多用法，可以参考to_char与to_date函数

如获得完整的时间格式

selectto_char(sysdate,'yyyy-mm-ddhh24:mi:ss')from

dual;

随便介绍几个其它函数的用法：

本月的天数

SELECTto_char(last_day(SYSDATE),'dd')daysFROMdual

今年的天数

selectadd_months(trunc(sysdate,'year'),12)-trunc(sysdate,'year')fromdual

下个星期一的日期

SELECTNext_day(SYSDATE,'monday')FROMdual

[Q]随机抽取前N条记录的问题[A]8i以上版本

select*from(select*fromtablenameorderbysys_guid())whererownum<N;

select*from(select*fromtablenameorderbydbms_random.value)whererownum<N;

注：dbms_random包需要手工安装，位于$ORACLE_HOME/rdbms/admin/dbmsrand.sql

dbms_random.value(100,200)可以产生100到200范围的随机数[Q]抽取从N行到M行的记录，如从20行到30行的记录[A]select*from(selectrownumid,t.*fromtablewhere……

andrownum<=30)whereid>20;[Q]怎么样抽取重复记录[A]select*fromtablet1wherewheret1.rowid!=

(selectmax(rowid)fromtablet2

wheret1.id=t2.idand=)

或者

selectcount(*),t.col_a,t.col_bfromtablet

groupbycol_a,col_b

havingcount(*)>1

如果想删除重复记录，可以把第一个语句的select替换为delete[Q]怎么样设置自治事务DECLAREpragmaautonomous_transaction;BEGIN

……

commit|rollback;END;--说明：数据库事务是一种单元操作，要么是全部操作都成功，要么全部失败。在Oracle中，一个事务是从执行第一个数据管理语言(DML)语句开始，直到执行一个COMMIT语句，提交保存这个事务，或者执行一个ROLLBACK语句，放弃此次操作结束。事务的“要么全部完成，要么什么都没完成”的本性会使将错误信息记入数据库表中变得很困难，因为当事务失败重新运行时，用来编写日志条目的INSERT语句还未完成。针对这种困境，Oracle提供了一种便捷的方法，即自治事务。自治事务从当前事务开始，在其自身的语境中执行。它们能独立地被提交或重新运行，而不影响正在运行的事务。正因为这样，它们成了编写错误日志表格的理想形式。在事务中检测到错误时，您可以在错误日志表格中插入一行并提交它，然后在不丢失这次插入的情况下回滚主事务。因为自治事务是与主事务相分离的，所以它不能检测到被修改过的行的当前状态。这就好像在主事务提交之前，它们一直处于单独的会话里，对自治事务来说，它们是不可用的。然而，反过来情况就不同了:主事务能够检测到已经执行过的自治事务的结果。要创建一个自治事务，您必须在匿名块的最高层或者存储过程、函数、数据包或触发的定义部分中，使用PL/SQL中的PRAGMAAUTONOMOUS_TRANSACTION语句。在这样的模块或过程中执行的SQLServer语句都是自治的。触发无法包含COMMIT语句，除非有PRAGMAAUTONOMOUS_TRANSACTION标记。但是，只有触发中的语句才能被提交，主事务则不行。列表A展示了对一个简单但灵活的错误日志表格所作的CREATETABLE和CREATESEQUENCE语句。列表B是一个独立的存储过程，用于更新错误日志表格。该过程接受最多三个数字和三个文本变量，然后将它们和时间戳以及调用过程的用户一起储存在表格中。列表A：CREATETABLEerrorlog(errorlog_idNUMBER,logged_onTIMESTAMPDEFAULTSYSTIMESTAMP,logged_byVARCHAR2(30)DEFAULTUSER,num1NUMBER,num2NUMBER,num3NUMBER,text1VARCHAR2(1000),text2VARCHAR2(1000),text3VARCHAR2(1000));CREATESEQUENCEerrorlog_seqSTARTWITH1INCREMENTBY1;列表B：CREATEORREPLACEPROCEDURElog_error(n1INNUMBER:=NULL,t1INVARCHAR:=NULL,n2INNUMBER:=NULL,t2INVARCHAR:=NULL,n3INNUMBER:=NULL,t3INVARCHAR:=NULL)ISPRAGMAAUTONOMOUS_TRANSACTION;BEGININSERTINTOerrorlog(errorlog_id,num1,num2,num3,text1,text2,text3)VALUES(errorlog_seq.NEXTVAL,n1,n2,n3,t1,t2,t3);COMMIT;END;/要测试这个过程，就要更新(UPDATE)或删除(DELETE)表格中的某些行;这就引发了主事务。然后执行存储过程，将您选择的要记入日志的数据传递给它。最后，重新运行主事务，选择(SELECT)错误日志表格，您的日志条目仍会在那儿。本篇文章来源于：开发学院原文链接：/2009/0610/5948.php[Q]怎么样在过程中暂停指定时间[A]DBMS_LOCK包的sleep过程

如：dbms_lock.sleep(5);表示暂停5秒。[Q]怎么样快速计算事务的时间与日志量[A]可以采用类似如下的脚本

DECLARE

start_timeNUMBER;

end_timeNUMBER;

start_redo_sizeNUMBER;

end_redo_sizeNUMBER;

BEGIN

start_time:=dbms_utility.get_time;

SELECTVALUEINTOstart_redo_sizeFROMv$mystatm,v$statnames

WHEREm.STATISTIC#=s.STATISTIC#

ANDs.NAME='redosize';

--transactionstart

INSERTINTOt1

SELECT*FROMAll_Objects;

--otherdmlstatement

COMMIT;

end_time:=dbms_utility.get_time;

SELECTVALUEINTOend_redo_sizeFROMv$mystatm,v$statnames

WHEREm.STATISTIC#=s.STATISTIC#

ANDs.NAME='redosize';

dbms_output.put_line('EscapeTime:'||to_char(end_time-start_time)||'centiseconds');

dbms_output.put_line('RedoSize:'||to_char(end_redo_size-start_redo_size)||'bytes');

END;

[Q]怎样创建临时表createglobaltemporaryTABLEtablename(columnlist)

oncommitpreserverows;

--提交保留数据会话临时表

oncommitdeleterows;

--提交删除数据事务临时表

临时表是相对于会话的，别的会话看不到该会话的数据。CREATEGLOBALTEMPORARYTABLEflight_schedule(startdateDATE,enddateDATE,costNUMBER)ONCOMMITPRESERVEROWS;[Q]怎么样在PL/SQL中执行DDL语句[A]1、8i以下版本dbms_sql包

2、8i以上版本还可以用

executeimmediatesql;

dbms_utility.exec_ddl_statement('sql');[Q]怎么样获取IP地址[A]服务器(817以上)：utl_inaddr.get_host_address

客户端：sys_context('userenv','ip_address')[Q]怎么样加密存储过程wrapiname=a.sql

PL/SQLWrapper:Release.0-ProductiononTueNov2722:26:482001

Copyright(c)OracleCorporation1993,2000.

AllRightsReserved.

Processinga.sqltoa.plb

提示a.sql转换为a.plb，这就是加密了的脚本，执行a.plb即可生成加密了的存储过程[Q]怎么样在ORACLE中定时运行存储过程[A]可以利用dbms_job包来定时运行作业，如执行存储过程，一个简单的例子，提交一个作业：

VARIABLEjobnonumber;

BEGIN

DBMS_JOB.SUBMIT(:jobno,'ur_procedure;',SYSDATE,'SYSDATE+1');

commit;

END;

之后，就可以用以下语句查询已经提交的作业

select*fromuser_jobs;[Q]怎么样从数据库中获得毫秒[A]9i以上版本，有一个timestamp类型获得毫秒，如

SQL>selectto_char(systimestamp,'yyyy-mm-ddhh24:mi:ssxff')time1,

to_char(current_timestamp)time2fromdual;

TIME1

TIME2

2003-10-2410:48:45.656000

24-OCT-0356000AM+08:00

可以看到，毫秒在to_char中对应的是FF。

8i以上版本可以创建一个如下的java函数

SQL>createorreplaceandcompile

javasource

named"MyTimestamp"

as

importjava.lang.String;

importjava.sql.Timestamp;

publicclassMyTimestamp

{

publicstaticStringgetTimestamp()

{

return(newTimestamp(System.currentTimeMillis())).toString();

}

};

SQL>javacreated.

注：注意java的语法，注意大小写

SQL>createorreplacefunctionmy_timestampreturnvarchar2

aslanguagejava

name'MyTimestamp.getTimestamp()returnjava.lang.String';

/

SQL>functioncreated.

SQL>selectmy_timestamp,to_char(sysdate,'yyyy-mm-ddhh24:mi:ss')ORACLE_TIMEfromdual;

MY_TIMESTAMP

ORACLE_TIME

2003-03-1719:15:59.688

2003-03-1719:15:59

如果只想获得1/100秒(hsecs)，还可以利用dbms_utility.get_time

[Q]如果存在就更新，不存在就插入可以用一个语句实现吗[A]9i已经支持了，是Merge，但是只支持select子查询，

如果是单条数据记录，可以写作select……fromdual的子查询。

语法为：

MERGEINTOtable

USINGdata_source

ON(condition)

WHENMATCHEDTHENupdate_clause

WHENNOTMATCHEDTHENinsert_clause;

如

MERGEINTOcoursec

USING(SELECTcourse_name,period,

course_hours

FROMcourse_updates)cu

ON(c.course_name=cu.course_name

ANDc.period=cu.period)

WHENMATCHEDTHEN

UPDATE

SETc.course_hours=cu.course_hours

WHENNOTMATCHEDTHEN

INSERT(c.course_name,c.period,

c.course_hours)

VALUES(cu.course_name,cu.period,

cu.course_hours);

[Q]怎么实现左联，右联与外联[A]在9i以前可以这么写:

左联：

selecta.id,,b.addressfroma,b

wherea.id=b.id(+)

右联:

selecta.id,,b.addressfroma,b

wherea.id(+)=b.id

外联

SELECTa.id,,b.address

FROMa,b

WHEREa.id=b.id(+)

UNION

SELECTb.id,''name,b.address

FROMb

WHERENOTEXISTS(

SELECT*FROMa

WHEREa.id=b.id);

在9i以上，已经开始支持SQL99标准，所以，以上语句可以写成：

默认内部联结：

selecta.id,,b.address,c.subject

from(ainnerjoinbona.id=b.id)

innerjoincon=

whereother_clause

左联

selecta.id,,b.address

fromaleftouterjoinbona.id=b.id

whereother_clause

右联

selecta.id,,b.address

fromarightouterjoinbona.id=b.id

whereother_clause

外联

selecta.id,,b.address

fromafullouterjoinbona.id=b.id

whereother_clause

or

selecta.id,,b.address

fromafullouterjoinbusing(id)

whereother_clause

[Q]怎么实现一条记录根据条件多表插入[A]9i以上可以通过Insertall语句完成，仅仅是一个语句，如：

INSERTALL

WHEN(id=1)THEN

INTOtable_1(id,name)

values(id,name)

WHEN(id=2)THEN

INTOtable_2(id,name)

values(id,name)

ELSE

INTOtable_other(id,name)

values(id,name)

SELECTid,name

FROMa;

如果没有条件的话，则完成每个表的插入，如

INSERTALL

INTOtable_1(id,name)

values(id,name)

INTOtable_2(id,name)

values(id,name)

INTOtable_other(id,name)

values(id,name)

SELECTid,name

FROMa;

[Q]如何实现行列转换[A]1、固定列数的行列转换

如

student

subjectgrade

student1

语文80

student1

数学70

student1

英语60

student2

语文90

student2

数学80

student2英语100

……

转换为

语文

数学

英语

student1

80

70

60

student2

90

80

100

……

语句如下：

selectstudent,sum(decode(subject,'语文',grade,null))"语文",

sum(decode(subject,'数学',grade,null))"数学",

sum(decode(subject,'英语',grade,null))"英语"

fromtable

groupbystudent

2、不定列行列转换

如

c1

c2

1

我

1

是

1

谁

2

知

2

道

3

不

……

转换为

1

我是谁

2

知道

3

不

这一类型的转换必须借助于PL/SQL来完成，这里给一个例子

CREATEORREPLACEFUNCTIONget_c2(tmp_c1NUMBER)

RETURNVARCHAR2

IS

Col_c2VARCHAR2(4000);

BEGIN

FORcurIN(SELECTc2FROMtWHEREc1=tmp_c1)LOOP

Col_c2:=Col_c2||cur.c2;

ENDLOOP;

Col_c2:=rtrim(Col_c2,1);

RETURNCol_c2;

END;

/

SQL>selectdistinctc1,get_c2(c1)cc2fromtable;即可[Q]比较好的行列转换解决办法?数据格式一CARD_CODEQBAL001127001210001336001497002196002212002315002432数据格式二CARD_CODEQ1Q2Q3Q40012710369700296121532从格式一到格式二SELECTa.card_code,

SUM(decode(a.q,1,a.bal,0))q1,

SUM(decode(a.q,2,a.bal,0))q2,

SUM(decode(a.q,3,a.bal,0))q3,

SUM(decode(a.q,4,a.bal,0))q4

FROMt_change_lca

GROUPBYa.card_code

ORDERBY1;从格式二到格式一SELECTt.card_code,

t.rnq,

decode(t.rn,1,t.q1,2,t.q2,3,t.q3,4,t.q4)bal

FROM(SELECTa.*,b.rn

FROMt_change_cla,

(SELECTROWNUMrnFROMdualCONNECTBYROWNUM<=4)b)t

ORDERBY1,2;技巧：构造虚拟表把源数据的行数增加。根据行号选择显示不同的字段数据格式一CARD_CODEQ001quarter_1001quarter_2001quarter_3001quarter_4002quarter_1002quarter_2002quarter_3002quarter_4数据格式二CARD_CODEQ002quarter_1;quarter_2;quarter_3;quarter_4001quarter_1;quarter_2;quarter_3;quarter_4从格式一到格式二SELECTt1.card_code,substr(MAX(sys_connect_by_path(t1.q,';')),2)q

FROM(SELECTa.card_code,

a.q,

row_number()over(PARTITIONBYa.card_codeORDERBYa.q)rn

FROMt_change_lc_commaa)t1

STARTWITHt1.rn=1

CONNECTBYt1.card_code=PRIORt1.card_code

ANDt1.rn-1=PRIORt1.rn

GROUPBYt1.card_code;技巧：根据分组后的行号，构造一棵树（或者多棵）。把从根到叶子的值串接起来。注：sys_connect_by_path(字段名,’分割符’)中字段值中部不可以有，；之类的分割符，否则会报ORA-30004错误从格式二到格式一SELECTt.card_code,

substr(t.q,

instr(';'||t.q,';',1,rn),

instr(t.q||';',';',1,rn)-instr(';'||t.q,';',1,rn))q

FROM(SELECTa.card_code,a.q,b.rn

FROMt_change_cl_commaa,

(SELECTROWNUMrnFROMdualCONNECTBYROWNUM<=100)b

WHEREinstr(';'||a.q,';',1,rn)>0)t

ORDERBY1,2;技巧：构造虚拟表把源数据的行数增加。根据行号选择显示字段的不同部分[Q]一条Sql实现任意的行转列拼接（不是decode）表结构和数据如下（表名Test）：NOVALUENAME1a测试11b测试21c测试31d测试42e测试54f测试64g测试7Sql语句：selectNo,ltrim(max(sys_connect_by_path(Value,';')),';')asValue,ltrim(max(sys_connect_by_path(Name,';')),';')asNamefrom(selectNo,Value,Name,rnFirst,lead(rnFirst)over(partitionbyNoorderbyrnFirst)rnNextfrom(selecta.No,a.Value,a.Name,row_number()over(orderbya.No,a.Valuedesc)rnFirstfromTesta)tmpTable1)tmpTable2startwithrnNextisnullconnectbyrnNext=priorrnFirstgroupbyNo;检索结果如下：NOVALUENAME1a;b;c;d测试1;测试2;测试3;测试42e测试54f;g测试6;测试7简单解释一下那个Sql吧：1、最内层的Sql（即表tmpTable1），按No和Value排序，并列出行号：selecta.No,a.Value,a.Name,row_number()over(orderbya.No,a.Valuedesc)rnFirstfromTesta该语句结果如下：NOVALUENAMERNFIRST1d测试411c测试321b测试231a测试142e测试554g测试764f测试672、外层的Sql（即表tmpTable2），根据No分区，取出当前行对应的下一条记录的行号字段：selectNo,Value,Name,rnFirst,lead(rnFirst)over(partitionbyNoorderbyrnFirst)rnNextfrom(这里是tmpTable1的SQL)tmpTable1lead(rnFirst)：取得下一行记录的rnFirst字段over(partitionbyNoorderbyrnFirst)按rnFirst排序，并按No分区，分区就是如果下一行的No字段与当前行的No字段不相等时，不取下一行记录显示该语句结果如下：NOVALUENAMERNFIRSTRNNEXT1d测试4121c测试3231b测试2341a测试14NULL2e测试55NULL4g测试7674f测试67NULL3、最后就是最外层的sys_connect_by_path函数与start递归了sys_connect_by_path(Value,';')startwithrnNextisnullconnectbyrnNext=priorrnFirst这个大概意思就是从rnNext为null的那条记录开始，递归查找，如果前一记录的rnFirst字段等于当前记录的rnNext字段，就把2条记录的Value用分号连接起来，大家可以先试试下面这个没有Max和Group的Sql：selectNo,sys_connect_by_path(Value,';')asValue,sys_connect_by_path(Name,';')asNamefrom(selectNo,Value,Name,rnFirst,lead(rnFirst)over(partitionbyNoorderbyrnFirst)rnNextfrom(selecta.No,a.Value,a.Name,row_number()over(orderbya.No,a.Valuedesc)rnFirstfromTesta)tmpTable1)tmpTable2startwithrnNextisnullconnectbyrnNext=priorrnFirst结果是：NOVALUENAME1;a;测试11;a;b;测试1;测试21;a;b;c;测试1;测试2;测试31;a;b;c;d;测试1;测试2;测试3;测试42;e;测试54;f;测试64;f;g;测试6;测试7可以看到，每个No的最后一条记录就是我们要的了所以在sys_connect_by_path外面套一个Max，再加个GroupbyNo，得到的结果就是行转列的结果了最后再加一个Ltrim，去掉最前面的那个分号，完成。[Q]怎么样实现分组取前N条记录[A]8i以上版本，利用分析函数

如获取每个部门薪水前三名的员工或每个班成绩前三名的学生。

Select*from

(selectdepno,ename,sal,row_number()over(partitionbydepno

orderbysaldesc)rn

fromemp)

wherern<=3[Q]怎么样把相邻记录合并到一条记录[A]8i以上版本，分析函数lag与lead可以提取后一条或前一天记录到本记录。

Selectdeptno,ename,hiredate,lag(hiredate,1,null)over

(partitionbydeptnoorderbyhiredate,ename)last_hire

fromemp

orderbydepno,hiredate[Q]如何取得一列中第N大的值？[A]select*from

(selectt.*,dense_rank()over(orderbyt2desc)rankfromt)

whererank=[$N]

[Q]怎么样把查询内容输出到文本[A]用spool如

如sqlplus–s

"/assysdba"<<EOF

setheadingoff

setfeedbackoff

spooltemp.txt

select*fromtab;

dbms_output.put_line(‘test’);

spooloff

exit

EOF[Q]如何在SQL*PLUS环境中执行OS命令？[A]比如进入了SQLPLUS，启动了数据库，忽然想起监听还没有启动，此时不用退出SQLPLUS，也不用另外起一个命令行窗口，直接输入：

SQL>hostlsntctlstart

或者unix/linux平台下

SQL>!<OScommand>

windows平台下

SQL>$<OScommand>

总结：HOST<OScommand>可以直接执行OS命令。

备注：cd命令无法正确执行。[Q]怎么设置存储过程的调用者权限[A]普通存储过程都是所有者权限，如果想设置调用者权限，请参考如下语句

createorreplace

procedure……()

AUTHIDCURRENT_USER

As

begin

……

end;[Q]SQL语句优化方法30例在SQL语句优化过程中，我们经常会用到hint,现总结一下在SQL优化过程中常见OracleHINT的用法：1./*+ALL_ROWS*/表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.例如:SELECT/*+ALL_ROWS*/EMP_NO,EMP_NAM,DAT_INFROMBSEMPMSWHEREEMP_NO='SCOTT';2./*+FIRST_ROWS*/表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.例如:SELECT/*+FIRST_ROWS*/EMP_NO,EMP_NAM,DAT_INFROMBSEMPMSWHEREEMP_NO='SCOTT';3./*+CHOOSE*/表明如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐量;表明如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法;例如:SELECT/*+CHOOSE*/EMP_NO,EMP_NAM,DAT_INFROMBSEMPMSWHEREEMP_NO='SCOTT';4./*+RULE*/表明对语句块选择基于规则的优化方法.例如:SELECT/*+RULE*/EMP_NO,EMP_NAM,DAT_INFROMBSEMPMSWHEREEMP_NO='SCOTT';5./*+FULL(TABLE)*/表明对表选择全局扫描的方法.例如:SELECT/*+FULL(A)*/EMP_NO,EMP_NAMFROMBSEMPMSAWHEREEMP_NO='SCOTT';6./*+ROWID(TABLE)*/提示明确表明对指定表根据ROWID进行访问.例如:SELECT/*+ROWID(BSEMPMS)*/*FROMBSEMPMSWHEREROWID>='AAAAAAAAAAAAAA'ANDEMP_NO='SCOTT';7./*+CLUSTER(TABLE)*/提示明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效.例如:SELECT/*+CLUSTER*/BSEMPMS.EMP_NO,DPT_NOFROMBSEMPMS,BSDPTMSWHEREDPT_NO='TEC304'ANDBSEMPMS.DPT_NO=BSDPTMS.DPT_NO;8./*+INDEX(TABLEINDEX_NAME)*/表明对表选择索引的扫描方法.例如:SELECT/*+INDEX(BSEMPMSSEX_INDEX)USESEX_INDEXBECAUSETHEREAREFEWMALEBSEMPMS*/FROMBSEMPMSWHERESEX='M';9./*+INDEX_ASC(TABLEINDEX_NAME)*/表明对表选择索引升序的扫描方法.例如:SELECT/*+INDEX_ASC(BSEMPMSPK_BSEMPMS)*/FROMBSEMPMSWHEREDPT_NO='SCOTT';10./*+INDEX_COMBINE*/为指定表选择位图访问路经,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的布尔组合方式.例如:SELECT/*+INDEX_COMBINE(BSEMPMSSAL_BMIHIREDATE_BMI)*/*FROMBSEMPMSWHERESAL<5000000ANDHIREDATE<SYSDATE;11./*+INDEX_JOIN(TABLEINDEX_NAME)*/提示明确命令优化器使用索引作为访问路径.例如:SELECT/*+INDEX_JOIN(BSEMPMSSAL_HMIHIREDATE_BMI)*/SAL,HIREDATEFROMBSEMPMSWHERESAL<60000;12./*+INDEX_DESC(TABLEINDEX_NAME)*/表明对表选择索引降序的扫描方法.例如:SELECT/*+INDEX_DESC(BSEMPMSPK_BSEMPMS)*/FROMBSEMPMSWHEREDPT_NO='SCOTT';13./*+INDEX_FFS(TABLEINDEX_NAME)*/对指定的表执行快速全索引扫描,而不是全表扫描的办法.例如:SELECT/*+INDEX_FFS(BSEMPMSIN_EMPNAM)*/*FROMBSEMPMSWHEREDPT_NO='TEC305';14./*+ADD_EQUALTABLEINDEX_NAM1,INDEX_NAM2,...*/提示明确进行执行规划的选择,将几个单列索引的扫描合起来.例如:SELECT/*+INDEX_FFS(BSEMPMSIN_DPTNO,IN_EMPNO,IN_SEX)*/*FROMBSEMPMSWHEREEMP_NO='SCOTT'ANDDPT_NO='TDC306';15./*+USE_CONCAT*/对查询中的WHERE后面的OR条件进行转换为UNIONALL的组合查询.例如:SELECT/*+USE_CONCAT*/*FROMBSEMPMSWHEREDPT_NO='TDC506'ANDSEX='M';16./*+NO_EXPAND*/对于WHERE后面的OR或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展.例如:SELECT/*+NO_EXPAND*/*FROMBSEMPMSWHEREDPT_NO='TDC506'ANDSEX='M';17./*+NOWRITE*/禁止对查询块的查询重写操作.18./*+REWRITE*/可以将视图作为参数.19./*+MERGE(TABLE)*/能够对视图的各个查询进行相应的合并.例如:SELECT/*+MERGE(V)*/A.EMP_NO,A.EMP_NAM,B.DPT_NOFROMBSEMPMSA(SELETDPT_NO,AVG(SAL)ASAVG_SALFROMBSEMPMSBGROUPBYDPT_NO)VWHEREA.DPT_NO=V.DPT_NOANDA.SAL>V.AVG_SAL;20./*+NO_MERGE(TABLE)*/对于有可合并的视图不再合并.例如:SELECT/*+NO_MERGE(V)*/A.EMP_NO,A.EMP_NAM,B.DPT_NOFROMBSEMPMSA(SELECTDPT_NO,AVG(SAL)ASAVG_SALFROMBSEMPMSBGROUPBYDPT_NO)VWHEREA.DPT_NO=V.DPT_NOANDA.SAL>V.AVG_SAL;21./*+ORDERED*/根据表出现在FROM中的顺序,ORDERED使ORACLE依此顺序对其连接.例如:SELECT/*+ORDERED*/A.COL1,B.COL2,C.COL3FROMTABLE1A,TABLE2B,TABLE3CWHEREA.COL1=B.COL1ANDB.COL1=C.COL1;22./*+USE_NL(TABLE)*/将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.例如:SELECT/*+ORDEREDUSE_NL(BSEMPMS)*/BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAMFROMBSEMPMS,BSDPTMSWHEREBSEMPMS.DPT_NO=BSDPTMS.DPT_NO;23./*+USE_MERGE(TABLE)*/将指定的表与其他行源通过合并排序连接方式连接起来.例如:SELECT/*+USE_MERGE(BSEMPMS,BSDPTMS)*/*FROMBSEMPMS,BSDPTMSWHEREBSEMPMS.DPT_NO=BSDPTMS.DPT_NO;24./*+USE_HASH(TABLE)*/将指定的表与其他行源通过哈希连接方式连接起来.例如:SELECT/*+USE_HASH(BSEMPMS,BSDPTMS)*/*FROMBSEMPMS,BSDPTMSWHEREBSEMPMS.DPT_NO=BSDPTMS.DPT_NO;25./*+DRIVING_SITE(TABLE)*/强制与ORACLE所选择的位置不同的表进行查询执行.例如:SELECT/*+DRIVING_SITE(DEPT)*/*FROMBSEMPMS,DEPT@BSDPTMSWHEREBSEMPMS.DPT_NO=DEPT.DPT_NO;26./*+LEADING(TABLE)*/将指定的表作为连接次序中的首表.27./*+CACHE(TABLE)*/当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端例如:SELECT/*+FULL(BSEMPMS)CAHE(BSEMPMS)*/EMP_NAMFROMBSEMPMS;28./*+NOCACHE(TABLE)*/当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端例如:SELECT/*+FULL(BSEMPMS)NOCAHE(BSEMPMS)*/EMP_NAMFROMBSEMPMS;29./*+APPEND*/直接插入到表的最后,可以提高速度.insert/*+append*/intotest1select*fromtest4;30./*+NOAPPEND*/通过在插入语句生存期内停止并行模式来启动常规插入.insert/*+noappend*/intotest1select*fromtest4;[Q]Oracle中使用SQLMODEL定义行间计算目的在本教程中，您将学习如何使用Oracle数据库10gSQLMODEL子句执行行间计算。所需时间大约30分钟主题本教程包括下列主题:概述情景前提条件设置示例数据查看示例语法使用位置和符号单元格引用在规则右侧使用多单元格引用使用CV()函数和ANY通配符CodingFORLoops:指定新单元格的简洁方法了解规则的评估顺序处理NULL度量和缺失单元格创建引用模型创建迭代模型使用排序规则总结概述利用SQLMODEL子句，您可以根据查询结果定义多维数组，然后将规则应用于该数组以计算新值。这些规则可以是复杂的相互依赖的计算。与外部解决方案相比，通过将高级计算集成到HYPERLINK"/price/soft_