Creating a PL/SQL Stored Procedure in JDeveloper

JDeveloper enables you to create stored procedures in the database through the Database Navigator. In these steps, you create a stored procedure that can be used as an alternative way of inserting an employee record in the sample application.

Select the DatabaseNavigatorName tab to view the Database Navigator.
Expand the database connection node (by default called Connection1) to see the objects in the HR database.
Right-click Procedures, and select New Procedure.
In the Create PL/SQL Procedure dialog, enter insert_employee as the object name. Click OK.

The skeleton code for the procedure is displayed in the Source Editor.

After the keywords CREATE OR REPLACE, enter the following lines of code replacing the existing line:

PROCEDURE INSERT_EMPLOYEE (p_first_name  employees.first_name%type, 
  p_last_name    employees.last_name%type,
  p_email        employees.email%type,
  p_phone_number employees.phone_number%type,
  p_job_id       employees.job_id%type,
  p_salary       employees.salary%type
)

After the BEGIN statement, replace the line that reads NULL with the following:
```
  INSERT INTO Employees VALUES (EMPLOYEES_SEQ.nextval, p_first_name, 
    p_last_name, p_email, p_phone_number, SYSDATE, p_job_id, 
    p_salary,.30, 100, 80);
```
You can see that the statement uses the same hard-coded values that are used for the last three columns in the addEmployee method in the DataHandler.java class.
Add the procedure name in the END statement:
```
END insert_employee;
```
Save the file, and check whether there are any compilation errors.

Example 6-6 Creating a PL/SQL Stored Procedure to Insert Employee Data

CREATE OR REPLACE PROCEDURE INSERT_EMPLOYEE (p_first_name  employees.first_name%type, 
  p_last_name    employees.last_name%type,
  p_email        employees.email%type,
  p_phone_number employees.phone_number%type,
  p_job_id       employees.job_id%type,
  p_salary       employees.salary%type
) 
AS
BEGIN
  INSERT INTO Employees VALUES (EMPLOYEES_SEQ.nextval, p_first_name , 
    p_last_name , p_email , p_phone_number, SYSDATE, p_job_id, 
    p_salary,.30,100,80);
END insert_employee;

The complete code for the stored procedure is shown in Example 6-6.