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

本文链接：https://blog.csdn.net/fa_ge/article/details/1890054

最近几个月学习了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 T 1 a ,T2 b WHERE a.关键字=b.关键字这是我们MSSQL 编程人员常用且喜欢的一种写法，但在Oracle 编程中，你白天提灯笼也找不到的。不过呢，在Oracle PL/SQL有两种写法类似于上面这种功能.

第一种:SQL>WITH 表名 AS (

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

3 )

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

2 SELECT a.字段1，b.字段2…FROM T 1 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 num 1 IN OUT NUMBER,num 2 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

--结果

3.333333

(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。