Stephan Petit – GS/ASE-EDS
Oracle Tutorials
PL/SQL Best Practices
2013-05-21 EDMS Doc. 1263582 v.2 2
PL/SQL Best Practices - Agenda
• Why best practices ?
• The Black Box Paradigm
• Coding conventions
• Why coding conventions ?
• One set of coding conventions that works
• Error handling
• Trapping
• Reporting
• Recovering
• Summary
• References
2013-05-21 EDMS Doc. 1263582 v.2 3
Why Best Practices ?
2013-05-21 EDMS Doc. 1263582 v.2 4
Why Best Practices ?
• Proven efficiency
• Tuned over the years
• Shared by many
• Bring more efficiency in coding/maintenance
• Avoid common mistakes
2013-05-21 EDMS Doc. 1263582 v.2 5
The Black Box Paradigm
2013-05-21 EDMS Doc. 1263582 v.2 6
The Black Box Paradigm
• Not dedicated to PL/SQL
• It works very well with PL/SQL !
• A Black Box:
• Performs a well identified action
• Autonomously : all steps from a to z
• Uniquely : two different boxes cannot do the same action
• Has a clear list of input necessary for its action
• Always checks the input
• Checks the action is allowed
• Always returns an output (status/data)
2013-05-21 EDMS Doc. 1263582 v.2 7
The Black Box Paradigm
Actio
n
Input A Output
• mandatory parameters • status (done/warning/error)
• optional parameters • data (fetched/computed)
• user id (action allowed ?) • user interface
2013-05-21 EDMS Doc. 1263582 v.2 8
The Black Box Paradigm
Actio
n
A
How it works inside is not your business !
The input and output are for you,
the rest is the job of the box
2013-05-21 EDMS Doc. 1263582 v.2 9
The Black Box Paradigm
Action
C
Action Action
A B OR
Action
D
Boxes can be combined to implement more complex actions
2013-05-21 EDMS Doc. 1263582 v.2 10
The Black Box Paradigm
Action
Z
Leading to new black boxes ! Etc…
2013-05-21 EDMS Doc. 1263582 v.2 11
PL/SQL & Black Boxes
• Being involved in programming, you will:
• Use existing black boxes
• Write new black boxes
• In both cases:
• You want to be able to trust them
• You want to be able to understand then, maintain
or debug them, even years after they were created
• Consider every single PL/SQL procedure or
function as a black box
2013-05-21 EDMS Doc. 1263582 v.2 12
PL/SQL Modules Classification
• It is advisable to classify PL/SQL modules
according to the type of actions they perform
• Better modularity
• Better reusability
• Prevents from code duplication
• In the end: powerful library of modules, like
with OO approach
2013-05-21 EDMS Doc. 1263582 v.2 13
PL/SQL Modules Classification
• One efficient PL/SQL modules classification:
• Data action (insert, update, delete)
• Data fecthing / computation Kernel
• Data checking / authorization
• User interface display Interface
2013-05-21 EDMS Doc. 1263582 v.2 14
PL/SQL Module Grouping
• It is advisable to group PL/SQL modules by
themes
• Easier to find the module you need
• Use packages
• Example: one package for data checkers, one for
authorization checkers etc…
2013-05-21 EDMS Doc. 1263582 v.2 15
Coding Conventions
2013-05-21 EDMS Doc. 1263582 v.2 16
Why Coding Conventions ?
• Quick understanding
• Code is easy to read
• Code structure visible in a glance
• Easier code sharing
• Less misunderstanding
2013-05-21 EDMS Doc. 1263582 v.2 18
Why Coding Conventions ?
• Quick understanding
• Reliability
• Easier code review
• Code is understandable
• Obvious bugs are smashed
• Easier to find a reviewer !
2013-05-21 EDMS Doc. 1263582 v.2 19
Why Coding Conventions ?
• Quick understanding
• Reliability
• Maintainability
• Easier debugging
• Easier modifications
• Crucial for long lifetime systems and quick
turnover in teams
2013-05-21 EDMS Doc. 1263582 v.2 20
Why Coding Conventions ?
• Quick understanding
• Reliability
• Maintainability
• Security
• Systematic use of proven code patterns
• Example: use of bind variables against SQL
injection
2013-05-21 EDMS Doc. 1263582 v.2 21
Why Coding Conventions ?
• Quick understanding
• Reliability
• Maintainability
• Security
• Trainability
• Crucial when quick turnover in teams
• Any team member can train any new comer the
same way
2013-05-21 EDMS Doc. 1263582 v.2 22
Why Coding Conventions ?
• Quick understanding
• Reliability
• Maintainability
• Security
• Trainability
• Speed in coding
• Prevents from reinventing the wheel
• Less thinking about « style »
• Easier reuse of existing pieces of code
2013-05-21 EDMS Doc. 1263582 v.2 23
Why Coding Conventions ?
• Quick understanding
• Reliability
• Maintainability
• Security
• Trainability
• Speed in coding
• Error handling
• Errors are handled in a systematic and standard
way
2013-05-21 EDMS Doc. 1263582 v.2 24
Why Coding Conventions ?
• Quick understanding
• Reliability
• Maintainability
• Security
• Trainability
• Speed in coding
• Error handling
Coding Conventions bring great things
but they require some efforts
2013-05-21 EDMS Doc. 1263582 v.2 25
Coding Conventions
One example
2013-05-21 EDMS Doc. 1263582 v.2 26
One Set of Coding Conventions
• The following coding conventions are being
in use in GS/ASE for more than 15 years
• They have proven their efficiency
• They are given as an example, other
conventions may also be good
• The most important:
Have and follow coding conventions !
2013-05-21 EDMS Doc. 1263582 v.2 27
Coding Conventions: Case
• Use UPPER CASE for:
• SQL and PL/SQL keywords
• Module names
• Exceptions
• Constants and types
• Use lower case for
• Variables
• Comments
• Tables, views etc… names
• Column names
2013-05-21 EDMS Doc. 1263582 v.2 28
Coding Conventions: Comments
• Use -- instead of /*…*/
• Easier to comment a whole block of code when
debugging
BEGIN
IF p_book_id < 0 THEN
-- This case should not happen unless the book was lost
-- Carry on by checking the list of lost books
…
END IF;
END;
2013-05-21 EDMS Doc. 1263582 v.2 29
Coding Conventions: Naming
• Parameter naming
• p_name
• Local variable naming
• l_name
• Constant naming
• C_NAME
• Type naming
• T_NAME
• etc…
2013-05-21 EDMS Doc. 1263582 v.2 30
Coding Conventions: Indenting
• General indentation:
• Two blanks indicate a new logical block
• Example
BEGIN
l_author := ‘Pierre Boulle’;
IF p_book_id = 12345 THEN
FOR l_counter IN 1..100 LOOP
…
END LOOP;
END IF;
END;
2013-05-21 EDMS Doc. 1263582 v.2 31
Coding Conventions: Indenting
• SELECT statement:
SELECT editor
,publication_date
,title
FROM books
WHERE book_id = 12345
OR ( title = ‘Planet of the Apes’
AND author = ‘Pierre Boulle’
)
ORDER BY title;
2013-05-21 EDMS Doc. 1263582 v.2 32
Coding Conventions: Indenting
• INSERT statement:
• It is much safer to specify the column names
INSERT INTO books (
book_id
,title
,author
)
VALUES (
12345
,’Planet of the Apes’
,’Pierre Boulle’
);
2013-05-21 EDMS Doc. 1263582 v.2 33
Coding Conventions: Indenting
• IF statement:
• Important: make sure there is always an ELSE
statement
IF l_var IS NULL THEN
…
ELSE
IF l_var > 0 AND l_var < 100 THEN
…
ELSE
…
END IF;
END IF;
2013-05-21 EDMS Doc. 1263582 v.2 34
Coding Conventions: Indenting
• Concatenation:
l_text := ‘Today we are’
||TO_CHAR(SYSDATE, ‘DD-MM-YYYY’)
||’ and the time is ‘
||TO_CHAR(SYSDATE, ‘HH24:MI’);
2013-05-21 EDMS Doc. 1263582 v.2 35
Coding Conventions: Indenting
• Commas:
• Better at the beginning of each line, rather than
at the end (lines are easier to add or remove)
SELECT col_1
,col_2
,col_3
FROM table
WHERE col_1 > 0
AND col_2 IS NOT NULL
AND col_3 LIKE ‘Hello%’;
2013-05-21 EDMS Doc. 1263582 v.2 36
Coding Conventions: Parameters
• To declare a procedure
PROCEDURE GET$BOOK_AUTHOR(
p_book_id IN NUMBER := NULL
,p_title IN VARCHAR2 := NULL
,p_author OUT VARCHAR2
) IS
BEGIN
…
END;
• Advice: if a parameter is optional, use NULL as
default value for easier debugging
2013-05-21 EDMS Doc. 1263582 v.2 37
Coding Conventions: Parameters
• To call a procedure, use the syntax =>
• No ambiguity regarding which parameter gets
which value
l_author [Link]%TYPE;
KNL_LIBRARY.GET$BOOK_AUTHOR(
p_book_id => 12345
,p_author => l_author
);
2013-05-21 EDMS Doc. 1263582 v.2 38
Coding Conventions: Constants
• Constants (declared in package headers) are
a must when strings or numbers have to be
compared
C_ANSWER_1 CONSTANT VARCHAR2(50) := ‘Blue’;
IF p_answer = ‘blue’ THEN
…
END IF;
IF p_answer = C_ANSWER_1 THEN
…
END IF;
2013-05-21 EDMS Doc. 1263582 v.2 39
Coding Conventions: Dynamic Code
• Use bind variables
• Very good protection against code injection
l_statement := ‘INSERT INTO log_table (
log_date
,log_text
)
VALUES (
:l_date
,:l_text
)’;
EXECUTE IMMEDIATE l_statement
USING IN SYSDATE
,IN ‘Hello World !’;
2013-05-21 EDMS Doc. 1263582 v.2 40
Coding Conventions: Metadata
• Very useful: a block of comments before all modules
/*-----------------------------------------------------------------------*/
/* */
/* Module : EXE$PROCEDURE_NAME */
/* Goal : Short description of the module/procedure. */
/* Keywords : Few keywords describing what the module does. */
/* Type : CHECK INTERFACE DATA_ACTION DATA_RETRIEVER */
/* */
/*-----------------------------------------------------------------------*/
/* Description: */
/* */
/* Long description of the procedure: its goal. */
/* Explanation about parameters (Input and Output). */
/* How the procedure works, the "tricks", etc. */
/* */
/*-----------------------------------------------------------------------*/
/* History: */
/* */
/* YYYY-MM-DD : First name and Name - Creation. */
/* */
/* YYYY-MM-DD : First name and Name - Review */
/* */
/* YYYY-MM-DD : First name and Name */
/* Description of the modification. */
/* */
/*-----------------------------------------------------------------------*/
PROCEDURE EXE$PROCEDURE_NAME(
p_param1 IN VARCHAR2
…
2013-05-21 EDMS Doc. 1263582 v.2 41
Error Handling
2013-05-21 EDMS Doc. 1263582 v.2 42
Error Handling
• Errors can produce
• A crash of the system
• A result that is not correct (without crashing)
or not understandable
• Lots of time may be spent on support /
debugging
• Hence the importance of instrumenting the code
• Three types of error handling
• Trapping
• Reporting
• Recovering
2013-05-21 EDMS Doc. 1263582 v.2 43
Error Handling: Trapping
• Use custom exceptions
• Advice: always have a ‘when others’ exception
• Possibility to add useful info in case of crash
BEGIN
…
EXCEPTION
WHEN L_MY_EXCEPTION THEN
-- Specific treatment for this error
…
WHEN OTHERS THEN
-- Generic handling (output of parameters for ex.)
…
END;
2013-05-21 EDMS Doc. 1263582 v.2 44
Error Handling: Reporting
• Once caught, errors have to be reported
• To the system manager
• System values, parameters, failing module name
etc…
• To the user
• Friendly and clear texts
• From a module to its caller
• Stuff that can be used by a piece of code to react the
best possible way
• Error messages
• For humans: text
• For machines: codes
2013-05-21 EDMS Doc. 1263582 v.2 45
Error Handling: Reporting
• Basic skeleton of a kernel stored procedure (1/3)
PROCEDURE GET$BOOK_AUTHOR(
p_book_id IN NUMBER := NULL
,p_title IN VARCHAR2 := NULL
,p_author OUT VARCHAR2
,p_exitcode OUT NUMBER
,p_exittext OUT VARCHAR2
) IS
L_PB_FATAL EXCEPTION;
BEGIN
p_exitcode := 0;
p_exittext := NULL;
Systematically in
…
all kernel
EXCEPTION
procedures
…
END; -- GET$BOOK_AUTHOR
2013-05-21 EDMS Doc. 1263582 v.2 46
Error Handling: Reporting
• Basic skeleton of a kernel stored procedure (2/3)
BEGIN
…
IF p_book_id IS NULL AND p_title IS NULL THEN
-- We have no input to compute the author of the book !
p_exitcode := 20150; -- Invalid input
p_exittext := ‘At least an id or a title has to be provided’;
RAISE L_PB_FATAL;
END IF;
…
EXCEPTION
WHEN L_PB_FATAL THEN
IF p_exitcode = 0 THEN
p_exitcode := 20000; -- Error not documented
END IF;
END;
2013-05-21 EDMS Doc. 1263582 v.2 47
Error Handling: Reporting
• Basic skeleton of a kernel stored procedure (3/3)
BEGIN
…
EXCEPTION
WHEN L_PB_FATAL THEN
IF p_exitcode = 0 THEN
p_exitcode := 20000; -- Error not documented
END IF; The original error is
WHEN OTHERS THEN forwarded, with
p_exitcode := SQLCODE; more intersting info
p_exittext := SUBSTR(‘Unexpected error: ‘
||SQLERRM
||’ in GET$BOOK_AUTHOR with parameters ‘
||NVL(p_book_id, ‘NULL’)
||’. Please contact [Link]@[Link]’;
END;
2013-05-21 EDMS Doc. 1263582 v.2 48
Error Handling: Reporting
• Standard call to a kernel module:
BEGIN
PROCEDURE GET$BOOK_DATA( KNL_LIBRARY.GET$BOOK_AUTHOR(
p_book_id IN NUMBER p_book_id => p_book_id
,p_author OUT VARCHAR2 ,p_author => p_author
,p_editor OUT VARCHAR2 ,p_exitcode => p_exitcode
,p_exitcode OUT NUMBER ,p_exittext => p_exittext
,p_exittext OUT VARCHAR2 );
) IS IF p_exitcode <> 0 THEN
L_PB_FATAL EXCEPTION; RAISE L_PB_FATAL;
END IF;
…
EXCEPTION
WHEN L_PB_FATAL THEN
…
END;
2013-05-21 EDMS Doc. 1263582 v.2 49
Error Handling: Recovering
• What if a data action procedure fails ?
Proc.
A
Input Output
2013-05-21 EDMS Doc. 1263582 v.2 50
Error Handling: Recovering
• What if a data action procedure fails ?
Proc.
A Success
Input Output
2013-05-21 EDMS Doc. 1263582 v.2 51
Error Handling: Recovering
• What if a data action procedure fails ?
It’s ok to fail
but in a
correct way !
Proc. Good
A Failure
NOT good !
Input
2013-05-21 EDMS Doc. 1263582 v.2 52
Error Handling: Recovering
• Use savepoints in all data action modules
PROCEDURE REGISTER$BOOK(
…
,p_exitcode OUT NUMBER
,p_exittext OUT VARCHAR2
) IS
L_PB_FATAL EXCEPTION;
BEGIN
SAVEPOINT BEFORE_REGISTERING_BOOK;
…
EXCEPTION
WHEN L_PB_FATAL THEN
ROLLBACK TO BEFORE_REGISTERING_BOOK;
WHEN OTHERS THEN
ROLLBACK TO BEFORE_REGISTERING_BOOK;
END; -- GET$BOOK_AUTHOR
2013-05-21 EDMS Doc. 1263582 v.2 53
Error Handling: Procs vs. Funcs
• What about functions ?
• Functions should return one single value and
have no OUT parameters (although its is
possible)
• Therefore, difficult to have a precise error reporting
• Functions must return something
• What does a NULL return mean ? Error or not ?
• Advice: use functions only for very simple
computations, that never crash (!)
2013-05-21 EDMS Doc. 1263582 v.2 54
Error Handling: Display Modules
• Test first. Display second.
• First check all parameters
(using kernel modules)
• Then compute everything that can be computed
(idem)
• If no error was found, display the interface,
otherwise gracefully show a nice error message
2013-05-21 EDMS Doc. 1263582 v.2 55
Summary
2013-05-21 EDMS Doc. 1263582 v.2 56
Summary
• Keep the black box mechanism in mind
• Build that great library you’d love to use !
• Asemble components like Lego elements
®
• Use coding conventions
• It’s a treat to yourself in the future
• It’s a sign of respect to your colleagues today
• Instrument your code as much as possibe
• The worse will always happen at the worst
moment !
2013-05-21 EDMS Doc. 1263582 v.2 57
References
« Expert PL/SQL Practices »
Apress edition
ISBN13: 978-1-4302-3485-2
August 2011
2013-05-21 EDMS Doc. 1263582 v.2 58
Thank you for your attention !
[Link]@[Link]
2013-05-21 EDMS Doc. 1263582 v.2 59