Tutorial: Creating a Standalone Stored Procedure

To create a standalone stored procedure, use either the SQL Developer tool Create PL/SQL Procedure or the DDL statement CREATE PROCEDURE.

This tutorial shows how to use the Create PL/SQL Procedure tool to create a standalone stored procedure named ADD_EVALUATION that adds a row to the EVALUATIONS table (created in "Creating Tables with the CREATE TABLE Statement").

To create a standalone stored procedure using Create PL/SQL Procedure tool:

1. On the Connections tab, expand hr_conn.

   Under the hr_conn icon, a list of schema object types appears.

2. Right-click Procedures.

   A list of choices appears.

3. Click New Procedure.

   The Create PL/SQL Procedure window opens.

4. For Schema, accept the default value, HR.

5. For Name, change PROCEDURE1 to ADD_EVALUATION.

6. Click the icon Add Column.

   A row appears under the column headings. Its fields have these default values: Name, param1; Type, VARCHAR2; Mode, IN; Default Value, empty.

7. For Name, change param1 to evaluation_id.

8. For Type, select NUMBER from the drop-down list.

9. For Mode, accept the default value, IN.

10. Leave Default Value empty.

11. Add a second parameter by repeating steps 6 through 10 with the Name employee_id and the Type NUMBER.

12. Add a third parameter by repeating steps 6 through 10 with the Name evaluation_date and the Type DATE.

13. Add a fourth parameter by repeating steps 6 through 10 with the Name job_id and the Type VARCHAR2.

14. Add a fifth parameter by repeating steps 6 through 10 with the Name manager_id and the Type NUMBER.

15. Add a sixth parameter by repeating steps 6 through 10 with the Name department_id and the Type NUMBER.

16. Add a seventh parameter by repeating steps 6 through 10 with the Name total_score and the Type NUMBER.

17. Click OK.

    The ADD_EVALUATION pane opens, showing the CREATE PROCEDURE statement that created the procedure.

    Because the only statement in the execution part of the procedure is NULL, the procedure does nothing.

18. Replace NULL with this statement:

    INSERT INTO EVALUATIONS (
       evaluation_id,
       employee_id,
       evaluation_date,
       job_id,
       manager_id,
       department_id,
       total_score 
    )
    VALUES (
      ADD_EVALUATION.evaluation_id,
      ADD_EVALUATION.employee_id,
      ADD_EVALUATION.evaluation_date,
      ADD_EVALUATION.job_id,
      ADD_EVALUATION.manager_id,
      ADD_EVALUATION.department_id,
      ADD_EVALUATION.total_score
    );
    

    (Qualifying the parameter names with the procedure name ensures that they are not confused with the columns that have the same names.)

    The title of the ADD_EVALUATION pane is in italic font, indicating that the procedure is not yet saved in the database.

19. From the File menu, select Save.

    Oracle Database compiles the procedure and saves it. The title of the ADD_EVALUATION pane is no longer in italic font.