Creating a procedure is a speedy and trouble-free process, as long as you are familiar with PL/SQL. However, even if you are not a seasoned PL/SQL developer, Oracle has again done an outstanding job with the integrated help system. When you are at a standstill, all you have to do is highlight any SQL or PL/SQL keyword and the context sensitive help will be displayed by pressing the F1 key. To create a new procedure, click the procedure node and select New PL/SQL Procedure.
Figure 6: Add New PL/SQL Procedure
From here, you can define the parameters and parameter types all within a window similar to the table window shown earlier in figure 4. In this example I have created a new procedure named NewEmployee. I established two parameters, one named p_emp_id with a data type of number, and the second named p_start_date with a data type of date. Now all that remains is to click the Save button and the example code in listing 1 is generated.
Listing 1: Default New Employee Procedure
CREATE PROCEDURE "HR"."PROCEDURE1" (
"P_EMP_ID" IN NUMBER,
"P_START_DATE" IN DATE) IS
-- Declare constants and variables in this section.
-- Example: <Variable Identifier> <DATATYPE>
-- <Variable Identifier> CONSTANT <DATATYPE>
-- varEname VARCHAR2(40);
-- varComm REAL;
-- varSalary CONSTANT NUMBER:=1000;
-- comm_missing EXCEPTION;
BEGIN -- executable part starts here
-- Write PL/SQL and SQL statements to implement the processing logic
-- of subprogram. Example:
-- SELECT ENAME,
-- COMM
-- INTO varEname,
-- varComm
-- FROM EMP
-- WHERE EMPNO = 7369;
--
-- IF varComm IS NULL THEN
-- RAISE comm_missing;
-- END IF;
NULL;
-- EXCEPTION -- exception-handling part starts here
-- WHEN comm_missing THEN
-- dbms_output.put_line('Commision is NULL');
END "PROCEDURE1";
At this point, all that is necessary is to modify the generated code in listing 1 to execute an insert into the job_history table.
Listing 2: Modified New Employee Procedure
PROCEDURE "HR"."PROCEDURE1" (
"P_EMP_ID" IN NUMBER,
"P_START_DATE" IN DATE) IS
BEGIN
INSERT INTO job_history (employee_id, start_date)
VALUES(p_emp_id, p_start_date);
END "PROCEDURE1";
As you can see, creating a procedure is comparatively effortless and straightforward. Once again one drawback that I feel could be improved upon is Intellisense.
