Note: Index article on some 12c features can be seen here
The with clause is more efficient to use than sub queries.
Instead of using a stored PL/SQL function, we can include the body of the function in the SQL query.
Benefit is that it will run faster than the call of a function by the same statement.
This can be better explained with an example.
Lets create a table with some test data.
Look at the usage of the function using with - in the body of the query.
The function in the with clause over-rides the function created at schema level. In below screenshot, you can see the schema level function returning SYSDATE+1 and the with clause function returning SYSDATE+2. Check the output - its SYSDATE+2.
Note: You need to put in a slash (/) at the end else SQLPLUS waits for more input (refer lines 9-12 in the snapshot waiting when ENTER key was pressed).
The with clause is more efficient to use than sub queries.
Instead of using a stored PL/SQL function, we can include the body of the function in the SQL query.
Benefit is that it will run faster than the call of a function by the same statement.
This can be better explained with an example.
Lets create a table with some test data.
Look at the usage of the function using with - in the body of the query.
The function in the with clause over-rides the function created at schema level. In below screenshot, you can see the schema level function returning SYSDATE+1 and the with clause function returning SYSDATE+2. Check the output - its SYSDATE+2.
Note: You need to put in a slash (/) at the end else SQLPLUS waits for more input (refer lines 9-12 in the snapshot waiting when ENTER key was pressed).
No comments:
Post a Comment