Oracle笔记

过滤字母：

regexp_replace(emissions_name,'[A-Za-z]') 

 

精确到一位小数： 

to_char(vv1.val,'FM999990.0')

 

生成自增长排序：

row_number()over(partition by  1 order by data_id)  -- 在表中根据data_id生成一个自增长排序

 

查表的占用空间：

SELECT segment_name AS TABLENAME,round(sum(BYTES)/1024/1024,2)||'M' FROM user_segments WHERE segment_name='FDW_TERM_SUB_MODEL_CITYAREA'
group by segment_name;

 

同义词：

create or replace synonym ip_natural_person for ods.ip_natural_person;

--和视图的功能类似。就是一种映射关系。

 

找出数据中每月最后一天的数据：

可以使用not exist

select * from test a where not exist(select * from test b where a和b的关联条件 and b>a)

 

授权：

grant select on proc_ods_22.ip_org to proc_dw_22;

 

实现序列号： http://www.linuxidc.com/Linux/2012-05/59702.htm

1. 创建 Sequence

使用如下命令新建sequence(用户需要有CREATE SEQUENCE 或者CREATE ANY SEQUENCE权限):

CREATE  SEQUENCE test_sequence 
 INCREMENT  BY   1 　  --  每次加的个数据  
 START  WITH   1 　　  --  从1开始计数  
 NOMAXVALUE　　　   --  不设置最大值  
 NOCYCLE　　　　　   --  一直累加，不循环  
 CACHE  10 ;  
 
[注意] 
如果设置了CACHE值，ORACLE将在内存里预先放置一些sequence，以使存取速度更快。cache里面的取完后，oracle自动再取一组到cache。 但是，使用cache可能会跳号， 当遇到数据库突然异常down掉（shutdown abort),cache中的sequence就会丢失. 
因此，推荐在create sequence的时候使用 nocache 选项。

2. 使用 sequence:

sequence.CURRVAL    -- 返回 sequence的当前值 
sequence.NEXTVAL    -- 增加sequence的值，然后返回 sequence 值

 

 

对比找出表数据的不同：

select count(1) from
(select * from DIM_CUSTOMER MINUS
select * from DIM_CUSTOMER_bak);

 

使用dump来查询数据类型和ASCLL码：

select dump(stu_name) from student;

 

查表的主键：

SELECT
COL.COLUMN_NAME
FROM
USER_CONSTRAINTS CON,USER_CONS_COLUMNS COL
WHERE
CON.CONSTRAINT_NAME=COL.CONSTRAINT_NAME AND CON.CONSTRAINT_TYPE='P'
AND COL.TABLE_NAME='DIM_CUSTOMER';  --表名填要查的表

函数：

rtrim　　删除字符串右边的空格后的字符串

nvl　　相当于mysql中的ifnull

instr　　instr函数在Oracle/PLSQL中是返回要截取的字符串在源字符串中的位置   ag:SELECT instr('syranmo','s') FROM dual; -- 返回 1 

 

要插入数据时：

用select * from xxx for update; --会锁整张表

用select t.* , t.rowid from ICONTROL.SCH_TASK_JOB_PROCEDURE t; --只会锁用到的行数

陷阱：

在字段中chr(0)后的东西不显示，ag:select 'abc'||chr(0)||'def' from dual; 结果只显示abc

 

SELECT * FROM user_source t WHERE lower(t.text) LIKE '%vip%' ;    --查哪个程序使用到了该表 

 

查看锁表进程SQL语句： 
select sess.sid, 
    sess.serial#, 
    lo.oracle_username, 
    lo.os_user_name, 
    ao.object_name, 
    lo.locked_mode 
    from v$locked_object lo, 
    dba_objects ao, 
    v$session sess 
where ao.object_id = lo.object_id and lo.session_id = sess.sid; 

 

杀进程：

ALTER SYSTEM KILL SESSION 'sid,serial#'

 

优化表的查询

CREATE OR REPLACE PROCEDURE "RUNSTAT" (pi_owner in varchar2, pi_table_name in varchar2, pi_part_name in varchar2) as
begin
dbms_stats.gather_table_stats(ownname => pi_owner,tabname => pi_table_name, partname => pi_part_name);
end;

 

在结果中输出某信息（用于方便定位错误）

dbms_output.put_line('1'); 

 

将报错输出到某表：

EXCEPTION
WHEN OTHERS THEN
execute immediate('INSERT INTO FDW_DATA_CONTRAST_RESULT_IWB VALUES('||data_id||',''DW校验执行出错,请检查SQL'')');
commit;