Oracle Developer Tools for Visual Studio .NET
page 4 of 7
by Steven Swafford
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 33077/ 89

Working with Procedures

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.


View Entire Article

User Comments

No comments posted yet.

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2021 ASPAlliance.com  |  Page Processed at 2021-04-16 11:02:58 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search