疯了，oracle中怎么创建存储过程呀？？？？？？？？？？
oracle中怎么创建存储过程
[解决办法]
我的异常网推荐解决方案：oracle存储过程,http://www.myexception.cn/oracle-develop/177537.html
[解决办法]
--研究明白了这个基本上创建一个存储过程是不成问题了
create or replace package pkg
is
type cur is ref cursor;
procedure bb( a in int,b out varchar2,Rst out cur);
end pkg;
/
create or replace package body pkg
is
procedure bb( a in int,b out varchar2,Rst out cur)
is
begin
b:=a;
open Rst for select * from table1;
end;
end pkg;
[解决办法]
楼主啊，遇到问题自己多琢磨琢磨～
[解决办法]
很简单的，下面有个新增商品的小例子，不过在这之前你得自己建表哦
CREATE OR REPLACE Package body Product
as
/* 新增商品关系类型信息 */
--PRODUCT
PROCEDURE ProductRelationTypeInsert(
v_PKID IN OUTNUMBER ,
v_FULLNAMEVARCHAR2,
v_RELATIONANAMEVARCHAR2,
v_RELATIONBNAMEVARCHAR2,
v_DESCRIPTIONVARCHAR2,
v_PARENTIDNUMBER,
v_VALIDCHAR,
v_LASTMODIFIERNUMBER,
v_LASTMODIFYTIMECHAR,
v_IMPORTTIMECHAR,
v_EXPORTTIMECHAR
)
IS
BEGIN
INSERT INTO TPRODUCTRELATIONTYPE (PKID,FULLNAME,RELATIONANAME,RELATIONBNAME,DESCRIPTION,PARENTID,VALID,LASTMODIFIER,LASTMODIFYTIME,IMPORTTIME,EXPORTTIME)
VALUES (
TPRODUCTRELATIONTYPE_ID.NEXTVAL,
v_FULLNAME,
v_RELATIONANAME,
v_RELATIONBNAME,
v_DESCRIPTION,
v_PARENTID,
v_VALID,
v_LASTMODIFIER ,
TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS '),
v_IMPORTTIME,
v_EXPORTTIME)
RETURNING PKID INTO v_PKID;
END ProductRelationTypeInsert;
[解决办法]
---------------------例子来了-----------------------------------
create or replace procedure SP_GA_RESULT_SETTING
(
idataid in varchar2,
icoCodeList in varchar2,
iruleCodeList in varchar2,
excepIdS in varchar2,
beginDate in varchar2,
endDate in VARCHAR2
) is
vResSettleSqlStr varchar2(4000);
replaceExcepIds varchar2(4000);
BEGIN
replaceExcepIds := replace(excepIdS,',',''',''');
vResSettleSqlStr := 'UPDATE GA_RESULT_LIST LIST
SET LIST.STATUS = 5, LIST.MODIFY_TIME = SYSDATE, LIST.RESULT_ID = ' || idataid || '
WHERE LIST.UUID IN
(SELECT A.UUID
FROM GA_RESULT_LIST A, GL_VOU_HEAD B
WHERE A.VOU_NO = B.VOU_NO
AND A.VOU_CO_CODE = B.CO_CODE
AND A.VOU_FISCAL = B.FISCAL
AND A.VOU_ACCOUNT_ID = B.ACCOUNT_ID
AND B.VOU_DATE >= TO_DATE(''' || beginDate || ''',''YYYY-MM-DD'')
AND B.VOU_DATE <= TO_DATE(''' || endDate || ''',''YYYY-MM-DD'')';
if icoCodeList <> '*' then
vResSettleSqlStr := vResSettleSqlStr || ' AND B.CO_CODE IN (''' || icoCodeList || ''')';
end if;
if iruleCodeList <> '*' then
vResSettleSqlStr := vResSettleSqlStr || ' AND A.RULE_CODE IN (''' || iruleCodeList || ''')';
end if;
if excepIdS IS NOT NULL then
vResSettleSqlStr := vResSettleSqlStr || ' AND A.RULE_CODE NOT IN(''' || replaceExcepIds || ''')';
end if;
vResSettleSqlStr := vResSettleSqlStr || ' AND (SELECT COUNT(*) FROM GA_RESULT_TEMP K
WHERE A.VOU_NO = K.VOU_NO
AND A.VOU_CO_CODE = K.VOU_CO_CODE
AND A.VOU_FISCAL = K.VOU_FISCAL
AND A.VOU_ACCOUNT_ID = K.VOU_ACCOUNT_ID
AND A.RULE_CODE = K.RULE_CODE
AND A.VOU_SEQ = K.VOU_SEQ
AND K.RESULT_ID = '|| idataid ||') = 0)';
execute immediate vResSettleSqlStr;
commit;
end SP_GA_RESULT_SETTING;
