Good habits in PL / SQL

Further it will be told about Oracle Database 11g, about those habits that we developed over time.

Standard

Within the team, you need to develop your own standards (and naturally take into account the standards of the client).
The minimum that should be included in the standard:

• Naming database objects (tables, views, types, collections, packages, etc.);
• Variable naming (scope, locality, constancy, variable type, cursor, cursor variables)
• Commenting code
• Some rules for writing code

If desired, you can include anything here. It is important that everyone agrees with the standard and subscribes (on pain of cutting off the fingers).

Code management

There were several articles on the topic of database migration and versioning. Articles are undoubtedly good, but only when you have several dozen tablets. We have 200k PL / SQL lines of code (the number of other objects is comparable to this) and a team of 10 people.
At first they tried to use the RequesitePro + ClearQuest + ClearCase bundle to control the whole, but after a short time they refused the SS and replaced it with git with local storage for gitorious.
Different parts of the application are divided into different repositories. Created separate repositories for modifications for individual clients.
The approach to the files is the following: there is a baseline and there are patches for it, with time both are updated.

Code writing

Consider a simple procedure (this is just an example):

CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = NVL(newsal_in, 1000) WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept; * This source code was highlighted with Source Code Highlighter .

1. CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = NVL(newsal_in, 1000) WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept; * This source code was highlighted with Source Code Highlighter .
2. CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = NVL(newsal_in, 1000) WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept; * This source code was highlighted with Source Code Highlighter .
3. CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = NVL(newsal_in, 1000) WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept; * This source code was highlighted with Source Code Highlighter .
4. CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = NVL(newsal_in, 1000) WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept; * This source code was highlighted with Source Code Highlighter .
5. CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = NVL(newsal_in, 1000) WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept; * This source code was highlighted with Source Code Highlighter .
6. CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = NVL(newsal_in, 1000) WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept; * This source code was highlighted with Source Code Highlighter .
7. CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = NVL(newsal_in, 1000) WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept; * This source code was highlighted with Source Code Highlighter .
8. CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = NVL(newsal_in, 1000) WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept; * This source code was highlighted with Source Code Highlighter .
9. CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = NVL(newsal_in, 1000) WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept; * This source code was highlighted with Source Code Highlighter .
10. CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = NVL(newsal_in, 1000) WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept; * This source code was highlighted with Source Code Highlighter .
11. CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = NVL(newsal_in, 1000) WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept; * This source code was highlighted with Source Code Highlighter .
12. CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = NVL(newsal_in, 1000) WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept; * This source code was highlighted with Source Code Highlighter .
13. CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = NVL(newsal_in, 1000) WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept; * This source code was highlighted with Source Code Highlighter .

CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = NVL(newsal_in, 1000) WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept; * This source code was highlighted with Source Code Highlighter .

Here it is worth paying attention to% TYPE. Whenever a variable type is a type of a column or field, then% TYPE should be used.
In the FOR loop, multiple UPDATE statements are executed (and yes, the whole procedure can be crammed into one UPDATE). In Oracle DB, pl / sql and sql code perform different engines, and accordingly there will be many context switches between these engines, which takes some time.
Therefore, we will rewrite our procedure as follows:

1. CREATE OR REPLACE PROCEDURE upd_for_dept (
2. dept_in IN employee.department_id% TYPE
3. , newsal_in IN employee.salary% TYPE)
4. IS
5. deptlist id_list;
6. BEGIN
7. SELECT employee_id
8. BULK COLLECT INTO deptlist
9. FROM employee WHERE department_id = dept_in;
11. FORALL indx IN deptlist. FIRST ..deptlist. LAST
12. UPDATE employee
13. SET salary = NVL (newsal_in, 1000)
14. WHERE employee_id = deptlist (indx);
15. END upd_for_dept;

* This source code was highlighted with Source Code Highlighter .

The FORALL operator will not cause context switches. But what else is bad here. This is NVL. NVL is a PL / SQL function, therefore switching of the context again. Therefore, it should be replaced with COALESCE or a CASE statement.
Here the rule naturally emerges about the use of functions built into the SQL engine.
When using collections, the design of procedures and functions naturally changes. The final option would be such a procedure:

1. CREATE OR REPLACE PROCEDURE upd_for_dept (
2. deptlist IN id_list,
3. , newsal_in IN employee.salary% TYPE)
4. IS
6. BEGIN
7. FORALL indx IN deptlist. FIRST ..deptlist. LAST
8. UPDATE employee
9. SET salary = COALESCE (newsal_in, 1000)
10. WHERE employee_id = deptlist (indx);
11. END upd_for_dept;

* This source code was highlighted with Source Code Highlighter .

The collection is created as follows:

1. CREATE TYPE id_list IS TABLE OF INTEGER ;

* This source code was highlighted with Source Code Highlighter .

It is also worth it to put the SQL statements in separate procedures and functions, as SQL in PL / SQL is a hardcode.
When using typed collections, the following features should be considered:

• You must understand and be able to use different types of collections.
• SQL% BULK_ROWCOUNT helps to find out the number of affected rows
• SAVE EXCEPTIONS in FORALL helps you catch errors
• There can be only one SQL statement in FORALL

Almost always (I have never seen that this was not true) the speed of executing code using the collection is 6 times higher than in the usual way.
At this point I want to finish.
Intentionally not covered such topics as error handling and logging, CBO query optimization. If it is interesting to highlight these moments.