A block within a block called as a Nested Block
The variable declared in any block is live as long as the block in which it is declared is getting executed.
In the Nested Block above, the variable X is declared in the outer block and hence it is available in the inner as well as the outer block however the variable Y is declared in the inner block hence it is available only to the inner block.
Now suppose the variable declared in the inner block is also given the same name as the variable in outer block say X then in the inner block when we refer to X it will be always taken as the local variable X and not X of outer block. This effect is called as “Local Screening”. In such situation if one wants to refer to the variable of the outer block then the outer block is given a label and in the inner block when one wants to refer to the outer block variable then it is prefixed with the outer block label.
Example: Labeled Nested Block
-- Outer Block Starts here
<<l_outer>> -- Label given to the outer block
DECLARE
v_empname VARCHAR2 (20) := 'Neeta';
v_salary NUMBER (4) := 1000;
BEGIN
DECLARE -- Inner Block Starts Here
v_empname VARCHAR2 (30) := 'Richa';
v_address VARCHAR2 (20) := 'Pune';
BEGIN
DBMS_OUTPUT.put_line(l_outer.v_empname);
DBMS_OUTPUT.put_line(v_empname);
END; -- Inner block ends
DBMS_OUTPUT.put_line(v_empname);
DBMS_OUTPUT.put_line(v_salary);
END; -- Outer Block Ends
/
Halo,
ReplyDeleteZoooooooom! That’s how speedy and easy this read was! Looking forward to more of such powerful content on Nested Blocks in PL/SQL!
I have data in a column
#abcd#efghi#jlmn#opqrs
I need to retrieve values between '#'. I need to get output as below
var1 = abcd
var2 = efghi
var3 = jlmn
var4 = opqrs
Similary if column value is
#abcd#efghi#jlmnopqrs
var1 = abcd
var2 = efghi
var3 = jlmnopqrs
var4 = null
But nice Article Mate! Great Information! Keep up the good work!
Thanks and Regards
Hello,
ReplyDelete11/10!! Your blog is such a complete read. I like your approach with. Clearly, you wrote it to make learning a cake walk for me.
how to find first nonspace character from end of the string
ex: 'abc def g '
I should get 4 in this case. (the first nonspace character from backwards in the above example is g and the position is 4 from backwards.
Could you help me in getting this?
I am so grateful for your blog. Really looking forward to read more.
Best Regards,
Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check oracleappstechnical.com
ReplyDeleteOracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.
ReplyDelete