ORACLE之常用FAQ:SQL&PL/SQL
第一部分、SQL&PL/SQL[@more@][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 < 0 ) then
raise PROGRAM_ERROR;
end if;
loop
l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
l_num := trunc( l_num/p_base );
exit when ( l_num = 0 );
end loop;
return l_str;
end to_base;
/
create or replace function to_dec
( p_str in varchar2,
p_from_base in number default 16 ) return number
is
l_num number default 0;
l_hex varchar2(16) default '0123456789ABCDEF';
begin
if ( p_str is null or p_from_base is null ) then
return null;
end if;
for i in 1 .. length(p_str) loop
l_num := l_num * p_from_base + instr(l_hex,upper(substr(p_str,i,1)))-1;
end loop;
return l_num;
end to_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
from dual
[Q]怎麼獲得今天是星期幾，還關於其它日期函式用法
[A]可以用to_char來解決，如
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;
在獲取之前可以設定日期語言，如
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
還可以在函式中指定
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
其它更多用法，可以參考to_char與to_date函式
如獲得完整的時間格式
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
隨便介紹幾個其它函式的用法：
本月的天數
SELECT to_char(last_day(SYSDATE),'dd') days FROM dual
今年的天數
select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual
下個星期一的日期
SELECT Next_day(SYSDATE,'monday') FROM dual
[Q]隨機抽取前N條記錄的問題
[A]8i以上版本
select * from (select * from tablename order by sys_guid()) where rownum < N;
select * from (select * from tablename order by dbms_random.value) where rownum< N;
注：dbms_random包需要手工安裝，位於$ORACLE_HOME/rdbms/admin/dbmsrand.sql
dbms_random.value(100,200)可以產生100到200範圍的隨機數
[Q]抽取從N行到M行的記錄，如從20行到30行的記錄
[A]select * from (select rownum id,t.* from table where ……
and rownum <= 30) where id > 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
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,
to_char(current_timestamp) time2 from dual;
TIME1 TIME2
----------------------------- ----------------------------------------------------------------
2003-10-24 10:48:45.656000 24-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;
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.688 2003-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、固定列數的行列轉換
如
student subject grade
---------------------------
student1 語文 80
student1 數學 70
student1 英語 60
student2 語文 90
student2 數學 80
student2 英語 100
……
轉換為
語文 數學 英語
student1 80 70 60
student2 90 80 100
……
語句如下：
select student,sum(decode(subject,'語文', grade,null)) "語文",
sum(decode(subject,'數學', grade,null)) "數學",
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 rn<=3
[Q]怎麼樣把相鄰記錄合併到一條記錄
[A]8i以上版本，分析函式lag與lead可以提取後一條或前一天記錄到本記錄。
Select deptno,ename,hiredate,lag(hiredate,1,null) over
(partition by deptno order by hiredate,ename) last_hire
from emp
order by depno,hiredate
[Q]如何取得一列中第N大的值？
[A]select * from
(select t.*,dense_rank() over (order by t2 desc) rank from t)
where rank = &N;
[Q]怎麼樣把查詢內容輸出到文字
[A]用spool如
如sqlplus –s " / as sysdba" <
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
備註：cd命令無法正確執行。
[Q]怎麼設定儲存過程的呼叫者許可權
[A]普通儲存過程都是所有者許可權，如果想設定呼叫者許可權，請參考如下語句
create or replace
procedure ……()
AUTHID CURRENT_USER
As
begin
……
end;
[Q]怎麼快速獲得使用者下每個表或表分割槽的記錄數
[A]可以分析該使用者，然後查詢user_tables字典，或者採用如下指令碼即可
SET SERVEROUTPUT ON SIZE 20000
DECLARE
miCount INTEGER;
BEGIN
FOR c_tab IN (SELECT table_name FROM user_tables) LOOP
EXECUTE IMMEDIATE 'select count(*) from "' || c_tab.table_name || '"' into miCount;
dbms_output.put_line(rpad(c_tab.table_name,30,'.') || lpad(miCount,10,'.'));
--if it is partition table
SELECT COUNT(*) INTO miCount FROM User_Part_Tables WHERE table_name = c_tab.table_name;
IF miCount >0 THEN
FOR c_part IN (SELECT partition_name FROM user_tab_partitions WHERE table_name = c_tab.table_name) LOOP
EXECUTE IMMEDIATE 'select count(*) from ' || c_tab.table_name || ' partition (' || c_part.partition_name || ')'
INTO miCount;
dbms_output.put_line(' '||rpad(c_part.partition_name,30,'.') || lpad(miCount, 10,'.'));
END LOOP;
END IF;
END LOOP;
END;
[A]怎麼在Oracle中發郵件
[Q]可以利用utl_smtp包發郵件，以下是一個傳送簡單郵件的例子程式
/****************************************************************************
parameter: Rcpter in varchar2 接收者郵箱
Mail_Content in Varchar2 郵件內容
desc: ·傳送郵件到指定郵箱
·只能指定一個郵箱，如果需要傳送到多個郵箱，需要另外的輔助程式
****************************************************************************/
CREATE OR REPLACE PROCEDURE sp_send_mail( rcpter IN VARCHAR2,
mail_content IN VARCHAR2)
IS
conn utl_smtp.connection;
--write title
PROCEDURE send_header(NAME IN VARCHAR2, HEADER IN VARCHAR2) AS
BEGIN
utl_smtp.write_data(conn, NAME||': '|| HEADER||utl_tcp.CRLF);
END;
BEGIN
--opne connect
conn := utl_smtp.open_connection('smtp.com');
utl_smtp.helo(conn, 'oracle');
utl_smtp.mail(conn, 'oracle info');
utl_smtp.rcpt(conn, Rcpter);
utl_smtp.open_data(conn);
--write title
send_header('From', 'Oracle Database');
send_header('To', '"Recipient" ');
send_header('Subject', 'DB Info');
--write mail content
utl_smtp.write_data(conn, utl_tcp.crlf || mail_content);
--close connect
utl_smtp.close_data(conn);
utl_smtp.quit(conn);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
utl_smtp.quit(conn);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
WHEN OTHERS THEN
NULL;
END sp_send_mail;
[A]怎麼樣在Oracle中寫作業系統檔案，如寫日誌
[Q]可以利用utl_file包，但是，在此之前，要注意設定好Utl_file_dir初始化引數
/**************************************************************************
parameter:textContext in varchar2 日誌內容
desc: ·寫日誌,把內容記到伺服器指定目錄下
·必須配置Utl_file_dir初始化引數，並保證日誌路徑與Utl_file_dir路徑一致或者是其中一個
****************************************************************************/
CREATE OR REPLACE PROCEDURE sp_Write_log(text_context VARCHAR2)
IS
file_handle utl_file.file_type;
Write_content VARCHAR2(1024);
Write_file_name VARCHAR2(50);
BEGIN
--open file
write_file_name := 'db_alert.log';
file_handle := utl_file.fopen('/u01/logs',write_file_name,'a');
write_content := to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss')||'||'||text_context;
--write file
IF utl_file.is_open(file_handle) THEN
utl_file.put_line(file_handle,write_content);
END IF;
--close file
utl_file.fclose(file_handle);
EXCEPTION
WHEN OTHERS THEN
BEGIN
IF utl_file.is_open(file_handle) THEN
utl_file.fclose(file_handle);
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END sp_Write_log;
[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 < 0 ) then
raise PROGRAM_ERROR;
end if;
loop
l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
l_num := trunc( l_num/p_base );
exit when ( l_num = 0 );
end loop;
return l_str;
end to_base;
/
create or replace function to_dec
( p_str in varchar2,
p_from_base in number default 16 ) return number
is
l_num number default 0;
l_hex varchar2(16) default '0123456789ABCDEF';
begin
if ( p_str is null or p_from_base is null ) then
return null;
end if;
for i in 1 .. length(p_str) loop
l_num := l_num * p_from_base + instr(l_hex,upper(substr(p_str,i,1)))-1;
end loop;
return l_num;
end to_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
from dual
[Q]怎麼獲得今天是星期幾，還關於其它日期函式用法
[A]可以用to_char來解決，如
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;
在獲取之前可以設定日期語言，如
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
還可以在函式中指定
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
其它更多用法，可以參考to_char與to_date函式
如獲得完整的時間格式
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
隨便介紹幾個其它函式的用法：
本月的天數
SELECT to_char(last_day(SYSDATE),'dd') days FROM dual
今年的天數
select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual
下個星期一的日期
SELECT Next_day(SYSDATE,'monday') FROM dual
[Q]隨機抽取前N條記錄的問題
[A]8i以上版本
select * from (select * from tablename order by sys_guid()) where rownum < N;
select * from (select * from tablename order by dbms_random.value) where rownum< N;
注：dbms_random包需要手工安裝，位於$ORACLE_HOME/rdbms/admin/dbmsrand.sql
dbms_random.value(100,200)可以產生100到200範圍的隨機數
[Q]抽取從N行到M行的記錄，如從20行到30行的記錄
[A]select * from (select rownum id,t.* from table where ……
and rownum <= 30) where id > 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
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,
to_char(current_timestamp) time2 from dual;
TIME1 TIME2
----------------------------- ----------------------------------------------------------------
2003-10-24 10:48:45.656000 24-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;
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.688 2003-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、固定列數的行列轉換
如
student subject grade
---------------------------
student1 語文 80
student1 數學 70
student1 英語 60
student2 語文 90
student2 數學 80
student2 英語 100
……
轉換為
語文 數學 英語
student1 80 70 60
student2 90 80 100
……
語句如下：
select student,sum(decode(subject,'語文', grade,null)) "語文",
sum(decode(subject,'數學', grade,null)) "數學",
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 rn<=3
[Q]怎麼樣把相鄰記錄合併到一條記錄
[A]8i以上版本，分析函式lag與lead可以提取後一條或前一天記錄到本記錄。
Select deptno,ename,hiredate,lag(hiredate,1,null) over
(partition by deptno order by hiredate,ename) last_hire
from emp
order by depno,hiredate
[Q]如何取得一列中第N大的值？
[A]select * from
(select t.*,dense_rank() over (order by t2 desc) rank from t)
where rank = &N;
[Q]怎麼樣把查詢內容輸出到文字
[A]用spool如
如sqlplus –s " / as sysdba" <
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
備註：cd命令無法正確執行。
[Q]怎麼設定儲存過程的呼叫者許可權
[A]普通儲存過程都是所有者許可權，如果想設定呼叫者許可權，請參考如下語句
create or replace
procedure ……()
AUTHID CURRENT_USER
As
begin
……
end;
[Q]怎麼快速獲得使用者下每個表或表分割槽的記錄數
[A]可以分析該使用者，然後查詢user_tables字典，或者採用如下指令碼即可
SET SERVEROUTPUT ON SIZE 20000
DECLARE
miCount INTEGER;
BEGIN
FOR c_tab IN (SELECT table_name FROM user_tables) LOOP
EXECUTE IMMEDIATE 'select count(*) from "' || c_tab.table_name || '"' into miCount;
dbms_output.put_line(rpad(c_tab.table_name,30,'.') || lpad(miCount,10,'.'));
--if it is partition table
SELECT COUNT(*) INTO miCount FROM User_Part_Tables WHERE table_name = c_tab.table_name;
IF miCount >0 THEN
FOR c_part IN (SELECT partition_name FROM user_tab_partitions WHERE table_name = c_tab.table_name) LOOP
EXECUTE IMMEDIATE 'select count(*) from ' || c_tab.table_name || ' partition (' || c_part.partition_name || ')'
INTO miCount;
dbms_output.put_line(' '||rpad(c_part.partition_name,30,'.') || lpad(miCount, 10,'.'));
END LOOP;
END IF;
END LOOP;
END;
[A]怎麼在Oracle中發郵件
[Q]可以利用utl_smtp包發郵件，以下是一個傳送簡單郵件的例子程式
/****************************************************************************
parameter: Rcpter in varchar2 接收者郵箱
Mail_Content in Varchar2 郵件內容
desc: ·傳送郵件到指定郵箱
·只能指定一個郵箱，如果需要傳送到多個郵箱，需要另外的輔助程式
****************************************************************************/
CREATE OR REPLACE PROCEDURE sp_send_mail( rcpter IN VARCHAR2,
mail_content IN VARCHAR2)
IS
conn utl_smtp.connection;
--write title
PROCEDURE send_header(NAME IN VARCHAR2, HEADER IN VARCHAR2) AS
BEGIN
utl_smtp.write_data(conn, NAME||': '|| HEADER||utl_tcp.CRLF);
END;
BEGIN
--opne connect
conn := utl_smtp.open_connection('smtp.com');
utl_smtp.helo(conn, 'oracle');
utl_smtp.mail(conn, 'oracle info');
utl_smtp.rcpt(conn, Rcpter);
utl_smtp.open_data(conn);
--write title
send_header('From', 'Oracle Database');
send_header('To', '"Recipient" ');
send_header('Subject', 'DB Info');
--write mail content
utl_smtp.write_data(conn, utl_tcp.crlf || mail_content);
--close connect
utl_smtp.close_data(conn);
utl_smtp.quit(conn);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
utl_smtp.quit(conn);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
WHEN OTHERS THEN
NULL;
END sp_send_mail;
[A]怎麼樣在Oracle中寫作業系統檔案，如寫日誌
[Q]可以利用utl_file包，但是，在此之前，要注意設定好Utl_file_dir初始化引數
/**************************************************************************
parameter:textContext in varchar2 日誌內容
desc: ·寫日誌,把內容記到伺服器指定目錄下
·必須配置Utl_file_dir初始化引數，並保證日誌路徑與Utl_file_dir路徑一致或者是其中一個
****************************************************************************/
CREATE OR REPLACE PROCEDURE sp_Write_log(text_context VARCHAR2)
IS
file_handle utl_file.file_type;
Write_content VARCHAR2(1024);
Write_file_name VARCHAR2(50);
BEGIN
--open file
write_file_name := 'db_alert.log';
file_handle := utl_file.fopen('/u01/logs',write_file_name,'a');
write_content := to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss')||'||'||text_context;
--write file
IF utl_file.is_open(file_handle) THEN
utl_file.put_line(file_handle,write_content);
END IF;
--close file
utl_file.fclose(file_handle);
EXCEPTION
WHEN OTHERS THEN
BEGIN
IF utl_file.is_open(file_handle) THEN
utl_file.fclose(file_handle);
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END sp_Write_log;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/72950/viewspace-911423/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ORACLE之常用FAQ:效能調整Oracle
- ORACLE之常用FAQ V1.0Oracle
- ORACLE之常用FAQ:ORACLE構架體系Oracle
- ORACLE之常用FAQ:ORACLE網路與安全Oracle
- SQL&PL/SQL （轉）SQL
- ORACLE之常用FAQ:備份與恢復Oracle
- itpub上的ORACLE之常用FAQ V1.0(轉)Oracle
- ORACLE之常用FAQ V1.0一(構架體系)(轉)Oracle
- 好書推薦—《精通Oracle Database 12c SQL&PL/SQL程式設計》OracleDatabaseSQL程式設計
- Oracle 常用SQLOracleSQL
- Oracle FaqOracle
- ORACLE 常用的SQLOracleSQL
- oracle常用SQL查詢OracleSQL
- oracle常用SQL語句OracleSQL
- Oracle常用監控SQLOracleSQL
- Oracle DBA常用sql分享OracleSQL
- oracle 維護常用SQLOracleSQL
- Oracle 常用SQL筆記OracleSQL筆記
- 《學習Oracle從這裡開始》之常用SQL篇OracleSQL
- Oracle並行FAQOracle並行
- Oracle Database Internals FAQOracleDatabase
- Oracle Backup and Recovery FAQOracle
- 【LOB】Oracle Lob管理常用sqlOracleSQL
- Oracle DG運維常用SQLOracle運維SQL
- 【BLOCK】Oracle 塊管理常用SQLBloCOracleSQL
- Oracle 常用SQL查詢列表OracleSQL
- 常用ORACLE查詢命令SQLOracleSQL
- Oracle-常用監控SQLOracleSQL
- Oracle常用sql語法集合OracleSQL
- Oracle常用監控SQL(轉）OracleSQL
- ORACLE常用SQL指令碼2OracleSQL指令碼
- discuz /faq.php SQL Injection VulPHPSQL
- FAQ寶典之RancherServerServer
- ORACLE DG 日常維護常用SQLOracleSQL
- Oracle維護常用SQL語句OracleSQL
- oracle常用經典sql查詢OracleSQL
- Oracle SQL效能優化常用方法OracleSQL優化
- Oracle資料庫網路與安全之FAQ彙集(轉)Oracle資料庫