oracle速查

*唯一性
  distinct
 
*字符函数
  str1 || str2         连接字符串
  ASCII(str)        返回串中第一个字符的ASCII码
  CHR(n)        返回ASCII值为n的字符
  INITCAP(str)        串首字母大写
  INSTR(str,set[,start[,occurrence]]) set在str中从start位置开始第occurrence次出现的位置,start<0 表示倒过来查
  LENGTH(str)        串长度
  LOWER(str)        整串小写
  UPPER(str)        整串大写
  LPAD(str,length[,'set'])串左边加上指定set串，使str长度达到length
  RPAD(str,length[,'set'])串右边加上指定set串，使str长度达到length
  LTRIM(str[,'set']    串左边去掉若干字符，使str首字符不是set中任何一个
  RTRIM(str[,'set']    串右边去掉若干字符，使str首字符不是set中任何一个
  SOUNDEX(str)        串转换成拼写不同但发音相同的串
  SUBSTR(str,start[,count])从str的start开始，截取长度为count的子串
  TRANSLATE(str,str1,str2)把str中的str1替换为str2串
 
update tablea set resource_name=substr(resource_id,instr(resource_id,'.',-1)+1,100)
 
*Decode函数
  select last_name,first_name,fk_department,
         decode(fk_department,                       --需转换的字段
                'INT','INTERIOR',                    -- INT 转换为 INTERIOR
                'POL','POLITICAL_SCIENCE',           -- PLO 转换为 POLITICAL_SCIENCE
                'WEL','WELFARE',                     -- WEL 转换为 WELFARE
                'UNKNOWN'                            -- 其它的转换成 UNKNOW
                )
  from employee
  order 3;
 
 
*数值函数
  val1 + val2            求两数的和
  val1 - val2            求两数的差
  val1 * val2            求两数的积
  val1 / val2            求两数的商
  ABS(val)            绝对值
  CEIL(val)            不小于val的最小整数
  COS(val)            余弦
  COSH(val)            双去余弦
  EXP(val)            自然指数
  FLOOR(val)            不大于val的最大整数
  LN(val)            自然对数
  LOG(val)            常用对数
  MOD(val,divisor)        val除以divisor的余数
  NVL(val,substitute)        将数值为null的value字段值用substitute代替
  POWER(val,exponent)        求val的exponent次指数幂
  ROUND(val,precision)        四舍五入到precision指定的精度
  SIGN(val)            符号函数 >0  +   ；  <0  -
  SIN(val)            正弦
  SINH(val)            双曲正弦
  SQRT(val)            平方根
  TAN(val)            正切
  TANH(val)            双曲正切
  TRUNC(val,precision)        将val截取到precision指定的精度,可用于日期、时间，如TRUNC(SYSDATE, 'MM'),返回该月第一天
 
 
*日期函数
  ADD_MONTHS(date,count)    对date增加count个月
  GREATEST(date1,date2,...)    从一串日期中找出最大的日期
  LEAST(date1,date2,...)    从一串日期中找出最小的日期
  LAST_DAY(day)                返回day所在月的最后一天
  MONTHS_BETWEEN(date2,date1)    两个日期之间相隔的月数[ (date2-date1)/31.0带小数 ]
  NEXT_DAY(date,'day')        date所在星期中星期day的日期
  TO_CHAR(date,'Format')    根据Format转化date为string
  TO_DATE(string,'Format')    将string按Format格式转化成日期
 
 
*日期描述符
  D        用数字表示的星期几
  DAY        星期几的完整表示（如：MONDAY)
  Day        与DAY相同，但仅是首字母为大写(Monday)
  day        与DAY相同，当全部是小写(monday)
  DY        用三个字符表示的星期缩写(FRI)
  Dy        与DY相同，但仅首字母大写
  dy        与DY相同，但全部为小写
  DD        一月中的几号
  DDD        一年中的第几天
  J        Julian日期中的年
  WW        一年中的星期数
  W        一月中的星期数
  IW        ISO标准中表示的星期数
  MM        月数
  MON        用三个字母表示的月份缩写(APR)
  Mon        与MON相同，但仅首字母大写
  mon        与MON相同，但全部为小写
  MONTH        完整的月份表示(APRIL)
  Month        与MONTH相同，但仅首字母大写(April)
  month        与MONTH相同，当全部为小写(april)
  RM        罗马数字表示的月(XII)
  I        ISO年的最后一位数字
  IY            ISO年的最后二位数字
  IYY        ISO年的最后三位数字
  IYYY        ISO四位数字表示的年
  RR        年份的最后二位数字
  SYYYY        带符号的年份(公元前1500年= -1500)
  Y        年份中得最后一个数字
  YY            年份中得最后二个数字
  YYY        年份中得最后三个数字
  YYYY        四个数字表示的年
  YEAR        年份的拼写（1997 NINETEEN-NINETY-SEVEN）
  Year        首字母大写的年份拼写
  year        全部小写的年份拼写
  Q        数字表示的季节(1,2,3,4)
  CC/SCC    世纪
  BC/AD        显示公元前/后
  B.C./A.D.    显示公元前/后
  bc/ad        显示公元前/后
  b.c/a.d.    显示公元前/后
  AM/PM        显示午前/后
  A.M./P.M.    显示午前/后
  am/pm        显示午前/后
  HH        小时
  HH12        小时
  HH24        小时
  MI        分钟
  SS        秒
  SSSS       自午夜以来的秒数  ？？
 
*聚组函数(NULL值的记录不参加计算)
  avg(val)    平均值
  count(val)    计数
  max(val)    最大值
  min(val)    最小值
  stddev(val)    均差
  sum(val)    合计
  variance(val)    方差
 
*伪字段
  null        NULL值
  sysdate    当前系统日期
  rownum      表示提取顺序的序列号
 
*系统中有用的表
  dual表:当一个值必须被引用时使用,如 select sysdate from dual 返回系统当前时间
  tab 表:用户可以存取的所有表
 
 
*外连接
  select a.*, b.* from table1 a,table2 b where a.num=b.id(+)
    这样，a.num没有b.id对应时也会入选（b.*被置为NULL）
 
*集合操作
  缺省union会消掉重复的记录，如果要全部的记录，使用union all
  minus
 
*LOB操作
   Function/Procedure      Description
   ---------------------------------------------
   APPEND()        Appends the LOB value to another LOB
   COPY()          Copies all or part of a LOB to another LOB
   ERASE()         Erases part of a LOB, starting at a specified offset
   LOADFROMFILE()     Load BFILE data into an internal LOB
   TRIM()         Trims the LOB value to the specified shorter length
   WRITE()         Writes data to the LOB at a specified offset
   WRITEAPPEND()     Writes data to the end of the LOB
 
 
 
   PL/SQL: DBMS_LOB Procedures To Modify BLOB, CLOB, and NCLOB Values
   Function/Procedure      Description
   ---------------------------------------
   APPEND()         Appends the LOB value to another LOB
   COPY()         Copies all or part of a LOB to another LOB
   ERASE()         Erases part of a LOB, starting at a specified offset
   LOADFROMFILE()     Load BFILE data into an internal LOB
   TRIM()         Trims the LOB value to the specified shorter length
   WRITE()         Writes data to the LOB at a specified offset
   WRITEAPPEND()     Writes data to the end of the LOB
 
   PL/SQL: DBMS_LOB Procedures To Read or Examine Internal and External LOB values
   Function/Procedure      Description
   ---------------------------------------
   COMPARE()         Compares the value of two LOBs
   GETCHUNKSIZE()     Gets the chunk size used when reading and writing. This only works on internal LOBs and does not apply to external LOBs (BFILEs).
   GETLENGTH()         Gets the length of the LOB value
   INSTR()         Returns the matching position of the nth occurrence of the pattern in the LOB
   READ()         Reads data from the LOB starting at the specified offset
   SUBSTR()         Returns part of the LOB value starting at the specified offset
 
   PL/SQL:  DBMS_LOB Procedures To Operate on Temporary LOBs
   Function/Procedure      Description
   ---------------------------------------
   CREATETEMPORARY()     Creates a temporary LOB
   ISTEMPORARY()     Checks if a LOB locator refers to a temporary LOB
   FREETEMPORARY()     Frees a temporary LOB
 
   PL/SQL: DBMS_LOB Read-Only Procedures for BFILEs
   Function/Procedure      Description
   ---------------------------------------
   FILECLOSE()         Closes the file[1]
   FILECLOSEALL()     Closes all previously opened files
   FILEEXISTS()     Checks if the file exists on the server
   FILEGETNAME()      Gets the directory alias and file name
   FILEISOPEN()     Checks if the file was opened using the input BFILE locators[2]
   FILEOPEN()         Opens a file[3]
   [1] Use CLOSE() instead.
   [2] Use ISOPEN() instead.
   [3] Use OPEN() instead.
 
 
   PL/SQL: DBMS_LOB Procedures To Open and Close Internal And External LOBs
   Function/Procedure      Description
   ---------------------------------------
   OPEN()          Opens a LOB
   ISOPEN()         Sees if a LOB is open
   CLOSE()          Closes a LOB
 
*查表空间大小
  select sum(bytes) from dba_segments where segment_name = upper('ali_zeus_csreason');
 
* NULLS FRIST  指定null值排序时在前面，缺省时null值排在后面
  select * from test order by name desc NULLS FRIST
 
 
*Merge使用例子(9i)：
MERGE INTO table1 A
    USING ( select area_id_1, count(*) as num
    from  table2
    where customer_id=0  and  owner_1 is null and (is_return is null or is_return='n') and is_deleted='n'
    group by area_id_1
    )  B
    ON (A.id = B.area_id_1 and A.id_type='M' and  A.field_type= 'newLeads' )
WHEN MATCHED THEN
    UPDATE SET value=B.num,  gmt_create=sysdate
WHEN NOT MATCHED THEN
    INSERT VALUES (B.area_id_1, 'M', sysdate, 'newLeads',  B.num );
  1.在MERGE语句中必须指定一个WHEN MATCHED和一个WHEN NOT MATHCED语句。如果除这两种情况之外还有别的情况，你可能就需要使用一个常规的INSERT或者UPDATE语句。
  2.另外一点是MERGE语句一次只能修改一行记录，而且不能修改在ON子句中引用的列。
  3.MERGE语句的目标表（target table）必须是一个可以使用INSERT语句进行插入或者UPDATE语句进行更新的表或者视图。源表（source table）可以是任何的查询表，比如说外部表或者管道化表函数。
  
*Partition by 用法：（找出ID重复的纪录）
SELECT ROWID, site, ID, gmt_create,
       RANK () OVER (PARTITION BY ID ORDER BY level_1, ROWID) levle_2
  FROM (SELECT ROWID, site, ID, gmt_create,
               RANK () OVER (PARTITION BY ID ORDER BY gmt_create DESC)
                                                                      level_1
          FROM cs_show_to@us.world
         WHERE ID IN (SELECT   ID
                          FROM cs_show_to@us.world
                      GROUP BY ID
                        HAVING COUNT (*) > 1))  
 
 
 
*改列名(9i)
  ALTER TABLE table_name RENAME COLUMN tiitle_old TO title_new;
 
*改表结构(9i)
  DBMS_REDEFINITION的PL/SQL工具包可以使一个DBA改变表格的列结构。这是一个复杂的过程，但通常情况下遵循以下的步骤即可实现：
    1. 使用DBMS_REDEFINITION.CAN_REDEF_TABLE来检查表格满足在线的重新定义，并指定重新定义是否遵循主要的键或者遵循行Ids。
    2. 在相同规划中建立一个空表格，但结构是一样的。省略你想要放弃的列，包含你想要建立的新的列。
    3. 使用DBMS_REDEFINITION.START_REDEF_TABLE来开始重新定义过程。这一过程的参数说明了旧的表格，新的表格，以及如何将现成的列映射到新表格的列。
    4. 建立任一约束，索引。
    5. 使用DBMS_REDEFINITION.FINISH_REDEF_TABLE来完成这一过程。当重新定义在两个表格之间交换时，无论原来表格的大小，都必须将表格锁定为一小段时间。
    6. 放弃在重新定义中使用的临时表格，而不再使用它。
  重新定义一个表格不会自动地更新任何可以访问表格的程序代码。程序必须分开地更新和调试。然而，DBMS_REDEFINITION does所可以做的就是缩短时间，这正是用户的需求。
 
 
*update
 update tablea  a
 set a.area_id_5=(
     select b.access_area_id
     from tableb b
     where a.area_id_5 in( 1184)
     and a.APPLICANT=b.id
     and a.is_deleted='n'
     and b.is_deleted='n'
     and b.status='enable'
     );
 
  UPDATE (<SELECT Statement>)
  SET ....;
 
*两表更新
UPDATE (SELECT a.area_id_1 aa, b.area_id_1 bb, a.owner_1 cc, b.owner_1 dd
          FROM tablea a, tableb b
         WHERE a.ID = b.ID and b.AREA_ID_1=51)
   SET aa = bb,
       cc = dd
 
 
*取指定数startRow开始的maxRowReturned条记录
  select * from
  (
    select rownum countrownum,t1.*  from table t1 where rownum < (startRow + maxRowReturned)
  )
  where countrownum >= startRow
 
*分配权限
grant select,insert,update,delete on tablea to ZZZ
 
*全局名
CREATE PUBLIC SYNONYM tablea FOR ZZZ.tablea
 
 
 
**安全与审计
  *安全级别：
   1)合法用户的帐户安全性
   2)数据库对象的访问安全性
   3)管理全局权限的系统级安全性
*用户
  create user <USERN_AME>
    identified by <PASSWORD|externally>
    [default tablespace <DEF_TAB_SPACE>]
    [temporary tablespace <TMP_TAB_SPACE>]
    [profile <PROFILE_NAME>]
    [password expire];
  alter user <USER_NAME>
    [default role < ROLE_NAME | NONE | all | all except ROLE_NAME >]
    [account < lock | unlock >]
    [password expire]
  drop user <USER_NAME> [cascade];
 
*角色
  create role <ROLE_NAME> [identified by <PASSWORD>];
  alter role <ROLE_NAME>
    [identified by <PASSWORD> | not identified | identified externally ];
  grant <PRIVILEGES> to <ROLE_NAME>;
  grant <ROLE_NAME> to <ROLE_NAME>;  -- 将角色授予另一个角色
  set role < ROLE_NAME | none >;    -- 多角色用户切换启用的角色
*分配空间
  alter user <USER_NAME>
    quota <SIZE> on <TAB_SPACE>;
*用户环境文件
  create profile PROFILE_NAME limit
    [SESSIONS_PER_USER         <value|UNLIMITED|DEFAULT>]
    [CONNECT_TIME              <value|UNLIMITED|DEFAULT>]
    [IDLE_TIME                 <value|UNLIMITED|DEFAULT>]
    [FAILED_LOGIN_ATTEMPTS     <value|UNLIMITED|DEFAULT>]
    [PASSWORD_LIFE_TIME        <value|UNLIMITED|DEFAULT>]
    [PASSWORD_RESUSE_TIME      <value|UNLIMITED|DEFAULT>]
    [PASSWORD_RESUSE_MAX       <value|UNLIMITED|DEFAULT>]
    [PASSWORD_LOCK_TIME        <value|UNLIMITED|DEFAULT>]
    [PASSWPRD_GRACE_TIME       <value|UNLIMITED|DEFAULT>]
    [PASSWORD_VERIFY_FUNCTION  < fun_name |NULL|DEFAULT>];
*数据库帐户和主机帐户相连
   设置数据库初始化参数OS_AUTHENT_PREFIX，缺省值为OPS$，建立数据库和操作系统帐户的关系：
       数据库帐户= OS_AUTHENT_PREFIX + 操作系统帐户
 
  create user <USERN_AME>
    identified by externally;
  alter role <ROLE_NAME>
    identified externally;
*分配对象操作权限
  grant <PRIVILEGES> on <OBJECT> to <USRE|ROLE> [with < admin | grant >option ];
  revoke <PRIVILEGES> on <OBJECT> from <USRE|ROLE>;
 
*与权限有关的数据字典视图
 DBA_ROLES        角色名及其口令状态
 DBA_ROLE_PRIVS   已被授予角色的用户
 DBA_SYS_PRIVS    已被授予系统权限的用户
 DBA_TAB_PRIVS    已被授予表中权限的用户
 DBA_COL_PRIVS    已被授予列中权限的用户
 ROLE_ROLE_PRIVS  已被授予其它角色的角色
 ROLE_SYS_PRIVS   已被授予角色的系统权限
 ROLE_TAB_PRIVS   已被授予角色的表权限
 
*远程查询
  create [public] database link <DB_LINK_NAME> connect to <USER_NAME> identified by <PWD> using 'REMOTE_HOST_ORA_NAME'
  drop database link <DB_LINK_NAME>
 
* 产生连续100条记录(更多的类似)
    SELECT a.id + b.id + 1 id
    FROM (           SELECT 0 id FROM DUAL
          UNION ALL  SELECT 1 id FROM DUAL
          UNION ALL  SELECT 2 id FROM DUAL
          UNION ALL  SELECT 3 id FROM DUAL
          UNION ALL  SELECT 4 id FROM DUAL
          UNION ALL  SELECT 5 id FROM DUAL
          UNION ALL  SELECT 6 id FROM DUAL
          UNION ALL  SELECT 7 id FROM DUAL
          UNION ALL  SELECT 8 id FROM DUAL
          UNION ALL  SELECT 9 id FROM DUAL
         ) a,
        (            SELECT 0 id FROM DUAL
          UNION ALL  SELECT 10 id FROM DUAL
          UNION ALL  SELECT 20 id FROM DUAL
          UNION ALL  SELECT 30 id FROM DUAL
          UNION ALL  SELECT 40 id FROM DUAL
          UNION ALL  SELECT 50 id FROM DUAL
          UNION ALL  SELECT 60 id FROM DUAL
          UNION ALL  SELECT 70 id FROM DUAL
          UNION ALL  SELECT 80 id FROM DUAL
          UNION ALL  SELECT 90 id FROM DUAL
         ) b
    ORDER BY id
 
  
*联表多记录多字段更新：
         UPDATE rptt_revisit_detail_tmp a
            SET (a.visit_id, a.visit_date, a.visitor, a.eligible,
                 a.date_available) =
                   (
                   SELECT b.id, b.visit_date, b.creator, b.eligible, 'n'
                      FROM zeus.ali_zeus_visit b
                     WHERE a.customer_id = b.customer_id
                       AND b.is_revisit = '1'
                       AND b.is_deleted = 'n'
                   )
          WHERE EXISTS ( SELECT 1
                           FROM zeus.ali_zeus_visit b
                          WHERE a.customer_id = b.customer_id
                            AND b.is_revisit = '1'
                            AND b.is_deleted = 'n' );  
 
*联表多记录更新：
UPDATE (SELECT a.category_id_1 aa, b.category_id - 100000000 bb
          FROM ali_zeus_temp_company a, ali_zeus_contract b
         WHERE (a.category_id_1 IS NULL OR a.category_id_1 = 0)
           AND (b.category_id > 0)
           AND a.contract_id = b.ID
           AND b.is_deleted = 'n'
           AND a.is_deleted = 'n')
   SET aa = bb
                            
*Kill session:
  select sid,serial#,machine,status from v$session where username is not null ;
  alter system kill session '<sid>,<serial#>';
  
*注释
--给表加注释
COMMENT ON TABLE zip_code IS 'US Postal Service Zip Codes';
--清注释
COMMENT ON TABLE zip_code IS '';
--字段加注释
COMMENT ON COLUMN ALI_WATCH_TASK.DATABASE_ID IS '1-->zeus_test 2-->bops_test';