Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Wednesday, 6 April 2016

Nested Blocks in PL/SQL

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
/
Share this article :

2 comments:

  1. Halo,


    Zoooooooom! 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

    ReplyDelete
  2. Hello,


    11/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,

    ReplyDelete