17,090
社区成员
发帖
与我相关
我的任务
分享
create or replace procedure P_CarMaintainRemindEamil is
CARNO varchar2(50); --待保養車牌
MAILADDRESS VARCHAR2(500); --收件人地址
SUMCAR NUMBER; --統計待保養車輛
cursor mycursor is
SELECT A.CARNO FROM BD_CAR A where sysdate < A.MAINTAINTODATE;
begin
SELECT COUNT(A.CARNO)
INTO SUMCAR
FROM BD_CAR A
where sysdate < A.MAINTAINTODATE;
open mycursor;
IF SUMCAR > 0 THEN
LOOP
fetch mycursor
INTO CARNO;
exit when mycursor%notfound;
SELECT max(B.MAIL)
INTO MAILADDRESS
FROM BD_CARANDDRIVER A, EMPLOYEE B
WHERE A.CARNO = CARNO
AND A.DRIVERNAME = B.CNAME
AND A.ISVALID = '1'; --查询司机邮件地址
INSERT INTO CIMAPGROUP
values
(CARNO, MAILADDRESS, '1', '1', '1', '1', '1'); --插入车牌没问题。司机邮件地址都是一个
END LOOP;
END IF;
end P_CarMaintainRemindEamil;
CREATE OR REPLACE PROCEDURE P_DRIVERLICENCEMAIL IS
DRIVERNAME VARCHAR2(20); --待換證司機姓名
SUBJECT VARCHAR2(100); --郵件主題
MAILCONTENT VARCHAR2(5000); --郵件內容
MAILADDRESS VARCHAR2(500); --收件人郵箱地址
LICENCEDATE DATE; --駕駛證到期日期
SUMDRIVER NUMBER; --統計待換證司機
LICENCENO VARCHAR2(50); --駕駛證號
CURSOR MYCURSOR IS
SELECT A.DRIVERNAME FROM BD_DRIVER A where sysdate < A.LICENSEVALTO;
BEGIN
SELECT COUNT(A.DRIVERNAME)
INTO SUMDRIVER
FROM BD_DRIVER A
WHERE SYSDATE < A.LICENSEVALTO; --統計待換證司機
OPEN MYCURSOR;
IF SUMDRIVER > 0 THEN
LOOP
FETCH MYCURSOR
INTO DRIVERNAME;
EXIT WHEN MYCURSOR% NOTFOUND;
SELECT MAX(A.LICENSEVALTO)
INTO LICENCEDATE
FROM BD_DRIVER A
WHERE A.DRIVERNAME = DRIVERNAME; --駕駛證到期日期
SELECT MAX(A.LICENSENO) --不加max报错,一个表十几条数据没有重复,一个驾驶证一个司机
INTO LICENCENO
FROM BD_DRIVER A
WHERE A.DRIVERNAME = DRIVERNAME; --駕駛證號
SUBJECT := '【調度室】司機換證提醒'; --邮件主题
MAILADDRESS := 'Happy4944@163.com'; --收件人郵箱
MAILCONTENT := 'XXX';
INSERT INTO APP_TEMPCAR
VALUES
(DRIVERNAME, --司机姓名不一样
‘’,
LICENCENO, --数据还是一样
MAILCONTENT,
1111,
SUBJECT,
'',
LICENCEDATE);
END LOOP;
END IF;
END P_DRIVERLICENCEMAIL;
create or replace procedure P_CarMaintainRemindEamil is
CARNO varchar2(50); --待保養車牌
MAILADDRESS VARCHAR2(500); --收件人地址
SUMCAR NUMBER; --統計待保養車輛
type v_mail_cursor is REF CURSOR;
v_mail v_mail_cursor;
cursor mycursor is
SELECT A.CARNO FROM BD_CAR A where sysdate < A.MAINTAINTODATE;
begin
SELECT COUNT(A.CARNO)
INTO SUMCAR
FROM BD_CAR A
where sysdate < A.MAINTAINTODATE;
open mycursor;
IF SUMCAR > 0 THEN
LOOP
fetch mycursor
INTO CARNO;
exit when mycursor%notfound;
OPEN v_mail for
SELECT B.MAIL
FROM BD_CARANDDRIVER A, EMPLOYEE B
WHERE A.CARNO = CARNO
AND A.DRIVERNAME = B.CNAME
AND A.ISVALID = '1'; --查询司机邮件地址
loop
FETCH v_mail INTO mailaddress;
exit when v_mail%notfound;
INSERT INTO CIMAPGROUP
values
(CARNO, MAILADDRESS, '1', '1', '1', '1', '1'); --插入车牌没问题。司机邮件地址都是一个
END LOOP;
END LOOP;
END IF;
end P_CarMaintainRemindEamil;
create or replace procedure P_CarMaintainRemindEamil is
CARNO varchar2(50); --待保養車牌
MAILADDRESS VARCHAR2(500); --收件人地址
SUMCAR NUMBER; --統計待保養車輛
type v_mail_cursor is REF CURSOR;
v_mail v_mail_cursor;
cursor mycursor is
SELECT A.CARNO FROM BD_CAR A where sysdate < A.MAINTAINTODATE;
begin
SELECT COUNT(A.CARNO)
INTO SUMCAR
FROM BD_CAR A
where sysdate < A.MAINTAINTODATE;
open mycursor;
IF SUMCAR > 0 THEN
LOOP
fetch mycursor
INTO CARNO;
exit when mycursor%notfound;
OPEN v_mail for
SELECT B.MAIL
FROM BD_CARANDDRIVER A, EMPLOYEE B
WHERE A.CARNO = CARNO
AND A.DRIVERNAME = B.CNAME
AND A.ISVALID = '1'; --查询司机邮件地址
loop
FETCH v_mail INTO mailaddress
exit when v_mail%notfound;
INSERT INTO CIMAPGROUP
values
(CARNO, MAILADDRESS, '1', '1', '1', '1', '1'); --插入车牌没问题。司机邮件地址都是一个
END LOOP;
END LOOP;
END IF;
end P_CarMaintainRemindEamil;
试试看