How To Write PL/SQL Body Code

How To Write PL/SQL Body Code

July 07, 2020
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

tutorial pl sql tutorial database programming database developer database administrator sql database plsql tutorial plsql sql programming language plsql learning plsql personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

tutorial pl sql tutorial database programming database developer database administrator sql database plsql tutorial plsql sql programming language plsql learning plsql personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

tutorial pl sql tutorial database programming database developer database administrator sql database plsql tutorial plsql sql programming language plsql learning plsql personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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

Leave a Reply

Your email address will not be published. Required fields are marked *

*