转载注明出处: http://renjie120.iteye.com/
1.查询字段中的含有'_'的条目 ,因为_在like语句中本来表示了任意的字符,这里就要特殊处理:
--查询职员id中含有_的数据
SELECT * FROM emp_t
WHERE emp_id LIKE '%/_%'ESCAPE'/'
2.把一个表放在内存里
alter table tablename cache.
3.使用decode函数很重要! 下面的sign()使用的很巧妙,用来判断数字的大小
--按年龄段(小于 20,20-30,---)统计人数,我可以用下面的语句,在一个sql中就搞定!!
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;
4.需要查找在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;
5.查询一个表中的重复记录:
select rowid, 字段
from 表名
where 表名.rowid !=
(select max(rowid) from 表名 别名 where 表名.字段 = 别名.字段)
6.设置字段如果是varchar2(100),允许100个英文和50个汉字,但是修改为varchar2(100 char)之后,可以允许100个汉字和100个英文了。---------有错,实际:varchar2(1000)实际存储汉字少于500!
ALTER TABLE RP_PROJECT_INFO_T MODIFY(CONSTRUCTION_SCALE varchar2(100 CHAR));
7.关于long字段类型和varchar2类型的问题
varchar2最大为4000字节长度,原以为是可以存储2000个汉字,实际不会这样!甚至可能远远小于这些。可以选择long类型来存储字符串,但是long字符串有很多的问题,例如:
1、一个表中只能包含一个 LONG 类型的列。
2、不能索引LONG类型列。
3、不能将含有LONG类型列的表作聚簇。
4、不能在SQL*Plus中将LONG类型列的数值插入到另一个表格中,如insert into …select。
5、不能在SQL*Plus中通过查询其他表的方式来创建LONG类型列,如create table as select。
6、不能对LONG类型列加约束条件(NULL、NOT NULL、DEFAULT除外),如:关键字列(PRIMARY KEY)不能是 LONG 数据类型。
7、LONG类型列不能用在Select的以下子句中:where、group by、order by,以及带有distinct的select语句中。
8、LONG类型列不能用于分布查询。
9、PL/SQL过程块的变量不能定义为LONG类型。
10、LONG类型列不能被SQL函数所改变,如:substr、instr。
long的特性是:
1、LONG 数据类型中存储的是可变长字符串,最大长度限制是2GB。
2、对于超出一定长度的文本,基本只能用LONG类型来存储,数据字典中很多对象的定义就是用LONG来存储的。
3、LONG类型主要用于不需要作字符串搜索的长串数据,如果要进行字符搜索就要用varchar2类型。
4、很多工具,包括SQL*Plus,处理LONG 数据类型都是很困难的。
5、LONG 数据类型的使用中,要受限于磁盘的大小。
插入一个超出2000多的汉字,使用下面的方法:
ps = conn.prepareStatement(sql.toString());
String temp = “字符串。。。。”
ps.setCharacterStream(1, new StringReader(temp), temp.length());
从一个long表中插入到另外一个long字段的表,使用to_lob():下面示例
SQL>create table tlong(itemcd number(30),itemdesc long);
/
Table created.
SQL>Create table tlob(ItemCd Number(30),Itemdesc clob);
Table created
Now dump some values from some table into table tlong
SQL>insert into tlong select icode,iname from InvTab;
2000 rows created.
Now try to insert into lob table from long table
SQL>Insert into tlob select itemcd,TO_LOB(itemdesc) from tlong
2000 rows created.
找了很久,似乎没有直接把long字段的字符串形式读取出来,也就是to_char(long字段)不好用!!这也是不推荐使用long字段的原因之一!!
总之:oracle推荐不使用long字段!应该使用clob字段...
8.将oracle表中的long字段转换为varchar2字段 (中秋通宵一晚的收获就在此了)
--创建临时表,注意使用了to_lob()函数
create table 临时表 as
select to_lob(t.long字段) 别名, t.主键
from 原表 t;
--在临时表中添加一个varchar2字段
alter table 临时表 add (testvarchar2 varchar2(4000));
--将临时表里面的long里面的值copy到varchar2字段中去!!
update 临时表 s2 set (testvarchar2)=(select 别名 from
(select 别名,主键 from 临时表) s1 where s2.主键=s1.主键);
--将原表中的long字段清空
update 原表 t set t.long字段 = null;
--修改原来的long字段类型为varchar2类型
ALTER TABLE 原表 MODIFY(long字段 varchar2(4000));
--从临时表中的varchar2类型拷贝到原表中的新的varchar2字段中去!
update 原表 t set t.long字段 = (select ss.testvarchar2 from 临时表 ss where ss.主键 = t.主键)
将varchar2字段转换为long字段类型,也要借助临时表:
--创建临时表
create table 临时表 as
select t.varchar2字段, t.主键 from 原表 t ;
--清空原表中的varchar2字段值
update 原表 t
set t.varchar2字段 = null ;
--修改原表字段类型为long
ALTER TABLE 原表 MODIFY(varchar2字段 long);
--插入临时表中存储的varchar2字段到long值中去!
update 原表 t set t.varchar2字段 =
(select tt.varchar2字段
from 临时表 tt
where tt.主键 = t.主键);
应该再在后面加一个删除临时表的操作。。。drop就ok 了。。
9.修改数据库默认连接数以及session的连接数的相关sql语句:
1. 查看processes和sessions参数
SQL> show parameter processes
NAME TYPE VALUE
db_writer_processes integer 1
gcs_server_processes integer 0
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 50
SQL> show parameter sessions
NAME TYPE VALUE
license_max_sessions integer 0
license_sessions_warning integer 0
logmnr_max_persistent_sessions integer 1
sessions integer 60
shared_server_sessions integer
2. 修改processes和sessions值
SQL> alter system set processes=300 scope=spfile;
系统已更改。
SQL> alter system set sessions=335 scope=spfile;
系统已更改。
3. 修改processes和sessions值必须重启oracle服务器才能生效
ORACLE的连接数(sessions)与其参数文件中的进程数(process)有关,它们的关系如下:
sessions=(1.1*process+5)
查询语句:
下面是相关查询语句:
查询数据库当前进程的连接数:
select count(*) from v$process;
查看数据库当前会话的连接数:
select count(*) from v$session;
查看数据库的并发连接数:
select count(*) from v$session where status='ACTIVE';
查看当前数据库建立的会话情况:
select sid,serial#,username,program,machine,status from v$session;
查询数据库允许的最大连接数:
select value from v$parameter where name = 'processes';
或者:show parameter processes;
修改数据库允许的最大连接数:
alter system set processes = 300 scope = spfile;
(需要重启数据库才能实现连接数的修改)
重启数据库:
shutdown immediate;
startup;
查看当前有哪些用户正在使用数据:
select osuser,a.username,cpu_time/executions/1000000||'s',sql_fulltext,machine
from v$session a,v$sqlarea b
where a.sql_address = b.address
order by cpu_time/executions desc;
10.在oracle里面删除重复行:
第一种:使用over()函数:
我的一个应用里面有表:money_detail_t,其中记账时间,记账金额,记账类型三者如果一样就视为重复!
下面的语句可以得到每条数据是否有重复的,并排序输出:
select m.money_time,
m.money,
m.money_type,
row_number() over(partition by money_time, money, money_type order by money_time) row_flag
from money_detail_t m
最后一列row_flag就是如果得到同样的数据行就会进行统计,结果如下:
money_time money money_type row_flag
20110102 10 A 1
20110102 10 A 2
20110102 10 A 3
20110102 10 B 1
20110102 10 C 1
删除:根据主键money_sno删除即可...
delete from money_detail_t
where money_sno in (select money_sno
from (select m.money_sno,
row_number() over(partition by money_time, money, money_type order by money_time) row_flag
from money_detail_t m)
where row_flag > 1);
第二种删除重复行数据:
利用rowid:
delete from vitae a
where (a.peopleId, a.seq) in (select peopleId, seq
from vitae
group by peopleId, seq
having count(*) > 1)
and rowid not in (select min(rowid)
from vitae
group by peopleId, seq
having count(*) > 1)
11.看看over()函数还可以用来做什么?
create table t_test(
tid int, //序列号
tname varchar2(20), //名字
tsalary number(8,2), //工资
tdeptno int, //部门
primary key(tid)
);
begin
insert into t_test values(1,'小王',4500.21,3);
insert into t_test values(2,'小张',4200,3);
insert into t_test values(3,'小K',3000,3);
insert into t_test values(4,'小Q',8500.5,4);
insert into t_test values(5,'小T',1520.5,4);
insert into t_test values(6,'小丁',3000,5);
insert into t_test values(7,'小李',3000,5);
insert into t_test values(8,'小KK',3000,5);
END;
--求工资占部门总工资额的比率
select tname, tsalary,tsalary/sum(tsalary) over(partition by tdeptno) per from t_test
不用over()实现上面的结果:
select a.tname,a.tsalary,a.tsalary/b.ttl per,a.tdeptno
from t_test a,(select tdeptno,sum(tsalary) ttl from t_test group by tdeptno) b
where a.tdeptno=b.tdeptno
得到每个人在全部公司里面的工资排名 :
--dense_rank()l是连续排序,有两个第二名时仍然跟着第三名
select dense_rank() over(order by tsalary desc) ser,tname,tsalary,tdeptno from t_test
--rank()是跳跃排序,有两个第二名时接下来就是第四名
select rank() over(order by tsalary desc) ser,tname,tsalary,tdeptno from t_test
得到每个人在各个部门里面 的工资排名:
select dense_rank() over(partition by tdeptno order by tsalary desc) ser,tname,tsalary,tdeptno from t_test
select rank() over( partition by tdeptno order by tsalary desc) ser,tname,tsalary,tdeptno from t_test
实现汇总:
全部公司的汇总
select tname,tsalary,tdeptno,sum(tsalary)over(partition by null ) ttl from t_test
12.利用序列和触发器创建自动递增的主键列:
CREATE OR REPLACE TRIGGER tri_create_task
BEFORE INSERT ON task
FOR EACH ROW
BEGIN
SELECT TASKSEQ.NEXTVAL
INTO :NEW.taskid
FROM DUAL;
END ;
13.case when语句:
select u.id,u.realname,U.SEX from users u;
查询结果如下
ID REALNAME SEX
1 10082 松XX
2 10084 林XX 1
3 10087 西XX
4 10100 胡XX
5 10102 龙XX 1
上表结果中的"sex"是用代码表示的,希望将代码用中文表示。可在语句中使用CASE语句。
select u.id,u.realname,U.SEX, ( case u.sex when 1 then '男' when 2 then '女' else '空的' END ) 性别 from users u; |
14.查询表的使用大小:
select segment_name, bytes/1024/1024
from user_segments
where segment_type = 'TABLE'
and segment_name like '%OA_ERRORS%';
15.添加oracle函数进行字符串拆分:
* Oracle 创建 split 和 splitstr 函数
*/
/* 创建一个表类型 */
create or replace type tabletype as table of VARCHAR2(32676)
/
/* 创建 split 函数 */
CREATE OR REPLACE FUNCTION split (p_list CLOB, p_sep VARCHAR2 := ',')
RETURN tabletype
PIPELINED
/**************************************
* Name: split
* Author: Sean Zhang.
* Date: 2012-09-03.
* Function: 返回字符串被指定字符分割后的表类型。
* Parameters: p_list: 待分割的字符串。
p_sep: 分隔符,默认逗号,也可以指定字符或字符串。
* Example: SELECT *
FROM users
WHERE u_id IN (SELECT COLUMN_VALUE
FROM table (split ('1,2')))
返回u_id为1和2的两行数据。
**************************************/
IS
l_idx PLS_INTEGER;
v_list VARCHAR2 (32676) := p_list;
BEGIN
LOOP
l_idx := INSTR (v_list, p_sep);
IF l_idx > 0
THEN
PIPE ROW (SUBSTR (v_list, 1, l_idx - 1));
v_list := SUBSTR (v_list, l_idx + LENGTH (p_sep));
ELSE
PIPE ROW (v_list);
EXIT;
END IF;
END LOOP;
END;
/
/* 创建 splitstr 函数 */
CREATE OR REPLACE FUNCTION splitstr (str IN CLOB,
i IN NUMBER := 0,
sep IN VARCHAR2 := ','
)
RETURN VARCHAR2
/**************************************
* Name: splitstr
* Author: Sean Zhang.
* Date: 2012-09-03.
* Function: 返回字符串被指定字符分割后的指定节点字符串。
* Parameters: str: 待分割的字符串。
i: 返回第几个节点。当i为0返回str中的所有字符,当i 超过可被分割的个数时返回空。
sep: 分隔符,默认逗号,也可以指定字符或字符串。当指定的分隔符不存在于str中时返回sep中的字符。
* Example: select splitstr('abc,def', 1) as str from dual; 得到 abc
select splitstr('abc,def', 3) as str from dual; 得到 空
**************************************/
IS
t_i NUMBER;
t_count NUMBER;
t_str VARCHAR2 (4000);
BEGIN
IF i = 0
THEN
t_str := str;
ELSIF INSTR (str, sep) = 0
THEN
t_str := sep;
ELSE
SELECT COUNT ( * )
INTO t_count
FROM table (split (str, sep));
IF i <= t_count
THEN
SELECT str
INTO t_str
FROM (SELECT ROWNUM AS item, COLUMN_VALUE AS str
FROM table (split (str, sep)))
WHERE item = i;
END IF;
END IF;
RETURN t_str;
END;
/
16、行列转,10g中换有一个函数: wmsys.wm_concat
17、查看oracle 数据库版本:select * from v$version;
18、关于job定时任务:
创建JOB执行存储过程:
#1,--创建Job
variable update_order_job number;
begin
dbms_job.submit(:update_order_job, 'PROCE_UPDATE_ORDER;', sysdate, 'sysdate+1/1440');--每1执行PROCE_UPDATE_ORDER存储过程
end;
#2,--创建Job或:
declare update_order_job number;
begin
dbms_job.submit(update_order_job, 'PROCE_UPDATE_ORDER;', sysdate, 'sysdate+1/1440');--每1执行PROCE_UPDATE_ORDER存储过程
end;
#3,删除Job
begin
dbms_job.remove(23);--和select * from user_jobs; 中的job值对应,看what对应的过程
end;
#4,执行Job
begin
dbms_job.run(24);
end;
#5,查看Job
select * from user_jobs;
19、导出oracle下面的全部序列:
通过序列的系统表导出一段sql
select 'CREATE SEQUENCE '||t.sequence_name||' minvalue '||t.min_value||' maxvalue'||' '||t.max_value||' increment by '||t.increment_by
||' start with '||(t.last_number)||' nocache order nocycle;' from user_sequences t
20、查询数据库全部表名:
select * from all_tab_comments t where t.OWNER = 'HOLIDAYBAK'
21、求数据库的时间间隔天数:
求时间间隔的天数:本来在以前的代码中使用的是ceil (ROOM_DATE-sysdate)结果是报数据类型不兼容(在oracle9i上面好用,10g上面不好用)。。。改成下面的方式就ok了。。
select trunc(ROOM_DATE) - trunc(sysdate) shijiancha,
to_char(ROOM_DATE, 'yyyy/mm/dd') dd
from HOTEL_ROOM_ITEM
order by ROOM_DATE desc
转载注明出处: http://renjie120.iteye.com/