oracle常用语法大全

下面的oracle语法都是平时开发过程中经常使用的一些例子，是我在开发的时候总结的一小部分，有些语法在管理数据库和数据库开发的时候还是挺实用的。

--登陆不区分大小写
alter system set sec_case_sensitive_logon=false; 

--获取某个大写英文字母下一字母
select chr(ascii(substr('01B1',length('01B1')-1,1))+1) from dual;

--保留两位小数
select to_number(trim(to_char(13.01,'99999999999999.99'))) from dual;  

--选出1-100连续整数
select rownum from dual connect by rownum<=100;  

--当前日期是星期几 'yyyy-mm-dd hh-mi-ss'
select to_char(sysdate,'day') from dual;

--指定日期是星期几(数字)
select to_char(to_date('2015-12-19','yyyy-mm-dd'),'D') from dual;  

--获取某个月有多少天
SELECT TO_CHAR(ADD_MONTHS(SYSDATE,0),'YYYY-MM') MONTH ,TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE,0)),'DD') DAY FROM DUAL; 

--取当前日期前一个月的第一天的日期
SELECT last_day(add_months(SYSDATE, -2)) + 1 FROM dual;

--取当前日期前一个月的最后一天的日期
SELECT last_day(add_months(SYSDATE, -1)) FROM dual;

--本月的第一天和最后一天
select to_char(trunc(sysdate,'MONTH'),'yyyy-mm-dd') First_DayOfMonth
       ,to_char(last_day(trunc(sysdate,'MONTH')),'yyyy-mm-dd') Last_DayOfMonth from dual;

--查询数据库连接情况
select username,sid,serial#,client_info,client_identifier from v$session;

--查看所有session信息
select * from v$session; 

--数据连接数
select sys_context('userenv','sid') from dual;

--当前连接IP
SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM DUAL; 

select username,sysdate,sys_context('userenv','ip_address') from v$session;  

--杀死数据库连接session
select sid,serial# from v$session where username = 'WF_STG';

select 'alter system kill session '''||sid||', '||serial#||''';' aa from v$session where username = 'WF_STG';
--删除用户及用户表空间
drop user WF_STG cascade;

--创建用户，设置密码，指定表空间
create user WF_STG identified by zhao default tablespace users temporary tablespace temp profile default;

--用户授权授权
grant connect,resource,dba,create any view,create any table,create any synonym,delete any table,insert any table,select any table,update any table to WF_STG;

--取消授权
revoke connect,resource,dba from wf_stg;

--oracle表空间不足扩容的方法   
--第一种方法，在原有数据库文件上面设置自增长（例：每次增长512M)
AUTOEXTEND ON NEXT 512M ALTER DATABASE DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS02.DBF' AUTOEXTEND ON NEXT 512M; --MAXSIZE 1024M

--第二种方法，为表空间新增数据库文件，并指定文件大小，自增长
alter tablespace vgsm add datafile 'c:\oracle\product\10.2.0\oradata\vgsm\vgsm_01.dbf'
size 1024M autoextend on next 50M maxsize unlimited;

--查看所有表空间使用情况
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
　　D.TOT_GROOTTE_MB "表空间大小(M)",
　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
　　F.TOTAL_BYTES "空闲空间(M)",
　　F.MAX_BYTES "最大块(M)"
　　FROM (SELECT TABLESPACE_NAME,
　　ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
　　ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
　　FROM SYS.DBA_FREE_SPACE
　　GROUP BY TABLESPACE_NAME) F,
　　(SELECT DD.TABLESPACE_NAME,
　　 ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
　　FROM SYS.DBA_DATA_FILES DD
　　GROUP BY DD.TABLESPACE_NAME) D
　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
　　ORDER BY 1;

--database link 创建
CREATE [PUBLIC] DATABASE LINK link CONNECT TO username IDENTIFIED BY password USING ‘connectstring’

create database link CWHT
  connect to FD4
  using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.18.22)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl)
)
)';

--根据一个表更新另一张表的信息，更新字段的内容是根据两个表关联进行获取
update (select a.uni_no,a.dno,b.dn1 from gz_jzy.SALARY2015 a,DBZ1 b where a.uni_no=b.uni_no)  set DNO=DN1;

--字符串分割函数：
v_content:分号隔开的字符串
v_r char(60); --分割后的字符串
v_n    number;  --分割后数组长度
v_m    number;  --数组索引

select length(trim(v_content)) -
             length(replace(trim(v_content), ';', ''))
          into v_n from dual;

      FOR v_m IN 1 .. (v_n + 1) LOOP
        v_r := regexp_substr(trim(v_content), '[^;]+', 1, v_m);


      END LOOP;

--电话号码正在表达式
select REGEXP_INSTR('14527845882','^(13[0-9]|15[012356789]|17[678]|18[0-9]|14[57])[0-9]{8}$') from dual;
select regexp_instr('0','^[0-9]+(.[0-9]{n})?$') from dual;

--选出一系列不连续的数字中间断的数字
select  min(max(id)) into v_e_id
        from
        (select id,
                id - row_number() over(order by id) diff
          from animalspecies
          where trim(up_id) = trim(v_id) and up_id1 is null and
            (id between (select min(id) from animalspecies)
               and (select max(id) from animalspecies)))
        group by diff;

(select regexp_substr(trim(cpy.jingfei_userid),'[^;]+',1,level)
                from dual connect by level<=length(trim(cpy.jingfei_userid))-length(replace(trim(cpy.jingfei_userid),';'))+1)