[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,truefalse); 其中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; …… commitrollback; [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
|