sqlserver和oracel的使用心得记录（语句技巧和错误解决）

sqlserver :

“for”错：

USE [master]
GO
EXEC dbo.sp_dbcmptlevel @dbname=数据库名, @new_cmptlevel=90
GO

查询表显示一直在查询而不出数据

--原因表死锁了

-- 查询死锁
select    
    request_session_id spid,   
    OBJECT_NAME(resource_associated_entity_id) tableName    
from    
    sys.dm_tran_locks   
where    
    resource_type='OBJECT' 

 --杀死死锁进程
kill 354 

ORACEL:

 

ora-00054:资源正忙：

select t2.username,t2.sid,t2.serial#,t2.logon_time

from v$locked_object t1,v$session t2

where t1.session_id=t2.sid order by t2.logon_time;

alter system kill session 'sid,serial#';

 

查看安装的Oracle客户端安装的是多少位：
在%ORACLE_HOME%\inventory\ContentsXML目录下面找到comps.xml文件，

 

例如我安装的路径为C:\Oracle_Client\Client64\product\11.2.0\client_1\inventory\ContentsXML\comps.xml
在这个文件中搜索：
如果找到PLAT="NT_AMD64"表示安装的是64位的Oracle 客户端，如果找到PLAT="NT_X86"则表示安装的是32位的Oracle客户端。

 

oracel语句：存储过程返回数据集：

--利用游标返回结果集
CREATE OR REPLACE PACKAGE pkg_query AS TYPE cur_query IS REF CURSOR;
END pkg_query;


CREATE OR REPLACE PROCEDURE UP_UserInfo_List_select
       (
        WhereSql         in  varchar2,   --查询条件
        StartIndex       in  Number,  --当前页
        EndIndex         in  Number,  --每页显示记录条数
        RecordCount      out Number,
        v_cur            out SYS_REFCURSOR)   --返回的结果集
IS
   v_sql VARCHAR2(1000) := '';      --sql语句
BEGIN
   --查总记录数
   v_sql := 'SELECT TO_NUMBER(COUNT(*)) FROM UserInfo WHERE 1=1';
   IF WhereSql IS NOT NULL or WhereSql <> '' THEN
       v_sql := v_sql || WhereSql;
   END IF;
   EXECUTE IMMEDIATE v_sql INTO RecordCount;

   --实现分页查询
   v_sql := 'SELECT * FROM (SELECT A.*, rownum r FROM (SELECT * FROM UserInfo ';
   IF WhereSql IS NOT NULL or WhereSql <> '' THEN
       v_sql := v_sql || ' WHERE 1=1' || WhereSql || ' ORDER BY UserId';
   END IF;

   v_sql := v_sql || ') A WHERE rownum <= ' || EndIndex || ') B WHERE r >= '|| StartIndex;
   DBMS_OUTPUT.put_line(v_sql);
   
   OPEN v_cur FOR v_sql;
   
END UP_UserInfo_List_select;

--执行，plsql这里是看不到结果集，找到这个过程点测试，才能看到。
declare
RecordCount number :=0;
v_cur pkg_query.cur_query;
begin
  UP_UserInfo_List_select('',1,10,RecordCount,v_cur);
  DBMS_OUTPUT.put_line(RecordCount);
end;

  组内排序：

select * from (
select  rank() over (partition by category order by category,sum(price) desc,goodsno) as r,
category,goodsno,SUM(price) amount
from TestCate
group by category,goodsno
)tt where  tt.t<=1

oracel实现split，将字符串转成table

 SELECT REGEXP_SUBSTR('ZJ03800,D01014', '[^,]+', 1, LEVEL, 'i') AS STR  
  FROM DUAL  CONNECT BY LEVEL <=  
  LENGTH('ZJ03800,D01014') - LENGTH(REGEXP_REPLACE('ZJ03800,D01014', ',', ''))+1

oracel savepoint的特别好用点

insert into t values (2,'Tough2');
//创建savepoint t2
savepoint t2;

//创建savepoint t3
update t set name='Tough' where id=2;
savepoint t3;

//回滚到update之前的状态，savepoint t3丢失
rollback to savepoint t2; //回滚点：从此处到t2之间的，不包括声明t2之前的

commit;//之后所有的savepoint都将被删除

 用处：当存储过程中带有事务，并且有rollback时。在程序代码中调用该过程并且程序中也有事务时，存储过程中的rollback会把所有的曾删改都回滚，包括程序代码中的。那么此时  为了不影响该存储过程的使用和效果，又不会把我们程序代码中做的增删改回滚的话，那就可以在该过程中加入回滚点，只回滚过程中自身需要回滚的代码

通用：

IN和Exists

 IN是把外表和内表作hash 连接，而Exists是对外表作loop循环，每次loop循环再对内表进行查询。
如果查询的两个表大小相当，那么用IN和Exists差别不大。如果两个表中一个较小，一个是大表，则子查询表大的用Exists，子查询表小的用IN：

例如：表A（小表），表B（大表）：
　　select * from A where cc in (select cc from B)
效率低，用到了A表上cc列的索引；
　　select * from A where exists(select cc from B where cc=A.cc) 
效率高，用到了B表上cc列的索引。

相反的：
　　select * from B where cc in (select cc from A)
效率高，用到了B表上cc列的索引；
　　select * from B where exists(select cc from A where cc=B.cc)
效率低，用到了A表上cc列的索引。

not IN 和not Exists如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not Exists都比not IN要快

update语句不同写法 的 不同效果：

--只会更新子表与主表共有的字段
update stg.mytest
set val = tmp.name
from stg.mytest_2 tmp
where tmp.id = stg.mytest.id;

--更新主表所有字段，若子表中不存在则主表的更新字段为null
update stg.mytest set val = (select name from stg.mytest_2 where stg.mytest_2.id = stg.mytest.id)