从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

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

           
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

 

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

                   
as

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

AVG ( [ ALL | DISTINCT ]  col)

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

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

MAX ( [ ALL | DISTINCT ]  col)

MAX ( [ ALL | DISTINCT ]  col)

MIN ( [ ALL | DISTINCT ]  col)

MIN ( [ ALL | DISTINCT ]  col)

SUM ( [ 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;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值