Oracle常见问题QA第2版.docx
《Oracle常见问题QA第2版.docx》由会员分享,可在线阅读,更多相关《Oracle常见问题QA第2版.docx(122页珍藏版)》请在冰豆网上搜索。
![Oracle常见问题QA第2版.docx](https://file1.bdocx.com/fileroot1/2023-1/26/22678759-051e-40f3-9519-7859dac634c8/22678759-051e-40f3-9519-7859dac634c81.gif)
Oracle常见问题QA第2版
前言
本文档由piner发表。
您可以自由地将此文档复制发行,但请保留此声明页。
请勿将此文档用于任何商业用途。
第一部分、SQL&PL/SQL
[Q]怎么样查询特殊字符,如通配符%与_
[A]select*fromtablewherenamelike'A\_%'escape'\'
[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;
/
createorreplacefunctionto_hex(p_decinnumber)returnvarchar2
is
begin
returnto_base(p_dec,16);
endto_hex;
/
createorreplacefunctionto_bin(p_decinnumber)returnvarchar2
is
begin
returnto_base(p_dec,2);
endto_bin;
/
createorreplacefunctionto_oct(p_decinnumber)returnvarchar2
is
begin
returnto_base(p_dec,8);
endto_oct;
/
[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')fromdual;
随便介绍几个其它函数的用法:
本月的天数
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())whererownumselect*from(select*fromtablenameorderbydbms_random.value)whererownum注:
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.*fromtable)whereidbetweenNandM;
[Q]怎么样抽取重复记录
[A]select*fromtablet1wherewheret1.rowed!
=
(selectmax(rowed)fromtablet2
wheret1.id=t2.idandt1.name=t2.name)
或者
selectcount(*),t.col_a,t.col_bfromtablet
groupbycol_a,col_b
havingcount(*)>1
如果想删除重复记录,可以把第一个语句的select替换为delete
[Q]怎么样设置自治事务
[A]8i以上版本,不影响主事务
pragmaautonomous_transaction;
……
commit|rollback;
[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]怎样创建临时表
[A]8i以上版本
createglobaltemporarytablename(columnlist)
oncommitpreserverows;--提交保留数据会话临时表
oncommitdeleterows;--提交删除数据事务临时表
临时表是相对于会话的,别的会话看不到该会话的数据。
[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]怎么样加密存储过程
[A]用wrap命令,如(假定你的存储过程保存为a.sql)
wrapiname=a.sql
PL/SQLWrapper:
Release8.1.7.0.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;
TIME1TIME2
---------------------------------------------------------------------------------------------
2003-10-2410:
48:
45.65600024-OCT-0310.48.45.656000AM+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_TIMESTAMPORACLE_TIME
-------------------------------------------
2003-03-1719:
15:
59.6882003-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,a.name,b.addressfroma,b
wherea.id=b.id(+)
右联:
selecta.id,a.name,b.addressfroma,b
wherea.id(+)=b.id
外联
SELECTa.id,a.name,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,a.name,b.address,c.subject
from(ainnerjoinbona.id=b.id)
innerjoinconb.name=c.name
whereother_clause
左联
selecta.id,a.name,b.address
fromaleftouterjoinbona.id=b.id
whereother_clause
右联
selecta.id,a.name,b.address
fromarightouterjoinbona.id=b.id
whereother_clause
外联
selecta.id,a.name,b.address
fromafullouterjoinbona.id=b.id
whereother_clause
or
selecta.id,a.name,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、固定列数的行列转换
如
studentsubjectgrade
-------------------