Embedded Procedure

You can put a print procedure in your code, and call that instead of typing DBMS_Output.Put_Line repeatedly.

The Code:

Declare vText Varchar2(20) := 'Test Text'; Procedure pDOPL ( P_String in VARCHAR2 ) is Begin DBMS_OUTPUT.put_line(p_String); End; Begin pDOPL ('Stmt# 1'); pDOPL ('Stmt# 2'); pDOPL (vText); End; /

The Output:

 
 
Stmt# 1
Stmt# 2
Test Text
 
 

DBMS_Output.Put_Line in a Procedure

I get tired of typing 'DBMS_Output.Put_Line,' and unless this turns out to be a big time-waster, I think I'll just call the pPrint procedure:

 
 
pPrint.SQL
 
 
 
Create Or Replace
  Procedure pPrint (Print_String In Varchar2)
  Is
  Begin
    Dbms_Output.Put_Line(Print_String);
  End;
/
 
 

This calls the pPrint procedure:

 
 
Begin
    pPrint ('apple');
End;
/
 
 

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

Procedures (vs. Functions)

Procedures perform a process.

Functions return values.

Create Table Accounts
    ( Acct  Number(5)
    , Amt   Number(8,2)
    );
    
Create Or Replace 
Procedure Cr_Acct         /* Spec */
      ( Acct In Integer,  
        Amt  In Real)     
As
Begin                     /* Body */
   Insert Into Accounts 
       Values (Acct, Amt);  
End Cr_Acct;              
/
Select *
  From User_Source
 Where Name = 'CR_ACCT';Declare
   Acct_Id Number :=  &Id;
   Amt     Number :=  &Deposit;
Begin
   Cr_Acct(Acct_Id, Amt);
End;
/
Select * From Accounts;