PLSQL - Improving code efficiency

Question

This question is regarding PLSQL - for improving the efficiency of the code and coding standards.

Any help, pointers, references or suggestions are highly appreciated.

Question:

I have a plsql procedure with an INPUT parameter i_flag which is of type BOOLEAN.

Based upon the value of this i_flag( which can be either true or false) I have to execute a sql query. If the value is TRUE then SQL1 (Assume query 1.1) else if the value is FALSE SQL2 (Assume query 1.2) would be executed.

SQL2 is same as SQL1 except an addition of where clause.

SQL1 (1.1)

select a.user_id, a.user_name, a.dept_id, b.country from user a , contact b
where a.user_id = b.user_id;

SQL1 (1.2)

select a.user_id, a.user_name, a.dept_id, b.country from user a , contact b
where a.user_id = b.user_id
and a.user_status is not null;

Instead of writing IF-ELSE in plsql is it possible to write this query in a single SQL query?

`which can be either true or false` Yes, that is usually how boolean works ;) — Juan Carlos Oropeza, Sep 25 '15 at 17:44
@JuanCarlosOropeza Could be null also, of course. It would be an abomination, but ... — David Aldridge, Sep 27 '15 at 09:41
`i_flag` doesn't sound like the most self-documenting parameter name. — William Robertson, Nov 11 '15 at 16:56

Mureinik · Accepted Answer · 2015-09-25T18:46:43.497

3

You can create the same behavior in a single query using the logical or operator:

select a.user_id, a.user_name, a.dept_id, b.country 
from user a , contact b
where a.user_id = b.user_id AND (i_flag = TRUE OR a.user_status IS NOT NULL)

Note, by the way, that implicit joins (having two tables in the from clause) is a deprecated syntax, and it's recommended to switch to the modern, explicit, syntax:

SELECT a.user_id, a.user_name, a.dept_id, b.country 
FROM   user a
JOIN   contact b ON a.user_id = b.user_id
where  i_flag = TRUE OR a.user_status IS NOT NULL

edited Sep 25 '15 at 18:46

answered Sep 25 '15 at 17:19

Mureinik

297,002
52
306
350

6

This won't work because `boolean` is not a valid SQL data type. It's only valid in PL/SQL. You'd need to convert the boolean parameter to a local variable that is a SQL type such as a `varchar2(1)` that is either Y or N. You could then use that local variable in your query. – Justin Cave Sep 25 '15 at 18:49

score 0 · Answer 2 · answered Sep 25 '15 at 22:56

First of all, if you want to use a boolean param in a SQL query, you have to substitute a sql-compatible type such as NUMBER, i.e. use 0 or 1 instead of FALSE or TRUE.

Second, while Mureinik's answer based on OR will work, Oracle will often give better performance if you use an alternative. One alternative is like this:

SELECT a.user_id, a.user_name, a.dept_id, b.country 
FROM   user a
JOIN   contact b ON a.user_id = b.user_id
WHERE  1 = CASE WHEN i_flag = 1 THEN 1
                WHEN a.user_status IS NOT NULL THEN 1
                ELSE 0 END

It ain't pretty but in larger queries it sometimes helps significantly.

score 0 · Answer 3 · answered Sep 26 '15 at 09:47

Syntax-wise, all of the above answers are great. My two-cents, however, refer to a different alteration that might help your performance. The biggest bottleneck when running PL/SQL procs is seen when bouncing between the PL/SQL and the SQL engine (which are seperated by ORACLE's architecture). Therefore, the rule of thumb is to minimize the amount of individual calls to each procedure.

If we apply this rule to your question - you should check if its possible to pass a refcursor to the procedure, that contains all of the users to be queried, and their appropriate boolean value. If the procedure is now called from within a loop for a batch of users, doing so will greatly aid performance when you'll scale to large numbers.

score 0 · Answer 4 · answered Sep 26 '15 at 13:58

0

I think writing dynamic sql for this will be good choice.

create or replace procedure sp_..(i_flag boolean) 
as 
 sql_stmt varchar2(1000):='select a.user_id, a.user_name,'|| 
 'a.dept_id,b.country from user a , contact b '||
 'where a.user_id = b.user_id';
begin 
 if (i_flag=false) then 
   sql_stmt:=sql_stmt||' and a.user_status is not null';
end if;
 ---do some stuff with sql_stmt(i.e. refcursor)
end sp_..;

answered Sep 26 '15 at 13:58

Somnath Bandopadhyay

66
4

1

IMO useless use of dynamic SQL. YMMV. – user272735 Sep 28 '15 at 13:13
would you prefer using dbms_sql @user272735? – Somnath Bandopadhyay Sep 28 '15 at 13:29
I don't need to provide more examples as the accepted answer by @Mureinik is the (correct) solution. Dynamic SQL is always suboptimal if the same functionality can be made with static SQL. – user272735 Sep 30 '15 at 05:15
Hope you have ever tried with any input parameter (where i_flag=true) Since sql engine does not parse any non sql argment. – Somnath Bandopadhyay Oct 01 '15 at 09:52
@Justin Cave already said that...I missed it...You have to rely on dbms_sql as static sql cant do that – Somnath Bandopadhyay Oct 01 '15 at 09:54
I'm sorry but you're wrong. You can convert the boolean parameter to varchar2 in PL/SQL and then use the converted value in the static SQL. Pseudocode example: `v_flag constant varchar2(1) := case i_flag when true then 'Y' else 'N' end;` It's just a pitty @Mureinik hadn't bothered to fix his answer. – user272735 Oct 01 '15 at 19:21
Sorry to make such lengthy conversation.Thanks for your answer. – Somnath Bandopadhyay Oct 02 '15 at 07:12

score 0 · Answer 5 · answered Oct 14 '15 at 13:36

Even if we would be able to implement the or condition inside single query, it might be not so good idea in terms of performance and dynamic sql could be a better choice. If you are looking to improve code efficiency you may want to look at similar to https://www.youtube.com/watch?v=rWEqO-GpJ4M

PLSQL - Improving code efficiency

5 Answers5