Oracle PL-SQL Block
- Connect to the database by using
PETSTORE as the User Name,
GREATPETS as the Password, and
MYDB as the Host String. Approve the entries.
- 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
- Open the cursor and loop through the records.
- Fetch the records into the variables and display the data for the object table attributes by using
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.
- Retrieve data from a nested table column and display it by using
- End the first
FOR loop, close the cursor, and end the PL/SQL block.
- Compile the PL/SQL block.
- SQL*Plus now displays the result of the compilation of your block. This is the end of the simulation. Click the Exit button.
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 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
- Variable declarations
- Literals, characters, and reserved words
- Data types available for PL/SQL
- 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.