分享
 
 
 

从SQL SERVER 向ORACLE 8迁移的技术实现方案

王朝oracle·作者佚名  2004-10-27
窄屏简体版  字體: |||超大  

不知道从哪里得到这个文档,有用就放上来了 -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

GO

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) /*创建自命名外键约束*/

)

GO

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

IF EXISTS (SELECT 1 FROM sysindexes

WHERE name = 'emp_pub_id_ind')

DROP INDEX employee. emp_pub_id_ind

GO

CREATE INDEX emp_pub_id_ind

ON employee(pub_id)

GO

<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

[ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n ]

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 ] ]

AS

sql_statement [...n]

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

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

CREATE PROCEDURE procedure_name

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

AS

DECLARE

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

BEGIN

/*主体SQL语句部分*/

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

END

2、示例:

IF EXISTS(SELECT 1 FROM sysobjects

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

DROP PROCEDURE titles_sum

GO

CREATE PROCEDURE titles_sum

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

AS

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

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

AS

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

BEGIN

/*主体SQL语句部分*/

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

EXCEPTION

/*异常处理部分*/

END ;

2、示例:

CREATE OR REPLACE PROCEDURE drop_class

( arg_student_id IN varchar2,

arg_class_id IN varchar2,

status OUT number )

AS

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

IS

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))

AS

BEGIN

END;

是错误的,应如下定义

CREATE OR REPLACE PROCEDURE PROC_SELE_YS

(YSDM CHAR,GZ NUMBER)

AS

BEGIN

END;

或者

CREATE OR REPLACE PROCEDURE PROC_SELE_YS

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

AS

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_nameistype 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]

AS

sql_statement [...n]

或者使用IF UPDATE子句:

CREATE TRIGGER [owner.]trigger_name

ON [owner.]table_name

FOR { INSERT, UPDATE }

[WITH ENCRYPTION]

AS

IF UPDATE (column_name)

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

sql_statement [ ...n]

2、示例:

IF EXISTS (SELECT 1 FROM sysobjects

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

DROP TRIGGER reminder

GO

CREATE TRIGGER employee_insupd

ON employee

FOR INSERT, UPDATE

AS

/* 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

GO

第一頁    上一頁    第1頁/共2頁    下一頁    最後頁
第01頁 第02頁 
 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有