sql1

[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]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') 


[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