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

thunder-zh · 发表于 2002-3-29 01:05

从sybase的存储过程转向oracle的存储过程有一些惯性专不过来：
1.sybase的@@rowscount系统变量反映update等语句执行时影响的数据表记录条数，oracle中对应的是什么？
2.sybase存储过程中可用select @n_ret 直接返回局部变量@n_ret的值，再oracle中如何实现相同的功能？
3.sybase存储过程中参数说明可以为 para1 char(4),在oracle中不允许，是否oracle没有办法说明类型为4位字符的参数？
初次写oracle的存储过程，总是这也不对，那也不对，头痛！
请各位大虾帮帮忙！

biti_rainy · 发表于 2002-3-29 01:55

先花一天工夫仔细看一看pl/sql程序设计然后开始做
否则问一步走一步太麻烦

thunder-zhu · 发表于 2002-3-29 18:34

看书之后，了解了PL/SQL的一些与SYBASE不同点，写出来一方面请大虾指点是否有谬误，也希望抛砖引玉，大家能进一步提出其它应关注的地方，使我等初学者少走弯路，功德无量。
1. ORACLE存储过程的参数不能限定位数，参数类型定位为
CHAR(5)是非法的，只能定义为CHAR,具体位数限定有调用时的实参决定，这一点确实与SYBASE有很大不同；
2.游标在PL/SQL中作用极大，游标的概念渗透到整个PL/SQL的核心，连INSERT,UPDATE等语句都隐含了一个隐式游标SQL,类似SYBASE的@@ROWCOUNT等系统变量，在ORACLE中定义为游标属性SQL%ROWCOUNT;
3.ORACLE中显示一个变量的语句为SELECT V_VAR FROM DUAL;
与SYBASE不同的是必须加FROM DUAL;
4.SYBASE存储过程可以通过类似SELECT * FROM T_TABLE来返回数据集，在ORACLE中似乎不能，所有不带INTO的SELECT 语句在ORACLE存储过程中是非法的。这一点变化带来最大麻烦，应为POWERBUILDER调用SYBASE存储过程很喜欢这种方式。

jlandzpa · 发表于 2002-3-29 18:44

very good!

THUNDER2 · 发表于 2002-3-30 01:35

我也加一点

1、在存储过程中声明参数不用加 DECLARE

2、在每个块中只有一个 DECLARE

3、参数名不用加 @ 后缀

4、每条语句后要加分号

5、变量附值不同：
   myvalue:='abc';
   select @myvalue='abc'

   SELECT EMPNO into :emp from EMP WHERE ...;
   SELECT @emp=EMPNO from emp WHERE...

6、在TRIGGER 中出现exception 不用加ROLLBACK命令

7、oracle 不使用 read locks ,无论是 read-consistent 还是 serializable 事务所以要注意SELECT FOR UPDATE的使用

thunder-zh · 发表于 2002-4-15 17:29

pb的数据窗口如何使用oracle的存储过程？
用惯了sybase存储过程返回数据集给pb数据窗口使用的方式，
不知道改用oracle如何实现类似的功能？

zhuzhichao · 发表于 2002-4-15 17:48

好,那我也來加.

galebird · 发表于 2002-4-16 13:03

¶
   移植方案

一.后台存储过程利用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。

devid · 发表于 2002-12-19 17:02

太精彩了!

tangkp · 发表于 2003-5-30 10:07

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

建议

pb的数据窗口如何使用oracle的存储过程？

太精彩了!

浏览过的版块