标签归档:oracle

oracle通过dblink调用远程的存储过程procedure

oracle通过dblink调用远程的存储过程procedure,并且有传出传入参数的方法如下。

set serveroutput on;
declare 
info2 varchar2(100);
begin 
[email protected]('009','2','2',info2); 
dbms_output.put_line(info2); 
end; 

另:如果远程存储过程有提交commit,会报错,需要在本地做提交操作。

oracle判断后插入数据

有时候用oracle的数据库,插入数据的时候需要判断一下。比如,每天每人只能写一条。
在插入的时候就要先做个判断,查一下是不是当天有此人的操作记录。
就要先

select count(*) from xxx

一下。
这天查了一下,有种写法可以不用先select
本来是

insert 
into form(formid) values(111)

改成

insert 
when (not exists (select 1 from form where formid = 111)) then
into form(formid) select 111 from dual

这样,当数据库里有一个formid=111的记录的时候,就不会进行insert操作了

用oracle的merge实现mysql的replace into

mysql有一个replace into的dml语句,类似insert,但是会在insert之前检查表的唯一索引或主键。如果存在,就改为update操作。
这在很多应用中是一个很常用的操作。有了这个replace into ,就可以将一个 select后判断后做update or insert改为一句话,甚是方便。

oracle9以后(好像)有了merge into,也可以实现类似功能,sql demo如下

merge into qq a
using (select ‘2022’ company_no, ‘cname’ company_name from qq where rownum<2) b on (a.company_no = b.company_no) WHEN MATCHED THEN UPDATE SET a.company_name = a.company_name|| 'a' WHEN NOT MATCHED THEN INSERT (a.company_no, a.company_name) VALUES (b.company_no, b.company_name); [/code]

mysql里面的oracle decode

oracle里面有一个decode的函数,顾名思义,可以将1,2,3按照需求转成a,b,c,最后还有一个缺省值。

mysql里面好像没有这个函数。但是可以用另外的函数来实现类似的效果。

IF(expr1,expr2,expr3)
如果expr1是TRUE(expr1<>;0且expr1<>;NULL),那么IF()返回expr2,否则它返回expr3。IF()返回一个数字或字符串值

oracle的写法

SELECT decode(ttype,1,'a',2,'b',3,'c','d') FROM taba

可以在mysql里写成

SELECT if( ttype =1, 'a', if( ttype =2, 'b', if( ttype =3, 'c', 'd' ) ) ) 
FROM taba

oracle pl/sql身份证15位转15位的function

工作中经常遇到身份证要从15位转换到18位的数据,朋友给了一个这样的oracle,pl/sql的函数。测试过,没问题贴出来,备用。

create or replace function f_15to18(idCardNum varchar2) return varchar2 is
  Result varchar2(20);
i smallint;
num smallint:=0;
sNum varchar2(2);
code char(1);
temp17 varchar2(20);
begin
  
  if length(idCardNum)=18 or idCardNum is null then 
   Result:=upper(idCardNum);
  elsif length(idCardNum)=15 then 
  temp17:=substr(idCardNum,1,6) || '19' || substr(idCardNum,7,9);
  i:=18;
  

  while i>1 loop
  num:=num+mod(power(2,i-1),11)*substr(temp17,19-i,1) ;
  i:=i-1;
  end loop;
  
  num:=mod(num,11);
  
  sNum:=num || '';
  if num=0 then
    code:='1';
    elsif num=1 then
    code:='0';
    elsif num=2 then
    code:='X';
    else
    code:=(12-num) || '';
  end if;
     Result:=temp17 || code;
  else 
     Result:= idCardNum;
  end if;
  return Result;
  exception 
  when others then
  dbms_output.put_line(idCardNum);
  
end f_15to18;
/

oracle的时间格式

查看当前oracle时间的格式
select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';
修改当前session的format
alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
windows修改时间格式并保存
客户端的注册表HKEY_LOCAL_MACHINE->SOFTWARE->ORACLE->HOME0中,如无默认的日期格式,则“新建”->“字符串”,重命名为“NLS_DATE_FORMAT”(注意引号内的字符应大写),并赋值为如:yyyy-mm-dd hh24:mi:ss, 可直接使用,而不需要重启Oracle。

oracel表简单解锁方法

有时候操作数据库会被锁掉,表现就是对表操作的时候没有任何反应。
解锁的方法如下。重复下面的三个sql

SQL>  select sid from v$lock where block=1;

       SID
----------
        20

SQL> select sid,serial# from v$session where sid=20;

       SID    SERIAL#
---------- ----------
        20       2649

SQL> alter system kill session '20,2649';

System altered.

只到没有block=1的sid

SQL> select sid from v$lock where block=1;

no rows selected

感谢wxx

另一个查锁的sql

select l.sid,
       s.username,
       decode(l.type, 'TM', 'TABLE   LOCK', 'TX', 'ROW   LOCK', NULL) LOCK_LEVEL,
       o.owner,
       o.object_name,
       o.object_type,
       s.terminal,
       s.machine,
       s.program,
       s.osuser
  from v$session s, v$lock l, dba_objects o
 where s.sid = l.sid
   and o.object_id = l.id1
   and s.username is not null

删除表中重复数据的sql

一张多用户的通讯记录表,主要是手机信息,每个用户可以建立自己的联系人组。
原来数据对数据没有校验,用户可以在一个组里面输入重复手机号码,如果数据量大,会造成数据重复。
所以要做一个唯一性的索引,如下:

create unique index inx_uniq_cellphone_onegroup on addresslist (cellphone, groupid, operatorid);

目的是保证每个用户的每个组里面,不能有同样的cellphone。
但是建立的时候出错,因为里面原来有重复的数据。
清除重复的数据,用如下的sql,如果重复的就删除rowid不等于max(rowid)的那条记录。

delete addresslist
 where rowid not in (select rowid1
                       from addresslist a,
                            (select cellphone,
                                    groupid,
                                    operatorid,
                                    max(rowid) rowid1,
                                    count(*)
                               from addresslist
                              group by cellphone, groupid, operatorid) b
                      where a.cellphone = b.cellphone
                        and a.groupid = b.groupid
                        and a.operatorid = b.operatorid
                        and a.rowid = b.rowid1)

常见Oracle HINT的用法(转贴)

  1. /*+ALL_ROWS*/
  表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.
  例如:
  SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;
  2. /*+FIRST_ROWS*/
  表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.
  例如:
  SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;
  3. /*+CHOOSE*/
  表明如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐量;
  表明如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法;
  例如:
  SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;

  4. /*+RULE*/
  表明对语句块选择基于规则的优化方法.
  例如:
  SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;

  5. /*+FULL(TABLE)*/
  表明对表选择全局扫描的方法.
  例如:
  SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO=’SCOTT’;

  6. /*+ROWID(TABLE)*/
  提示明确表明对指定表根据ROWID进行访问.
  例如:
  SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>=’AAAAAAAAAAAAAA’
  AND EMP_NO=’SCOTT’;

  7. /*+CLUSTER(TABLE)*/
  提示明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效.
  例如:
  SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
  WHERE DPT_NO=’TEC304′ AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

  8. /*+INDEX(TABLE INDEX_NAME)*/
  表明对表选择索引的扫描方法.
  例如:
  SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX=’M’;

  9. /*+INDEX_ASC(TABLE INDEX_NAME)*/
  表明对表选择索引升序的扫描方法.
  例如:
  SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO=’SCOTT’;

  10. /*+INDEX_COMBINE*/
  为指定表选择位图访问路经,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的布尔组合方式.
  例如:
  SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS
  WHERE SAL<5000000 AND HIREDATE   11. /*+INDEX_JOIN(TABLE INDEX_NAME)*/   提示明确命令优化器使用索引作为访问路径.   例如:   SELECT /*+INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)*/ SAL,HIREDATE   FROM BSEMPMS WHERE SAL<60000;   12. /*+INDEX_DESC(TABLE INDEX_NAME)*/   表明对表选择索引降序的扫描方法.   例如:   SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';   13. /*+INDEX_FFS(TABLE INDEX_NAME)*/   对指定的表执行快速全索引扫描,而不是全表扫描的办法.   例如:   SELECT /*+INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE DPT_NO='TEC305';   14. /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/   提示明确进行执行规划的选择,将几个单列索引的扫描合起来.   例如:   SELECT /*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROM BSEMPMS WHERE EMP_NO='SCOTT' AND DPT_NO='TDC306';   15. /*+USE_CONCAT*/   对查询中的WHERE后面的OR条件进行转换为UNION ALL的组合查询.   例如:   SELECT /*+USE_CONCAT*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';   16. /*+NO_EXPAND*/   对于WHERE后面的OR 或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展.   例如:   SELECT /*+NO_EXPAND*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';   17. /*+NOWRITE*/   禁止对查询块的查询重写操作.   18. /*+REWRITE*/   可以将视图作为参数.   19. /*+MERGE(TABLE)*/   能够对视图的各个查询进行相应的合并.   例如:   SELECT /*+MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELET DPT_NO   ,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO   AND A.SAL>V.AVG_SAL;

  20. /*+NO_MERGE(TABLE)*/
  对于有可合并的视图不再合并.
  例如:
  SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELECT DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL;

  21. /*+ORDERED*/
  根据表出现在FROM中的顺序,ORDERED使ORACLE依此顺序对其连接.
  例如:
  SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;

  22. /*+USE_NL(TABLE)*/
  将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.
  例如:
  SELECT /*+ORDERED USE_NL(BSEMPMS)*/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

  23. /*+USE_MERGE(TABLE)*/
  将指定的表与其他行源通过合并排序连接方式连接起来.
  例如:
  SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

  24. /*+USE_HASH(TABLE)*/
  将指定的表与其他行源通过哈希连接方式连接起来.
  例如:
  SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

  25. /*+DRIVING_SITE(TABLE)*/
  强制与ORACLE所选择的位置不同的表进行查询执行.
  例如:
  SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,[email protected] WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;

  26. /*+LEADING(TABLE)*/
  将指定的表作为连接次序中的首表.

  27. /*+CACHE(TABLE)*/
  当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端
  例如:
  SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

  28. /*+NOCACHE(TABLE)*/
  当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端
  例如:
  SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

  29. /*+APPEND*/
  直接插入到表的最后,可以提高速度.
  insert /*+append*/ into test1 select * from test4 ;

  30. /*+NOAPPEND*/
  通过在插入语句生存期内停止并行模式来启动常规插入.
  insert /*+noappend*/ into test1 select * from test4 ;