求助：从sybase的存储过程转向oracle的存储过程的入门级问题

galebird

¶
                           移植方案

一.后台存储过程利用conv72从SYBASE转换到ORACLE。

USAGE: conv72 [-P -F -M] <input_file_name>
       -P指存储过程，-F指函数
例如：conv72 -P filename.sql
转换后,产生文件名字是filename.sql.sql



二.转换后的调整。


（一）后台存储过程的调整

1、NULL
ORACLE对NULL 的条件判断只能用： 变量 IS NULL 和变量 IS NOT NULL；
对NULL 的附值只能用：变量 := NULL；
因此需要对所有存储过程中有关 NULL 的操作做上述相应调整。

2、StoO_error:=0;
所有存储过程中，凡是对数据库做有效操作之前，包括UPDATE，DELETE，INSERT以及
执行子存储过程或函数，都有必要将ORACLE转化过程中自动加上的变量StoO_error清
零，即在操作之前加上 StoO_error:=0; 。

3、WHEN NO_DATA_FOUND THEN        StoO_error  :=0;
所有存储过程中，对数据库做有效操作之后，通常会用IF StoO_error != 0 THEN来
检验操作成功与否。对于空操作，即WHERE 子句条件不满足时，ORACLE会产生例外，
而SYBASE 不会出错。因此为了一致，在 EXCEPTION 中 应加入
WHEN NO_DATA_FOUND THEN        StoO_error  :=0;

4、示例：
                StoO_error  :=0;/*操作之前加上 StoO_error:=0*/

                BEGIN
                DELETE  BOAD
                WHERE         (EXCH_ID = i_exch_id) AND (SWT_ID = i_swt_id)
                AND (FRAME_NBR = i_frame_nbr) AND (SHELF_NBR = i_shelf_nbr)
                AND (BOAD_NBR = i_boad_nbr);
                StoO_rowcnt := SQL%ROWCOUNT;
                EXCEPTION
                        WHEN NO_DATA_FOUND THEN       
                                StoO_error  :=0;
                        WHEN OTHERS THEN
                                StoO_error  := SQLCODE;
                END;

                IF StoO_error != 0 THEN
                BEGIN
                        ROLLBACK TO SAVEPOINT aa;
                        i_status :=  -1 ;
                        RETURN /*  */;
                END;
                END IF;
5、事务设计
ORACLE的存储过程中，COMMIT 语句将会把此前未提交的所有事务都提交，且对于
已提交的事务，无论在后台存储过程中，还是在前台 PB 的脚本中，都无法回滚。
这与SYBASE 不同，因此事务应该结合后台存储过程与前台 PB 脚本设计。

6、事务的调整
在ORACLE中，没有SYBASE中事务嵌套数的概念，在后台的存储过程中，遇到
COMMIT时，将把整个事务（包括前台和后台）全部提交。而在SYBASE中，只
有当事物数为最低级时（设计都在前台）才能提交。

由于所有事务都在前台提交，
因此把后台存储过程中所有的COMMIT全部注释掉。
       


7、时间参数调整
       
如果该存储过程的输入参数为DATE型时，在POWERBUILDER中创建存储过程
时，将引发错误。

为了解决此问题，

在后台，将这种存储过程的DATE型输入参数
全部改为VARCHAR2型。
在存储过程中，将VARCHAR2转化为DATE，为了前后台一致，
采用统一的转化格式。

i_start_DATE:=TO_DATE(i_start_string,'YYYY/MM/DD HH24:MI:SS');
i_end_date  :=TO_DATE(i_end_string,  'YYYY/MM/DD HH24:MI:SS');

在前台POWERBUILDER中，将DATE或者DATETIME型变量转化为STRING型，
也采用上述统一格式，再作为存储过程的参数。

v_STRING=STRING(v_DATE,    'YYYY/MM/DD HH:MM:SS')
v_STRING=STRING(v_DATETIME,'YYYY/MM/DD HH:MM:SS')


（二）用于数据窗口的后台存储过程的修改

1、通过特定表返回时，WHERE子句的处理


        TABLE:                TEST

        COLUMN:                NAME        VARCHAR2(20);
                        AGE        NUMBER;
                        BIRTH        DATE;
                        REMARKS        VARCHAR2(30);

        最后的返回语句如下，WHERE子句的处理中，对字符型和日期型进行转义处理

        PBDBMS.PUT_LINE('SELECT NAME,AGE,BIRTH,REMARKS ')
        PBDBMS.PUT_LINE(' FROM TEST ');
        str_where:=' WHERE NAME='|| ''''||VAR_NAME||''''
                  ||'AND AGE ='  || TO_CHAR(VAR_AGE)
                  ||'AND BIRTH=' || ''''||TO_CHAR(VAR_BIRTH)||'''';
        PBDBMS.PUT_LINE(str_where);

        注意：
             如果NUMBER型变量是该存储过程的输入参数，则要对他进行
             单独处理
             IF VAR_AGE IS NULL THEN
                CONV:=' AND AGE IS NULL ';
             ELSE
                CONV:=' AND AGE = ' || TO_CHAR(VAR_AGE);
             END IF;               


2、最后数据的返回通过表DUAL进行

    有两种方式：
     （1）、用PBDBMS.PUT_LINE。无法处理返回值为空值的情况
            使用方法见底下示例：
            注意：对不同变量类型进行了不同处理。
                               
     （2）、用PBDBMS.PUT
            该过程已经过修改，可对各种变量类型进行处理，不在需要在程序中对
            不同类型进行不同处理，同时也可适应NULL值的情况。
            使用方法见底下示例：
      注意：直接使用该参数，多个变量返回时注意中间加PUT(',');

      另外：PBDBMS包中行最大长度为255，而PUT函数不换行，因此估计
            长度快到255时，使用PUT_LINE(',')来实现换行目的。
                       
        PROCEDURE AAA
        (I_SO_NBR VARCHAR2)
        IS
        I_NUM INTEGER;
        i_date date;
        I_NAME VARCHAR2(50);
        I_COV VARCHAR2(100);
        I_COV2 VARCHAR2(100);
        BEGIN
                /* 第一种方式*/
                I_COV:= ' SELECT  '||''''||I_NAME||''''||','||TO_CHAR(I_NUM)||','||''''||TO_CHAR(I_DATE)||'''';
                I_COV2:=' FROM DUAL';
       
                PBDBMS.PUT_LINE(i_cov);
                PBDBMS.PUT_LINE(I_cov2);

                /*第二种方式*/
                PBDBMS.PUT_LINE('SELECT ');
                PBDBMS.PUT(I_NAME);
                PBDBMS.PUT(',');/* 如长度将超过255，此处用PUT_LINE('，')进行换行*/

                PBDBMS.PUT(I_NUM);PBDBMS.PUT(',');
                PBDBMS.PUT(I_DATE);
                PBDBMS.PUT_LINE(' FROM DUAL');

        END;



（三）前台脚本中嵌入式SQL语句的调整

1、ORACLE 存储过程前台脚本调用方式：
DECLARE sosp_cust_i PROCEDURE FOR sosp_cust_i  
   (:name,:reg_nbr,:li_cust_cat_id,osition,   
    null,arent_id)  ;
execute sosp_cust_i;


SYBASE 存储过程前台脚本调用方式：
DECLARE sosp_cust_i PROCEDURE FOR sosp_cust_i  
   @name = :ls_name,   
   @reg_nbr = :ls_regnbr,   
   @cat_id = :ii_cust_cat,   
   @position = null,   
   @type_id = :ii_cust_type,   
   @parent_id = :ll_parent_id  ;
execute sosp_cust_i;


2、前台脚本中嵌入式的SQL语句中
(1)NULL
    ORACLE对NULL 的条件判断只能用： 字段名 IS NULL 和 字段名?IS NOT NULL；
    而不能用：字段名 = NULL 和 字段名?<> NULL?
    若有上述情况，应做相应调整。
    如：select * from so_charge where receipt_nbr = null;
    应改为：select * from so_charge where receipt_nbr is null;

(2)""->''
    ORACLE对字符串使用单引号，而SYBASE单引号、双引号均可
    因此若有如select * from so where state="A";的语句
    应改为select * from so where state='A';

(3)insert -> insert into
    SYBASE允许使用insert tablename...,而ORACLE必须使用
    insert into tablename...,若有上述情况，应做相应调整。

(4)+ --> ||
    字符串连接，SYBASE使用'+'，而ORACLE使用'||'。?

(5)getdate() --> sysdate
    取系统时间，SYBASE使用getdate()，而ORACLE使用sysdate。