Oracle经典SQL语句

Oracle 三元条件查询

select (case a.status
         when '1' then '库存'
         when '2' then '可以使用'
         when '3' then '在用'
         when '4' then '损坏'
         when '5' then '报废'
         when '6' then '已注销'
         else '未知状态'
       end) as status, a.*
from T_MKCARD a;

select decode(a.status,'1','库存','2','可以使用','3','在用','4','损坏','5','报废','6','已注销','未知状态') as status, a.*
from T_MKCARD a;

select decode(greatest(endtime,sysdate),sysdate,'系统时间','结束时间')||'大' from t_pdproduct t;

Oracle正则表达式替换

SELECT REGEXP_REPLACE(forwarddestination,'(.*)@.*','\[e-mail]') from users order by username;
update users set forwarddestination=REGEXP_REPLACE(forwarddestination,'(.*)@.*','\[e-mail]') where username='111';

Oracle处理树

select * from t_bossmenu where parentcode=000224 connect by parentcode = prior menucode start with menucode =000223 order by menucode asc;

select lpad(ename, level * 2 + length(ename), '    ') as enames from emp where level <= 3 connect by prior empno = mgr start with empno = 7839 order by deptno;

Oracle查询表结构

select * from user_col_comments where table_name = 'T_ACINVOICE';

select a.column_id as 列号,
       a.column_name as 列名,
       a.data_type as 类型,
       decode(a.data_type, 'NUMBER', a.data_precision, a.data_length) as 长度,
       a.data_scale as 小数位,
       decode(e.uniqueness, 'UNIQUE', 'Y', 'N') as 是否是唯一的,
       decode(e.key, 'Y', 'Y', 'N') 是否是主键,
       f.comments as 注释,
       a.nullable as 是否允许空,
       a.data_default as 默认值
  from user_tab_columns a,
       user_col_comments f,
       (select b.table_name,
               b.index_name,
               b.uniqueness,
               c.column_name,
               decode(d.constraint_name, NULL, 'N', 'Y') key
          from user_indexes b,
               user_ind_columns c,
               (select constraint_name
                  from user_constraints
                 where constraint_type = 'P') d
         where b.index_name = c.index_name
           and b.index_name = d.constraint_name(+)) e
 where a.table_name = 'T_ACINVOICE'
   and a.table_name = e.table_name(+)
   and a.column_name = e.column_name(+)
   and a.table_name = f.table_name
   and a.column_name = f.column_name
 order by a.column_id;

创建和运行Oracle的Job

variable job number; 
begin 
        sys.dbms_job.submit(job => :job, 
                            what => 'DELETE FROM CA_APPCMD_LOG WHERE V_ENTITLECODE IS NULL;', 
                             next_date => to_date('15-03-2009 10:06:41', 'dd-mm-yyyy hh24:mi:ss'), 
                             interval => 'sysdate+1/288',
														 no_parse=>true);--每天1440分钟，即五分钟运行语句一次 
       commit; 
      end; 
/

select * from dba_jobs;
/

begin 
dbms_job.run(:job); 
end; 
/ 

Oracle分页查询语句:

SELECT *
  FROM (SELECT temp.*, ROWNUM num
          FROM (
                --完整的查询语句
                select a.*,
                        to_char(a.BEGINTIME, 'yyyy-mm-dd') as begindate,
                        to_char(a.ENDTIME, 'yyyy-mm-dd') as enddate,
                        b.REGIONNAME,
                        c.STORENAME,
                        d.PARANAME as PROD_TYPENAME,
                        e.FEENAME as FEENAME
                  from T_PDPRODUCT a,
                        T_MKREGION  b,
                        T_MKSTORE   c,
                        T_SYDICT    d,
                        T_ACFEETYPE e
                 where a.REGIONCODE = b.REGIONCODE
                   and a.STORECODE = c.STORECODE
                   and a.PROD_TYPE = d.PARAVALUE
                   and a.FEECODE = e.FEECODE(+)
                   and d.TYPECODE = '014'
                 order by a.PROD_SN
								 --end
								 ) temp
         where ROWNUM <= 10)
 where num > 0;

SELECT rs2.*, rs2.rownumber
  FROM (SELECT rs1.*, ROWNUM AS rownumber
          FROM (
                --完整的查询语句
                select a.*,
                        to_char(a.BEGINTIME, 'yyyy-mm-dd') as begindate,
                        to_char(a.ENDTIME, 'yyyy-mm-dd') as enddate,
                        b.REGIONNAME,
                        c.STORENAME,
                        d.PARANAME as PROD_TYPENAME,
                        e.FEENAME as FEENAME
                  from T_PDPRODUCT a,
                        T_MKREGION  b,
                        T_MKSTORE   c,
                        T_SYDICT    d,
                        T_ACFEETYPE e
                 where a.REGIONCODE = b.REGIONCODE
                   and a.STORECODE = c.STORECODE
                   and a.PROD_TYPE = d.PARAVALUE
                   and a.FEECODE = e.FEECODE(+)
                   and d.TYPECODE = '014'
                 order by a.PROD_SN
								 --end
								 ) rs1) rs2
 WHERE rs2.rownumber <= 10
   and rs2.rownumber > 0;

利用分析函数:
select * from(select 表名.*,row_number() over(order by 排序字段) as 排序别名 from 所有者.表名) where 排序别名>(当前显示页面数-1)*每页显示纪录数 and 排序别名<=当前显示页面数*每页显示纪录数;
示例:
/*先按字段排序，然后分页，再按其他字段排序*/
 select * from (select rownum,scott.emp.*,row_number() over(order by sal desc) as r from scott.emp) where r between 1 and 5 order by empno desc;

/*分页后再按其他字段排序*/
 select * from (select rownum,scott.emp.*,row_number() over(order by rowid) as r from scott.emp) where r between 1 and 5 order by empno desc;

利用伪列rownum:(不能排序)
select * from(select rownum 伪列别名,表名.* from 所有者.表名) where 伪列别名>(当前显示页面数-1)*每页显示纪录数 and 伪列别名<=当前显示页面数*每页显示纪录数;
示例:
/*分页后再按其他字段排序*/
select * from (select rownum rowno,e.* from 表 e where rownum<=结束记录数) where rowno<=结束纪录数 and rowno>=开始记录数 order by empno desc;
select * from (select rownum rowno,e.* from scott.emp e where rownum<=5) where rowno<=5 and rowno>=0 order by empno desc;
/*先按字段排序，然后分页，再按其他字段排序*/
select rownum,e.* from (select emp.*,rownum rowno from scott.emp order by sal desc) e where rownum between 1 and 5 order by empno desc;
/*分页后再按其他字段排序*/
select rownum,e.* from (select emp.*,rownum rowno from scott.emp order by sal desc) e where rowno between 1 and 5 order by empno desc;

利用集合操作:(不能排序)
SELECT rownum,表名.* FROM 表名 WHERE ROWNUM<=当前显示页面数*每页显示纪录数
MINUS
SELECT rownum,表名.* FROM 表名 WHERE ROWNUM<=(当前显示页面数-1)*每页显示纪录数
示例:
/*分页后排序，不适合查询大批量数据*/
select * from(SELECT rownum,emp.* FROM emp WHERE ROWNUM<=5
MINUS
SELECT rownum,emp.* FROM emp WHERE ROWNUM<=0) order by empno;

--先按id排序，分页后按照typecode排序
select * from (select rownum,t_sydict.*,row_number() over(order by id desc) as r from t_sydict) where r between 1 and 5 order by typecode desc;


--分页后按照id排序
SELECT * FROM (  SELECT temp.* ,ROWNUM num FROM ( 

select * from T_SYDICT where typecode=021  order by typecode asc

) temp where ROWNUM <= 3 )where num >0 order by id desc;

--几乎每一个WEB应用都会用到分页，因此，将其做得通用高效就变得非常重要了，根据自己的想法用存储过程做了一个分页的存储过程，与大家分享，希望能够通过讨论得到更好的解决方案。 
--之所以用存储过程，是因为以后需要修改的话不需要修改程序代码，只需要修改存储过程的代码。但这个例子是在存储过程里动态生成的SQL语句，不知道会不会因此失去存储过程一次编译和快速的特点。代码如下：

--1、首先建立一个包，用户创建一个游标类型
create or replace package pkg_query as
  type cur_query is ref cursor;
end pkg_query;

--2、创建存储过程

CREATE OR REPLACE PROCEDURE prc_query
       (p_tableName        in  varchar2,   --表名
        p_strWhere         in  varchar2,   --查询条件
        p_orderColumn      in  varchar2,   --排序的列
        p_orderStyle       in  varchar2,   --排序方式
        p_curPage          in out Number,  --当前页
        p_pageSize         in out Number,  --每页显示记录条数
        p_totalRecords     out Number,     --总记录数
        p_totalPages       out Number,     --总页数
        v_cur              out pkg_query.cur_query)   --返回的结果集
IS
   v_sql VARCHAR2(1000) := '';      --sql语句
   v_startRecord Number(4);         --开始显示的记录条数
   v_endRecord Number(4);           --结束显示的记录条数
BEGIN
   --记录中总记录条数
   v_sql := 'SELECT TO_NUMBER(COUNT(*)) FROM ' || p_tableName || ' WHERE 1=1';
   IF p_strWhere IS NOT NULL or p_strWhere <> '' THEN
       v_sql := v_sql || p_strWhere;
   END IF;
   EXECUTE IMMEDIATE v_sql INTO p_totalRecords;
   
   --验证页面记录大小
   IF p_pageSize < 0 THEN
       p_pageSize := 0;
   END IF;
   
   --根据页大小计算总页数[Page]
   IF MOD(p_totalRecords,p_pageSize) = 0 THEN
       p_totalPages := p_totalRecords / p_pageSize;
   ELSE
       p_totalPages := p_totalRecords / p_pageSize + 1;
   END IF;
   
   --验证页号
   IF p_curPage < 1 THEN
       p_curPage := 1;
   END IF;
   IF p_curPage > p_totalPages THEN
       p_curPage := p_totalPages;
   END IF;
   
   --实现分页查询
   v_startRecord := (p_curPage - 1) * p_pageSize + 1;
   v_endRecord := p_curPage * p_pageSize;
   v_sql := 'SELECT * FROM (SELECT A.*, rownum r FROM ' ||
            '(SELECT * FROM ' || p_tableName;
   IF p_strWhere IS NOT NULL or p_strWhere <> '' THEN
       v_sql := v_sql || ' WHERE 1=1' || p_strWhere;
   END IF;
   IF p_orderColumn IS NOT NULL or p_orderColumn <> '' THEN
       v_sql := v_sql || ' ORDER BY ' || p_orderColumn || ' ' || p_orderStyle;
   END IF;
   v_sql := v_sql || ') A WHERE rownum <= ' || v_endRecord || ') B WHERE r >= '
            || v_startRecord;
   DBMS_OUTPUT.put_line(v_sql);
   OPEN v_cur FOR v_sql;
END prc_query;

批量插入测试数据

set serveroutput on
    Declare
       rcode integer;
    begin
		      dbms_output.enable(99999999999999);
          for i in 1..10000
  loop
            insert into T_CUSTOMERINFO values (LPAD(seq_custno.nextval,10,'0'),null,01,0101,LPAD(seq_custno.currval,10,'0'),1,1,'110',null,'110','110',null,null,'000',to_date('2009-05-20' , 'yyyy-mm-dd') ,'系统数据',null,'A','系统地址','admin',19,0,null);
            dbms_output.put_line('rcode：'||to_char(i));
          end loop;
          rcode := 1;
          dbms_output.put_line('结果是：'||to_char(rcode));
    exception
      when others then
        rollback;
        rcode := 0;
        dbms_output.put_line('结果是：'||to_char(rcode));
    end;		
/