游标和过程
打开游标或存储过程后，如果要使用COMMIT 或ROLLBACK 语句必须十分小心，因为这两个语句将关闭游标或存储过程。
Oracle游标运行过程中，如果表数据有删除或修改的话，游标取的值是打开游标时的旧结果。
查询表某一时间点的状态
写在where之前 as of timestamp to_timestamp('2016-10-20 14:12:00', 'yyyy-mm-dd hh24:mi:ss')
Group by
注意group by应为nvl(ms_mzxx.yqsb, 1)，而不是ms_mzxx.yqsb
select yygh_ghjl.ksdm as ksdm,
nvl(ms_mzxx.yqsb, 1) as yqsb,
count(ms_mzxx.mzxh) as jcz
from ms_mzxx, yygh_ghjl
where ms_mzxx.sfrq >= to_date('ldt_begin', 'yyyy-mm-dd hh24:mi:ss')
and ms_mzxx.sfrq <= to_date('ldt_end', 'yyyy-mm-dd hh24:mi:ss')
and ms_mzxx.brxz = 17
and ms_mzxx.ghgl = yygh_ghjl.ghlsh
group by yygh_ghjl.ksdm,ms_mzxx.yqsb
group by的顺序和select后的列的顺序不一定一致，也可以group by的列比查询的列多。
显式使用索引
select /*+index(t2 idx_cf01_fphm)*/
算序号
ROW_NUMBER() OVER(PARTITION BY 分组字段 ORDER BY 排序字段)
Rank() OVER(PARTITION BY 分组字段 ORDER BY 排序字段)
查看oracle版本
select * from v$version
exists
子句中也可以使用union all，如在查询开有单据的就诊记录中就可以用到。
比较两个库之间的表结构
PL/SQL的工具->比较用户对象，可以比较两个库之间的表结构的不同，并生成对目标回话的执行脚本。
判断是否是数值
trim(translate(sjfp,'0123456789',' '))
Lag和Lead分析函数
可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列。
这种操作可以代替表的自联接，并且LAG和LEAD有更高的效率。
如查空出的mzxh：
select mzxh, lag(mzxh, 1, 0) over(order by mzxh) as prio
from ms_mzxx
where sfrq >= to_date('2015-07-12 11:10:11', 'yyyy-mm-dd hh24:mi:ss')
and sfrq <= to_date('2015/7/13 16:20:23', 'yyyy-mm-dd hh24:mi:ss'))
查询锁
SELECT /*+ rule */ Lpad(' ', Decode(l.Xidusn, 0, 3, 0)) || l.Oracle_Username User_Name,
o.Owner, o.Object_Name, s.Sid, s.Serial# ,s.PROGRAM , s.MACHINE ,'alter system kill session '''||s.Sid ||','||s.Serial# ||'''' ,0 as will_select
, DECODE (l.LOCKED_MODE,
0, 'None',
1, 'NULL',
2, '行共享锁',
3, '行排他锁',
4, '表结构锁',
5, '共享行排他锁',
6, '完全排他锁',
TO_CHAR (l.LOCKED_MODE)
) mode_held
FROM V$locked_Object l, Dba_Objects o, V$session s
WHERE l.Object_Id = o.Object_Id
AND l.Session_Id = s.Sid
ORDER BY o.Object_Id, Xidusn DESC
外键
1. 禁用所有外键约束
select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R';
2. 启用所有外键约束
select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R';
3. 删除所有外键约束
select 'alter table '||table_name||' drop constraint '||constraint_name||';' from user_constraints where constraint_type='R';
update
当update字符串类型字段的值中包含单引号时，将单引号写成两个单引号，才能将单引号存入库中。
过程
create or replace procedure pro_cur_xmmx(cur_xmmx out sys_refcursor,
yjxh in number) is
ll_yjxh number;
begin
ll_yjxh := yjxh;
open cur_xmmx for
select ylxh, yldj, ylsl from ms_yj02 where yjxh = ll_yjxh;
end;
存储过程中可以使用参数 in/out/inout，out参数中的sys_refcursor类型是返回过程体中游标的结果集。
PB中的调用方式如下：
declare pro_cur_xmmx procedure for pro_cur_xmmx( yjxh=>:ll_yjxh);
execute pro_cur_xmmx;
do while sqlca.sqlcode = 0
fetch pro_cur_xmmx into :ll_ylxh, :ldc_yldj, :ll_ylsl ;
mle_1.text += string(ll_ylxh) + string (ldc_yldj) + string(ll_ylsl)
loop
close pro_cur_xmmx;
查看包的内容 select * from all_source
可以查看过程或者函数的文本内容
查看视图内容
All_views中text为Long类型，需要创建中间表，使用to_lob()，转换为clob进行查询。
视图的数据类型
His中创建视图其中一列是返回类型是字符串的函数，第三方创建新的视图中直接使用该列，结果his视图中该列数据类型是varchar2(4000)，而第三方视图中该列数据类型是char，最后第三方的存储过程中使用游标获取该列再赋值时，存储过程卡死，显示该列数据类型为long value。尝试使用cast转换his和第三方视图中列的数据类型，莫名其妙好了，再将视图修改为原来类型不能重现错误……
Ora-12170连接超时
监听和服务都没有问题的情况下考虑设置入站规则
函数中记得处理异常
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END;
Order by 和rownum的顺序
如果按主键排序则是先执行排序再执行rownum，如果排序字段不是主键则是先执行rownum再排序。但是在测试数据量大的表时并不准确可能跟表的存储有关，最好是嵌套一层。
更新特殊字符（回车、制表符）
update GY_fybm
set pydm = replace(pydm, chr(10), ''), fymc = replace(fymc, chr(10), '')
where fymc like '%' || chr(10) || '%'
or pydm like '%' || chr(10) || '%'
赋予函数权限
grant execute on fun_name to user;
恢复drop表（drop要慎重啊）
flashback table COMM.EXAM_DRUG_GROUP_DETAIL to before drop
唯一约束（旧值有重复）
先创建索引
create index idx_bws_wi_id_unit_code on bs_warehouse_store (wi_id,unit,pc_cate_code) ;
创建完成后在执行语句
alter table bs_warehouse_store add constraint unq_wi_id_unit_code unique(wi_id,unit,pc_cate_code) enable novalidate;
DBMS_LOB. Append(clob,clob or varchar)
两个参数均不能为空串，适用于大字符串拼接。||可以拼接空串，不会返回空。
检索blob的内容
dbms_lob.instr(列名称,utl_raw.cast_to_raw('所要匹配的内容'),1,1)<>0
扩充分区表
select 'ALTER TABLE ' || (tab_par.table_name) ||
' ADD PARTITION DATE2020' ||
' VALUES LESS THAN (TO_DATE(''2021-1-1'',''YYYY-MM-DD''))' ||
' TABLESPACE ' || tab_par.tablespace_name || ';'
from (select table_name,max(tablespace_name) as tablespace_name
from dba_tab_partitions
where table_owner = '大写用户名'
group by table_name) tab_par;
表空间使用情况
SELECT a.tablespace_name,
a.bytes / (1024 * 1024 * 1024) total,
b.bytes / (1024 * 1024 * 1024) used,
c.bytes / (1024 * 1024 * 1024) free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name
order by "% FREE ";
解析XML
select extractvalue(xmltype(xml字符串),'root/output/sign') from dual;
to_char(‘’,’000000000’)
格式化完字符串前会多加个空格
会话的历史SQL
v$active_session_history
BlobToClob
CREATE OR REPLACE FUNCTION BlobToClob(blob_in IN BLOB) RETURN CLOB AS
v_clob CLOB;
v_varchar VARCHAR2(10000);
v_start PLS_INTEGER := 1;
v_buffer PLS_INTEGER := 10000;
BEGIN
DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
FOR i IN 1 .. CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer) LOOP
v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start));
DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
--DBMS_OUTPUT.PUT_LINE(v_varchar);
v_start := v_start + v_buffer;
END LOOP;
RETURN v_clob;
END BlobToClob;
Union
Union的每一部分都会去重。。
Clob和blob不能同时更新
字符串转blobRawtohex(字符串)
数值精度
Cast(字段名 as number(10,2))
关闭dblink
Alter session close database link dblink名称;
连接SQL server
Odbc+initdg4odbc.ora，如果sql server中字段类型为nvarchar(max)，会丢失该字段。
分区表
设置interval可以自动增加分区，通过查看表定义可以看出是否自动增加分区。
Dbms_metadata.get_ddl(‘TABLE’,表名称,用户名)