INSERT INTO employees (
employee_id,
first_name,
last_name,
card_num,
work_days
)
WITH names AS (
SELECT 1, 'John', 'Doe', 'D564311','YYYYYNN' FROM dual UNION ALL
SELECT 2, 'Justin', 'Case', 'C224311','YYYYYNN' FROM dual UNION ALL
SELECT 3, 'Mike', 'Jones', 'J288811','YYYYYNN' FROM dual UNION ALL
SELECT 4, 'Jane', 'Smith', 'S564661','YYYYYNN' FROM dual
) SELECT * FROM names;
4 rows affected
hidden batch(es)
CREATE TABLE locations AS
SELECT level AS location_id,
'Door ' || level AS location_name,
CASE round(dbms_random.value(1,3))
WHEN 1 THEN 'A'
WHEN 2 THEN 'T'
WHEN 3 THEN 'T'
END AS location_type
FROM dual
CONNECT BY level <= 5;
5 rows affected
hidden batch(es)
create table access_history(
seq_num integer GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
employee_id NUMBER(6),
card_num varchar2(10),
location_id number(4),
access_date date,
processed NUMBER(1) default 0
);
✓
hidden batch(es)
INSERT INTO access_history(
employee_id,
card_num,
location_id,
access_date
)
WITH rws AS (
SELECT 1,'J11111',2,TO_DATE('2021/08/15 08:30:25', 'YYYY/MM/DD HH24:MI:SS') FROM dual UNION ALL
SELECT 1,'J11111',3,TO_DATE('2021/08/15 18:30:35', 'YYYY/MM/DD HH24:MI:SS') FROM dual UNION ALL
SELECT 2,'E11111',2,TO_DATE('2021/08/15 11:20:35', 'YYYY/MM/DD HH24:MI:SS') FROM dual) SELECT * FROM rws;
3 rows affected
hidden batch(es)
CREATE PROCEDURE LAST_EMPLOYEE_HISTORY(
i_employee_id IN EMPLOYEES.EMPLOYEE_ID%TYPE,
i_rws IN PLS_INTEGER DEFAULT 20,
o_cursor OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN o_cursor FOR
SELECT e.employee_id,
e.first_name,
e.last_name,
e.card_num,
l.location_id,
l.location_name,
a.access_date
FROM employees e
INNER JOIN access_history a
ON a.employee_id = e.employee_id
INNER JOIN locations l
ON l.location_id = a.location_id
WHERE e.employee_id = i_employee_id
ORDER BY access_date DESC
FETCH FIRST i_rws ROWS ONLY;
END;
/
✓
hidden batch(es)
SELECT * FROM USER_ERRORS;
NAME
TYPE
SEQUENCE
LINE
POSITION
TEXT
ATTRIBUTE
MESSAGE_NUMBER
…
hidden batch(es)
DECLARE
cur SYS_REFCURSOR;
v_eid EMPLOYEES.EMPLOYEE_ID%TYPE;
v_fname EMPLOYEES.FIRST_NAME%TYPE;
v_lname EMPLOYEES.LAST_NAME%TYPE;
v_card EMPLOYEES.CARD_NUM%TYPE;
v_lid LOCATIONS.LOCATION_ID%TYPE;
v_loc LOCATIONS.LOCATION_NAME%TYPE;
v_dt ACCESS_HISTORY.ACCESS_DATE%TYPE;
BEGIN
LAST_EMPLOYEE_HISTORY(1, 1, cur);
LOOP
FETCH cur INTO v_eid, v_fname, v_lname, v_card, v_lid, v_loc, v_dt;
EXIT WHEN cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_fname || ' ' || v_lname || ': ' || v_loc || ' @ ' || v_dt);
END LOOP;
END;
/
1 rows affected
dbms_output:
John Doe: Door 3 @ 2021-08-15T18:30:35
