iflytek_Oracle存储过程编写-员工生日提醒、入职年限提醒、资产借用提醒等存储过程案例

1、Oracle自学：

as ：别名(alias的缩写)

:= ：赋值

|| ：连接符号可以将两个字段的值连接在一起

fetch into : 就是将游标里的数据取出来存放在变量里

exit when：游标一条一条地遍历记录，当找不到记录时退出（游标迭代完了，退出）

%notfound ：
cursor%NOTFOUND表示这个游标没有查到数据
–%FOUND
– sql 语句影响了一行或多行时为TRUE
–%NOTFOUND
– sql 语句没有影响任何行时为TRUE
–%ROWCOUNT– sql 语句影响的行数
–%ISOPEN - 游标是否打开，始终为FALSE

%rowtype ：表示该类型为行数据类型，存储的是一行数据，一行数据里可以有多列，类似于表里的一行数据，也可以是游标里的一行数据。

–%rowcount– sql 语句影响的行数

%type ：对应一个字段
%rowtype ：对应一条记录(即2个以上字段)

mod ：取余函数

EXCEPTION
when others then ：http://www.jb51.cc/article/p-pyynmeja-me.html

ref 就是声明变量游标作为变量处理的意思

–员工异常打卡信息集合
cur_person pkg_mail_param.cur_personmail;//集合的东西
empRowType cur_person%rowtype;//行类型

oracle字段就类似于excel的数据列、数据库里叫记录的属性

单引号有两个作用，一是字符串是由单引号引用，二是转义。

N’ AS SCBZ
新增了一个字段，字段名为scbz，每条记录的字段值都为’N’
as的作用就是当做别名的意思，当做。其实空格代替as 也可以。（前面时候吗的别名）

2、写存储过程理论积累

T_FA_FaBorrowing

t_fa_faborrowing

资产借用归还提醒：property—borrow-return-remind
预计归还日期： expecte-date
实际归还日期：actual

T_FA_FaBorrowingEntry - FActualReturnDate
t_fa_faborrowingentry - factualreturnDate

(这两个的东西找出方法)

T_BD_Person - FEmail
t_bd_person - femail

在存储过程（PROCEDURE）和函数(FUNCTION)中没有区别，在视图（VIEW）中只能用AS不能用IS，在游标(CURSOR)中只能用IS不能用AS。

–资产借用归还提醒
–add by hjyang6
type propertyreturninfo is record(
–借用人
emp_user t_fa_faborrowing.fuserid%type,
–预计归还日期
emp_expectedReturnDate t_fa_faborrowing.fexpectedreturndate%type,
–实际归还日期
emp_actualReturnDate t_fa_faborrowingentry.factualreturndate %type,
–Email
emp_email t_bd_person.femail%type
);

–资产借用归还提醒
–add by hjyang6
type cur_returnremind is ref cursor return propertyreturninfo;

当前日期时间：v_lastyearfrist date := trunc(SYSDATE,‘yyyy’)+1;

内连接、外连接、@R_404_432@：
SELECT * FROM A inner join B ON A.F1 = B.F1,内连接，显示的查询结果，就是查询条件A 的必须等于B的数据；
SELECT * FROM A left join B ON A.F1 = B.F1,左连接，显示的查询结果，就是左边表的全部，和右边表的符合查询条件的数据。
这样很好理解了吧

not就是不满足某个条件的时候
AND的优先级大于OR，试验如下：

fetch…into…是sql 里面获取游标里的量用到的 fetch 后是你的游标 into 后是你的变量exit when 是当什么时候退

%notfound：表示这个游标没有查到数据

3、查找数据

select faborrowing.fuserid,
faborrowing.fexpectedreturndate,
faborrowingentry.factualreturndate,
person.femail
from t_fa_faborrowing faborrowing
left join t_bd_person person
on person.fid = faborrowing.fuserid
left join t_fa_faborrowingentry faborrowingentry
on faborrowing.fid = faborrowingentry.fparentid;

SELECT person.femail
FROM t_fa_faborrowing faborrowing
left join t_fa_faborrowingentry faborrowingentry
on faborrowing.fid = faborrowingentry.fparentid
WHERE to_char(faborrowing.fexpectedreturndate,‘yyyy-MM-dd’) =
‘2015-05-08’
AND faborrowingentry.factualreturndate IS NULL;

SELECT person.femail
FROM t_bd_person person
WHERE person.FID IN
(SELECT faborrowing.fuserid
FROM t_fa_faborrowing faborrowing
left join t_fa_faborrowingentry faborrowingentry
on faborrowing.fid = faborrowingentry.fparentid
WHERE to_char(faborrowing.fexpectedreturndate,‘yyyy-MM-dd’) =
‘2015-05-08’
AND faborrowingentry.factualreturndate IS NULL)

sysdate

SELECT person.femail
FROM t_bd_person person
WHERE person.FID IN
(SELECT faborrowing.fuserid
FROM t_fa_faborrowing faborrowing
left join t_fa_faborrowingentry faborrowingentry
on faborrowing.fid = faborrowingentry.fparentid
WHERE to_char(faborrowing.fexpectedreturndate,‘yyyy-MM-dd’) =
to_char(sysdate +1,’yyyy-MM-dd’)
AND faborrowingentry.factualreturndate IS NULL)

4、存储过程

/*****************系统调用方法*******************************/
–资产借用归还提醒
–add by hjyang6
procedure pro_remind_mail as
–发送方邮件地址(与登录用户相同)
v_sender VARCHAR2(50) := ‘eas_service@iflytek.com’;
–SMTP服务器地址(默认)
v_mailhost VARCHAR2(30) := ‘mail.iflytek.com’;

--当前时间
v_current_date date;
--预计归还日期
v_expected_date date;
--实际归还日期
v_actual_date date;

--资产借用归还提醒信息集合
cur_person pkg_mail_param.cur_returnremind;--定义提醒的结合
empRowType cur_person%rowtype;--每一行的类型绑定

--邮件标题
v_subject varchar2(100) := null;
--邮件内容
v_message varchar2(4000) := null;

-- oracle  内部错误信息及编码
v_errm varchar(512) := null;
v_code varchar(512) := null;

begin

--发邮件的标题？--这里内容放在后面
v_subject := '资产借用归还信息提醒【 今天：' || to_char(sysdate(),'yyyy-mm-dd') || '】';

-- 获取资产借用归还提醒集合--获取集合
pro_obtain_remindinfo(cur_person);

    --循环集合
loop
  fetch cur_person
    into empRowType;
  exit when cur_person%notfound;
  --empRowType.person_mail := 'jfzhao@iflytek.com';
  v_message              := null;
  build_remind_message(v_message);
  IF cur_person%rowCount mod 100 = 1 THEN
     build_smtp_connection(v_sender,v_mailhost);
  ELSE
     UTL_SMTP.ehlo(v_conn,v_mailhost);
  END IF;
  pro_send_mail(v_sender,empRowType.person_mail,v_subject,v_message);
end loop;
close cur_person;
build_smtp_connection(v_sender,v_mailhost);
pro_send_mail(v_sender,'erpsrv@iflytek.com','EAS考勤提醒发送成功','EAS普通员工考勤提醒邮件发送成功！');

UTL_SMTP.QUIT(v_conn);

Exception
when others then
v_errm := sqlerrm;
v_code := sqlcode;
insert into t_syslog
values
(sys_guid(),‘pkg_mail.pro_remind_mail’,v_errm,v_code,sysdate);
BEGIN
UTL_SMTP.QUIT(v_conn);
EXCEPTION
WHEN OTHERS THEN
v_errm := sqlerrm;
v_code := sqlcode;
insert into t_syslog
values
(sys_guid(),sysdate);
END;
END pro_remind_mail;

/*****************功能支撑方法************************************/

/******************数据构造方法***********************************/

–对全员需要资产借用归还提醒的集合
procedure pro_obtain_remindinfo(cur_remind out pkg_mail_param.cur_returnremind) as
– oracle 内部错误信息及编码
v_errm varchar(512) := null;
v_code varchar(512) := null;
begin
open cur_remind for
select faborrowing.fuserid,
faborrowing.fexpectedreturndate as empexpecteddate
from t_fa_faborrowing faborrowing
left join t_bd_person person
on person.fid = faborrowing.fuserid
left join t_fa_faborrowingentry faborrowingentry
on faborrowing.fid = faborrowingentry.fparentid

where faborrowingentry.factualreturnDate is null
group by faborrowing.fuserid;

Exception
when others then
v_errm := sqlerrm;
v_code := sqlcode;
insert into t_syslog
values
(sys_guid(),
‘pro_obtain_remindinfo’,
v_errm,
v_code,
sysdate);
end pro_obtain_remindinfo;

–组合邮件提醒内容
procedure build_remind_message(v_message in out varchar2) as
begin
v_message := ‘您好！
您从行政部仓库所借机器/设备将于明天到期，请于到期前做好工作安排并及时归还所借物资，感谢您的支持！
如需延期使用暂不能及时归还的，请按照以下流程操作：
1、逾期后需延期借用（累计借用周期在一个月内）的，请重新提交邮件申请，注明需续借周期并抄送部门领导审批；
2、逾期后需延期借用（累计借用周期在一个月以上）的，请提交OA“固定资产申请”流程，并办理相关调拨借用手续，借用期满后办理退库；
3、逾期后不予归还并不办理延期借用手续的，将按照《固定资产管理制度》进行相应处罚。

以下为《固定资产管理制度》中，涉及资产借用及处罚相关条款，请参阅！

7.8固定资产借用
7.8.1借用周期（累计借用时间在一个月）内的，由需求人发送邮件申请，经分管领导和资产管理部门审批后办理登记借用手续；超过借用周期的，由需求人提交OA“固定资产申请”流程办理领用手续，借用期满后办理退库。
7.8.2固定资产原则上一律不准借给公司以外人员使用，特殊情况必须经部门分管领导和资产管理部门审批。
8.6借用期满的固定资产，借用人应主动退回行政部仓库。逾期未及时归还的将直接将其所借用资产调拨至借用人名下，由其部门承担相应折旧费用，并对借用人处以50元罚款。
‘;
end build_attendance_message;

代码地址：

书写代码内容

写的乱仅仅是记录