create or replace procedure HB_test(BmStr in varchar2,
IoPoint in number,
TransType in number
) is
mycou number;
begin
declare
S_YW_JLDH varchar2(200); ---单据编号
S_MES_material_id number; ---物料id
S_Yw_KSSJ date; ---业务开始时间
S_YW_JSSJ date; ---业务结束时间
S_YW_ZBL number(18,2); ---单据总量
S_YW_BM number; ---序列编号
cursor cur_emp is select
ibt.YW_BM, ---序列编号
ibt.YW_JLDH, ---单据编号
ibt.MES_material_id, ---物料id
ibt.Yw_KSSJ, ---业务开始时间
ibt.YW_JSSJ, ---业务结束时间
ibt.YW_ZBL ---单据总量
from ptjl_wh.io_business_tb ibt where yw_bm in (BmStr) begin
open cur_emp;
loop
fetch cur_emp into S_YW_BM,S_YW_JLDH,S_MES_material_id,S_YW_KSSJ,S_YW_JSSJ,S_YW_ZBL;
exit when cur_emp%notfound;
close cur_emp;
commit; end;end HB_Insert_SP;现在Bmstr=‘3245,3246,3247’就会报数字错误
如果Bmstr=‘3245‘则执行正确
我知道是in的问题 该怎么解决
解决方案 »
- oracle11g安装问题
- 求助关于tnsnames.ora配置后无法连接的问题
- dbms_rowid.create_rowid 中的object_number 问题
- oracle如何设置用户其默认表架构?
- 关于odi在同一个库内建立的临时表重复报错问题
- 某个字段值为逗号隔开的多个字符串,现要以逗号为界分别取出这些值,用来绑定到datagrid,该如何做?
- distinct搞不懂的问题,请指教!
- 初学者简单问题-关于新建数据库
- 高手请赐教。如何才能把可变的 字符串 传到PROCEDURE 的程序里呢。
- oracel的函数问题
- 跪请:Fedora Core 7下安装Oralce 9.2服务端的详细步骤,尤其是安装前后FC7的设置方面,困饶我很长时间了啊!
- 推荐超好用实用的Oracle工具 - Oracle SQL Handler
2 id NUMBER(18,0),
3 isextract VARCHAR2(40),
4 operate DATE
5 );表已创建。已用时间: 00: 00: 00.04
scott@SZTYORA>
scott@SZTYORA> INSERT INTO yuqingdongtai(id, isextract, operate) values(1,'YES',DATE'2009-01-01');已创建 1 行。已用时间: 00: 00: 00.01
scott@SZTYORA> INSERT INTO yuqingdongtai(id, isextract, operate) values(2,'YES',DATE'2009-02-11');已创建 1 行。已用时间: 00: 00: 00.00
scott@SZTYORA> INSERT INTO yuqingdongtai(id, isextract, operate) values(3,'YES',DATE'2010-03-21');已创建 1 行。已用时间: 00: 00: 00.00
scott@SZTYORA> COMMIT;提交完成。已用时间: 00: 00: 00.00
scott@SZTYORA>
scott@SZTYORA> CREATE OR REPLACE PROCEDURE UPDATE_YUQING
2 (
3 i_username IN VARCHAR2,
4 i_operate IN DATE,
5 i_ids IN VARCHAR
6
7 )
8 IS
9 v_sql VARCHAR2(4000); -- 字符串类型,最大4000
10 BEGIN
11 v_sql:= 'update yuqingdongtai set isextract =:USERNAME, OPERATE =:operate where id in '||i_ids;
12 execute immediate v_sql using i_username, i_operate;
13 COMMIT;
14 END UPDATE_YUQING;
15 /过程已创建。已用时间: 00: 00: 00.03
scott@SZTYORA> select * from yuqingdongtai; ID ISEXTRACT OPERATE
---------- -------------------------------------------------------------------------------- -------------------
1 YES 2009-01-01 00:00:00
2 YES 2009-02-11 00:00:00
3 YES 2010-03-21 00:00:00已选择3行。已用时间: 00: 00: 00.07
scott@SZTYORA> execute UPDATE_YUQING('NO',TO_DATE('2009-02-11','YYYY-MM-DD'),'(1,2)');PL/SQL 过程已成功完成。已用时间: 00: 00: 00.01
scott@SZTYORA> select * from yuqingdongtai; ID ISEXTRACT OPERATE
---------- -------------------------------------------------------------------------------- -------------------
1 NO 2009-02-11 00:00:00
2 NO 2009-02-11 00:00:00
3 YES 2010-03-21 00:00:00已选择3行。已用时间: 00: 00: 00.00
scott@SZTYORA>
id NUMBER(18,0),
isextract VARCHAR2(40),
operate DATE
);INSERT INTO yuqingdongtai(id, isextract, operate) values(1,'YES',DATE'2009-01-01');
INSERT INTO yuqingdongtai(id, isextract, operate) values(2,'YES',DATE'2009-02-11');
INSERT INTO yuqingdongtai(id, isextract, operate) values(3,'YES',DATE'2010-03-21');
COMMIT;CREATE OR REPLACE PROCEDURE UPDATE_YUQING
(
i_username IN VARCHAR2,
i_operate IN DATE,
i_ids IN VARCHAR
)
IS
v_sql VARCHAR2(4000); -- 字符串类型,最大4000
BEGIN
v_sql:= 'update yuqingdongtai set isextract =:USERNAME, OPERATE =:operate where id in '||i_ids;
execute immediate v_sql using i_username, i_operate;
COMMIT;
END UPDATE_YUQING;
/select * from yuqingdongtai;execute UPDATE_YUQING('NO',TO_DATE('2009-02-11','YYYY-MM-DD'),'(1,2)');select * from yuqingdongtai;
order by??
我知道v_sql:= 'update yuqingdongtai set isextract =:USERNAME, OPERATE =:operate where id in '||i_ids;
execute immediate v_sql;
这么使用 但是我那有游标!
execute immediate v_sql using i_username, i_operate;
这句话中 using i_username, i_operate;是什么意思?
参考下
--处理字符类型
create or replace TYPE VARTABLETYPE is table of varchar2(128);--函数
create or replace function str2varList( p_string in varchar2 ) return VarTableType
as
v_str long default p_string || ',';
v_n number(6);
v_data VarTableType := VarTableType();
begin
loop
v_n :=instr( v_str, ',' );
exit when (nvl(v_n,0) = 0);
v_data.extend;
v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1))); v_str := substr( v_str, v_n+1 );
end loop;
return v_data;
end;10g中也可以用正则来分解,如:
SELECT REGEXP_SUBSTR('1,bcddd,a,4,5,6,7,8,9,10,11', '([^,]+)', 1, ROWNUM) str
FROM DUAL CONNECT BY ROWNUM <(LENGTH(REGEXP_REPLACE('1,bcddd,a,4,5,6,7,8,9,10,11', '[^,]', '')) + 2);