Showing posts with label Anonymous Block. Show all posts
Showing posts with label Anonymous Block. Show all posts

Saturday, March 14, 2009

Anonymous Block vs Procedure with Parameters

/*---------------------------------------------------*/
/* Example 1: Create an anonymous block */
/*---------------------------------------------------*/

Declare
vShopList Varchar2(25) := 'Apples';
Begin
Dbms_Output.Put_Line ('Buy more '||vShopList);
End;
/
Buy more Apples

/*---------------------------------------------------*/
/* Example 2: Change to a procedure and execute */
/*---------------------------------------------------*/

Create or Replace Procedure pShopList
is
vShopList Varchar2(25) := 'Apples';
Begin
Dbms_Output.Put_Line ('Buy more '||vShopList);
End;
/

Procedure created.

/*-----------------------------------------------*/
/* Run procedure */
/*-----------------------------------------------*/

Begin
pShopList;
End;
/
Buy more Apples

/*---------------------------------------------------*/
/* 3. Change procedure to accept parameter */
/*---------------------------------------------------*/

Create or Replace Procedure pShopList
(vShopList Varchar2)
is
Begin
Dbms_Output.Put_Line ('Buy more '||vShopList);
End;
/

Procedure created.

/*-----------------------------------------------*/
/* Run procedure with input parameter */
/*-----------------------------------------------*/

Begin
pShopList ('Bananas');
End;
/
Buy more Bananas

Monday, March 2, 2009

Labeled Block

An Anonymous Block

Declare
v_Ctr Number(3) := 1;
Begin
Dbms_Output.Put_Line(v_Ctr);
End;
/

A Named Block

<<l_Named_Block>>
Declare
v_Ctr Number(3) := 1;
Begin
Dbms_Output.Put_Line(v_Ctr);
End l_Named_Block;
/