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

2/9/2008来源:Oracle教程人气:6084

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