SQL

Best Practices

• Use separate line for each expression in a SELECT list
• Place each table in a FROM clause on its own line
• Place each expression in WHERE clause on its own line
• Use sensible abbreviations for table and column aliases
• UPPER all oracle reserve words and right align them
• Lower all object words
• Keep beginning and ending of multi-line statements equally indented
• Don't repeat the same logical SQL statement. Repetition makes it almost impossible to maintain and optimize.
• Use ROWNUM in WHERE CLAUSE especially when using EXISTS condition SELECT 'X' from SGBSTDN WHERE ....
• In statements with multiple key words, attempt to right align the key words, for example:

SELECT DISTINCT last_name
,first_name
,middle_init
FROM l0_empl_directory
WHERE first_name IS NOT NULL
AND UPPER (last_name) LIKE UPPER ('S%')
ORDER BY last_name
,first_name
,middle_init;

• FORALL
• Use with inserts, updates and deletes.
• Move data from collections to tables. For Ex: the For loop has more DB hits for every execution.

FOR rec IN stu_cur LOOP

UPDATE student
SET waiver = ...
WHERE student_id = rec.student_id;
END LOO;

Instead FORALL has less performance issues.

PROCEDURE p_remove_emps_by_dept (p_stulist stuid_t)IS

BEGIN

FORALL aStuid IN stulist.FIRST..stulist.LAST

UPDATE student
SET waiver = ...
WHERE student_id = p_stulist(aStuid);
END;

• BULK COLLECT
  • You MUST know how to use collections to use this feature!
  • Only a single DML statement is allowed per FORALL.
  • SQL%BULK_ROWCOUNT returns the number of rows affected by each row in the binding array.
  • Use SAVE EXCEPTIONS to continue past errors since
  • If error occurs, prior successful DML statements are NOT ROLLED BACK
• Use with implicit and explicit queries.
• Move data from tables into collections
• Recurring SQL statement in PL/SQL loop. Oracle recommended threshold: five rows!
• Things to be aware of:

• CASE
• Use case statements instead of Decode when possible for better readability and maintainability.

Back to Top

PL/SQL Best Practices

• If your program could be useful on both the server side and the client side, move it to the server, because it can be invoked from both sides there.
• If the program is used only in and relevant to client-side modules (it may, for example, manipulate the contents of a field in a form) and you think or know that it will be useful in more than one module, put it into a shared library.
• If your program is very specific to a current form or report, define it within that module.
• Use small, narrowly-focused packages. For example, one package for student matching, another for creating person records. Package is usually created for the following reasons
• Logical containers for related elements
• Overloading
• Package-level data and caching
• Initialization section
• Local or nested modules
• To keep executable sections small/tiny

• Avoid hard-coded declarations by using SUBTYPEs and anchored declarations (%TYPE and %ROWTYPE). Ensure that %type is used for the objects in the pl/sql block.
• Avoid hard-coding and repetition.
• Each variable and constant you declare should have one purpose and one purpose only in an object (PKG, FUNCTION, and PROCEDURE). The name for that variable or constant should describe, as clearly as possible, that single-minded purpose ( Use the identifier naming convention).
• Remove any unused variables from code.
• A subprogram can defined at any of the following levels:
  • Define your subprograms as close as possible to their usage(s).The shorter the distance from usage to definition, the easier it is to find, understand and maintain that code.
• Local within another subprogram
• Private, defined in the package body
• Public, defined in the package specification
• The best rule to follow is:
• Always Fetch into Cursor Records. Main advantages are less code and if your select list changes your code is not affected.  For ex The below ( highlighted in yellow) is correct but the ex in red is more efficient.

name VARCHAR2 (30);
minbal NUMBER(10,2);
BEGIN
OPEN company_pkg.allrows;
FETCH company_pkg.allrows
INTO name, minbal;
IF name = 'ACME' THEN ...
CLOSE company_pkg.allrows;

rec company_pkg.allrows%ROWTYPE;
BEGIN
OPEN company_pkg.allrows;
FETCH company_pkg.allrows INTO rec;
IF rec.name = 'ACME' THEN ...

• Place the keywords (IF, THEN, ELSE, ELSIF, ENDIF) on separate lines. Avoid Unnecessary Nested IFs. For Ex: The following statements are equivalent. The flat structure expresses the logic more clearly and with less code.

• Use Boolean Elements to Improve Readability Boolean variables and functions allow you to greatly improve readability of programs.
• Compare the two IF statements below:

IF scbcrse_crse_code BETWEEN 10000 AND 50000

AND room_status (cat_rec.empno) = 'N'
AND(MONTHS_BETWEEN (cat_rec.activitydate, SYSDATE) > 10)
THEN give_waiver (cat_rec.pidm);
END IF;

IF eligible_for_register (cat_rec.crnno)
THEN give_waiver (cat_rec.pidm);
END IF;

• Avoid IF With Boolean Variables:

IF hiredate < SYSDATE
THEN date_in_past := TRUE;
ELSE date_in_past := FALSE;
END IF;

With this: date_in_past := hiredate < SYSDATE;

• Avoid Unstructured Exits from Loops/span>
• Do not EXIT or RETURN out of a FOR loop.
• A FOR loop should only be used when you want to execute the body a fixed number of times.
• Stay for the duration or use a different loop construct.
• Do not use the EXIT syntax in a WHILE loop.
• The loop should be terminated only when the condition in the boundary evaluates to FALSE.

Back to Top

Data Analytics & Reporting Team (DART)IT Analytics, SVC4010, Contact Us