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.

You will:

    1- Write a PL/SQL procedure to remove a record using Procedure Builder.

    2- Use input and output parameters.

    3- Run the stored procedure.

    4- Test the stored procedure.

    5- Save the procedure.

 

	More Resources by Google:

Write a procedure to remove a department record.

=

Select "program units" and click “create”.

=

Type the procedure name (remove department); and click “OK.”

=

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 it's 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.

=

Compile the procedure.

Successfully compiled.  Then close the window.

=

Save the procedure in the database server.

Drag it to the database server.

=

Go to the PL/SQL interpreter.

Here, 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.

Do any necessary changes.

Change the called procedure and its parameter.

Remove the department number 50.

Use the "text_io" package to output the status of the deleted transaction.

=

Compile the procedure; and close the window.

=

Run the “test my proc” calling procedure.

“OK” means: the deleted transaction was successful.

=

Query the department table again.

There should be no "Finance" department.

Now, you should practice this over and over, until you become a master at it.

Good Luck!