单独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;
解决方案 »
- alert.log日志问题 菜鸟求教
- 安装plsqlDeveloper 问题
- 怎样把表名作为参数传入存储过程中?
- ORA-01704: string literal too long
- oracle已经删除怎么windows 服务中还有oracle的服务
- 我在数据库中删除1000万条记录后,但是数据库怎么没有减少?如何减少数据库??
- 导入.dmp的时候,提示IMP-00010: 不是有效的导出文件,标题检验失败(oracle两个安装版本不同)
- 安装问题2
- NVL问题
- oracle DBConsoleorcl oracle 11g发生服务特定错误: 2
- 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,表示有数据插入