SQL Server －－> Oracle

　?基於Oracle??的程式Support SQL Server或?是??而又愉快的;但是?基於SQL
Server??的Support Oracle可不是那???,你可能面?大量接踵而?的???要?此做相
　　Oracle以其良好的?定性和安全性受到了?多DBA?的推崇和好?,但其糟糕的可操作性
和易用性也??大多?的Programmer感到深?痛?.
　　SQL Server的T-SQL是那?的自由?活,Oracle的PL/SQL又是那?的??刻板,SQL Server --> Oracle??你感到那?的不??.
　　你是否花了?整天未曾?通一?300行的sp?……
　　你是否?了一?疑???耽?了好?天工期??此???眠?……
　　如果是,???看下面的?容.
　　一. basic
　　二. 如何返回?果集?
　　三. ?於??表
　　四. ?行??SQL
　　五. 自增的identity如何移植?
　　六. ?於??器
　　七. ???,外??,全??
　　八. 想要F2正常?窗?需做哪些工作?
　　九. Top n?rownum
　　十. 性能?化
　　十一. 其他
　　......
　　二. 如何返回?果集?
　　“天啦.Oracle的sp不能返回?果集!!!”……
　　在SQL ServeràOracle的工作中你是否??出??的?呼?……
　　在Oracle的任何PL/SQL?句?中所有的select?句必?要有into子句!
　　?就是Oracle的sp不能返回?果集的原因!
　　任何的事情都有其解?的?法,你千?不要?此而??.下面的是解?的例程:
　　create or replace package pkg_test
　　as
　　type cur_test is ref cursor; -- 定?一?cursor的type
　　end pkg_test;
　　/
　　create or replace procedure p_test
　　(
　　v_cur out pkg_test.cur_test
　　)
　　as
　　v_sql varchar2(100); --
　　begin
　　v_sql := 'select a1,a2 from test';
　　OPEN v_cur FOR v_sql; --
　　exception
　　when others then
　　DBMS_OUTPUT.PUT_LINE('Error ---------------' sqlcode ' : ' sqlerrm );
　　end p_test;
　　/
　　Java程式:
　　……
　　CallableStatement call = conn.prepareCall("{ call p_test(?) }");
　　call.registerOutParameter(1, OracleTypes.CURSOR);// ??out??的SQL???型
　　call.execute();
　　ResultSet rs=(ResultSet)call.getObject(1);// 取得得?据?果集合
　　while(rs.next())
　　……
　　......
　　三. ?於??表
　　你一定在程式中和SQL Server的sp中??了很多用到??表的地方;……
　　你也一定??了在Oracle的sp中不能create table;……
　　你?一定??了原?Oracle中的??表和SQL Server中的??表根本不是
　　一?概念,它?法?到SQL Server中??表的作用!……
　　?一切都是?什????又?如何解??
　　抱著??多的疑?,我?先?回?一下SQL Server的??表.
　　SQL Server的??表有??:局部??表(#temp)和全局??表(##temp)
　　局部??表是?立於每一?session的,?於每一?session??,就算都create一?同名
的#temp但是每?session都?立操作自己的#temp,互相不干?.session退出後自??放自己的
　　全局??表一旦?建,所有的session都可以使用,只不?只有??建全局??表的session
　　值得一提的是,SQL Server的??表全部都存?在tempdb???中,SQL SERVER6.5有
tempdb in RAM的??,自7.0之後的版本?然取消了??性.但只要你的tempdb分配了足?大的
硬?空?,在多用???操作???表的性能就不?降低.?且每隔一段?期SQL SERVER就自
　　但是Oracle中的??表和SQL Server中的??表的?理?制全然不同.Oracle的??表?
多只能相?於SQL Server中的全局??表,而且它?永久存在,如果你自己不去drop它的?
,Oracle是不?自??其?放的.而且SQL Server中的局部??表在Oracle中?法替代,因此
我在Oracle中放?了???表的使用,所有的??表全部改?永久表.
　　在Oracle的sp中不能出?DDL?句.因?所有的DDL?句(create,drop,alter,truncate等)
都是?式?有commit命令,Oracle的sp中不能有?式commit的存在.如果你非要在sp中建表或
者?除表的?,你可以用??SQL?完成?式commit.
　　例如:
　　execute immediate “create table ……”;
　　?於??SQL,後面?有一??做出??的介?.
　　在???有可能有????,?理不??影?性能或者?致程式出?:
　　1. 在sp一?始直接用??SQL建永久表,然後?表?行操作,sp退出之前再用??SQL?除
　　?注意了,在多用???操作的?候.A session?用了?sp?成功?建了表, B session
也?用?sp??去?建??表的?候.Oracle?很野?的?sp中?,然後我?的客?就?看到很
　　2. ?了?多用?使用互不干?,由程式生成sessionid?入sp或者利用Oracle的函?
userenv(‘sessionid’)生成sessionid.然後在sp中用??SQL生成表名+sessionid的表,??
　　但???有一???:由於sp被?常?用?致不?的建表?表.而Oracle的表都存放在表
空?上.??大量的DDL?句??表空??的碎片不?的增多而表空??不?增大,要知道
Oracle的碎片整理必?要手??行,它不?像SQL SERVER那?自?整理.”Oracle最?人?意
的是它可以?化,Oracle最?人不?意的是它必??化!”?了一?季度甚至於一?月,我?
的用?就?向我?抱怨我?的系?跑得越?越慢了.又提到了我前面??的?:我?的?大多
?程式?有受到真正的考?,如果不考?清楚的?,我?的系??有?重的??.
　　?於原SQL SERVER中的??表移植Oracle中我的?理方法如下:
　　1. ?建1-Module-CreateTempTable.sql文件:
　　所有原SQL SERVER中用到的??表都在此建立,只是每?表多一?字段sessionid int,如
　　2. ?建0-Module-DropTempTable.sql文件,?面的?容是:
　　begin
　　for r in (select 'drop table 'object_name as sqls from user_objects where
object_type = ' TABLE' and object_name in (‘temp1’,’temp2’,……)) loop -- 所有
　　execute immediate (r.sqls);
　　end loop;
　　end;
　　???SQL文件?我?的程式自??行.
　　3. 由程式生成sessionid或者通?userenv(‘sessionid’) 生成sessionid?入表的
sessionid字段.每?session只?理本session的??.
　　注:在Oracle中用如下方式建立的??表同SQL Server的全局??表效果相?:
　　CREATE GLOBAL TEMPORARY TABLE temp1
　　(……
　　……
　　sessionid int)
　　on commit delete rows；
　　四. ?行??SQL
　　一???SQL?句是在?行?而不是在???建立和?行的,在???,用?如果不了解查
?的??或者想要查?的?象,就可以使用??SQL.但是一般而言,?行??SQL的系??源花
?是?行同?的??SQL的?到三倍.因?每次?行??SQL的?候都必?重新?它?行?法分
析.由於此?因素,希望大家?可能的少使用??SQL.
　　SQL Server用exec(字符串)可以?行??SQL,如果需要??行的??SQL中得到?果值的
　　在Oracle中,可以使用DBMS_SQL包和execute immediate ‘……’??行??SQL,不?要
注意的是execute immediate是Oracle8i才推出的新特性,在Oracle8及以前的版本中是不能用
　　例程如下:
　　SQL SERVER:
　　declare @count int
　　declare @SQL nvarchar(200)
　　set @SQL = N'select count(*) from sysobjects'

　　exec sp_executesql @SQL,N'@i int output',@count output
　　print @count
　　Oracle:
　　set serveroutput on
　　/
　　declare
　　i_count int ;
　　v_SQL varchar2(200) := 'begin select count(*) into :1 from user_objects;
　　begin
　　execute immediate v_SQL using out i_count;

　　dbms_output.put_line(i_count);
　　end;
　　/
　　DBMS_SQL包使用起?????繁?而且?式使用游?,不提倡使用,故此?不做?明.
　　??:有一?需要注意了, execute immediate所能?行的字符串的?度是有限制的,如
　　五. 自增的identity如何移植?
　　SQL Server中的identity特性?我?的工作??了很大的方便,我?可以利用它方便的?
行排序.但是在Oracle中??有??的特性.Oracle只有sequence的概念.sequence是事???
性的,sequence?不是依附在表的上面,它是?立存在的,和事?是??的.但是我?可以利用
　　SQL SERVER的原表??:
　　create table ftm07 -- ?收票???
　　(
　　ftg00c TYPE_v_CMPID not , -- 公司?
　　ftg01c varchar(20) not , -- 票???
　　ftg02f int identity not , -- 流水?
　　ftg03d TYPE_v_DATE , -- ???理日
　　ftg04c TYPE_v_ENUM , -- ??
　　ftg06c varchar(20) , -- ?票管理??
　　ftg07c varchar(2) , -- 票????
　　ftg08c varchar(20) , -- 票?????
　　constraint PK_FTM07 primary key (ftg00c, ftg01c, ftg02f)
　　)
　　移植至Oracle中的做法:
　　create table ftm07 -- ?收票???
　　(
　　ftg00c varchar2(3) not , -- 公司?
　　ftg01c varchar2(20) not , -- 票???
　　ftg02f int not , -- 流水? /* SEQUENCE */
　　ftg03d varchar2(8) , -- ???理日
　　ftg04c varchar2(1) , -- ??
　　ftg06c varchar2(20) , -- ?票管理??
　　ftg07c varchar2(2) , -- 票????
　　ftg08c varchar2(20) , -- 票?????
　　constraint PK_FTM07 primary key (ftg00c, ftg01c, ftg02f)
　　);
　　CREATE SEQUENCE ftm07_seq INCREMENT BY 1;

　　create or replace TRIGGER Cash_ftm07_insert_before -- 增加一?insert之前的??
　　before insert on ftm07
　　for each row
　　declare
　　i_id integer;
　　begin
　　select ftm07_seq.nextval into i_id from dual;

　　:NEW.ftg02f := i_id;
　　end;
　　可以?上面看到,如同SQL SERVER一?,你?不需要?ftg02f字段?行操作,??器??你
搞定一切.但是有一?不同需要注意了:sequence的值不能被手工重置
　　在SQL SERVER中可以通?dbcc checkident(表名,reseed,0)或者truncate table
tableName(如果??也不需要的?)?表中的identity?位重置?1,而Oracle的sequence做不
到??,sequence只有?到最大值後,系?才?自??其重置??定的最小值.
　　六. ?於??器
　　SQL Server的??器都是在???句之後?行,而且是?句?的.也就是?一??句只能
?致一次??,而不管它有多少行??被更改,由於??表inserted和deleted的存在,?保了
每行??都能被引用.而Oracle的??器分????句之前?行或者???句之後?行,?句
　　?SQL Server的??器移植至Oracle上,你可以?建一????句之後的行???器,切?
,一定要用行???器而不能用?句???器.因?Oracle的??器模式中?有像SQL Server
那?的inserted和deleted?表的存在,而只有:new和ld.:new和ld不能算成是二?的表,它只是
　　在Oracle中,游?的概念?穿整?PL/SQL,游?在Oracle中被?泛?用.就?每一?update
?句和delete?句都有一??置的?式的游?!因此,我?可以使用行???器加ld及:new??
到SQL Server中的?句???器加inserted及deleted的效果.
　　例程如下:
　　SQL SERVER:
　　create trigger Cash_frm02_delete
　　on dbo.frm02
　　for delete
　　as
　　if @@rowcount=0
　　return
　　delete frm05
　　from frm05 f,deleted d
　　where f.fre00c = d.frb00c and f.fre01c = d.frb01c and f.fre02c = d.frb02c
　　if @@error <> 0
　　goto error_handler
　　return
　　error_handler:
　　begin
　　rollback transaction
　　return
　　end
　　Oracle:
　　create or replace trigger Cash_frm02_delete
　　after delete
　　on frm02
　　for each row
　　begin
　　delete from frm05
　　where fre00c = :OLD.frb00c and fre01c = :OLD.frb01c and fre02c = :OLD.frb02c;
　　exception
　　when others then
　　rollback;
　　end Cash_frm02_delete;
　　七. ???,外??,全??
　　???(inner join)也就是我?平常的等??.在SQL-92??之中,?部??可指定於FROM
或WHERE子句中.?是 SQL-92在WHERE子句中唯一支援的???型.
　　SQL-92??中的外??分?left outer join和right outer join.??的?法是*=和=*。
SQL Server 同?支援 SQL-92 外部???法????法，以便以使用 *= ? =* ?算子於
WHERE 子句中的方式?指定外部??。建?您使用 SQL-92 ?法，因?它?不??生??
Transact-SQL 外部??有?所?生的模??可?象，?且MS???在以後的版本中?有可能
　　全??:SQL-92 FULL OUTER JOIN ?算子指出???料表中的所有?料列都必?包含於?
果之中，而不管?料表中是否有符合的?料。SQL Server支持全??,但不幸的是:Oracle?不
支持全??,如果你想?到全??的效果,那?只有使用union了.
　　Oracle的PL/SQL?不是非常的支持SQL-92??,它有?多地方的做法都???不同,它的外
　　Oracle的外??符?是(+),?且在整?select?句中,只能有一?表做?主表和其他的表
?行外?.如果你的SQL Server中用到??或者??以上的表作?主表的?,那?只有完全改
?.?且在SQL Server的外??句中如果用到了in或者or的?,那?成Oracle???周折.
　　例程如下:
　　SQL Server:
　　select a.COMP_ID,a.TRANS_NO,b.PART_NO from TransMaster a
　　left outer join TransDetail b on
　　(a.COMP_ID = b.COMP_ID and a.TRANS_NO = b.TRANS_NO)
　　Oracle:
　　select a.COMP_ID,a.TRANS_NO,b.PART_NO from TransMaster a,TransDetail b
　　where a.COMP_ID = b.COMP_ID(+) and a.TRANS_NO = b.TRANS_NO(+);
　　//?注意(+)??符????挨著?表的字段出?
　　......
　　九. Top n?rownum
　　在SQL Server中可以用top n?返回指定?量的??,Oracle中?之??的是rownum,只是
不同的是:SQL Server的top n是???序返回的??,而Oracle的rownum指定的是物理?序的
序?,其物理的序?是在order by之前就已??定好了的.因此下面的?句在Oracle中都是?
　　???句1: 　　select rownum,fom01.* from fom01 where rownum < 11 order by foa02d;
　　//???句永?也得不到正?的,你想要的?果.你可以?行一下看看?果.
　　//因?rownum是物理?序的序?,是在排序之前就已??定好了的.
　　?正後的?句:
　　select rownum,ss.* from 　　(select * from fom01 order by foa02d) ss where rownum < 11;
　　但是要注意??的?句在Oracle8i之後才能正常?行,因?Oracle8i才能在子查?中使用
　　???句2:
　　select rownum,fom01.* from fom01 where rownum = 2;
　　//???句?返回不了任何的值,但是?2改成1就可以返回一???。因?Oracle的
rownum只有在生成了1後才能有2,同?的有了2才能有3,以此?推.其???道理也很??,你
有?有看?一?房子?有?好1?就?好了2??在半空? ^_^
　　?正後的?句: 　　select * from (select rownum r,fom01.* from fom01 where rownum <= 2)
　　where r > 1;
　　......
　　十一. 其他
　　?有一些SQL Server?Oracle的不同需要注意。
　　SQL Server Oracle
　　T-SQL??;?表示?束每??句 PL/SQL使用分?;?束每??句
　　T-SQL用go?表示批次?行 T-SQL用/?表示批次?行
　　@@rowcount sql%rowcount
　　@@spid Userenv(‘sessionid’)
　　@@FETCH_STATUS 游?名%found
　　object_id(‘object_name’) select object_id from user_objects
　　nvarchar,varchar字段之?互相??用convert(varchar(n),字段名)和co
nvert(nvarchar(n),字段名) nvarchar,varchar字段之?互相??用translate(字段名
　　有if exists(?果集)的?法 ?有if exists(?果集)的?法
　　字符串用+?接 字符串用?接
　　?值?句用set或者select ?值?句用pascal的?值方法:=
　　sp的形?需要指定?度 sp的形?不需要指定?度
　　case … when … then … else … end decode()
　　update?句可以from?果集 update?句不能from?果集
　　insert?句可以只?insert insert?句必??insert into
　　查看表??用sp_help tableName 查看表??用desc tableName
　　?果集需要指定?名 ?果集不需要指定?名
　　?表或?果集取?名可以用as ?表或?果集取?名不能用as
　　日期加?用dateadd和datediff函? 日期加?直接用+?-?
　　?前日期和??用getdate() ?前日期和??用sysdate
　　suser_id(),user_id() uid
　　user_name(),suser_name() user
　　like 支持[]通配符 like 不支持[]通配符
　　打印?出用print 打印?出用dbms_output.put_line()
　　http://www.oradb.net/tran/08.htm
　　http://www.oradb.net/tran/09.htm
　　http://www.oradb.net/tran/ora2sql.htm