PL/SQL   «Prev 

Oracle PL-SQL Block

  1. Connect to the database by using PETSTORE as the User Name, GREATPETS as the Password, and MYDB as the Host String. Approve the entries.
  2. Declare variables for all the columns within the SALE_HEADER table. Name each variable as the column name prefixed with t_. Next, enter the query for a cursor that selects all the columns from SALE_HEADER, restricted to records only where sale_id is 36.
  3. Open the cursor and loop through the records.
  4. Fetch the records into the variables and display the data for the object table attributes by using DBMS_OUTPUT. The display should be in two lines, where the first line is the header containing the columns displayed and the second line is the data.
  5. Retrieve data from a nested table column and display it by using DBMS_OUTPUT.
  6. End the first FOR loop, close the cursor, and end the PL/SQL block.
  7. Compile the PL/SQL block.
  8. SQL*Plus now displays the result of the compilation of your block. This is the end of the simulation. Click the Exit button.

PL/SQL Basics

A PL/SQL block is a complete section of PL/SQL code. A PL/SQL program is made up of one or more blocks that logically divide the work. Blocks can even be nested within other blocks. The following description contains a full discussion on block structure.

Syntax Rules

Syntax and rules govern what you can and cannot do in PL/SQL. While the following syntax and programming standards alone do not make a program good, failure to understand the rules of the language can certainly make a program bad. In this module, we discuss the basic principles of the language, including PL/SQL block structure
  1. Variable declarations
  2. Literals, characters, and reserved words
  3. Data types available for PL/SQL
  4. Wrapper utility to hide code

The PL/SQL Block

The basic program unit in PL/SQL is called a block. Blocks contain sets of instructions for Oracle to execute, display information to the screen, write to files, call other programs, manipulate data, and more. All PL/SQL programs are made of at least one block. Methods of implementation range from programs that are executed one time only and are not stored anywhere, to blocks that are stored in the database for later use. Blocks support all DML statements, and using Native Dynamic SQL (NDS) or the built-in DBMS_SQL, they can run DDL statements.