ORACLE的几个常见小问题的详细解答

　　
　　[B]第一部分、SQL&PL/SQL[/B]
　　[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) clearcase/" target="_blank" >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 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;
　　
　　

原文转自：http://www.ltesting.net