PL/SQL Coding Guidelines - Part 2

PL/SQL & SQL
CODING GUIDELINES – PART 2
Larry Nung

AGENDA
Variables & Types
Reference
Q & A
2

8. TRY TO USE ANCHORED DECLARATIONS
FOR VARIABLES, CONSTANTS AND TYPES.

BAD
DECLARE
v_empName VARCHAR2(10);
BEGIN
…
END;

GOOD
DECLARE
v_empName emp.ename%TYPE;
BEGIN
…
END;

9.TRY TO HAVE A SINGLE LOCATION TO DEFINE
YOUR TYPES.

10. TRY TO USE SUBTYPES FOR CONSTRUCTS
USED OFTEN IN YOUR APPLICATION.

BAD
DECLARE
v_str VARCHAR2(4000);
BEGIN
…
END;

GOOD
CREATE OR REPLACE PACKAGE PKG_SUBTYPE
AS
SUBTYPE STRING_MAX IS VARCHAR2(4000);
END PKG_SUBTYPE;
DECLARE
v_str PKG_SUBTYPE.STRING_MAX;
BEGIN
...
END;

11. NEVER INITIALIZE VARIABLES WITH NULL.

BAD
DECLARE
v_str VARCHAR2(30) := null;
BEGIN
...
END;

GOOD
DECLARE
v_str VARCHAR2(30);
BEGIN
...
END;

12. AVOID COMPARISONS WITH NULL VALUE,
CONSIDER USING IS [NOT] NULL.

BAD
DECLARE
v_str VARCHAR2(30);
BEGIN
if v_str = null then
…
end if;
END;

GOOD
DECLARE
v_str VARCHAR2(30);
BEGIN
if v_str is null then
…
end if;
END;

13. AVOID INITIALIZING VARIABLES USING
FUNCTIONS IN THE DECLARATION SECTION.

BAD
DECLARE
l_company_name VARCHAR2(30) :=
util_pck.get_company_name(in_id => 47);
BEGIN
…
END;

GOOD
DECLARE
v_str VARCHAR2(30);
BEGIN
<<init>>
BEGIN
v_str := util_pck.get_company_name(inId =>
47);
EXCEPTION
WHEN VALUE_ERROR THEN
...
END init;
END;

14. NEVER OVERLOAD DATA STRUCTURE
USAGES.

BAD
<<main>>
DECLARE
v_str VARCHAR2(30);
BEGIN
<<sub>>
DECLARE
v_str VARCHAR2(4000) ;
BEGIN
…
END sub;
END main;

15. NEVER USE QUOTED IDENTIFIERS.

BAD
<<main>>
DECLARE
"v_str" VARCHAR2(30) ;
BEGIN
…
END main;

16. AVOID USING OVERLY SHORT NAMES FOR
DECLARED OR IMPLICITLY DECLARED IDENTIFIERS.

17. AVOID THE USE OF ROWID OR UROWID

REFERENCE
 Trivadis PL/SQL & SQL Coding Guidelines Version
2.0
 http://www.trivadis.com/sites/default/files/downloads/PL
SQL_and_SQL_Coding_Guidelines_2_0_HiRes.pdf
27

PL/SQL Coding Guidelines - Part 2

More Related Content

Viewers also liked

More from Larry Nung

PL/SQL Coding Guidelines - Part 2