#
# Oracle Performance Tuning Chapter 9 p. 147
# Tuning SQL*Forms
#
# Combining Similar Cursors with Table Joins
    SELECT EMP_NAME, EMP_MGR
        INTO  :BLK.EMP_NAME, :BLK.EMP_MGR
        FROM  EMP
        WHERE EMP_NO = :BLK.EMP_NO;

    SELECT EMP_NAME
        INTO  :BLK.MGR_NAME
        FROM  EMP
        WHERE EMP_NO = :BLK.EMP_MGR;
#
# Oracle Performance Tuning Chapter 9 pp. 147-148
#
# Combining Similar Cursors with Table Joins (cont.)
    DEFINE TRIGGER LOOKUP_EMP_MGR
        DECLARE 
            CURSOR C IS
            SELECT E.EMP_NAME, E.EMP_MGR, M.EMP_NAME
                FROM EMP M, EMP E 
                WHERE E.EMP_NO = :BLK.E_NO
                AND E.EMP_MGR = M.EMP_NO (+);
 
        BEGIN
            OPEN C; 
            FETCH C INTO :BLK.EMP_NAME,:BLK.EMP_MGR, :BLK.MGR_NAME;

            IF C%NOTFOUND THEN
                CLOSE C; 
                MESSAGE ('INVALID EMPLOYEE NUMBER ENTERED ....');
                RAISE FORM_TRIGGER_FAILURE;
            ELSE
                CLOSE C;
            END IF;
        END;
#
# Oracle Performance Tuning Chapter 9 p. 148
#
# Performing Multiple Fetches from the Same Cursor
    DEFINE TRIGGER LOOKUP_EMP_MGR

        DECLARE 
            CURSOR C IS
            SELECT E.EMP_NAME 
                FROM EMP E
                WHERE E.EMP_NO IN (:BLK.E_NO, :BLK.M_NO)
                ORDER BY DECODE (E.EMP_NO, :BLK.E_NO, 'A', 'Z');

        BEGIN
            OPEN   C; 
            FETCH C INTO :BLK.EMP_NAME, 

            IF C%NOTFOUND THEN
                CLOSE C; 
                MESSAGE ('INVALID EMPLOYEE NUMBER ENTERED ....');
                RAISE FORM_TRIGGER_FAILURE;
            ELSE
                IF :BLK.EMP_MGR IS NOT NULL THEN
                    FETCH C INTO :BLK.MGR_NAME;

                    IF C%NOTFOUND THEN
                        MESSAGE ('WARNING - EMPLOYEE HAS INVALID MANAGER');
                    END IF;
                END IF;

                CLOSE C; 
            END IF;
        END;

#
# Oracle Performance Tuning Chapter 9 p. 149
#
#Passing Parameters via Procedures	
    PROCEDURE procedure_name [argument_list]   IS
            local_variable_declaration
        BEGIN
            .
            .
            .
            PL/SQL block(s)
            .
            .
            .
        END;
#
# Oracle Performance Tuning Chapter 9 p. 149-150
#
#Passing Parameters via Procedures (cont.)
    DEFINE PROCEDURE LOOKUP_EMP (ENO   NUM   IN, 
                                 MGR   NUM   IN OUT, 
                                 NAME  CHAR  IN OUT )  IS
        BEGIN
            DECLARE 
                CURSOR C IS
                SELECT EMP_MGR, EMP_NAME 
                FROM EMP 
                WHERE EMP_NO = ENO; 
            BEGIN 
                OPEN  C1; 
                FETCH C1 INTO :BLK.EMP_MGR, :BLK.EMP_NAME; 
                CLOSE C1; 
            END; 
        END;

    TRIGGER XYZ;

        BEGIN
       
            LOOKUP_EMP (:BLK.EMP_NO, :BLK.EMP_MGR, :BLK.EMP_NAME); 
        
            LOOKUP_EMP (:BLK.EMP_MGR, :BLK.DUMMY, :BLK.MGR_NAME);
           
        END;
# If you use triggers, you will have to rely on background fields and/or
# global variables to pass and return data.
#
#
# Oracle Performance Tuning Chapter 9 p. 151-152
#
# Storing Fields in PL/SQL Local Variables
    DECLARE
        SAL_VAL    NUMBER;
        INT_VAL    NUMBER;
    BEGIN
        SAL_VAL := :BLK.EMP_SAL;

        IF SQL_VAL <= 2000   THEN
            INT_VAL := 0;
        END IF;

        IF  SAL_VAL >=  2001  AND
            SQL_VAL <=  8000  THEN
            INT_VAL :=  (SAL_VAL * 15 ) / 100;
        END IF;

        IF  SAL_VAL >= 8001    THEN
            INT_VAL := (SAL_VAL * 25 ) / 100;
        END IF;

        :BLK.EMP_INT := INT_VAL;
    END;
#
# Oracle Performance Tuning Chapter 9 p. 157
#
# Preventing Open Base Queries
    PRE-QUERY TRIGGER

        BEGIN
            IF   :BLK.FLD1 IS NULL
            AND  :BLK.FLD2 IS NULL
            AND  :BLK.FLD3 IS NULL   THEN
                MESSAGE ('INVALID QUERY - ...');
                RAISE FORM_TRIGGER_FAILURE;
            END IF;
        END;
#
# Oracle Performance Tuning Chapter 9 p. 157-158
#
# Preventing Open Base Queries (cont.)
    PRE-QUERY TRIGGER
        BEGIN
            IF :BLK.FLD1 IS NULL 
            OR SUBSTR(:BLK.FLD1,1,1) IN ('%' , '_') THEN 
                MESSAGE ('INVALID QUERY - . . .');
                RAISE FORM_TRIGGER_FAILURE;
            END IF;
        END;
#
# Oracle Performance Tuning Chapter 9 p. 158
#
#
    PRE-QUERY TRIGGER

        DECLARE
            TMP CHAR(240)
        BEGIN
            TMP := NAME_IN ('BLK.FLD');

        IF TMP IS NULL THEN
            MESSAGE   ('INVALID QUERY - ...);
            RAISE FORM_TRIGGER_FAILURE;
        END IF;
    END;
#
# Oracle Performance Tuning Chapter 9 p. 163
#
# Performing Pseudo-dynamic Base Table Queries 
    SELECT . . .
        FROM  EMP
        WHERE . . .
        AND ( UPPER(NAME) LIKE UPPER(:BASE.NAME ) 
        OR :BASE.NAME IS NULL )
#
# Oracle Performance Tuning Chapter 9 p. 164
#
# Performing Pseudo-dynamic Base Table Queries (cont.)
# You can replace the previous with the following type of query:
    SELECT . . .
        FROM  EMP
        WHERE . . .

#and a PRE-QUERY trigger:
    BEGIN
        IF :BASE.FLD IS NOT NULL THEN
           :BASE.NAME  :=  '=NAME AND UPPER(NAME) LIKE ' 
                || '''' || UPPER (:BASE.NAME) || '''';
        END IF; 
    END;

# When a value is entered into :BLK.NAME, the SQL is transposed as:
    SELECT . . .
        FROM EMP
        WHERE . . .
        AND ( UPPER(NAME) LIKE UPPER(:BASE.NAME) ) ;

#When no value is entered into :BLK.NAME, the SQL is transposed as:
    SELECT . . .
        FROM EMP
        WHERE . . .

#Now, consider the use of an ORDER BY clause in a dynamic base table:
    SELECT . . .
        FROM EMP
        WHERE . . .
        ORDER BY DECODE (:BLK.SORT_SEQ,  'E',    EMP_NO,
                                         'M',    EMP_MGR,
                                                 EMP_DEPT)

#You can replace the previous with the following query:
    SELECT . . .
        FROM EMP
        WHERE . . .
#
# Oracle Performance Tuning Chapter 9 pp. 164-165
#
# Performing Pseudo-dynamic Base Table Queries (cont.)
# ...and the PRE-QUERY trigger:
    BEGIN
        IF :BLK.SEQN = 'E' THEN
           :BASE.FLD := '=FLD) ORDER BY (EMP_NO '; 
        ELSIF    
            :BLK.SEQN = 'M' THEN
            :BASE.FLD := '=FLD) ORDER BY (EMP_MGR ';
        ELSE
            :BASE.FLD := '=FLD) ORDER BY (EMP_DEPT ';
        END IF; 
    END;
#
# Oracle Performance Tuning Chapter 9 p. 165
#
# Performing Pseudo-dynamic Base Table Queries (cont.)
# 
    BEGIN
        :BASE.FLD := '# = FLD AND UPPER(NAME) LIKE 
               ' || '''' || UPPER (:BASE.FLD) || '''';
    END;
#
# Oracle Performance Tuning Chapter 9 p. 165
#
# Performing Pseudo-dynamic Base Table Queries (cont.)
# 
    :BASE.FLD :=  '# || ''X'' IS NOT NULL AND UPPER(NAME) LIKE 
                          '' || '''' || UPPER(:BASE.NAME) || '''';
#This SQL is transposed as:
    SELECT . . .
        FROM EMP
        WHERE  . . .
        AND (NAME || 'X' IS NOT NULL AND
             UPPER(NAME) LIKE UPPER(:BASE.NAME) ) );
#
# Oracle Performance Tuning Chapter 9 p. 166
#
# Using the OR Clause with Dynamic Base Table Queries
#
    EMP
        WHERE  DEPT_NO = 10
           OR  DEPT_NO = 20

        FIELD_NAME : [ SMITH% ]	User-entered query condition

    SELECT ..., ..., ROWID	Actual SQL statement
        FROM EMP
        WHERE  DEPT_NO = 10
           OR  DEPT_NO = 20
           AND ( NAME LIKE SMITH%' )
#
# The translated SQL statement is now:
    SELECT ..., ..., ROWID
        FROM EMP
        WHERE DEPT_NO    = 10
            OR ( DEPT_NO = 20
            AND  ( NAME LIKE SMITH%' ) )
#
#
    EMP	
        WHERE  ( DEPT_NO = 10
            OR  DEPT_NO  = 20 )

        FIELD_NAME : [ SMITH% ]

    SELECT ..., ...,  ROWID
        FROM EMP
        WHERE  ( DEPT_NO = 10
            OR  DEPT_NO  = 20 )
            AND ( NAME LIKE 'SMITH%' )
#
# Oracle Performance Tuning Chapter 9 p. 169
#
# Running LIST_VALUES Over a Large Table
#
# Here is an example of a KEY-LISTVAL trigger:
    TRIGGER KEY-LISTVAL

        BEGIN
            IF :SYSTEM.CURSOR_FIELD = 'EMP_NO' THEN

                :GLOBAL.LVAL := '';
                CALL_QUERY ('NEW_LISTVAL', NO_HIDE);

                IF  :GLOBAL.LVAL IS NOT NULL THEN
                    :SYSTEM.CURSOR_FIELD := :GLOBAL.LVAL;
                END IF;

                ERASE ('GLOBAL.LVAL');
            ELSE
                LIST_VALUES;
            END IF;
        END;
#
# Oracle Performance Tuning Chapter 9 pp. 169-170
#
# Running LIST_VALUES Over a Large Table (cont.)
#
# 
    TRIGGER ON-ERROR
        BEGIN
            IF ERROR_CODE = 40502 THEN
            :GLOBAL.LVAL := '';
            CALL_QUERY ('NEW_LISTVAL', NO_HIDE);

            IF  :GLOBAL.LVAL IS NOT NULL THEN
                :SYSTEM.CURSOR_FIELD := :GLOBAL.LVAL;
            END IF;

            ERASE ('GLOBAL.LVAL');
        ELSE
            MESSAGE (ERROR_TYPE ||'-'|| TO_CHAR(ERROR_CODE)
                     ||''||ERROR_TEXT);
            RAISE FORM_TRIGGER_FAILURE;
        END IF;
    END;
#
# Oracle Performance Tuning Chapter 9 p. 170
#
# Running LIST_VALUES Over a Large Table (cont.)
#
    TRIGGER ON-ERROR

    BEGIN
        IF  ERROR_CODE = 40502 THEN
            EXECUTE_TRIGGER ('KEY-LISTVAL');
        ELSE
            MESSAGE (ERROR_TYPE ||''|| TO_CHAR(ERROR_CODE) ||''||
                     ERROR_TEXT);
            RAISE FORM_TRIGGER_FAILURE;
        END IF;
    END;
#
# Oracle Performance Tuning Chapter 9 p. 171
#
# Standardizing the Use of LIST_VALUES
#
        SELECT   EMP_NO, 
            SUBSTR (EMP_NAME, 1, 20 ),
            CURRENT_EMP
            FROM EMP 
#
#
        SELECT EMP_NO, 
            SUBSTR ( EMP_NAME, 1, 20 ), 
            CURRENT_EMP
        FROM EMP
        WHERE EMP_NO > 0	        /* FORCE EMP_NO INDEX */
#
#
        SELECT EMP_NO, 
            SUBSTR (EMP_NAME,1,20), 
            DECODE ( CURRENT_EMP, 'Y', CURRENT', 'NON CURRENT' )
            FROM EMP
            WHERE EMP_NO > 0
#
#
        SELECT TO_CHAR (EMP_NO, `000009' ),
            SUBSTR ( EMP_NAME,1,20),
            DECODE ( CURRENT_EMP, 'Y', 'CURRENT', 'NON CURRENT' )
            FROM EMP
            WHERE EMP_NO > 0
#
# Oracle Performance Tuning Chapter 9 p. 172
#
# Including Constants in LIST_VALUES
#
    SELECT N', 'NO'
        INTO  . . .
        FROM DUAL
        UNION
    SELECT 'Y', 'YES'
        FROM DUAL
#
# Oracle Performance Tuning Chapter 9 p. 176
#
# SQL*Forms Functions
    Function EMP_TAX ( Sal IN NUMBER ) RETURN
        NUMBER IS TAX   NUMBER;
    BEGIN
        IF  SAL  <=  20000 THEN
            TAX  :=  SAL * 20 / 100 ;
        ELSIF
            SAL  <=  30000 THEN
            TAX  :=  SAL * 25 / 100 ;
        ELSE
            TAX  :=  SAL * 30 / 100;
        END IF;

        RETURN ( SAL );
    END;
#
# Oracle Performance Tuning Chapter 9 p. 178
#
# SQL*Forms Functions (cont.)
    FUNCTION XDECODE (  TOK  IN  CHAR,
                        FM1  IN  CHAR,  TO1  IN  CHAR,
                        FM2  IN  CHAR,  TO2  IN  CHAR,
                        FM3  IN  CHAR,  TO3  IN  CHAR  ) RETURN CHAR IS
    BEGIN
        IF  TOK  =  FM1    THEN
            RETURN  ( TO1 );
        ELSIF
            TOK  =  FM2    THEN
            RETURN  ( TO2 );
        ELSIF
            TOK  =  FM3    THEN
            RETURN  ( TO3 );
        ELSE
            RETURN  ( NULL );
        END IF;
    END;
#
# Oracle Performance Tuning Chapter 9 pp. 178-179
#
# Optional Procedure and Function Parameters 
    FUNCTION XDECODE ( TOK  IN  CHAR,
                FM1  IN  CHAR := NULL,  TO1  IN  CHAR := NULL,
                FM2  IN  CHAR := NULL,  TO2  IN  CHAR := NULL,
                FM3  IN  CHAR := NULL,  TO3  IN  CHAR := NULL )
                RETURN CHAR IS
    BEGIN
        .
        .
        .
    END;
#
# Oracle Performance Tuning Chapter 9 p. 179
#
# Optional Procedure and Function Parameters (cont.)
#
#
    BEGIN
        .
        .
        .
        :BLK.SEX_DESC := XDECODE(:BLK.SEX, 'M', 'MALE');
        .
        .
        .
        :BLK.SEX_DESC := XDECODE(:BLK.SEX, 'M', 'MALE','F','FEMALE');
        .
        .
        .
        :BLK.SEX_DESC:=XDECODE(:BLK.SEX,'M','MALE',
                               'F','FEMALE','O','OTHER');
        .
        .
        .
    END;
#
#
    PROCEDURE EMP_TAX ( FLD1 IN NUMBER  := 0,
                        FLD2 IN CHAR    := 'ABC',
                        FLD3 IN BOOLEAN := TRUE )   IS
        BEGIN
        .
        .
        .
        END;
#
# Oracle Performance Tuning Chapter 9 p. 180
#
# Common SQL*Forms Triggers and Procedures
# 
    BEGIN
        IF :SYSTEM.CURSOR_VALUE IS NOT NULL THEN
            ENTER;
            IF NOT FORM_SUCCESS THEN
                RAISE FORM_TRIGGER_FAILURE;
            END IF;
        END IF;

        IF :SYSTEM.FORM_STATUS != 'CHANGED' THEN
            .
            .
            .
            GO_BLOCK ( 'XXXX' );
            .
            .
            .
        ELSE
            MESSAGE ('INVALID ACTION - COMMITS ARE OUTSTANDING');
            RAISE FORM_TRIGGER_FAILURE;
        END IF;
    END;
#
# Within SQL*Forms, this routine may work best as a system Boolean
# library function, which allows the routine to be referenced in-line:
    IF OUTSTANDING_COMMITS THEN 
        MESSAGE ( 'INVALID ACTION - COMMITS ARE OUTSTANDING' );
    ELSE
        .
        .
        .
    END IF;
#
# Oracle Performance Tuning Chapter 9 p. 181
#
# Checking whether a field can be updated
#
    IF  FIELD_CHARACTERISTIC  (:SYSTEM.CURSOR_FIELD, UPDATEABLE)  = 'TRUE' 
    OR  (FIELD_CHARACTERISTIC (:SYSTEM.CURSOR_FIELD, UPDATE_NULL) = 'TRUE' 
            AND :SYSTEM.CURSOR_VALUE IS NULL )
    OR  :SYSTEM.RECORD_STATUS IN ('INSERT', 'NEW') THEN
        .
        .        
        .
    ELSE MESSAGE ('INVALID ACTION - FIELD CANNOT BE UPDATED');
    END IF; 
#
#Enforcing commits on exits
    TRIGGER KEY-COMMIT

        :GLOBAL.COMMIT_FLAG := 'Y';
        COMMIT;
        :GLOBAL.COMMIT_FLAG := NULL;

    TRIGGER PRE-COMMIT

        IF  NVL(:GLOBAL.COMMIT_FLAG, 'N') <> 'Y' THEN
            MESSAGE ('INVALID ACTION - MAY ONLY COMMIT VIA COMMIT KEY ');
        END IF;
#
# Oracle Performance Tuning Chapter 9 p. 182
#
# Performing full-block scrolling
#
    TRIGGER KEY-SCRDOWN

        DECLARE
            XNUM  NUMBER;
            XBLK  NUMBER;
            XREC  NUMBER  := 0;
        BEGIN
            IF BLOCK_CHARACTERISTIC (:SYSTEM.CURSOR_BLOCK, BASE_TABLE) 
                IS NOT NULL
            THEN
                XBLK  := :SYSTEM.CURSOR_BLOCK;
                XNUM  := BLOCK_CHARACTERISTIC ( XBLK, RECORDS_DISPLAYED );
   
                GO_RECORD(TO_NUMBER(BLOCK_CHARACTERISTIC(XBLK, TOP_RECORD))
                    + XNUM;

                WHILE XREC < XNUM  AND :SYSTEM.LAST_RECORD = 'FALSE' LOOP 
                    NEXT_RECORD;
                    XREC := XREC + 1;
                END LOOP;

                GO_RECORD (TO_NUMBER(BLOCK_CHARACTERISTIC
                    (XBLK,TOP_RECORD) );
            ELSE
                MESSAGE ('THIS FUNCTION NOT ALLOWED FOR THIS BLOCK');
            END IF;
        END;
#
# Oracle Performance Tuning Chapter 9 p. 182-183
#
# Performing full-block scrolling (cont.)
#
    TRIGGER KEY-SCRUP

        DECLARE
            XBLK  NUMBER;
            XREC  NUMBER;
        BEGIN
            IF BLOCK_CHARACTERISTIC(:SYSTEM.CURSOR_BLOCK, BASE_TABLE) 
                IS NOT NULL
            THEN
                XBLK  := :SYSTEM.CURSOR_BLOCK;
                XREC  := BLOCK_CHARACTERISTIC ( XBLK, RECORDS_DISPLAYED );
                WHILE  XREC > 0 AND :SYSTEM.CURSOR_RECORD != 1 LOOP 
                    PREVIOUS_RECORD;
                    XREC := XREC - 1;
                END LOOP;

                GO_RECORD(TO_NUMBER(BLOCK_CHARACTERISTIC(XBLK,TOP_RECORD);
            ELSE
                MESSAGE ('THIS FUNCTION NOT ALLOWED FOR THIS BLOCK');
            END IF;
        END;
#
# Oracle Performance Tuning Chapter 9 p. 183
#
# Positioning a query at a particular record 
# 
    PROCEDURE POSITION_AT_FIRST_RECORD

        DECLARE
           XREC  NUMBER;
           XMTH  DATE;
        BEGIN
           XMTH := TO_DATE ('01' || (TO_CHAR (SYSDATE, 'MMYYYY'),
               'DDMMYYYY');

           WHILE :BLK.ROWID IS NOT NULL AND :BLK.DATE_EFF < XMTH LOOP
               NEXT_RECORD;
           END LOOP;

           XREC := TO_NUMBER(:SYSTEM.CURSOR_BLOCK);

           WHILE TO_NUMBER(BLOCK_CHARACTERISTIC('BLK',TOP_RECORD))<>XREC
               AND :SYSTEM.LAST_RECORD = 'FALSE' LOOP
               NEXT_RECORD;
           END LOOP;

           GO_RECORD (TO_NUMBER(BLOCK_CHARACTERISTIC('BLK',TOP_RECORD)));
        END;