Guidlines on scope of procedures/variables.
Kashif, November 04, 2002 - 6:28 pm UTC
Hi Tom,
Are there are any guidelines on the scope of procedures/variables i.e. when and where should one declare them? For example, when is declaring a global procedure/varaible optimal, and when is declaring a local procedure/variable optimal, assuming I have the choice of declaring them anywhere? Any performance considerations here? Is it true that global procedures/variables occupy more space in memory?
I have read in some texts that unless there is the need to declare variables/procedures, they should not be declared 'statically', but rather be declared conditionally, e.g.
if criteria_are_met then
declare
var1 varchar2 (10);
...
Is this a good practice?
Is declaring procedure A within procedure Z different from declaring procedure A outside procedure Z, in the same package? How?
Finally, if one has the option of declaring procedures/functions/variables in the package spec versus in the package body, where should they be declared?
TIA.
Kashif
November 05, 2002 - 8:58 am UTC
you only declare variables as global when they need to be -- their very usage dictates that they must persist from call to call.
Otherwise, they should be local.
Just like in any programming language. globals are to be avoided unless they are necessary.
The other thing -- about having multiple nested levels:
if criteria_are_met then
declare
var1 varchar2 (10);
...
is one I never have ascribed to for the simple reason that I never nest blocks like that in any language. Rather, I move that out into a subroutine (modularize the code). I like to keep my routines on a screen -- so I can see the TOP of the routine and the BOTTOM of the routine at a glance. Nested blocks would pretty much obviate that -- so I make them routines which pushes their variables out of the current routine all together.
declaring procedure a within procedure z limits the scope of A to Z. Take that block from above -- if it were not REUSABLE, no other routine would ever call it, you might consider putting it in Z to limit it's scope. Else, put it outside of Z to allow others to reuse it.
You only put in the spec that which OTHERS outside of the package could reasonably want to call. You publish those functions/procedures that you want exposed -- no more, no less.
is global really global
joachim Mayer, July 21, 2003 - 9:20 am UTC
Hi Tom,
thx for the help you are giving programmers out there. I saw this note on global variables from you but I am still confused because oracle seems to hide them somehow. I will show you my example
I create a simple package with contains a variable in the header-section. Should be global I belive.
mwstat@KXXL> create package my_pack as
2 var constant number := 3;
3 end;
4 /
Package created.
mwstat@KXXL> show error
No errors.
And I can use this value var in any other package or procedure like this
mwstat@KXXL> r
1 begin
2 dbms_output.put_line('var:'|| my_pack.var);
3* end;
var:3
PL/SQL procedure successfully completed.
But when I try to use my_pack.var in a view or a query I get an error
mwstat@KXXL> select * from transport_order where order_id = my_pack.var;
select * from transport_order where order_id = my_pack.var
*
ERROR at line 1:
ORA-06553: PLS-221: 'VAR' is not a procedure or is undefined
I was always in the opinion the variables defined in the header are global and global means anyone can see and use them. Does this exclude queries in oracle or where am I mistaken?
I have about 100 constants to work with and I had the plan to put then in a constant-package, in the header so all the other programmers may use them instead of using plain numbers which leads to problems quite often. Is this a good idea or would you counsel to do it different?
Thx in adavance
hannibal
July 21, 2003 - 3:21 pm UTC
It is GLOBAL in PL/SQL, "sql" cannot "see" it
Think of PLSQL as a layer on top of SQL. PLSQL can see sql, PLSQL can pass things to SQL, SQL knows not too much about PLSQL.
If the SQL is executed in the context of PLSQL,eg:
begin
for x in ( select * from transport_order where order_id = my_pack.var )
loop
....
it'll work just find, cause PLSQL will see my_pack.var and "bind" it for us.
You can either:
o do all sql in plsql (my response, it is what I do) and keep it in the package
o use an application context and have the programmers call a "init" function to warm up their session cache (an on-login trigger could do it as well) then you can
select * from t where order_id = sys_context( 'my_pack', 'var' );
o use a table and code:
select * from t where order_id =
( select value from lookups where variable = 'VAR' and rownum = 1 );
they are in the order of my preference.
i see ...
joachim mayer, July 22, 2003 - 1:33 am UTC
anything clear... thx tom.
hannibal
How about procedure encapsulation within a package?
Andy Hardy, August 10, 2005 - 1:16 pm UTC
Hi,
I write encapsulated procedures within my packages, e.g.
create or replace package blah as
procedure foo;
procedure foo2;
procedure foo3;
end;
create or replace package body blah
is
produre foo
is
procedure minifoo
is
begin
null;
end minifoo;
begin
null;
end foo;
produre foo2
is
procedure minifoo2
is
begin
null;
end minifoo2;
begin
null;
end foo2;
, etc...
(We use an IDE that prettifies the indentation, etc).
I find this useful in 'scoping' my procedures i.e. if a module only makes sense from the context of foo, then it's defined within foo.
However, some of the other developers say that they sometimes find it difficult to locate the 'real begin' - they know that they should be looking at foo, but there's a lot of stuff to work through before they hit the 'main' code.
Any thoughts on this?
August 11, 2005 - 8:39 am UTC
I'm not a personal fan of procedures in procedures.
A hidden procedure in a package, absolutely.
But a procedure in a procedure is a technique I rarely use.
I do think in general it makes it harder to read. The only time I use it is when I really want a standalone stored procedure (a utility designed to be called from a command line)