Plsql Writing Naming conventions

As the saying goes, there is no rule inadequate surrounding area.

The design of the SQL script, personally, should be started from the coding specification.

Some time ago the company some colleagues submitted scripts, the style is very different, let me review to feel uncomfortable, no audit code of pleasure.

Special collation of the company part of the regular coding standards, the new training, I hope the comrades write code more beautiful and efficient (note: The specification part of the network, thanks to the network of shrimp contributions):

1.1. PL/SQL Program naming conventions

1. Whether it is a PL/SQL object or a variable or cursor used inside a PL/S object, you must be able to understand the meaning of variables and cursors from the name, and be sure to comment on them.

2. text files uploaded to CVS all use '. sql ' as the filename suffix

1.1.1. Stored Procedures

The name of the stored procedure must conform to the Usp_detailname format, where USP represents a stored procedure, and Detailname is the name of the meaning associated with the stored procedure meaning, such as: Usp_business_rule.

1.1.2. Packages

The name of the package must conform to the Pkg_ detailname format, where PKG is the package, Detailname is the meaning of the package meaning, for example: Pkg_sorce_all.

1.1.3. Triggers

The name of the trigger must conform to the Trg_detailname format, where TRG is the trigger, Detailname is the meaning associated with the trigger purpose, and can refer to an existing Trigger object.

1.1.4. Functions

The name of the function must conform to the Fnc_detailname format, where FNC is a function, and detailname is a meaning that is related to the meaning of the function, for example: Fnc_salarycount.

1.1.5. Variables & Cursors

1. The name of the variable must conform to the Statute, please start with "Xxx_variablename", where XXX represents the type of the variable. Variablename represents a variable name, such as passing in a parameter to a function, plus in,out,inout, for example, such as: In_num_cust_code.

2. If the field according to the table, please use%type or%rowtype mode. Please do not define variables directly.

such as: Num_cust_code Tablename.cust_code%type

3. The cursor should start with "Cur_", please describe it at the beginning of the code, and describe the definition of the cursor.

such as: Cur_cust_info

4. All variables must be marked with the variable type before the variable, the number type adopts NUM,VARCAHR2 with VAR, and the incoming method of the function and so on In_var_cust_name,out_num_code,inout_num_code The way to differentiate whether the type of the variable is bit in,out,inout type

5. Cannot exceed 30 characters, cannot use the keyword, must start with the letter.

Note: The above naming rules will primarily be used when creating new objects, and if modified on the basis of the original PL/SQL objects, follow the naming conventions of the original code, and note the comments of the code. You must also start with the PL/SQL code to make a comment.

1.2. PL/SQL Encoding specification

The coding specifications for PL/SQL include:

² Notes

² variable Naming

² Writing Format

² Logical Branch

² cyclic Processing

1.2.1 Notes

1. Please write out the comment block in the following format strictly at the beginning of all programs:

-- *******************************************

--Process Name:

--Function Description:

--Input parameter description:

--Output parameter description:

--called procedure or function:

--Create a person:

--Date Created:

--Modify the person:

--Date Modified:

--Change Reason:

--Modify the result:

--Release Notes:

-- ********** *********************************

2. Separator lines and annotations are usually set in the declare, begin, exception, and end sections of PL/SQL blocks;

3. Each variable needs to be annotated with a variable, explaining the purpose of the variable;

4. Please add separator lines and annotations to important program sections and difficult-to-understand procedures.

5. Please indicate the use and usage of cursors, etc.

6. The total amount of annotation and program volume is as much as 1:1.

1.2.2 Variable name and meaning

Variable name follow the naming convention, use frequent, critical variables, and for readability and modification, add a note to the definition to indicate its meaning.

To facilitate reading and debugging, minimize the use of single-letter variables, such as I, l, etc. as a variable name, while noting the difference between the lowercase L and the number 1 use. The use of keywords is strictly prohibited and conforms to Oracle's naming conventions.

1.2.3 Modification of existing programs

1. Delete before and after the deletion code/*deleted by yourname on Yyyy/mm/dd start*/and/*deleted by yourname on YYYY/MM/DD end*/Please also indicate the reason for the deletion.

2. Modify the original code all comments (delete) out, in the final explanation of the reason for the comment, while writing your new added code in the comments, first add/*modified by yourname on YYYY/MM/DD start*/and/* Modified by Yo Urname on YYYY/MM/DD end*/need guarantee

A. All commented code is the original code

B. All code that is not commented is the newly added code

3. Add the added code before adding/*added by yourname on Yyyy/mm/dd start*/and/*added by yourname on YYYY/MM/DD end*/also please note the explanation, the reason for the increase, etc.

To facilitate reading and debugging, minimize the use of single-letter variables, such as I, l, etc. as a variable name, while noting the difference between the lowercase L and the number 1 use.

Please note that the changes you have made must be detailed at the beginning of the PL/SQL code, including changes to those places, the reasons for the changes, the date of the modification, the modification of the person, etc.

1.2.4 Writing format

1. The hierarchy of nested structures is shown by the sentence layering indentation;

2. Insert blank lines in the comment segment and in the program segment, and in different program segments;

3. Write only one statement per line.

1.2.5 Logical Branch

The logical branches are in the following format:

L IF ... Then
......
ELSE
......
END IF;

L CHOOSE Case
Case 1 ...
......
Case 2 ...
......
Case ELSE ...
......
Case END;

1.2.6 Cycle Processing

The format of the loop processing is as follows:

L for ... LOOP
......
END LOOP;

L while ... LOOP
......
END LOOP;

L DECLARE
CURSOR cursor_name is
......
(SQL STATEMENT for ther CURSOR)
BEGIN
For variable_name in cursor_name LOOP
...
(STATEMENT)
END LOOP;
END

1.2.7 Cursor Handling

The cursor itself is a SQL workspace for processing multi-row or single-line query processing, mainly divided into the following two classes

1. Implicit cursor is implicitly defined by the select of DML and PL/SQL and cannot be controlled using fetch,open,colse, etc., but can use the properties of the cursor, such as select xxx to xxx from XX X.

2. Explicit cursor is primarily controlled by a program that displays data that returns one or more rows.

Four steps to perform

：

1.

Declaration: Defines the name and structure of the cursor, which can be used in select by using the ORDER by

2. Open the cursor: Execute the query and bind all the variables involved

What to do:

Allocating memory for Select and parsing SELECT statements

Variables bound for input

Configure the pointer in the first row of the active set

Note: If the query does not return results, PL/SQL exceptions will not be raised, and you can test the returned results after the fetch is executed

If the declaration within the cursor includes update. Row locks are performed as

3.Fetch: Assigns the value of the current row to the variable, and each Fetch moves the cursor pointer down one line.

Automatically exits for loop if the last line is reached

4. Close: Releases the active set and can again use the Open

Several properties of cursors:

Sql%rowcount the number of rows affected by the most recently executed SQL statement. (An integer value)
The Sql%found Boolean property, if the most recent SQL statement affects one or more rows, its value is
TRUE.
The Sql%notfound Boolean property, if the most recent SQL statement does not affect any rows, its value is
TRUE.
Sql%isopen is always false because PL/SQL always closes an implicit cursor immediately after they have finished executing.

Example

EG1: General usage

CURSOR C1 is

SELECT empno, ename

from EMP;

Emp_record C1%rowtype;

BEGIN

OPEN C1;

. . .

FETCH C1 into Emp_record;

EG2: Using a For loop to implement cursors

DECLARE

CURSOR C1 is

SELECT empno, ename

from EMP;

Emp_record C1%rowtype;

BEGIN

For Emp_record in C1 LOOP

--implicit open and implicit fetch occur

IF Emp_record.empno = 7839 Then

...

END LOOP; --Implicit close occurs

END;

The cursor for loop does not require a FETCH statement. The cursor opens, each time a row is repeatedly fetched in the loop, and when all the rows are processed, the cursor is automatically closed.

EG3: How cursors are not defined

BEGIN

For Emp_record in (SELECT empno, ename

From EMP) LOOP

--implicit open and implicit fetch occur

IF Emp_record.empno = 7839 Then

...

END LOOP; --Implicit close occurs

END;

EG1: Cursors with variables

You must specify the data type of the specified parameter, but do not specify the size

DECLARE

CURSOR C1

(v_deptno number, v_job VARCHAR2) Is

SELECT empno, ename

From EMP

WHERE Deptno = V_deptno

and job = V_job;

BEGIN

OPEN C1 (, ' clerk ');

...

Eg5:for Update----When you add a for update, the entire table or field is locked.

SELECT ... From ...

For UPDATE [of column_reference ][nowait]

DECLARE

CURSOR C1 is

SELECT empno, ename

From EMP

For UPDATE NOWAIT;

NOWAIT: Returns an Oracle error message if this line is locked for another session.

Eg6:where Current OF

Used to delete and update the forward in the cursor

You must use Forupdate to lock the line.

Use the where current of to point to a line

DECLARE

CURSOR C1 is

SELECT ...

For UPDATE NOWAIT;

BEGIN

...

For Emp_record in C1 LOOP

UPDATE ...

WHERE current of C1;

...

END LOOP;

COMMIT;

END;

1.2.8 Exception handling

The exception of PL/SQL is divided into three major categories

1. Predefined Exception anomalies

2. non-predefined Exception Anomalies

3. User Defined Exception exception

where 1, 2 will be implicitly raised, 3 need to show raised

The following example

Predefined Exception

BEGIN SELECT ... COMMIT;

EXCEPTION

When No_data_found Then

Statement1;

Statement2;

When Too_many_rows Then

Statement1;

When OTHERS Then

Statement1;

Statement2;

Statement3;

END;

.......

non-predefined Exception

DECLARE

E_products_invalid EXCEPTION;

PRAGMA Exception_init (

E_products_invalid,-2292);

V_message VARCHAR2 (50);

BEGIN

. . .

EXCEPTION

When E_products_invalid Then

: g_message: = ' Product code

Specified is not valid. ';

. . .

END;

user-defined Exception

DECLARE

E_amount_remaining EXCEPTION;

. . .

BEGIN

. . .

RAISE e_amount_remaining;

. . .

EXCEPTION

When E_amount_remaining Then

: g_message: = ' There is still an amount

In the stock. ';

. . .

END;

Raise_application_error

DECLARE

.......

Invalidpart EXCEPTION;

BEGIN

.......

IF Sql%notfound Then

RAISE Invalidpart;

END IF;

EXCEPTION

When Invalidpart Then

Raise_application_error ( -20003, ' Invalid part ID # ' | | Partnum);

When OTHERS Then

Raise_application_error ( -20000,errnum| | ERRMSG);

END

² Use the Exception keyword to declare a user-defined exception in the Declarations section of a PL/SQL block

² Use PL/SQL command raise to detect user-defined exceptions

²pl/sql can use the Raise_application_error procedure to return a user-defined number of errors and messages to the calling environment. All user-defined error messages must be between 20000 and-20999

The ²pl/sql program can use when OTHERS exception handling to handle all exceptions without specific processing, when OTHERS must be placed at the end of the exception handling

The ²PL/SQL program can return Oracle internal error numbers and messages using special Sqlcode and SQLERRM functions

²sqlcode SQLERRM, SQLCODE returns the error type of a number type, and SQLERRM returns an error message description of the error type.

DECLARE

V_error_code number;

V_error_message VARCHAR2 (255);

BEGIN

...

EXCEPTION

...

When OTHERS Then

ROLLBACK;

V_error_code: = SQLCODE;

V_error_message: = SQLERRM;

INSERT into Errors VALUES (V_error_code,

V_error_message);

END;

Plsql Writing Naming conventions