从SQL Server 向Oracle 8迁移的技术实现方案

不知道从哪里得到这个文档，有用就放上来了－gwb

数据库端SQL语法的迁移

以下为常用的SQL语法迁移，包括数据类型、ID列向SEQUENCE迁移、表（主键、外键、CHECK、UNIQUE、DEFAULT、INDEX）、游标、存储过程、函数、触发器、常用SQL语法与函数几个方面，考虑SQL SERVER的实际情况，没有涉及ORACLE特有的PACKAGE、EXCEPTION等。在以下的描述中，将SQL SERVER的TRANSACT-SQL简称为T-SQL。在ORACLE中，其语法集称为PL/SQL。

<一> 数据类型的迁移

<1>、ORACLE端语法说明

在ORACLE中，分析其数据类型，大致可分为数字、字符、日期时间和特殊四大类。其中，数字类型有NUMBER；字符类型有CHAR与VARCHAR2；日期时间类型只有DATE一种；除此之外，LONG、RAW、LONG RAW、BLOB、CLOB和BFILE等数据类型都可视为特殊数据类型。

<2>、SQL SERVER端语法说明

在SQL SERVER中，参照上面对ORACLE的划分，数据类型也大致可分为数字、字符、日期时间和特殊四大类。数字类型又可分为精确数值、近似数值、整数、二进制数、货币等几类，其中，精确数值有DECIMAL[(P[, S])]与NUMERIC[(P[, S])]；近似数值有FLOAT[(N)]；整数有INT、SMALLINT、TINYINT；二进制数有BINARY[(N)]、VARBINARY[(N)]；货币有MONEY、SMALLMONEY。字符类型有CHAR[(N)]与VARCHAR[(N)]。日期时间类型有DATETIME、SMALLDATETIME。除此之外，BIT、TIMESTAMP、TEXT和IMAGE、BINARY VARING等数据类型都可视为特殊数据类型。

<3>、从SQL SERVER向ORACLE的迁移方案

比较ORACLE与SQL SERVER在数据类型上的不同，当从SQL SERVER向ORACLE迁移时，可以做如下调整：

	SQL SERVER	ORACLE
数字类型	DECIMAL[(P[, S])]	NUMBER[(P[, S])]
	NUMERIC[(P[, S])]	NUMBER[(P[, S])]
	FLOAT[(N)]	NUMBER[(N)]
	INT	NUMBER
	SMALLINT	NUMBER
	TINYINT	NUMBER
	MONEY	NUMBER[19,4]
	SMALLMONEY	NUMBER[19,4]
字符类型	CHAR[(N)]	CHAR[(N)]
字符类型	VARCHAR[(N)]	VARCHAR2[(N)]
日期时间类型	DATETIME	DATE
日期时间类型	SMALLDATETIME	DATE
其它	TEXT	CLOB
	IMAGE	BLOB
	BIT	NUMBER（1）

方法:

公司原系统中的Money 用于金额时转换用number(14,2);用于单价时用 number(10,4)代替;

<二> ID列向SEQUENCE迁移

<1>、SQL SERVER端语法说明

在SQL SERVER中，可以将数据库中的某一字段定义为IDENTITY列以做主键识别，如：

jlbh numeric(12,0) identity(1,1) /*记录编号字段*/

CONSTRAINT PK_tbl_example PRIMARY KEY nonclustered (jlbh) /*主键约束*/

在这里，jlbh是一个ID列，在向具有该列的表插入记录时，系统将从1开始以1的步长自动对jlbh的值进行维护。

<2>、ORACLE端语法说明

但在ORACLE中，没有这样的ID列定义，而是采用另一种方法，即创建SEQUENCE。

如：

/*--1、创建各使用地区编码表--*/

drop table LT_AREA;

create table LT_AREA

(

area_id number(5,0) NOT NULL, /*地区编码*/

area_name varchar2(20) NOT NULL, /*地区名称*/

constraint PK_LT_AREA PRIMARY KEY(area_id)

);

/*--2、创建SEQUENCE，将列area_id 类ID化--*/

drop sequence SEQ_LT_AREA;

create sequence SEQ_LT_AREA increment by 1 /*该SEQUENCE以1的步长递增*/

start with 1 maxvalue 99999; /*从1开始，最大增长到99999*/

/*--3、实际操作时引用SEQUENCE的下一个值--*/

insert into LT_AREA(area_id, area_name) values(SEQ_LT_AREA.NEXTVAL, '深圳');

insert into LT_AREA(area_id, area_name) values(SEQ_LT_AREA.NEXTVAL, '广州');

insert into LT_AREA(area_id, area_name) values(SEQ_LT_AREA.NEXTVAL, '北京');

/*--4、新插入连续三条记录后，下一条语句运行后，‘上海’地区的area_id为4--*/

insert into LT_AREA(area_id, area_name) values(SEQ_LT_AREA.NEXTVAL, '上海');

<3>、从SQL SERVER向ORACLE的迁移方案

根据以上分析，当从SQL SERVER向ORACLE迁移时，可以做如下调整：

1、去掉建表语句中有关ID列的identity声明关键字；

2、创建SEQUENCE，将此SEQUENCE与需类ID化的列对应；

3、在INSERT语句中对相应列引用其SEQUENCE值：SEQUENCENAME.NEXTVAL

实际上，处理以上情况在ORACLE中采用的方法为对有自动增长字段的表增加一插入前触发器（具体资料见后“触发器”一节），如下：

CREATE OR REPLACE TRIGGER GenaerateAreaID

BEFORE INSERT ON LT_AREA

FOR EACH ROW

Select SEQ_LT_AREA.NEXTVAL INTO :NEW.ID

FROM DUAL;

BEGIN

END GenaerateAreaID;

GenaerateAreaID实际上修改了伪记录:new的area_id值。 :new最有用的一个特性----当该语句真正被执行时，:new中的存储内容就会被使用。所以系统每次都能自动生成新的号码。

<三> 表（主键、外键、CHECK、UNIQUE、DEFAULT、INDEX）

<1>、SQL SERVER端语法说明

有如下SQL SERVER语句：

/* ------------------------ 创建employee 表------------------------ */

IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME = ‘employee’

AND TYPE = ‘U’)

DROP TABLE employee

CREATE TABLE employee

(

emp_id empid /*empid为用户自定义数据类型*/

/*创建自命名主键约束*/

CONSTRAINT PK_employee PRIMARY KEY NONCLUSTERED

/*创建自命名CHECK约束*/

CONSTRAINT CK_emp_id CHECK (emp_id LIKE

'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' or

emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'),

/* CHECK约束说明：Each employee ID consists of three characters that

represent the employee's initials, followed by a five

digit number ranging from 10000 to 99999 and then the

employee's gender (M or F). A (hyphen) - is acceptable

for the middle initial. */

fname varchar(20) NOT NULL,

minit char(1) NULL,

lname varchar(30) NOT NULL,

ss_id varchar(9) UNIQUE, /*创建唯一性约束*/

job_id smallint NOT NULL

DEFAULT 1, /*设定DEFAULT值*/

job_lvl tinyint

DEFAULT 10, /*设定DEFAULT值*/

/* Entry job_lvl for new hires. */

pub_id char(4) NOT NULL

DEFAULT ('9952') /*设定DEFAULT值*/

REFERENCES publishers(pub_id), /*创建系统命名外键约束*/

/* By default, the Parent Company Publisher is the company

to whom each employee reports. */

hire_date datetime NOT NULL

DEFAULT (getdate()), /*设定DEFAULT值*/

/* By default, the current system date will be entered. */

CONSTRAINT FK_employee_job FOREIGN KEY (job_id)

REFERENCES jobs(job_id) /*创建自命名外键约束*/

)

/* --------------------- 创建employee表上的index --------------------- */

IF EXISTS (SELECT 1 FROM sysindexes

WHERE name = 'emp_pub_id_ind')

DROP INDEX employee. emp_pub_id_ind

CREATE INDEX emp_pub_id_ind

ON employee(pub_id)

<2>、ORACLE端语法说明

在ORACLE端的语法如下：

/* ---------------------- 创建employee 表---------------------- */

DROP TABLE employee；

CREATE TABLE employee

(

emp_id varchar2(9) /*根据用户自定义数据类型的定义调整为varchar2(9)*/

/*创建自命名主键约束*/

CONSTRAINT PK_employee PRIMARY KEY NONCLUSTERED

/*创建自命名CHECK约束*/

CONSTRAINT CK_emp_id CHECK (emp_id LIKE

'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' or

emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'),

/* CHECK约束说明：Each employee ID consists of three characters that

represent the employee's initials, followed by a five

digit number ranging from 10000 to 99999 and then the

employee's gender (M or F). A (hyphen) - is acceptable

for the middle initial. */

fname varchar2(20) NOT NULL,

minit varchar2(1) NULL,

lname varchar2(30) NOT NULL,

ss_id varchar2(9) UNIQUE, /*创建唯一性约束*/

job_id number(5,0) NOT NULL

/*这里考虑了SMALLINT的长度，也可调整为number*/

DEFAULT 1, /*设定DEFAULT值*/

job_lvl number(3,0)

/*这里考虑了TINYINT的长度，也可调整为number*/

DEFAULT 10, /*设定DEFAULT值*/

/* Entry job_lvl for new hires. */

pub_id varchar2(4) NOT NULL

DEFAULT ('9952') /*设定DEFAULT值*/

REFERENCES publishers(pub_id), /*创建系统命名外键约束*/

/* By default, the Parent Company Publisher is the company

to whom each employee reports. */

hire_date date NOT NULL

DEFAULT SYSDATE, /*设定DEFAULT值*/

/*这里，SQL SERVER的getdate()调整为ORACLE的SYSDATE*/

/* By default, the current system date will be entered. */

CONSTRAINT FK_employee_job FOREIGN KEY (job_id)

REFERENCES jobs(job_id) /*创建自命名外键约束*/

);

/* -------------------- 创建employee表上的index -------------------- */

DROP INDEX employee. emp_pub_id_ind;

CREATE INDEX emp_pub_id_ind ON employee(pub_id);

<3>、从SQL SERVER向ORACLE的迁移方案

比较这两段SQL代码，可以看出，在创建表及其主键、外键、CHECK、UNIQUE、DEFAULT、INDEX时，SQL SERVER 与ORACLE的语法大致相同,但时迁移时要注意以下情况:

（1） Oracle定义表字段的default属性要紧跟字段类型之后，如下：

Create table MZ_Ghxx

( ghlxh number primay key ,

rq date default sysdate not null,

….

而不能写成

Create table MZ_Ghxx

( ghlxh number primay key ,

rq date not null default sysdate,

….

2）T-SQL定义表结构时，如果涉及到用默认时间和默认修改人员，全部修改如下：

ZHXGRQ DATE DEFAULT SYSDATE NULL，

ZHXGR CHAR（8） DEFAULT ‘FUTIAN’ NULL，

3）如表有identity定段，要先将其记录下来，建完表之后，马上建相应的序列和表触发器，并作为记录。

<四> 游标

<1>、SQL SERVER端语法说明

1、DECLARE CURSOR语句

语法：

DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR

FOR select_statement

[FOR {READ ONLY | UPDATE [OF column_list ]}]

例：

DECLARE authors_cursor CURSOR FOR

SELECT au_lname, au_fname

FROM authors

WHERE au_lname LIKE ‘B%’

ORDER BY au_lname, au_fname

2、OPEN语句

语法：

OPEN cursor_name

例：

OPEN authors_cursor

3、FETCH语句

语法：

FETCH

FROM cursor_name

[INTO @variable_name1, @variable_name2,… ]

例：

FETCH NEXT FROM authors_cursor

INTO @au_lname, @au_fname

4、CLOSE语句

语法：

CLOSE cursor_name

例：

CLOSE authors_cursor

5、DEALLOCATE语句

语法：

DEALLOCATE cursor_name

例：

DEALLOCATE authors_cursor

6、游标中的标准循环与循环终止条件判断

（1）FETCH NEXT FROM authors_cursor INTO @au_lname, @au_fname

（2）-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

WHILE @@FETCH_STATUS = 0

BEGIN

-- Concatenate and display the current values in the variables.

PRINT "Author: " + @au_fname + " " + @au_lname

-- This is executed as long as the previous fetch succeeds.

FETCH NEXT FROM authors_cursor INTO @au_lname, @au_fname

END

（3）CLOSE authors_cursor

7、隐式游标

MSSqlServer中对于数据操纵语句受影响的行数，有一个全局的变量：@@rowcount，其实它是一个隐式的游标，它记载了上条数据操纵语句所影响的行数，当@@rowcount小于1时，表时，上次没有找到相关的记录,如下：

Update students set lastname = ‘John’ where student_id = ‘301’

If @@rowcount < 1 then

Insert into students values (‘301’,’stdiv’,’john’,’996-03-02’)

表示如果数据表中有学号为“301”的记录，则修改其名字为“John”，如果找不到相应的记录，则向数据库中插入一条“John”的记录。

8、示例：

-- Declare the variables to store the values returned by FETCH.

DECLARE @au_lname varchar(40), @au_fname varchar(20)

DECLARE authors_cursor CURSOR FOR

SELECT au_lname, au_fname

FROM authors

WHERE au_lname LIKE ‘B%’

ORDER BY au_lname, au_fname

OPEN authors_cursor

-- Perform the first fetch and store the values in variables.

-- Note: The variables are in the same order as the columns

-- in the SELECT statement.

FETCH NEXT FROM authors_cursor INTO @au_lname, @au_fname

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

WHILE @@FETCH_STATUS = 0

BEGIN

-- Concatenate and display the current values in the variables.

PRINT "Author: " + @au_fname + " " + @au_lname

-- This is executed as long as the previous fetch succeeds.

FETCH NEXT FROM authors_cursor INTO @au_lname, @au_fname

END

CLOSE authors_cursor

DEALLOCATE authors_cursor

<2>、ORACLE端语法说明

1、 DECLARE CURSOR语句

语法：

CURSOR cursor_name IS select_statement;

例：

CURSOR authors_cursor IS

SELECT au_lname, au_fname

FROM authors

WHERE au_lname LIKE ‘B%’

ORDER BY au_lname, au_fname;

2、 OPEN语句

语法：

OPEN cursor_name

例：

OPEN authors_cursor;

3、 FETCH语句

语法：

FETCH cursor_name INTO variable_name1 [, variable_name2,… ] ;

例：

FETCH authors_cursor INTO au_lname, au_fname;

4、 CLOSE语句

语法：

CLOSE cursor_name

例：

CLOSE authors_cursor;

5、简单游标提取循环结构与循环终止条件判断

1> 用%FOUND做循环判断条件的WHILE循环

（1）FETCH authors_cursor INTO au_lname, au_fname ；

（2）WHILE authors_cursor%FOUND LOOP

-- Concatenate and display the current values in the variables.

DBMS_OUTPUT.ENABLE;

DBMS_OUTPUT.PUT_LINE( ‘Author: ‘ || au_fname || ‘ ‘ || au_lname) ;

FETCH authors_cursor INTO au_lname, au_fname ；

END LOOP ;

（3）CLOSE authors_cursor ;

2> 用%NOTFOUND做循环判断条件的简单LOOP

END LOOP循环

（1）OPEN authors_cursor;

（2）LOOP

FETCH authors_cursor INTO au_lname, au_fname ；

-- Exit loop when there are no more rows to fetch.

EXIT WHEN authors_cursor%NOTFOUND ;

-- Concatenate and display the current values in the variables.

DBMS_OUTPUT.ENABLE;

DBMS_OUTPUT.PUT_LINE( ‘Author: ‘ || au_fname || ‘ ‘ || au_lname) ;

END LOOP ;

（3）CLOSE authors_cursor ;

3>用游标式FOR循环,如下：

DECLARE

CURSOR c_HistoryStudents IS

SELECT id,first_name,last_name

FROM Students

WHERE major = ‘History’

BEGIN

FOR v_StudentData IN c_HistoryStudents LOOP

INSERT INTO registered_students

(student_id,first_name,last_name,department,course)

VALUES(v_StudentData.ID,v_StudentData.first_name, v_StudentData.last_name,’HIS’,301);

END LOOP;

COMMIT;

END;

首先，记录v_StudentData没有在块的声明部分进行声明，些变量的类型是c_HistoryStudents%ROWTYPE，v_StudentData的作用域仅限于此FOR循环本身；其实，c_HistoryStudents以隐含的方式被打开和提取数据，并被循环关闭。

6、隐式游标SQL%FOUND 与SQL%NOTFOUND

与MSSQL SERVER 一样，ORACLE也有隐式游标，它用于处理INSERT、DELETE和单行的SELECT..INTO语句。因为SQL游标是通过PL/SQL引擎打开和关闭的，所以OPEN、FETCH和CLOSE命令是无关的。但是游标属性可以被应用于SQL游标，如下：

BEGIN

UPDATE rooms

SET number_seats = 100

WHERE room_id = 9990;

--如果找不相应的记录，则插入新的记录

IF SQL%NOTFOUND THEN

INSERT INTO rooms(room_id,number_seats)

VALUES (9990,100)

END IF

END;

7、示例：

-- Declare the variables to store the values returned by FETCH.

-- Declare the CURSOR authors_cursor.

DECLARE

au_lname varchar2(40) ;

au_fname varchar2(20) ;

CURSOR authors_cursor IS

SELECT au_lname, au_fname

FROM authors

WHERE au_lname LIKE ‘B%’

ORDER BY au_lname, au_fname;

BEGIN

OPEN authors_cursor;

FETCH authors_cursor INTO au_lname, au_fname ；

WHILE authors_cursor%FOUND LOOP

-- Concatenate and display the current values in the variables.

DBMS_OUTPUT.ENABLE;

DBMS_OUTPUT.PUT_LINE( ‘Author: ‘ || au_fname || ‘ ‘ || au_lname) ;

FETCH authors_cursor INTO au_lname, au_fname ；

END LOOP ;

CLOSE authors_cursor ;

END ;

<3>、从SQL SERVER向ORACLE的迁移方案

比较上述SQL代码，在迁移过程中要做如下调整：

（1）T-SQL对CURSOR的声明在主体代码中，而PL/SQL中对CURSOR的声明与变

量声明同步，都要在主体代码（BEGIN关键字）之前声明，所以在迁移时要

将游标声明提前,MSSQL SERVER的Cursor定义后的参数省去；

（2）对CUOSOR操作的语法中PL/SQL没有T-SQL里DEALLOCATE CURSOR这一部分，

迁移时要将该部分语句删除。

（3）PL/SQL 与T-SQL对游标中的循环与循环终止条件判断的处理不太一样，根

据前面的讨论并参考后面对两种语法集进行控制语句对比分析部分的叙述，

建议将T-SQL中的游标提取循环调整为PL/SQL中的WHILE游标提取循环结

构，这样可保持循环的基本结构大致不变，同时在进行循环终止条件判断时

要注意将T-SQL中的对@@FETCH_STATUS全局变量的判断调整为对

CURSOR_NAME%FOUND语句进行判断。

（4）对于T-SQL，没有定义语句结束标志，而PL/SQL用“；”结束语句。

（5）对于原MSSQL SERVER类型的游标，如果游标取出的值没有参与运算的，全部采用FOR循环方式来替换；而对于取出的值还要进行其它运算的，可以采用直接在定义变量位置定义变量。

(6)MSSQL中对于同一游标重复定义几次的情况在ORACLE中可通过游标变量来解决.如下：

MSSQL SERVER 中：

Declare cur_ypdm cursor for

Select * from yp

Open cur_yp

Fetch cur_yp into @yp,@mc …

While @@fetch_status <> -1

Begin

If @@fetch_status <> -2

Begin

….

End

Fetch cur_yp into @yp,@mc …

End

Close cur_ypdm

Deallocate cur_ypdm

Declare cur_ypdm cursor for

Select * from yp where condition 1

Open cur_yp

Fetch cur_yp into @yp,@mc …

While @@fetch_status <> -1

Begin

If @@fetch_status <> -2

Begin

….

End

Fetch cur_yp into @yp,@mc …

End

Close cur_ypdm

Deallocate cur_ypdm

Declare cur_ypdm cursor for

Select * from yp where condition 2

Open cur_yp

Fetch cur_yp into @yp,@mc …

While @@fetch_status <> -1

Begin

If @@fetch_status <> -2

Begin

….

End

Fetch cur_yp into @yp,@mc …

End

Close cur_ypdm

Deallocate cur_ypdm

在程序中，三次定义同一游标cur_yp

在迁移过程中，最好先定义一游标变量，在程序中用open打开，如下：

declare

type cur_type is ref cur_type;

cur_yp cur_type;

…

begin

open cur_yp for select * from yp;

loop

fetch cur_yp into yp,mc …

Exit When cur_yp%NotFound;

….

end loop;

close cur_yp;

open cur_yp for select * from yp where condition1;

loop

fetch cur_yp into yp,mc …

Exit When cur_yp%NotFound;

….

end loop;

close cur_yp;

open cur_yp for select * from yp where condition2;

loop

fetch cur_yp into yp,mc …

Exit When cur_yp%NotFound;

….

end loop;

close cur_yp;

end;

(7)请注意，游标循环中中一定要退出语名，要不然执行时会出现死循环。

<五> 存储过程/函数

<1>、SQL SERVER端语法说明

1、语法：

CREATE PROC[EDURE] [owner.]procedure_name [;number]

[ (parameter1[, parameter2]…[, parameter255])]

[ {FOR REPLICATION} | {WITH RECOMPILE}

[ {[WITH] | [ , ] } ENCRYPTION ] ]

sql_statement [

其中，Parameter = @parameter_name datatype [=default] [output]

说明：T-SQL中存储过程的结构大致如下

CREATE PROCEDURE procedure_name

/*输入、输出参数的声明部分*/

DECLARE

/*局部变量的声明部分*/

BEGIN

/*主体SQL语句部分*/

/*游标声明、使用语句在此部分*/

END

2、示例：

IF EXISTS(SELECT 1 FROM sysobjects

WHERE name = 'titles_sum' AND type = 'P')

DROP PROCEDURE titles_sum

CREATE PROCEDURE titles_sum

@TITLE varchar(40) = '%', @SUM money OUTPUT

BEGIN

SELECT 'Title Name' = title

FROM titles

WHERE title LIKE @TITLE

SELECT @SUM = SUM(price)

FROM titles

WHERE title LIKE @TITLE

END

<2>、ORACLE端PROCEDURE语法说明

1、语法：

CREATE [OR REPLACE] PROCEDURE procedure_name

[ (parameter1 [ {IN | OUT | IN OUT } ] type ,

…

parametern [ {IN | OUT | IN OUT } ] type ) ]

{ IS | AS }

[BEGIN]

sql_statement [

n] ;

[END] ;

说明：PL/SQL中存储过程的结构大致如下

CREATE OR REPLACE PROCEDURE procedure_name

（ /*输入、输出参数的声明部分*/ )

/*局部变量、游标等的声明部分*/

BEGIN

/*主体SQL语句部分*/

/*游标使用语句在此部分*/

EXCEPTION

/*异常处理部分*/

END ；

2、示例：

CREATE OR REPLACE PROCEDURE drop_class

( arg_student_id IN varchar2,

arg_class_id IN varchar2,

status OUT number )

counter number ;

BEGIN

status := 0 ;

-- Verify that this class really is part of the student’s schedule.

select count (*) into counter

from student_schedule

where student_id = arg_student_id

and class_id = arg_class_id ;

IF counter = 1 THEN

delete from student_schedule

where student_id = arg_student_id

and class_id = arg_class_id ;

status := -1 ;

END IF ;

END ;

<3>ORACLE端FUNCTION语法说明

(1) 语法

CREATE [OR REPLACE] FUNCTION function_name

[(argument [{IN | OUT | IN OUT }] ) type,

…

[(argument [{IN | OUT | IN OUT }] ) type

RETURN return_type {IS | AS}

BEGIN

…

END;

关键字return 指定了函数返回值的数据类型。它可以是任何合法的PL/SQL数据类型。每个函数都必须有一个return 子句，因为在定义上函数必须返回一个值给调用环境。

（2）示例

CREATE OR REPLACE FUNCTION blanace_check(Person_Name IN varchar2)

RETURN NUMBER

Balance NUMBER(10,2);

BEGIN

Select sum(decode(acton,’BOUGHT’,Amount,0))

INTO balance

FROM ledger

WHERE Person = Person_name;

RETURN (balance);

END;

(3)过程与函数的区别

函数可以返回一个值给调用环境；而过程不能，过程只能通过返回参数（带“OUT”或“IN OUT”）传回去数据。

<4>从SQL SERVER向ORACLE的迁移方案

通过比较上述SQL语法的差异，在迁移时必须注意以下几点：

1、对于有返回单值的MSSQL存储过程，在数据库移值最好转换成ORALCE的函数；对于MSSQL有大量数据的处理而又不需返回值的存储过程转换成ORACLE的过程

2、在T-SQL中，输入、输出参数定义部分在“CREATE…”和“AS”之间，前后

没有括号；而在PL/SQL中必须有“（”和“）”与其他语句隔开。

3、在T-SQL中，声明局部变量时，前面要有DECLARE关键字；

而在PL/SQL中不用DECLARE关键字。

4、在T-SQL中，参数名的第一个字符必须是“@”，并符合标识符的规定；

而在PL/SQL中，参数名除符合标识符的规定外没有特殊说明,T-SQL中,对于参数可其数据类型及其长度和精度;但是PL/SQL中除了引用%TYPE和%ROWTYPE之外,不能在定义参数数据类型时给出长度和精度,如下:

CREATE OR REPLACE PROCEDURE PROC_SELE_YS

(YSDM CHAR(6),GZ NUMBER(14,4))

BEGIN

…

END;

是错误的,应如下定义

CREATE OR REPLACE PROCEDURE PROC_SELE_YS

(YSDM CHAR,GZ NUMBER)

BEGIN

…

END;

或者

CREATE OR REPLACE PROCEDURE PROC_SELE_YS

(YSDM YSDMB.YSDM%TYPE,GZ YSDMB.GZ%TYPE)

BEGIN

…

END;

5、对于T-SQL，游标声明在主体SQL语句中，即声明与使用语句同步；

而在PL/SQL中，游标声明在主体SQL语句之前，与局部变量声明同步。

6、对于T-SQL，在主体SQL语句中用如下语句对局部变量赋值（初始值或

数据库表的字段值或表达式）：

“SELECT 局部变量名 = 所赋值（初始值或数据库表的字段值或表达式）”；

而在PL/SQL中，将初始值赋给局部变量时，用如下语句：

“局部变量名 : = 所赋值（初始值或表达式）;” ，

将检索出的字段值赋给局部变量时，用如下语句：

“SELECT 数据库表的字段值 INTO 局部变量名 …” 。

7、在PL/SQL中，可以使用%TYPE来定义局部变量的数据类型。说明如下：

例如，students表的first_name列拥有类型VARCHAR2(20)，基于这点，

我们可以按照下述方式声明一个变量：

V_FirstName VARCHAR2(20) ;

但是如果改变了first_name列的数据类型则必须修改该声明语句，因此可以采

用%TYPE进行变量数据类型声明：

V_FirstName students.first_name%TYPE ;

这样，该变量在存储过程编译时将由系统自动确定其相应数据类型。

8、对于T-SQL，没有定义语句结束标志，而PL/SQL用“END <过程名>；”结束语句。

9、存储过程的调用要注意：在MSSQLSERVER中的格式为“EXEC Procedure_Name {arg1,arg2,…}，但在ORACLE中直接引用过程名即可，如要执行存储过程DefaltNo，其参数为“9”，则执行时为 Default(“9”)。

10、ORACLE 数据库的存储过程不支持用select 子句直接返回一个数据集，要做到通过程产生一记录集有两种方案：

方案一：采用包和游标变量

第一步,创建一个包，定义一个游标变量

create package p_name

type cursor_name is ref cursor;

end;

第二步，创建过程，但是基返回参数用包中的游标类型

create procedure procedure_name(s in out p_name.cursor_name) is

begin

open s for select * from table_name

;

end;

这样，通过存储过程就可以返回一个数据集了，但用到这种情况，过程的参数中只这返回结果的游标参数可以带关键字”OUT”,其它不能带”out”，否则，系统会出现导常。

方案二：通过中间表，建一中间表，其表格的列为所需数据列再加上一个序列字段。过程的处理为将数据插入到中间表中，同时通过

select userenv(‘sessionid’) from dual;取得当前连接会话的序号，将取得的序号值放置到序列字段中，同时存储过程返回连接会话的序号，前台PB程序直接访问中间表，数据窗口在检索时通过序号参数可将所需的数据检索出来。

<六> 触发器

<1>、SQL SERVER端语法说明

1、语法：

CREATE TRIGGER [owner.]trigger_name

ON [owner.]table_name

FOR { INSERT, UPDATE, DELETE }

[WITH ENCRYPTION]

sql_statement [

或者使用IF UPDATE子句：

CREATE TRIGGER [owner.]trigger_name

ON [owner.]table_name

FOR { INSERT, UPDATE }

[WITH ENCRYPTION]

IF UPDATE (column_name)

[{AND | OR} UPDATE (column_name)…]

sql_statement [

2、示例：

IF EXISTS (SELECT 1 FROM sysobjects

WHERE name = 'reminder' AND type = 'TR')

DROP TRIGGER reminder

CREATE TRIGGER employee_insupd

ON employee

FOR INSERT, UPDATE

/* Get the range of level for this job type from the jobs table. */

DECLARE @min_lvl tinyint,

@max_lvl tinyint,

@emp_lvl tinyint,

@job_id smallint

SELECT @min_lvl = min_lvl,

@max_lvl = max_lvl,

@emp_lvl = i.job_lvl,

@job_id = i.job_id

FROM employee e, jobs j, inserted i

WHERE e.emp_id = i.emp_id AND i.job = j.job_id

IF (@job_id = 1) and (@emp_lvl <> 10)

BEGIN

RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)

ROLLBACK TRANSACTION

END

ELSE

IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)

BEGIN

RAISERROR ('The level for job_id:%d should be between %d and %d.',

16, 1, @job_id, @min_lvl, @max_lvl)

ROLLBACK TRANSACTION

END

<2>、ORACLE端语法说明

1、语法：

CREATE [OR REPLACE] TRIGGER trigger_name

{ BEFORE | AFTER } triggering_event ON table_name

[ FOR EACH ROW ]

[ WHEN trigger_condition ]

trigger_body ;

2、使用说明与示例：

（1）、上语法中，trigger_event 是对应于DML的三条语句INSERT、UPDATE、

DELETE；table_name是与触发器相关的表名称；FOR EACH ROW是可选

子句，当使用时，对每条相应行将引起触发器触发；condition是可选的

ORACLE BOOLEAN条件，当条件为真时触发器触发；trigger_body是触发

器触发时执行的PL/SQL块。

（2）、ORACLE触发器有以下两类：

1> 语句级（Statement-level）触发器，在CREATE TRIGGER语句中不

包含FOR EACH ROW子句。语句级触发器对于触发事件只能触发一次，

而且不能访问受触发器影响的每一行的列值。一般用语句级触发器处理

有关引起触发器触发的SQL语句的信息——例如，由谁来执行和什么时

间执行。

2> 行级（Row-level）触发器，在CREATE TRIGGER语句中

包含FOR EACH ROW子句。行级触发器可对受触发器影响的每一行触

发，并且能够访问原列值和通过SQL语句处理的新列值。行级触发器的

典型应用是当需要知道行的列值时，执行一条事务规则。

（3）在触发器体内，行级触发器可以引用触发器触发时已存在的行的列值，这些

值倚赖于引起触发器触发的SQL语句。

1> 对于INSERT语句，要被插入的数值包含在new.column_name，这里的

column_name是表中的一列。

2> 对于UPDATE语句，列的原值包含在old.column_name中，数据列的新

值在new.column_name中。

3> 对于DELETE语句，将要删除的行的列值放在old.column_name中。

触发语句

：old

：new

INSERT

无定义——所有字段都是NULL

当该语句完成时将要插入的数值

UPDATE

在更新以前的该行的原始取值

当该语句完成时将要更新的新值

DELETE

在删除行以前的该行的原始取值

未定义——所有字段都是NULL

4> 在触发器主体中，在new和old前面的“：”是必需的。而在触发器的

WHEN子句中，:new和:old记录也可以在WHEN子句的condition内部

引用，但是不需要使用冒号。例如，下面CheckCredits触发器的主体仅

当学生的当前成绩超过20时才会被执行：

CREATE OR REPLACE TRIGGER CheckCredits

BEFORE INSERT OR UPDATE OF current_credits ON students

FOR EACH ROW

WHEN (new.current_credits > 20)

BEGIN

/*Trigger body goes here. */

END ;

但CheckCredits也可以按下面方式改写：

CREATE OR REPLACE TRIGGER CheckCredits

BEFORE INSERT OR UPDATE OF current_credits ON students

FOR EACH ROW

BEGIN

IF :new.current_credits > 20 THEN

/*Trigger body goes here. */

END IF ;

END ;

注意，WHEN子句仅能用于行级触发器，如果使用了它，那么触发器主体

仅仅对那些满足WHEN子句指定的条件的行进行处理。

（4）触发器的主体是一个PL/SQL块，在PL/SQL块中可以使用的所有语句在触

发器主体中都是合法的，但是要受到下面的限制：

1> 触发器不能使用事务控制语句，包括COMMIT、ROLLBACK或

SAVEPOINT。ORACLE保持这种限制的原因是：如果触发器遇到错误时，

由触发器导致的所有数据库变换均能被回滚（roll back）取消；但如果

触发器确认（commit）了对数据库进行的部分变换，ORACLE就不能完全

回滚（roll back）整个事务。

2> 在触发器主体中调用到的存储过程的实现语句里也不能使用事务控制语

句。

3> 触发器主体不能声明任何LONG或LONG RAW变量。而且，:new和:old

不能指向定义触发器的表中的LONG或LONG RAW列。

4> 当声明触发器的表中有外键约束时，如果将定义触发器的表和需要作为

DELETE CASCADE参考完整性限制的结果进行更新的表称为变化表，

将外键相关联的表称为限制表，则在此触发器主体中的SQL语句不允许

读取或修改触发语句的任何变化表，也不允许读取或修改限制表中的主

键、唯一值列或外键列。

（5）以下是建立一个事前插入触发器的示例：

CREATE OR REPLACE TRIGGER Credit_Charge_Log_Ins_Before

BEFORE insert ON Credit_Charge_Log

FOR EACH ROW

DECLARE

Total_for_past_3days number ;

BEGIN

-- Check the credit charges for the past 3 days.

-- If they total more than $1000.00, log this entry

-- int the Credit_Charge_Attempt_Log for further handling.

select sum ( amount ) into total_for_past_3days

from Credit_Charge_Log

where Card_Number = :new.Card_Number

and Transaction_Date >= sysdate – 3;

IF total_for_past_3days > 1000.00 THEN

insert into credit_Charge_Attemp_Log

(Card_Number, Amount, Vendor_ID, Transaction_Date)

values

(:new.Card_Number, :new.Amount,

:new.Vendor_ID, :new.Transaction_Date);

END IF ;

END ;

<3>、从SQL SERVER向ORACLE的迁移方案

1、通过比较上面SQL语法的不同并考虑现有SQL SERVER的实际编程风格，在从

T-SQL向PL/SQL迁移时，要遵守下面规则：

1> 在CREATE TRIGGER定义中采用AFTER关键字，即调整为事后触发器。

2> 在CREATE TRIGGER定义中采用FOR EACH ROW关键字，即调整为行级触发

器。

3> 将触发器主体中的“inserted”调整为“:new”，将“deleted”调整为“:old”。

4> 在触发器主体中禁用CURSOR操作:new与:old。

5> 在触发器主体中禁用COMMIT、ROLLBACK、SAVEPOINT等事务控制语句。

2、用触发器解决ID列向SEQUENCE迁移的问题：

下面的GenerateStudentID触发器使用了:new。这是一个before INSERT触

发器，其目的是使用student_sequence序列所产生的数值填写

students表的ID字段。

例：

CREATE OR REPLACE TRIGGER GenerateStudentID

BEFORE INSERT ON students

FOR EACH ROW

BEGIN

SELECT student_sequence.nextval

INTO :new.ID

FROM dual;

END;

在上面的触发器主体中，GenerateStudentID实际上修改了:new.ID的值。这

是:new最有用的一个特性——当该语句真正被执行时，:new中的存储内容就

将被使用。有了这个触发器，我们就可以使用下面这样的INSERT语句，而不

会产生错误：

INSERT INTO students (first_name, last_name)

VALUES (‘LUO’, ‘TAO’) ;

尽管我们没有为主键列ID（这是必需的）指定取值，触发器将会提供所需要

的取值。事实上，如果我们为ID指定了一个取值，它也将会被忽略，因为触

发器修改了它。如果我们使用下面的语句：

INSERT INTO students (ID, first_name, last_name)

VALUES (-789, ‘LUO’, ‘TAO’) ;

其处理结果还是相同的。无论在哪种情况下，student_sequence.nextval都

将用作ID列值。

由此讨论，可以采用这种方法处理SQL SERVER中ID列向ORACLE的SEQUENCE

转换的问题。

另外，由于上面的原因，我们不能在after行级触发器中修改 :new，因为该

语句已经被处理了。通常，:new仅仅在before行级触发器中被修改，而:old

永远不会被修改，仅仅可以从它读出数据。

此外，:new和:old记录仅仅在行级触发器内部是有效的。如果试图要从语句

级触发器进行引用，将会得到一个编译错误。因为语句级触发器只执行一次

——尽管语句要处理许多行——所以:new和:old是没有意义的，因为怎么确

定它们引用的会是哪一行呢？

<七> 常用SQL语法与函数

<1>、SQL SERVER端常用语法说明

1、使用局部变量：

1> 变量定义：

DECLARE @variable_name datatype [,…]

例：

declare

@name varchar(30),

@type int

2> 给变量赋值：

方法一：

例：

declare @int_var int

select @int_var = 12

方法二：

例：

declare

@single_auth varchar(40),

@curdate datetime

select @single_auth = au_lname,

@curdate = getdate()

from authors

where au_id = ‘123-45-6789’

2、使用T-SQL标准控制结构：

1> 定义语句块

语法：

BEGIN

Statements

END

2> IF

ELSE语句

语法：

IF boolean_expression

{ statement | statement_block }

ELSE

{ statement | statement_block }

示例：

if (select avg(price) from titles where type = ‘business’) > $19.95

print ‘The average price is greater then $19.95’

else

print ‘The average price is less then $19.95’

3> IF EXISTS语句

语法：

IF [not] EXISTS (select_statement)

{ statement | statement_block }

[ELSE

{ statement | statement_block }]

示例：

declare

@lname varchar(40),

@msg varchar(255)

select @lname = ‘Smith’

if exists(select * from titles where au_lname = @lname)

begin

select @msg = ‘There are authors named’ + @lname

print @msg

end

else

begin

select @msg = ‘There are no authors named’ + @lname

print @msg

end

4> 循环语句：

WHILE

语法：

WHILE boolean_condition

[{ statement | statement_block }]

[BREAK]

[condition]

示例：

declare

@avg_price money,

@max_price money,

@count_rows int,

@times_thru_the_loop int

select @avg_price = avg(price),

@max_price = max(price),

@count_rows = count(*),

@times_thru_the_loop = 0

from titles

while @avg_price < $25 and (@count_rows < 10 or @max_price < $50)

begin

select @avg_price = avg(price) * 1.05,

@max_price = max(price) * 1.05,

@time_thru_the_loop = @time_thru_the_loop + 1

end

if @time_thru_the_loop = 0

select @time_thru_the_loop = 1

update titles

set price = price * power(1.05, @time_thru_the_loop)

4> GOTO语句

语法：

GOTO label

label:

示例：

begin transaction

insert tiny(c1) values(1)

if @@error != 0 goto error_handler

commit transaction

return

error_handler:

rollback transaction

return

5> RETURN语句

语法：

RETURN

（1）用于无条件退出一个批处理、存储过程或触发器。

示例：

if not exists(select 1 from inventory

where item_num = @item_num)

begin

raiseerror 51345 ‘Not Found’

return

end

print ‘No error found’

return

（2）用于存储过程中返回状态值。

示例：

create procedure titles_for_a_pub

(@pub_name varchar(40) = null)

if @pub_name is null

return 15

if not exists(select 1 from publishers

where pub_name = @pub_name)

return –101

select t.tile from publishers p, titles t

where p.pub_id = t.pub_id

and pub_name = @pub_name

return 0

3、T-SQL中的游标提取循环语句：

（1）FETCH [NEXT FROM] cursor_name INTO @variable_1,

@variable_n

（2）WHILE @@FETCH_STATUS = 0

BEGIN

Other_statements

FETCH [NEXT FROM] cursor_name INTO @variable_1,

@variable_n

END

（3）CLOSE cursor_name

4、T-SQL中的事务处理语句：

1> 开始一个事务：

BEGIN TRAN[SACTION [transaction_name]]

2> 提交一个事务：

COMMIT TRAN[SACTION [transaction_name]]

3> 回滚一个事务：

ROLLBACK TRAN[SACTION [transaction_name]]

4> 使用事务保存点：

BEGIN TRAN[SACTION [transaction_name]]

SAVE TRAN[SACTION] savepoint_name

ROLLBACK TRAN[SACTION] savepoint_name

COMMIT TRAN[SACTION [transaction_name]]

5、T-SQL中可用于错误判断或其它处理的全局变量：

1> @@rowcount：前一条命令处理的行数

2> @@error：前一条SQL语句报告的错误号

3> @@trancount：事务嵌套的级别

4> @@transtate：事务的当前状态

5> @@tranchained：当前事务的模式（链接的（chained）或非链接的）

6> @@servername：本地SQL SERVER的名称

7> @@version ： SQL SERVER和O/S的版本级别

8> @@spid：当前进程的id

9> @@identity：上次insert操作中使用的identity值

10> @@nestlevel：存储过程/触发器中的嵌套层

11> @@fetch_status：游标中上条fetch语句的状态

6、使用标准内置错误消息发送函数：

函数说明：

RAISERROR ({msg_id | msg_str}, severity, state

[, argument1 [，argument2][，

] )

[WITH LOG]

其中，msg_id表示错误号，用户定义错误消息的错误号在50001到2147483647之

间，特定的消息会引起错误50000。msg_str是错误消息正文，最多可有255个字

符。Severity描述了与这个消息关联的用户定义的严重性级别，取值包括0和10

至25之间的任何整数。State描述了错误的“调用状态”，它是1到127之间的整

数值。Argument定义用于代替在msg_str中定义的变量或对应与msg_id的消息的

参数。WITH LOG表示要在服务器错误日志和事件日志中记录错误。

例1：

RAISEERROR( ‘Invalid customer id in order.’, 16, 1)

则返回：

Msg 50000, Level 16, State 1

Invalid customer id in order.

例2：

sp_addmessage 52000, 16, ‘Invalid customer id %s in order’

RAISEERROR( 52000, 16, 1, ‘ID52436’)

则返回：

Msg 52000, Level 16, State 1

Invalid customer id ID52436 in order.

<2>、ORACLE端常用语法说明

1、使用局部变量：

1> 定义变量：

VARIABLE_NAME DATA TYPE [ := INITIAL VALUE ] ;

例：定义变量

v_Num number;

v_string varchar2(50);

例：定义变量并赋初值

v_Num number := 1 ;

v_string varchar2(50) := ‘Hello world!’ ;

2> 给变量赋值：

方法一：

例：

v_Num := 1;

v_string := ‘Hello world!’;

方法二：

例：

SELECT first_name INTO v_String

FROM students

WHERE id = v_Num ;

2、使用PL/SQL标准控制结构：

1> 定义语句块

语法：

BEGIN

Statements ；

END ；

2> IF

THEN

ELSE语句

语法：

IF boolean_expression THEN

{ statement | statement_block } ；

[ELSIF boolean_expression THEN /*注意此处的写法—— ELSIF */

{ statement | statement_block } ；]

[ELSE

{ statement | statement_block } ；]

END IF ；

示例：

v_NumberSeats rooms.number_seats%TYPE;

v_Comment VARCHAR2(35);

BEGIN

/* Retrieve the number of seats in the room identified by ID 99999.

Store the result in v_NumberSeats. */

SELECT number_seats

INTO v_NumberSeats

FROM rooms

WHERE room_id = 99999;

IF v_NumberSeats < 50 THEN

v_Comment := 'Fairly small';

ELSIF v_NumberSeats < 100 THEN

v_Comment := 'A little bigger';

ELSE

v_Comment := 'Lots of room';

END IF;

END;

3> 循环语句：

（1）简单循环语句：

语法：

LOOP

{ statement | statement_block } ；

[EXIT [WHEN condition] ；]

END LOOP ；

其中，语句EXIT [WHEN condition];等价于

IF condition THEN

EXIT ；

END IF ；

示例1：

v_Counter BINARY_INTEGER := 1;

BEGIN

LOOP

-- Insert a row into temp_table with the current value of the

-- loop counter.

INSERT INTO temp_table

VALUES (v_Counter, 'Loop index');

v_Counter := v_Counter + 1;

-- Exit condition - when the loop counter > 50 we will

-- break out of the loop.

IF v_Counter > 50 THEN

EXIT;

END IF;

END LOOP;

END;

示例2：

v_Counter BINARY_INTEGER := 1;

BEGIN

LOOP

-- Insert a row into temp_table with the current value of the

-- loop counter.

INSERT INTO temp_table

VALUES (v_Counter, 'Loop index');

v_Counter := v_Counter + 1;

-- Exit condition - when the loop counter > 50 we will

-- break out of the loop.

EXIT WHEN v_Counter > 50;

END LOOP;

END;

（2）WHILE循环语句：

语法：

WHILE condition LOOP

{ statement | statement_block } ；

END LOOP ；

示例1：

v_Counter BINARY_INTEGER := 1;

BEGIN

-- Test the loop counter before each loop iteration to

-- insure that it is still less than 50.

WHILE v_Counter <= 50 LOOP

INSERT INTO temp_table

VALUES (v_Counter, 'Loop index');

v_Counter := v_Counter + 1;

END LOOP;

END;

示例2：

v_Counter BINARY_INTEGER;

BEGIN

-- This condition will evaluate to NULL, since v_Counter

-- is initialized to NULL by default.

WHILE v_Counter <= 50 LOOP

INSERT INTO temp_table

VALUES (v_Counter, 'Loop index');

v_Counter := v_Counter + 1;

END LOOP;

END;

（3）数字式FOR循环语句：

语法：

FOR loop_counter IN [REVERSE] low_bound..high_bound LOOP

{ statement | statement_block } ；

END LOOP ；

这里，loop_counter是隐式声明的索引变量。

示例1：

FOR循环的循环索引被隐式声明为BINARY_INTEGER。在循环前面没有

必要声明它，如果对它进行了声明，那么循环索引将屏蔽外层的声明，

如下所示

v_Counter NUMBER := 7;

BEGIN

-- Inserts the value 7 into temp_table.

INSERT INTO temp_table (num_col)

VALUES (v_Counter);

-- This loop redeclares v_Counter as a BINARY_INTEGER, which

-- hides the NUMBER declaration of v_Counter.

FOR v_Counter IN 20..30 LOOP

-- Inside the loop, v_Counter ranges from 20 to 30.

INSERT INTO temp_table (num_col)

VALUES (v_Counter);

END LOOP;

-- Inserts another 7 into temp_table.

INSERT INTO temp_table (num_col)

VALUES (v_Counter);

END;

示例2：

如果在FOR循环中有REVERSE关键字，那么循环索引将从最大值向最

小值进行循环。请注意语法是相同的——仍然首先书写的是最小值，

如下所示

BEGIN

FOR v_Counter IN REVERSE 10..50 LOOP

-- v_Counter will start with 50, and will be decremented

-- by 1 each time through the loop.

NULL;

END LOOP;

END;

示例3：

FOR循环中的最大值和最小值没有必要必须是数字型文字，它们可以

是能够被转换为数字值的任何表达式，如下所示

v_LowValue NUMBER := 10;

v_HighValue NUMBER := 40;

BEGIN

FOR v_Counter IN REVERSE v_LowValue..v_HighValue LOOP

INSER INTO temp_table

VALUES (v_Counter, ‘Dynamically sqecified loop range’);

END LOOP;

END;

4> GOTO语句

语法：

GOTO label;

<<label>>

示例：

v_Counter BINARY_INTEGER := 1;

BEGIN

LOOP

INSERT INTO temp_table

VALUES (v_Counter, 'Loop count');

v_Counter := v_Counter + 1;

IF v_Counter > 50 THEN

GOTO l_EndOfLoop;

END IF;

END LOOP;

<<l_EndOfLoop>>

INSERT INTO temp_table (char_col)

VALUES ('Done!');

END;

5> EXIT语句

语法：

EXIT;

参见上面的PL/SQL标准控制结构之循环语句说明部分。

3、PL/SQL中的游标提取循环语句：

1> 简单循环

此循环采用简单的循环语法（LOOP..END LOOP），如下所示

-- Declare variables to hold information about the students

-- majoring in History.

v_StudentID students.id%TYPE;

v_FirstName students.first_name%TYPE;

v_LastName students.last_name%TYPE;

-- Cursor to retrieve the information about History students

CURSOR c_HistoryStudents IS

SELECT id, first_name, last_name

FROM students

WHERE major = 'History';

BEGIN

-- Open the cursor and initialize the active set

OPEN c_HistoryStudents;

LOOP

-- Retrieve information for the next student

FETCH c_HistoryStudents INTO v_StudentID, v_FirstName, v_LastName;

-- Exit loop when there are no more rows to fetch

EXIT WHEN c_HistoryStudents%NOTFOUND;

-- Process the fetched rows. In this case sign up each

-- student for History 301 by inserting them into the

-- registered_students table. Record the first and last

-- names in temp_table as well.

INSERT INTO registered_students (student_id, department, course)

VALUES (v_StudentID, 'HIS', 301);

INSERT INTO temp_table (num_col, char_col)

VALUES (v_StudentID, v_FirstName || ' ' || v_LastName);

END LOOP;

-- Free resources used by the cursor

CLOSE c_HistoryStudents;

-- Commit our work

COMMIT;

END;

请注意，EXIT WHEN语句的位置是紧跟在FETCH语句的后边。在检索完最后一

个行以后，c_HistoryStudents%NOTFOUND变为TRUE，该循环退出。EXIT WHEN

语句的位置也在数据处理部分的前面，这样做是为了确保该循环过程不处理任

何重复行。

2> WHILE循环

此循环采用WHILE .. LOOP的循环语法，如下所示

-- DECLARE cursor to retrieve the information about History students

CURSOR c_HistoryStudents IS

SELECT id, first_name, last_name

FROM students

WHERE major = 'History';

-- Declare a record to hold the fetched information.

v_StudentData c_HistoryStudents%ROWTYPE;

BEGIN

-- Open the cursor and initialize the active set

OPEN c_HistoryStudents;

-- Retrieve the first row, to set up for the WHILE loop

FETCH c_HistoryStudents INTO v_StudentData;

-- Continue looping while there are more rows to fetch

WHILE c_HistoryStudents%FOUND LOOP

-- Process the fetched rows, in this case sign up each

-- student for History 301 by inserting them into the

-- registered_students table. Record the first and last

-- names in temp_table as well.

INSERT INTO registered_students (student_id, department, course)

VALUES (v_StudentData.ID, 'HIS', 301);

INSERT INTO temp_table (num_col, char_col)

VALUES (v_StudentData.ID,

v_StudentData.first_name || ' '

|| v_StudentData.last_name);

-- Retrieve the next row. The %FOUND condition will be checked

-- before the loop continues again.

FETCH c_HistoryStudents INTO v_StudentData;

END LOOP;

-- Free resources used by the cursor

CLOSE c_HistoryStudents;

-- Commit our work

COMMIT;

END;

请注意，FETCH语句出现了两次——一次是在循环的前面，另一次是在循环处

理的后面，这样做是为了使循环条件（c_HistoryStudents%FOUND）对每一次循

环叠代都求值以确保该循环过程不处理任何重复行。

3> 游标式FOR循环

因与迁移关系不大，此处略。

4、PL/SQL中的事务处理语句：

在PL/SQL中，事务的开始位置是从前一个事务结束以后执行的第一条SQL语句，

或者在连接到该数据库以后所执行的第一条SQL语句。事务的结束是使用COMMIT

或ROLLBACK语句标识的。

1> COMMIT的语法是：

COMMIT [work];

可选的关键字work用来提高可读性。

2> ROLLBACK的语法是：

ROLLBACK [work];

可选的关键字work用来提高可读性。

3> ROLLBACK语句会撤消整个事务，如果使用SAVEPOINT命令，那么只有部分的事

务需要被撤消，其语法是：

SAVEPOINT name;

这里name是保存点的名字。

4> 示例：

v_NumIterations NUMBER;

BEGIN

-- Loop from 1 to 500, inserting these values into temp_table.

-- Commit every 50 rows.

FOR v_LoopCounter IN 1..500 LOOP

INSERT INTO temp_table (num_col) VALUES (v_LoopCounter);

v_NumIterations := v_NumIterations + 1;

IF v_NumIterations = 50 THEN

COMMIT;

v_NumIterations := 0;

END IF;

END LOOP;

END;

5、使用标准内置错误消息发送函数：

与T-SQL中RAISEERROR对应，PL/SQL中有这样一个内置函数

函数说明：

RAISE_APPLICATION_ERROR (error_number, error_message, [keep_errors]) ;

这里，error_number是从-20,000到-20,999之间的参数；error_message是与此

错误相关的正文，error_message必须不多于512个字节；而keep_errors是一个

可选的布尔值参数，其为TRUE则新的错误将被添加到已经引发的错误列表中（如

果有的话），其为FALSE（这是缺省的设置）则新的错误将替换错误的当前列表。

例：

RAISE_APPLICATION_ERROR(-20000, ‘Can’t find any record.’) ;

<3>、T-SQL与PL/SQL常用函数比较(以下的exp为expression的缩写)

T-SQL

PL/SQL

字符类函数

Ascii(char_exp)

Ascii(str_exp)

Char(int_exp)

Chr(int_exp)

Datalength(char_exp)

Length(str_exp)

Substring(exp, start, length)

Substr(exp, start, length)

Upper(char_exp)

Upper(str_exp)

Lower(char_exp)

Lower(str_exp)

Stuff(char_exp1,start,length,

Char_exp2)

Translate(str_exp,from_str,to_str)

Ltrim(char_exp)

Ltrim(str_exp1 [,str_exp2])

Rtrim(char_exp)

Rtrim(str_exp1 [,str_exp2])

日期类函数

Getdate()

Sysdate

数学类函数

Abs(numeric_exp)

Abs(number_exp)

Ceiling(numeric_exp)

Ceil(number_exp)

Exp(float_exp)

Exp(number_exp)

Floor(numeric_exp)

Floor(number_exp)

Power(numeric_exp,int_exp)

Power(number_exp1,number_exp2)

Round(numeric_exp,int_exp)

Round(number_exp1 [,number_exp2])

Sign(int_exp)

Sign(number_exp)

Sqrt(float_exp)

Sqrt(number_exp)

转换函数

Convert(datatype[(length)],exp,format)

To_char(datatype,str_format)

Convert(datatype[(length)],exp,format)s

To_date(str_exp,date_format)

Convert(datatype[(length)],exp,format)

To_number(str_exp,num_format)

其它函数

AVG([ALL | DISTINCT] col)

COUNT({[ALL | DISTINCT] col] | *})

COUNT({[ALL | DISTINCT] col} | *))

MAX([ALL | DISTINCT] col)

MIN([ALL | DISTINCT] col)

SUM([ALL | DISTINCT] col)

STDEV(col)

STDDEV(col)

VAR(col)

VARIANCE(col)

ISNULL(check_exp, replace_value)

NVL(check_exp, replace_value)

CASE

DECCODE

<4>MSSQL与ORACLE比较注意几个语法转换

(1)ISNULL与 NVL

在MSSQL中为了替换空值常用ISNULL函数，如ISNULL(@dno,”00”)表示当变量@dno的值为空时，则用”00”替换其值;在ORACLE 中，同样的功能用NVL实现，如上述例可以用NVL(dno,”00”)来替换。

(2)CASE 与 DECCODE

CASE在MSSQL中可以用以对某个值进行多个判断分支进行处理，简化了代码如下：

Update Student set Class = (Case inyear when “1993” then “8” when “1994” then “7” when “1995 then “6” else “0”)

相同的功能在ORACLE中可以用DECCODE来实现,如上例在ORACLE应作如下处理:

Update Student set class=deccode (inyeare,’1993’,’8’,’1994’,’7’,’1995’,’6’,’0 ‘) ;

(3)日期运算

在MSSQL中对于日期的处理不能数值型有很大区，其主要实现的函数有DATEADD、DATEDIFF；而在ORACLE中把日期当作数值来处理，其主要处理函数有ADD_MONTH、MONTH_BETWEEN、D1(+-*/)D2等,下面列出其替换方法

DATEADD(YEAR,1,pubdate)

ADD_MONTHS(D1,12)

DATEADD(MONTH,3,pubdate)

ADD_MONTHS(D1,3)

DATEADD(DAY,13,pubdate)

D1 + 13

DATEADD(DAY,-3,pubdate)

D1 – 3

DATEADD(HOUR,6,pubdate)

D1 + 6/24

DATEADD(MINUTES,24,pubdate)

D1 + 24/1440

DATEDIFF(minute, D1, D2)

(D2-D1)*1440

DATEDIFF(hour, D1, D2)

(D2-D1)*24

DATEDIFF(month, D1, D2)

MONTHS_BETWEEN(D1,D2)

DATENAME(month, getdate())

TO_CHAR(sysdate,’MONTH’)

DATENAME(year, getdate())

TO_CHAR(sysdate,’YEAR’)

DATENAME(day,getdate())

TO_CHAR(sysdate,’DAY’)

DATEDART(month,getdate())

TO_CHAR(sysdate,’MM’)

DATEDART(year,getdate())

TO_CHAR(sysdate,’YYYY’)

DATEDART(day,getdate())

TO_CHAR(sysdate,’DD’)

GETDATE（）

sysdate

<4>、从T-SQL向PL/SQL迁移方案

通过上述讨论，在从T-SQL向PL/SQL迁移时，在常用语法与函数方面要逐一细致比较

后再行调整，特别要注意常用函数怎么进行替换和主体控制结构怎么进行调整。

(1)将所有的GETDATE全部转换成为SYSDATE；

(2）将所有的selct @var = column from table where condition 改成

select column into var from table where condition;

将所有的selct @var1 = @var2 改成

var1 :=var2;

(3)将所有的convert全部转换成为 to_char 或 trunc

例一：

declare rq1 datetime,rq2 datetime

…

select je from sr where rq > = convert (char(10),rq1,111)

and rq < convert(char(10),rq2,111)

应改成:

date rq1;

date rq2;

select sr into je where rq > = trunc(rq1) and rq < trunc(rq2);

例二：

declare rq1 datetime,rq2 datetime

…

select je from sr where convert(char(10),rq,111) > = convert (char(10),rq1,111)

and rq < convert(char(10),rq2,111)

应改成:

date rq1;

date rq2;

select sr into je where trunc(rq)> = trunc(rq1) and trunc(rq) < trunc(rq2);

或：

date rq1;

date rq2;

select sr into je where to_char(rq,’yyyy/mm/dd’)> =to_char(rq1,’yyyy/mm/dd’) and to_char(rq,’yyyy/mm/dd’) < to_char(rq2,’yyyy/mm/dd’);

(3)PL/SQL不支付时间字段与规则字符串的直接比例

如在T-SQL中的

select @je = sr where rq > ‘2001.01.01’是可以的。

而在PL/SQL中

select sr into je where rq > ‘2001.01.01’;是行不通的，如果要实现比例，则应改成;

select sr into je where rq > to_date(‘2001.01.01’,’yyyy.mm.dd’);或

select sr into je where to_char(rq,’yyyy.mm.dd’) > ‘2001.01.01’;

(4)将T-SQL中的DATEDIFF全部改成TRUNC(D1-D2),MONTHS_BETWEEN

如select @ts = datediff(day,date1,date2),在PL/SQL中应改为：

ts = trunc(date2 – date1);

如select @ys = datediff(month,date1,date2),在PL/SQL中应改为：

ts = months_between(date1 – date2);

(5)DateAdd全部改为D+n 或Add_Months

如select date2 = dateadd(day,date1,3),在PL/SQL中应改为：

date2 :=date1 + 3;

如select date2 = dateadd(month,date1,6),在PL/SQL中应改为：

date2 :=add_months(date1 ,6);

(6)

<八> 临时表问题

ORALCE8i以上的版本才支持临时表，其创建语法为:

CREATE GLOBAL TEMPORARY TABLE table_name

(clomn1 type,column2 type);而对于ORALCE8i以下的版本不支持，综合考虑，在从SQL SERVER向ORACLE迁移时，对于临时表，采用以下方案：

1、将T-SQL语句中的临时表在后台实际化——即将之创建为正式表，在其本身的列中增加一列作为序号用于标识不同的操作。

2、在将临时表实际化时对所有这样的表都要加“tmp_”前缀。

<九> ORACLE特别处

<1>、dual的使用

在ORACLE中可以用这样的语法从后台服务器提取时间值：

select sysdate into :varible from dual ;

<2>、ORACLE不能在存储过程中建数据表

<十>连接远程的数据库（包括不同服务器）

数据库链接（Database Link）与分布式数据库功能紧密相连。数据库链接允许用户处理远程数据库而不用知道数据是在什么地方。当建立了一个数据库链接之后，提供对远程数据的登录信息。每当使用数据库链接时，在分布式网络上初始化一个对话（Session），以解决对远程数据库对象的引用。

(1) 先创建一个远程数据库的别名，用ORACLE8 NET EASY CONFIG，也在在文件TNSNAMES.ORA中加入以下格式内容:

别名.WORLD =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = NMP)(SERVER = 远程数据服务器名称)(PIPE = ORAPIPE))

(CONNECT_DATA = (SID = 远程数据库名称)))

(2) 创建一数据库链接

语法：

CREATE OR REPLACE [public] DATABASE LINK connect_name

CONNECT TO username IDENTIFIED BY password

USING ‘connect_string’;

创建数据库链接时具体使用的语法取决于下面两个条件：

n 数据库链接的“公共”或“私有”状态；

n 使用缺省还是显式方式登录远程数据库。

如果建立的数据库链接是公共所用，则在创建时应指明关键字 PUBLIC；无此参数系统默认为私有;

可以为数据库链接指定用户和密码，如下：

create or replace database link db_link_1

connect to ‘scott’ identified by ‘tiger’

using ‘hq’

则在当前数据库创建了一个指向远程数据库”hq”的链接，连接时所用的用户名和密码为“tiger”;

如果不指定用户和密码，则在应用数据库链接，系统会使用当时的用户进行尝试连接，创建过程如下：

create or replace database link db_link_1

using ‘hq’

（3）远程数据库对象的引用

创建数据库链接以后，在当前数据库就可以访问远程数据库中具有访问权限的对象，引用的方法为将数据库链接名称添加到任何可以访问远程帐号的表或视图上。当将数据库链接名称加添加到表或视图名称上时，必须在数据库链接名称之前使用一个“@”，如：

SELECT * FROM worker@remote_connect;

(3) 使用同义词对远程对象进行访问

对于已创建了数据库链接的远程数据中对象，可以在本地数据库创建其同义词，达到在访问在逻辑上相当天本地数据库对象，使语法简洁，如下：

CREATE SYNONYM worker_syn

FOR worker@remote_connect;

创建后，对于远程数据库remote_connect的worker表的访问就可以变成如下：

select * from worker_syn;

posted on 2005-09-08 09:22 ξσ Dicky σξ 阅读(1331) 评论(0) 编辑收藏举报

刷新页面返回顶部

ξσ Dicky's Blog σξ

导航

公告