下面是我在做项目中写的存储过程的例子,请参考!
/* SQLPLUS中调用存贮过程方法如下: */
set serveroutput on;
var C varchar2(50);
exec aheoms.GetSEQ('0551','0',:C);
print C
-------------------------------------------------------------
/*
功能 :获得采番号
参数1:条件ID,可根据实际情况传入(AREA_ID,PLAN_ID等)
参数2:采番类型:
0:获得TBL_Y_PLAN的主键PLAN_ID
1: 获得TBL_Y_PLAN_ITEM的外键PLAN_ID
2: 获得TBL_M_PLAN的主键PLAN_ID
3: 获得TBL_M_PLAN_ITEM中ITEM_ID最大号进行采番
4: 获得TBL_ONDUTY_NOTE中的采番号NOTE_ID
参数3:
*/
CREATE OR REPLACE PROCEDURE AHEOMS.GetSEQ (
CONDITION_ID IN VARCHAR2,
SEQ_TYPE IN VARCHAR2,
SEQ_ID OUT VARCHAR2
)
IS
strCondition VARCHAR2(300);
strTempID VARCHAR2(50);
AREA_ID VARCHAR2(10);
BEGIN
IF SEQ_TYPE = '0' THEN
AREA_ID := CONDITION_ID;
strCondition := CONCAT(CONCAT(AREA_ID,SUBSTR(TO_CHAR(SYSDATE,'yyyy'),3,4)),'%');
SELECT MAX(PLAN_ID) INTO strTempID FROM TBL_Y_PLAN WHERE PLAN_ID LIKE strCondition;
IF LENGTH(strTempID) = 9 THEN
SEQ_ID := CONCAT(SUBSTR(strTempID,1,6),LPAD(TO_CHAR(TO_NUMBER(SUBSTR(strTempID,7,9)) + 1),3,'0'));
ELSE
SEQ_ID := CONCAT(CONCAT(AREA_ID,SUBSTR(TO_CHAR(SYSDATE,'yyyy'),3,4)),'001');
END IF;
--dbms_output.put_line(CONCAT('NEW TBL_Y_PLAN.PLAN_ID:',SEQ_ID));
END IF;
--参数2为:1 通过传入的PLAN_ID,获得TBL_Y_PLAN_ITEM中ITEM_ID最大的号并加1
IF SEQ_TYPE = '1' THEN
SELECT MAX(ITEM_ID) INTO strTempID FROM TBL_Y_PLAN_ITEM WHERE PLAN_ID = CONDITION_ID;
IF LENGTH(strTempID) >0 THEN
SEQ_ID := LPAD(TO_CHAR(strTempID + 1),4,'0');
ELSE
SEQ_ID := '0001';
END IF;
END IF;
--参数2为:2通过传入的AREA_ID,自动计算出月计划编号,格式为05510508001
IF SEQ_TYPE = '2' THEN
AREA_ID := CONDITION_ID;
strCondition := CONCAT(CONCAT(CONDITION_ID,SUBSTR(TO_CHAR(SYSDATE,'yyyymm'),3,6)),'%');
SELECT MAX(PLAN_ID) INTO strTempID FROM TBL_M_PLAN WHERE PLAN_ID LIKE strCondition;
IF LENGTH(strTempID) = 11 THEN
SEQ_ID := CONCAT(SUBSTR(strTempID,1,8),LPAD(TO_CHAR(TO_NUMBER(SUBSTR(strTempID,9,11)) + 1),3,'0'));
ELSE
SEQ_ID :=CONCAT(CONCAT(AREA_ID,SUBSTR(TO_CHAR(SYSDATE,'yyyymm'),3,6)),'001');
END IF;
END IF;
--参数2为:3 通过传入的PLAN_ID,获得TBL_M_PLAN_ITEM中ITEM_ID最大的号并加1
IF SEQ_TYPE = '3' THEN
SELECT MAX(ITEM_ID) INTO strTempID FROM TBL_M_PLAN_ITEM WHERE PLAN_ID = CONDITION_ID;
IF LENGTH(strTempID) >0 THEN
SEQ_ID := LPAD(TO_CHAR(strTempID + 1),4,'0');
ELSE
SEQ_ID := '0001';
END IF;
END IF;
--参数2为:4 通过传入的LOG_ID,获得TBL_ONDUTY_NOTE中NOTE_ID最大号并加1
IF SEQ_TYPE = '4' THEN
SELECT MAX(NOTE_ID) INTO strTempID FROM TBL_ONDUTY_NOTE WHERE LOG_ID = CONDITION_ID;
IF LENGTH(strTempID) > 0 THEN
SEQ_ID := strTempID+1;
ELSE
SEQ_ID := 1;
END IF;
END IF;
END GetSEQ;
这样在sqlplus中就可以创建存储过程了.
其中以SEQ_TYPE为标志,进行区分不同函数功能.