nvl oracle ora-00909,Order by 的数值型灵活使用

关键字：select,order by,数值,sql语句,查询

作者：佚名    更新：2004-05-28    浏览：11640

本文来自Chinabcb

代码:

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')

------------------------------------------

twelve

select 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 item

select * 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

更改数据库时间显示格式：

SQL> alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

会话已更改。

1. 选取 TOP N 行记录

代码:

A. SELECT * FROM CAT WHERE ROWNUM<=N

B. SELECT * FROM

( SELECT * FROM CAT ORDER BY TABLE_TYPE )

WHERE ROWNUM<=N

2. 选取N1-N2行记录

代码:

A. SELECT TABLE_NAME,TABLE_TYPE FROM

( SELECT ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT )

WHERE ROWSEQ BETWEEN N1+1 AND N2;

或:

代码:

SELECT * FROM CAT WHERE ROWNUM<=N2

MINUS

SELECT * FROM CAT WHERE ROWNUM

B. SELECT TABLE_NAME,TABLE_TYPE FROM

( SELECT ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT ORDER BY

TABLE_TYPE)

WHERE ROWSEQ BETWEEN N1+1 AND N2;

查主键名称：

代码:

select * from user_constraints

where table_name = 'ART'

and constraint_type ='P';

保存过程内容到文件

先修改init.ora

例如：

utl_file_dir=/usr //路径为 oracle所在的盘:/usr

此过程将用户TEMP的P1过程的代码保存到ORACLE安装盘下/USR/TEXT.TXT中

代码:

create or replace procedure TEST

is

file_handle utl_file.file_type;

STOR_TEXT VARCHAR2(4000);

N NUMBER;

I NUMBER;

begin

I:=1;

SELECT MAX(LINE) INTO N FROM ALL_SOURCE WHERE OWNER='TEMP' AND

NAME='P1';

file_handle:=utl_file.fopen('/usr','test.txt','a');

WHILE I<=N LOOP

SELECT TEXT INTO STOR_TEXT FROM ALL_SOURCE WHERE OWNER='TEMP' AND

NAME='P1' AND LINE= I;

I:=I+1;

utl_file.put_line(file_handle,stor_text);

END LOOP;

utl_file.fclose(file_handle);

commit;

end TEST;

/

0、建立分区表

代码:

create table partition_test

(

id number(9),

tmpStr varchar2(10)

)

partition by range(id)

(

partition id01 values less than (3000000) tablespace test_tabspc1,

partition id02 values less than (6000000) tablespace test_tabspc2,

partition id03 values less than (9000000) tablespace test_tabspc3,

partition id04 values less than (12000000) tablespace test_tabspc4,

partition id05 values less than (MAXVALUE) tablespace test_tabspc5

)

/

1、建立局部分区索引

Create index your_index on caishui.partition_test(id)

local

(

partition id01 tablespace test_tabspc1,

partition id02 tablespace test_tabspc2,

partition id03 tablespace test_tabspc3,

partition id04 tablespace test_tabspc4,

partition id05 tablespace test_tabspc5

)

/

2、重建某一个分区的索引

alter index your_index rebuild partition id01 tablespace test_tabspc1

/

3、增加分区

alter table caishui.partition_test

add partition id06 values less than (15000000) tablespace test_tabspc6

/

4、有影响

5、可以

ALTER TABLE PARTITION_TEST

MERGE PARTITIONS

id01, id02

INTO PARTITION 新分区名

/

6、外部数据文件 d:\test.txt

1|猪八戒

2|孙悟空

3|唐僧

建一个控制文件 d:\test.ctl

load data

infile 'd:\test.txt'

append

into table partition_test

FIELDS TERMINATED BY "|"

(id,tmpStr)

将数据文件的数据导入数据库

sqlldr userid=caishui/password control=d:\test.ctl

如何正确利用Rownum来限制查询所返回的行数？

软// 本文转自 C++Builder研究 -

软件环境：

1、Windows NT4.0+ORACLE 8.0.4

2、ORACLE安装路径为：C:\ORANT

含义解释：

1、rownum是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，

依此类推，这个伪字段可以用于限制查询返回的总行数。

2、rownum不能以任何基表的名称作为前缀。

使用方法：

现有一个商品销售表sale，表结构为：

代码:

month　　　 char(6)　　　　　 --月份

sell　　　　number(10,2)　　　--月销售金额

create table sale (month char(6),sell number);

insert into sale values('200001',1000);

insert into sale values('200002',1100);

insert into sale values('200003',1200);

insert into sale values('200004',1300);

insert into sale values('200005',1400);

insert into sale values('200006',1500);

insert into sale values('200007',1600);

insert into sale values('200101',1100);

insert into sale values('200202',1200);

insert into sale values('200301',1300);

insert into sale values('200008',1000);

commit;

SQL> select rownum,month,sell from sale where

rownum=1;(可以用在限制返回记录条数的地方，保证不出错，如：隐式游标)

ROWNUM MONTH SELL

--------- ------ ---------

1 200001 1000

SQL> select rownum,month,sell from sale where rownum=2;(1以上都查不到记录)

没有查到记录

SQL> select rownum,month,sell from sale where rownum>5;

(由于rownum是一个总是从1开始的伪列，Oracle 认为这种条件不成立，查不到记录)

没有查到记录

只返回前3条纪录

SQL> select rownum,month,sell from sale where rownum<4;

ROWNUM MONTH SELL

--------- ------ ---------

1 200001 1000

2 200002 1100

3 200003 1200

如何用rownum实现大于、小于逻辑？(返回rownum在4—10之间的数据)(minus操作，速度会受影响)

SQL> select rownum,month,sell from sale where rownum<10

2 minus

3 select rownum,month,sell from sale where rownum<5;

ROWNUM MONTH SELL

--------- ------ ---------

5 200005 1400

6 200006 1500

7 200007 1600

8 200101 1100

9 200202 1200

想按日期排序，并且用rownum标出正确序号(有小到大)

SQL> select rownum,month,sell from sale order by month;

ROWNUM MONTH SELL

--------- ------ ---------

1 200001 1000

2 200002 1100

3 200003 1200

4 200004 1300

5 200005 1400

6 200006 1500

7 200007 1600

11 200008 1000

8 200101 1100

9 200202 1200

10 200301 1300

查询到11记录.

可以发现，rownum并没有实现我们的意图，系统是按照记录入库时的顺序给记录排的号，rowid也是顺序分配的

SQL> select rowid,rownum,month,sell from sale order by rowid;

ROWID ROWNUM MONTH SELL

------------------ --------- ------ ---------

000000E4.0000.0002 1 200001 1000

000000E4.0001.0002 2 200002 1100

000000E4.0002.0002 3 200003 1200

000000E4.0003.0002 4 200004 1300

000000E4.0004.0002 5 200005 1400

000000E4.0005.0002 6 200006 1500

000000E4.0006.0002 7 200007 1600

000000E4.0007.0002 8 200101 1100

000000E4.0008.0002 9 200202 1200

000000E4.0009.0002 10 200301 1300

000000E4.000A.0002 11 200008 1000

查询到11记录.

正确用法，使用子查询

SQL> select rownum,month,sell from (select month,sell from sale group

by month,sell) where rownum<13;

ROWNUM MONTH SELL

--------- ------ ---------

1 200001 1000

2 200002 1100

3 200003 1200

4 200004 1300

5 200005 1400

6 200006 1500

7 200007 1600

8 200008 1000

9 200101 1100

10 200202 1200

11 200301 1300

按销售金额排序，并且用rownum标出正确序号(有小到大)

SQL> select rownum,month,sell from (select sell,month from sale group

by sell,month) where rownum<13;

ROWNUM MONTH SELL

--------- ------ ---------

1 200001 1000

2 200008 1000

3 200002 1100

4 200101 1100

5 200003 1200

6 200202 1200

7 200004 1300

8 200301 1300

9 200005 1400

10 200006 1500

11 200007 1600

查询到11记录.

利用以上方法，如在打印报表时，想在查出的数据中自动加上行号，就可以利用rownum。

返回第5—9条纪录，按月份排序

SQL> select * from (select rownum row_id ,month,sell

2 from (select month,sell from sale group by month,sell))

3 where row_id between 5 and 9;

ROW_ID MONTH SELL

---------- ------ ----------

5 200005 1400

6 200006 1500

7 200007 1600

8 200008 1000

9 200101 1100

(1)

查所及杀锁

代码:

select l.session_id sid,

l.locked_mode lockmode,

l.oracle_username db_user,

l.os_user_name os_user,

s.machine,

s.schemaname,

o.object_name tablename,

q.sql_text

from v$locked_object l, v$session s, v$sql q, all_objects o

where l.session_id=s.sid and

s.type='USER' and

s.sql_address=q.address and

l.object_id=o.object_id

alter system kill session 'sid,SERIAL#'

1.having 子句的用法

having 子句对 group by 子句所确定的行组进行控制,having 子句条件中只允许涉及常量,聚组函数或group by

子句中的列.

2.外部联接"+"的用法

外部联接"+"按其在"="的左边或右边分左联接和右联接.若不带"+"运算符的表中的一个行不直接匹配于带"+"预算符的表中的任何行,则前者的行

与后者中的一个空行相匹配并被返回.若二者均不带’+’,则二者中无法匹配的均被返回.利用外部联接"+",可以替代效率十分低下的 not in

运算,大大提高运行速度.例如,下面这条命令执行起来很慢

代码:

select a.empno from emp a where a.empno not in

(select empno from emp1 where job=’SALE’);

倘若利用外部联接,改写命令如下:

代码:

select a.empno from emp a ,emp1 b

where a.empno=b.empno(+)

and b.empno is null

and b.job=’SALE’;

可以发现,运行速度明显提高.

3.删除表内重复记录的方法

可以利用这样的命令来删除表内重复记录:

代码:

delete from table_name a

where rowid< (select max(rowid) from table_name

where column1=a.column1 and column2=a.column2

and colum3=a.colum3 and ...);

问：用ORACLE的like(匹配操作命令)操作时，要查的条件含有特殊符号(_或%)，该怎样写？

如我要找出以tt_开头的表，若安以下写法只能取出以tt开头的表，

因为_在like中用意是任意单一字符。

代码:

SELECT Tname FROM tab

WHERE Tname like 'tt_％'

答：SELECT * FROM tab

WHERE tname LIKE 'TT/_%' ESCAPE '/'

自增字段：

ORACLE一般的做法是同时使用序列和触发器来生成一个自增字段.

CREATE SEQUENCE SEQname

INCREMENT BY 1

START WITH 1

MAXVALUE 99999999

/

CREATE TRIGGER TRGname

BEFORE INSERT ON table_name

REFERENCING

NEW AS :NEW

FOR EACH ROW

Begin

SELECT SEQname.NEXTVAL

INTO :NEW.FIELDname

FROM DUAL;

End;

/

动态sql:

在oracle8.1.5中：

用execute immediate来实现

declare

tsql varchar2(200);

begin

tsql:='insert into '||tname||'values ('aaa','bbb')';

execute immediate tsql;

end;

/

说明：复制表(只复制结构,源表名：a 新表名：b)

SQL: select * into b from a where 1<>1

说明：拷贝表(拷贝数据,源表名：a 目标表名：b)

SQL: insert into b(a, b, c) select d,e,f from b;

说明：显示文章、提交人和最后回复时间

SQL: select a.title,a.username,b.adddate from table a,(select

max(adddate) adddate from table where table.title=a.title) b

说明：外连接查询(表名1：a 表名2：b)

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a =

b.c

说明：日程安排提前五分钟提醒

SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5

说明：两张关联表，删除主表中已经在副表中没有的信息

SQL:

delete from info where not exists ( select * from infobz where

info.infid=infobz.infid )

说明：--

SQL:

代码:

SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE

FROM TABLE1,

(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE

FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND

FROM TABLE2

WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X,

(SELECT NUM, UPD_DATE, STOCK_ONHAND

FROM TABLE2

WHERE TO_CHAR(UPD_DATE,'YYYY/MM') =

TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') &&

'/01','YYYY/MM/DD') - 1, 'YYYY/MM') ) Y,

WHERE X.NUM = Y.NUM (+)

AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B

WHERE A.NUM = B.NUM

说明：--

SQL:

select * from studentinfo where not exists(select * from student where

studentinfo.id=student.id) and 系名称='"&strdepartmentname&"' and

专业名称='"&strprofessionname&"' order by 性别,生源地,高考总成绩

说明：

从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源)

SQL:

SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS

telyear,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '01', a.factration)) AS JAN,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '02', a.factration)) AS FRI,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '03', a.factration)) AS MAR,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '04', a.factration)) AS APR,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '05', a.factration)) AS MAY,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '06', a.factration)) AS JUE,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '07', a.factration)) AS JUL,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '08', a.factration)) AS AGU,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '09', a.factration)) AS SEP,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '10', a.factration)) AS OCT,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '11', a.factration)) AS NOV,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '12', a.factration)) AS DEC

FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration

FROM TELFEESTAND a, TELFEE b

WHERE a.tel = b.telfax) a

GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy')

说明：四表联查问题：

SQL: select * from a left inner join b on a.a=b.b right inner join c on

a.a=c.c inner join d on a.a=d.d where .....

说明：得到表中最小的未使用的ID号

SQL:

代码:

SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1)

THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID

FROM Handle

WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)