ORACLE PL/SQL & SQL SERVER T-SQL

晋6988

　　
　　1、外连接   
　　·MS SQL SERVER 支持两种形式表间连接   
　　①从Sybase继承来的形式：   
　　字段1 *= 字段2 （左连接）   
　　字段1 =* 字段2 （右连接）   
　　没有这种形式的全外连接语法   
　　②标准的外连接语法   
　　left [outer] join on 逻辑表达式   
　　right [outer] join on 逻辑表达式   
　　full [outer] join (全外连接) on 逻辑表达式   
　　这里的逻辑表达式 可以是很复杂的表达式例如 ：A.ID=B.ID AND (A.Parebt_ID=1 OR A.Parent_ID=2)   
　　
　　需要提醒大家的是：你写的查询语句报告过这样的错误  
　　Joined tables cannot be specified in a query containing outer join operators. Joined tables cannot be specified in a query containing outer join operators. View or function 
　　
　　'dbo.VU_CAF_BILLS' contains joined tables   
　　这句话告诉你，你查询语句引用的视图或者子查询也用到了外连接，但是引用视图或者子查询外连接语法与你的外连接语法不一直导致的   
　　例如：select A.[ZONE],A.FLAG,A.FlagDesc,A.CAF_NO   
　　from dbo.VU_CAF_BILLS A,TU_Flag   
　　where A.CAF_NO*=TU_Flag.ObjNo   
　　视图dbo.VU_CAF_BILLS的外连接语法是标准的SQL语法，而本语句中的外连接语法却是Sybase式的外连接语法。  
　　
　　
　　·Oracle不支持标准的外连接语法，也没有全外连接，这是它的缺陷   
　　字段1 = 字段2(+) （左连接）   
　　字段1(+) = 字段2 （右连接）   
　　
　　·使用外连接语句的用处   
　　①不想因为表连接而使主表数据行丢失，这一点毋庸多说   
　　①找某条记录在A表存在，而在B表不存在，按常规做法使用not in （select 查询子句）语法，   
　　使用not in 最大的缺点速度慢，原因是每个数据行都去做：select 查询子句   
　　而使用下面的语法：   
　　select TU_COMPANY.*   
　　from TU_COMPANY left join TU_Comp_Agent on TU_COMPANY.ID=TU_Comp_Agent.CompCode   
　　where TU_Comp_Agent.Id is null   
　　
　　2、触发器   
　　·从我了解到的，MS SQL SERVER,仅有表的触发器，而且触发时机不够丰富   
　　如插入触发在子，不区分单条插入还是多条插入，也不区分插入前触发还是插入后触发   
　　碰到多条数据的插入，需要使用游标处理每条插入的数据   
　　
　　·Oracle提供的触发器不仅有基于表的触发器，而且其他类型的，例如数据库级的触发器：数据库启动、数据库关闭   
　　对于表级的触发器，区分单条插入还是多条插入，也区分插入前触发还是插入后触发   
　　
　　3、表数据复制   
　　·库内数据复制   
　　·MS SQL Server  
　　Insert into 复制表名称 select 语句 （复制表已经存在）   
　　select 字段列表 into 复制表名称 from 表 （复制表不存在）   
　　·Oracle   
　　Insert into 复制表名称 select 语句 （复制表已经存在）   
　　create table 复制表名称 as select 语句 （复制表不存在）   
　　
　　·文本文件转入、转出的批量处理   
　　·MS SQL Server  
　　BCP命令行程序   
　　·Oracle   
　　SQLLDR命令行程序   
　　
　　
　　4、多表更新、删除   
　　一条更新语句是不能更新多张表的，除非使用触发器隐含更新，我这里说的意思是：根据其他表数据更新你要更新的表   
　　一般形式：   
　　·MS SQL Server  
　　update A   
　　SET 字段1=B表字段表达式,   
　　字段2=B表字段表达式   
　　from B  
　　WHERE 逻辑表达式   
　　·Oracle   
　　update A   
　　SET 字段1=（select 字段表达式 from B WHERE ...）,   
　　字段2=（select 字段表达式 from B WHERE ...）   
　　WHERE 逻辑表达式   
　　
　　从以上来看，感觉oracle没有ms sql好，主要原因：假如A需要多个字段更新，MS_SQL语句更简练   
　　
　　你知道刚学数据库的人怎么做上面这件事情吗，他们使用游标一条一条的处理   
　　
　　另外，Oracle中的Delete的from子句也没有多表联接的功能，只能通过子查询的方式来做：
　　delete from 表A where exists (select * from 表B where 表A.empid=表B.empid)
　　delete from 表A where 表A.empid in (select empid from 表B)
　　
　　
　　5、关于存储过程或函数中使用的临时表，两者都提供了这个功能   
　　临时表，最主要的好处是，操作不留任何痕迹、不产生日志，   
　　所以速度快   
　　·MS SQL SERVER   
　　CREATE TABLE #表名称（........） 或者 SELECT 字段表达式列表 INTO #表名称 FROM   
　　表名称前加#即可，这些临时表都是只在一个数据库连接会话期间有效   
　　
　　·Oracle  
　　create [Global] Temporary Table ,加上[Global]就是全局的临时表（所有数据库连接会话都是可见的），   
　　不则为私有的（在一个数据库连接会话期间有效）  
　　
　　
　　6、动态执行SQL语句   
　　·MS SQL SERVER 7.0好象没有这个功能，MS SQL SERVER 2000已经这个功能。   
　　你是不是想在存储过程的参数中传递一个表名或者在过程体里动态   
　　生成一个SQL语句，你会发现很难办到。看了下面的例子：你以前的问题全解决了   
　　declare @count int   
　　declare @SQL nvarchar(200)   
　　set @SQL = N'select count(*) from sysobjects'   
　　exec sp_executesql @SQL,N'@i int output',@count output   
　　
　　
　　·Oracle提供了两种方法实现这个功能   
　　①程序包DBMS_SQL，执行一个语句的过程：   
　　打开游标（open_cursor，对于非查询语句，无此过程）   
　　分析语句（Parse)   
　　绑定变量（bind_variable）   
　　执行语句（execute)   
　　关闭游标（close_cursor,对于非查询语句，无此过程)   
　　②execute immediate ls_SQL   
　　
　　Oracle用|| 符号作为连接符，而SQL Server的连接符是加号：+ 。
　　
　　
　　Oracle查询如下所示： 
　　Select ‘Name’ || ‘Last Name’ From tableName 
　　对应的SQL Server查询如下所示： 
　　Select ‘Name’ + ‘Last Name’
　　
　　
　　数字取舍 
　　Oracle数据库内有一个TRUNC函数，该函数返回m位十进制数的n位；如果省略m则n就是0位。m的值可以为负，表示截去小数点左边m位数字。
　　在SQL Server下可以用Round或者Floor。 
　　以下是Oracle查询： 
　　SELECT   TRUNC(15.79,1) "Truncate" FROM DUAL; 
　　下面是同类查询的SQL Server版本： 
　　SELECT ROUND(15.79, 0) rounded , ROUND(15.79, 0,1) truncated 
　　SELECT FLOOR(ROUND(15.79, 0)), FLOOR(ROUND(15.79, 0,1) )
　　
　　
　　数字转换 
　　Oracle的TO_CHAR函数可以把n位NUMBER数据类型转换为VARCHAR2 数据类型，同时采用可选的数字格式。 
　　SQL Server则通过STR函数返回数字转换之后的字符数据。不过，该函数不具方便的Format参数。 
　　Oracle查询如下： 
　　SELECT to_char(123.45 ,99999999999999) from tab 
　　SELECT to_char(EXPIRY_DATE,'DDMONYYYY') from tab 
　　以下是SQL Server版本的查询： 
　　SELECT STR(123.45, 14) 
　　SELECT STR(round(123.455 , 2),12,2) 
　　SELECT CAST(REPLACE((CONVERT(varchar(12) , EXPIRYDATE, 106 )),' ' , '') as varchar(9))
　　LENGTH和LEN 
　　以下是Oracle的查询： 
　　SELECT LENGTH('SQLMAG') "Length in characters" FROM DUAL; 
　　以上查询在SQL Server下是这样写的： 
　　SELECT LEN('SQLMAG') "Length in characters"
　　
　　
　　日期 
　　以上两种系统都具有各自的当前日期和时间格式。 
　　Oracle取得日期和采用如下方式： 
　　SYSDATE 
　　SQL Server则是这样的： 
　　GETDATE() 
　　你可以用各种语法操作日期。以下的代码对Oracle日期值中的月份进行了格式调整（返回日期再加上n月）： 
　　Select add_months(sysdate,12) from dual 
　　SQL Server则是如下完成同等功能的： 
　　Select dateadd(mm,12,getdate()) 
　　数据的减法也不同。以下代码在Oracle中直接对数据进行减法操作： 
　　SELECT sysdate -add_months(sysdate,12) FROM dual 
　　SQL Server则是这样做的： 
　　SELECT   datediff(dd, GetDate(),dateadd(mm,12,getdate()))
　　执行查询时
　　PLSQL执行查询的时候，FROM子句是必须的
　　而SQL SERVER不一定
　　如ORACLE中select 2*5 from dual
　　而SQL SERVER中select 2*5
　　
　　
　　PL/SQL用";"分割每一條語句，T-SQL用回車分割一條語句。  
　　
　　PL/SQL和T-SQL的注釋方法相同。  
　　
　　if语句
　　
　　PL/SQL的if語句，與vb很類似，最後要用";"結束：
　　IF <condition_1> THEN ...
　　
　　ELSIF <condition_2> THEN ...
　　
　　/*... ...*/
　　
　　ELSIF <condition_n> THEN ...
　　
　　ELSE ...
　　
　　END IF;
　　
　　T-SQL的if語句
　　if <conditon> begin
　　/*...*/
　　end
　　
　　
　　
　　PL/SQL是以block為單元的，所有的PL/SQL均以block組成。T-SQL沒有block的概念。結構如下：
　　DECLARE
　　/* Declarative section: variables, types, and local subprograms. */
　　BEGIN
　　/* Executable section: procedural and SQL statements go here. */
　　/* This is the only section of the block that is required. */
　　EXCEPTION
　　/* Exception handling section: error handling statements go here. */
　　END;
　　
　　
　　
　　賦值語句
　　
　　PL/SQL可以在變量聲明中賦值：
　　
　　l_date DATE := to_date('31-JUL-02');  
　　
　　T-SQL要變量與賦值分割開來
　　
　　declare @sDate datetime
　　
　　set @sDate=getdate()
　　
　　注意賦值符號不一樣。PL/SQL是":="， T-SQL是"="  
　　
　　以下是PL/SQL的Declaration section的賦值語句示範：
　　
　　declare
　　hire date date; /* implicit initialization with null */
　　job title varchar2(80) := ’Salesman’;
　　emp found boolean; /* implicit initialization with null */
　　salary incr constant number(3,2) := 1.5; /* constant */
　　. . .
　　begin . . . end;
　　
　　Boolean data may only be true, false, or null.
　　
　　
　　
　　PL/SQL的select into vs. T-SQL select into
　　
　　T-SQL中,例如:
　　
　　select * from author into temp1 where au_id=8081  --將自動建立一個temp1的table到當前數據庫
　　
　　PL/SQL的select into 功能強大的多，不是建立一個table而是返回值。值可以有兩種類型，%TYPE或者%ROWTYPE。例如：
　　
　　declare
　　employee rec EMP%ROWTYPE;
　　max sal EMP.SAL%TYPE;
　　begin
　　select EMPNO, ENAME, JOB, MGR, SAL, COMM, HIREDATE, DEPTNO
　　into employee rec
　　from EMP where EMPNO = 5698;
　　select max(SAL) into max sal from EMP;
　　. . .
　　end;
　　
　　%ROWTYPE是一種復合類型,可以實現T-SQL中select into的類似功能，但區別在於實現的方法不同，T-SQL把用戶需要的數據放入到一張自定義的表中，而PL/SQL通過一種類似結構的變量來存儲紀錄集，
　　
　　是變量機制，而不是表。
　　
　　
　　PL/SQL在用select方法給變量賦值至少有一條紀錄返回，否則拋出異常；如果在賦值中，select返回多個紀錄，必須使用cursor。