order by 的数值型灵活使用
select * from table_a where id=p_id order by decode(函数,'asc',1,'desc',-1)*jsny; 控制试图的访问时间：
6.create view ...
as
select ... from where exists(select x from dual where sysdate>=8:00am and sysdate<=5:00pm)妙用decode实现排序
select * from tabname
order by decode(mode,'FIFO',1,-1)*to_char(rq,'yyyymmddhh24miss'); select * from tabname
order by decode(mode,'FIFO',rq-sysdate, sysdate-rq)
找出某个时期内工作日数：
select count(*)
from ( select rownum-1 rnum
from all_objects
where rownum <= to_date('2002-02-28','yyyy-mm-dd') - to_date('2002-
02-01','yyyy-mm-dd')+1 )
where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' ) not
in ( '1', '7' )我觉得查询重复记录的语句就很经典
select rowid,bdsszd from BADWDJ a where a.rowid != (select max(rowid) from BADWDJ b where a.bdsszd =b.bdsszd)
由它引申的就有很多有用的语句，如昨天回答别人的排序的难题
select id,bdsszd from BADWDJ a where a.id = (select max(id) from BADWDJ b where a.bdsszd =b.bdsszd) order by id树型结构表的查询：
select ID,PARENT_ID from parent_child
connect by prior id = parent_id
start with id = 1;1.decode这个函数一定需要会，我觉得sql的灵活很多地方都是通过这个function来体现的，相当于if，很好用。
2.group by，这个东东想想简单，其实好多统计功能是离不开这个操作的。oracle8中扩充了group by rollup和cube的操作。有时候省了你好多功夫的。值得注意的是，当你对事物做过有效的人为归并之后执行group by 往往会更让人心旷神怡。
3.很表竖置的经典写法，也要记住：sum(decode( )) group by ...
注意：需要在一个subquery中确定一个横置判点。
4.树形结构表的遍历写法：select ...from ....
start with ... connect by prior (父子关系表达式)select * from xxx where decode(:var,null,'0',column1) = decode(:var,null,'0',:var);816以上的 一些分析函数如 rank() over() and row_number() over()
当然关于 group by rollup/cube使用的人恐怕特别少如何实现最大匹配的操作?
例如:给定字符串 '1234', 而表中可能有记录项'1','12','123','1234','12345',要选出'1234'项
select * from (
select col_FT from table_FT
where instr('12345',col_FT)=1
order by length(col_FT) desc)
where rownum =1给你一个意想不到的东西
SQL> select to_char(to_date(12,'yyyy'),'year') from dual;
TO_CHAR(TO_DATE(12,'YYYY'),'YEAR')
------------------------------------------
twelveselect to_char(sysdate,'day') from dual
还有 d、iw、mm等等格式对于translate函数有一个功能
比如：找出某个字符串中完全是数字
select * from xxx where translate(column1,'1234567890','') = column1;
select trunc(sysdate) from dual;
select trunc(sysdate,'mm') from dual;
大家构造几个例子看看就能明白
select a,b,sum(c) from xxx group by rollup(a,b);select a,b,sum(c) from xxx group by cube(a,b);怎么查找字符串里面包含有%的记录：
当然，常规方法就是利用 escape了
可如果不知道escape也行，比如
select * from xxx where replace(a,'%','') = a;利用decode解决动态sql的不确定条件查询的问题:
假设前台传入的都是变量
select * from xxx where decode(:var,null,'0',column1) = decode(:var,null,'0',:var);
这样比 like :var||'%' 效率高另:对于
select ...
from a,b
where a.a = b.a(+) and b.a is null;
我想对于不明白的朋友,我要交代一下用处才好:比如,你需要查找在a表中有而b表中没有的记录
也许你会选择 not in:
select * from a aa where aa.a1 not in (select a1 from bb);
这是效率最低的
或者:
select a1 from aa
minus
select a1 from bb;所有这些写法,都不如下面下率高:
select a.* from aa a,bb b
where a.a1 = b.a1(+) and b.a1 is null;给一个很普通的适用的最高效的外连接例子（不是什么新鲜玩意）：
select ...
from a,b
where a.a = b.a(+) and b.a is null;我要按年龄段（小于20，20-30，---）统计人数，我可以用
select
sum(decode(sign(age - 20),-1,1,0)),
sum(decode(sign(age - 20),-1,0,(decode(sign(age - 30,-1,1,0))))),
sum(decode(sign(age - 30),-1,0,(decode(sign(age - 40,-1,1,0))))),
sum(decode(sign(age - 40),-1,0,(decode(sign(age - 50,-1,1,0))))),
sum(decode(sign(age - 50),-1,0,1))
from xxx;
这样只做一遍表扫描
这是分了20以下和50以上的
类似的问题，自己扩展了添加行号：
select (select count(*) from a1 where item <= a.item) AS ROW, * FROM a1 as a order by itemselect * from table1 a
where id in (select top 3 from table1 where 物品=a.物品 order by price desc)每一种物品有很多价格，每一种物品选择排在前三的纪录1。job的使用：
DBMS_JOB.SUBMIT(:jobno,//job号
'your_procedure;',//要执行的过程
trunc(sysdate)+1/24,//下次执行时间
'trunc(sysdate)+1/24+1'//每次间隔时间
);
删除job:dbms_job.remove(jobno);
修改要执行的操作:job:dbms_job.what(jobno,what);
修改下次执行时间：dbms_job.next_date(job,next_date);
修改间隔时间：dbms_job.interval(job,interval);
停止job:dbms.broken(job,broken,nextdate);
启动job:dbms_job.run(jobno);
例子：
VARIABLE jobno number;
begin
DBMS_JOB.SUBMIT(:jobno,
'Procdemo;',//Procdemo为过程名称
SYSDATE, 'SYSDATE + 1/720');
commit;
end;
/
2。把一个表放在内存里
alter table tablename cache.
3。创建临时表
CREATE GLOBAL TEMPORARY TABLE TABLENAME (
COL1 VARCHAR2(10),
COL2 NUMBER
) ON COMMIT PRESERVE(DELETE) ROWS ;
这种临时表不占用表空间，而且不同的SESSION之间互相看不到对方的数据
在会话结束后表中的数据自动清空，如果选了DELETE ROWS，则在提交的时候即清空数据，PRESERVE则一直到会话结束
4。加一个自动增加的id号
第一种方法：
第一步：创建SEQUENCE
create sequence s_country_id increment by 1 start with 1 maxvalue 999999999;
第二步：创建一个基于该表的before insert 触发器，在触发器中使用该SEQUENCE
create or replace trigger bef_ins_t_country_define
before insert on t_country_define
referencing old as old new as new for each row
begin
select s_country_id.nextval into :new.country_id from dual;
end;
/
第二种方法：
CREATE OR REPLACE TRIGGER TR1
BEFORE INSERT ON temp_table
FOR EACH ROW
declare
com_num NUMBER;
BEGIN
SELECT MAX(ID) INTO COM_NUM FROM TEMP_TABLE;
:NEW.ID:=COM_NUM+1;
END TR1;
/
5。限制用户登录：创建一个概要文件
create profile CLERK_PROFILE limit
session_per_user 1 #用户可拥有的会话次数
idle_time 10 #进程处于空闲状态的时间(10分钟)
然后就可以将该概要文件授予一个用户
alter user A profile CLERK_PROFILE;
6。使触发器为无效alter trigger yourtriggername disable如果是对于某一个表的所有的触发器：
alter table yourtablename disable all triggers
select * from table_a where id=p_id order by decode(函数,'asc',1,'desc',-1)*jsny; 控制试图的访问时间：
6.create view ...
as
select ... from where exists(select x from dual where sysdate>=8:00am and sysdate<=5:00pm)妙用decode实现排序
select * from tabname
order by decode(mode,'FIFO',1,-1)*to_char(rq,'yyyymmddhh24miss'); select * from tabname
order by decode(mode,'FIFO',rq-sysdate, sysdate-rq)
找出某个时期内工作日数：
select count(*)
from ( select rownum-1 rnum
from all_objects
where rownum <= to_date('2002-02-28','yyyy-mm-dd') - to_date('2002-
02-01','yyyy-mm-dd')+1 )
where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' ) not
in ( '1', '7' )我觉得查询重复记录的语句就很经典
select rowid,bdsszd from BADWDJ a where a.rowid != (select max(rowid) from BADWDJ b where a.bdsszd =b.bdsszd)
由它引申的就有很多有用的语句，如昨天回答别人的排序的难题
select id,bdsszd from BADWDJ a where a.id = (select max(id) from BADWDJ b where a.bdsszd =b.bdsszd) order by id树型结构表的查询：
select ID,PARENT_ID from parent_child
connect by prior id = parent_id
start with id = 1;1.decode这个函数一定需要会，我觉得sql的灵活很多地方都是通过这个function来体现的，相当于if，很好用。
2.group by，这个东东想想简单，其实好多统计功能是离不开这个操作的。oracle8中扩充了group by rollup和cube的操作。有时候省了你好多功夫的。值得注意的是，当你对事物做过有效的人为归并之后执行group by 往往会更让人心旷神怡。
3.很表竖置的经典写法，也要记住：sum(decode( )) group by ...
注意：需要在一个subquery中确定一个横置判点。
4.树形结构表的遍历写法：select ...from ....
start with ... connect by prior (父子关系表达式)select * from xxx where decode(:var,null,'0',column1) = decode(:var,null,'0',:var);816以上的 一些分析函数如 rank() over() and row_number() over()
当然关于 group by rollup/cube使用的人恐怕特别少如何实现最大匹配的操作?
例如:给定字符串 '1234', 而表中可能有记录项'1','12','123','1234','12345',要选出'1234'项
select * from (
select col_FT from table_FT
where instr('12345',col_FT)=1
order by length(col_FT) desc)
where rownum =1给你一个意想不到的东西
SQL> select to_char(to_date(12,'yyyy'),'year') from dual;
TO_CHAR(TO_DATE(12,'YYYY'),'YEAR')
------------------------------------------
twelveselect to_char(sysdate,'day') from dual
还有 d、iw、mm等等格式对于translate函数有一个功能
比如：找出某个字符串中完全是数字
select * from xxx where translate(column1,'1234567890','') = column1;
select trunc(sysdate) from dual;
select trunc(sysdate,'mm') from dual;
大家构造几个例子看看就能明白
select a,b,sum(c) from xxx group by rollup(a,b);select a,b,sum(c) from xxx group by cube(a,b);怎么查找字符串里面包含有%的记录：
当然，常规方法就是利用 escape了
可如果不知道escape也行，比如
select * from xxx where replace(a,'%','') = a;利用decode解决动态sql的不确定条件查询的问题:
假设前台传入的都是变量
select * from xxx where decode(:var,null,'0',column1) = decode(:var,null,'0',:var);
这样比 like :var||'%' 效率高另:对于
select ...
from a,b
where a.a = b.a(+) and b.a is null;
我想对于不明白的朋友,我要交代一下用处才好:比如,你需要查找在a表中有而b表中没有的记录
也许你会选择 not in:
select * from a aa where aa.a1 not in (select a1 from bb);
这是效率最低的
或者:
select a1 from aa
minus
select a1 from bb;所有这些写法,都不如下面下率高:
select a.* from aa a,bb b
where a.a1 = b.a1(+) and b.a1 is null;给一个很普通的适用的最高效的外连接例子（不是什么新鲜玩意）：
select ...
from a,b
where a.a = b.a(+) and b.a is null;我要按年龄段（小于20，20-30，---）统计人数，我可以用
select
sum(decode(sign(age - 20),-1,1,0)),
sum(decode(sign(age - 20),-1,0,(decode(sign(age - 30,-1,1,0))))),
sum(decode(sign(age - 30),-1,0,(decode(sign(age - 40,-1,1,0))))),
sum(decode(sign(age - 40),-1,0,(decode(sign(age - 50,-1,1,0))))),
sum(decode(sign(age - 50),-1,0,1))
from xxx;
这样只做一遍表扫描
这是分了20以下和50以上的
类似的问题，自己扩展了添加行号：
select (select count(*) from a1 where item <= a.item) AS ROW, * FROM a1 as a order by itemselect * from table1 a
where id in (select top 3 from table1 where 物品=a.物品 order by price desc)每一种物品有很多价格，每一种物品选择排在前三的纪录1。job的使用：
DBMS_JOB.SUBMIT(:jobno,//job号
'your_procedure;',//要执行的过程
trunc(sysdate)+1/24,//下次执行时间
'trunc(sysdate)+1/24+1'//每次间隔时间
);
删除job:dbms_job.remove(jobno);
修改要执行的操作:job:dbms_job.what(jobno,what);
修改下次执行时间：dbms_job.next_date(job,next_date);
修改间隔时间：dbms_job.interval(job,interval);
停止job:dbms.broken(job,broken,nextdate);
启动job:dbms_job.run(jobno);
例子：
VARIABLE jobno number;
begin
DBMS_JOB.SUBMIT(:jobno,
'Procdemo;',//Procdemo为过程名称
SYSDATE, 'SYSDATE + 1/720');
commit;
end;
/
2。把一个表放在内存里
alter table tablename cache.
3。创建临时表
CREATE GLOBAL TEMPORARY TABLE TABLENAME (
COL1 VARCHAR2(10),
COL2 NUMBER
) ON COMMIT PRESERVE(DELETE) ROWS ;
这种临时表不占用表空间，而且不同的SESSION之间互相看不到对方的数据
在会话结束后表中的数据自动清空，如果选了DELETE ROWS，则在提交的时候即清空数据，PRESERVE则一直到会话结束
4。加一个自动增加的id号
第一种方法：
第一步：创建SEQUENCE
create sequence s_country_id increment by 1 start with 1 maxvalue 999999999;
第二步：创建一个基于该表的before insert 触发器，在触发器中使用该SEQUENCE
create or replace trigger bef_ins_t_country_define
before insert on t_country_define
referencing old as old new as new for each row
begin
select s_country_id.nextval into :new.country_id from dual;
end;
/
第二种方法：
CREATE OR REPLACE TRIGGER TR1
BEFORE INSERT ON temp_table
FOR EACH ROW
declare
com_num NUMBER;
BEGIN
SELECT MAX(ID) INTO COM_NUM FROM TEMP_TABLE;
:NEW.ID:=COM_NUM+1;
END TR1;
/
5。限制用户登录：创建一个概要文件
create profile CLERK_PROFILE limit
session_per_user 1 #用户可拥有的会话次数
idle_time 10 #进程处于空闲状态的时间(10分钟)
然后就可以将该概要文件授予一个用户
alter user A profile CLERK_PROFILE;
6。使触发器为无效alter trigger yourtriggername disable如果是对于某一个表的所有的触发器：
alter table yourtablename disable all triggers
preference number not null := 0;
always_batch constant number := 1;
order_by constant number := 2;
postfilter_batch constant number := 4; -- PUBLIC DATA STRUCTURES
type BROWSE_REC is record
(
word VARCHAR2(256) NULL,
doc_count NUMBER(38,0) NULL
); type BROWSE_TAB is table of BROWSE_REC index by binary_integer; -- PUBLIC CONSTANTS
BROWSE_BEFORE constant varchar2(10) := 'BEFORE';
BROWSE_AROUND constant varchar2(10) := 'AROUND';
BROWSE_AFTER constant varchar2(10) := 'AFTER';/*------------------------------- count_hits ----------------------------*/
/*
NAME
count_hits - get quick count of text results DESCRIPTION
count text hits ARGUMENTS
index_name (IN) index name being queried
text_query (IN) text query string
exact (IN) exact count or upper bound
part_name (IN) index partition name NOTES
none RETURNS
number of hits
*/
FUNCTION count_hits (
index_name in varchar2
,text_query in varchar2
,exact in boolean default TRUE
,part_name in varchar2 default NULL
) return number;
/*------------------------------- chk_xpath ----------------------------*/
/*
NAME
chk_xpath - check xpath expression DESCRIPTION
takes an xpath expression and return an expression context can
process. ARGUMENTS
index_name (IN) index name being queried
text_query (IN) xpath expression
part_name (IN) index partition name NOTES
none RETURNS
number of hits
*/
FUNCTION chk_xpath (
index_name in varchar2
,text_query in varchar2
,part_name in varchar2 default NULL
) return varchar2;/*------------------------------- fcontains ----------------------------*/
/*
NAME
fcontains - functional contains DESCRIPTION ARGUMENTS
text_value (IN) text to search
text_query (IN) text query
policy_name (IN) policy name NOTES
none RETURNS
score
*/
FUNCTION fcontains (
text_value in varchar2
,text_query in varchar2
,policy_name in varchar2
) return number;
/*------------------------------- store_sqe ----------------------------*/
/*
NAME
store_sqe DESCRIPTION
Create a stored query ARGUMENTS
query_name (IN) name of a stored query
text_query (IN) text query string
NOTES
EXCEPTIONS
ORA-20000 - application error ( with an textile error stack)
RETURNS
none
*/
PROCEDURE store_sqe(
query_name in varchar2
,text_query in varchar2
);/*------------------------------- remove_sqe ----------------------------*/
/*
NAME
remove_sqe DESCRIPTION
Delete a stored query ARGUMENTS
query_name (IN) name of a stored query NOTES EXCEPTIONS
ORA-20000 - application error ( with an textile error stack) RETURNS
none
*/
PROCEDURE remove_sqe(
query_name in varchar2
);/*------------------------------ explain ----------------------------------*/
/*
NAME
explain DESCRIPTION
Evaluate the query specified in 'text_query' parameter and return
the Query Execution Plan in the feedback table. Do NOT execute the
query. ARGUMENTS
index_name (IN) index name being queried
text_query (IN) ConText query string
explain_table (IN) the result table to receive the feedback result
sharelevel (IN) feedback table share options:
0 = single-use, 1 = multiple-use.
explain_id (IN) ID to identify results returned
part_name (IN) index partition name
NOTES
none
EXCEPTIONS
ORA-20000 - application error (with a textile error stack)
RETURNS
none
*/
PROCEDURE explain(
index_name in varchar2,
text_query in varchar2,
explain_table in varchar2,
sharelevel in number default 0,
explain_id in varchar2 default NULL,
part_name in varchar2 default NULL
);/*------------------------------ hfeedback ----------------------------------*/
/*
NAME
hfeedback DESCRIPTION
Evaluate the query specified in 'text_query' parameter and return
the Hierarchical Query Feedback in the feedback table. Do NOT execute the
query. ARGUMENTS
index_name (IN) index name being queried
text_query (IN) ConText query string
feedback_table (IN) the result table to receive the feedback result
sharelevel (IN) feedback table share options:
0 = single-use, 1 = multiple-use.
feedback_id (IN) ID to identify results returned
part_name (IN) index partition name
NOTES
none
EXCEPTIONS
ORA-20000 - application error (with a textile error stack)
RETURNS
none
*/
PROCEDURE hfeedback(
index_name in varchar2,
text_query in varchar2,
feedback_table in varchar2,
sharelevel in number default 0,
feedback_id in varchar2 default NULL,
part_name in varchar2 default NULL
);/*------------------------------ hfeed_execute ------------------------------*/
/*
FOR INTERNAL USE -- CUSTOMERS SHOULD NOT CALL DIRECTLY
*/
PROCEDURE hfeed_execute(
iowner in varchar2,
index_name in varchar2,
text_query in varchar2,
feedback_table in varchar2,
sharelevel in number default 0,
feedback_id in varchar2 default NULL,
fmode in number,
part_name in varchar2 default NULL
);/*------------------------------ browse_words ------------------------------*/
/*
NAME
browse_words DESCRIPTION ARGUMENTS
index_name (IN) index name being queried
seed (IN) seed word
restab (IN) the result table
browse_id (IN) ID to identify results returned
numwords (IN) length of the produced list
direction (IN) direction of the browse
part_name (IN) index partition name NOTES EXCEPTIONS RETURNS
*/
PROCEDURE browse_words(
index_name in varchar2,
seed in varchar2,
restab in varchar2,
browse_id in number default 0,
numwords in number default 10,
direction in varchar2 default BROWSE_AROUND,
part_name in varchar2 default NULL
);/*------------------------------ browse_words ------------------------------*/
/*
NAME
browse_words DESCRIPTION ARGUMENTS
index_name (IN) index name being queried
seed (IN) seed word
resarr (IN OUT) PL/SQL array
numwords (IN) length of the produced list
direction (IN) direction of the browse
part_name (IN) index partition name NOTES EXCEPTIONS RETURNS
*/
PROCEDURE browse_words(
index_name in varchar2,
seed in varchar2,
resarr in out NOCOPY BROWSE_TAB,
numwords in number default 10,
direction in varchar2 default BROWSE_AROUND,
part_name in varchar2 default NULL
);end ctx_query;
create or replace procedure pSecurityDaysCaculate is
RecDate date;
Days Number;
type RSSecurity is Record(
BMBM VARCHAR2(50),
NAME VARCHAR2(50),
WZD NUMBER(10),
WTD NUMBER(10),
WD NUMBER(10),
WXX NUMBER(10),
WYB NUMBER(10),
WSW NUMBER(10),
WZS NUMBER(10),
WQS NUMBER(10),
WJX NUMBER(10),
WHZ NUMBER(10),
TARGET NUMBER(10),
TOTARGET NUMBER(10)
);
type RSDateRecord is record(
RDate DATE);
RSD RSDateRecord;
Cursor RSDC is Select * from tDateRecord;
RSS RSSecurity;
Cursor RSSC is select * from table_glaq_aqt;
begin
open rsdc;
fetch rsdc into rsd;
if rsdc%NOTFOUND then
RecDate:=to_date(to_char(sysdate,'yyyy-mm-dd')||' 18:00:00','yyyy-mm-dd hh24:mi:ss');
Insert into tDateRecord Values(RecDate-1);
else
select RDate into RecDate from tDateRecord;
end if;
Days:=trunc(sysDate-RecDate,0);
if days>1 then
Update tDateRecord Set RDate=to_date(to_char(sysdate-1,'yyyy-mm-dd')||'18:00:00','yyyy-mm-dd hh24:mi:ss');
end if;
close rsdc;
open rssc ;
fetch rssc into rss;
if rssc%FOUND then
loop
if rss.totarget=0 then
update table_glaq_aqt set wzd=rss.wzd+days,wtd=rss.wtd+days,wd=rss.wd+days
,wxx=rss.wxx+days,wyb=rss.wyb+days,wsw=rss.wsw+days,wzs=rss.wzs+days
,wqs=rss.wqs+days,wjx=rss.wjx+days,whz=rss.whz+days where bmbm=rss.bmbm;
else
update table_glaq_aqt set wzd=rss.wzd+days,wtd=rss.wtd+days,wd=rss.wd+days
,wxx=rss.wxx+days,wyb=rss.wyb+days,wsw=rss.wsw+days,wzs=rss.wzs+days
,wqs=rss.wqs+days,wjx=rss.wjx+days,whz=rss.whz+days,TOTARGET=rss.TOTARGET-days
where bmbm=rss.bmbm;
end if;
fetch rssc into rss;
exit when rssc%NOTFOUND;
end loop;
end if;
close rssc;
if to_char(sysdate,'hh24')>='18' then
Update tDateRecord Set RDate=to_date(to_char(sysdate,'yyyy-mm-dd')||'18:00:00','yyyy-mm-dd hh24:mi:ss');
end if;
exception
when NO_DATA_FOUND then
raise_application_error(-20100,'no data find!');
when OTHERS then
raise_application_error(-20101,'some custom error message!');
end pSecurityDaysCaculate;
/