资产借用归还提醒、员工生日及入职满几年的祝福及祝贺

来源：互联网发布：有c语言基础学java 编辑：程序博客网时间：2024/06/10 22:18

1、应用到一些表名信息：
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;
2、数据库查询语句：

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) 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)

3、印象比较深刻的连接查询语句：

select person.fbirthday,       person.femail,       person_position.fjoindate,       person.femail  from t_bd_person person  left join t_hr_personposition person_position    on person_position.fpersonid = person.fid  left join T_ORG_PositionMember PositionMember    on PositionMember.Fpersonid = person.fid  left join t_org_PositionHierarchy PositionHierarchy    on PositionHierarchy.Fid = PositionMember.Fpositionid  left join T_ORG_PositionMember PositionMember    on PositionMember.Fpersonid = person.fid where person.femail is not null;-- 刷新我的三观啊

0 0