Define and call procedure : Procedure Definition « Stored Procedure Function « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Stored Procedure Function
  3. Procedure Definition

Define and call procedure

   

SQL>
SQL>
SQL> -- A procedure block.
SQL>
SQL>
SQL> -- Executing the swapn procedure.
SQL> -- Demonstration of a nested procedure block.
SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     first_number    NUMBER;
  3     second_number   NUMBER;
  4
  5  PROCEDURE swapn (num_one IN OUT NUMBER, num_two IN OUT NUMBER) IS
  6      temp_num    NUMBER;
  7  BEGIN
  8      temp_num := num_one;
  9      num_one := num_two;
 10      num_two := temp_num ;
 11  END;
 12
 13  BEGIN
 14
 15      first_number := 10;
 16     second_number := 20;
 17     DBMS_OUTPUT.PUT_LINE('First Number = ' || TO_CHAR (first_number));
 18     DBMS_OUTPUT.PUT_LINE('Second Number = ' || TO_CHAR (second_number));
 19
 20     -- Swap the values
 21     DBMS_OUTPUT.PUT_LINE('Swapping the two values now.');
 22     swapn(first_number, second_number);
 23
 24     -- Display the results
 25      DBMS_OUTPUT.PUT_LINE('First Number = ' || to_CHAR (first_number));
 26      DBMS_OUTPUT.PUT_LINE('Second Number = ' || to_CHAR (second_number));
 27  END;
 28  /
First Number = 10
Second Number = 20
Swapping the two values now.
First Number = 20
Second Number = 10

PL/SQL procedure successfully completed.

SQL>
SQL>








Related examples in the same category

1.Creat an empty procedure
2.replace a procedure
3.Create a stored procedure and how to call it.
4.Define procedure to insert data
5.Create procedure with authid
6.Create a stored procedure with authid
7.Mutually exclusive local subprograms.
8.A forward declaration.
9.Inner procedure
10.Save calculation result to a table in procedure
11.exception throwed out of the procedure
12.AUTHID clause in a CREATE PROCEDURE statement indicates that this procedure is being created with user's or invoker's rights
13.Increase gift price
14.A local subprogram within a stored procedure
15.Overloaded local procedures: number and varchar2
16.Using all the default values
17.Forward Referencing
18.Mark procedure with authid current_user
19.Reference package variable in a procedure
20.Only manager can change the password