PL/SQL is Oracle's procedural extension to industry-standard SQL. PL/SQL naturally,
efficiently, and safely extends SQL.
Its primary strength is in providing a server-side, stored procedural language that is easy-to-use, seamless with SQL,
robust, portable, and secure. Thus, it offers a platform for robust, high-performing enterprise applications.
PL/SQL is an implicit part of the Oracle Database.
PL/SQL is the preferred language for accessing Oracle databases. PL/SQL is completely seamless with SQL, sharing the
same datatypes, and is therefore ideal for SQL intensive applications - there are no data type conversions necessary
and the application logic is simply procedures extending SQL or massaging SQL tables. In a scenario with lots of database
read/writes and very little computation, code written in PL/SQL will always run significantly faster then similar code
written in Java.
My code normally contains fewer than 60 lines from BEGIN to END of a block per routine. My code is efficient,
doing bulk processing as much as possible. Bind variables have a great impact on the performance of a system.
Without them, performance would be terrible. Hence I manually bind my variables. I pass my code to fellow developers for
objective points of view.
A common problem I avoid is doing hard coding that would prove to be a hassle to maintain. I use "archored" datatypes
that is anchored directly to the table's column. Every time a procedure is compiled, PL/SQL will look up the datatype of the column
from the data dictionary and use that in the compiled code. So if the DBA does make the column alphanumeric, no harm will
befall this code.
I also use subtypes. A SUBTYPE is an alias for another already defined datatype. In the following "employee types"
(employee_tp) package specification, for example, I define a new datatype called
fullname_t,
which is really nothing more than another name for
VARCHAR2(1000):
1 CREATE OR REPLACE PACKAGE employee_rp
2 AS
3 SUBTYPE fullname_t IS VARCHAR2 (1000);
4 END employee_rp;
With this SUBTYPE in place, I can adjust my declaration to
l_fullname employee_tp.fullname_t;
Data is always fetched into records that are anchored directly to the table's row.
This keeps us in sync with any changes made to the table; and Reference Cursors (REF CURSOR) are used to
return rows from target tables.
I occasionally automate recompilation of code using Oracle's DBMS_ UTILITY.COMPILE_SCHEMA and DBMS_SCHEDULER/DBMS_JOB
The assigning of values to PL/SQL variables in SQL statements is called
binding.
The binding of an entire collection at once is called bulk binding. Bind variables have a great impact on the performance of
a system (more efficient use of SGA). Without them, performance will be terrible.
I develop/follow naming conventions; for example, UPPERCASE constants, mixed-case composite-name variables ("myVariable").
I try to keep the actual SQL code to a minimum. The less SQL you write, the better.
I try to keep global variables down to a minimum.
I keep the executable sections small and modular to avoid "spaghetti"
And I share my code amongst fellow developers to get an objective view of what's written.
