第一部分、SQL&PL/SQL
[Q]怎么樣查詢特殊字符，如通配符%與_
[A]select * from table where name like 'A\_%' escape '\'
[Q]如何插入單引號(hào)到數(shù)據(jù)庫(kù)表中
[A]可以用ASCII碼處理，其它特殊字符如&也一樣，如
insert into t values('i'||chr(39)||'m'); -- chr(39)代表字符'
或者用兩個(gè)單引號(hào)表示一個(gè)
or insert into t values('I''m'); -- 兩個(gè)''可以表示一個(gè)'
[Q]怎樣設(shè)置事務(wù)一致性
[A]set transaction [isolation level] read committed; 默認(rèn)語句級(jí)一致性
set transaction [isolation level] serializable;
read only; 事務(wù)級(jí)一致性
[Q]怎么樣利用游標(biāo)更新數(shù)據(jù)
[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，錯(cuò)誤信息最大2048B
異常變量
SQLCODE 錯(cuò)誤代碼
SQLERRM 錯(cuò)誤信息
[Q]十進(jìn)制與十六進(jìn)制的轉(zhuǎn)換
[A]8i以上版本：
to_char(100,'XX')
to_number('4D','XX')
8i以下的進(jìn)制之間的轉(zhuǎn)換參考如下腳本
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]怎么樣抽取重復(fù)記錄
[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
如果想刪除重復(fù)記錄，可以把第一個(gè)語句的select替換為delete
[Q]怎么樣設(shè)置自治事務(wù)
[A]8i以上版本，不影響主事務(wù)
pragma autonomous_transaction;
……
commit|rollback;
[Q]怎么樣在過程中暫停指定時(shí)間
[A]DBMS_LOCK包的sleep過程
如：dbms_lock.sleep(5);表示暫停5秒。
[Q]怎么樣快速計(jì)算事務(wù)的時(shí)間與日志量
[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]怎樣創(chuàng)建臨時(shí)表
[A]8i以上版本
create global temporary tablename(column list)
on commit preserve rows; --提交保留數(shù)據(jù) 會(huì)話臨時(shí)表
on commit delete rows; --提交刪除數(shù)據(jù) 事務(wù)臨時(shí)表
臨時(shí)表是相對(duì)于會(huì)話的，別的會(huì)話看不到該會(huì)話的數(shù)據(jù)。
[Q]怎么樣在PL/SQL中執(zhí)行DDL語句
[A]1、8i以下版本dbms_sql包
2、8i以上版本還可以用
execute immediate sql;
dbms_utility.exec_ddl_statement('sql');
[Q]怎么樣獲取IP地址
[A]服務(wù)器(817以上)：utl_inaddr.get_host_address
客戶端：sys_context('userenv','ip_address')
[Q]怎么樣加密存儲(chǔ)過程
[A]用wrap命令，如（假定你的存儲(chǔ)過程保存為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轉(zhuǎn)換為a.plb，這就是加密了的腳本，執(zhí)行a.plb即可生成加密了的存儲(chǔ)過程
[Q]怎么樣在ORACLE中定時(shí)運(yùn)行存儲(chǔ)過程
[A]可以利用dbms_job包來定時(shí)運(yùn)行作業(yè)，如執(zhí)行存儲(chǔ)過程，一個(gè)簡(jiǎn)單的例子，提交一個(gè)作業(yè)：
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno, 'ur_procedure;',SYSDATE,'SYSDATE + 1');
commit;
END;
之后，就可以用以下語句查詢已經(jīng)提交的作業(yè)
select * from user_jobs;
[Q]怎么樣從數(shù)據(jù)庫(kù)中獲得毫秒
[A]9i以上版本，有一個(gè)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中對(duì)應(yīng)的是FF。
8i以上版本可以創(chuàng)建一個(gè)如下的java函數(shù)
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]如果存在就更新，不存在就插入可以用一個(gè)語句實(shí)現(xiàn)嗎
[A]9i已經(jīng)支持了，是Merge，但是只支持select子查詢，
如果是單條數(shù)據(jù)記錄，可以寫作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]怎么實(shí)現(xiàn)左聯(lián)，右聯(lián)與外聯(lián)
[A]在9i以前可以這么寫:
左聯(lián)：
select a.id,a.name,b.address from a,b
where a.id=b.id(+)
右聯(lián):
select a.id,a.name,b.address from a,b
where a.id(+)=b.id
外聯(lián)
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以上，已經(jīng)開始支持SQL99標(biāo)準(zhǔn)，所以，以上語句可以寫成：
默認(rèn)內(nèi)部聯(lián)結(jié)：
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
左聯(lián)
select a.id,a.name,b.address
from a left outer join b on a.id=b.id
where other_clause
右聯(lián)
select a.id,a.name,b.address
from a right outer join b on a.id=b.id
where other_clause
外聯(lián)
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]怎么實(shí)現(xiàn)一條記錄根據(jù)條件多表插入
[A]9i以上可以通過Insert all語句完成，僅僅是一個(gè)語句，如：
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;
如果沒有條件的話，則完成每個(gè)表的插入，如
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]如何實(shí)現(xiàn)行列轉(zhuǎn)換
[A]1、固定列數(shù)的行列轉(zhuǎn)換
如
student subject grade
---------------------------
student1 語文 80
student1 數(shù)學(xué) 70
student1 英語 60
student2 語文 90
student2 數(shù)學(xué) 80
student2 英語 100
……
轉(zhuǎn)換為
語文 數(shù)學(xué) 英語
student1 80 70 60
student2 90 80 100
……
語句如下：
select student,sum(decode(subject,'語文', grade,null)) "語文",
sum(decode(subject,'數(shù)學(xué)', grade,null)) "數(shù)學(xué)",
sum(decode(subject,'英語', grade,null)) "英語"
from table
group by student
2、不定列行列轉(zhuǎn)換
如
c1 c2
--------------
1 我
1 是
1 誰
2 知
2 道
3 不
……
轉(zhuǎn)換為
1 我是誰
2 知道
3 不
這一類型的轉(zhuǎn)換必須借助于PL/SQL來完成，這里給一個(gè)例子
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]怎么樣實(shí)現(xiàn)分組取前N條記錄
[A]8i以上版本，利用分析函數(shù)
如獲取每個(gè)部門薪水前三名的員工或每個(gè)班成績(jī)前三名的學(xué)生。
Select * from
(select depno,ename,sal,row_number() over (partition by depno
order by sal desc) rn
from emp)
where rn host lsntctl start
或者unix/linux平臺(tái)下
SQL>!
windows平臺(tái)下
SQL>$
總結(jié)：HOST 可以直接執(zhí)行OS命令。
備注：cd命令無法正確執(zhí)行。
[Q]怎么設(shè)置存儲(chǔ)過程的調(diào)用者權(quán)限
[A]普通存儲(chǔ)過程都是所有者權(quán)限，如果想設(shè)置調(diào)用者權(quán)限，請(qǐng)參考如下語句
create or replace
procedure ……()
AUTHID CURRENT_USER
As
begin
……
end;
[Q]怎么快速獲得用戶下每個(gè)表或表分區(qū)的記錄數(shù)
[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中發(fā)郵件
[Q]可以利用utl_smtp包發(fā)郵件，以下是一個(gè)發(fā)送簡(jiǎn)單郵件的例子程序
/****************************************************************************
parameter: Rcpter in varchar2 接收者郵箱
Mail_Content in Varchar2 郵件內(nèi)容
desc: ·發(fā)送郵件到指定郵箱
·只能指定一個(gè)郵箱，如果需要發(fā)送到多個(gè)郵箱，需要另外的輔助程序
****************************************************************************/
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中寫操作系統(tǒng)文件，如寫日志
[Q]可以利用utl_file包，但是，在此之前，要注意設(shè)置好Utl_file_dir初始化參數(shù)
/**************************************************************************
parameter:textContext in varchar2 日志內(nèi)容
desc: ·寫日志,把內(nèi)容記到服務(wù)器指定目錄下
·必須配置Utl_file_dir初始化參數(shù)，并保證日志路徑與Utl_file_dir路徑一致或者是其中一個(gè)
****************************************************************************/
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;