A leftover perhaps.....
A reader, June 26, 2008 - 12:08 pm UTC
Something inherited from PL/1?
June 26, 2008 - 4:23 pm UTC
Ada...
Thanks
Steve, June 26, 2008 - 1:06 pm UTC
"Nested procedure", much better description, thanks for the example too.
When I first saw code last year using a nested procedure, I didn't think much about them other than I probably would never have a use. Now that I've recently seen more code using them and looked closer at how they were being used, I saw no benefit and only potential for trouble.
Thanks for confirming my suspicions.
Steve
Embedded Procedures/Functions Helpful in PL/SQL scripts.
Bob, June 26, 2008 - 2:07 pm UTC
I occassionally use an embedded procedure or function in a PL/SQL script.
It comes in handy if there is a lot of code in the procedure and the block or the code is repetitive.
SQL> SET SERVEROUTPUT ON SIZE 50000
SQL>
SQL> DECLARE
2
3 PROCEDURE P_PRINT( v_printme VARCHAR2 ) IS
4
5 BEGIN
6 dbms_output.put_line(v_printme);
7 END P_PRINT;
8
9 FUNCTION F_CALC_RATING( age NUMBER, weight NUMBER) return number IS
10
11 BEGIN
12 RETURN age * weight;
13 END F_CALC_RATING;
14
15 BEGIN
16
17 P_PRINT('HELLO World');
18
19 P_PRINT('Rating for Tom is ' || TO_CHAR(F_CALC_RATING(35, 175)));
20
21 P_PRINT('Rating for Joe is ' || TO_CHAR(F_CALC_RATING(27, 275)));
22
23 P_PRINT('Rating for John is ' || TO_CHAR(F_CALC_RATING(52, 185)));
24
25 P_PRINT('Goodbye, thank you for dropping by.');
26
27 END;
28 /
HELLO World
Rating for Tom is 6125
Rating for Joe is 7425
Rating for John is 9620
Goodbye, thank you for dropping by.
PL/SQL procedure successfully completed.
SQL>
June 26, 2008 - 4:36 pm UTC
Ok, yes, this I agree with IF you are not allowed to install code permanently.
And it just transported me back to version 6.
which had plsql
but no stored procedures
and the only way to write a legible block of code of any size (only anonymous blocks in embedded sql back then) was to do this.
So, yes, that particular example here is a "package before packages"
Nested procedures and even globals can be useful is used carefully
Ray DeBruyn, June 26, 2008 - 3:23 pm UTC
Hi Tom,
I do a lot of PL/SQL Web Toolkit pages. I may create a customer list, and edit form and a submit procedure. I'll create a package with these and that's all the package is used for.
I don't want to enter a ton of code, so I'll put enough for an idea of a submit procedure.
The main code has three executable lines, initialize, do_dml and do_redirect. The idea is to break down the tasks into small, easy to follow sets of code. I've gotten to follow coding standards that allow me to create a code generator or really more of a smart template.
PROCEDURE edit_submit (p_person_id IN data_element_users.id%TYPE
, z_chk IN NUMBER
, p_business_id IN bk_businesses.id%TYPE
, p_parameter_names IN names_arr DEFAULT empty_names_arr
, p_parameter_values IN values_arr DEFAULT empty_values_arr
, p_delete IN VARCHAR2 DEFAULT NULL )
IS
inserting BOOLEAN;
updating BOOLEAN;
deleting BOOLEAN;
PROCEDURE set_parameters
IS
BEGIN
NULL;
--{GENERATED_DML_SET_PARAMETERS} 21
FOR rowIndex IN 1..p_parameter_names.COUNT LOOP
IF p_parameter_names(rowIndex) = 'p_name' THEN
business_info.name := p_parameter_values(rowIndex);
END IF;
IF p_parameter_names(rowIndex) = 'p_primary_phone_number' THEN
business_info.primary_phone_number := p_parameter_values(rowIndex);
END IF;
IF p_parameter_names(rowIndex) = 'p_website_url' THEN
business_info.website_url := p_parameter_values(rowIndex);
END IF;
IF p_parameter_names(rowIndex) = 'p_business_status_id' THEN
business_info.business_status_id := p_parameter_values(rowIndex);
END IF;
IF p_parameter_names(rowIndex) = 'p_active_flag_yn' THEN
tennant_info.active_flag_yn := NVL(p_parameter_values(rowIndex), 'N');
END IF;
IF p_parameter_names(rowIndex) = 'p_primary_room_number' THEN
tennant_info.primary_room_number := p_parameter_values(rowIndex);
END IF;
END LOOP;
END set_parameters;
PROCEDURE initialize
IS
BEGIN
initialize_global(p_person_id, z_chk, p_business_id);
set_parameters;
IF is_eis_admin OR is_business_admin THEN NULL;
ELSE
error_message := error_message || '<FONT CLASS=ERROR>You do not have permission to view this page.</FONT><BR>';
END IF;
--DEVELOPER set boolean conditions
deleting := p_delete IS NOT NULL;
inserting := business_info.id IS NULL;
updating := business_info.id IS NOT NULL;
END initialize;
FUNCTION validate_insert
RETURN BOOLEAN
IS
v_return BOOLEAN;
BEGIN
v_return := TRUE;
--DEVELOPER define insert validation rules
IF 'CONDITION' = 'BAD CONDITION' THEN
error_message := error_message || '<FONT CLASS=ERROR>Validation failed on ...</FONT><BR>';
v_return := FALSE;
END IF;
RETURN v_return;
END validate_insert;
PROCEDURE do_insert
IS
BEGIN
IF validate_insert THEN
SELECT seq_bk_tables.NEXTVAL
INTO business_info.id
FROM DUAL;
INSERT INTO bk_businesses VALUES business_info;
tennant_info.building_id := building_info.id;
tennant_info.business_id := business_info.id;
INSERT INTO bk_tennants VALUES tennant_info;
END IF;
EXCEPTION
WHEN OTHERS THEN
error_message := error_message || '<FONT CLASS=ERROR>Failed to insert due to the following unexpected error: ' || SQLERRM || '</FONT><BR>';
business_info.id := NULL;
END do_insert;
FUNCTION validate_update
RETURN BOOLEAN
IS
v_return BOOLEAN;
BEGIN
v_return := TRUE;
--DEVELOPER define update validation rules
IF 'CONDITION' = 'BAD CONDITION' THEN
error_message := error_message || '<FONT CLASS=ERROR>Validation failed on ...</FONT><BR>';
v_return := FALSE;
END IF;
RETURN v_return;
END validate_update;
PROCEDURE do_update
IS
BEGIN
IF validate_update THEN
UPDATE bk_businesses
SET ROW = business_info
WHERE id = business_info.id;
UPDATE bk_tennants
SET ROW = tennant_info
WHERE business_id = tennant_info.business_id
AND building_id = tennant_info.building_id;
END IF;
EXCEPTION
WHEN OTHERS THEN
error_message := error_message || '<FONT CLASS=ERROR>Failed to update due to the following unexpected error: ' || SQLERRM || '</FONT><BR>';
END do_update;
FUNCTION validate_delete
RETURN BOOLEAN
IS
v_return BOOLEAN;
BEGIN
v_return := TRUE;
--DEVELOPER define delete validation rules
IF is_eis_admin THEN NULL;
ELSE
error_message := error_message || '<FONT CLASS=ERROR>You do not have permission to delete businesses.</FONT><BR>';
v_return := FALSE;
END IF;
RETURN v_return;
END validate_delete;
PROCEDURE do_delete
IS
BEGIN
IF validate_delete THEN
DELETE bk_businesses
WHERE id = business_info.id;
business_info.id := NULL;
END IF;
EXCEPTION
WHEN OTHERS THEN
error_message := error_message || '<FONT CLASS=ERROR>Failed to delete due to the following unexpected error: ' || SQLERRM || '</FONT><BR>';
END do_delete;
PROCEDURE do_dml
IS
BEGIN
IF error_message IS NULL THEN
IF deleting THEN
do_delete;
ELSIF updating THEN
do_update;
ELSIF inserting THEN
do_insert;
END IF;
END IF;
END do_dml;
PROCEDURE do_redirect
IS
BEGIN
error_message := NVL(error_message, '<FONT CLASS=SUCCESS>Success</FONT><BR>');
error_message := REPLACE(error_message, ' ', '%20');
--DEVELOPER add redirect parameters
owa_util.redirect_url('p_business.edit_form'
|| '?p_person_id=' || p_person_id
|| '&z_chk=' || z_chk
|| '&p_business_id=' || business_info.id
|| '&p_message=' || error_message);
END do_redirect;
BEGIN
initialize;
do_dml;
do_redirect;
END edit_submit;
June 26, 2008 - 4:38 pm UTC
one word for you:
packages
use them. and you do not even want to know what I think of:
EXCEPTION
WHEN OTHERS THEN
error_message := error_message || '<FONT CLASS=ERROR>Failed
to delete due to the following unexpected error: ' || SQLERRM || '</FONT><BR>';
END do_delete;
but, suffice to say, it is not good.
Follow on from last post
Ray DeBruyn, June 26, 2008 - 3:29 pm UTC
I had too much in the last post and I just want to add this. The advantage of nesting the procedures is that I can define more parameters through p_parameter_names and p_parameter_values that immediately become available. I don't need to modify code to change the parameter list as new ones need to be added.
I also do application level security and there is data I need for all procedures in this package, so I create an initialize_global procedure that populates package global variables for me.
June 26, 2008 - 4:43 pm UTC
I don't see that as an advantage. I don't mind having formal parameter lists
In fact, I demand it. A change that adds a parameter is a non-insignificant change, it takes review. Nothing better than a type safe formal named parameter.
sorry, but I am not a fan of this particular coding style myself.
and, it should be in a package, the nested procedures provide nothing a package couldn't provide "better"
Ray DeBruyn, June 26, 2008 - 5:38 pm UTC
This was a piece of code from a package. I thought I was adding too much to begin with, so I just chose the one example piece.
One big difference here is that this is the client code. This is the one place where I need to handle all exceptions including the unexpected ones. That being said, there's not any real effort in this snippet to find expected errors.
I like this style also for formatting - tables within tables etc. If the code starts to get too unruly, I just break it apart into smaller chunks. I put those into procedures and call them separately.
Also, I hate html that has incorrect open vs close tags. If I have a TABLE with a TR that has two TDs and each TD has a table, I'll code it as:
htp.p('<TD VALIGN=TOP>');
add_table;
htp.p('</TD>');
add_table will have all the TABLE, TR and TD tags for that table - no confusion.
June 26, 2008 - 5:40 pm UTC
actually, truth be told, for mod_plsql - I'd be very hard pressed to write code anymore.
http://apex.oracle.com/ It is been years since I've actually coded an "accept" routine by hand..
Perhaps a warning ?
Gary, June 26, 2008 - 7:32 pm UTC
"local nested procedure can see and modify outer procedures variables"
Perhaps you could petition Oracle development for a warning about this, similar to the 'WHEN OTHERS...' warning you got added.
Great link to Apex
Stew, June 26, 2008 - 11:00 pm UTC
I thought it was highly appropriate, and at the same time very funny because you just provided the link and not the product name. I've become a big fan of Apex in the last 6 months and just came back from the ODTUG conference where I met hundreds of like-minded programmers (plus some DBAs who looked a bit lost?). Thanks for the interesting and thoughtful keynote speech you made. I hope you got to enjoy the French Quarter some before you left! :-)
How call sub-sub-procedure
A reader, October 31, 2018 - 8:43 am UTC
any way to call p_inner() while it is a subprogram inside other ?
November 01, 2018 - 10:46 am UTC
If you declare a nested procedure, you can only call it within the scope of that declaration. So if you have a procedure within a procedure, you can only access it from the body of the parent procedure:
declare
procedure p ( p int ) as
procedure p_inner ( p int ) as
begin
dbms_output.put_line ( 'Value ' || p );
end p_inner;
begin
p_inner ( p );
end p;
begin
p ( 1 );
end;
/
Value 1
declare
procedure p ( p int ) as
procedure p_inner ( p int ) as
begin
dbms_output.put_line ( 'Value ' || p );
end p_inner;
begin
p_inner ( p );
end p;
begin
p_inner ( 1 );
end;
/
PLS-00201: identifier 'P_INNER' must be declared
Nesting in PL/SQL is for the birds.
lh, November 02, 2018 - 12:11 pm UTC
For Ada it was said that 'Nesting in Ada is for the birds'.