提问人：sudhir mishra 提问时间：10/31/2023 最后编辑：sudhir mishra 更新时间：11/1/2023 访问量：81
Oracle代码没有编译，请帮帮我
Oracle code is not compiling kindly help me
问：
过程 UpdateAttendance
（
V_AttendanceDate日期，
V_LoginId varchar2，
V_SignType varchar2，
V_IpAddress varchar2，
V_LogonUserName VARCHAR2，
V_ShiftName VARCHAR2，
V_WorkLocationType VARCHAR2，
V_WorkLocation VARCHAR2，
V_Self_Covid_Status VARCHAR2，
V_Self_Covid_Remark VARCHAR2，
V_Family_Covid_Status VARCHAR2，
V_Family_Covid_Remark VARCHAR2，
curAttendanceDate OUT T_CURSOR
) 如 开始
- 在本节中，我收到错误 选择 SignOut 为 NULL 时的情况，然后间隔“-1”天 （5） + V_AttendanceDate ELSE V_AttendanceDate结束
从T_ATTENDANCE_ATTENDANCE进入V_AttendanceDate
WHERE UPPER (ShiftName) = 'NIGHT'
AND LoginId = V_LoginId
AND AttendanceDate = INTERVAL '-1' DAY (5) + V_AttendanceDate
AND V_SignType = 'SIGNOUT'
AND Active = 1;
UPDATE T_ATTENDANCE_ATTENDANCE
SET SignIn =
CASE
WHEN V_SignType = 'SIGNIN'
AND SignIn IS NULL
THEN
SYSDATE
ELSE
SignIn
END,
SignOut =
CASE
WHEN V_SignType = 'SIGNOUT'
AND signout IS NULL
THEN
SYSDATE
ELSE
SignOut
END,
IpAddress =
CASE
WHEN NVL (IpAddress, '') = '' THEN V_IPAddress
ELSE IpAddress || ',' || V_IPAddress
END,
LogonUserName =
CASE
WHEN NVL (LogonUserName, '') = '' THEN V_LogonUserName
ELSE LogonUserName || ',' || V_LogonUserName
END,
WorkLocation =
CASE
WHEN V_SignType = 'SIGNIN' THEN V_WorkLocation
ELSE WorkLocation
END,
WorkLocationType =
CASE
WHEN V_SignType = 'SIGNIN' THEN V_WorkLocationType
ELSE WorkLocationType
END,
ShiftName =
CASE
WHEN V_SignType = 'SIGNIN'
AND NVL (ShiftName, '') = ''
THEN
V_ShiftName
ELSE
ShiftName
END
WHERE AttendanceDate = V_AttendanceDate
AND LoginId = V_LoginId
AND Active = 1;
UPDATE T_ATTENDANCE_ATTENDANCE
SET ShiftName = 'LEAVE'
WHERE AttendanceDate < SYSTIMESTAMP - 2
AND SignIn IS NULL
AND SignOut IS NULL
AND ShiftName IN ('MORNING',
'AFTERNOON1',
'AFTERNOON2',
'GENERAL1',
'GENERAL2',
'NIGHT');
INSERT INTO T_ATTENDANCE_USER_COVID_STATUS (LoginId,
AttendanceDate,
Self_Covid_Status,
Self_Covid_Remark,
Family_Covid_Status,
Family_Covid_Remark,
UpdatedOn,
UpdatedBy)
SELECT V_LoginId,
V_AttendanceDate,
V_Self_Covid_Status,
V_Self_Covid_Remark,
V_Family_Covid_Status,
V_Family_Covid_Remark,
SYSTIMESTAMP,
UPPER (V_LoginId)
FROM DUAL
WHERE V_SignType = 'SIGNIN';
结束 UpdateAttendance;
答：
0赞
Littlefoot
10/31/2023
#1
如果你使用了一个像样的编辑器，代码格式化程序会显示一些错误，比如
- 您单独注释了打开引用光标语句和左列
- 失踪
end if
修复后，代码看起来像这样，但我不知道它是否会编译，因为我们没有你的表。
CREATE OR REPLACE PACKAGE BODY PKG_ATTENDANCE_DASHBOARD
AS
PROCEDURE Proc_Dashboard (V_CommandType VARCHAR2,
V_LoginId VARCHAR2,
V_SignType VARCHAR2,
V_AttendanceDate DATE,
V_Year VARCHAR2,
V_Month VARCHAR2,
V_IPAddress VARCHAR2,
V_LogonUserName VARCHAR2,
V_WorkLocation VARCHAR2,
V_WorkLocationType VARCHAR2,
V_Company VARCHAR2,
V_UserType VARCHAR2,
V_ShiftName VARCHAR2,
V_Self_Covid_Status VARCHAR2,
V_Self_Covid_Remark VARCHAR2,
V_Family_Covid_Status VARCHAR2,
V_Family_Covid_Remark VARCHAR2,
V_UpdatedBy VARCHAR2 /* curGetCalendar OUT SYS_REFCURSOR,
curGetCovidStatus OUT SYS_REFCURSOR,
curListCompanyName OUT SYS_REFCURSOR,
curListTowerName OUT SYS_REFCURSOR,
curAttendanceListByDate OUT SYS_REFCURSOR*/
)
AS
V_Date DATE := TO_DATE (V_Year || '-' || V_Month || '-01', 'yyyy-mm-dd');
BEGIN
IF V_CommandType = 'getCalendar'
THEN
WITH
DaysInMonth (dates)
AS
(SELECT V_Date AS dates FROM DUAL
UNION ALL
SELECT INTERVAL '1' DAY (5) + dates
FROM DaysInMonth
WHERE EXTRACT (MONTH FROM dates) = EXTRACT (MONTH FROM V_Date))
SELECT dates
FROM DaysInMonth;
INSERT INTO T_ATTENDANCE_ATTENDANCE (LoginId,
AttendanceDate,
ShiftName,
ShiftStartTime,
ShiftEndTime,
Active,
AttendanceStatus)
SELECT u.LoginID,
d.dates,
'',
NULL,
NULL,
1,
'Approved'
FROM DaysInMonth d CROSS JOIN T_ATTENDANCE_USER_MASTER u
WHERE EXTRACT (MONTH FROM dates) = EXTRACT (MONTH FROM V_Date)
AND LoginId = V_LoginId
AND NOT EXISTS
(SELECT LoginId
FROM T_ATTENDANCE_ATTENDANCE
WHERE LoginId = V_LoginId
AND EXTRACT (YEAR FROM ATTENDANCEDATE) =
EXTRACT (YEAR FROM V_Date)
AND EXTRACT (MONTH FROM AttendanceDate) =
EXTRACT (MONTH FROM V_Date)
AND ACTIVE = 1);
V_Date := INTERVAL '1' MONTH + V_Date;
WITH
DaysInMonth (dates)
AS
(SELECT V_Date AS dates FROM DUAL
UNION ALL
SELECT INTERVAL '1' DAY (5) + dates
FROM DaysInMonth
WHERE EXTRACT (MONTH FROM dates) = EXTRACT (MONTH FROM V_Date))
SELECT dates
FROM DaysInMonth;
INSERT INTO T_ATTENDANCE_ATTENDANCE (LoginId,
AttendanceDate,
ShiftName,
ShiftStartTime,
ShiftEndTime,
Active,
AttendanceStatus)
SELECT u.LoginID,
d.dates,
'',
NULL,
NULL,
1,
'Approved'
FROM DaysInMonth d CROSS JOIN T_ATTENDANCE_USER_MASTER u
WHERE EXTRACT (MONTH FROM dates) = EXTRACT (MONTH FROM V_Date)
AND LoginId = V_LoginId
AND NOT EXISTS
(SELECT LoginId
FROM T_ATTENDANCE_ATTENDANCE
WHERE LoginId = V_LoginId
AND EXTRACT (YEAR FROM ATTENDANCEDATE) =
EXTRACT (YEAR FROM V_date)
AND EXTRACT (MONTH FROM AttendanceDate) =
EXTRACT (MONTH FROM V_date)
AND ACTIVE = 1);
OPEN curGetCalendar FOR
SELECT AttendanceDate,
ShiftName,
TO_CHAR (AttendanceDate, 'ddd') cDAY,
V_Year,
V_Month,
TO_CHAR (SignIn, 'yyyy-MM-dd hh:mm:ss tt') SignIn,
TO_CHAR (SignOut, 'yyyy-MM-dd hh:mm:ss tt') SignOut
FROM T_ATTENDANCE_ATTENDANCE
WHERE LoginId = V_LoginId
AND EXTRACT (YEAR FROM ATTENDANCEDATE) = V_Year
AND EXTRACT (MONTH FROM AttendanceDate) = V_Month
AND ACTIVE = 1
ORDER BY AttendanceDate ASC;
UPDATE a
SET a.shiftname =
(SELECT CASE
WHEN TO_CHAR (TO_DATE (a.attendancedate), 'DAY') IN
('SATURDAY')
AND a.week IN (2, 4)
THEN
'WEEKLYOFF'
WHEN TO_CHAR (TO_DATE (a.attendancedate), 'WW') IN
('SUNDAY')
THEN
'WEEKLYOFF'
ELSE
'GENERAL1'
END
FROM (SELECT attendancedate,
shiftname,
ROW_NUMBER ()
OVER (
PARTITION BY TO_CHAR (
TO_DATE (attendancedate, 'DAY'))
ORDER BY attendancedate) AS week
FROM T_ATTENDANCE_ATTENDANCE a1
LEFT JOIN T_ATTENDANCE_USER_MASTER u
ON u.loginid = a1.loginid
LEFT JOIN T_ATTENDANCE_EMPLOYEE_MASTER e
ON e.empid = u.empid
WHERE a1.loginid = V_loginid
AND e.company IS NULL
AND EXTRACT (YEAR FROM attendancedate) =
EXTRACT (YEAR FROM SYSDATE)
AND EXTRACT (MONTH FROM attendancedate) =
EXTRACT (MONTH FROM SYSDATE)
AND NVL (shiftname, 'x') = 'x') a);
ELSE
IF V_CommandType = 'UpdateAttendance'
THEN
SELECT CASE
WHEN SignOut IS NULL THEN INTERVAL '-1' DAY (5) + p_AttendanceDate
ELSE V_AttendanceDate
END
INTO V_AttendanceDate
FROM T_ATTENDANCE_ATTENDANCE
WHERE UPPER (ShiftName) = 'NIGHT'
AND LoginId = V_LoginId
AND AttendanceDate = INTERVAL '-1' DAY (5) + V_AttendanceDate
AND V_SignType = 'SIGNOUT'
AND Active = 1;
UPDATE T_ATTENDANCE_ATTENDANCE
SET SignIn =
CASE
WHEN V_SignType = 'SIGNIN'
AND SignIn IS NULL
THEN
SYSTIMESTAMP
ELSE
SignIn
END,
SignOut =
CASE
WHEN V_SignType = 'SIGNOUT'
AND signout IS NULL
THEN
SYSTIMESTAMP
ELSE
SignOut
END,
IpAddress =
CASE
WHEN NVL (IpAddress, '') = '' THEN V_IPAddress
ELSE IpAddress || ',' || V_IPAddress
END,
LogonUserName =
CASE
WHEN NVL (LogonUserName, '') = '' THEN V_LogonUserName
ELSE LogonUserName || ',' || V_LogonUserName
END,
WorkLocation =
CASE
WHEN V_SignType = 'SIGNIN' THEN V_WorkLocation
ELSE WorkLocation
END,
WorkLocationType =
CASE
WHEN V_SignType = 'SIGNIN' THEN V_WorkLocationType
ELSE WorkLocationType
END,
ShiftName =
CASE
WHEN V_SignType = 'SIGNIN'
AND NVL (ShiftName, '') = ''
THEN
V_ShiftName
ELSE
ShiftName
END
WHERE AttendanceDate = V_AttendanceDate
AND LoginId = V_LoginId
AND Active = 1;
UPDATE T_ATTENDANCE_ATTENDANCE
SET ShiftName = 'LEAVE'
WHERE AttendanceDate < SYSTIMESTAMP - 2
AND SignIn IS NULL
AND SignOut IS NULL
AND ShiftName IN ('MORNING',
'AFTERNOON1',
'AFTERNOON2',
'GENERAL1',
'GENERAL2',
'NIGHT');
INSERT INTO T_ATTENDANCE_USER_COVID_STATUS (LoginId,
AttendanceDate,
Self_Covid_Status,
Self_Covid_Remark,
Family_Covid_Status,
Family_Covid_Remark,
UpdatedOn,
UpdatedBy)
SELECT V_LoginId,
V_AttendanceDate,
V_Self_Covid_Status,
V_Self_Covid_Remark,
V_Family_Covid_Status,
V_Family_Covid_Remark,
SYSTIMESTAMP,
UPPER (V_LoginId)
FROM DUAL
WHERE V_SignType = 'SIGNIN';
END IF;
IF V_CommandType = 'GetCovidStatus'
THEN
OPEN cur2 FOR
SELECT E1.FirstName || ' ' || E1.LastName UpdatedBy,
C.*,
E.FirstName || ' ' || E.LastName FullName
FROM T_ATTENDANCE_USER_COVID_STATUS C
LEFT JOIN T_ATTENDANCE_USER_MASTER U
ON U.LoginID = C.LoginId
AND U.IsActive = 'Y'
LEFT JOIN T_ATTENDANCE_EMPLOYEE_MASTER E
ON E.EmpID = U.EmpID
AND E.IsActive = 'Y'
LEFT JOIN T_ATTENDANCE_USER_MASTER U1
ON U1.LoginID = C.UpdatedBy
AND U1.IsActive = 'Y'
LEFT JOIN T_ATTENDANCE_EMPLOYEE_MASTER E1
ON E1.EmpId = U1.EmpId
AND E1.IsActive = 'Y'
WHERE C.LoginId = V_LoginId
AND C.AttendanceDate = SUBSTR (TO_CHAR (SYSTIMESTAMP, 'yyyy-mm-dd
hh24:mi:ss.ff3'), 1, 10);
END IF;
IF V_CommandType = 'UpdateCovidStatus'
THEN
IF NOT EXISTS
(SELECT 1
FROM T_ATTENDANCE_USER_COVID_STATUS
WHERE AttendanceDate = V_AttendanceDate
AND LoginId = V_LoginId)
THEN
INSERT INTO T_ATTENDANCE_USER_COVID_STATUS (LoginId,
AttendanceDate,
Self_Covid_Status,
Self_Covid_Remark,
Family_Covid_Status,
Family_Covid_Remark,
UpdatedOn,
UpdatedBy)
SELECT V_LoginId,
V_AttendanceDate,
V_Self_Covid_Status,
V_Self_Covid_Remark,
V_Family_Covid_Status,
V_Family_Covid_Remark,
SYSTIMESTAMP,
UPPER (V_UpdatedBy)
FROM DUAL;
ELSE
UPDATE T_ATTENDANCE_USER_COVID_STATUS
SET self_Covid_Status = V_Self_Covid_Status,
Self_Covid_Remark = V_Self_Covid_Remark,
Family_Covid_Status = V_Family_Covid_Status,
Family_Covid_Remark = V_Family_Covid_Remark,
UpdatedOn = SYSTIMESTAMP,
UpdatedBy = V_UpdatedBy
WHERE AttendanceDate = V_AttendanceDate
AND LoginId = V_LoginId;
END IF;
END IF;
IF V_CommandType = 'GetCompany'
THEN
OPEN cur3 FOR
SELECT 1 AS sr, UPPER (CompanyName) CompanyName FROM T_ATTENDANCE_COMPANYS
UNION
SELECT 0, 'All Companies' FROM DUAL
ORDER BY sr ASC;
END IF;
IF V_CommandType = 'TeamAvailability'
THEN
OPEN cur4 FOR SELECT TowerPk, RTRIM (LTRIM (TowerName)) TowerName
FROM T_ATTENDANCE_TOWER_MASTER
WHERE IsActive = 'Y'
UNION
SELECT 0, 'Admin' FROM DUAL
ORDER BY TowerName ASC;
OPEN cur5 FOR
SELECT E.FirstName || ' ' || E.LastName AS Name,
E.ContactNo,
r.RoleName AS Role,
A.ShiftName,
A.SignIn,
A.SignOut,
NVL (T.TowerName, 'Admin') TowerName,
UPPER (E.Company) AS Company,
WorkLocation,
WorkLocationType,
Covid.Self_Covid_Status,
Covid.Family_Covid_Status
FROM T_ATTENDANCE_ATTENDANCE A
JOIN T_ATTENDANCE_USER_MASTER U
ON A.LoginId = U.LoginID
AND U.IsActive = 'Y'
JOIN T_ATTENDANCE_EMPLOYEE_MASTER E
ON U.EmpID = E.EmpID
AND E.IsActive = 'Y'
LEFT JOIN T_ATTENDANCE_USER_ATTENDANCE_TEAM uat
ON uat.LoginId = u.LoginID
AND uat.isActive = 'Y'
LEFT JOIN T_ATTENDANCE_TOWER_MASTER T ON T.TowerPk = uat.TowerPk
JOIN T_ATTENDANCE_ROLE_MASTER R ON R.RolePk = u.RolePk
LEFT JOIN T_ATTENDANCE_USER_COVID_STATUS Covid
ON Covid.LoginId = A.LoginId
AND Covid.AttendanceDate = a.AttendanceDate
WHERE a.AttendanceDate =
CASE
WHEN TO_NUMBER (TO_CHAR (SYSTIMESTAMP, 'HOUR')) < 7
THEN
INTERVAL '-1' DAY (5) + V_AttendanceDate
ELSE
V_AttendanceDate
END
AND a.Active = 1
AND E.Company =
CASE V_Company
WHEN 'All Companies' THEN E.Company
ELSE V_Company
END
AND ( ( NVL (V_UserType, 'Consultant') = 'Consultant'
AND E.Company NOT IN (''))
OR ( NVL (p_UserType, 'Employee') = 'Employee'
AND E.Company = ''))
ORDER BY T.TowerName, A.SignIn DESC;
END IF;
END IF;
END Proc_Dashboard;
END PKG_ATTENDANCE_DASHBOARD;
评论
0赞
sudhir mishra
10/31/2023
它没有编译我正在使用 Oracle 版本 4.1
0赞
sudhir mishra
10/31/2023
请帮助我，它显示了很多错误
0赞
Littlefoot
10/31/2023
没有 Oracle 数据库版本 4.1;我猜你说的是SQL Developer？但这只是一个工具，它对你编写的代码没有影响。至于“很多错误”，正如我所说 - 我们无法运行您的代码，我们没有您的表，因此我们会遇到很多错误（比您更多）。如果你一下子写了那个程序——这很难相信——你做错了。循序渐进，经常测试，在修复错误之前不要再往前走。因此，我建议你一步一步地重新开始。
0赞
MT0
10/31/2023
“column alias can't be day” 是的，它可以摆弄
0赞
Littlefoot
10/31/2023
@MT0：啊，是的，可以。我的格式化程序报告的“错误”实际上不是错误，而是信息，说 DAY 可能与关键字冲突。谢谢。
0赞
MT0
10/31/2023
#2
除其他错误外，您不能在 PL/SQL 中单独使用语句。你需要.但是，您甚至不想这样做，而应该将您的日历生成合并到语句中：SELECTSELECT ... [BULK COLLECT] INTO ...INSERT
INSERT INTO T_ATTENDANCE_ATTENDANCE(
LoginId,
AttendanceDate,
ShiftName,
ShiftStartTime,
ShiftEndTime,
Active,
AttendanceStatus
)
WITH DaysInMonth (dates) AS (
SELECT V_Date
FROM DUAL
UNION ALL
SELECT dates + INTERVAL '1' DAY
FROM DaysInMonth
WHERE dates + INTERVAL '1' DAY < ADD_MONTHS(TRUNC(v_date, 'MONTH'), 1)
)
SELECT u.LoginID,
d.dates,
NULL,
NULL,
NULL,
1,
'Approved'
FROM DaysInMonth d
CROSS JOIN T_ATTENDANCE_USER_MASTER u
WHERE LoginId = V_LoginId
AND NOT EXISTS(
SELECT LoginId
FROM T_ATTENDANCE_ATTENDANCE
WHERE LoginId = V_LoginId
AND ATTENDANCEDATE >= TRUNC(v_date, 'MONTH')
AND ATTENDANCEDATE < ADD_MONTHS(TRUNC(v_date, 'MONTH'), 1)
AND ACTIVE = 1
);
此外，和永远不会是真的，因为在 Oracle 中，与 Oracle 相同，并且 Oracle 使用三位一体逻辑 so 并且不计算 to 或 但是，相反，两者都计算到 和 since 不是，那么逻辑的两个分支都不会匹配。E.Company NOT IN ('')E.Company = ''''NULLE.Company NOT IN (NULL)E.Company = NULLTRUEFALSENULLNULLTRUE
您想要的是使用和匹配非空字符串和空字符串。E.Company IS NOT NULLE.company IS NULL
评论
0赞
sudhir mishra
10/31/2023
我故意将他字段留空，您可以填写任何文本
评论
CREATE TABLESELECT * FROM user_errors并通读它。