ORACLE PL/SQL与SQL SERVER T-SQL一些比较【经典】

 

ORACLE PL/SQL与SQL SERVER T-SQL一些比较【经典】

2007年03月02日 星期五 下午 06:52

 

 

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。

 

未完待续

 

------------------------

 

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语句更简练   

 

  你知道刚学数据库的人怎么做上面这件事情吗，他们使用游标一条一条的处理   

 

 

  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