PL/SQL & SQL CODING GUIDELINES – Part 8

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

AGENDA
Dynamic SQL
Stored Objects
Reference
Q&A
2

58. ALWAYS USE A STRING VARIABLE TO
EXECUTE DYNAMIC SQL.

BAD
-- Bad
DECLARE
l_empno emp.empno%TYPE := 4711;
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM emp
WHERE epno = :p_empno' USING l_empno;
END;

GOOD
-- Good
DECLARE
l_empno emp.empno%TYPE := 4711;
l_sql VARCHAR2(32767);
BEGIN
l_sql := 'DELETE FROM emp WHERE epno = :p_empno';
EXECUTE IMMEDIATE l_sql USING l_empno;
EXCEPTION
WHEN others
THEN
DBMS_OUTPUT.PUT_LINE(l_sql);
END;

59. TRY TO USE OUTPUT BIND ARGUMENTS IN THE
RETURNING INTO CLAUSE OF DYNAMIC
INSERT, UPDATE, OR DELETE STATEMENTS
RATHER THAN THE USING CLAUSE.

BAD
CREATE OR REPLACE PACKAGE BODY employee_api IS
PROCEDURE upd_salary (in_employee_id IN
employees.employee_id%TYPE ,in_increase_pct IN
types_up.percentage ,out_new_salary OUT
employees.salary%TYPE)
IS
co_sql_stmt CONSTANT types_up.big_string_type := '
UPDATE employees SET salary = salary + (salary / 100 * :1)
WHERE employee_id = :2 RETURNING salary INTO :3';
BEGIN
EXECUTE IMMEDIATE co_sql_stmt
USING in_increase_pct, in_employee_id, OUT
out_new_salary;
END upd_salary;
END employee_api; /

GOOD
PROCEDURE upd_salary (in_employee_id IN
employees.employee_id%TYPE ,in_increase_pct IN
types_up.percentage ,out_new_salary OUT
employees.salary%TYPE)
IS
co_sql_stmt CONSTANT types_up.big_string_type :=
'UPDATE employees SET salary = salary + (salary / 100 * :1)
WHERE employee_id = :2 RETURNING salary INTO :3';
BEGIN
EXECUTE IMMEDIATE co_sql_stmt
USING in_increase_pct, in_employee_id
RETURNING INTO out_new_salary;
END upd_salary;
END employee_api; /

60. TRY TO USE NAMED NOTATION WHEN
CALLING PROGRAM UNITS.

BAD
DECLARE
r_employee employees%rowtype;
co_id CONSTANT employees.employee_id%type :=
107;
BEGIN
r_employee :=
employee_api.employee_by_id(co_id);
END; /

GOOD
DECLARE
co_id CONSTANT employees.employee_id%type :=
107;
BEGIN
r_employee :=
employee_api.employee_by_id(in_employee_id =>
co_id);
END; /

61. ALWAYS ADD THE NAME OF THE PROGRAM
UNIT TO ITS END KEYWORD.

BAD
FUNCTION employee_by_id (in_employee_id IN employees.employee_id%TYPE)
RETURN employees%rowtype IS
BEGIN
SELECT *
INTO r_employee
FROM employees
WHERE employee_id = in_employee_id;
RETURN r_employee;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN TOO_MANY_ROWS THEN
RAISE;
END;
END; /

GOOD
FUNCTION employee_by_id (in_employee_id IN employees.employee_id%TYPE)
RETURN employees%rowtype IS
BEGIN
SELECT *
INTO r_employee
FROM employees
WHERE employee_id = in_employee_id;
RETURN r_employee;
EXCEPTION
NULL;
RAISE;
END employee_by_id;
END employee_api; /

62. ALWAYS USE PARAMETERS OR PULL IN
DEFINITIONS RATHER THAN REFERENCING
EXTERNAL VARIABLES IN A LOCAL PROGRAM UNIT.

BAD
CREATE OR REPLACE PACKAGE BODY EMPLOYEE_API IS
PROCEDURE calc_salary (in_employee_id IN employees.employee_id%TYPE) IS
r_emp employees%rowtype;
FUNCTION commission RETURN NUMBER IS
l_commission employees.salary%TYPE := 0;
BEGIN
IF r_emp.commission_pct IS NOT NULL
THEN
l_commission := r_emp.salary * r_emp.commission_pct;
END IF;
RETURN l_commission;
END commission;
BEGIN
SELECT * INTO r_emp FROM employees WHERE employee_id = in_employee_id;
SYS.DBMS_OUTPUT.PUT_LINE(r_emp.salary + commission());
EXCEPTION
NULL;
NULL;
END calc_salary;
END employee_api; /

GOOD
CREATE OR REPLACE PACKAGE BODY EMPLOYEE_API IS
PROCEDURE calc_salary (in_employee_id IN employees.employee_id%TYPE) IS
r_emp employees%rowtype;
FUNCTION commission (in_salary IN employees.salary%TYPE ,in_comm_pct IN employees.commission_pct%TYPE)
RETURN NUMBER IS
l_commission employees.salary%TYPE := 0;
BEGIN
IF in_comm_pct IS NOT NULL THEN
l_commission := in_salary * in_comm_pct;
END IF;
RETURN l_commission;
END commission;
BEGIN
SELECT * INTO r_emp FROM employees WHERE employee_id = in_employee_id;
SYS.DBMS_OUTPUT.PUT_LINE( r_emp.salary + commission(in_salary => r_emp.salary ,in_comm_pct =>
r_emp.commission_pct) );
EXCEPTION
NULL;
NULL;
END calc_salary;
END employee_api; /

63. ALWAYS ENSURE THAT LOCALLY DEFINED
PROCEDURES OR FUNCTIONS ARE
REFERENCED.

BAD
CREATE OR REPLACE PACKAGE BODY
my_package IS
PROCEDURE my_procedure IS
FUNCTION my_func RETURN NUMBER IS
co_true CONSTANT INTEGER := 1;
BEGIN
RETURN co_true;
END my_func;
BEGIN
NULL;
END my_procedure;
END my_package; /

GOOD
CREATE OR REPLACE PACKAGE BODY
my_package IS
PROCEDURE my_procedure IS
FUNCTION my_func RETURN NUMBER IS
co_true CONSTANT INTEGER := 1;
BEGIN
RETURN co_true;
END my_func;
BEGIN
sys.dbms_output.put_line(my_func());
END my_procedure;
END my_package; /

64. TRY TO REMOVE UNUSED PARAMETERS
OR MODIFY CODE TO USE THE PARAMETER.

BAD
CREATE OR REPLACE PACKAGE BODY department_api IS
FUNCTION name_by_id (in_department_id IN
departments.department_id%TYPE,in_manager_id IN
departments.manager_id%TYPE)
RETURN departments.department_name%TYPE IS
l_department_name departments.department_name%TYPE;
BEGIN
<<find_department>>
BEGIN
SELECT department_name INTO l_department_name FROM
departments WHERE department_id = in_department_id;
EXCEPTION
WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
l_department_name := NULL;
END find_department;
RETURN l_department_name;
END name_by_id;
END department_api; /

GOOD
CREATE OR REPLACE PACKAGE BODY department_api IS
FUNCTION name_by_id (in_department_id IN
departments.department_id%TYPE)
RETURN departments.department_name%TYPE IS
l_department_name departments.department_name%TYPE;
BEGIN
<<find_department>>
BEGIN
SELECT department_name INTO l_department_name FROM
departments WHERE department_id = in_department_id;
EXCEPTION
WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
l_department_name := NULL;
END find_department;
RETURN l_department_name;
END name_by_id;
END department_api; /

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 & SQL CODING GUIDELINES – Part 8

More Related Content

What's hot

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

More from Larry Nung

Recently uploaded

PL/SQL & SQL CODING GUIDELINES – Part 8