Wednesday, March 23, 2011

Product Suggestion: Nested Internal Procedures

SQL Anywhere already has nested BEGIN blocks, complete with variable scope rules, local variables, even local EXCEPTION handling. What it needs is an extension to the BEGIN statement to define nested internal PROCEDURE blocks with CALL, RETURN and parameter passing.

A local variable defined at the front of a BEGIN block is created when the block is entered and destroyed when the block terminates. The scope of a local variable includes all the executable statements in the same BEGIN block, as well as all statements in any nested BEGIN block. - page 262, SQL Anywhere Studio 9 Developer's Guide

Before...

Here's an excerpt from a large stored procedure that mimics "calls" to local sections of code by using Y/N variables and IF statements. All the code is local to one single CREATE PROCEDURE because of the large number of local variables (over 200, not shown) that are shared across the different sections; using a separate CREATE PROCEDURE for each section of code would require thousands of lines of additional code to implement parameter passing.

CREATE PROCEDURE rroad_monitor_options (
...
BEGIN
DECLARE @do_task_load_criteria_into_@variables VARCHAR ( 1 );
DECLARE @do_task_copy_inputable_@variables_into_@old_variables VARCHAR ( 1 );
DECLARE @do_task_load_factory_settings_into_inputable_@variables VARCHAR ( 1 );
DECLARE @do_task_cancel_alerts_as_required VARCHAR ( 1 );
DECLARE @do_task_replace_entire_target_row_with_@variables VARCHAR ( 1 );
...
SET @do_task_load_criteria_into_@variables = 'N';
SET @do_task_copy_inputable_@variables_into_@old_variables = 'N';
SET @do_task_load_factory_settings_into_inputable_@variables = 'N';
SET @do_task_cancel_alerts_as_required = 'N';
SET @do_task_replace_entire_target_row_with_@variables = 'N';
...
WHEN @submit_form = 'Show Criteria' THEN
SET @do_task_load_criteria_into_@variables = 'Y';
SET @do_task_copy_inputable_@variables_into_@old_variables = 'Y';

WHEN @submit_form = 'Switch Display' THEN
SET @do_task_load_criteria_into_@variables = 'Y';
SET @do_task_copy_inputable_@variables_into_@old_variables = 'Y';

WHEN @submit_form = 'Restore Factory Settings' THEN
SET @do_task_load_criteria_into_@variables = 'Y';
SET @do_task_copy_inputable_@variables_into_@old_variables = 'Y';
SET @do_task_load_factory_settings_into_inputable_@variables = 'Y';
SET @do_task_cancel_alerts_as_required = 'Y';
SET @do_task_replace_entire_target_row_with_@variables = 'Y';
...

IF @do_task_load_criteria_into_@variables = 'Y' THEN
...
END IF;

IF @do_task_copy_inputable_@variables_into_@old_variables = 'Y' THEN
...
END IF;

IF @do_task_load_factory_settings_into_inputable_@variables = 'Y' THEN
...
END IF;

IF @do_task_cancel_alerts_as_required = 'Y' THEN
...
END IF;

IF @do_task_replace_entire_target_row_with_@variables = 'Y' THEN
...
END IF;
...

...and After

Here's what that excerpt might look like with the crude SET and IF statements changed to CALL and INTERNAL PROCEDURE statements:

CREATE PROCEDURE rroad_monitor_options (
...
BEGIN
...
WHEN @submit_form = 'Show Criteria' THEN
CALL load_criteria_into_@variables();
CALL copy_inputable_@variables_into_@old_variables();

WHEN @submit_form = 'Switch Display' THEN
CALL load_criteria_into_@variables();
CALL copy_inputable_@variables_into_@old_variables();

WHEN @submit_form = 'Restore Factory Settings' THEN
CALL load_criteria_into_@variables();
CALL copy_inputable_@variables_into_@old_variables();
CALL load_factory_settings_into_inputable_@variables();
CALL cancel_alerts_as_required();
CALL replace_entire_target_row_with_@variables();
...

INTERNAL PROCEDURE load_criteria_into_@variables()
BEGIN
...
END;

INTERNAL PROCEDURE copy_inputable_@variables_into_@old_variables()
BEGIN
...
END;

INTERNAL PROCEDURE load_factory_settings_into_inputable_@variables()
BEGIN
...
END;

INTERNAL PROCEDURE cancel_alerts_as_required()
BEGIN
...
END;

INTERNAL PROCEDURE replace_entire_target_row_with_@variables()
BEGIN
...
END;
...

Since the main point behind an internal procedure is to take advantage of local variable scope rules (inheritance), I could live without parameter passing altogether... but here's what the full BNF might look like:
<internal_procedure>        ::= INTERNAL PROCEDURE <procedure_name>
"(" [ <parameter_list> ] ")"
[ RESULT "(" <result_set_column_list> ")" ]
<begin_block>

<parameter_list> ::= <parameter_specification> { "," <parameter_specification> }

<parameter_specification> ::= [ <parameter_disposition> ]
<parameter_name>
<data_type>
[ <parameter_default> ]

<parameter_disposition> ::= IN
| OUT
| INOUT -- default

<parameter_default> ::= DEFAULT <expression>

<result_set_column_list> ::= <result_set_column> { "," <result_set_column> }

<result_set_column> ::= <identifier> <data_type>

2 comments:

Anonymous said...

Internal procedures: Reminded me of the "inner procedures" in PASCAL-style languages - something that never made it in the C/C++ world:)

Volker

alexeyk77 said...

Not far from lambdas. SA can be first RDBMS which support functional programming paradigm.