oracle学习

1、oracle 同义词

   SELECT * FROM SYS.ALL_SYNONYMS WHERE SYNONYM_NAME='S'.里面有同义词的表与用户

   修改同义词：create or replace
   创建同义词：   create public synonym table_name for user.table_name;    
   删除同义词：   drop public synonym table_name;           
   查看所有同义词：select * from dba_synonyms       

2、db_link

    create public database link xbzhpdb
                  connect to xbzh identified by xbzh
                  using '(DESCRIPTION =
                            (ADDRESS_LIST =
                              (ADDRESS = (PROTOCOL = TCP)(HOST = 10.30.110.54)(PORT = 1521))
                            )
                            (CONNECT_DATA =
                              (SERVICE_NAME = xbzhpdb)
                            )
                          )';

   select * from dba_db_links;  
   drop public database link XBZHPDB;
   drop [OWNER] database link XBZHPDB;

3、row_number()
    
    SELECT A.MYPMSDJJ,A.MYPFAC_CD,LTRIM( A.MYPPRO_CD，'0'）
    FROM
    (SELECT
       row_number() over ( PARTITION BY MYPFAC_CD, MYPPRO_CD ORDER BY CALDAY DESC ) rn,
       MYPMSDJJ,MYPFAC_CD,MYPPRO_CD
    FROM
       STA_PRICE_JJ  
    WHERE
       MYPFAC_CD IN ( SELECT REFINERY_LCBM FROM BASE_REFINERY @XBZHPDB WHERE REFINERY_LCLX = 0 ) 
       AND LTRIM( MYPPRO_CD, '0' ) IN ( '300668', '300644' ) ) A
    WHERE A.rn = 1

4、
   

Oracle不显示小数点前的0 
oracle to_char(小数) 默认如此 oracle就是这样的 


to_char用法本身也是需要格式化的，只不过我们平时对于普通字符都是直接to_char而已，对于时间，钱币等都是需要格式化的，对于小数不格式化就是没有前面的零的，可以这样格式化
select to_char(0.1,'fm9999990.9999') from dual;