Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Tony.

Asked: September 22, 2002 - 6:10 pm UTC

Last updated: September 09, 2005 - 3:19 pm UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

Tom,
There are cases in which SQL and PL/SQL both can do the job, but with more complexity in SQL then in PL/SQL. Examples could be DECODE/CASE statement in SQL VS nested IF statements in PL/SQL. Some people are so comfortable in SQL with NO experience in PL/SQL start to think on SQL way, and other group start talking of maintenance/enhancement through PL/SQL way with their level of comforts in PL/SQL. What are your suggestions in terms of performance or otherwise?
Thanks
Tony

and Tom said...

My mantra:

if you can do it in a single sql statement -- do it.

if not, try a little plsql

if that doesn't cut it, add a tiny bit of java to help it out

if that doesn't work (too slow, too whatever), a c based extproc works nicely.


Hence, if I can do it in SQL, I do it in SQL. PLSQL is always the second choice.

Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Tony, September 23, 2002 - 1:27 am UTC

Thanks for your views and quick response.

move embedded sqls to sp/functions (v8.1.5)

Ram, December 05, 2002 - 3:56 am UTC

in a c/s app (powerbuilder/oracle) we have some processes which read a .txt file (n number of records). each record could be an Add(A)/Delete(D) or Update (U).
each record potentially affects multiple tables ie A/D/U to x number of tables. i find that all the sqls to do this has been embedded within PB. so there will be x number of trips per record (totalling x & n trips) between the client and server. and some processes run for about 6 hrs !

1)won't a proc/function that does this in the db be more efficient ? <your opinion about this would be most helpful.>
2) its quite difficult to maintain code that is a total mix of pb code and sqls.
3) how to determine the approximate benefit in performance after moving the code to plsql. now i know the current time taken is about 6 hours. but how to 'guess' that after putting the code in modules of sp's/functions the process will take x time ?

i am sure it will be a better approach but my being 'sure' doesnt help me. i need to show & prove that it will run so much times faster. and for that i need to develop it.
any idea how to pre-judge it ?

thank you.


Tom Kyte
December 05, 2002 - 7:54 am UTC

1) i would think so. I would have PB load the records into a scratch (global temporary table) as fast as it can and then just run a stored procedure to do the modifications IN BULK -- no row at a time processing, mass updates, inserts and deletes. three statement/table MAX here.

2) agreed. especially since that CS PB app is heading on its way out and now you have the job of ripping it apart to discover what it does. wouldn't it be nice if it was just in the database? waiting and ready to be reused? programming paradigms change like fashion -- every spring... Data, well, it pretty much stays the same.

3) you cannot. It can be determined by a process known as benchmarking.


You can set up a simple simulation. You don't have to fully develop it. Just show that moving the change records into the database, running 3 statements instead of hundreds/thousands is much faster. Done.




plsql - ok. but bulks ?

Ram, December 05, 2002 - 9:25 am UTC

ok. almost what i was thinking of. (more than half way through your book + this site and i guess i have started thinking on the right lines :))

but i doubt whether i will be able to use the BULK feature. reason: then input values (for each column) needs to be validated, sometimes cross checking against the db. moreover, inserting/updating/deleting from n tables for 1 particular record makes up a transaction unit.

"ripping apart the code to see what it does"...hmmm.
you might find this hard to believe ( or maybe you have seen much "better" coding ) but in one place i actually saw code like this:
if variable > 0 then
if variable = 0 then (=> whatever can cause this to happen is beyond my understanding ! )
.....
end if ;
end if ;


Tom Kyte
December 05, 2002 - 11:18 am UTC

cross checking against the DB -- sounds suspiciously like a foreign key to me?

validated -- check constraints are really handy for that

then you can use bulk features. Also, no reason you cannot

o load plsql table
o validate data
o bulk insert

is there?

programming apporach

A reader, December 20, 2002 - 1:32 pm UTC

Hi tom,

I wrote a stored procedure as follows...


create or replace sp
( p_message out varchar2,p_status out varchar2)
as

var1 varchar2(20);
var2 number;
var3 varchar2(30);
begin
select id_val into var2 from t;

begin
select id_name int var1 from t2
where id_val = var2;

exception
when no_data_found then
-- dbms_output.put_line('we need to insert...');
RAISE;
when too_many_rows then
-- dbms_output.put_line('we need to insert...');
null;
when others then
-- dbms_output.put_line('trap the error ...');
p_message := sqlerrm;
p_status := 'F';
RAISE;
end;
/****************************/
begin
select username into var3 from user;
raise too_many_rows;
exception
when no_data_found then
--dbms_output.put_line('insert again..');
RAISE;
when too_many_rows
-- dbms_output.put_line('row already exists');
null;
when others then
-- dbms_output.put_line('trap the error ...');
p_message := sqlerrm;
p_status := 'F';
RAISE;
end;

exception
when no_data_found then
my_pkg.insert_t(var1,var2,p_status);
when others then
-- dbms_output.put_line('trap the error ...');
p_status := 'F';
insert into error_table(err.nextval,p_status,p_message);

end sp;

/*****************************************************/

do you think the way this script is written is not good ?
is the script writer uses the exception as the " label " ??

like "go to " statements in c or other languages ?

if it is bad what would be the right approach ?


Thanks,

Tom Kyte
December 20, 2002 - 1:55 pm UTC

I think it is non-sensical -- i don't see the purpose. I don't see the point of the raise too_many_rows with a NULL exception block.

I think a when others that is not followed by a RAISE is almost always a bug (in your case, it is a bug -- I hate "send back a status" coding - just let the exception propagate up to a level where someone can deal with it.)

If you want to find out whether you should insert or not, just:

begin
insert ....
exception
when dup_val_on_index then
null;
/* didn't need to insert */
end;



about the null in the exception

A reader, December 20, 2002 - 2:44 pm UTC

Tom,

I have audit trail implemented on the table t and users
so when atleast one row exists we need to update(but because of audit trail we insert )
so in the above procedure whereever I am raising too_many_rows
it is update ( I just worte null; to illustrate)

but my question is the use of the exeception in above
procedure is like label ? like go to stmt ?



Tom Kyte
December 20, 2002 - 3:01 pm UTC

i didn't like it -- sorry if that wasn't clear.

words of wisdom

brian e., February 12, 2003 - 5:29 pm UTC

this is exactly what our dba said.
it is, however, not what the instructor of the oracle class i'm taking said. i think this is because they said the problem was impossible to solve using plain sql, and it was not, and that made him cranky.

PL/SQL -/-> SQL ??

Duke Ganote, September 06, 2005 - 3:52 pm UTC

The original question claimed there was "more complexity in SQL than in PL/SQL. Examples could be DECODE/CASE statement in SQL VS nested IF statements in PL/SQL." I'm still attempting to fathom that.

Here's one of the more challenging PL/SQL code snippets I inherited (which I've abbreviated and annotated):

v_protection_class number;
CURSOR c is SELECT pnf.protection_class, pnf.location_code
','||pnf.Risk_Codes_1||','...
pnf.Risk_Codes_20||',' -- YES, CONCATENTATED!
as Risk_Code_ALL
from SOURCE_TABLE pnf;
FOR recSourceRecord IN c LOOP
v_protection_class := rec.protection_class;
IF v_Protection_Class IS NULL THEN
BEGIN
SELECT protection_class INTO v_Protection_Class
FROM risk_code_tbl
WHERE recSourceRecord.Risk_Code_ALL LIKE '%,'||risk_code||',%'
EXCEPTION
WHEN OTHERS THEN -- ARG!!!
v_Protection_Class := NULL;
END;
END IF;
IF v_Protection_Class IS NULL THEN
BEGIN
SELECT protection_class INTO v_Protection_Class
FROM location_code_tbl
WHERE location_code = trim(recSourceRecord.location_code)
AND protection_class is not null;
EXCEPTION
WHEN OTHERS THEN
v_Protection_Class := NULL;
END;
END LOOP;

Data profiling found that the subqueries sometimes returned multiple (identical) rows, which threw an exception so the result was incorrectly set to NULL! After some evaluation, the PL/SQL was re-written into SQL with explicitly nested conditions:

SELECT ( CASE WHEN pnf.Protection_Class IS NOT NULL THEN pnf.Protection_class
ELSE CASE WHEN ( SELECT r.protection_class
FROM risk_code_tbl R
WHERE R.line_of_business = pnf.LINE_OF_BUSINESS
AND R.risk_code in (
pnf.risk_codes_1,pnf.risk_codes_2,...
, pnf.risk_codes_19,pnf.risk_codes_20 )
AND rownum = 1 ) IS NOT NULL
THEN ( SELECT r.protection_class
FROM risk_code_tbl R
WHERE R.line_of_business = pnf.LINE_OF_BUSINESS
AND R.risk_code in (
pnf.risk_codes_1,pnf.risk_codes_2,...
, pnf.risk_codes_19,pnf.risk_codes_20 )
AND rownum = 1 )
ELSE (
SELECT TO_CHAR(L.protection_class)
FROM location_code_tbl L
WHERE L.location_code = trim(pnf.location_code)
AND L.protection_class IS NOT NULL
)
END
END ) AS protection_class
FROM source_table pnf;

Of course, this could be revised to be shorter and mildly more cryptic to procedural thinkers:

SELECT ( COALESCE( pnf.Protection_Class
, ( SELECT r.protection_class
FROM risk_code_tbl R
WHERE R.line_of_business = pnf.LINE_OF_BUSINESS
AND R.risk_code in (
pnf.risk_codes_1,pnf.risk_codes_2...
, pnf.risk_codes_19,pnf.risk_codes_20 )
AND rownum = 1 ) , (
, ( SELECT TO_CHAR(L.protection_class)
FROM location_code_tbl L
WHERE L.location_code = trim(pnf.location_code)
AND L.protection_class IS NOT NULL
)
) AS protection_class
FROM source_table pnf;

Do you know of data-oriented procedural code that cannot be rendered declaratively? The era before analytic functions provided some, I'm sure, but nowdays?

Tom Kyte
September 06, 2005 - 9:12 pm UTC

this is a concept I bring up in some of my seminars. I show a piece of procedural code

Then I write out what it does.

Then I explain how the customer I was benchmarking with said "it doesn't do that"

And I have to show them it did.

Then they realized they had a bug.

But that procedural code -- so easy to read, much easier than my equivalent SINGLE SQL statement (that worked with a runtime 3-6 hours less than their procedural code, so it ran for like 30 minutes.....)

it was wrong.

without COMMENTS and an ALGORITHM (psuedo code, remember that??? I still do that myself on paper many times) all code in any language is just bits and bytes on the screen.....

I'm sure there are procedural algorithms that (should|could) not be done in SQL (model clause and all....)

But many procedural algorithms should die an instant death and be done in a single sql statement.

If the world spent a fraction of the time learning databases and sql they seem to learning "a language", "a framework", data integrity would go way way way up...

(and remember, applications come, they go, they are transient, they could be erased -- but don't you DARE lose my data)

modular SQL

Duke Ganote, September 09, 2005 - 3:15 pm UTC

Of course, I concur: "many procedural algorithms should die an instant death and be done in a single sql statement" :))

With the advent of subquery factoring (named subqueries), see </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:8120272301765#44118532883998 <code>I'm very pleased with how modular SQL can be!

BTW: When did you first uncover the mantra ?

Tom Kyte
September 09, 2005 - 3:19 pm UTC

1992 or thereabouts.

After finally figuring out for myself that any reasonably complex system that is to be database independent won't be.... Took me 5 years to figure that out - only thing that made it clear was a sufficient level of knowledge of the databases I was working with.