代码贴下:create or replace procedure PRO_SMS_SENDINFO_lastTest(V_RESULTCODE OUT NUMBER,
V_RESULTINFO OUT VARCHAR2) IS
TYPE T_CURSOR IS REF CURSOR;
IV_NOTICE_CONTENT TI_O_SMS.NOTICE_CONTENT%TYPE; --短信内容
IV_EPARCHY_CODE TF_F_USER.EPARCHY_CODE%TYPE; --地市编码
--定制生效的套餐
IV_CURSOR T_CURSOR;
IV_SERIAL_NUMBER TF_F_USER_SP.SERIAL_NUMBER%TYPE; --手机号码
/*IV_PRODUCT_ID TF_BH_TRADE.PRODUCT_ID%TYPE; --套餐编码
IV_PRODUCT_NAME TD_B_PRODUCT.PRODUCT_NAME%TYPE; --套餐名称*/
--定制业务信息的短信提醒
IV_USER_ID TF_F_USER_SP.USER_ID%TYPE; --用户标识
IV_SP_ID TF_F_USER_SP.SP_ID%TYPE;
IV_SP_PRODUCT_ID TD_B_PARTY_PRODUCT.SP_PRODUCT_ID%TYPE;
IV_SP_PRODUCT_NAME TD_B_PARTY_PRODUCT.SP_PRODUCT_NAME%TYPE; -- SP名称
IV_USER_SP_ALL VARCHAR2(200); --用户所有的SP
IV_COUNT NUMBER(4);
IV_PRICE NUMBER(4); --价格
IV_USE_Count NUMBER(10);--记录用户个数
IV_SERIAL_NUMBER_TEMP TF_F_USER_SP.SERIAL_NUMBER%TYPE;
IV_USER_ID_TEMP TF_F_USER_SP.USER_ID%TYPE;
--IV_ATTR_CONTENT TD_B_DISCNT_ITEM.ATTR_CONTENT%TYPE;BEGIN
V_RESULTCODE := -1;
V_RESULTINFO := 0;
IV_EPARCHY_CODE := '0851';
BEGIN
--====================定制业务信息的短信提醒====================--
IV_SERIAL_NUMBER_TEMP := '-1';
IV_USER_ID_TEMP := '';
IV_USER_SP_ALL := '';
IV_COUNT := 0;
IV_PRICE := 0;
IV_USE_Count := 0;
OPEN IV_CURSOR FOR
SELECT DISTINCT A.SERIAL_NUMBER,
A.USER_ID,
(SELECT B.EPARCHY_CODE
FROM TF_BH_TRADE B
WHERE B.ACCEPT_DATE >=
TRUNC(ADD_MONTHS(SYSDATE, -1), 'MONTH')
AND B.SERIAL_NUMBER = A.SERIAL_NUMBER
AND B.USER_ID = A.USER_ID
AND B.EPARCHY_CODE IS NOT NULL
AND ROWNUM = 1) EPARCHY_CODE,
A.SP_ID,
A.SP_PRODUCT_ID
FROM TF_F_USER_SP A, TD_B_PARTY_PRICE C
WHERE A.SP_PRODUCT_ID = '3100190101'
AND ROWNUM < 5
AND A.END_DATE > SYSDATE
AND C.BILLINGMODECODE = 'OrderMonth';
LOOP
FETCH IV_CURSOR
INTO IV_SERIAL_NUMBER,
IV_USER_ID,
IV_EPARCHY_CODE,
IV_SP_ID,
IV_SP_PRODUCT_ID;
EXIT WHEN IV_CURSOR%NOTFOUND;--问题:当最后一条数据时跳出循环就无法执行下一行判断插入了
IF IV_SERIAL_NUMBER_TEMP <> IV_SERIAL_NUMBER AND
IV_SERIAL_NUMBER_TEMP <> '-1' THEN
BEGIN
-- 先发送上一个用户的短信。
IV_NOTICE_CONTENT := '温馨提示:您当月已订购以下增值产品:' || IV_USER_SP_ALL ||
'。您可拨打10010查询和退订。';
INSERT INTO TI_O_SMS
(SMS_NOTICE_ID,
EPARCHY_CODE,
IN_MODE_CODE,
SMS_CHANNEL_CODE,
RECV_OBJECT_TYPE,
RECV_OBJECT,
ID,
SMS_TYPE_CODE,
SMS_KIND_CODE,
NOTICE_CONTENT_TYPE,
NOTICE_CONTENT,
FORCE_REFER_COUNT,
SMS_PRIORITY,
REFER_TIME,
REFER_STAFF_ID,
REFER_DEPART_ID,
DEAL_TIME,
DEAL_STATE,
REMARK,
SEND_TIME_CODE,
SEND_OBJECT_CODE)
SELECT TO_NUMBER(F_SYS_GETSEQID('0851',
'seq_smssend_id')),
'0851',
'0',
'11', --短信渠道编码:客户服务
'00', --被叫对象类型:00-手机号码
IV_SERIAL_NUMBER_TEMP, --被叫对象:传手机号码
NVL(TO_NUMBER(IV_USER_ID_TEMP), 0), --被叫对象标识:传用户标识
'20', --短信类型:20-业务通知
'02', --短信种类:02-短信通知
'0', --短信内容类型:0-指定内容发送
IV_NOTICE_CONTENT, --短信内容
1, --指定发送次数
50, --短信优先级
SYSDATE, --提交时间
'',
'',
SYSDATE, --处理时间
'0', --处理状态:0-未处理
'定制业务信息的短信提醒', --备注
'2',
1
FROM DUAL;
IV_USER_SP_ALL := '';
IV_COUNT := 0;
END;
END IF;
SELECT DISTINCT A.SP_PRODUCT_NAME
INTO IV_SP_PRODUCT_NAME
FROM TD_B_PARTY_PRODUCT A
WHERE A.SP_ID = IV_SP_ID
AND A.SP_PRODUCT_ID = IV_SP_PRODUCT_ID;
/*SELECT to_number(REGEXP_REPLACE(SUBSTR(A.PARAVALUE,5,INSTR(A.PARAVALUE, ';', 3, 1) -
INSTR(A.PARAVALUE, '=', 3, 1) - 1),'¥|M',''))/100 price
INTO IV_PRICE
FROM TD_B_PARTY_PRICE A
WHERE A.PRODUCTCODE = IV_SP_PRODUCT_ID
AND A.BILLINGMODECODE = 'OrderMonth';*/
IF IV_COUNT = 0 THEN
IV_USER_SP_ALL := IV_SP_PRODUCT_NAME || '业务、资费' || IV_PRICE ||
'元/月;';
ELSE
IV_USER_SP_ALL := IV_USER_SP_ALL || '、' || IV_SP_PRODUCT_NAME ||
'业务、资费' || IV_PRICE || '元/月;';
END IF;
IV_COUNT := IV_COUNT + 1;
IV_USE_Count := IV_USE_Count + 1;
IF IV_USE_Count = IV_CURSOR%ROWCOUNT-1 THEN
IV_SERIAL_NUMBER_TEMP := '0';
ELSE
IV_SERIAL_NUMBER_TEMP := IV_SERIAL_NUMBER;
END IF;
--IV_SERIAL_NUMBER_TEMP := IV_SERIAL_NUMBER;
IV_USER_ID_TEMP := IV_USER_ID;
END LOOP;
CLOSE IV_CURSOR;
--====================定制业务信息的短信提醒====================-
V_RESULTINFO := '成功插入短信接口表';
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
V_RESULTINFO := '定制生效的套餐发短信提醒报错:' || SQLERRM;
V_RESULTCODE := -1;
END;
V_RESULTCODE := 0;
V_RESULTINFO := 'trade OK!';
end PRO_SMS_SENDINFO_lastTest;如题 如对提问不清楚 请讲 多谢了 在线等
V_RESULTINFO OUT VARCHAR2) IS
TYPE T_CURSOR IS REF CURSOR;
IV_NOTICE_CONTENT TI_O_SMS.NOTICE_CONTENT%TYPE; --短信内容
IV_EPARCHY_CODE TF_F_USER.EPARCHY_CODE%TYPE; --地市编码
--定制生效的套餐
IV_CURSOR T_CURSOR;
IV_SERIAL_NUMBER TF_F_USER_SP.SERIAL_NUMBER%TYPE; --手机号码
/*IV_PRODUCT_ID TF_BH_TRADE.PRODUCT_ID%TYPE; --套餐编码
IV_PRODUCT_NAME TD_B_PRODUCT.PRODUCT_NAME%TYPE; --套餐名称*/
--定制业务信息的短信提醒
IV_USER_ID TF_F_USER_SP.USER_ID%TYPE; --用户标识
IV_SP_ID TF_F_USER_SP.SP_ID%TYPE;
IV_SP_PRODUCT_ID TD_B_PARTY_PRODUCT.SP_PRODUCT_ID%TYPE;
IV_SP_PRODUCT_NAME TD_B_PARTY_PRODUCT.SP_PRODUCT_NAME%TYPE; -- SP名称
IV_USER_SP_ALL VARCHAR2(200); --用户所有的SP
IV_COUNT NUMBER(4);
IV_PRICE NUMBER(4); --价格
IV_USE_Count NUMBER(10);--记录用户个数
IV_SERIAL_NUMBER_TEMP TF_F_USER_SP.SERIAL_NUMBER%TYPE;
IV_USER_ID_TEMP TF_F_USER_SP.USER_ID%TYPE;
--IV_ATTR_CONTENT TD_B_DISCNT_ITEM.ATTR_CONTENT%TYPE;BEGIN
V_RESULTCODE := -1;
V_RESULTINFO := 0;
IV_EPARCHY_CODE := '0851';
BEGIN
--====================定制业务信息的短信提醒====================--
IV_SERIAL_NUMBER_TEMP := '-1';
IV_USER_ID_TEMP := '';
IV_USER_SP_ALL := '';
IV_COUNT := 0;
IV_PRICE := 0;
IV_USE_Count := 0;
OPEN IV_CURSOR FOR
SELECT DISTINCT A.SERIAL_NUMBER,
A.USER_ID,
(SELECT B.EPARCHY_CODE
FROM TF_BH_TRADE B
WHERE B.ACCEPT_DATE >=
TRUNC(ADD_MONTHS(SYSDATE, -1), 'MONTH')
AND B.SERIAL_NUMBER = A.SERIAL_NUMBER
AND B.USER_ID = A.USER_ID
AND B.EPARCHY_CODE IS NOT NULL
AND ROWNUM = 1) EPARCHY_CODE,
A.SP_ID,
A.SP_PRODUCT_ID
FROM TF_F_USER_SP A, TD_B_PARTY_PRICE C
WHERE A.SP_PRODUCT_ID = '3100190101'
AND ROWNUM < 5
AND A.END_DATE > SYSDATE
AND C.BILLINGMODECODE = 'OrderMonth';
LOOP
FETCH IV_CURSOR
INTO IV_SERIAL_NUMBER,
IV_USER_ID,
IV_EPARCHY_CODE,
IV_SP_ID,
IV_SP_PRODUCT_ID;
EXIT WHEN IV_CURSOR%NOTFOUND;--问题:当最后一条数据时跳出循环就无法执行下一行判断插入了
IF IV_SERIAL_NUMBER_TEMP <> IV_SERIAL_NUMBER AND
IV_SERIAL_NUMBER_TEMP <> '-1' THEN
BEGIN
-- 先发送上一个用户的短信。
IV_NOTICE_CONTENT := '温馨提示:您当月已订购以下增值产品:' || IV_USER_SP_ALL ||
'。您可拨打10010查询和退订。';
INSERT INTO TI_O_SMS
(SMS_NOTICE_ID,
EPARCHY_CODE,
IN_MODE_CODE,
SMS_CHANNEL_CODE,
RECV_OBJECT_TYPE,
RECV_OBJECT,
ID,
SMS_TYPE_CODE,
SMS_KIND_CODE,
NOTICE_CONTENT_TYPE,
NOTICE_CONTENT,
FORCE_REFER_COUNT,
SMS_PRIORITY,
REFER_TIME,
REFER_STAFF_ID,
REFER_DEPART_ID,
DEAL_TIME,
DEAL_STATE,
REMARK,
SEND_TIME_CODE,
SEND_OBJECT_CODE)
SELECT TO_NUMBER(F_SYS_GETSEQID('0851',
'seq_smssend_id')),
'0851',
'0',
'11', --短信渠道编码:客户服务
'00', --被叫对象类型:00-手机号码
IV_SERIAL_NUMBER_TEMP, --被叫对象:传手机号码
NVL(TO_NUMBER(IV_USER_ID_TEMP), 0), --被叫对象标识:传用户标识
'20', --短信类型:20-业务通知
'02', --短信种类:02-短信通知
'0', --短信内容类型:0-指定内容发送
IV_NOTICE_CONTENT, --短信内容
1, --指定发送次数
50, --短信优先级
SYSDATE, --提交时间
'',
'',
SYSDATE, --处理时间
'0', --处理状态:0-未处理
'定制业务信息的短信提醒', --备注
'2',
1
FROM DUAL;
IV_USER_SP_ALL := '';
IV_COUNT := 0;
END;
END IF;
SELECT DISTINCT A.SP_PRODUCT_NAME
INTO IV_SP_PRODUCT_NAME
FROM TD_B_PARTY_PRODUCT A
WHERE A.SP_ID = IV_SP_ID
AND A.SP_PRODUCT_ID = IV_SP_PRODUCT_ID;
/*SELECT to_number(REGEXP_REPLACE(SUBSTR(A.PARAVALUE,5,INSTR(A.PARAVALUE, ';', 3, 1) -
INSTR(A.PARAVALUE, '=', 3, 1) - 1),'¥|M',''))/100 price
INTO IV_PRICE
FROM TD_B_PARTY_PRICE A
WHERE A.PRODUCTCODE = IV_SP_PRODUCT_ID
AND A.BILLINGMODECODE = 'OrderMonth';*/
IF IV_COUNT = 0 THEN
IV_USER_SP_ALL := IV_SP_PRODUCT_NAME || '业务、资费' || IV_PRICE ||
'元/月;';
ELSE
IV_USER_SP_ALL := IV_USER_SP_ALL || '、' || IV_SP_PRODUCT_NAME ||
'业务、资费' || IV_PRICE || '元/月;';
END IF;
IV_COUNT := IV_COUNT + 1;
IV_USE_Count := IV_USE_Count + 1;
IF IV_USE_Count = IV_CURSOR%ROWCOUNT-1 THEN
IV_SERIAL_NUMBER_TEMP := '0';
ELSE
IV_SERIAL_NUMBER_TEMP := IV_SERIAL_NUMBER;
END IF;
--IV_SERIAL_NUMBER_TEMP := IV_SERIAL_NUMBER;
IV_USER_ID_TEMP := IV_USER_ID;
END LOOP;
CLOSE IV_CURSOR;
--====================定制业务信息的短信提醒====================-
V_RESULTINFO := '成功插入短信接口表';
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
V_RESULTINFO := '定制生效的套餐发短信提醒报错:' || SQLERRM;
V_RESULTCODE := -1;
END;
V_RESULTCODE := 0;
V_RESULTINFO := 'trade OK!';
end PRO_SMS_SENDINFO_lastTest;如题 如对提问不清楚 请讲 多谢了 在线等
先声明哈:查询结果是我手动改的,因为的确没有数据测试,我就手动改了一条数据。
不知道大家有没有遇到过这种问题?
*/
SELECT to_number(REGEXP_REPLACE(SUBSTR(A.PARAVALUE,5,INSTR(A.PARAVALUE, ';', 3, 1) - INSTR(A.PARAVALUE, '=', 3, 1) - 1),'¥|M',''))/100 price INTO IV_PRICE FROM TD_B_PARTY_PRICE A WHERE A.PRODUCTCODE = IV_SP_PRODUCT_ID AND A.BILLINGMODECODE = 'OrderMonth';
5,
INSTR(A.PARAVALUE, ';', 3, 1) -
INSTR(A.PARAVALUE, '=', 3, 1) - 1),
'¥|M',
'')) / 100 PRICE
INTO IV_PRICE
FROM TD_B_PARTY_PRICE A
WHERE A.PRODUCTCODE = IV_SP_PRODUCT_ID
AND A.BILLINGMODECODE = 'OrderMonth';
我想了个方法用nvl当返回空的时候就返回0
nvl('xxx',0); 但是我的语句居然放到这里不启用?????