|
Create PL/SQL to add department row using
procedure builder
Hands-On
Introduction
In this
Hands-On, you write a PL/SQL stored
procedure to add a record into the
department table (dept). You use three input
parameters to pass the department's columns
(Department number �deptno,� department name
�dname,� and department location �loc�); and
use one output parameter to check the status
of the insert transaction. You should use
the Procedure Builder.
Start Listener
Go to "MS-DOS"
and start the listener.
MS-DOS>
lsnrctl start
Normally when
you start your NT, the SERVICE processor
will start the listener. Once the listener
was started, you will be able to access the
database from any of the Oracle tools. A
listener is an Oracle agent that monitors a
specific port. It is a gateway of
communication between clients and Oracle
server.
Procedure
Builder Tool
The procedure
Builder tool is a software utility that
helps developers to write, debug, save, and
test their PL/SQL programs.
Open the
"Procedure Builder" tool.
Object
Navigator
The Object
Navigator window is a place that a developer
can browse and navigate all its created
objects.
In the �Object
Navigator� window, highlight "database
objects." Go to the Main menu at the top of
the window, choose the "File" sub-menu and
select the "connect� option.
In the
�Connect� window, login as "oracle" password
"learning", and then click on the "CONNECT"
button.
Open the
Database Objects
In the �Object
Navigator� window, click on the �+� sign
next to "Database Objects" to expand it.
Navigator all the database objects (schemas,
tables, views, triggers, procedures,
packages, functions, etc.)
Create a
Procedure
In the �Object
Navigator� window, highlight "Program Units�
and click on the green �+� sign which is the
�Create� icon.
In the �New
Program Unit� window, type the procedure
name as �add_department� and then click
�OK.� Make sure the �Procedure� radio button
is selected. That is the default option.
In the
�Program Unit� window, write a stored
procedure to add a record into the
department table. Use three parameters as an
input parameter to pass the department's
columns: Department number, department name,
and department location.
Use the "p_"
prefix to name the parameters. Use one
parameter as an output parameter to check
the status of the transaction.
Use comments
in your programs. Use double dashes for a
single line comment. And use �/*� ended with
�*/� for a multiple lines comment.
Writing a
PL/SQL procedure
In the PL/SQL
body, insert the input parameters' value
into the department table. Save the
transaction. Assign "OK" to the �Status�
parameter if transaction was successful.
In the
�EXCEPITON� section, define the exception.
Use the �duplicate value on index�
exception, the �invalid number� exception,
and the �OTHERS� exception. Use the others
in case you are missing other exceptions.
(Procedure
Builder)
PROCEDURE
add_dept
(p_deptno IN
dept.deptno%TYPE,
p_dname IN
dept.dname%TYPE,
p_loc IN
dept.loc%TYPE,
p_status OUT
VARCHAR2)
IS
-- No variable
BEGIN
/* This
program add dept. record. */
INSERT INTO
dept
VALUES
(p_deptno, p_dname, p_loc);
--- Save
record.
COMMIT;
-- Added
successfully if the get to this line.
p_status :=
�OK�;
EXCEPTION
-- Check for
an Unique or Primary Key
WHEN
dup_val_on_index THEN
p_status :=
�DUPLICATE RECORD�;
-- Check for
invalid input data
WHEN
invalid_number THEN
p_status :=
�INVALID INPUT DATA�;
-- Check for
any other problems
WHEN others
THEN
p_status :=
�CHECK THIS WE HAVE UNKNOWN PROBLEM.�;
END add_dept;
/
Compile a
PL/SQL procedure
Compile the
procedure. You should not have any error and
you should get "Successfully compiled"
message.
Errors in
Procedure Builder
Try to create
an intentional error and compile the
procedure.
For example:
p_status_was_not_defined := �DUPLICATE
RECORD�;
Read the error
messages. Notice that the error message most
of a time is very preside and tells the line
number such as:
Error 201 at
line 11, column2
Identifier
�p_status_was_not_defined� must be declared
Correct the
error and compile it again. "Successfully
Compiled."
Create a test
PL/SQL program
In the �Object
Navigator� window, highlight "Program Units�
and click on the �+� sign �Create� icon.
In the �New
Program Unit� window, type the procedure
name as �test_add_dept� and then click �OK.�
Make sure the �Procedure� radio button is
selected. That is the default option.
In the
�Program Unit� window, write a stored
procedure to test the �add_department�
procedure.
Declare a
status variable and make sure to call the
�add_department� procedure.
Notice, the
called procedure has three input parameters
and one output parameter; and department
number can not be more than two digit
numbers.
Enter an
invalid department number to see the
exception error message. To display the
status of your transaction value, use the
"TEXT_IO" instead of the "DBMS_OUTPUT", when
you run the procedure locally.
(Procedure
Builder)
PROCEDURE
test_add_dept
-- This
procedure will test add_dept procedure
v_status
VARCHAR2(40);
BEGIN
-- Call
add_dept with an invalid number.
add_dept(100,
�FINANCE�, �OHIO�, v_status);
-- Print �OK�
value if there is no error.
TEXT_IO.PUT_LINE(v_status);
EXCEPTION
WHEN others
THEN
p_status :=
�CHECK THIS WE HAVE UNKNOWN PROBLEM.�;
END
test_add_dept;
/
Compile and
test the program
Compile the
procedure.
On the
"PL/SQL" interpreter, query the department
table.
PL/SQL> SELECT
* FROM dept
ORDER BY
deptno;
Run a PL/SQL
procedure (Test program)
Run the
calling procedure-�test_add_dept.�
PL/SQL>
test_add_dept;
Error messages
You should get
an error since the department number is more
than 99.
Notice the
error message says �CHECK THIS WE HAVE
UNKNOWN PROBLEM.�
Correct the
Error, compile and run the program
In the �Object
Navigator� window, double click on the icon
next to the test_add_department to open the
�Program Unit� window; and change 100 to 40.
Compile and
close the window. Then run the calling
procedure-�test_add_dept� once more. Now,
you should see the �OK� message. That means
the transaction was added successfully.
Query the
department table again to check that the
transaction was successfully added.
PL/SQL> SELECT
* FROM dept
ORDER BY
deptno;
Notice, that
the Finance department was added.
Save the
PL/SQL procedure in the Database server
To save the
program in the database server, expand the
oracle schema in the �Object Navigator�
window. Expand the stored program units,
too. Then drag the procedure into the
�Stored Program Units.� Notice that the
solid line means: you can drop the object.
Save the
PL/SQL procedure in the Local Library
To save the
program in the local library, go to the
�Object Navigator� window, highlight PL/SQL
libraries and click on the create icon.
Click �OK.� Choose the �File� option and
select �Save as.� Save a library as
my_library in your folder. Then click �OK�
as �File System.� A library should be
created.
Now, drag the
procedure into its �Program Units.�
Highlight the library name and save it
again.
Questions:
Q: What is the
Procedure Builder Tool?
Q: What is the
listener in the Oracle database?
Q: How do you
start or stop your listener?
Q: What is the
Object Navigator in the Procedure Builder
tool?
Q: How to you
open a database using the Procedure Builder
tool?
Q: What is a
user�s schema?
Q: What type
of objects can you have under a schema?
Q: How do you
create a procedure using the Procedure
Builder Tool?
Q: What is a
Program Unit?
Q: Write a
PL/SQL stored procedure to add a record into
the department table (dept). You use three
input parameters to pass the department's
columns (Department number �DEPTNO,�
department name �DNAME,� and department
location �LOC�); and use one output
parameter to check the status of the insert
transaction. You should use the Procedure
Builder.
Note that you
should use the "p_" prefix to name the
parameters. You use this parameter as an
output parameter to check the status of your
transaction. Use comments in your programs.
Use double dashes for a single line comment.
And use �/*� ended with �*/� for a multiple
lines comment. In the �EXCEPITON� section,
define the exception. Use the �duplicate
value on index� exception, the �invalid
number� exception, and the �OTHERS�
exception. Use the others in case you are
missing other exceptions.
Q: Write a
stored procedure to test the
�add_department� procedure. Declare a status
variable and make sure to call the
�add_department� procedure. Enter an invalid
department number to see the exception error
message. To display the status of your
transaction value, use the TEXT_IO instead
of the DBMS_OUTPUT, when you run the
procedure locally.
Q: What is the
client side environment?
Q: What is the
server side environment?
Q: How do you
save the above PL/SQL procedure in your
local library? |
