Oracle PL/SQL技巧总结

游标和过程

打开游标或存储过程后，如果要使用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’,表名称,用户名)