Oracle 2 PL/SQL block programming Basics

ArticleDirectory

• 1.1pl/SQL block type
• 2.1 scalar Variables
• 2.2 compound Variables
• 2.3 reference Variables
• 2.4 lob variable
• 3.1 annotations
• 3.2 PL/SQL coding rules

1. Introduction to PL/SQL Blocks

Block is the basis of PL/SQLProgramUnit. Compiling PL/SQL programs is actually compiling PL/SQL blocks. To complete relatively simple application functions, only one PL/SQL block is required. To implement complex application functions, one PL/SQL block may be required to escape other PL/SQL blocks. Compile the PL/SQL application module. There is no restriction on the nesting hierarchy of the blocks.

PL/SQL is a block structure language, which means that PL/SQL programs can be divided into logical blocks. A pl/SQL block consists of three parts: the definition part, the execution part, and the exception handling part. The definition part is used to define variables, constants, cursors, user-defined exceptions, and complex data types. The execution part is used to implement the functions of the application module, this part contains the PL/SQL statements and SQL statements to be executed. The exception handling part is used to handle possible running errors in the execution part.

1.1pl/SQL block type ● anonymous Block

An anonymous block is a PL/SQL block without a name. Anonymous blocks can be embedded in applications or directly used in interactive environments. For example:

DeclareV_avgsalNumber(6,2)BeginSelect AVG(Sal)IntoV_avgsalFromEMPWhereEmpno= &No; dbms_output.putline ('Average salary'|V_avgsal );End;
 

● Subroutine

Subroutines include stored procedures, functions, and packages. By centralizing business logic and enterprise planning into PL/SQL subprograms, You can simplify development and maintenance of client programs and improve application performance. The following describes a variety of subprograms.
 

◆ Process

 
A process is used to perform specific operations. When the process is established, you can specify the input parameters or output parameters. By using the input function during the process, you can pass the data in the application environment to the Execution Section. By using the output parameters, You can transmit the data in the Execution Section to the application environment. You can use the create procedure command to create a process, for example:
 

 
Create ProcedureUpdate_sal (nameVarchar2, NewsalNumber)IsBeginUpdateEMPSetSal=NewsalWhereEname=Name;End;
 

 
When calling this process, you can use the excute or call command.
 

ExecUpdate_sal ('Scott',1000); Call update_sal ('Scott',1000);
 

◆ Function

Function is used to return specific data. When creating a function, the return clause must be included in the function header, and the return statement must be included in the function body to return data. You can use the create function command to create a function.

 Create   Function Annual_income (name vachar2) Return   Number   Is  Annual_salary Number ( 7 , 2  );  Begin      Select Sal *  12   Into Annual_salary From EMP Where Ename =  Name;  Return  Annual_salary; End ;

◆ Package

A package is used to logically combine related processes and functions. It consists of a package specification and a package body. The package specification is used to define common variables, constants, processes, and functions. You can use the create package command to create a package specification.

 
CreatePackage emp_pkgIsProcedureUpdate_sa (nameVarchar2, NewsalNumber);FunctionAnnual_income (nameVarchar2)Return Number;End;
 

The packet specification only contains the description of the process and function, without the implementation of the Process and function.Code. The package body is used to implement the procedures and functions in the package specification. You can use the create package body command to create a package body.

CreatePackage body emp_pkgIsProcedureUpdate_sa (nameVarchar2, NewsalNumber) Is
 

 
Begin
 

 
UpdateEMPSetSal=NewsalWhereEname=Name;
 

 
End;FunctionAnnual_income (nameVarchar2)Return Number is
 

Annual_salaryNumber(7,2);
 

 
Begin 
 

 
SelectSal*12 IntoAnnual_salaryFromEMPWhereEname=Name;
 

 
ReturnAnnual_salary;
 

End;
 

 
End;
 

The package name must be prefixed before the process and function name (package name. subroutine name ). To call other schema packages, you must add the schema name as the prefix.

● Trigger

A trigger is a stored procedure that is implicitly executed. When defining a trigger, you must specify the trigger event and trigger operation. Common trigger events include insert, update, and delete statements. The trigger operation is actually a PL/SQL block, the create trigger command is used to create a trigger.

CreateTriggerupdate_cascade afterUpdate OnDeptForEach rowBeginUpdateEMPSetDeptno=: New. deptnoWhereDeptno=: Old. deptno;End;
 

2. Data Types and variables

When compiling a PL/SQL program, variables and constants must be defined if values are stored temporarily. If data is transferred between an application and a subroutine, the data type must be specified for them. When compiling PL/SQL programs, you can use four types: scalar type, composite type, reference type, and LOB type.

2.1 scalar Variables

A scalar variable is a variable that can only store a single value. The most common variable is a scalar variable. When defining a scalar variable, you must specify the scalar data type. Scalar data types include numeric, character, date, and Boolean.

(1) Common scalar Data Types

Common scalar types include:

☆Varchar2 (n ):This data type is used to define a variable-length string. N indicates the maximum length of the specified string, and its maximum value is 32767 bytes.

☆Char (n ):This data type is used to define a fixed-length string. N indicates the maximum length of the specified string, and its maximum value is 32767 bytes. If n is not specified, the default value is 1.

☆Number (P, S ):Defines fixed-length integers and floating-point numbers. P indicates the precision and is used to specify the total number of digits. s indicates the scale and specifies the number of digits after the decimal point.

☆Date:Defines Date and Time data. The data length is fixed to 7 bytes.

☆Timestamp:The new data type in Oracle9i, used to define date and time data. The amplitude method is the same as date, but not only the date, but also the time and the last afternoon Mark are displayed when the data is displayed.

☆Long and long raw:The long data type is used to define a variable-length string, similar to varchar2, but its maximum length is 32760 bytes. Long raw is used to define a variable-length binary data with a maximum length of 32760 bytes.

☆Boolean:Used to define a Boolean variable. Its values are true, false, or null.

☆Binary_integer:Defines an integer in the range of-2147483647 and 2147483647.

☆Binary_float and binary_double:They are used to define Single-precision floating point numbers and double-precision floating point numbers. These two data types are mainly used for high-speed scientific operations. When assigning values to binary_float, they should contain the suffix F (1.5f). When assigning values to binary_double, they should contain the suffix D (3.23d ).

(2) Defining scalar Variables

To reference a scalar variable, you must first define it in the definition section before using these scalar variables in the execution section or exception section.

 
Identifier[Constant]Datatype[Not null] [: = | Default expr]
 

Identifier: used to specify the name of a variable or constant.

Constant: used to specify constants. When defining constants. The initial value must be specified, and the data cannot be changed.

Datatype: Specifies the Data Type of a variable or constant.

Not NULL: Used to forcibly initialize a variable (cannot be null). When not null is specified, a value must be provided for the variable.

: =: Specifies the initial value for a variable or constant.

Expr: the PL/SQL expression used to specify the initial value. It can be text, other variables, and functions.

(3) Use scalar Variables

After defining some scalar variables, these scalar variables can be referenced in the execution part and Exception Handling part. Assign values to variables in PL/SQL is different from other statements. You must add a colon (: =) before the equal sign ).

 Declare  V_ename  Varchar2 ( 5  ); V_sal  Number ( 6 , 2 ); C_tax_rate constant  Number ( 3 , 2 ): =  0.03  ; V_tax_sal  Number ( 6 , 2  );  Begin      Select Ename, Sal Into V_ename, v_sal From EMP Where Empno = &  No; v_tax_sal:  = V_sal *  C_tax_rate; dbms_output.putline (v_tax_sal );  End ;

(4) use the % Type attribute

When PL/SQL variables store column values, make sure that appropriate data types and lengths are used for the variables. Otherwise, a PL/SQL running error may occur during the running process. To avoid unnecessary errors, you can use PL/SQL attributes to define variables. When you use the % Type attribute to define a variable, it determines the type and length of the new variable according to the database column or other variables.

DeclareV_enameEMP. ename % type;V_salEMP. Sal % type;
 

 
C_tax_rate constantNumber(3,2):=0.03; V_tax_salV_sal % type;
 

BeginSelectEname, SalIntoV_ename, v_salFromEMPWhereEmpno= &No; v_tax_sal:=V_sal*C_tax_rate; dbms_output.putline (v_tax_sal );End;
 

 

2.2 compound Variables

Composite variables are used to store multiple values. When using composite variables, you must first define them with type. When you define a composite variable, you must use the PL/SQL Composite data type. PL/SQL Composite data types include PL/SQL record type, PL/SQL set type (including PL/SQL table, nested table, and varray. These four types are applied in different situations: when processing single-row single-column data, scalar variables are used; when processing single-row multi-column data, PL/SQL records are used; A set is used to process data in a single column and multiple rows. PL/SQL records and sets are used to process data in multiple rows and multiple columns.

1) PL/SQL record (record)

PL/SQL records are similar to the structure in advanced languages. Each PL/SQL record generally contains multiple members, which is used to simplify the processing of data in multiple columns in a single row.

PL/SQL records can be defined in two ways. The first method is to use type... is record to customize the record type and then define the record variable. Second, use the table name % rowtype attribute to directly define the record variable. After the record variable is defined, You can reference the record Member in the form of 'record variable. Record member.

 
Type emp_recore_typeIsRecord (name EMP. ename%Type, salary EMP. Sal%Type, title EMP. Job%Type); emp_record emp_record_type;
 

The following are the record variables defined using the table name % rowtype attribute:

 
Emp_record EMP%Rowtype
 

There are two ways to assign values to record variables: one is to assign values to each member using the value assignment statement; the other is to pass the select... Assign values to into statements, for example:

Emp_record.ename:='Scott';Select * IntoEmp_recordFromEMPWhereEno=788;
 

Use select... When an into statement is assigned a value, the name, number, and type of the column name list in the SELECT statement must be exactly the same as the number, name, and type of the record variable. 2) Set Type

The PL/SQL collection type is similar to a composite data type of the advanced language array, including PL/SQL table, nested table, and variable length array.

☆ PL/SQL table

A pl/SQL table is also called an index table. Unlike an array of advanced languages, the subscript of an array of advanced languages cannot be negative, but the subscript of a PL/SQL table can be negative; the number of elements in the advanced language array is limited, while the number of elements in the PL/SQL table is not limited, and the subscript has no upper or lower limit.

☆ Nested table

The number of nested tables is different from that of PL/SQL tables: the labels of arrays and nested tables in advanced languages cannot be negative. The number of elements in advanced languages is limited, the number of elements in a nested table is unlimited. Nested tables are similar to PL/SQL tables, but the data type of columns can be used in nested tables, while PL/SQL tables cannot be the data type of columns. When using a nested table in a table column, you must use the create type statement to create a nested table type.

☆ Varray

Varray (variable-length array) is similar to a nested table. It can be used as the Data Type of the columns and object type attributes. Note that the number of elements in a nested table is unlimited, while the number of elements in varray is limited.

☆ Built-in methods of set types

There are many built-in methods for set variables. The call format is: set type variables. method.

Table 2-1 lists common methods for Oracle set types.

Table 2-1 method of set type

Method	Description	Limits
Count	Returns the number of elements in the set.
Delete	Delete all elements in a collection
Delete (X)	Delete the element marked as X in the set. If X is null, it remains unchanged.	Invalid varray
Delete (x, y)	Delete the elements from X to Y. If the x> Y Set remains unchanged	Invalid varray
Exist (X)	If array element x has been initialized, true is returned; otherwise, false is returned.
Extend	Add an element at the end of the Set	The index table is invalid.
Extend (X)	Add x elements at the end of the Set	The index table is invalid.
Extend (x, n)	Add x copies of element N at the end of the Set	The index table is invalid.
First	Returns the subscript of the first element in the Set, and returns 1 for the varray set.
Last	Returns the subscript of the last element in the set. The return value of the varray set is always equal to count.
Limit	Returns the maximum number of elements in the varray set.	Useless for nested tables and index table sets
Next (X)	Returns the value of the element after element x and next to it. If the element is the last element, null is returned.
Frior (X)	Returns the value of the element next to it before element x. If the element is the first element, null is returned.
Trim	Deletes an element from the end of the set.	The index table is invalid.
Trim (X)	Delete x elements from the end of the Set	The index table is invalid.

 

2.3 reference Variables

A referenced variable is a variable used to store a value pointer. By using reference variables, the application can share the same object, thus reducing the occupied space. You can use the cursor variable (ref cursur) and object type variable ref ojb_type to reference variable types.

1) ref cursur

To enable an explicit cursor, you must specify the SELECT statement for the response when defining the explicit cursor. This explicit cursor is called a static cursor. When using a cursor variable, you do not need to specify a SELECT statement when defining the cursor variable to implement dynamic cursor operations.

2). Ref ojb_type

When writing an object type application, you can use ref to reference the object type to share the same object. Ref is actually a pointer to the object instance.

 

2.4 lob variable

Lob variables are used to store large volumes of data. Oracle divides LOB into two types: Internal lob and external lob. The internal lob includes three types: clob/BLOB/and nclob. Their data is stored in the database and supports transaction operations (commit, rollback, and storage points ). External lob only has one type of bfile. data of this type is stored in the operating system file and does not support transaction operations. Clob and nclob are used to store large volumes of character data, blob stores large batches of binary data, and bfile stores pointers to operating system files.

 

3. Compile PL/SQL code 3.1 comments.

Annotations are used to explain the functions of line or multiple lines of code, which improves the readability of PL/SQL programs.

☆Single line comment

Annotate a single line of code with the '--' symbol

☆Multi-line comment

Use "/* .. */" to comment out multiple lines of code

3.2 PL/SQL coding rules 1) identifier naming rules

When you use an identifier in PL/SQL to define variables and constants, the identifier name must start with a letter and cannot exceed 30 characters.

2) Case sensitivity rules

Uppercase keywords, uppercase data types, lowercase identifiers and parameters, and lowercase database objects and columns.

3) code indent

Same-level code alignment and lower-level code indent.