Oracle PL/SQL programming specification Guide

I. PL/SQL programming specification in case

Just like in SQL, PL/SQL is case-insensitive. The general principles are as follows:

Keywords (BEGIN, EXCEPTION, END, if then else, LOOP, end loop), data type (VARCHAR2, NUMBER), internal function (LEAST, SUBSTR) and user-defined subroutines (procedures, functions, packages), in upper case.

Variable name, column name and table name in SQL, in lower case.

Ii. Gaps in PL/SQL programming specifications

Blank blank lines and spaces) are equally important in PL/SQL as they are in SQL, because they are an important factor in improving code readability. In other words, you can use indentation in the Code to reflect the logical structure of the program. The following are some suggestions:

Leave a space at the left and right of the equal sign or comparison operator;

DECLARE, BEGIN, EXCEPTION, END, IF and end if, LOOP and end loop) are arranged on the left. In addition, the nested structure in the structure should be indented with three spaces using the space key instead of the Tab key );

The main code segments are separated by blank lines;

Separate different logical parts of the same structure into independent rows, even if the structure is short. For example, IF and THEN are placed in the same row, while ELSE and end if are placed in independent rows.

Iii. Naming Conventions for PL/SQL programming specifications

Using the following prefix is helpful to avoid conflicts with keywords and table names:

V _ variable name

Con _ constant name

I _ input parameter name, o _ output parameter name, io _ input and output parameter name

C _ cursor name or cursor name_cur

Rc _ Ref Cursor name

R_Record name or Record name_rec

FOR r_stud IN c_stud LOOP...

FOR stud_rec IN stud_cur LOOP

Type _ name, name_type (user-defined type)

T _ table name, table name_tab PL/SQL table)

Rec_Record name, Record name_rec Record variable)

E _ User-defined exception)

The package name should describe the main functions of the stored procedures and functions in the package.

The name of the stored procedure should describe the action performed by the stored procedure.

The function name should describe the returned variable

For example:

PACKAGE student_admin

-The admin suffix may be used to indicate the management function.

PROCEDURE remove_student (I _student_id IN student. studid % TYPE );

FUNCTION student_enroll_count (I _student_id student. studid % TYPE)

Return integer;

Iv. Notes on PL/SQL programming specifications

Annotations in PL/SQL are as important as those in SQL. They should explain the main parts of the program and all the key logical steps.

Use single line comment (-) instead of multi-line comment (/*). Even if PL/SQL processes these comments as well, it is easier to debug the code after the code is completed, because you cannot embed multi-line comments in multi-line comments. In other words, you can partially cancel comments in a single line comment code, but not in multiple lines of comments code.

5. Other suggestions

For SQL statements embedded in PL/SQL, use the same formatting guide to determine how these statements should appear in the code block.

A header comment is provided to describe the purpose of the code block and list the creation date and author name. Each revision has a line of comment, including the author name, date, and revision description.

For example, the following example shows the above suggestions. Please note that this example also uses the same width font Courier New), because each font occupies the same width can make formatting easier. Proportional space fonts hide spaces, making it difficult to align between lines. Most text and program Editors Use an equal-width font by default.

 
 

  
  
REM *************************************** *****************
  
  
REM * file name: coursediscount01. SQL
  
  
REM * version: 1
  
  
REM * purpose: discounts are given for at least a portion of courses registered with more than 10 students
  
  
REM * parameter: None
  
  
REM *
  
  
REM * Author: s. tashi time: 2000.1.1
  
  
REM * modifier: y. sonam time: 2000.2.1
  
  
REM * Description: modifies the cursor and adds indentation and comments.
  
  
REM *************************************** *****************
  
  
DECLARE 
  
  
-- C_DISCOUNT_COURSE: Find the courses with at least a part of more than 10 student registrations 
  
  
CURSORC_discount_courseIS 
  
  
SELECT DISTINCTCourse_no
  
  
FROM SectionSect
  
  
WHERE10 <= (SELECT COUNT(*)
  
  
FROMEnrollment enr
  
  
WHEREEnr. section_id = sect. section_id
  
  
);
  
  
-- Discount rate of courses with fees exceeding $2000.00 
  
  
Con_discount_2000 constant number: =. 90;
  
  
-- Discount rate of the course between $1001.00 and $2000.00 
  
  
Con_discount_other constant number: =. 95;
  
  
V_current_course_cost course. cost % TYPE;
  
  
V_discount_all NUMBER;
  
  
E_update_is_problematic EXCEPTION;
  
  
BEGIN 
  
  
-- Determine the current fee and new fee for the courses to be discounted 
  
  
FORR_discount_courseInC_discount_course LOOP
  
  
SELECTCost
  
  
INTOV_current_course_cost
  
  
FROMCourse
  
  
WHERECourse_no = r_discount_course.course_no;
  
  
IF v_current_course_cost> 2000THEN 
  
  
V_discount_all: = con_discount_2000;
  
  
ELSE 
  
  
IF v_current_course_cost> 1000THEN 
  
  
V_discount_all: = con_discount_other;
  
  
ELSE 
  
  
V_discount_all: = 1;
  
  
ENDIF;
  
  
ENDIF;
  
  
BEGIN 
  
  
UPDATECourse
  
  
SETCost = cost * v_discount_all
  
  
WHERECourse_no = r_discount_course.course_no;
  
  
EXCEPTION
  
  
WHENOTHERSTHEN 
  
  
RAISE e_update_is_problematic;
  
  
END;-- The subcode block of the update Record ends. 
  
  
ENDLOOP;-- The main cycle ends. 
  
  
COMMIT;
  
  
EXCEPTION
  
  
WHENE_update_is_problematicTHEN 
  
  
-- Transaction rollback 
  
  
ROLLBACK;
  
  
DBMS_OUTPUT.PUT_LINE
  
  
('There was a problem updating a course cost .');
  
  
WHENOTHERSTHEN 
  
  
NULL;
  
  
END;
  
  
/
 
 

1. Connection Methods and common usage of Oracle database tables
2. Application Analysis of Oracle recovery Manager
3. Five restrictions on using Oracle External tables
4. MySQL left connection, right connection, and internal connection
5. In-depth exploration of database connection Performance