Repository navigation
Draft: Oracle Naming and Programming Standards
This document defines and describes Oracle schema naming and programming conventions as well as best practices to be followed by Apptis developers when developing Citizenship Systems products for the Department of State.
It is expected that all Oracle programmers will adhere to these standards; existing code shall be retrofitted to meet the standards to whatever degree possible, as modifications to that code are required.
This document is part of the Citizenship Systems Process Asset Library (CS PAL). Any changes to this document must be done in accordance with the PAL Change Management Process. A Process Improvement Request (PIR) with recommended changes must be submitted in Rational ClearQuest (CQ). The Software Engineering Process Group (SEPG) is responsible for reviewing and administering changes to this document. The Enterprise Project Managers (PMs) are responsible for approving changes to this plan.
Schema Object Naming Standards define how tables, columns, stored procedures, etc. are named. A list of approved abbreviations or short names (e.g., APPL for Application; NUM for Number rather than NBR) are included here. This list is maintained by the database development group as part of the schema object naming standard.
Object names will be English words and abbreviations, which convey meaning about what the object represents in the system. Only UPPER case will be used with underscores for readability. No other special characters will be used. Where a prefix is appropriate for an object, the prefix will follow UPPER case formatting. Abbreviations for common words should be used across the board; a list of standard abbreviations is provided later in this document.
General naming guidelines:
- Avoid using proprietary acronyms (GDSINC, SQL)
- Avoid using product-specific names, or names whose meaning is subject to change over time (Schema, DCN159)
- Use singular form for table names
- Never name an object with an ORACLE reserved word.
- All Objects must be 30 characters or less.
Some objects should be named in an abbreviated form, when they are part of database object names. Please see DBA staff to suggest other abbreviated forms for inclusion.
Business Name Abbreviation Account ACCT Address ADDR Administration ADMIN Acceptance Facility AF Amount AMT Application APPL Attribute ATTR Business BUS Date DT Description DESC Document DOC Employee EMP Identifier ID Number NUM Parameter PARM Passport PPT Percent PCT Row Identifier RID Sequence SEQ System SYS Unique Identifier UID
2.2 Table Names Data tables are defined as tables containing data specific to the business of the application. Their names may include a prefix, to group tables of similar business purpose. The set of approved data table name prefixes is defined below:
Prefix Definition LKUP Lookup data IMP “Import” or staging data
Non-data tables are used for more generic purpose: error handling, maintenance, work holding space, etc. Their names may also include a prefix and a suffix as well. The suffix is used to indicate a more general grouping by purpose. General definitions of non-data tables, whose naming convention may include a prefix, suffix, or both, are given below:
[All]
Table Group Definitions Prefix Suffix Application Static Tables (code lookup tables) State code and other code lookup tables. LKUP LKUP_ (no suffix) System Utility Tables Tables to support Administrative utilities ADMIN_ (no suffix) Persistent Work Tables Tables to support interim work steps Application Group Name where Appropriate _WORK Staging Tables (For import/export) Tables used to hold imported and exported data Application Group Name where Appropriate (no suffix; use IMP_ prefix) Backup Tables Tables used to back up other tables Application Group Name where Appropriate _BKUP
2.3 Column Names Column name should provide a clear, concise description of the attribute in UPPER case, with underscores for readability. Any system-generated identifiers will always have the suffix ‘_ID’. The full column name will be the table name plus the suffix. All fields that reference a lookup table should include a suffix of ‘_CODE’ when referencing an actual code values versus ‘ID’ when referencing a surrogate integer field. Columns that are foreign keys to another table should preserve the column name across the relationship, if the 30 character limitation can be adhered to. An exception to this rule must be made when there are two foreign key columns representing the same primary key. UID [Unique] is a binary record identifier that is unique across all agencies.
2.4 Index Names
Index names should provide a clear and concise description of the index. A prefix is always used in the index name to indicate the type of index, and should include an underscore separator. The body of the index name includes the table name and may include the name(s) of the columns indexed as long as the 30 character limitation can be met. By type, the definitions are as follows:
Index Type Naming convention Example Primary Key PK_ + Name of Table PK_PTDISAPPL Foreign Key FK_ + Name of Table + ‘_’ + Name of Column (first column name required, rest optional) FK_TRACE_BATCH_ID Alternative Key AK_ + name of Table + ‘_’ + Name of Column This type of index is associated with a unique constraint on a table (i.e., an alternate primary key). AK_ACCT_SSN
Index Key An index that is not related to a primary, foreign, or ‘alternative’ key on the table. IX_Acct_LAST_NAME
2.5 Naming Views, Check Constraints, Defaults, and Rules The naming conventions for views, check constraints, and defaults consist of a prefix, underscore, and descriptive name. The descriptive name of these objects should include the purpose of the code behind the object; the table or column name may be included. A view name should include the purpose and a reference to the main type of data in the view, for example, VW_REJECTED_PPT. Check constraint names must be unique, so a reference to the object table name is appropriate; however, the table name can be embedded in the descriptive name (for example, CHK_APPL_NAME_IS_VALID). Note: Again, such names have a 30 character limit.
Database Object Format Example Default DF_ + table name + column name DF_APPL_SSN View VW_ + name VW_ACCT_PERSON_PROFILE Check Constraint CHK_ + name CHK_APPL_FEE_MAX Materialized View MV_ + name MV_ACCT_PERSON_PROFILE
2.6 Stored Procedure, Function Names
Procedures and Functions may be grouped by purpose or by the name of the entity processed. For example, there may be a group of procedures that manipulate Application data. Those procedures might be named APPL_INSERT, APPL_UPDATE, APPL_DATE, etc. In general, naming procedures with a ‘noun + verb’ format, works well for grouping by purpose. If entity names are not appropriate as a grouping prefix, the table-grouping prefixes in section 2.2 of this document can be used. 3 Overall PL/SQL Programming Style Apply a consistent and effective coding style when writing code:
- Programs are more readable and maintainable
- This standard will be used by all developers on Apptis Citizenship Systems development teams.
- Indent to show the logical flow of your program.
- Use white space to improve readability.
- Use a consistent commenting style that is low in maintenance and high in readability.
- Comment only to add value.
Visually separate the SQL reserved words which identify the separate clauses from the application-specific column and table names. The following table shows how to use right-alignment on the reserved words to create a vertical border between them and the rest of the SQL statement.
Here are some examples for schema SCOTT of this format in use:
SELECT last_name, first_name
FROM scott.employee WHERE department_id = 15 AND hire_date < SYSDATE;
SELECT department_id, SUM (salary) AS total_salary
FROM scott.employee GROUP BY department_id
HAVING total_salary > 100000
ORDER BY total_salary DESC;
INSERT INTO scott.emloyee
(employee_id,...)
VALUES (105, ... );
DELETE scott.employee
WHERE department_id = 15
OR department_id = 16;
UPDATE scott.employee
SET hire_date = SYSDATE WHERE hire_date IS NULL AND termination_date IS NULL;
SELECT last_name, first_name
FROM scott.employee emp
JOIN scott.department dept
ON dept.department_id = emp.department_id
WHERE hire_date < SYSDATE;
While the GROUP BY and ORDER BY alignment isn't exactly right-aligned to SELECT, the primary words ("GROUP" and "ORDER") are aligned. Notice that within each of the WHERE and HAVING clauses, we right-align the AND and OR Boolean connectors under the WHERE keyword. This right-alignment makes it very easy to identify the different clauses of the SQL statement, particularly with extended SELECTs. 3.1.1 Don't Skimp On the Use of Line Separation Within clauses, such separation makes the SQL statement easier to read: 1. Placing each expression of the WHERE clause on its own line. 2. Using a separate line for each expression in the select list of a SELECT statement. 3. Placing each table in the FROM clause on its own line. 4. Placing each separate assignment in a SET clause of the UPDATE statement on its own line. 5. Here are some illustrations of these conventions for schema SCOTT: SELECT last_name,
C.name,
MAX (SH.salary) best_salary_ever
FROM scott.employee E
JOIN Scott.company C
ON E.company_id = C.company_id
JOIN Scott.salary_history SH
ON E.employee_id = SH.employee_id
WHERE E.hire_date > ADD_MONTHS (SYSDATE, -60);
UPDATE scott.employee
4 Variable Naming Conventions 4.1 Establish Clear Variable Naming Conventions Database administrators have long insisted on, and usually enforced, strict naming conventions for tables, columns, and other database objects. The advantage of these conventions is clear: At a single glance, anyone who knows the conventions (and even many who do not) will understand the type of data contained in that column or table. If conventions for columns make sense in the database, they also make sense in PL/SQL programs. In general, follow the same guidelines for naming variables that you follow for naming columns in your development environment. In many cases your variables simply represent database values, having been fetched from a table into those variables via a cursor. Beyond these database-sourced variables, however, there are a number of types of variables that you will use again and again in your code. However, if your column names have underscores for readability, removal of the underscore may be replaced with Camel Case in variables used to hold column data. Generally, try to come up with a variable name suffix that will help identify the representative data. This convention not only limits the need for additional comments, it can also improve the quality of code, because the variable type often implies rules for its use. If you can identify the variable type by its name, then you are more likely to use the variable properly.
Below is a list of variable types and suggested naming conventions for each: 4.1.1 Module Parameter A parameter has one of three modes: IN, OUT, or IN OUT. These parameter variables should be prefixed with a "P”. Examples:
PROCEDURE CALC_SALES
(p_company_id IN NUMBER,
p_call_type IN VARCHAR2,
p_company_nm IN OUT VARCHAR2)
In addition to making the meaning clear, the prefixes also helps differentiate the variables from the names of columns in the code, if they happen to be the same.
4.1.2 Program Variables Program variables are used as placeholders for the program execution. The examples could be a placeholder to hold intermediate values in the computation, holding of values fetched from columns and so on. There are two types of program variables – local and global. There is no difference between the two, unless the same name is used in both contexts. To avoid confusion, employ a simple strategy to name variables differently, prefix local variables with "l_" (the letter L) and global variables with "g_". 4.1.3 Package Variables - Scope When two variables are defined inside the package – one in the package specification and one inside the package body procedures, the precedence of the variables may be contrary to what you expected. This could potentially introduce several bugs into your program. Instead, follow a simple approach of naming the variables with a meaningful prefix:
- "g_" for global (outside any procedure of the package in the package specification).
Example:
Cursor:
Append a suffix of _cur to the cursor name, as in:
CURSOR company_cur IS ...;
In case of implicit cursors, use the naming convention that implies the cursor nature. Since implicit cursors are both cursors and records, suffix the word "crec" to denote the variables, as shown in the example below:
FOR company_crec in ( SELECT …
FROM company)
LOOP 4.1.6 FOR Loop Index There are two kinds of FOR loops, numeric and cursor, each with a corresponding numeric or record loop index. In a numeric loop incorporate the word "index" or "counter" or some similar suffix into the name of the loop index, such as:
FOR year_idx IN 1 .. 12
In a cursor loop, the name of the record which serves as a loop index should follow the convention described above for records.
FOR emp_rec IN emp_cur
4.1.7 Named Constant A named constant cannot be changed after it gets its default value at the time of declaration. Prefix the name with a c_:
c_last_date CONSTANT DATE := SYSDATE:
This way a programmer is less likely to try to use the constant in an inappropriate manner. 4.1.8 PL/SQL Table TYPE In PL/SQL Version 2 you can create PL/SQL tables, which are similar to one-dimensional arrays. In order to create a PL/SQL table, you must first execute a TYPE declaration to create a table datatype with the right structure. Suffix _tabtyp to indicate that this is not actually a PL/SQL table, but a type of table.
Examples:
TYPE emp_names_tabtyp IS TABLE OF ...; TYPE dates_tabtyp IS TABLE OF ...;
4.1.9 PL/SQL Table A PL/SQL table is declared based on a table TYPE statement, as indicated above. In most situations, use the same name as the table type for the table, but leave off the type part of the suffix. The following examples correspond to the previous table types:
emp_names_tab emp_names_tabtyp; dates_tab dates_tabtyp;
4.1.10 Programmer-Defined Subtype In PL/SQL Version 2.1 you can define subtypes from base datatypes. Use the subtype suffix “_st” to make the meaning of the variable clear. Examples:
SUBTYPE primary_key_st IS BINARY_INTEGER; SUBTYPE large_string_st IS VARCHAR2;
4.1.11 Programmer-Defined TYPE for Record In PL/SQL Version 2 you can create records with a structure you specify (rather than from a table or cursor). To do this, you must declare a type of record which determines the structure (number and types of columns). Use a “_rectyp” suffix in the name of the TYPE declaration as follows:
TYPE sales_rectyp IS RECORD ...; 4.1.12 Programmer-Defined Record Instance Once you have defined a record type, you can declare actual records with that structure. Now you can drop the type part of the rectype prefix; the naming convention for these programmer-defined records is the same as that for records based on tables and cursors: sales_rec sales_rectyp;
4.2 Data Structures - Anchored Declarations 4.2.1 Anchor Declarations with %TYPE and %ROWTYPE You must declare all variables and constants before you can use them. Declare them to use %TYPE and %ROWTYPE attributes to anchor your variable to an existing variable or database element. This way you avoid, yet, another kind of “hard-coding” in your programs.
Hard-Coded Declarations:
ename VARCHAR2(60); total_sales NUMBER (10,2);
Anchored Declarations:
ename emp.ename%TYPE; total_sales sales_amt%TYPE;
4.2.1.1 How Anchoring Works
4.2.1.2 Benefits of Anchoring Synchronize PL/SQL variables with database columns and rows.
- If a variable or parameter does represent database information in your program, always use %TYPE or %ROWTYPE.
- Keeps your programs in synch with database structures without having to make code changes.
Normalize/consolidate declarations of derived variables throughout your programs.
- Make sure that all declarations of dollar amounts or entity names are consistent.
- Change one declaration and upgrade all others with recompilation.
SELECT ... SELECT LIST ...
FROM SCOTT.EMPLOYEE A
SCOTT.COMPANY B
ON A.COMPANY_ID = B.COMPANY_ID
SCOTT.HISTORY C
ON A.EMPLOYEE_ID = C.EMPLOYEE_ID
SCOTT.BONUS D
ON A.EMPLOYEE_ID = D.EMPLOYEE_ID
SCOTT.PROFILE E
ON B.COMPANY_ID = E.COMPANY_ID
SCOTT.SALES F
ON B.COMPANY_ID = F.COMPANY_ID;
With more sensible table aliases (including no tables aliases at all where the table name was short enough already), the relationships are much clearer:
SELECT ... SELECT LIST...
FROM SCOTT.EMPLOYEE EMP
SCOTT.COMPANY CO
ON EMP.COMPANY_ID = CO.COMPANY_ID
SCOTT.HISTORY HIST
ON EMP.EMPLOYEE_ID = HIST.EMPLOYEE_ID
SCOTT.BONUS BONUS
ON EMP.EMPLOYEE_ID = BONUS.EMPLOYEE_ID
SCOTT.PROFILE PROF
ON CO.COMPANY_ID = PROF.COMPANY_ID
SCOTT.SALES
ON CO.COMPANY_ID = SALES.COMPANY_ID;
4.3 Procedures vs. Packages Use Packages instead of standalone Procedures and Functions, unless absolutely necessary. Never use a standalone procedure or function except for demos, tests and standalone utilities (that call nothing and are called by nothing).
Packages:
- Break the dependency chain (no cascading invalidations when you install a new package body -- if you have procedures that call procedures -- compiling one will invalidate your database)
- Support encapsulation -- I will be allowed to write MODULAR, easy to understand code -- rather then MONOLITHIC, non-understandable procedures
- Increase namespace measurably. package names have to be unique in a schema, but can have many procedures across packages with the same name without colliding
- Support overloading
- Support session variables - when you need them
- Promote overall good coding techniques - stuff that lets you write code that is modular,
- Understandable, logically grouped together
- Type of Error/Exception
- An Error/Exception Code
- An error message
5.2 Structure of Error/Exception Handling. The Basic Syntax for coding the exception section: DECLARE
BEGIN
EXCEPTION
WHEN ex_name1 THEN
-Error handling statements
WHEN ex_name2 THEN
-Error handling statements
WHEN Others THEN
-Error handling statements
END;
General PL/SQL statements can be used in the Exception Block. When an exception is raised, Oracle searches for an appropriate exception handler in the exception section. For example (as shown above), if the error raised is 'ex_name1 ', then the error is handled according to the statements under it. Since it is not possible to determine all the possible runtime errors during testing for the code, the WHEN Others exception is used to manage the exceptions that are not explicitly handled. Only one exception can be raised in a Block and the control does not return to the Execution Section after the error is handled. Example:
DELCARE
Declaration section
BEGIN
DECLARE
Declaration section
BEGIN
Execution section
EXCEPTION
Exception section
END;
EXCEPTION
Exception section
END;
In the above case, if the exception is raised in the inner block it should be handled in the exception block of the inner PL/SQL block else the control moves to the Exception block of the next upper PL/SQL Block. If none of the blocks handle the exception the program ends abruptly with an error. 5.3 Types of Error/Exception. There are 3 types of errors/exceptions.
1. Named System Errors/Exceptions 2. Un-named System Errors/Exceptions 3. User-defined Errors/Exceptions 5.3.1 Named System Exceptions System exceptions are automatically raised by Oracle, when a program violates a RDBMS rule. There are some system exceptions which are raised frequently, so they are pre-defined and given a name in Oracle which are known as Named System Exceptions. Example: NO_DATA_FOUND and ZERO_DIVIDE are called Named System errors. Named system errors/exceptions are:
- Not Declared explicitly;
- Raised implicitly when a predefined Oracle error occurs;
- Caught by referencing the standard name within an exception-handling routine.
Example: A NO_DATA_FOUND exception is raised in a proc, you can write a code to handle the exception as given below: BEGIN
Execution section
EXCEPTION WHEN NO_DATA_FOUND THEN
dbms_output.put_line ('A SELECT...INTO did not return any row.');
END; 5.3.2 Unnamed System Errors/Exceptions Those system errors for which Oracle does not provide a name is known as un-named system exceptions. These exceptions do not occur frequently. These Exceptions have a code and an associated message. There are two ways to handle un-named system errors/exceptions:
1. By using the WHEN OTHERS exception handler; or
2. By associating the exception code to a name and using it as a named exception.
You assign a name to un-named system errors using a Pragma called EXCEPTION_INIT EXCEPTION_INIT associates a predefined Oracle error number to a programmer_defined exception name. Rules to be followed to use un-named system exceptions are:
- They are raised implicitly.
- If they are not handled in WHEN Others they must be handled explicitly.
- To handle the exception explicitly, they must be declared using Pragma.
exception_name EXCEPTION; PRAGMA EXCEPTION_INIT (exception_name, Err_code);
BEGIN
Execution section
EXCEPTION
WHEN exception_name THEN
handle the exception
END;
Example: Consider the product table and order_items table from SQL joins. Here product_id is a primary key in product table and a foreign key in order_items table. If you try to delete a product_id from the product table when it has child records in order_id table an exception will be thrown with Oracle code number -2292. You can provide a name to this exception and handle it in the exception section as given in the example below: DECLARE
Child_rec_exception EXCEPTION; PRAGMA EXCEPTION_INIT (Child_rec_exception, -2292);
BEGIN
Delete FROM product where product_id= 104;
EXCEPTION
WHEN Child_rec_exception
THEN Dbms_output.put_line('Child records are present for this product_id.');
END; / 5.3.3 User-defined Errors/Exceptions Apart from system errors we can explicitly define exceptions based on business rules. These are known as user-defined exceptions. Steps to be followed to use user-defined exceptions:
- They should be explicitly declared in the declaration section.
- They should be explicitly raised in the Execution Section.
- They should be handled by referencing the user-defined exception name in the exception section.
huge_quantity EXCEPTION;
CURSOR product_quantity
AS SELECT p.product_name as name, sum(o.total_units) as units
FROM scott.order_tems ORD,
JOIN scott.product PROD
l_quantity scott.order_tems.total_units% TYPE; l_up_limit CONSTANT scott.order_tems.total_units%TYPE := 20; l_message VARCHAR2(50);
BEGIN
ELSIF quantity < up_limit
THEN
v_message:= 'The number of unit is below the discount limit.';
END IF;
dbms_output.put_line (message);
END LOOP;
EXCEPTION
WHEN huge_quantity THEN
dbms_output.put_line (message);
END; / 5.4 Use RAISE_APPLICATION_ERROR ( ) RAISE_APPLICATION_ERROR is a built-in procedure in oracle which is used to display the user-defined error messages along with the error number whose range is in between -20000 and -20999. Whenever a message is displayed using RAISE_APPLICATION_ERROR, all previous transactions which are not committed within the PL/SQL Block are rolled back automatically (i.e. change due to INSERT, UPDATE, or DELETE statements). RAISE_APPLICATION_ERROR raises an exception but does not handle it.
RAISE_APPLICATION_ERROR is used for the following reasons:
- To create a unique id for an user-defined exception; or
- To make the user-defined exception look like an Oracle error.
- The Error number must be between -20000 and -20999.
- The Error_message is the message you want to display when the error occurs.
Steps to be followed to use RAISE_APPLICATION_ERROR procedure: Step 1. Declare a user-defined exception in the declaration section. Step 2. Raise the user-defined exception based on a specific business rule in the execution section. Step 3. Finally, catch the exception and link the exception to a user-defined error number in RAISE_APPLICATION_ERROR. Using the above example we can display an error message by using: RAISE_APPLICATION_ERROR. DECLARE
huge_quantity EXCEPTION;
CURSOR product_quantity is
SELECT p.product_name as name, sum(o.total_units) as units
FROM order_tems o
JOIN product p
ON o.product_id = p.product_id;
quantity order_tems.total_units%type; up_limit CONSTANT order_tems.total_units%type := 20; message VARCHAR2(50);
BEGIN
FOR product_rec in product_quantity LOOP
quantity := product_rec.units;
IF quantity > up_limit THEN
RAISE huge_quantity;
ELSIF quantity < up_limit THEN
v_message:= 'The number of unit is below the discount limit.';
END IF;
Dbms_output.put_line (message);
END LOOP;
EXCEPTION
WHEN huge_quantity THEN
raise_application_error(-2100, 'The number of unit is above the discount limit.');
END;
5.5 Oracle Predefined Errors/Exception Table PL/SQL declares predefined exceptions globally in the package STANDARD, which defines the PL/SQL environment. So, you do not need declare them yourself. You can write handlers for predefined exceptions using the names in the following list:
Exception Oracle Error Description ACCESS_INTO_NULL ORA-06530 Attempt to assign values to the attributes of a NULL object CASE_NOT_FOUND ORA-06592 No matching when clause in a case statement is found COLLECTION_IS_NULL ORA-06531 Attempt to apply collection methods other than exists to a NULL PL/SQL table or varray CURSOR_ALREADY_OPEN ORA-06511 Attempt to open a cursor that is already open. DUP_VAL_ON_INDEX ORA-00001 Unique constraint violated. INVALID_CURSOR ORA-01001 Illegal cursor operation. INVALID_NUMBER ORA-01722 Conversion to a number failed. LOGIN_DENIED ORA-01017 Invalid username/password. NO_DATA_FOUND ORA-01403 No data found. NOT_LOGGED_ON ORA-01012 Not connected to Oracle. PROGRAM_ERROR ORA-06501 Internal PL/SQL error. ROWTYPE_MISMATCH ORA-06504 Host cursor variable and PL/SQL cursor variable have incompatible row types. SELF_IS_NULL ORA-30625 Attempt to call a method on a NULL object instance. STORAGE_ERROR ORA-06500 Internal PL/SQL error raised if PL/SQL runs out of memory. SUBSCRIPT_BEYOND_COUNT ORA-06533 Reference to a nested table or varray index higher than the number of elements in the collection. SUBSCRIPT_OUTSIDE_LIMIT ORA-06532 Reference to a nested table or varray index outside the declared range. SYS_INVALID_ROWID ORA-01410 Conversion to a universal rowid failed. TIMEOUT_ON_RESOURCE ORA-00051 Time-out occurred while waiting for resource. TOO_MANY_ROWS ORA-01422 A select ... into statement matches more than one row. VALUE_ERROR ORA-06502 Truncation, arithmetic, or conversion error. ZERO_DIVIDE ORA-01476 Division by zero.
5.6 Error Handling SQL Examples 5.6.1 Can be found in ClearCase at:
DBDevTeam-1.0_Development\ Passport_Systems_Data\ DatabaseDevTeam\ Process\Oracle\Error_Handling_Samples.sql
6 Oracle PL/SQL Best Practices
In additional to the conventions defined above for the construction of PL/SQL programming, the following coding practices are recommended as supplementary guidelines. 6.1 Write as Little Customized Code as Possible Usually, the less code you write, the less likely it is that you will introduce bugs into application, and the more likely it is that you will meet deadlines and stay within budget. 6.2 Use Built-In Functions The basic PL/SQL language offers tons of functionality; developers need to get familiar with the built-in functions so knowing what you don't have to write. For example, function INSTR has four arguments, investigate and discover all INSTR can do for you instead of writing your own. 6.3 Use Built-In Packages for the Outside of PL/SQL The packages allow you to do things otherwise impossible inside PL/SQL, such as executing dynamic SQL, DDL, and PL/SQL code (DBMS_SQL), passing information through database pipes (DBMS_PIPES), and displaying information from within a PL/SQL program (DBMS_OUTPUT). It is no longer sufficient for a developer to become familiar simply with basic PL/SQL functions like TO_CHAR, ROUND, and so forth. Those functions have now become merely the innermost layer of useful functionality that Oracle has built upon (as should you). Then there are the built-in packages, which greatly expand the horizons. To take full advantage of the Oracle technology, developers must be aware of these packages and how they can help you instead of coding your own packages. 6.4 Integrate the pre-Built Packages In today’s PL/SQL marketplace, you can choose from pre-built, third-party libraries of PL/SQL code, probably in the form of packages. Find out what is available, so that you can avoid reinventing the wheel. 6.5 Reduce coding volume As you are writing your own code, you should also strive to reduce code volume. There are some specific techniques to keep in mind, as detailed in the following sections: 6.5.1 Use the cursor FOR loop. Whenever we need to read through every record fetched by a cursor, the cursor FOR loop will save us lots of typing over the "manual" approach of explicitly opening, fetching from, and closing the cursor.
Basic syntax of a cursor FOR loop:
FOR record_index IN cursor_name LOOP
<executable statement(s)>
END LOOP; 6.5.2 Work with records.
Whenever you fetch data from a cursor, you should fetch into a record declared against that cursor with the %ROWTYPE attribute. But if you find yourself declaring multiple variables which are related, declare instead your own record TYPE. Your code will tighten up and will more clearly self-document relationships. Example: 1 DECLARE 2 CURSOR emp_info_cur IS 3 SELECT emp_id, office_number 4 FROM employee
WHERE hired_dt = SYSDATE;
5 emp_info_rec emp_info_cur%ROWTYPE; 6 7 BEGIN 8 FOR emp_info_rec IN emp_info_cur 9 LOOP 10 update_emp 11 (emp_info_rec.emp_id, emp_info_rec.office_number); 12 END LOOP; 13 14 DBMS_OUTPUT.PUT_LINE 15 ('Last Employee ID Updated: ' || TO_CHAR (emp_info_rec.emp_id)); 16 END;
6.5.3 Use local modules to avoid redundancy and improve readability. If you perform the same calculation twice or more in a procedure, create a function to perform the calculation and then call that function twice instead. 6.6 Synchronize Program and Data Structures Do Not hardcode data structures and relationships into your program; that will widespread breakdowns of code resulting from the simplest database change.
Write code so that it will "automatically" adapt to changes in underlying data structures and relationships. You can do this by taking the following steps:
1. Always fetch from an explicit cursor into a record declared with %ROWTYPE, as opposed to individual variables. 2. Referring to the sample in 7.5.2, the cursor is declared in a package. That cursor may, therefore, be changed without your knowledge. Suppose that another expression is added to the SELECT list. Your compiled code is then marked as being invalid. If you fetched into a record, however, upon recompilation that record will take on the new structure of the cursor.
The following guidelines are additional recommendations for the section 4.3 of the Conventions section above. 7 Standardize PL/SQL Development Environment around Packages Coding standards and guideline are mostly about packages. Developers should center all the PL/SQL development effort around packages. Don't build standalone procedures or functions unless you absolutely have to. Expect that you will eventually construct groups of related functionality and start from the beginning with a package.
The more you use packages, the more you will discover you can do with them; the better you will become at constructing clean, easy-to-understand interfaces (or APIs) to the data and functionality; and the more effectively you will encapsulate acquired knowledge and then be able to reapply that knowledge at a later time, like share/re-use it with others.
The set of "best practices" for package design and usage highlighted as follows:
- Don't declare data in the package specification. Instead, "hide" it in the package body and build "get and set" programs to retrieve the data and change it. This way, you retain control over the data and also retain the flexibility to change your implementation without affecting the programs which rely on that data.
- Build toggles, configurable options into the packages, such as a “local” debug mechanism, which you can easily turn-on or off. This way, other user of your package can modify the behavior of programs inside the package without having to change their own code.
- Avoid writing repetitive code inside the package bodies. This is a particular danger when you overload multiple programs with the same name. Often the implementation of each of these programs is very similar. You will be tempted to simply cut and paste and then make the necessary changes. However, you will be much better off if you take the time to create a private program in the package which incorporates all common elements, and then have each overloaded program call that program.
- Spend as much time as you can in your package specifications. Hold off on building your bodies until you have tested your interfaces (as defined by the specifications) by building compliable programs which touch on as many different packages as possible.
- Be prepared to work in and enhance multiple packages simultaneously. For an instance, you are building a package to maintain orders and that you run into a need for a function to parse a string. If your string package does not yet have this functionality, stop your work in the orders package and enhance the string package. Do Unit-Test your generic function before deploy it in the orders package. Follow this disciplined approach to modularization and you will continually build up your toolbox of reusable utilities.
- Always keep your package specifications in separate files from your package bodies. If you change your body but not your specification, then a recompile only of the body will not invalidate any programs referencing the package.
- Compile all of the package specifications for your application before any of your bodies. That way, you will have minimized the chance that you will run into any unresolved or (seemingly) circular references.
- Encapsulate access to the data structures within packages. Recommend, for example, that you never repeat a line of SQL in your application; that all SQL statements be hidden behind a package interface; and that most developers never write any SQL at all. They can simply call the appropriate package procedure or function, or open the appropriate package cursor. If they don't find what they need, they can ask the owner/original developer of the package to add or change an element.
8 Structured Code and Other Best Practices Following are the most common guidelines in summary, which will ensure you to end up with programs which are easier to maintain and enhance:
- Never exit from a FOR loop (numeric or cursor) with an EXIT or RETURN statement. A FOR loop is a promise: The code will iterate from the starting to the ending value and will then stop execution.
- Never exit from a WHILE loop with an EXIT or RETURN statement. Rely solely on the WHILE loop condition to terminate the loop.
- Ensure that a function has a single successful RETURN statement as the last line of the executable section. Normally, each exception handler in a function would also return a value.
- Don't let functions have OUT or IN OUT parameters. The function should only return values through the RETURN clause.
- Make sure that the name of a function describes the value being returned (noun structure, as in "total_compensation"). The name of a procedure should describe the actions taken (verb-noun structure, as in "calculate_totals").
- Never declare the FOR loop index (either an integer or a record). This is done for you implicitly by the PL/SQL runtime engine.
- Do not use exceptions to perform branching logic. When you define your own exceptions, these should describe error situations only.
- When you use the ELSIF statement, make sure that each of the clauses is mutually exclusive. Watch out especially for logic like "sal BETWEEN 1 and 10000" and "sal BETWEEN 10000 and 20000."
- Remove all hardcoded "magic values" from your programs and replace them with named constants or functions defined in packages.
- Do not "SELECT COUNT(*)" from a table unless you really need to know the total number of "hits." If you only need to know whether there is more than one match, simply fetch twice with an explicit cursor.
- Do not use the names of tables or columns for variable names. This can cause compile errors. It can also result in unpredictable behavior inside SQL statements in your PL/SQL code. You could end up something like:
Needless to say, this caused many headaches. If just simply changed to the global reference to gv_regcd, you would have avoided all such problems.
- Set as a rule that individual developers never write their own exception-handling code, never use the pragmatic EXCEPTION_INIT to assign names to error numbers, and never call RAISE_APPLICATION_ERROR with hardcoded numbers and text. Instead, consolidate exception handling programs into a single package, and predefine all application-specific exceptions in their appropriate packages. Build generic handler programs that, most importantly, hide the way you record exceptions in a log. Individual handler sections of code should never expose the particular implementation, such as an INSERT into a table.
- Never write implicit cursors (in other words, never use the SELECT INTO syntax). Instead, always declare explicit cursors. This will improve coding productivity, and you will have SQL which is more likely (and able) to be reused.
9 Package and Procedures Template
CREATE OR REPLACE PACKAGE package /* | Copyright Information Here | | File name: | | Overview: | | Author(s): | | Modification History: | Date Who What |
- /
END package; /
CREATE OR REPLACE PACKAGE BODY package /* | Copyright Information Here | | File name: | | Overview: | | Author(s): | | Modification History: | Date Who What |
- /
PROCEDURE initialize
IS
BEGIN
NULL;
END initialize;
PROCEDURE subprogram
/*
| Copyright Information Here
|
| File name:
|
| Overview:
|
| Author(s):
|
| Modification History:
| Date Who What
|
*/
IS
PROCEDURE initialize
IS
BEGIN
NULL;
END initialize;
ROLLBACK;
RAISE;
END subprogram;
BEGIN
initialize;
END package; /