把Oracle数据库移植到Microsoft SQL Server 7.0(4)

引用完整性

下表提供了一个用来定义referential完整性约束的语法比较。

约束 Oracle Microsoft SQL　Server

PRIMARY KEY [CONSTRAINT constraint_name]

PRIMARY KEY (col_name [, col_name2 [..., col_name16]])

[USING INDEX storage_parameters] [CONSTRAINT constraint_name]

PRIMARY KEY [CLUSTERED | NONCLUSTERED] (col_name [, col_name2 [..., col_name16]])

[ON segment_name]

[NOT FOR REPLICATION]

UNIQUE [CONSTRAINT constraint_name]

UNIQUE (col_name [, col_name2 [..., col_name16]])

[USING INDEX storage_parameters] [CONSTRAINT constraint_name]

UNIQUE [CLUSTERED | NONCLUSTERED](col_name [, col_name2 [..., col_name16]])

[ON segment_name]

[NOT FOR REPLICATION]

FOREIGN KEY [CONSTRAINT constraint_name]

[FOREIGN KEY (col_name [, col_name2 [..., col_name16]])]

REFERENCES [owner.]ref_table [(ref_col [, ref_col2 [..., ref_col16]])]

[ON DELETE CASCADE] [CONSTRAINT constraint_name]

[FOREIGN KEY (col_name [, col_name2 [..., col_name16]])]

REFERENCES [owner.]ref_table [(ref_col [, ref_col2 [..., ref_col16]])]

[NOT FOR REPLICATION]

DEFAULT Column property, not a constraint

DEFAULT (constant_expression) [CONSTRAINT constraint_name]

DEFAULT {constant_expression | niladic-function | NULL}

[FOR col_name]

[NOT FOR REPLICATION]

CHECK [CONSTRAINT constraint_name]

CHECK (expression) [CONSTRAINT constraint_name]

CHECK [NOT FOR REPLICATION] (expression)

　

NOT FOR REPLICATION子句用来在复制过程中挂起列级别，FOREIGN KEY，以及CHECK约束。

　

外部键

定义外部关键字的语法在各种RDBMS中都是相似的。在外部关键字中标明的列数和每一列的数据类型必须和

REFERENCES子句相匹配。一个输入到列中的非空的值必须在REFERENCES子句中定义表和列中存在，并且被提

及的表的列必须有一个PRIMARY KEY或者UNIQUE约束。

Microsoft SQL Server约束提供了在同一个数据库中引用表的能力。要实现在数据库范围的应用完整性，可

以使用基于表的触发器。

Oracle和SQL Server都支持自引用表，这种表中有对同一个表的一列或几列的引用。例如，CLASS表中的

prereq列可以引用CLASS表中的ccode列以确保一个有效的课程编号是作为一个子句的先决条件输入的。

在Oracle中实现层叠式的删除和修改是使用CASCADE DELETE子句，而SQL Server用表触发器来实现同样的

功能。如果需要了解更多的信息，请参看本章后面的“SQL语言支持”部分 。

　

用户定义的完整性

用户定义的完整性允许你定义特定的商业规则，该规则不属于其他完整性的范畴。

　

过程

Microsoft SQL Server程序用CREATE PROCEDURE语句来接受或者返回用户提供的参数。除临时程序

以外，程序是在当前数据库中创建的。下表显示了Oracle和SQL Server的语法。

Oracle Microsoft SQL　

CREATE OR REPLACE PROCEDURE [user.]procedure

　　　　[(argument [IN | OUT] datatype

　　　　[, argument [IN | OUT] datatype]

{IS | AS} block CREATE PROC[EDURE] procedure_name 　　　　　　　[;number]

　　　　[

　　　　　　　{@parameter data_type} [VARYING] 　　　　　　　[= default] [OUTPUT]

　　　　]

　　　　[,…n]

[WITH

　　　　{　RECOMPILE 　　| ENCRYPTION |

　　　　　　RECOMPILE, ENCRYPTION} ]

[FOR REPLICATION]

AS

　　　　sql_statement […n]

　

在SQL Server中，临时程序是在tempdb数据库中通过在procedure_name前加上数字标记来创建的。加一

个数字标记（#procedure_name）表示是一个本地临时程序，加两个数字标记（##procedure_name）表示

是一个全局临时程序。

一个本地临时程序只能被创建它的用户使用。执行本地临时程序的许可不能授予其他用户。本地临时程序在

用户会话结束时自动删除。

一个全局的临时程序可以被所有的SQL Server用户使用。如果一个全局临时程序被创建了，所有的用户都可

以访问它，并且不能显式的撤回许可。全局临时程序在最后一个用户会话结束的时候自动删除。

SQL Server程序可以有最多32级嵌套。嵌套层数在被调用的程序开始执行时增加，在被调用的程序结束

运行时减少。

下面的例子说明了怎样用一个Transact-SQL程序来代替一个Oracle的PL/SQL封装函数。Transact-SQL的

版本更简单一些，因为SQL Server的返回结果的能力是在一个程序中直接用SELECT语句设置的，不需要

使用游标。

Oracle Microsoft SQL　

CREATE OR REPLACE PACKAGE 　　　STUDENT_ADMIN.P1 AS ROWCOUNT 　　　NUMBER :=0;

　　　CURSOR C1 RETURN 　　　STUDENT%ROWTYPE;

　　　FUNCTION 　　　SHOW_RELUCTANT_STUDENTS

(WORKVAR OUT VARCHAR2) 　　　　　　RETURN NUMBER;

END P1;

/

CREATE OR REPLACE PACKAGE BODY 　　　STUDENT_ADMIN.P1 AS CURSOR C1 　　　RETURN STUDENT%ROWTYPE

IS

　　　　　　SELECT * FROM 　　　　　　STUDENT_ADMIN.STUDENT

　　　　　　　　WHERE NOT EXISTS

　　　　　　(SELECT 'X' FROM 　　　　　　STUDENT_ADMIN.GRADE

　　　　　　WHERE 　　　　　　GRADE.SSN=STUDENT.SSN)　ORDER 　　　　　　BY SSN;

FUNCTION SHOW_RELUCTANT_STUDENTS

　　　(WORKVAR OUT VARCHAR2) RETURN 　　　NUMBER IS

　　　WORKREC STUDENT%ROWTYPE;

　　　BEGIN

　　　　　　IF NOT C1%ISOPEN THEN OPEN C1;

　　　　　　ROWCOUNT :=0;

　　　　　　ENDIF;

　　　　　　FETCH C1 INTO WORKREC;

　　　　　　IF (C1%NOTFOUND) THEN

　　　　　　　　CLOSE C1;

　　　　　　　　ROWCOUNT :=0;

　　　　　　ELSE

　　　　　　　　WORKVAR := WORKREC.FNAME||' 　　　　　　　　'||WORKREC.LNAME||

　　　　　　　　', social security number 　　　　　　　　'||WORKREC.SSN||' is not enrolled

　　　　　　　　　　　in any classes!';

　　　　　　　　ROWCOUNT := ROWCOUNT + 1;

　　　　　　ENDIF;

RETURN(ROWCOUNT); CREATE PROCEDURE

STUDENT_ADMIN.SHOW_

RELUCTANT_STUDENTS

AS SELECT FNAME+'' +LNAME+', social 　　security number'+ SSN+' is not enrolled in any 　

　classes!'

FROM STUDENT_ADMIN.STUDENT S

WHERE NOT EXISTS

　　　(SELECT 'X' FROM 　　　STUDENT_ADMIN.GRADE G

　　　WHERE G.SSN=S.SSN)

ORDER BY SSN

RETURN@@ROWCOUNT

GO

　　　　　　EXCEPTION

WHEN OTHERS THEN

IF C1%ISOPEN THEN CLOSE C1;

ROWCOUNT :=0;

ENDIF;

RAISE_APPLICATION_ERROR(-20001,SQLERRM);

END SHOW_RELUCTANT_STUDENTS;

END P1;

/ 　

　

SQL Server不支持与Oracle包或者函数相似的构造，也不支持在创建程序时的CREATE OR REPLACE选项。

　

延迟过程的执行

Microsoft SQL Server提供了WAITFOR，允许开发人员给定一个时间，时间段，或者事件来触发一个语句块、

程序或者事务的执行。这是Transact-SQL对于Oracle中dbms_lock_sleep的等价。

WAITFOR {DELAY 'time' | TIME 'time'}

指示Microsoft SQL Server等待直到给定的时间过去以后再执行，最多可以到24小时。

在这里

DELAY

指示Microsoft SQL Server等待，直到给定数量的时间过去以后才执行，最多可以设置到24小时。

'time'

需要等待的时间，时间可以是任何可接受的datetime数据类型的格式，或者可以作为一个本地变量给出。

但是，不能指定datetime值的日期部分。

TIME

指示SQL Server等到指定的时间

例如：

BEGIN

WAITFOR TIME '22:20'

EXECUTE update_all_stats

END

　

指定程序中的参数

要在一个程序中指定一个参数，可以使用下面给出的语法。

Oracle Microsoft SQL　

Varname datatype

DEFAULT ; {@parameter data_type} [VARYING]

　　　[= default] [OUTPUT]

　

触发器（Triggers）

Oracle和Microsoft SQL Server都有触发器，但它们在执行上有些不同。