Oracle 三元条件查询
Oracle正则表达式替换
Oracle处理树
Oracle查询表结构
创建和运行Oracle的Job
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,'(.*)@.*','\1@szrhzs.com.cn') from users order by username;
update users set forwarddestination=REGEXP_REPLACE(forwarddestination,'(.*)@.*','\1@szrhzs.com.cn') 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;
/
扫一扫
391
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
