Question

急求！怎么建立oracle存储过程(实例)

create PROCEDURE st_receipt
@sheetid char(16)
AS
BEGIN
insert into receipt select * from receipt0 wheresheetid=@sheetid --将 receipt0表中数据插入 receipt
update receipt set Flag=100 wheresheetid=@sheetid --更新receipt表中Flag值

insert into ReceiptItem select * from ReceiptItem0 where sheetid=@sheetid

delete from ReceiptItem0 where sheetid=@sheetid --删除ReceiptItem0表数据
delete from receipt0 where sheetid=@sheetid --删除 receipt0表数据
end

exec st_receipt @sheetid='ys141128'

这个是sql里的一个存储过程，放到oracle里该怎么写？在线等，急求！

Answer 1

--创建存储过程  IN_SHEETID 为输入参数
CREATE OR REPLACE PROCEDURE ST_RECEIPT(IN_SHEETID VARCHAR2) IS
BEGIN
  --将 receipt0表中数据插入 receipt
  INSERT INTO RECEIPT
    SELECT * FROM RECEIPT0 WHERE SHEETID = IN_SHEETID;
  --更新receipt表中Flag值
  UPDATE RECEIPT SET FLAG = 100 WHERE SHEETID = IN_SHEETID;
  --
  INSERT INTO RECEIPTITEM
    SELECT * FROM RECEIPTITEM0 WHERE SHEETID = IN_SHEETID;
  --删除ReceiptItem0表数据
  DELETE FROM RECEIPTITEM0 WHERE SHEETID = IN_SHEETID;
  --删除 receipt0表数据
  DELETE FROM RECEIPT0 WHERE SHEETID = IN_SHEETID;
END;

--调用存储过程
BEGIN
  ST_RECEIPT('ys141128');
END;
--或者
EXEC ST_RECEIPT('ys141128');

Answer 2

create or replace PROCEDURE st_receipt(p_sheetid varchar2(16))
AS
BEGIN
insert into receipt select * from receipt0 where sheetid=p_sheetid ; --将 receipt0表中数据插入 receipt
update receipt set Flag=100 wheresheetid=p_sheetid; --更新receipt表中Flag值

insert into ReceiptItem select * from ReceiptItem0 where sheetid=p_sheetid ;

delete from ReceiptItem0 where sheetid=p_sheetid ;--删除ReceiptItem0表数据
delete from receipt0 where sheetid=p_sheetid ; --删除 receipt0表数据
end ;

exec st_receipt('ys141128')