Take a look at this:

http://download.oracle.com/docs/cd/B...a96624/toc.htm

Then buy and read PL/SQL best practices by Steven Feuerstein.

Specific tips about your procedure: use implicit cursors when you want to read every record in a cursor or better yet learn about pl/sql collections, "or" clauses tend to be expensive, can you do without it, you have missing business logic that no one outside your company will know, you need to learn how to create and use packages, use functions to encapsulate business logic, and read up on exception handling.

This is a terrible idea, removing this code would give you more information than leaving it in place.
Code:
Exception
   When Others Then
      Rollback;
      dbms_output.put_line(Sqlerrm(Sqlcode));
But otherwise it probably won't blow up your database.