Programming Oracle Triggers and Stored Procedures (3rd Edition) (Prentice Hall PTR Oracle Series)

Chapter Eleven. PL/SQL Language Features

11.1 COMMENTS

11.2 ASSIGNMENTS AND STATEMENTS

11.3 BOOLEAN EXPRESSION

11.4 EXPRESSIONS WITH NULL

11.5 LOGICAL OPERATORS

11.6 STRING CONCATENATION

11.7 ARITHMETIC EXPRESSIONS

11.8 VARIABLE DECLARATIONS

11.9 TYPES

11.9.1 Boolean

11.9.2 Scalar Types

11.9.3 Records

11.9.4 %ROWTYPE

11.9.5 Index-By Tables

11.9.6 Varrays and Nested Tables

11.9.7 Objects

11.9.8 Large Objects (LOBs)

11.10 IF STATEMENT

11.10.1 Simple IF

11.10.2 If-Then-Else

11.10.3 If-Then-Elsif with Else

11.10.4 If-Then-Elsif No Else

11.10.5 Statement Expressions

11.10.6 Use DECODE and CASE in SQL

11.11 CASE STATEMENT

11.11.1 Searched CASE Statement

11.11.2 CASE with Selector

11.11.3 Using CASE within the SELECT

11.11.4 Using DECODE within the SELECT

11.12 LOOP

11.12.1 DO UNTIL LOOP

11.12.2 WHILE-LOOP

11.12.3 FOR LOOP

11.12.4 DO-WHILE-DO LOOP

11.12.5 Encapsulating the Logic of the Loop

11.13 STRING MANIPULATION FUNCTIONS

11.13.1 SUBSTR

11.13.2 INSTR

11.13.3 LPAD, RPAD

11.13.4 LTRIM, RTRIM

11.13.5 REPLACE

11.13.6 TRANSLATE

11.14 MISCELLANEOUS STRING FUNCTIONS

11.15 NUMERIC FUNCTIONS

11.16 RANDOM NUMBER GENERATION

11.17 DATE FUNCTIONS

11.17.1 SYSDATE

11.17.2 TO_CHAR, TO_DATE

11.17.3 ADD_MONTHS

11.17.4 LAST_DAY

11.17.5 MONTHS_BETWEEN

11.17.6 NEW_TIME

11.17.7 NEXT_DAY

11.17.8 ROUND, TRUNC

11.18 EXCEPTIONS

11.18.1 User-Defined Exceptions

11.18.2 Blocks with Exception Handlers

11.18.3 The EXCEPTION Clause

11.18.4 SQLCODE and SQLERRM

11.18.5 The RAISE Statement

11.18.6 Unhandled Exceptions and Exception Propagation

11.18.7 RAISE_APPLICATION_ERROR

11.18.8 EXCEPTION_INIT

11.19 DATABASE ACCESS WITH SQL

11.19.1 CURSOR FOR LOOPS

11.19.2 Select when Expecting a Single Row

11.19.3 Inserts and Updates

11.19.4 Explicit Cursors

11.20 SENDING PIPE MESSAGES (DBMS_PIPE)

11.20.1 Send-Receive Example

11.20.2 Interface Description

11.20.3 Exception Handling

11.21 SIGNALING EVENTS WITH ALERTS (DBMS_ALERT)

11.21.1 Interface Description

11.22 EMAIL NOTIFICATION WITH TRIGGERS AND ALERTS