Oracle PL/SQL Language Pocket Reference by Feuerstein, Steven -- Read -- Imperial Library of Trantor

Index

Introduction

Acknowledgments Conventions

PL/SQL Language Fundamentals

PL/SQL Character Set Identifiers Boolean, Numeric, and String Literals Numeric Literals Datetime Interval Literals Delimiters Comments Pragmas Statements Block Structure

Variables and Program Data

Scalar Datatypes

Numeric datatypes Character datatypes Unicode character datatypes Datetime datatypes BOOLEAN datatype

LOB Datatypes Implicit Datatype Conversions NULLs in PL/SQL Declaring Variables

Constrained declarations Constants Default values

Anchored Declarations Programmer-Defined Subtypes

Conditional and Sequential Control

Conditional Control Statements

IF-THEN combination IF-THEN-ELSE combination IF-THEN-ELSIF combination CASE statement CASE expression

Sequential Control Statements

GOTO NULL

Loops

Simple Loop Numeric FOR Loop Cursor FOR Loop WHILE Loop “Repeat Until” Loop Emulation EXIT Statement CONTINUE Statement Loop Labels

Database Interaction

Sequences in PL/SQL Transaction Management

COMMIT ROLLBACK SAVEPOINT SET TRANSACTION LOCK TABLE

Autonomous Transactions

Cursors in PL/SQL

Explicit Cursors

Declaring explicit cursors Opening explicit cursors Fetching from explicit cursors Closing explicit cursors Explicit cursor attributes

Implicit Cursors

SELECT FOR UPDATE clause WHERE CURRENT OF clause

Native Dynamic SQL DBMS_SQL SQL Injection and Bind Variables Cursor Variables Cursor Expressions

Exception Handling

Declaring Exceptions Raising Exceptions Scope Propagation

WHEN OTHERS clause SQLCODE and DBMS_UTILITY formatters for exception handlers Exceptions and transactions

Records in PL/SQL

Declaring Records Referencing a Record’s Fields Assigning Records Records and DML Nested Records

Collections in PL/SQL

Declaring a Collection Initializing a Collection Adding and Removing Elements Nested Table Functions Collection Methods Collections and Privileges Nested Collections Bulk Binds

Built-in Functions and Packages

Built-in Functions

Conversion functions String functions Numeric functions Date and time functions

Built-in Regular Expression Functions

Metacharacters REGEXP_COUNT REGEXP_LIKE REGEXP_INSTR REGEXP_SUBSTR REGEXP_REPLACE Match modifiers

Stored Procedures and Functions

Procedures Functions Parameters

Datatype Mode Default values Parameter-passing notations

Local Programs Program Overloading Forward Declarations Table Functions Function Result Cache Privileges and Stored PL/SQL

Triggers

Creating Triggers Trigger Predicates DML Events Compound DML Triggers DDL Events Database Events

Packages

Package Structure Referencing Package Elements Package Data SERIALLY_REUSABLE Pragma Package Initialization

Calling PL/SQL Functions in SQL

Calling a Function Improving Performance of Calling PL/SQL Functions from SQL Column Name Versus Function Name Precedence

Object-Oriented Features

Object Types Type Inheritance Methods

Member methods Static methods Constructor methods Comparison methods

Methods in Subtypes Manipulating Objects in PL/SQL and SQL Upcasting and Downcasting

REF operator VALUE operator DEREF operator

Changing Object Types

Compilation

Compiling Stored PL/SQL Programs Conditional Compilation Compiler Warnings Optimizing Compiler Performing Native Compilation of PL/SQL

Java Language Integration

Example Publishing Java to PL/SQL Data Dictionary

Index

← Prev
Back
Next →

← Prev
Back
Next →