How To Write PL/SQL Body Code

This online PL/SQL learning chapter will focus on the PL/SQL body code in the anonymous block. The most basic PL/SQL executable block is following:

BEGIN
  NULL;
END;

After executing the code you will see no output and the reason is in procedure NULL that does null executions which is doing nothing. The mandatory keywords for PL/SQL executable body are BEGIN and END.

In the next Oracle DBA example we are going to declare a variable v_num as numeric type and change the initial value 1 to 9. We will output the values before change and after it.

DECLARE
  v_num PLS_INTEGER := 1;
BEGIN
 DBMS_OUTPUT.PUT_LINE('In the beginning v_num=>'||v_num);
 v_num := 9;
 DBMS_OUTPUT.PUT_LINE('In the end v_num=>'||v_num);
END;

The example above has the most important line v_num := 9; that assigns a new value to the variable. See more about variables on this link.

In case you would need to re-execute the same code more than once or you would like to split your code into logical steps you can use a procedure in the PL/SQL anonymous block. In the following learning PL/SQL tutorial we will declare a procedure for the code above to keep our examples simple and clear. In your case your code can be long and it makes more sense to split the code and to use an Oracle procedure.

DECLARE
  v_num PLS_INTEGER := 1;

  PROCEDURE assign_value_9
  IS
  BEGIN
   DBMS_OUTPUT.PUT_LINE ('In the beginning v_num=>'||v_num) ;
   v_num := 9;
   DBMS_OUTPUT.PUT_LINE ('In the end v_num=>'||v_num) ;
  END assign_value_9;
BEGIN
    assign_value_9;
END;

As you see the code between the main BEGIN and END remained only 1 line and rest of the code was moved into the local Oracle procedure named assign_value_9. You can declare more than one procedure in the declaration section.

The another type of objects you can use are Oracle functions. The main difference between a procedure and a function in Oracle is that Oracle function needs always to return a value or this will cause an error. In the next example we will return some code to the main executable area and the function will return us number 9.

DECLARE
  v_num PLS_INTEGER := 1;

  FUNCTION get_value_9 RETURN PLS_INTEGER
  IS
  BEGIN
   DBMS_OUTPUT.PUT_LINE ('In the beginning of function v_num=>'||v_num) ;
   RETURN 9;
   DBMS_OUTPUT.PUT_LINE ('In the end of function v_num=>'||v_num) ;
  END get_value_9;
BEGIN
    v_num := get_value_9;
    DBMS_OUTPUT.PUT_LINE ('In the end v_num=>'||v_num) ;
END;

Take a look to the output in the code has 3 dbms_output lines but only two are showing. The reason is in the Oracle function that after returning a value it skips the rest of code.

See Also:
Next: How To Use Oracle Operators