PL/SQL & SQL CODING GUIDELINES – Part 5

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

AGENDA
Control Structures
CURSOR
CASE / IF / DECODE / NVL / NVL2 / COALESCE
Reference
Q&A
2

31. ALWAYS USE %NOTFOUND INSTEAD OF NOT
%FOUND TO CHECK WHETHER A CURSOR WAS
SUCCESSFUL

BAD
LOOP
FETCH c_employees INTO r_employee;
EXIT WHEN NOT c_employees%FOUND;
...
END LOOP;

GOOD
LOOP
FETCH c_employees INTO r_employee;
EXIT WHEN c_employees%NOTFOUND;
...
END LOOP;

32. AVOID USING %NOTFOUND DIRECTLY AFTER
THE FETCH WHEN WORKING WITH BULK
OPERATIONS AND LIMIT CLAUSE. USE
[ARRAY_NAME].COUNT() INSTEAD TO CHECK
WHETHER FURTHER FETCHS ARE NEEDED.

BAD
-- This example will only show 10 of 14 employees
DECLARE
TYPE t_employee_type IS TABLE OF emp%ROWTYPE;
t_employees t_employee_type;
CURSOR c_emp
IS SELECT *
FROM emp ORDER BY empno;
BEGIN
OPEN c_emp;
<<process_emp>>
LOOP
FETCH c_emp BULK COLLECT INTO t_employees LIMIT 5;
EXIT process_emp WHEN c_emp%NOTFOUND;
<<display_emp>>
FOR i IN 1..t_employees.COUNT()
LOOP
sys.dbms_output.put_line(t_employees(i).ename);
END LOOP display_emp;
END LOOP process_emp;
CLOSE c_emp;
END;

GOOD
-- This example does 4 fetches where 3 were sufficient
DECLARE
CURSOR c_emp
IS SELECT *
FROM emp
ORDER BY empno;
BEGIN
OPEN c_emp;
<<process_emp>>
LOOP
EXIT WHEN t_employees.COUNT() = 0;
<<display_emp>>
LOOP
CLOSE c_emp;
END;

GOOD
-- This examples does the trick (3 fetches only and process all rows)
DECLARE
CURSOR c_emp
IS SELECT *
FROM emp
ORDER BY empno;
BEGIN
OPEN c_emp;
<<process_emp>>
LOOP
<<display_emp>>
LOOP
EXIT WHEN t_employees.COUNT() = 0 OR c_emp%NOTFOUND;
CLOSE c_emp;
END;

33. ALWAYS CLOSE LOCALLY OPENED
CURSORS

BAD
CREATE PROCEDURE not_close_cursor (out_count OUT
INTEGER)
AS
CURSOR c1
IS
SELECT COUNT (*)
FROM all_users;
BEGIN
out_count := 0;
OPEN c1;
FETCH c1
INTO out_count;
END not_close_cursor;
...

GOOD
CREATE PROCEDURE close_cursor (out_count OUT
INTEGER)
AS
CURSOR c1
IS
SELECT COUNT (*)
FROM all_users;
BEGIN
out_count := 0;
OPEN c1;
FETCH c1
INTO out_count;
CLOSE c1
END close_cursor;

34. AVOID PROCEDURE OR FUNCTION CALLS
BETWEEN A SQL OPERATION AND AN IMPLICIT
CURSOR

BAD
CREATE PROCEDURE remove_emp_and_process (in_id
IN emp.empno%TYPE)
AS
BEGIN
DELETE FROM emp
WHERE empno = in_id
RETURNING deptno INTO l_deptno;
process_department (...);
IF SQL%ROWCOUNT > 1
THEN
-- Too many rows deleted! Rollback and recover...
ROLLBACK;
END IF;
END remove_emp_and_process;

CONTROL STRUCTURES
CASE / IF / DECODE / NVL / NVL2 / COALESCE

35. TRY TO USE CASE RATHER THAN AN IF
STATEMENT WITH MULTIPLE ELSIF PATHS

BAD
IF l_color = 'red'
THEN
...
ELSIF l_color = 'blue'
THEN
...
ELSIF l_color = 'black'
THEN
...

GOOD
CASE l_color
WHEN 'red' THEN ...
WHEN 'blue' THEN ...
WHEN 'black' THEN ...
END

36. TRY TO USE CASE RATHER THAN
DECODE

BAD
BEGIN
SELECT DECODE(dummy, 'A', 1
, 'B', 2
, 'C', 3
, 'D', 4
, 'E', 5
, 'F', 6
, 7)
INTO l_result
FROM dual;
...

GOOD
BEGIN
l_result := CASE dummy
WHEN 'A' THEN 1
WHEN 'B' THEN 2
WHEN 'C' THEN 3
WHEN 'D' THEN 4
WHEN 'E' THEN 5
WHEN 'F' THEN 6
ELSE 7
END;
...

37. ALWAYS USE COALESCE INSTEAD OF NVL, IF
PARAMETER 2 OF THE NVL FUNCTION IS A
FUNCTION CALL OR A SELECT STATEMENT

BAD
SELECT NVL(dummy, function_call())
FROM dual;

GOOD
SELECT COALESCE(dummy, function_call())
FROM dual;

38. ALWAYS USE CASE INSTEAD OF NVL2 IF
PARAMETER 2 OR 3 OF NVL2 IS EITHER A
FUNCTION CALL OR A SELECT STATEMENT

BAD
SELECT NVL2(dummy, function_call(), function_call())
FROM dual;

GOOD
SELECT CASE
WHEN dummy IS NULL THEN function_call()
ELSE function_call()
END
FROM dual;

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
30

PL/SQL & SQL CODING GUIDELINES – Part 5

More Related Content

What's hot

Viewers also liked

Similar to PL/SQL & SQL CODING GUIDELINES – Part 5

More from Larry Nung

Recently uploaded

PL/SQL & SQL CODING GUIDELINES – Part 5