iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - PL/SQL 

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 |

 

Lesson 09

“Why be a man when you can be a success?” Bertolt Brecht (1898 - 1956)

 

Read first then play the video:

   PLS-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?