This chapter teaches you how to create and drop procedures in Derby.

Creating a procedure

You can create a procedure using the CREATE PROCEDURE statement.

Syntax

Following is the syntax of the CREATE PROCEDURE statement.

CREATE PROCEDURE procedure_name (parameter_type parameter_name1, parameter_type
parameter_name2 . . . .) parameter_style;

Example

Suppose, we have created a table in Derby as shown below.

CREATE TABLE Emp ( Id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
   Name VARCHAR(255),
   Salary INT NOT NULL,
   Location VARCHAR(255),
   Phone_Number BIGINT
);

And inserted values in it as follows −

INSERT INTO Employees(Name, Salary, Location) VALUES
   (''Amit'', 30000, ''Hyderabad''),
   (''Kalyan'', 40000, ''Vishakhapatnam''),
   (''Renuka'', 50000, ''Delhi''),
   (''Archana'', 15000, ''Mumbai''),
   (''Trupthi'', 45000, ''Kochin'')";

Following example creates a procedure named Update_Procedure which accepts JAVA parameters.

ij> CREATE PROCEDURE Update_Procedure(IN id INTEGER, IN name VARCHAR(10))
PARAMETER STYLE JAVA READS SQL DATA LANGUAGE JAVA EXTERNAL NAME
''ProcedureExample.testProc
> 0 rows inserted/updated/deleted

Where ProcedureExample class looks like −

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class ProcedureExample {
   public static void testProc(int salary, String name) throws Exception {
      String connectionURL = "jdbc:derby:MYDATABASE;create=true";
      Connection conn = DriverManager.getConnection(connectionURL);
      String query = "UPDATE Employees SET SALARY = ? WHERE NAME = ?";
      PreparedStatement pstmt = conn.prepareStatement(query);
      pstmt.setInt(1, salary);
      pstmt.setString (2, name);
      pstmt.executeUpdate();
   }
}

You can verify the list of procedures using SHOW PROCEDURES query.

ij> SHOW PROCEDURES;
PROCEDURE_SCHEM |PROCEDURE_NAME       |REMARKS
------------------------------------------------------------------------
APP             |UPDATE_PROCEDURE     |ProcedureExample.te&
SALES           |EXAMPLE_ PROCEDURE   |com.example.sales.c&
SQLJ            |INSTALL_JAR          |org.apache.derby.ca&
SQLJ            |REMOVE_JAR           |org.apache.derby.ca&
SQLJ            |REPLACE_JAR          |org.apache.derby.ca&
SYSCS_UTIL      |SYSCS_BACKUP_DATABASE|org.apache.derby.ca&
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 

Here you can observe the newly created procedure.

Dropping a procedure

You can drop a procedure using the DROP PROCEDURE statement.

Syntax

Following is the syntax of the DROP PROCEDURE statement.

DROP PROCEDURE procedure_name;

Example

Following example drops a procedure named Update_Procedure created above.

ij> DROP PROCEDURE Update_Procedure;
> 0 rows inserted/updated/deleted