In a recent interview the interviewer asked:
"Would you write a simple SQL query OR Write that SQL query in PL/SQL as a stored procedure."
My question is, what's the difference, and when should each be used?
-
Could be looking for you to recognize different keywords in Postgres, or it could be testing if you know what a stored procedure is.Tim Biegeleisen– Tim Biegeleisen2016-08-04 12:55:42 +00:00Commented Aug 4, 2016 at 12:55
Add a comment
|
3 Answers
SQL query is usually faster than PL/SQL query, beacuse Oracle dont have to change engine to execute.
To understand this you need to know about context switching. When you write a simple sql, only Oracle SQL engine plays. But when you write a sql statement in a PL/SQL, SQL engine makes a context switch to PL/SQL engine and hence the statement is executed. The more is the context switching the more bad will be the performance. Check below link and you may google more on context switching .
http://www.oracle.com/technetwork/issue-archive/2015/15-jul/o45plsql-2543984.html
Comments
Essentially there is no difference, since the SQL engine executes the query regardless of the host language - it could be COBOL for all it cares.
There is a slight overhead of the extra component (you call the procedure and it executes the SQL, instead of you just executing the SQL directly), however that step can normally be measured in microseconds. (And as soon as the procedure does more than one thing - two queries, for example - then the advantage is back with the procedure.)
There are permissions implications, since definer-rights stored PL/SQL can not see roles while standalone SQL can.
