SQL and PL/SQL Best Practices
- Use standard comments for version tracking
- Number versions in whole numbers
- Only one statement per line
- Commas should be leading in stacked lists
- Document (in English) what each parm is
- Appropriate grants have to be incorporated into the scripts
- Indentation is one of the most common and effective ways to display a program's logical structure. Programs that are indented are lot easier to read than those that are not.
- Indent and align nested control structures, continuation lines, and embedded units consistently.
- Distinguish between indentation for nested control structures and for continuation lines.
- Use spaces for indentation, not the tab character. Use of three spaces is recommended.
- Comment As you Code
- Documenting after the fact is advisable. By commenting, you are trying to convey to someone else what the program is doing. This always clarifies things in your mind.
- Explain Why - Not the How. Avoid documenting the obvious. Explain why you are doing something, with a function, procedure or a section of a procedure.
- Make Comments Easy to Enter and Maintain
- Comments should reinforce indentation and therefore the logical structure of the program. Always starting your comments in the first column disrupts the logical flow of code. Always indent the comments at the same level as the code which they describe.
- End label for all program units, loops and nested blocks.
- Application code must raise, handle, log and communicate errors in a consistent manner.
- All developers should use common error log proc while handling the exceptions
- Don't hard-code -20NNN error codes and their related messages.
- And if you use the -20NNN codes, maintain a repository of the numbers you have used.
- Performance Analysis and comparison tool
- TKPROF
- SQL*Plus SET TIMING ON
- DBMS_UTILITY.GET_TIME/GET_CPU_TIME
- PL/SQL or TOAD utilities for explain plan.
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.
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.
Nested |
Flat |
IF <condition1> |
THEN |
... |
ELSE |
IF <condition2> |
THEN
THEN
THEN ... |
ELSE |
IF <condition3> |
THEN |
... |
ELSE |
ELSIF <Condition2>
THEN
IF <condition4> THEN ... |
END IF; END IF; END IF; |
ELSIF <Condition3>
END IF; |
IF <condition1> |
THEN
THEN ... |
ELSE |
IF <condition2> |
THEN |
ELSIF <Condition4>
... |
ELSE |
IF <condition3> |
THEN |
... |
ELSE |
END IF;
IF <condition4> THEN ... |
END IF; |
END IF; |
END IF; |
END IF; |
|
- 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.