Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


Procedures in PL/SQL

PL/SQL Procedures - Oracle 11g

This article describes creating a PL/SQL stored procedure using SQLDeveloper for Oracle 11g. The goal of the code in this procedure is to Insert a new user into a table if they are not already in the database table. If the user value is already in the database table, output will display that the username already exists and cannot be added. This article will also show the case sensitivity in PL/SQL when querying and comparing data to values in a table when using the Lower function as well as using Nested Blocks to catch exceptions.

Create the Table and Sequence

The first statements needed are the CREATE TABLE to store the values in and a sequence number to keep track of them.

CREATE TABLE VISITOR_TABLE (
id NUMBER NOT NULL,
visitor_name varchar2(255) NULL );
/
CREATE SEQUENCE seq_visitor start with 100;
/

Next, a quick insert to test them out.

insert into Visitors_Table VALUES (seq_visitor.nextval, 'Snoopy');

select * from Visitors_Table;

SQL Output

Creating a Procedure in PL/SQL

The statement to make the Proc SAMPLE_PROC0 starts out with CREATE or REPLACE <proc name> IS. The variables are declared immediately below the statement followed by the BEGIN and END; block. The first variable is the name that I will insert into the table. The second variable is a variable that will contain a "matching name" from the Visitors_Table, if it exists. I could have used varchar2(255) as the type for the testvisitor, but instead, I am declaring the variable using the Type attribute in the event that the table is altered and the field visitor_name changes to a different size or different type.

CREATE or REPLACE PROCEDURE SAMPLE_PROC0 IS
 myvisitor varchar2(255) := 'Snoopy2';
 testvisitor VISITORS_TABLE.VISITOR_NAME%TYPE := '';
BEGIN
--code to execute here...
END;
/

The code simply outputs a Hello statement with the name that myvisitor is set to and then tests to see if the value of myvisitor is already in the table. If it is not found, then it is inserted into the table in the outer block. There are Exception blocks in both the inner and outer blocks.

CREATE or REPLACE PROCEDURE SAMPLE_PROC0 IS
 myvisitor varchar2(255) := 'Snoopy2';
 testvisitor VISITORS_TABLE.VISITOR_NAME%TYPE := '';
BEGIN
 BEGIN
  dbms_output.put_line('Hello ' || myvisitor);

  --test to see if user exists
  select NVL(visitor_name,'^') into testvisitor from visitors_table where
  LOWER(visitor_name) like LOWER(myvisitor);

 EXCEPTION
  WHEN NO_DATA_FOUND THEN
   dbms_output.put_line('The user ' || myvisitor || ' does not exist in the Visitor Table');
  WHEN OTHERS THEN
   dbms_output.put_line(SQLERRM);
 end;

 if testvisitor is null then
  insert into Visitors_Table VALUES (seq_visitor.nextval, myvisitor);
  dbms_output.put_line('The user ' || myvisitor || ' has been added to the Visitor Table');
 else
  dbms_output.put_line('The name ' || myvisitor ||' already exists in the Visitor Table');
 end if;

 WHEN OTHERS THEN
  dbms_output.put_line(SQLERRM);
END;

The inner Nested Block catches the Exception WHEN NO_DATA_FOUND error. Note that I have put an NVL function around the value of visitor_name - it will not function as expected. When the query runs and attempts to find a value that is not in the table, there is no value found and the NVL function does not set the testvisitor value to '^' because an Exception is generated. The Exception block will catch the error and output the information in a put_line statement and the value of testvisitor will be null. The outer block detects that testvisitor is null and inserts the myvisitor value into the table and outputs the message. If testvisitor were found in the table, the else part of the IF block will output the message regarding myvisitor already exists.

Proc Output

If the Inner block exception of NO_DATA_FOUND was not caught, you would simply get the system message generated by SQLERRM.

Proc output without Exception for No_Data_Found

Adding Input to a PL/SQL Procedure

To accept data input to the Procedure, parentheses are added and the variable name with an optional IN or OUT or both IN OUT mode plus the datatype are added as shown.

CREATE or REPLACE PROCEDURE SAMPLE_PROC (input in varchar2) IS

The code is similar to the above code for SAMPLE_PROC0, it just includes the input data variable declared in the procedure specification and an additional line added to set the local variable myvisitor to the value of input.

CREATE or REPLACE PROCEDURE SAMPLE_PROC (input in varchar2) IS
 myvisitor varchar2(255) := '';
 testvisitor VISITORS_TABLE.VISITOR_NAME%TYPE := '';
BEGIN
 BEGIN
  myvisitor := input;
  dbms_output.put_line('Hello ' || myvisitor);

  CASE myvisitor
  WHEN NULL THEN
   dbms_output.put_line('myvisitor was null');
  ELSE
   dbms_output.put_line('myvisitor was ' || myvisitor);
 END CASE;

  --test to see if user exists
 SELECT visitor_name INTO testvisitor FROM visitors_table WHERE
 LOWER(visitor_name) LIKE LOWER(myvisitor);

 EXCEPTION
  WHEN NO_DATA_FOUND THEN
   dbms_output.put_line('The user ' || myvisitor || ' does not exist in the Visitor Table');
  WHEN OTHERS THEN
   dbms_output.put_line(SQLERRM);
 end;

 IF testvisitor IS NULL THEN
  INSERT INTO Visitors_Table VALUES (seq_visitor.nextval, myvisitor);
  dbms_output.put_line('The user ' || myvisitor || ' has been added to the Visitor Table');
 ELSE
  dbms_output.put_line('The name ' || myvisitor ||' already exists in the Visitor Table');
 END IF;

END;

The code executes as expected when the value for myvisitor is not found.

Data showing input was inserted

And the code also executes as expected when the value for myvisitor is found.

Data showing input was Found

The LOWER function in the Where clause is to prevent any duplicates that might be in a different case - say all Lower case, Upper case, Proper Case, or a mix. If I remove the Lower function from the variable and the table field in the Where clause, I can put in the value for myvisitor in all caps and it would allow it to be inserted into the table.

Block showing addition of data after removing LOWER function

A quick select statement on the table confirms the data was added.

View of table showing addition of data