PL/SQL Coding Guidelines - Part 3

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

AGENDA
Numeric Data Types
Character Data Types
Boolean Data Types
Large Objects
Reference
Q & A
2

VARIABLES & TYPES
Numeric Data Types

18. AVOID DECLARING NUMBER VARIABLES
OR SUBTYPES WITH NO PRECISION.

BAD
DECLARE
v_number number;
BEGIN
...
END;

GOOD
DECLARE
v_number number(9, 2);
BEGIN
...
END;

19. TRY TO USE PLS_INTEGER INSTEAD OF
NUMBER FOR ARITHMETIC OPERATIONS WITH
INTEGER VALUES (NO DECIMAL POINT).

VARIABLES & TYPES
Character Data Types

20. AVOID USING CHAR DATA TYPE.

BAD
DECLARE
v_str char(4000) := 'test';
BEGIN
DBMS_OUTPUT.PUT_LINE(Length(v_str));
END;

GOOD
DECLARE
v_str varchar2(4000) := 'test';
BEGIN
DBMS_OUTPUT.PUT_LINE(Length(v_str));
END;

21. AVOID USING VARCHAR DATA TYPE.

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

GOOD
DECLARE
v_str varchar2(4000);
BEGIN
…
END;

22. NEVER USE ZERO-LENGTH STRINGS TO
SUBSTITUTE NULL.

BAD
DECLARE
v_str varchar2(4000) := '';
BEGIN
DBMS_OUTPUT.PUT_LINE(CASE WHEN v_str is
null THEN 'null' ELSE 'not null' END);
END;

GOOD
DECLARE
v_str varchar2(4000);
BEGIN
DBMS_OUTPUT.PUT_LINE(CASE WHEN v_str is
null THEN 'null' ELSE 'not null' END);
END;

23. ALWAYS DEFINE YOUR VARCHAR2
VARIABLES USING CHAR SEMANTIC.

BAD
DECLARE
v_str varchar(200);
BEGIN
...
END;

GOOD
DECLARE
v_str varchar(200 char);
BEGIN
...
END;

VARIABLES & TYPES
Boolean Data Types

24. TRY TO USE BOOLEAN DATA TYPE FOR
VALUES WITH DUAL MEANING.

BAD
DECLARE
v_IsBigger number(1) := 1;
BEGIN
DBMS_OUTPUT.PUT_LINE(CASE WHEN
v_IsBigger = 1 THEN 'True' ELSE 'False' END);
END;

GOOD
DECLARE
v_IsBigger BOOLEAN := true;
BEGIN
DBMS_OUTPUT.PUT_LINE(CASE WHEN
v_IsBigger THEN 'True' ELSE 'False' END);
END;

VARIABLES & TYPES
Large Objects

25. AVOID USING THE LONG AND LONG
RAW DATA TYPES.

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
28

PL/SQL Coding Guidelines - Part 3

More Related Content

Similar to PL/SQL Coding Guidelines - Part 3

More from Larry Nung

Recently uploaded

PL/SQL Coding Guidelines - Part 3

Editor's Notes