ALTE DOCUMENTE
|
||||||||||
Course Reference Materials
Oracle9i PL/SQL : Basics
NETg course 61133
Copyright © 2002 National Education Training Group, Inc.
All rights reserved. No part of the material protected by this copyright may be reproduced or utilized in any form or by any means, electronic or mechanical, including photocopying, recording, broadcasting, or by any information storage and retrieval system, without permission in writing from National Education Training Group, Inc.
Skill Builder is a registered trademark of National Education Training Group, Inc. All other trademarks referenced are the trademark, service mark, or registered trademark of their respective holders. National Education Training Group, Inc. is not affiliated with any company or any other product or vendor mentioned in this course and its accompanying materials.
The software and technology used to implement this course contain trade secrets that NETg considers to be confidential and proprietary information, and your right to use this material is subject to the restrictions in the license agreement under which you obtained it.
Contents
Objectives
PL/SQL: An Introduction
Developing a Simple PL/SQL Block
Accessing the Database Using PL/SQL
Controlling Flow in PL/SQL Blocks
Composite Datatypes
Explicit Cursors
Handling Exceptions
Glossary
Alphabetical List of Terms
Total number of objectives = 72
Note: (ULT) is the unit, lesson, topic ID.
PL/SQL: An Overview
PL/SQL: Features (ULT 112): Identify the features of PL/SQL.
PL/SQL: Benefits (ULT 113): Identify the benefits of PL/SQL.
PL/SQL Engine Functionality (ULT 114): Identify the methods by which a PL/SQL engine block of code is processed by the different PL/SQL engines in a client-server environment.
PL/SQL Program Constructs
PL/SQL Blocks: An Introduction (ULT 121): Identify the features of the sections of a basic PL/SQL block.
PL/SQL Program Constructs: Features (ULT 122): Match the PL/SQL program constructs with their features.
PL/SQL Block: Syntax Rules (ULT 123): Identify the syntax rules to be applied in creating a PL/SQL block.
PL/SQL Datatypes
PL/SQL Variables: Functions (ULT 212): Identify the functions of PL/SQL variables.
Declaring Variables: Guidelines (ULT 213): Identify the guidelines for declaring PL/SQL variables.
PL/SQL Datatypes: Introduction (ULT 214): Match the PL/SQL datatypes with their descriptions.
Scalar Datatypes: Introduction (ULT 215): Match the scalar datatypes with their descriptions.
Declaring Scalar Variables (ULT 217): Declare a scalar variable in a PL/SQL block.
Declaring Scalar Variables with %TYPE (ULT 217): Declare a scalar variable with the %TYPE attribute. %TYPE attribute.
Declaring Variables
Assigning Values to Variables (ULT 221): Write the code to assign a value a to a variable by using the assignment operator.
Manipulating Values Using Operators: (ULT 222): Manipulate data in PL/SQL variables by using operators.
Manipulating Values Using SQL Functions (ULT 223): Manipulate data in PL/SQL variables by using SQL functions.
Converting Datatypes (ULT 224): Convert the datatype of a variable by using a data conversion function.
Variable Scope in Nested Blocks (ULT 225): Identify the features of variable scoping in nested PL/SQL blocks.
Bind Variables (ULT 226): Declare a bind variable in the SQL*Plus environment.
PL/SQL Programming Conventions
Code Writing Conventions (ULT 231): Identify the case conventions for writing PL/SQL code.
Identifiers: Naming Conventions (ULT 232): Match the identifiers with their naming conventions.
Retrieving Data
Retrieving Data with SELECT Statement (ULT 312): Retrieve data from a table by using the SELECT..INTO statement.
SELECT..INTO Statements: Exceptions (ULT 313): Identify the conditions in which various SELECT exceptions are raised.
Manipulating Data
Inserting Data (ULT 321): Write the code to add rows to a table by using the INSERT statement.
Updating Data (ULT 322): Write the code to modify the existing data in a table by using the UPDATE statement.
Deleting Data (ULT 323): Write the code to delete data from a table by using the DELETE statement.
Merging Data (ULT 324): Merge data from one table with that of another table by using the MERGE statement.
SQL Cursors
SQL Cursors: Features (ULT 331): Identify the features of a SQL cursor.
SQL Cursors: Attributes (ULT 332): Match the SQL cursor attributes with their descriptions.
Managing Transactions
Committing Current Transactions (ULT 341): Write the code to confirm the current transaction by using the COMMIT command.
Discarding Uncommitted Pending Changes (ULT 342): Write the code to discard the changes made to the table by using the ROLLBACK command.
Controlling Transaction Points (ULT 343): Write the code to control the transaction at the intermediate point by using the SAVEPOINT command.
Conditional Control
Performing Actions Using IF-THEN-ELSE (ULT 412): Write PL/SQL code using the IF-THEN-ELSE statement.
Performing Actions Using IF-THEN-ELSIF (ULT 413): Write PL/SQL code using the IF-THEN-ELSIF statement.
CASE Expressions (ULT 414): Write PL/SQL code using the CASE expression.
Boolean Conditions (ULT 415): Match a condition that uses logical operators with its result.
Looping Constructs
Repeating Statements Using a Basic Loop (ULT 421): Write the code for a basic loop to insert records into a table by using the LOOP keyword.
Repeating Statements Using a FOR LOOP (ULT 422): Write the code to execute a set of statements repeatedly by using the FOR LOOP.
Repeating Statements Using a WHILE LOOP (ULT 423): Write the code to execute a set of statements repeatedly by using the WHILE LOOP.
Nested Loops (ULT 424): Identify the features of a nested loop.
PL/SQL Records
Declaring a PL/SQL Record (ULT 512): Declare a PL/SQL record by using the RECORD datatype.
Referencing a PL/SQL Record (ULT 513): Reference a PL/SQL record.
Declaring with %ROWTYPE (ULT 514): Declare PL/SQL records with %ROWTYPE.
PL/SQL Table
Declaring a PL/SQL Table (ULT 521): Declare a PL/SQL table by using the TABLE datatype.
Referencing a PL/SQL Table (ULT 522): Reference a PL/SQL table.
PL/SQL Table: Methods (ULT 523): Match the methods to use PL/SQL tables with their descriptions.
PL/SQL Table of Records (ULT 524): Identify the syntax to reference a table of records.
LOB Datatype
LOB: Types (ULT 531): Match the LOB datatype variables with their descriptions.
LOB: Features (ULT 532): Identify the features of LOB variables in PL/SQL.
Cursors: An Introduction
Cursor Types (ULT 612): Identify the declaration methods of cursors, both implicit and explicit.
Controlling Explicit Cursors (ULT 613): Sequence the steps for controlling explicit cursors.
Explicit Cursors: Applications
Declaring Explicit Cursors (ULT 621): Write the code to declare a cursor by using the CURSOR statement.
Opening Explicit Cursors (ULT 622): Write the code to open a cursor by using the OPEN statement.
Retrieving Data from Explicit Cursors (ULT 623): Retrieve rows from a cursor by using the FETCH statement.
Closing Explicit Cursors (ULT 624): Close a cursor by using the CLOSE statement.
Explicit Cursors: Attributes
Checking Status Using %ISOPEN Attribute (ULT 631): Check the status of a cursor by using the %ISOPEN attribute.
Checking Status Using %FOUND Attribute (ULT 632): Check the status of a cursor by using the %FOUND attribute.
Exiting a Loop Using %NOTFOUND Attribute (ULT 633): Check the status of a cursor by using the %NOTFOUND attribute.
Counting Rows Returned Using %ROWCOUNT (ULT 634): Write the code to fetch a specified number of rows from a cursor by using the %ROWCOUNT attribute.
Cursor and Records (ULT 635): Write the code to process the rows of the active set conventionally by fetching values into a PL/SQL record.
Cursor FOR Loops (ULT 636): Write the code to process rows in an explicit cursor using cursor FOR loops.
Explicit Cursors: Advanced
Cursors with Parameters (ULT 641): Pass parameters to a cursor when a cursor is opened by using the cursor_name parameter.
FOR UPDATE Clause (ULT 642): Lock the records by using the FOR UPDATE clause.
Updating the Latest Fetched Row (ULT 643): Write the code to update the latest fetched row by using the WHERE CURRENT OF clause.
Exceptions: Basics
Exception: Types (ULT 712): Match the types of exceptions with their properties.
Predefined Oracle Server Exceptions (ULT 713): Match some common predefined Oracle server exceptions with their descriptions.
Trapping Exceptions: Guidelines (ULT 714): Identify the guidelines to trap exceptions.
Exceptions: Trapping
Trapping Predefined Exceptions (ULT 721): Complete the code to trap predefined exceptions.
Trapping Nonpredefined Exceptions (ULT 722): Complete the code to trap nonpredefined exceptions.
Trapping User-Defined Exceptions (ULT 723): Complete the code to trap user-defined exceptions.
Exceptions: Propagating
Trapping Exceptions: Functions (ULT 731): Match the functions for identifying the associated error message or error code with their descriptions.
Propagating Exceptions (ULT 732): Match each calling environment with its error-handling method.
RAISE_APPLICATION_ERROR Procedure (ULT 733): Use the RAISE_APPLICATION_ERROR procedure to raise user-defined error codes and messages.
Anonymous blocks are unnamed blocks. These are used at the point where they can be executed. The anonymous block has a DECLARE keyword instead of the header section of named blocks.
The Application trigger is a PL/SQL block that is associated with an application event and is fired automatically.
The keyword BEGIN separates the declaration section from the executable section of the subprogram. It is mandatory for the keyword BEGIN to be included.
The BFILE (binary file) datatype is used to store large binary objects in operating system files outside the database.
BINARY_INTEGER is the base type for integers between -2,147,483,647 and 2,147,483,647.
The BLOB (binary large object) datatype is used to store large binary objects in the database in line or out of line.
BOOLEAN is the base type that stores the values used for logical calculations.
Blocks are the units of a PL/SQL program. Blocks are either subprograms, named blocks, or anonymous blocks.
CHAR is the base type for fixed-length character data up to 32,767 bytes.
The CLOB (character large object) datatype is used to store large blocks of single-byte character data in the database.
Composite datatypes allow groups of fields to be defined and manipulated in PL/SQL blocks.
CONSTANT contains the variable whose value does not change.
Tables are created using the CREATE TABLE command in SQL.
Database trigger is a PL/SQL block that is associated with a database table and is fired automatically when triggered by DML.
A datatype that stores point-in-time values in a table.
The declaration section occurs between the keywords IS and BEGIN. The declaration section is where local identifiers are stated. This section is optional.
The END keyword marks the end of the executable section.
The keyword EXCEPTION occurs between the executable section and the exceptions section of the subprogram. This is where exceptions are handled. It is optional for the EXCEPTION section to be included.
The Exception Handling section is the final section in the PL/SQL block.
The executable section occurs between the keywords BEGIN and END;. The executable section contains the body of the subprogram, the SQL and PL/SQL statements. This section is mandatory.
A field is an intersection of a row and a column.
A function is a type of subprogram. Functions take in parameters and can be invoked. Generally functions are used to compute and return a value. For example, if the total_salary function had an empno parameter that was passed, the function would retrieve the employee's monthly salary from the sal column in the EMP table using the employee's number.
Identifier is the name of the variable.
LONG is the base type variable-length character data up to 32,760 bytes.
LONGRAW is the base type for binary data and byte strings up to 32,760 bytes.
The LOB (large object) datatypes hold values called locators.
The NCLOB (national language character large object) datatype is used to store large object blocks of single-byte or fixed-width multibyte NCHAR data in the database, in line or out of line.
NOT NULL is a condition specified for a column or a variable. A column or variable defined with the NOT NULL condition must necessarily contain a value. It cannot be NULL.
NUMBER is the base type for fixed and floating-point numbers.
PL/SQL is a procedural language extension to SQL.
PLS_INTEGER is the base type for signed integers between -2,147,483,647 and 2,147,483,647.
A procedure is a type of subprogram. Procedures are used to perform an action. Procedures take in parameters and can be invoked. For example, a get_ename procedure can be defined to accept the employee number empno as a parameter and retrieve the corresponding employee name from the EMP table. Then, on passing any employee number as a parameter, the procedure would retrieve and print the corresponding employee name.
Reference datatypes hold values called pointers.
Scalar datatypes hold a single value.
SQL*Plus is a tool used to develop subprograms to be stored on the server. SQL*Plus uses the PL/SQL engine in the Oracle 8i server. Subprograms can be directly typed at the prompt or a script can be created and edited in the default text editor (e.g. Notepad).
Subprograms are named PL/SQL blocks. There are two types, procedures and functions. Subprograms are also known as named blocks. Subprograms can take parameters and can be invoked on the client or the server. Subprograms can be stored on the server.
VARCHAR2 is the base type for variable-length character data up to 32,767 bytes.
|
