Oracle PL/SQL - Reusable PL/SQL Code

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Introduction
  4. Basics of PL/SQL Programs

Procedures

Procedures are wrapped code containers that usually represent some task.

They don't have to return anything to the main routine.

Functions

Functions serve as user-defined operators and return a value to the calling routine.

The only difference between procedures and functions is that functions return some value to the main routine.

Demo

SQL>
SQL> declare-- from  w  w w  . j  av a  2  s  .c  o  m
  2     v_tx VARCHAR2(50):= 'I just printed my <in> line!';--2
  3     procedure p_print
  4             (i_string_tx  in VARCHAR2,
  5                        i_replace_tx in VARCHAR2 := 'new')
  6                is
  7                begin
  8                     DBMS_OUTPUT.put_line(replace(i_string_tx,
  9                                                   '<in>', i_replace_tx));
 10                end;
 11  begin
 12     p_print (v_tx,'first');
 13     p_print (v_tx,'second');
 14     p_print (v_tx);
 15  end;
 16  /
I just printed my first line!
I just printed my second line!
I just printed my new line!

PL/SQL procedure successfully completed.
SQL>

All variables must be declared before declaring any subroutines, such as this procedure.

Related Topic