|
“Why be a man when
you can be a success?” Bertolt Brecht (1898 - 1956) |
Read
first then play the video:
PLS011(VIDEO)-Create
PL/SQL to remove department row
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.
|
“Dreams that do
come true can be as unsettling as those that don't.” Brett
Butler, 'Knee Deep in Paradise’ |
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?
|