CREATE TABLE T2 ( a INTEGER, b CHAR(10));CREATE PROCEDURE addtuple1(i IN NUMBER) ASBEGIN INSERT INTO T2 VALUES(i, 'xxx');END addtuple1; 执行: BEGIN addtuple1(99); END;
INSERT INTO T2(a,b) select a,b from t;
T2表中的a、b字段不在t表中呢?只有T2表中有这两个字段,该怎么实现呢?谢谢!!!
CREATE PROCEDURE insertvalue AS BEGIN INSERT INTO tables values(); INSERT INTO tables values(); INSERT INTO tables values(); INSERT INTO tables values(); INSERT INTO tables values(); END; 多条,也可以用个循环loop
还有个问题向大家请教,在执行下面的存储过程时报错“创建的过程带有编译错误。” create or replace procedure pNormalParam(f1 number, f2 varchar2, v_Result out number) as begin insert into t1 values(f1,f2); commit; v_Result : = 0; return; exception when others then begin rollback; v_Result : = -1; return; end; end pNormalParam; /请帮忙看看哪有问题?谢谢!!!
v_Result := 0; 不是 v_Result : = 0;
create or replace procedure pNormalParam(f1 number, f2 varchar2, v_Result out number) as begin insert into t1 values(f1,f2); commit; v_Result := 0; return; exception when others then begin rollback; v_Result := -1; return; end; end pNormalParam; / :=不要分开写。 其他都市正确的。
如果只是插入n条同样的记录的话,可以用 insert into t1 select '1','2' from dual connect by rownum<=n
CREATE OR REPLACE procedure wip_inv_cost(periods in varchar2) as begin insert into wipcost(BUS_CODE,PERIOD,MOP_ORDER,MOP_OPR_CODE,MOP_ITM_CODE,MOP_MOR_NO,MOP_LOTNUM,MOP_CURRENTQTY,MONEY,PERMONEY,ALLMONEY,MAN,PERMAN,ALLMAN,MADE,PERMADE,ALLMADE,OPSMONEY,PEROPSMONEY,ALLOPSMONEY,STATUS) select bus_code,periods,99 as mop_order,'完工待入庫'as mop_opr_code,mop_itm_code,mop_mor_no,a.mop_lotnum,mop_currentqty,0,allmoney,allmoney,0,ALLMAN,ALLMAN,0,ALLMADE,ALLMADE,0,ALLOPSMONEY,ALLOPSMONEY,3 from (select * From wipcost a where period=periods and not exists(select 1 from wipcost where period=periods and mop_lotnum=a.mop_lotnum and mop_order>a.mop_order)) a, (select mop_lotnum,max(mop_order) as maxorder from tblwipmfgorderop group by mop_lotnum) b where a.MOP_LOTNUM=b.mop_lotnum and a.mop_order=b.maxorder; commit; end;這個是我寫的存儲過程,其中bus_code,periods 是把參數插入表中,99是把常量插入裱中,還有把其他表的字段插入表中.
只是説没有必要写return v_Result : = -1; 也要改吧
set serveroutput ondeclare v_result number; begin pNormalParam(1,'a', v_Result); if v_result=0 then dbms_output.put_line('插入成功'); else dbms_output.put_line('插入失败'); end if; end; /
执行:
BEGIN addtuple1(99); END;
INSERT INTO tables values();
INSERT INTO tables values();
INSERT INTO tables values();
INSERT INTO tables values(); END; 多条,也可以用个循环loop
create or replace procedure pNormalParam(f1 number,
f2 varchar2, v_Result out number)
as
begin
insert into t1 values(f1,f2);
commit;
v_Result : = 0;
return;
exception
when others then
begin
rollback;
v_Result : = -1;
return;
end;
end pNormalParam;
/请帮忙看看哪有问题?谢谢!!!
不是 v_Result : = 0;
f2 varchar2, v_Result out number)
as
begin
insert into t1 values(f1,f2);
commit;
v_Result := 0;
return;
exception
when others then
begin
rollback;
v_Result := -1;
return;
end;
end pNormalParam;
/
:=不要分开写。
其他都市正确的。
insert into t1 select '1','2' from dual connect by rownum<=n
CREATE OR REPLACE procedure wip_inv_cost(periods in varchar2)
as
begin
insert into wipcost(BUS_CODE,PERIOD,MOP_ORDER,MOP_OPR_CODE,MOP_ITM_CODE,MOP_MOR_NO,MOP_LOTNUM,MOP_CURRENTQTY,MONEY,PERMONEY,ALLMONEY,MAN,PERMAN,ALLMAN,MADE,PERMADE,ALLMADE,OPSMONEY,PEROPSMONEY,ALLOPSMONEY,STATUS)
select bus_code,periods,99 as mop_order,'完工待入庫'as mop_opr_code,mop_itm_code,mop_mor_no,a.mop_lotnum,mop_currentqty,0,allmoney,allmoney,0,ALLMAN,ALLMAN,0,ALLMADE,ALLMADE,0,ALLOPSMONEY,ALLOPSMONEY,3 from
(select * From wipcost a where period=periods and
not exists(select 1 from wipcost where period=periods and mop_lotnum=a.mop_lotnum and mop_order>a.mop_order)) a,
(select mop_lotnum,max(mop_order) as maxorder from tblwipmfgorderop group by mop_lotnum) b
where a.MOP_LOTNUM=b.mop_lotnum and a.mop_order=b.maxorder;
commit;
end;這個是我寫的存儲過程,其中bus_code,periods 是把參數插入表中,99是把常量插入裱中,還有把其他表的字段插入表中.
v_Result : = -1; 也要改吧
v_result number;
begin
pNormalParam(1,'a', v_Result);
if v_result=0 then
dbms_output.put_line('插入成功');
else
dbms_output.put_line('插入失败');
end if;
end;
/