Oracle学习

========================
select sysdate-1 from dual;

SELECT SYSTIMESTAMP FROM DUAL;
2013-08-20 18:45:22

SELECT SYSTIMESTAMP AT TIME ZONE dbtimezone FROM DUAL; 
2013-08-20 18:44:49

SELECT SYSTIMESTAMP AT TIME ZONE COALESCE(null,DBTIMEZONE) FROM DUAL;
2013-08-20 18:49:57

SELECT COALESCE(null,DBTIMEZONE) FROM DUAL;
-06:00

 select COALESCE(null,'2013-08-20 00:00:00') from dual
 2013-08-20 00:00:00
 
SELECT TRUNC(SYSTIMESTAMP AT TIME ZONE COALESCE(null,DBTIMEZONE)) FROM DUAL;
2013-08-20 00:00:00

SELECT TO_DATE('031095','MMDDYY') ERRORR FROM DUAL
========================
USERENV(OPTION)
 
　　返回当前的会话信息.
 
　　OPTION='ISDBA'若当前是DBA角色,则为TRUE,否则FALSE.
 
　　OPTION='LANGUAGE'返回数据库的字符集.
 
　　OPTION='SESSIONID'为当前会话标识符.
 
　　OPTION='ENTRYID'返回可审计的会话标识符.
 
　　OPTION='LANG'返回会话语言名称的ISO简记.
 
　　OPTION='INSTANCE'返回当前的实例.
 
　　OPTION='terminal'返回当前计算机名
 
　　SELECT USERENV('LANGUAGE') FROM DUAL;
========================
返回系统会话信息sys_context
 
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 ;

========================
1.top n records

SELECT column FROM tableWHERE ROWNUM <= 10
========================
2. get table/view script
SELECT * FROM all_tables where rownum<100  and owner='OPERA' 

SELECT  table_name,column_name,data_type FROM user_tab_columns where  table_name in (select  table_name FROM all_tables where owner='OPERA'  ) 

查看view的script，直接copy binary出来就有文本
select dbms_metadata.get_ddl('VIEW', 'RESERVATION_GENERAL_VIEW') from dual;
或者
SELECT text FROM    user_views where rownum<100    


  select text from user_source where name='GET_SPRCIAL_REQUESTS'
  
  SELECT * FROM ALL_SOURCE  where name='GET_SPRCIAL_REQUESTS' and rownum<10
  
  select  *  from user_source where rownum<10 and type in ('PACKAGE','PACKAGE BODY') and name like '%reservation_ref%'

    select text from user_source where name='GET_SPRCIAL_REQUESTS'
  
  SELECT * FROM ALL_SOURCE  where name='reservation_ref' and rownum<10
  select * from dba_source where rownum<10 and  name='reservation_ref'
  
  select dbms_metadata.get_ddl('PROCEDURE','get_special_requests','OPERA') from dual;
  
  
  查看包名
  select * from all_source where  name='RESERVATION_REF' ;--and  rownum<10 ;

select * from user_source where name='RESERVATION_REF' ;-- and  rownum<10 ;

PACKAGE   包里面的接口
PACKAGE BODY   包代码

包里面的代码是用wrap加密的，必须下载个软件解密才能看到内容

DfUnWraper
========================
3.判断
DECODE的语法：DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，
========================
4.COALESCE函数用于返回其参数中第一个非空表达式。
COALESCE(expression [ ,...n ])
========================
5.NVL
NVL(expression1, expression2)
========================
6.
oracle sign 函数 　　取数字n的符号,大于0返回1,小于0返回-1,等于0返回0

　　SQL> select sign(100),sign(-100),sign(0) from dual;

========================
7.当前schema
select SYS_CONTEXT('USERENV','CURRENT_SCHEMA') CURRENT_SCHEMA from dual;

SELECT * FROM all_users ORDER BY username; 
select * from dba_objects where rownum<10  ; 


========================
8.列转行
错误:
with temp as(
  select 500 population, 'China' nation ,'Guangzhou' city from dual union all
  select 1500 population, 'China' nation ,'Shanghai' city from dual union all
  select 500 population, 'China' nation ,'Beijing' city from dual union all
  select 1000 population, 'USA' nation ,'New York' city from dual union all
  select 500 population, 'USA' nation ,'Bostom' city from dual union all
  select 500 population, 'Japan' nation ,'Tokyo' city from dual 
)
select population,
       nation,
       city,
       listagg(city,',') within GROUP (order by city) over (partition by nation) 
from temp

正确:
with temp as(
  select 500 population, 'China' nation ,'Guangzhou' city from dual union all
  select 1500 population, 'China' nation ,'Shanghai' city from dual union all
  select 500 population, 'China' nation ,'Beijing' city from dual union all
  select 1000 population, 'USA' nation ,'New York' city from dual union all
  select 500 population, 'USA' nation ,'Bostom' city from dual union all
  select 500 population, 'Japan' nation ,'Tokyo' city from dual 
)
select nation,wmsys.wm_concat(city) from
(select nation,city from temp order by city)
group by nation;


========================
9.日期比较
???当field是datetime类型时必须trunc才能比较

  and ( '' is null or rn.UPDATE_DATE <=    to_date('','yyyy-mm-dd') )
  and  ( '' is null or trunc(rn.begin_date)  =   to_date('','yyyy-mm-dd'))

========================
10.截掉时间日期后面的时间，截掉数字后面的小数
trunc(rn.begin_date)

select trunc(1.787) from dual;
========================
11.日期转化
to_date('2014-02-04','yyyy-mm-dd')
========================
12.判断空字符串
错误写法：
''=''
正确写法：
'' is null
========================
13.查看执行的SQL

SELECT * FROM v$sqlarea order by first_load_time desc;

SELECT * FROM v$sql order by first_load_time desc;
========================
14.function

FUNCTION GET_PRODUCTS(IN_TEST IN VARCHAR2
) RETURN VARCHAR2 IS

...
RETURN RETS;
========================



========================
  SELECT * FROM user_views where view_name='GENERAL_VIEW'
   SELECT * FROM all_views
========================
begin
  ...
end;


========================