ORACLE的幾個常見小問題的詳細解答
第一部分、SQL&PL/SQL
[Q]怎麼樣查詢特殊字符，如通配符%與_
[A]select * from table where name like 'A\_%' escape '\'
[Q]如何插入單引號到數據庫表中[A]可以用ASCII碼處理，其它特殊字符如&也一樣，如insert into t values('i'||chr(39)||'m'); -- chr(39)代表字符'
或者用兩個單引號表示一個or insert into t values('I''m');--兩個''可以表示一個'
[Q]怎樣設置事務一致性[A]set transaction [isolation level] read committed;默認語句級一致性set transaction [isolation level] serializable;
read only;事務級一致性[Q]怎麼樣利用游標更新數據[A]cursor c1 is
select * from tablename
where name is null for update [of column]
……
update tablename set column = ……
where current of c1;
[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以下的進制之間的轉換參考如下腳本create or replace function to_base( p_dec in number, p_base in number )
return varchar2
is
l_str varchar2(255) default NULL;
l_num number default p_dec;
l_hex varchar2(16) default '0123456789ABCDEF';
begin
if ( p_dec is null or p_base is null ) then
return null;
end if;
if ( trunc(p_dec) p_dec OR p_dec 20;
[Q]怎麼樣抽取重複記錄[A]select * from table t1 where where t1.rowed !=
(select max(rowed) from table t2
where t1.id=t2.id and t1.name=t2.name)
或者select count(*), t.col_a,t.col_b from table t
group by col_a,col_b
having count(*)>1
如果想刪除重複記錄，可以把第一個語句的select替換為delete
[Q]怎麼樣設置自治事務[A]8i以上版本，不影響主事務pragma autonomous_transaction;
……
commit|rollback;
[Q]怎麼樣在過程中暫停指定時間[A]DBMS_LOCK包的sleep過程如：dbms_lock.sleep(5);表示暫停5秒。
[Q]怎麼樣快速計算事務的時間與日誌量[A]可以採用類似如下的腳本DECLARE
start_time NUMBER;
end_time NUMBER;
start_redo_size NUMBER;
end_redo_size NUMBER;
BEGIN
start_time := dbms_utility.get_time;
SELECT VALUE INTO start_redo_size FROM v$mystat m,v$statname s
xyz資訊工坊
WHERE m.STATISTIC#=s.STATISTIC#
AND s.NAME='redo size';
--transaction start
INSERT INTO t1
SELECT * FROM All_Objects;
--other dml statement
COMMIT;
end_time := dbms_utility.get_time;
SELECT VALUE INTO end_redo_size FROM v$mystat m,v$statname s
WHERE m.STATISTIC#=s.STATISTIC#
AND s.NAME='redo size';
dbms_output.put_line('Escape Time:'||to_char(end_time-start_time)||' centiseconds');
dbms_output.put_line('Redo Size:'||to_char(end_redo_size-start_redo_size)||' bytes');
END;
[Q]怎樣創建臨時表[A]8i以上版本create global temporary tablename(column list)
on commit preserve rows;--提交保留數據會話臨時表on commit delete rows;--提交刪除數據事務臨時表臨時表是相對於會話的，別的會話看不到該會話的數據。
[Q]怎麼樣在PL/SQL中執行DDL語句[A]1、8i以下版本dbms_sql包2、8i以上版本還可以用execute immediate sql;
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）
wrap iname=a.sql
PL/SQL Wrapper: Release 8.1.7.0.0 - Production on Tue Nov 27 22:26:48 2001
Copyright (c) Oracle Corporation 1993, 2000.All Rights Reserved.
Processing a.sql to a.plb
提示a.sql轉換為a.plb，這就是加密了的腳本，執行a.plb即可生成加密了的存儲過程[Q]怎麼樣在ORACLE中定時運行存儲過程[A]可以利用dbms_job包來定時運行作業，如執行存儲過程，一個簡單的例子，提交一個作業：
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno, 'ur_procedure;',SYSDATE,'SYSDATE + 1');
commit;
END;
之後，就可以用以下語句查詢已經提交的作業select * from user_jobs;
[Q]怎麼樣從數據庫中獲得毫秒[A]9i以上版本，有一個timestamp類型獲得毫秒，如SQL>select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ssxff') time1,
xyz資訊工坊
to_char(current_timestamp) time2 from dual;
TIME1 TIME2
----------------------------- --------------------- -------------------------------------------
2003-10-24 10:48:45.65600024-OCT-03 10.48.45.656000 AM +08:00
可以看到，毫秒在to_char中對應的是FF。
8i以上版本可以創建一個如下的java函數SQL>create or replace and compile
java source
named "MyTimestamp"
as
import java.lang.String;
xyz
import java.sql.Timestamp;
public class MyTimestamp
{
public static String getTimestamp()
{
return(new Timestamp(System.currentTimeMillis())).toString();
}
};
SQL>java created.
注：注意java的語法，注意大小寫SQL>create or replace function my_timestamp return varchar2
as language java
name 'MyTimestamp.getTimestamp() return java.lang.String';
/
SQL>function created.
SQL>select my_timestamp,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') ORACLE_TIME from dual;
MY_TIMESTAMP ORACLE_TIME
------------------------ -------------------
2003-03-17 19:15:59.6882003-03-17 19:15:59
如果只想獲得1/100秒(hsecs)，還可以利用dbms_utility.get_time
[Q]如果存在就更新，不存在就插入可以用一個語句實現嗎[A]9i已經支持了，是Merge，但是只支持select子查詢，
如果是單條數據記錄，可以寫作select …… from dual的子查詢。
語法為：
MERGE INTO table
USING data_source
ON (condition)
WHEN MATCHED THEN update_clause
WHEN NOT MATCHED THEN insert_clause;
如MERGE INTO course c
USING (SELECT course_name, period,
course_hours
FROM course_updates) cu
ON (c.course_name = cu.course_name
AND c.period = cu.period)
WHEN MATCHED THEN
UPDATE
SET c.course_hours = cu.course_hours
WHEN NOT MATCHED THEN
INSERT (c.course_name, c.period,
c.course_hours)
VALUES (cu.course_name, cu.period,
cu.course_hours);
[Q]怎麼實現左聯，右聯與外聯[A]在9i以前可以這麼寫:
左聯：
select a.id,a.name,b.address from a,b
where a.id=b.id(+)
右聯:
select a.id,a.name,b.address from a,b
where a.id(+)=b.id
外聯SELECT a.id,a.name,b.address
FROM a,b
WHERE a.id = b.id(+)
UNION
SELECT b.id,'' name,b.address
FROM b
WHERE NOT EXISTS (
SELECT * FROM a
WHERE a.id = b.id);
在9i以上，已經開始支持SQL99標準，所以，以上語句可以寫成：
默認內部聯結：
select a.id,a.name,b.address,c.subject
from (a inner join b on a.id=b.id)
inner join c on b.name = c.name
where other_clause
左聯select a.id,a.name,b.address
from a left outer join b on a.id=b.id
where other_clause
右聯select a.id,a.name,b.address
from a right outer join b on a.id=b.id
where other_clause
外聯select a.id,a.name,b.address
from a full outer join b on a.id=b.id
where other_clause
or
select a.id,a.name,b.address
from a full outer join b using (id)
where other_clause
[Q]怎麼實現一條記錄根據條件多表插入[A]9i以上可以通過Insert all語句完成，僅僅是一個語句，如：
INSERT ALL
WHEN (id=1) THEN
INTO table_1 (id, name)
values(id,name)
WHEN (id=2) THEN
INTO table_2 (id, name)
values(id,name)
ELSE
INTO table_other (id, name)
values(id, name)
SELECT id,name
FROM a;
如果沒有條件的話，則完成每個表的插入，如INSERT ALL
INTO table_1 (id, name)
values(id,name)
INTO table_2 (id, name)
values(id,name)
INTO table_other (id, name)
values(id, name)
SELECT id,name
FROM a;
[Q]如何實現行列轉換[A]1、固定列數的行列轉換如studentsubject grade
---------------------------
student1語文80
student1數學70
student1英語60
student2語文90
student2數學80
student2英語100
……
轉換為語文數學英語student1 8070 60
student2 9080 100
……
語句如下：
select student,sum(decode(subject,'語文', grade,null)) "語文",
sum(decode(subject,'數學', grade,null)) "數學",
xyz軟體補給站
sum(decode(subject,'英語', grade,null)) "英語"
from table
group by student
2、不定列行列轉換如c1 c2
--------------
1我1是1誰2知2道3不……
轉換為1我是誰2知道3不這一類型的轉換必須藉助於PL/SQL來完成，這裡給一個例子CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)
RETURN VARCHAR2
IS
Col_c2 VARCHAR2(4000);
BEGIN
FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP
Col_c2 := Col_c2||cur.c2;
END LOOP;
Col_c2 := rtrim(Col_c2,1);
RETURN Col_c2;
END;
/
SQL> select distinct c1 ,get_c2(c1) cc2 from table;即可[Q]怎麼樣實現分組取前N條記錄[A]8i以上版本，利用分析函數如獲取每個部門薪水前三名的員工或每個班成績前三名的學生。
Select * from
(select depno,ename,sal,row_number() over (partition by depno
order by sal desc) rn
from emp)
where rnset heading off
set feedback off
spool temp.txt
select * from tab;
dbms_output.put_line(‘test’);
spool off
exit
EOF
[Q]如何在SQL*PLUS環境中執行OS命令？
[A]比如進入了SQLPLUS，啟動了數據庫，忽然想起監聽還沒有啟動，此時不用退出SQLPLUS，也不用另外起一個命令行窗口，直接輸入：
SQL> host lsntctl start
或者unix/linux平台下SQL>!
windows平台下SQL>$
總結：HOST可以直接執行OS命令。
備註：cd命令無法正確執行。
[Q]怎麼設置存儲過程的調用者權限[A]普通存儲過程都是所有者權限，如果想設置調用者權限，請參考如下語句create or replace
procedure ……()
AUTHID CURRENT_USER
As
begin
……
end;
