create or replace procedure test_count
as
id VARCHAR2(50);
name VARCHAR2(50);
credentialCode VARCHAR2(50);
FK_ITEM_ID VARCHAR2(50);
totalMoney VARCHAR2(50);
totalBuyTime VARCHAR2(50);
usedTime VARCHAR2(50);
freeTime VARCHAR2(50);
totalTime VARCHAR2(50);
diffTime VARCHAR2(50);
--IF_RIGHT VARCHAR2(50);
begin
SELECT
--c.*,c.购买学时 - c.已花费 应该剩余
c.userid into id ,c.name into name, c.login_id into credentialCode ,c.fk_item_id into FK_ITEM_ID,
c.购买学时 - c.已花费 into freeTime,c.已花费 into usedTime, c.购买学时 into totalBuyTime,
c.different_time into diffTime,c.totalMoney into totalMoney
FROM
(
SELECT e.name,
--s.login_id,
T.login_id,
T . SID AS userid,
NVL (T .utime, 0) + NVL (TO_NUMBER(T .ptime), 0) - SUM (zb.class_time) AS different_time,
SUM (zb.class_time) AS 购买学时,
T .ptime AS 剩余,
T .utime AS 已花费,
zb.fk_item_id AS fk_item_id,
t.totalMoney
FROM
(
SELECT
su. ID AS SID,
su .login_id as login_id, -- 加
SUM (pbc. TIME) AS utime,
pt. TIME AS ptime,
"SUM" (zii.study_price * pbc. TIME) as totalMoney
FROM
sso_user su
LEFT JOIN training_course_student tc ON tc.student_id = su. ID -- 学习记录进度表
LEFT JOIN zgpx_class_opencourse zo ON tc.course_id = zo. ID -- 课程批次关联表
LEFT JOIN pe_bzz_tch_course pbc ON pbc. ID = zo.course_id -- 课程基本信息表
LEFT JOIN ZGPX_ITEM_INFO zii on pbc.CONFER_NAME = zii.id -- 资格表
LEFT JOIN (
SELECT
ssu. ID AS ssuid,
SUM (ptp. TIME) AS TIME
FROM
PE_TOTAL_PEROID ptp -- 学时账号余额表
LEFT JOIN SSO_USER ssu ON ssu.LOGIN_ID = ptp.CREDENTIAL_CODE
AND ptp.FK_ITEM_ID = ssu.FK_ITEMINFO_ID
GROUP BY
ssu. ID
) pt ON pt.ssuid = su. ID
WHERE
tc.is_delete IS NULL
AND (
pbc.confer_name = su.fk_iteminfo_id
OR pbc. ID IS NULL
)
GROUP BY
su. ID,
pt. TIME,
su .login_id
) T
LEFT JOIN zgpx_book_sso_order zb ON zb.fk_sso_user = T . SID,
--sso_user s, --
pe_employee_info e
WHERE
(zb.fk_order_status = '20131012' or zb.fk_order_status = '2016052502')
--and s.id =T . SID --
--and e.fk_sso_user_id=s.id
and e.fk_sso_user_id=t.SID
GROUP BY
T . SID,
--s.login_id, --
T.login_id,
e.name,
T .utime,
T .ptime,
fk_item_id,
totalMoney
) C
WHERE
c.different_time != '0' -- c.different_time大于0 或小于0均为异常数据
ORDER BY
c.different_time DESC;
DBMS_OUTPUT.put_line('总人数：'||diffTime);
end;
