[B]第一部分、SQL&PL/SQL[/B]
[Q]怎么樣查詢(xún)特殊字符，如通配符%與_
[A]select * from table where name like ‘A\_%‘ escape ‘\‘
[Q]如何插入單引號到數據庫表中
[A]可以用ASCII碼處理，其它特殊字符如&也一樣，如
insert into t values(‘i‘||chr(39)||‘m‘); -- chr(39)代表字符‘
或者用兩個(gè)單引號表示一個(gè)
or insert into t values(‘I‘‘m‘); -- 兩個(gè)‘‘可以表示一個(gè)‘
[Q]怎樣設置事務(wù)一致性
[A]set transaction [isolation level] read committed; 默認語(yǔ)句級一致性
set transaction [isolation level] serializable;
read only; 事務(wù)級一致性
[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]十進(jìn)制與十六進(jìn)制的轉換
[A]8i以上版本：
to_char(100,‘XX‘)
to_number(‘4D‘,‘XX‘)
8i以下的進(jì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 < 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]利用以下的查詢(xún)，你就明白了
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]怎么獲得今天是星期幾，還關(guān)于其它日期函數用法
[A]可以用to_char來(lái)解決，如
select to_char(to_date(‘2002-08-26‘,‘yyyy-mm-dd‘),‘day‘) from dual;
在獲取之前可以設置日期語(yǔ)言，如
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函數
如獲得完整的時(shí)間格式
select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss‘) from dual;
隨便介紹幾個(gè)其它函數的用法：
本月的天數
SELECT to_char(last_day(SYSDATE),‘dd‘) days FROM dual
今年的天數
select add_months(trunc(sysdate,‘year‘), 12) - trunc(sysdate,‘year‘) from dual
下個(gè)星期一的日期
SELECT Next_day(SYSDATE,‘monday‘) FROM dual
[Q]隨機抽取前N條記錄的問(wè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)可以產(chǎn)生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
如果想刪除重復記錄，可以把第一個(gè)語(yǔ)句的select替換為delete
[Q]怎么樣設置自治事務(wù)
[A]8i以上版本，不影響主事務(wù)
pragma autonomous_transaction;
……
commit|rollback;
[Q]怎么樣在過(guò)程中暫停指定時(shí)間
[A]DBMS_LOCK包的sleep過(guò)程
如：dbms_lock.sleep(5);表示暫停5秒。
[Q]怎么樣快速計算事務(wù)的時(shí)間與日志量
[A]可以采用類(lèi)似如下的腳本
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; --提交保留數據 會(huì )話(huà)臨時(shí)表
on commit delete rows; --提交刪除數據 事務(wù)臨時(shí)表
臨時(shí)表是相對于會(huì )話(huà)的，別的會(huì )話(huà)看不到該會(huì )話(huà)的數據。
[Q]怎么樣在PL/SQL中執行DDL語(yǔ)句
[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
客戶(hù)端：sys_context(‘userenv‘,‘ip_address‘)
[Q]怎么樣加密存儲過(guò)程
[A]用wrap命令，如（假定你的存儲過(guò)程保存為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即可生成加密了的存儲過(guò)程
[Q]怎么樣在ORACLE中定時(shí)運行存儲過(guò)程
[A]可以利用dbms_job包來(lái)定時(shí)運行作業(yè)，如執行存儲過(guò)程，一個(gè)簡(jiǎn)單的例子，提交一個(gè)作業(yè)：
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno, ‘ur_procedure;‘,SYSDATE,‘SYSDATE + 1‘);
commit;
END;
之后，就可以用以下語(yǔ)句查詢(xún)已經(jīng)提交的作業(yè)
select * from user_jobs;
[Q]怎么樣從數據庫中獲得毫秒
[A]9i以上版本，有一個(gè)timestamp類(lèi)型獲得毫秒，如
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以上版本可以創(chuàng )建一個(gè)如下的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的語(yǔ)法，注意大小寫(xiě)
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è)語(yǔ)句實(shí)現嗎
[A]9i已經(jīng)支持了，是Merge，但是只支持select子查詢(xún)，
如果是單條數據記錄，可以寫(xiě)作select …… from dual的子查詢(xún)。
語(yǔ)法為：
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í)現左聯(lián)，右聯(lián)與外聯(lián)
[A]在9i以前可以這么寫(xiě):
左聯(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)開(kāi)始支持SQL99標準，所以，以上語(yǔ)句可以寫(xiě)成：
默認內部聯(lián)結：
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í)現一條記錄根據條件多表插入
[A]9i以上可以通過(guò)Insert all語(yǔ)句完成，僅僅是一個(gè)語(yǔ)句，如：
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;
如果沒(méi)有條件的話(huà)，則完成每個(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í)現行列轉換
[A]1、固定列數的行列轉換
如
student subject grade
---------------------------
student1 語(yǔ)文 80
student1 數學(xué) 70
student1 英語(yǔ) 60
student2 語(yǔ)文 90
student2 數學(xué) 80
student2 英語(yǔ) 100
……
轉換為
語(yǔ)文 數學(xué) 英語(yǔ)
student1 80 70 60
student2 90 80 100
……
語(yǔ)句如下：
select student,sum(decode(subject,‘語(yǔ)文‘, grade,null)) "語(yǔ)文",
sum(decode(subject,‘數學(xué)‘, grade,null)) "數學(xué)",
sum(decode(subject,‘英語(yǔ)‘, grade,null)) "英語(yǔ)"
from table
group by student
2、不定列行列轉換
如
c1 c2
--------------
1 我
1 是
1 誰(shuí)
2 知
2 道
3 不
……
轉換為
1 我是誰(shuí)
2 知道
3 不
這一類(lèi)型的轉換必須借助于PL/SQL來(lái)完成，這里給一個(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í)現分組取前N條記錄
[A]8i以上版本，利用分析函數
如獲取每個(gè)部門(mén)薪水前三名的員工或每個(gè)班成績(jì)前三名的學(xué)生。
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]怎么樣把查詢(xún)內容輸出到文本
[A]用spool如
如sqlplus –s " / as sysdba" <<EOF
set heading off
set feedback off
spool temp.txt
select * from tab;
dbms_output.put_line(‘test’);
spool off
exit
EOF
[Q] 如何在SQL*PLUS環(huán)境中執行OS命令？
[A] 比如進(jìn)入了SQLPLUS，啟動(dòng)了數據庫，忽然想起監聽(tīng)還沒(méi)有啟動(dòng)，此時(shí)不用退出SQLPLUS，也不用另外起一個(gè)命令行窗口，直接輸入：
SQL> host lsntctl start
或者unix/linux平臺下
SQL>!<OS command>
windows平臺下
SQL>$<OS command>
總結：HOST <OS command>可以直接執行OS命令。
備注：cd命令無(wú)法正確執行。
[Q]怎么設置存儲過(guò)程的調用者權限
[A]普通存儲過(guò)程都是所有者權限，如果想設置調用者權限，請參考如下語(yǔ)句
create or replace
procedure ……()
AUTHID CURRENT_USER
As
begin
……
end; |
