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 更改数据库时间显示格式: 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来限制查询所返回的行数? 软件环境: 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 1000SQL> 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_idalter 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’; 可以发现,运行速度明显提高.
sql精华[推荐]
提供人昵称:与或 作者:与或 --------------------------------------------------------------------------------
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)
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
)
/
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来限制查询所返回的行数? 软件环境:
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 1000SQL> 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_idalter 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’; 可以发现,运行速度明显提高.