|
Create PL/SQL to remove department row
Hands-On
Introduction
In this
Hands-On, you write a PL/SQL stored
procedure to remove a record from the
department table (dept). You use one input
parameter to pass the department number (deptno);
and use one another output parameter to
check the status of the delete transaction.
To write a
procedure to remove a department record,
first select "program units" and click
�create�. Type the procedure name
�remove_dept� (remove department); and click
�OK.�
Write a PL/SQL
procedure using parameters
Define one
input parameter for the department number;
and an output parameter as a status
parameter. You will use this parameter to
test the status of the deleted transaction.
In the PL/SQL
body, delete the department record where its
department number matches with the input
department number parameter. Save the
deleted transaction. Assign "OK" to the
status output parameter for a successful
deleted transaction.
Include the
exceptions.
(Procedure
Builder)
PROCEDURE
remove_dept
(p_deptno IN
dept.deptno%TYPE,
p_status OUT
VARCHAR2)
IS
-- Delete a
record
DELETE FROM
dept
WHERE deptno =
p_deptno;
-- Save the
transaction.
COMMIT;
-- Check the
status.
p_status :=
�OK�;
EXCEPTION
WHEN
no_data_found THEN
p_status :=
�NO DATA FOUND.�;
WHEN others
THEN
p_status :=
�Other Problems.�;
END
remove_dept;
/
Compile and
save the PL/SQL procedure
Compile the
procedure. You should not have any error and
after successfully compiled. Then close the
window.
Save the
procedure in the database server by dragging
it to the database server.
Write a test
PL/SQL procedure
Go to the
PL/SQL interpreter. There, you can write an
anonymous block to run the procedure to test
it.
Or, you can
open the previous �test my proc� procedure;
and modify it. This is an easier way, and
the do any necessary changes. Change the
called procedure and its parameters.
Remove the
department number 40. Use the "TEXT_IO"
package to output the status of the deleted
transaction.
(Procedure
Builder)
PROCEDURE
test_remove_dept
-- This
procedure will test remove_dept procedure
v_status
VARCHAR2(40);
BEGIN
-- Call
remove_dept with a valid number.
remove_dept(40, 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_remove_dept;
/
Compile, Run
and test the PL/SQL program
Compile the
procedure; and close the window.
Run the �test
my proc� calling procedure.
PL/SQL> test_remove_dept;
Your output
should be �OK.� That means: the deleted
transaction was successful.
Query the
department table again.
PL/SQL> SELECT * FROM dept;
There should
be no "Finance" department.
Questions:
Q: Write a
procedure to remove a department record.
Make sure to define one input parameter for
the department number; and an output
parameter as a status parameter. You will
use this parameter to test the status of the
deleted transaction.
In the PL/SQL
body, delete the department record where its
department number matches with the input
department number parameter. Save the
deleted transaction and assign "OK" to the
status output parameter for a successful
deleted transaction.
Q: Write a
PL/SQL procedure to test the above created
PL/SQL procedure.
Q: What does
the TEXT_IO package?
Q: Name only
one procedure that is in the TEXT_IO
package.
Q: What are
the differences between the TEXT_IO and
DBMS_OUTPUT packages? |
