【总结】oracle零散知识点搜集

转载注明出处： 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/