"CREATE PROCEDURE" - Creating a Simple Procedure

JDBC for Oracle - Herong's Tutorial Examples

∟"CREATE PROCEDURE" - Creating a Simple Procedure

This section describes how to create a simple procedure that runs a simple query.

In order to test CallableStatement objects, I created a very simple stored procedure that updates the Point value of a Profile record:

-- Login as with "/ AS SYSDBA"
-- GRANT CREATE PROCEDURE TO Herong;" first

herong> sqlplus
Enter user-name: Herong/TopSecret

SQL> -- Creating a stored procedure
SQL> CREATE PROCEDURE UpdatePoint AS
  2  BEGIN
  3    UPDATE Profile SET Point = Point+1
  4    WHERE FirstName LIKE 'Herong';
  5  END;
  6  /

Procedure created.

SQL> -- Deleting old rows
SQL> DELETE FROM Profile /

SQL> -- Adding a new row
SQL> INSERT INTO Profile (ID, FirstName, LastName, Point)
  2  VALUES (1, 'Herong', 'Yang', 0)
  3  /

1 row created.

SQL> -- Checking the current Point value
SQL> SELECT ID, FirstName, Point FROM Profile
  2  WHERE FirstName LIKE 'Herong';

        ID FIRSTNAME                 POINT
---------- -------------------- ----------
         1 Herong                        0

SQL> -- Testing the procedure
SQL> EXECUTE UpdatePoint();

PL/SQL procedure successfully completed.

SQL> -- Checking the updated Point value
SQL> SELECT ID, FirstName, Point FROM Profile
  2 WHERE FirstName LIKE 'Herong';

        ID FIRSTNAME                 POINT
---------- -------------------- ----------
         1 Herong                        1

SQL>