PL/SQL-variables and plsql Variables

PL/SQL-variables and plsql Variables

1. PL/SQL Introduction

PL/SQL is also a program Language called Procedural Language/SQL ). PL/SQL is an extension of oracle's SQL statements. It adds the features of programming languages to the use of common SQL statements, therefore, PL/SQL organizes data operations and query statements into procedural units of PL/SQL code, and implements complex functions or programming languages through logical judgment and loop operations, it can only run in oracle. Of course, other databases also have their own "pl/SQL", which is not unique to oracle. For example, there are also mysql databases, but each database has different functions. PL/SQL in oracle is much more powerful than mysql.

PL/SQL has the following advantages:

• It makes the functions of a group of SQL statements more modular;
• Adopts the structure of Procedural language control programs;
• You can automatically handle errors in the program so that the program will not be interrupted when an error occurs;
• It has good portability and can be transplanted to another Oracle database;
• Integrated into the database, faster calls;
• Reduces Network Interactions and improves program performance.

The PL/SQL block consists of four basic parts: Declaration, execution body start, exception handling, and execution body end. The basic structure of the four parts is as follows:

DECLARE -- optional part -- variable, constant, cursor, user-defined EXCEPTION declaration BEGIN -- necessary part -- execution program EXCEPTION composed of SQL statements and PL/SQL statements -- optional part -- program EXCEPTION, capture exceptions and handle exceptions END; -- required

Ii. PL/SQL Variables

1. program variables

PL/SQL supports data types in SQL, including NUMBER, VARCHAR2, DATE, and other Oracle SQL data types. The declared variable must specify the Data Type of the variable, or set the initial value for the variable when declaring the variable. The variable Declaration must be in the DECLARE section. The syntax for declaring variables is as follows:

Variable name data type: = initial value; -- set the Initial Value

Variable name data type; -- do not set the Initial Value

The value assignment of a variable must be performed directly at the beginning and end. You can directly use: = to assign values, or use into in the select statement to assign values. The sample code is as follows:

DECLARE v_name VARCHAR2 (50); -- DECLARE the variable v_nickname VARCHAR2 (50): = 'John '; -- DECLARE the variable and set the initial value v_age NUMBER; BEGIN v_name: = 'wang 5 '; -- assign SELECT age INTO v_age FROM person WHERE id = 1 to the variable; -- set the value by querying -- use oracle dbms to output the value of each variable dbms_output.put_line ('v _ name: '| v_name); dbms_output.put_line ('v _ nickname:' | v_nickname); dbms_output.put_line ('v _ age: '| v_age); END;

2. Program Constants

For a query constant, it must be assigned a value during declaration. You cannot set its value again. This is similar to a constant in java. Program constants are modified when they are declared using constant. The declaration syntax is as follows:

Variable name constant data type: = initial value; -- set the Initial Value

The instance code is as follows;

DECLARE c_nickname constant VARCHAR2 (50): = 'John '; -- declares a constant and sets the initial value. If the initial value is not set, an error "BEGIN -- c_nickname: = 'wang 5'" is returned '; -- If you assign a value to a constant, dbms_output.put_line ('C _ nickname: '| c_nickname) is returned. -- print the END value of the constant;

3. bind variables

Bind variables in SQL plus as follows:

Statement:

var v_name varchar2(50);

Assignment

execute :v_name :='hello world';

Print results

 print v_name;

The output is as follows:

This variable only exists in the current session. If the current session is closed and a new connection is enabled, the variable does not exist.

Iii. variable types

In the following sample code, the structure of the person table is as follows:

Id NUMBER (11) not null, username VARCHAR2 (255) NULL, age NUMBER (11) NULL, password VARCHAR2 (255) NULL, primary key (id )) -- ------------------------------ Records of PERSON -- ---------------------------- insert into person values ('1', 'zhang san', '20', 'zhang123 '); insert into person values ('2 ', 'Li si', '20', 'lisi123 '); insert into person values ('3', 'wang wu', '20', 'wang123 '); insert into person values ('4', 'zhao liu', '20', 'zhao123 ');

1.% type

% Type is mainly used to define the data type of a variable. It is the same as the known data type of the variable or the data type of a class in the table. The advantages of using % type are as follows: 1. When we do not know the Data type of a field in the data table, we can use it; 2. The data type of fields in the database can be changed at runtime, so we do not need to modify the program because the % type in the program will deteriorate accordingly with the field type.

The statement syntax is as follows:

Variable name table name. Field name % type;

The sample code is as follows:

DECLARE v_id person. id % TYPE; -- The v_id variable data TYPE is the id data TYPE v_username person in the person table. username % TYPE; -- The v_id variable data TYPE is the username data TYPE v_age person in the person table. age % TYPE; -- The v_id variable data TYPE is the age data TYPE in the person table v_password person. password % TYPE; -- The v_id variable data TYPE is the data TYPE of the password in the person table begin select id, username, age, password INTO v_id, v_username, v_age, v_password FROM person where id = 1; values ('Id: '| v_id); dbms_output.put_line ('username:' | v_username); dbms_output.put_line ('Age: '| v_age); dbms_output.put_line ('password: '| v_password); END;

Query the records with id 1 and print the results.

2.% rowtype

% Rowtype is also a variable used to define an uncertain type. It can be understood as a copy extracted from a row of database records. With % rowtype, we can get a row of records, and then use the variable. attribute name to get a single attribute value. The syntax for declaring % rowtype is as follows:
Variable name table name % rowtype;

The sample code is as follows:

1 DECLARE 2 r_person person % rowtype; -- represents a row of 3 BEGIN 4 in the person table -- Method 1 assign a value of 5 SELECT id, username, age, password INTO r_person.id, r_person.username, r_person.age, r_person.password FROM person WHERE id = 1; 6 dbms_output.put_line ('Id: '| r_person.id); 7 bytes ('username:' | r_person.username); 8 dbms_output.put_line ('Age: '| r_person.age); 9 dbms_output.put_line ('password:' | r_person.password); 10 -- method 2 Assignment 11 SELECT * INTO r_person FROM person WHERE id = 2; 12 bytes ('Id: '| r_person.id); 13 dbms_output.put_line ('username:' | r_person.username); 14 dbms_output.put_line ('Age: '| r_person.age ); 15 dbms_output.put_line ('password: '| r_person.password); 16 END;

You can assign values using the preceding two methods. It can be used in the cursor.

3. varray

Varray (varing array) is a dynamic array type in PL/SQL. We can dynamically expand the array size, but the total size after expansion cannot exceed the declared size. When using varray, you must declare the type of a varray array, declare a variable, and assign this type to it. These variables are used to operate the declared variables.

Example:

1 DECLARE 2 TYPE arrays is varray (7) OF VARCHAR2 (10); -- defines an array with five elements. Each element type is varchar2 (10) 3 -- arrays is an object, which must be referenced with a variable before use. v_list is the variable 4 v_list arrays: = arrays ('zhangsan', 'lisi', 'wangw ', 'zhaoliu', 'wangw'); 5 asize NUMBER; -- Record array size 6 BEGIN 7 dbms_output.put_line (v_list (1); 8 dbms_output.put_line (v_list (2 )); 9 dbms_output.put_line (v_list (3); 10 dbms_output.put_line (v_list (4); 11 Dbms_output.put_line (v_list (5); 12 -- dbms_output.put_line (v_list (6); at this time, the print will keep the subscript out of bounds. 13 asize: = v_list.COUNT (); 14 dbms_output.put_line ('total: '| asize); 15 -- increase the v_list size by 16 v_list.EXTEND (2 ); -- the total size of the added elements cannot exceed 17 asize: = v_list.COUNT (); 18 dbms_output.put_line ('total: '| asize); 19 v_list (6 ): = 'xiaozhao'; 20 v_list (7): = 'xiaoli'; 21 dbms_output.put_line (v_list (6); 22 dbms_output.put_line (v_list (7 )); 23 -- reduce the v_list size by 24 v_list.TRIM (2); 25 asize: = v_list.COUNT (); 26 dbms_output.put_line ('total: '| asize); 27 END;

16 rows expand the array, and 17 rows reduce the array size. The running result is as follows:

4. table

The table type is similar to the array in javascript and can be considered as a variable array. It can be understood that its size is infinite. We can assign values to the space corresponding to any of its indexes, and we do not need to specify its size when declaring it.

The sample code is as follows:

1 DECLARE 2 TYPE strings is table of VARCHAR2 (10) -- The element type is varchar2 (10) 3 index by binary_integer; 4 v_list strings; -- the defined table cannot be used directly, the other variable 5 BEGIN 6 v_list (1): = 'hello'; 7 v_list (9999): = 'World'; 8 v_list (99): = 33; -- if the value is a numeric value, it is converted to a character. If the value is a numeric value, 9 dbms_output.put_line (v_list (1) error is returned )); 10 dbms_output.put_line (v_list (99); 11 dbms_output.put_line (v_list (9999); 12 END;

The running result is as follows:

5,Record

Record can be understood as a set in java and can store multiple types of data. The record also needs to be declared first, then the variable should be declared to assign values, and then the variable should be operated.

The sample code is as follows:

1 DECLARE 2 TYPE v_record is record (3 id number, 4 username person. username % TYPE, 5 r_tb_person person % ROWTYPE 6); 7 v_person v_record; -- record cannot be used directly. It must be assigned to variable 8 BEGIN 9/* select id, username, age, password into v_person.r_tb_person.id, region, v_person.r_tb_person.age, v_person.r_tb_person.password from person where id = 1; in this way, you can assign values */10 SELECT * INTO v_person.r_tb_person FROM person WHERE id = 1; 11 bytes ('Id: '| v_person.r_tb_person.id); 12 dbms_output.put_line ('username:' | bytes); 13 dbms_output.put_line ('Age: '| v_person.r_tb_person.age ); 14 dbms_output.put_line ('password: '| v_person.r_tb_person.password); 15 END;

How to Use variables in pl/SQL like clauses

If the ss is of the varchar type
SQL Server:
Like '%' + @ ss + '%' (all User-Defined variables in SQL Server start)
Oracle:
Like '%' | ss | '%'

Pl/SQL variable usage

Define and use variables

PL/SQL has four types: scalar type, composite type, reference type (reference), LOB (Large Obejct) Type

1. scalar type

The most common type is the scalar type. It refers to the variable that can only store a single value, including the numeric type, character type, date type, and boolean type. Each type also contains the corresponding child type.

Constant scalar type:

VARCHAR2 (n), CHAR (n), NUMBER (p, s), DATE, TIMESTAMP, LONG, long raw, BOOLEAN, BINARY_INTEGER (only PL/SQL ), BINARY_FLOAT and BINARY_DOUBLE (10 Gb new)

Define scalar:

Identifier [CONSTANT] datatype [not null] [: = | DEFAULT expr]

When using a scalar, note that the = sign is replaced by: =, which is the same as the value assignment symbol @ _ @ in delphi @_@

Example:

V_name VARCHAR2 (10 );
Vanderbilt Rate constants number (4, 2): = 3.04;

To prevent the defined variable TYPE from being inconsistent with the field TYPE in the table, you can use % TYPE to define it:

V_name employee. name % TYPE;

As shown above, the v_name type is the same as the name field type in the table "employee !!

2. Compound variables:

Variable used to store multiple values is called a composite variable, including PL/SQL records, PL/SQL tables, nested tables, and VARRAY.

1. PL/SQL records

Similar to the structure concept in C/C ++:

Declare
TYPE employee_record is RECORD (
Id employee. id % TYPE,
Name employee. name % TYPE,
Email employee. email % TYPE );
Em_record employee_record;
Begin
Select id, name, email into em_record from employee where name = & name;
Dbms_output.put_line ('employee name: '| em_record.name | 'employee ID:' | em_record.id );
End;

2. PL/SQL tables are similar to arrays. The difference is that PL/SQL tables allow negative subscripts without the upper and lower limits, for example:

Declare
TYPE employee_table is table of employee. name % TYPE index by BINaRY_INTEGER;
Em_table employee_table;
Begin
Select name into em_table (-1) from employee where name = & name;
Dbms_output.put_line ('employee name: '| em_table (-1 ));
End;

3. A nested table is similar to a PL/SQL table. The difference is that a nested table can be the Data Type of a table column, but a PL/SQL table cannot. When a nested table is used as a table column, you must specify a special storage table for it, such:

Create... the remaining full text>