Manuscript

In this tutorial, you will learn how to edit your SQL statement with different options.

As you noticed from your previous hands-on, if you made an error while typing a line of “SQL,” you had to use the “BACKSPACE” key to erase characters until you reached the mistake. However, this approach only worked if you were still on the same line in the “SQL” entry buffer.  Now, you are going to learn the “command line” and “notepad” editing techniques.

• In the “command line” technique, you can type in the number corresponding to the line to be corrected.  Then you can type in the change, delete, or insert command to edit the SQL buffer.

• If you don’t like “command line” editing, you can simply type edit, or “ed,” at the prompt in SQL*PLUS.  Oracle will immediately bring up your favorite text editor, perhaps notepad.  You can change your text editor with the define_editor command.

• There are two categories of PL/SQL blocks: anonymous and named.

• An anonymous block is a one time procedure and can not be referenced over and over.

• A named block can be referenced as many times as you want.  Procedures, functions, packages and triggers are examples of named blocks.

• A PL/SQL block contains “DECLARATION,” “BODY,” and “EXCEPTION” sections.

• The “DECLARATION” section is the place where all PL/SQL variables, types, cursors, and user defined exceptions would be declared.

• The “BODY” section contains the logical flow of your program instructions.

• The “EXCEPTION” portion will handle certain types of predefined or user defined exceptions.  It handles predefined exceptions without explicitly coding error-handling routines in the procedure.  And it handles user defined exceptions when the exceptions are explicitly raised.

• In your hands-on training, you will learn how to write code for each component a PL/SQL block.

• An implicit cursor is a “SQL” statement that is executed in the “BODY” section of the PL/SQL block.  Remember it returns only one record and must have an “INTO” clause.

• An explicit cursor is a named cursor that is defined in the “DECLARATION” section.  It will correspond to a defined SQL statement.  It can return as many records as you need.

• In order to obtain data from a cursor, the cursor should be opened and fetched into a variable.  Then the cursor should be closed.

• In the “BODY” section of the PL/SQL block, you can use conditional statement and process flow such as “if-then-else,” “simple loop,” “while-loop,” and “for-loop” statements.

• The “FOR LOOP” statement handles the steps normally done in the open statement, and implicitly fetches data from the cursor until there is no row.  It also declares the cursor variable.  The “END LOOP” statement closes the cursor.

• You can pass parameters to the cursor as input parameters.