单独SQL可以插入数据
写入存贮过程,缺插入不了数据,帮忙看看什么原因?//可以插入数据
INSERT INTO xf_de
(warehouse, xf_zone, xfnnum, xflnum, xstyle)
SELECT DISTINCT warehouse,
'',
UPPER(SUBSTR(xf_cntshtno, 6, 5)),
0,
xf_style
FROM xfcnt
WHERE xfcode = 'WH1'
AND xfno = 'PC01'
AND length(xtno) = 10
AND xfno not like 'T%';
//写入存贮过程,缺插入不了数据,帮忙看看什么原因 p1 = 'WH1',p2='PC01'
CREATE OR REPLACE PROCEDURE UP_UPDATE(p1 varchar2,p2 varchar2)
AS
BEGIN
--更新表
INSERT INTO xf_de
(warehouse, xf_zone, xfnnum, xflnum, xstyle)
SELECT DISTINCT warehouse,
'',
UPPER(SUBSTR(xfno, 6, 5)),
0,
xstyle
FROM xfcnt
WHERE xfcode = p1
AND xfno = p2
AND length(xtno) = 10
AND xfno not like 'T%';
END;
写入存贮过程,缺插入不了数据,帮忙看看什么原因?//可以插入数据
INSERT INTO xf_de
(warehouse, xf_zone, xfnnum, xflnum, xstyle)
SELECT DISTINCT warehouse,
'',
UPPER(SUBSTR(xf_cntshtno, 6, 5)),
0,
xf_style
FROM xfcnt
WHERE xfcode = 'WH1'
AND xfno = 'PC01'
AND length(xtno) = 10
AND xfno not like 'T%';
//写入存贮过程,缺插入不了数据,帮忙看看什么原因 p1 = 'WH1',p2='PC01'
CREATE OR REPLACE PROCEDURE UP_UPDATE(p1 varchar2,p2 varchar2)
AS
BEGIN
--更新表
INSERT INTO xf_de
(warehouse, xf_zone, xfnnum, xflnum, xstyle)
SELECT DISTINCT warehouse,
'',
UPPER(SUBSTR(xfno, 6, 5)),
0,
xstyle
FROM xfcnt
WHERE xfcode = p1
AND xfno = p2
AND length(xtno) = 10
AND xfno not like 'T%';
END;
解决方案 »
- 我心乱了,很苦恼,很高手解答
- 关于证书错误
- sqlplus 批量到入sql 脚本,脚本文件的格式UTF-8 出现乱码,怎么解决啊
- 如何删除数据文件?急!
- 求助:三层结构中,客户端如何使用ORACLE的BFILE功能存取文件?
- 有带参数的视图吗?
- linux下oracle9204的连接问题,在linux服务端连不数据库,但是在客户端通过命令行(cmd>>sqlplus /nolog)可以连上, 问题解决马上结账,绝
- Net服务名配置时,出现TNS:listener could not find instance错误,如何解决?
- 关于数据库访问的问题(表或视图不存在)
- 请教SQL,是否可以根据某列 数据分组显示
- sql多版本问题
- 给了权限,为什么连接不成功?
在后面加上commit;
没有提交在其它会话是看不见的CREATE OR REPLACE PROCEDURE UP_UPDATE(p1 varchar2,p2 varchar2)
AS
BEGIN
--更新表
INSERT INTO xf_de
(warehouse, xf_zone, xfnnum, xflnum, xstyle)
SELECT DISTINCT warehouse,
'',
UPPER(SUBSTR(xfno, 6, 5)),
0,
xstyle
FROM xfcnt
WHERE xfcode = p1
AND xfno = p2
AND length(xtno) = 10
AND xfno not like 'T%';
commit;--提交
END;
2.确保commit
或者执行完SHOW ERROR看看,有什么提示
AS
BEGIN
--更新表
INSERT INTO xf_de
(warehouse, xf_zone, xfnnum, xflnum, xstyle)
SELECT DISTINCT warehouse,
'',
UPPER(SUBSTR(xfno, 6, 5)),
0,
xstyle
FROM xfcnt
WHERE xfcode = p1
AND xfno = p2
AND length(xtno) = 10
AND xfno not like 'T%';
commit;--提交END;
begin
UP_UPDATE(p1 varchar2,p2 varchar2);
end;
2、存储过程中不允许有select语句出现,除非是赋值
(p_result out number)
is
begin
p_Result:=0; insert into testtable(usid,usname)
select fuserid,fusername from tusers where fuserid like '1018%';
p_result:=sql%rowcount;
end ins_test;加一个 p_result:=sql%rowcount;
看看返回结果 sql%rowcount; 影响行数
如果>0,表示有数据插入