The SYSDATE function is available in both SQL and PL/SQL. They both return the current date-time (down to nearest second) for the database, So it would be reasonable to assume that they "act" the same in both SQL statements and PL/SQL blocks.
That would, however, be a bad assumption to make, because in reality:
In SQL, SYSDATE is called just once for the entire statement.
In PL/SQL, SYSDATE is called every time it is invoked.
Wow. Mind blown.
Let's take a look.
In the script below, I create a table and insert four rows. Then I create a package that keeps track of distinct dates added to a collection and show those dates. Finally, a function that uses the package.
When I call the store_and_return_date, I can specify whether or not I want SYSDATE to be called again from within the PL/SQL function - which will be invoked below inside a SELECT statement.
CREATE TABLE tab (id INT) / BEGIN INSERT INTO tab VALUES (1); INSERT INTO tab VALUES (2); INSERT INTO tab VALUES (3); INSERT INTO tab VALUES (4); COMMIT; END; / CREATE OR REPLACE PACKAGE tracker AUTHID DEFINER IS TYPE when_t IS TABLE OF INTEGER INDEX BY VARCHAR2 (100); dates when_t; PROCEDURE add_date (date_in IN DATE); FUNCTION store_and_return_date (date_in IN DATE, call_in_plsql_in IN VARCHAR2) RETURN DATE; PROCEDURE show_count; END; / CREATE OR REPLACE PACKAGE BODY tracker IS PROCEDURE add_date (date_in IN DATE) IS BEGIN dates (TO_CHAR (date_in, 'YYYY-MM-DD HH:MI:SS')) := 0; END; PROCEDURE show_count IS l_index VARCHAR2 (100) := dates.FIRST; BEGIN DBMS_OUTPUT.put_line ('Date Count = ' || dates.COUNT); WHILE l_index IS NOT NULL LOOP DBMS_OUTPUT.put_line (l_index); l_index := dates.NEXT (l_index); END LOOP; dates.delete; END; FUNCTION store_and_return_date (date_in IN DATE, call_in_plsql_in IN VARCHAR2) RETURN DATE IS d DATE; BEGIN /* This is the value for SYSDATE passed in from The SELECT */ add_date (date_in); IF call_in_plsql_in = 'YES' THEN /* Now we call SYSDATE inside PL/SQL. Will it be the same value as passed in for date_in, or different? The count in the collection will tell us */ add_date (SYSDATE); END IF; DBMS_SESSION.sleep (1); RETURN date_in; END; END; /
And here's the output:
SELECT tracker.store_and_return_date ( SYSDATE, call_in_plsql_in => 'NO') FROM tab / BEGIN tracker.show_count; END; / SELECT tracker.store_and_return_date ( SYSDATE, call_in_plsql_in => 'YES') FROM tab / BEGIN tracker.show_count; END; /
Date Count = 1 2020-09-29 01:28:58 Date Count = 4 2020-09-29 01:29:02 2020-09-29 01:29:03 2020-09-29 01:29:04 2020-09-29 01:29:05
And there you see the difference between SQL and PL/SQL engines when it comes to executing SYSDATE. There are four rows in the TAB table. But the value for SYSDATE when executed in SQL doesn't change for the duration of the query, so there is just one element in the array.
When I ask for SYSDATE to also be called in PL/SQL, it is executed and the current date-time returned - even when that function is called from within a SQL statement.