根据这个存储过程开50万个户
CREATE OR REPLACE PROCEDURE P_CREATE_USER
/********************************************************************************
* 函数名称: p_Create_User
* 功能描述: 开户
* 作 者:
* 编写日期:
* 输 入:
* 输 出: 无
* 返 回 值: 存储过程执行标识
* 修改历史:
********************************************************************************/
(PI_ACC_NBR IN VARCHAR2, --用户账户
PI_QUERY_PWD IN VARCHAR2, --用户密码
PI_AREA_CODE IN VARCHAR2, --区域编码
PI_AREA_NAME IN VARCHAR2, --区域名称
PI_PRODUCT_ID IN VARCHAR2, --产品ID
PI_WIDTH_ID IN VARCHAR2, --宽带ID
PO_RETCODE OUT VARCHAR2, --返回结果
PO_MESSAGE OUT VARCHAR2 --返回消息
) IS VN_AREA_ID AREA.AREA_ID%TYPE;
VC_COUNT NUMBER(3);
VC_STRMSG VARCHAR2(200);
VC_CUST_SEQ CUST.CUST_SEQ_NBR%TYPE;
VC_SERV_SEQ SERV.SERV_SEQ_NBR%TYPE;
VC_ACCT_SEQ ACCT.ACCT_SEQ_NBR%TYPE;
VC_BUY_CLASS PRODUCTION_PRI_CLASS.VALUE_CLASS%TYPE;BEGIN
VC_COUNT := 0;
PO_RETCODE := 1; --插入区域
BEGIN
SELECT COUNT(*) INTO VC_COUNT FROM AREA WHERE AREA_CODE = PI_AREA_CODE;
EXCEPTION
WHEN OTHERS THEN
VC_COUNT := 0;
END;
IF VC_COUNT = 0 THEN
BEGIN
SELECT MAX(AREA_ID)
INTO VN_AREA_ID
FROM AREA
WHERE LENGTH(AREA_ID) = 3;
EXCEPTION
WHEN OTHERS THEN
VN_AREA_ID := 101;
END;
BEGIN
INSERT INTO AREA
(AREA_ID, NAME, STATE, AREA_CODE, RATE)
VALUES
(VN_AREA_ID + 1, PI_AREA_NAME, 'L0I', PI_AREA_CODE, 1);
EXCEPTION
WHEN OTHERS THEN
PO_MESSAGE := '插入区域错误';
RETURN;
END;
ELSE
--存在则更新区域信息
UPDATE AREA SET NAME = PI_AREA_NAME WHERE AREA_CODE = PI_AREA_CODE;
END IF; BEGIN
SELECT AREA_ID
INTO VN_AREA_ID
FROM AREA
WHERE AREA_CODE = PI_AREA_CODE;
EXCEPTION
WHEN OTHERS THEN
VC_COUNT := 1;
END; SELECT BM_OPER_CUST_ID.NEXTVAL INTO VC_CUST_SEQ FROM DUAL; SELECT BM_OPER_SERV_ID.NEXTVAL INTO VC_SERV_SEQ FROM DUAL; SELECT BM_OPER_ACCT_ID.NEXTVAL INTO VC_ACCT_SEQ FROM DUAL; --插入客户表
INSERT INTO CUST
(STATE_DATE,
CUST_CONTACT_NAME,
SOCIAL_ID_TYPE,
CUST_CONTACT_TEL,
CUST_SEQ_NBR,
NAME,
FOREIGN_CUST_ID,
CUST_ID)
VALUES
(SYSDATE, 'VNET', '11', '1234', '1', 'VNET', '', VC_CUST_SEQ); --插入用户表
SELECT COUNT(*) INTO VC_COUNT FROM SERV WHERE ACC_NBR = PI_ACC_NBR; IF VC_COUNT > 0 THEN
PO_MESSAGE := '用户已存在!';
ROLLBACK;
RETURN;
END IF;
BEGIN
SELECT MAX(BUY_CLASS) INTO VC_BUY_CLASS FROM PRODUCTION_PRI_CLASS;
EXCEPTION
WHEN OTHERS THEN
VC_BUY_CLASS := 100;
END; INSERT INTO SERV
(SERV_ID,
ACC_NBR,
STATE_DATE,
STATE,
CUST_SEQ_NBR,
ACCT_ID,
USER_TYPE,
VIP_FLAG,
BUY_CLASS,
AREA_CODE,
CARD_NO,
ACCT_SEQ_NBR,
QUERY_PWD,
CREATED_DATE,
AREA_ID,
SERV_SEQ_NBR,
USER_NAME,
WIDTH_ID,
WIDTH_FLUX,
CUST_ID)
VALUES
(VC_SERV_SEQ,
PI_ACC_NBR,
SYSDATE,
'F0A',
'1',
VC_ACCT_SEQ,
'1',
'1',
VC_BUY_CLASS,
PI_AREA_CODE,
'',
VC_ACCT_SEQ,
PI_QUERY_PWD,
SYSDATE,
VN_AREA_ID,
VC_SERV_SEQ,
'vnet',
PI_WIDTH_ID,
'3',
VC_CUST_SEQ); --插入帐户表
INSERT INTO ACCT
(STATE_DATE,
CUST_SEQ_NBR,
ACCT_ID,
FOREIGN_ACCT_ID,
ACCT_SEQ_NBR,
CREATED_DATE,
STATE,
PAYMENT_METHOD,
CUST_ID,
BALANCE)
VALUES
(SYSDATE,
'1',
VC_ACCT_SEQ,
'',
'1',
SYSDATE,
'10A',
'A01',
VC_CUST_SEQ,
'0'); --插入订购关系
SELECT COUNT(*)
INTO VC_COUNT
FROM ADD_PACK_PLAN
WHERE ADD_PACK_PLAN_ID = PI_PRODUCT_ID; IF VC_COUNT = 0 THEN
PO_MESSAGE := '产品不存在!';
ROLLBACK;
RETURN;
END IF; BEGIN
SELECT COUNT(*)
INTO VC_COUNT
FROM SERV_PRODUCTION
WHERE ACC_NBR = PI_ACC_NBR
AND ADD_PACK_PLAN_ID = PI_PRODUCT_ID
AND START_TIME <= SYSDATE
AND END_TIME >= SYSDATE;
EXCEPTION
WHEN OTHERS THEN
VC_COUNT := 0;
END; IF VC_COUNT = 0 THEN
INSERT INTO SERV_PRODUCTION
(SERV_ID,
ACC_NBR,
ADD_PACK_PLAN_ID,
START_TIME,
END_TIME,
STATE,
STATE_TIME,
START_USING_SECT,
END_USING_SECT,
CONT_STATE,
BUY_MODE,
CREATE_TIME,
FLAG)
VALUES
(VC_SERV_SEQ,
PI_ACC_NBR,
PI_PRODUCT_ID,
SYSDATE,
TO_DATE('2099-12-01 23:59:59', 'yyyy-mm-dd hh24:mi:ss'),
'NORM',
SYSDATE,
'00:00',
'23:59',
'0',
'0',
SYSDATE,
'0');
END IF;
COMMIT;
PO_RETCODE := '0';
PO_MESSAGE := '开户订购成功!';
RETURN;EXCEPTION
WHEN OTHERS THEN
PO_RETCODE := '1';
PO_MESSAGE := '数据库操作失败!';
ROLLBACK;
VC_STRMSG := VC_STRMSG || '错误号:' || SQLCODE || ' 错误信息:' ||
SQLERRM; INSERT INTO UT_ERR_LOG
(PROC, ERRMSG, ERR_DATE)
VALUES
('p_Create_User', VC_STRMSG, SYSDATE); COMMIT;
RETURN;
END;
CREATE OR REPLACE PROCEDURE P_CREATE_USER
/********************************************************************************
* 函数名称: p_Create_User
* 功能描述: 开户
* 作 者:
* 编写日期:
* 输 入:
* 输 出: 无
* 返 回 值: 存储过程执行标识
* 修改历史:
********************************************************************************/
(PI_ACC_NBR IN VARCHAR2, --用户账户
PI_QUERY_PWD IN VARCHAR2, --用户密码
PI_AREA_CODE IN VARCHAR2, --区域编码
PI_AREA_NAME IN VARCHAR2, --区域名称
PI_PRODUCT_ID IN VARCHAR2, --产品ID
PI_WIDTH_ID IN VARCHAR2, --宽带ID
PO_RETCODE OUT VARCHAR2, --返回结果
PO_MESSAGE OUT VARCHAR2 --返回消息
) IS VN_AREA_ID AREA.AREA_ID%TYPE;
VC_COUNT NUMBER(3);
VC_STRMSG VARCHAR2(200);
VC_CUST_SEQ CUST.CUST_SEQ_NBR%TYPE;
VC_SERV_SEQ SERV.SERV_SEQ_NBR%TYPE;
VC_ACCT_SEQ ACCT.ACCT_SEQ_NBR%TYPE;
VC_BUY_CLASS PRODUCTION_PRI_CLASS.VALUE_CLASS%TYPE;BEGIN
VC_COUNT := 0;
PO_RETCODE := 1; --插入区域
BEGIN
SELECT COUNT(*) INTO VC_COUNT FROM AREA WHERE AREA_CODE = PI_AREA_CODE;
EXCEPTION
WHEN OTHERS THEN
VC_COUNT := 0;
END;
IF VC_COUNT = 0 THEN
BEGIN
SELECT MAX(AREA_ID)
INTO VN_AREA_ID
FROM AREA
WHERE LENGTH(AREA_ID) = 3;
EXCEPTION
WHEN OTHERS THEN
VN_AREA_ID := 101;
END;
BEGIN
INSERT INTO AREA
(AREA_ID, NAME, STATE, AREA_CODE, RATE)
VALUES
(VN_AREA_ID + 1, PI_AREA_NAME, 'L0I', PI_AREA_CODE, 1);
EXCEPTION
WHEN OTHERS THEN
PO_MESSAGE := '插入区域错误';
RETURN;
END;
ELSE
--存在则更新区域信息
UPDATE AREA SET NAME = PI_AREA_NAME WHERE AREA_CODE = PI_AREA_CODE;
END IF; BEGIN
SELECT AREA_ID
INTO VN_AREA_ID
FROM AREA
WHERE AREA_CODE = PI_AREA_CODE;
EXCEPTION
WHEN OTHERS THEN
VC_COUNT := 1;
END; SELECT BM_OPER_CUST_ID.NEXTVAL INTO VC_CUST_SEQ FROM DUAL; SELECT BM_OPER_SERV_ID.NEXTVAL INTO VC_SERV_SEQ FROM DUAL; SELECT BM_OPER_ACCT_ID.NEXTVAL INTO VC_ACCT_SEQ FROM DUAL; --插入客户表
INSERT INTO CUST
(STATE_DATE,
CUST_CONTACT_NAME,
SOCIAL_ID_TYPE,
CUST_CONTACT_TEL,
CUST_SEQ_NBR,
NAME,
FOREIGN_CUST_ID,
CUST_ID)
VALUES
(SYSDATE, 'VNET', '11', '1234', '1', 'VNET', '', VC_CUST_SEQ); --插入用户表
SELECT COUNT(*) INTO VC_COUNT FROM SERV WHERE ACC_NBR = PI_ACC_NBR; IF VC_COUNT > 0 THEN
PO_MESSAGE := '用户已存在!';
ROLLBACK;
RETURN;
END IF;
BEGIN
SELECT MAX(BUY_CLASS) INTO VC_BUY_CLASS FROM PRODUCTION_PRI_CLASS;
EXCEPTION
WHEN OTHERS THEN
VC_BUY_CLASS := 100;
END; INSERT INTO SERV
(SERV_ID,
ACC_NBR,
STATE_DATE,
STATE,
CUST_SEQ_NBR,
ACCT_ID,
USER_TYPE,
VIP_FLAG,
BUY_CLASS,
AREA_CODE,
CARD_NO,
ACCT_SEQ_NBR,
QUERY_PWD,
CREATED_DATE,
AREA_ID,
SERV_SEQ_NBR,
USER_NAME,
WIDTH_ID,
WIDTH_FLUX,
CUST_ID)
VALUES
(VC_SERV_SEQ,
PI_ACC_NBR,
SYSDATE,
'F0A',
'1',
VC_ACCT_SEQ,
'1',
'1',
VC_BUY_CLASS,
PI_AREA_CODE,
'',
VC_ACCT_SEQ,
PI_QUERY_PWD,
SYSDATE,
VN_AREA_ID,
VC_SERV_SEQ,
'vnet',
PI_WIDTH_ID,
'3',
VC_CUST_SEQ); --插入帐户表
INSERT INTO ACCT
(STATE_DATE,
CUST_SEQ_NBR,
ACCT_ID,
FOREIGN_ACCT_ID,
ACCT_SEQ_NBR,
CREATED_DATE,
STATE,
PAYMENT_METHOD,
CUST_ID,
BALANCE)
VALUES
(SYSDATE,
'1',
VC_ACCT_SEQ,
'',
'1',
SYSDATE,
'10A',
'A01',
VC_CUST_SEQ,
'0'); --插入订购关系
SELECT COUNT(*)
INTO VC_COUNT
FROM ADD_PACK_PLAN
WHERE ADD_PACK_PLAN_ID = PI_PRODUCT_ID; IF VC_COUNT = 0 THEN
PO_MESSAGE := '产品不存在!';
ROLLBACK;
RETURN;
END IF; BEGIN
SELECT COUNT(*)
INTO VC_COUNT
FROM SERV_PRODUCTION
WHERE ACC_NBR = PI_ACC_NBR
AND ADD_PACK_PLAN_ID = PI_PRODUCT_ID
AND START_TIME <= SYSDATE
AND END_TIME >= SYSDATE;
EXCEPTION
WHEN OTHERS THEN
VC_COUNT := 0;
END; IF VC_COUNT = 0 THEN
INSERT INTO SERV_PRODUCTION
(SERV_ID,
ACC_NBR,
ADD_PACK_PLAN_ID,
START_TIME,
END_TIME,
STATE,
STATE_TIME,
START_USING_SECT,
END_USING_SECT,
CONT_STATE,
BUY_MODE,
CREATE_TIME,
FLAG)
VALUES
(VC_SERV_SEQ,
PI_ACC_NBR,
PI_PRODUCT_ID,
SYSDATE,
TO_DATE('2099-12-01 23:59:59', 'yyyy-mm-dd hh24:mi:ss'),
'NORM',
SYSDATE,
'00:00',
'23:59',
'0',
'0',
SYSDATE,
'0');
END IF;
COMMIT;
PO_RETCODE := '0';
PO_MESSAGE := '开户订购成功!';
RETURN;EXCEPTION
WHEN OTHERS THEN
PO_RETCODE := '1';
PO_MESSAGE := '数据库操作失败!';
ROLLBACK;
VC_STRMSG := VC_STRMSG || '错误号:' || SQLCODE || ' 错误信息:' ||
SQLERRM; INSERT INTO UT_ERR_LOG
(PROC, ERRMSG, ERR_DATE)
VALUES
('p_Create_User', VC_STRMSG, SYSDATE); COMMIT;
RETURN;
END;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货