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;
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.
If the Inner block exception of NO_DATA_FOUND was not caught, you would simply get the system message generated by SQLERRM.
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.
And the code also executes as expected when the value for myvisitor is 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.
A quick select statement on the table confirms the data was added.
