Oracle 10g PL/SQL 和 MSSQL 在编程方面有哪些异同?

 

最近几个月学习了Oracle 10g PL/SQL编程，发现其语法/功能/函数/句式都和MSSQL 2000有很大的差异。Oracle 的强大功能，真让人感叹不已,我相信只有用了它的人才会有体会。我用MSSQL 2000数据库有两年多了，并一直从事数据库开发工作，个人认为MSSQL 2000数据库是一个功能强大，语法句式相对简单，对于初学者比较容易上手，通过短时间内学习可以担任工作.MSSQL2000更接近过程化编程语言.但Oracle 10g PL/SQL更象面向对象语言，因为它具有面向对象特性，如记录，数组,对象, 过程重载,构造函数等。下面我将从常用子句，函数,包一一列举Oracle PL/SQL 和MSSQL 2000编程有什么异同点.

 

一：常用子句写法上(只提不同点)

(1):SELECT

MSSQL : SELECT a.字段1，b.字段2… INTO 表|临时表 FROM T1 a ,T2 b WHERE a.关键字=b.关键字这是我们MSSQL 编程人员常用且喜欢的一种写法，但在Oracle 编程中，你白天提灯笼也找不到的。不过呢，在Oracle PL/SQL有两种写法类似于上面这种功能.

第一种:SQL>WITH 表名 AS (

          2 SELECT a.字段1，b.字段2…FROM T1 a ,T2 b WHERE a.关键字=b.关建字

          3 )

第二种：SQL>CREATE TABLE 表名 AS /*当然建临时表也可以的*/

           2 SELECT a.字段1，b.字段2…FROM T1 a ,T2 b WHERE a.关键字=b.关建字

这样就实现了和MSSQL一样的功能,不过呢第种方法总有些别扭。呵呵.

 

(2):INSERT子句

    在MSSQL还是Oracle PL/SQL中插入单行数据，我们都可以用 INSERT INTO T VALUES(值1，值2…)  

 这种写法。但是当你想为表初始化插入多组数据时,MSSQL 中可以这样写

 INSERT INTO T(ID ,NAME)

 SELECT 1,’zhang’ UNION ALL

 SELECT 2 ‘liang’ UNION ALL

 SELECT 3,‘cheng’

 但在Oracle 中 怎么写呢?

SQL>INSERT INTO T (ID,NAME)

  2 (SELECT 1,’zhang’ FROM DUAL;)

   3 UNION ALL

   4 (SELECT 2,’liang’ FROM DUAL;)

   5 UNION ALL

   6 (SELECT 3,’cheng’ FROM DUAL;)

这是针对一个表赋初值的情况,如果我想把一张表的数据根据不同的情况插入到不同的数据表中呢？如果是在MSSQL中怕是要写过程来实现这样的功能了，Oracle中呢?一条语句搞定,前提是先要建好要保存的表.(这是我最不喜欢Oracle的一个方面,在MSSQL创建表是方便的 )

 SQL>INSERT ALL

2 WHEN deptno=10 THEN INTO T1

3 WHEN deptno=20 THEN INTO T2

4 WHEN deptno=30 THEN INTO T3

5 ELSE INTO T4

6 SELECT * FROM EMP;  

 

(3):UPDATE 子句

如：要把表EMP 中雇员名为 “SCOTT”的工资，补贴与雇员”SMITH”一样.

在MSSQL中我们可能会这样写:

UPDATE EMP

SET SAL=B.SAL,COMM=B.SAL

FROM EMP A JOIN (SELECT,EMPNO, SAL ,COMM FROM EMP WHERE NAME=’SMITH’)B

ON A.EMPNO=B.EMPNO

WHERE EMP.NAME=’SCOTT’

但在Oracle PL/SQL 中写法比较简单些，一睹为快.

SQL>UPDATE EMP

2 SET (SAL,COMM)=

3 (SELECT SAL,COMM FROM EMP WHERE NAME=’SMITH’)

4 WHERE NAME=’SCOTT’

比较比较，后者更简洁吧。

 

(4):ORDER BY 子句

Oracle 和MSSQL在排序上有一点很大不同,Oracle 可以直接引用列的别名来排序，而MSSQL不行的。如：要显示雇员全年工资，以降序排列.

MSSQL:

SELECT NAME,SUM(SAL*12)AS ‘全年工资’

FROM EMP

GROUP BY NAME

ORDER BY SUM(SAL*12) DESC

Oracle :

 SQL>SELECT NAME,SAL*12 AS ‘全年工资’

    2 FROM EMP

    3 ORDER BY ‘全年工资’ DESC

 

(5) TOP 子句

我们在检索数据时，常和它打交道，有时我们想看前几条记录，在MSSQL很方便做到的.如我们想看到前5名工资最高的雇员名和工资

MSSQL:

 SELECT TOP 5 NAME,SAL FROM EMP ORDER BY SAL DESC

Oracle:

 SQL:>SELECT NAME ,SAL FROM EMP WHERE ROWNUM<6 ORDER BY SAL DESC

也就是说在MSSQL中表达前多少条是用TOP子句，而在Oracle中是用ROWNUM子句的.

 

(6) 自动编号

    程序员朋友可能用到它是最多的，我们常在数据库编程时，要借助它来帮助计算.如对一个表进行运行汇总.如没有这个自动编号ID ，我想计算会很困难的。在MSSQL中创建自动编号,是十分简单的.

    MSSQL:

      CREATE TABLE T(ID INT IDENTITY(1,1),NAME VARCHAR(10))

      或

      ALTER TABLE T

      ADD ID INT IDENTITY(1,1)

    简单的一条语句可以搞定很多工作,但在Oracle中没有这么简单，可以说还很麻烦.下面来看在Oracle中是怎么建自动编号的.

    Oracle:

      --第一步：先创建序列

      SQL>CREATE SEQUENCE IDENTITY_ID START WITH 1 INCREMENT BY 1

      /

      --第二步：创建表

      SQL>CREATE TABLE TEMP(ID INT,NAME VARCHAR2(10))

      /

SQL>INSERT INTO TEMP VALUES(IDENTITY_ID.NEXTVAL,’ZHANG’);

SQL> select * from temp;

 

        ID NAME

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

         1 zhang

    

(7):条件子句

 

Oracle PL/SQL 中:                           MSSQL中：

IF condition THEN                           IF condition

   sql子句;                                    BEGIN  

ELSE IF condition THEN                            sql子句

   sql子句;                                     END

ELSE                                         ELSE

   sql子句;                                     BEGIN  

END IF;                                            sql子句;

                                                     END

 

   CASE WHEN condition1 THEN SQL子句;             CASE WHEN condition1 THEN SQL子句

        WHEN condition2 THEN SQL子句;                  WHEN condition2 THEN SQL 子句

        ELSE sql子句;                                  ELSE sql子句  

   END CASE;                                       END AS ‘列名’  

 

以上可以看出Oracle每条子句结尾都以分号表示结束，条件语句结束符也不同.

 

(8):循环语句

在MSSQL中循环只有下面一种形式,当然借助GOTO语句循环是另一回事了.

WHILE condition

BEGIN

    sql block

END

在Oracle中，循环语句多样

 (1)                            (2)                           (3)    

LOOP                          WHILE condition LOOP     FOR I IN [REVERSE] Range

 Statements1;                   statements1;            statement1;

 …                             …                      …;

 EXIT WHEN condition;        END LOOP;                END LOOP;  

END LOOP;

 

二：常用函数(只提不同)

 

(1):数学函数      

Oracle                    MSSQL

A:取最小整数         Ceil()                    Ceiling()  

B:自然对数           Ln()                      Log()

C:取佘               Mod()                     %    

D:随机数             无/不知道                 Rand()

E:列表中最大数       Greatest                  没有

F:列表中最小数       Least                     没有

(2):字符函数

                           Oracle                   MSSQL

    A:字符串串联          Connect()/ ‘||’          ‘+’  

B:ASCII转字符         Chr()                     char()

C:返回字符串中        Instr()                   Charindex()

的起始字符  

D:字符串左边填充字符 Lpad()                    没有  

 E:子串                Substr()                  Substring()

 F:首字符大写          InitCap()                 没有

 G:列表中的最大字符串 Greatest()                没有

 H: 列表中的最小字符串 Least()                   没有

 I: 是否为空           Nvl()/Nvl2()              Isnull()

 J: 转为字符           To_Number()               Convert()

 

(3):日期函数

A:当前日期            Sysdate                   Getdate()

B:日期加              Add_Months()              Dateadd()

C:月的最后一天        Last_Day()                没有

D:日期后第一个工作日 Next_Day()                没有

E:日期转字符          To_Char()                 Convert()

F:字符转日期          To_Date()                 Convert()

I:日期截尾            Trunc()                  Convert()

J:十六进制到二进制    Hex_To_Raw                Convert()

K:二进制到十六进制    Raw_To_Hex                Convert()

 

注：MSSQL中执行函数在查询分析器中，SELECT ABS(数据)就有结果，但在Oracle PL/SQL中执行

SQL>SELECT ABS(数值) FROM dual;

 

三：程序写法不同

  

(1) 过程(Procedure)

  

  Orcale PL/SQL                                       MSSQL

CREATE [OR REPLACE] PROCEDURE PRO_NAME(参数 类型) CREATE PROCEDURE PRO_NAME(参数 类型,…)

AS/IS                                             AS

BEGIN                                             BEGIN

Sql Statements;                                     Sql Statements

END;                                              END;

 

Oracle PL/SQL和MSSQL 存储过程的写法都大同小异,但Oracle PL/SQL的存储过程的参数列表可以是in out输入输出参数，而MSSQL中却没有这样的参数.

SQL>CREATE OR REPLACE PROCEDURE test(

 2 num1 IN OUT NUMBER,num2 IN OUT NUMBER)

 3 AS

 4   v1 NUMBER(6,2);

 5   v2 NUMBER(6,2);

 6 BEGIN  

 7   v1:=num1/num2;

 8   v2:=MOD(num1,num2);

 9 end;

      

--测试

SQL> VAR n1 NUMBER;

SQL> VAR n2 NUMBER;

SQL> EXEC :n1:=100

SQL> EXEC :n2:=30

SQL> EXEC test(:n1,:n2)

SQL> PRINT n1 n2

--结果

   N1

   3.333333

   N2

   10

        

(2):函数(Function)

    

Oracle PL/SQL                                      MSSQL

 

CREATE OR REPLACE FUNCTION FUN_NAME(参数 类型,…) CREATE FUNCTION FUN_NAME(参数 类型,…)

RETURN 类型                                      RETURNS 类型

AS/IS                                            AS

定义变量;                                        BEGIN

BEGIN                                                定义变量

Sql Statements;                                    Sql Statements

  RETURN 变量;                                       RETURN 变量

END;                                               END

 

   通过上面定义的，我们可以看出Oracle的变量都在BEGIN 之前定义，而MSSQL定义一个变量是在

   BEGIN 块里定义的。还有的是在MSSQL里 RETURNS 多一个”S”.

 

(3):触发器(Trigger)

    

      Oracle PL/SQL                                     MSSQL

   CREATE OR REPLACE TRIGGER Tri_Name           CREATE TRIGGER Tri_Name

   Timing event1[,event2,event3]                ON Table_Name    

   ON Table_Name                                AFTER/FOR/INSTEAD OF event1[,event2,event3]

   PL/SQL block;                                AS

                                                BEGIN

                                                  Action

                                                END

 Oracle的触发器和MSSQL的触发器定义方法，有很大的不同。Oracle 是先触发事件再指定表，而MSSQL

 刚相反。并且Oracle定义触发器时不用”AS”.Oracle的触发器种类很多，有DML 触发事件,DDL触发事件，系统触发事件(如：用户登陆，退出，关闭例程等),但在MSSQL触发器只有DML触发事件.最后值得一提的是Oracle 的触发器的虚表 old 和 new ,而MSSQL的需表是 inserted 和 deleted。

New 和 inserted 表保存是更新后或插入后的数据,old 和 delete 表保存的是更新前和删除前的记录。

 

 

(4):游标(Cursor)

 

       Oracle PL/SQL                                      MSSQL

--定义游标

 DCLARE CURSOR Cur_Name                         DECLARE Cur_Name CURSOR

 IS Sql语句 ;                                   IS Sql 语句

--打开游标

 OPEN Cur_Name;                                  OPEN Cur_Name

--提取数据

 FETCH Cur_Name INTO 变量1,…                   FETCH FROM Cur_Name INTO 变量1,…

--或者

 FETCH Cur_Name BULK COLLECT INTO 集合1,…

--关闭游标

 CLOSE Cur_Name;                                 CLOSE Cur_Name

                                                  DEALLOCATE Cur_Name

 

Oracle 的游标功能十分强大，它不仅能处理单行数据还能利用集合处理多行数据。让人吃惊的是Oracle的游标还可以带参数，功能类似于存储过程和函数.下面写一段代码看看和MSSQL有什么不同.

DECLARE

  CURSOR emp_cursor(no NUMBER)IS SELECT ename FROM emp WHERE empno=no;

  v_name VARCHAR2(10);

BEGIN

  OPEN emp_cursor(1008);

  LOOP

    FETCH emp_cursor INTO v_name;

    EXIT WHEN emp_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(v_name);

  END LOOP;

  CLOSE emp_cursor;

END;

 

--结果

 scott

  

很显然，Oracle游标的的循环模式是

LOOP

FETCH 游标名 INTO 变量;

EXIT WHEN 游标名%NOTFOUND;

  ...

END LOOP;

 

而MSSQL游标的循环模式是：

WHILE(@@FETCH_STATUS=0)

    BEGIN

FETCH 游标名 INTO 变量

...

FETCH 游标名 INTO 变量

    END