PL/SQL Basics

I. What is PL/SQL?

PL/SQL (procedural language/structure Query Language) is an oracle extension to the standard SQL specification that fully supports SQL data operations, transaction control, and so on.

Ii. the rules for the preparation of PL/SQL

1. Naming rules for identifiers

1) variables, recommended to use v_ as a prefix, such as v_sal, v_job, etc.

2) constants, it is recommended to use C_ as a prefix, such as c_rate.

3) cursors, it is recommended to use _cursor as a suffix, such as emp_cursor

4) Exceptions, it is recommended to use E_ as a prefix, such as E_integrity_error

5) when defining a PL/SQL table type, it is recommended to use _table_type as a suffix, such as Sql_table_type

6) when defining a PL/SQL table variable, it is recommended to use _table as a suffix, such as sal_table

7) when defining a PL/SQL record type, it is recommended to use _record_type as a suffix, such as Emp_record_type

8) when defining a PL/SQL record variable, it is recommended to use _record as a suffix, such as Emp_record

2. Casing Rules

1) SQL keyword in uppercase format, such as select UPDATE SET where

2) PL/SQL keywords in uppercase format, such as declare BEGIN end, etc.

3) data type in uppercase format, such as int VARCHAR2 DATE

4) Identification compliance parameters in lowercase format, such as V_sal c_rate, etc.

5) database objects and columns in lowercase format, such as EMP sal ename, etc.

Iii. PL/SQL Programming structure

1. The basic structure of PL/SQL program blocks

DECLARE

--declaration part: Used to define variable initialization variables, etc.

BEGIN

--The execution section is used to execute the query and other operations departments

EXCEPTION

--To handle the exception part

END;

/

1) Declaration department: Mainly used to declare variables, constants, data types, cursors, exception handling names, and local subroutine definitions, and this part is started by declare.

2) Executive department: This part through variable assignment, process control, data query, data manipulation, data definition and other implementation functions, starting from the keyword begin.

3) Exception Handling section: Handling Exceptions or errors

4)/:P the L/SQL program block needs to end with a forward slash (/) to execute.

Note the point:

The execution section is mandatory, and the Declarations section and the exception section are optional

Each PL/SQL is started by Begin/declare, and end-of-

Each statement must end with a semicolon, the SQL statement can be multi-line, but the semicolon represents the end of the statement, and the other row can have multiple SQL statements separated by semicolons.

Example code:

DECLARE

A number (4); --Declaring a variable

BEGIN

a:=1+2;

Dbms_output. Put_Line (' 1+2= ' | | a);--Output variable value

EXCEPTION--Exception handling

Wehn OTHERS Then

Dbms_output. Put_Line (' abnormal ');

END;

/

Explanation: Dbms_output is a System package provided by Oracle, and Put_Line is the process that the package contains to output string information. When you use the Dbms_output package to output data or messages, you must set the Sql*plus environment variable serverout to ON

2.pl/sql Data types

For a PL/SQL program, its constants and variable data types, in addition to using the same data type as SQL, have a specific type

Boolean evaluates to true false or null

Binary_integer signed integer

NATURAL subtype of Binary_integer, representing nonnegative integers

Naturaln subtype of Binary_integer, non-null nonnegative integer

POSITIVE of Binary_integer, representing positive integers

Positiven of Binary_integer, representing a positive integer that is not null

Signtype subtype of Binary_integer, with a value of-1, 0, or 1

Pls_integer Pls_integer is a data type used specifically for PL/SQL programs, and it cannot be used in columns created by tables. Pls_integer a smaller representation range than the number variable, taking up less. Pl_integer is more advantageous for CPU use

Simple_integer 11G new Type, Binary_integer subtype, has the same value range as Binary_integer, but cannot be a null value. If memory overflow is not triggered when used

exception, the result is simply truncated.

STRING is the same as VARCHAR2

RECORD a group of other types of combinations

REF cursor points to a row-level pointer

3. Variables and Constants

1)

The most commonly used variable is a scalar variable.

Variable_name data_type [NOT NULL: =default value];

Variable_name variable Name

Not NULL: You can define a non-null constraint on a variable, and if you use this option, you must assign a value to the variable, and you cannot change the variable to null in other places in the program

Example:

Define a variable and assign a value of 200 to the variable

Declare    v_num Number (4); begin   V_num:=;   Dbms_output.put_line (' variable value: ' | | V_num); End ; /

Define a variable v_num of type number and assign a value of 200, and then use the Dbms_out.put_line procedure to output the value of the V_num variable

2) Constants

CONSTANT_NAME constant Data_type {: =default}value;

CONSTANT_NAME constant Name

Constant modifier for defining constants

The data type of the DATA_TYPE constant

: =|default constant Assignment, where: = The assignment operator cannot have a space between the colon and the equal sign.

Example:

DECLARE Number (4): = $;    begin    dbms_output.put_line (' constant value:'| | v_num); End ; /

4. Composite data type

Composite data types are mainly composed of three kinds:%type%rowtype and custom record types

1)%type type

Sometimes you use the data in a table to assign values to variables, you first need to know the class data type of the variable, otherwise you cannot determine the data type of the variable. You can use%type to resolve this,%type is used to implicitly specify the data type of the variable as the data type of the corresponding column.

Format:

Var_name Table_name.cloumn_name%type[not Null]{:=|default}value

Var_name variable Name

TABLE_NAME Table name

The name of the data column in the Cloumn_name table for the data to be assigned to the variable

Declare variable types V_name and v_deptno using the%type keyword and query the employee name and department number 200 from the Employess table

Declare   v_name employess.name%type;  V_deptno employess.department_id%type;   begin    Select  into V_name,v_deptno       from where employe_id=N;   End ;   /  

2) record type

Record types are stored as a unit of data related to a set of logical types, and record types can be understood to encapsulate variables of multiple types to define a variable of type called record type and then use the equivalent Java class

, the object has multiple properties, called by the class name. Property name. However, you must include a common variable or a member of the record data type. Use a record type variable to define a variable for a record type mechanism (a common understanding is to define the structure of a record type) before you can declare a variable of a record type. The definition record type must use the Type statement, which indicates the fields that will be included in the record and the data type in the statement.

Grammar:

Type Record_name is record (

Field1_name data_type[not NULL] [: =default_value],

Field2_name data_type[not NULL] [: =default_value];

)

Record_name The custom record data type name, such as number

Field1_name field names in the record data type

Data_type: The data type of the field

Example:

Defines a record type that is named Record_my_type, which consists of a string of V_name and an integer v_age variable named Re_person

Declare   --defines a type of structure for a record data type (equivalent to a number of normal types)type Record_my_type record (V_namevarchar, V_age Number; )  --declaring record type variablesRe_person Record_my_type;begin     SelectName,age intoRe_person fromTb_personwhereId= -; Dbms_output.put_line ('Name:'||re_person.v_name); Dbms_output.put_line ('Age:'||re_person.v_age); End; /

PL/SQL Basics