PL/SQL & SQL CODING GUIDELINES – Part 4

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

AGENDA
DML and SQL
Reference
Q&A
2

26. ALWAYS SPECIFY THE TARGET COLUMNS
WHEN EXECUTING AN INSERT COMMAND.

BAD
INSERT INTO
messages
VALUES
(l_mess_no ,l_mess_typ ,l_mess_text );

GOOD
INSERT INTO
messages
(mess_no ,mess_typ ,mess_text )
VALUES
(l_mess_no ,l_mess_typ ,l_mess_text );

27. ALWAYS USE TABLE ALIASES WHEN YOUR SQL
STATEMENT INVOLVES MORE THAN ONE SOURCE.

GOOD
SELECT
a.pid ,a.name ,a.birthday ,b.country
FROM person a JOIN country b ON (a.cid = b.cid)
WHERE …

28. TRY TO USE ANSI-JOIN SYNTAX, IF
SUPPORTED BY YOUR ORACLE VERSION.

29. TRY TO USE ANCHORED RECORDS AS
TARGETS FOR YOUR CURSORS.

BAD
DECLARE
CURSOR c_user IS
SELECT user_id, firstname, lastname
FROM user;
l_user_id user.user_id%TYPE;
l_firstname user.firstname%TYPE;
l_lastname user.lastname%TYPE;
BEGIN
OPEN c_user;
FETCH c_user INTO l_user_id, l_firstname, l_lastname;
WHILE c_user%FOUND
LOOP
FETCH c_user INTO l_user_id, l_firstname, l_lastname;
END LOOP;
CLOSE c_user;
END;

GOOD
DECLARE
CURSOR c_user IS
SELECT user_id, firstname, lastname
FROM user;
r_user c_user%ROWTYPE;
BEGIN
OPEN c_user;
FETCH c_user INTO r_user;
<<process_user>>
WHILE c_user%FOUND
LOOP
FETCH c_user INTO r_user;
END LOOP process_user;
CLOSE c_user;
END;

30. USE BULK OPERATIONS (BULK COLLECT,
FORALL) WHENEVER YOU HAVE TO REPEATEDLY
EXECUTE A DML OR SELECT COMMAND FOR
MORE THAN 4 TIMES.

BAD
DECLARE
TYPE t_employee_type IS TABLE OF
emp.empno%TYPE;
t_employees t_employee_type :=
t_employee_type(7369,7698,7839,7844,7876);
BEGIN
FOR i IN 1..t_employees.COUNT()
LOOP
UPDATE emp SET sal = sal * 1.1
WHERE empno = t_employees(i);
END LOOP;
END;

GOOD
DECLARE
TYPE t_employee_type IS TABLE OF
emp.empno%TYPE;
t_employees t_employee_type :=
t_employee_type(7369,7698,7839,7844,7876);
BEGIN
FORALL i IN 1..t_employees.COUNT()
UPDATE emp SET sal = sal * 1.1
WHERE empno = t_employees(i);
END;

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
19

PL/SQL & SQL CODING GUIDELINES – Part 4

More Related Content

Viewers also liked

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

More from Larry Nung

Recently uploaded

PL/SQL & SQL CODING GUIDELINES – Part 4