PL/SQL 02 declaring variable declare

Grammar:
identifier [CONSTANT] datatype [not NULL] [: = | DEFAULT Expr]

Identifier: Used to specify the name of a variable or constant.
CONSTANT: Used to specify constants. When you define a constant, you must specify its initial value, and its value cannot be changed.
DataType: The data type used to specify a variable or constant.
Not NULL: Used to force the initialization of a variable (cannot be null). When you specify a not NULL option, you must provide a value for the variable.
: = used to specify initial values for variables and constants.
Default: Used to specify the initial value for constants and variables.
Expr: a PL/SQL expression that specifies the initial value, which can be a literal value, other variables, functions, and so on.

Cases:
V_ename VARCHAR2 (10);
V_sal number (6,2);
V_balance binary_float; --oracle 10g New data type
C_tax_rate CONSTANT Number (3,2): = 5.5;
V_hiredate DATE;
V_valid BOOLEAN not NULL DEFAULT FALSE;

V_sal Emp.sal%type; --%type
V_tax_sal V_sal%type; --You can use the%type of variables

--Composite variables

1, record variables (similar to high-level language structure)
DECLARE
TYPE Emp_record_type is record (
Name Emp.ename%type,
Salary Emp.sal%type,
Title Emp.job%type);
Emp_record Emp_record_type;
BEGIN
Select Ename,sal,job into Emp_record
from EMP where empno=7788;
Dbms_output.put_line (' Employee Name: ' | | Emp_record.name); --record variables. Record members
END;

2. Table variables (arrays like high-level languages)
It is important to note that the PL/SQL table variable differs from an array of high-level languages, and the subscript of a high-level language array cannot be negative, but the subscript of a PL/SQL table variable can be negative, the number of elements in a high-level language is limited, and the number of elements in a PL/SQL table variable is not limited.

DECLARE
TYPE Ename_table_type is table of Emp.ename%type
INDEX by Binary_integer;
Ename_table Ename_table_type;
BEGIN
Select Ename to Ename_table ( -1) from EMP
where empno=7788;
Dbms_output.put_line (' Employee Name: ' | | Ename_table (-1));
END;

3. Nested tables (Nested table)
Nested tables are similar to arrays of high-level languages. It is important to note that the subscript for high-level language arrays and nested tables cannot be negative, the number of elements in a high-level language is limited, and the number of elements in a nested table is unlimited. nested tables and PL/SQL table variables are very similar, but a nested table can be a data type for a table column, and a PL/SQL table variable cannot be a data type for a table column. When you use a nested table in a table column, you must first create a nested table type in the CREATE TYPE statement.

CREATE OR REPLACE TYPE emp_type as OBJECT (
Name VARCHAR2 (10),
Salary number (6,1),
HireDate date);
/
CREATE OR REPLACE TYPE Emp_array is TABLE of Emp_type;
/

The object type Emp_type is used to store employee information, and Emp_array is a emp_type-based nested table type that can be used to store information about multiple employees. When a nested table type is established, it can be referenced as a user-defined data type in a table column or object property. However, it is important to note that when you use a nested table type as a table column, you must specify a dedicated storage table for it.

CREATE TABLE Department (
Deptno Number (2),
Dname VARCHAR2 (10),
Employee Emp_arry
) NESTED TABLE employee STORE as employee; --employee for storage tables

4, Varray (variable-length array)
Similar to a nested table, it can be used as a data type for table columns and object type properties. However, it is important to note that there is no limit to the number of elements in the nested table, and the number of elements in the Varray is limited. When you use Varray, you must first establish the Varray type.

CREATE TYPE Article_type as OBJECT (
Title VARCHAR2 (20),
Pub DATE
);
/
CREATE TYPE Article_arry is VARCHAR2 ($) of Article_type;
/

Object type Article_type is used to store article information, while Article_arry is used to store information for multiple articles and can store up to 20 articles. When the Varray type is established, it can be referenced as a user-defined data type in a table or object property.

Such as:
CREATE TABLE Author (
ID Number (6),
Name VARCHAR2 (10),
Article Article_arry
);

Note that the nested table column data needs to be stored in a dedicated storage table, while the Varray data is stored in the table segment along with other column data.

--Reference variables
A reference variable is a variable used to hold a numeric pointer. By using reference variables, you can make your application share the same objects, thereby reducing space consumption.

1. REF CURSOR
When you use an explicit cursor, you need to specify the corresponding SELECT statement when you define an explicit cursor, which is called a static cursor. When you use a cursor variable, you do not need to specify a SELECT statement when you define a cursor variable, but you specify a SELECT statement when you open the cursor, enabling dynamic cursor operation.

DECLARE
TYPE C1 is REF CURSOR;
Emp_cursor C1;
V_ename Emp.ename%type;
V_sal Emp.sal%type;
BEGIN
OPEN Emp_cursor for
Select Ename,sal from emp where deptno=10;
LOOP
FETCH emp_cursor into V_ename,v_sal;
EXIT when Emp_cursor%notfound;
Dbms_output.put_line (V_ename);
END LOOP;
CLOSE Emp_cursor;
END;
/

C1 is a REF cursor type, and emp_cursor is a cursor variable, and its corresponding SELECT statement is specified when the cursor variable is opened.

2. REF Obj_type
When writing an object type application, in order to share the same object, you can use ref to refer to the object type, which is actually a pointer to an object instance.

The following example shows how to use REF. First establish the object type home and object Table homes, and then insert the data.

CREATE OR REPLACE TYPE home_type as OBJECT (
Street VARCHAR2 (50),
City Varchar2 (20),
State VARCHAR2 (20),
ZipCode VARCHAR2 (6),
Owner VARCHAR2 (10)
);
/
CREATE TABLE Homes of Home_type;
INSERT into homes values (' Hullen North No. 12th ', ' hohhot ', ' Inner Mongolia ', ' 010010 ', ' Mindy ');
INSERT into homes values (' Hullen North No. 13th ', ' hohhot ', ' Inner Mongolia ', ' 010010 ', ' Qin Bin ');
Commit

The object Table homes holds the home and the name of the householder. Assuming that there are four people per family, you can use REF to apply the Home_type object type to reduce footprint when demographic is used to allow family addresses to be shared by each family member in the same family. Examples are as follows:

CREATE TABLE Person (
ID Number (6) primary key,
Name VARCHAR2 (10),
Addr REF Home_type
);
INSERT into person Select 1, ' Marvin ', ref (P)
From homes p where p.owner= ' Mindy ';
INSERT into person select 2, ' Ma Wu ', ref (P)
From homes p where p.owner= ' Mindy ';
INSERT into person Select 3, ' Horse full ', ref (P)
From homes p where p.owner= ' Mindy ';
INSERT into person Select 4, ' Horse Only ', ref (P)
From homes p where p.owner= ' Mindy ';
Commit

When inserting data for the person table, the addr column will hold an address pointer to the corresponding data in the homes table.

--Non-PL/SQL variables
When you interact with a PL/SQL block in a sql*plus or application (for example, pro*c/c++), you need to use the Sql*plus variable or the application variable to complete the data. When you reference a non-PL/SQL variable in a PL/SQL block, you must add a colon (":") before the non-PL/SQL variable.

1. Use Sql*plus variable
When you reference a sql*plus variable in a PL/SQL block, you must first define the variable using the VARIABLE command, and you need to use the PRINT command if you want to output the variable contents in Sql*plus.

var name varchar2 (10)
Begin
Select Ename into:name from emp
where empno=7788;
End
sql> Print name;

NAME
--------------------------------
SCOTT

2. Use the Procedure Builder variable
When referencing the Procedure Builder variable in a PL/SQL block, you must first define the variable with the CREATE command, and if you output the variable contents in Procedure Builder, you can use the package text_io.

pl/sql> CREATE CHAR name LENGTH 10
Pl/sql> BEGIN
+> SELECT ename into:name from emp
+> WHERE empno=7788;
+> END;
Pl/sql> Text_io. Put_Line (: Name);
SCOTT

3. Use pro*c/c++ variable
When you reference a host variable for a pro*c/c++ program in a PL/SQL block, you must first define the host variable, and if you want to output the variable content, you can use printf ().

Char name[10];
EXEC SQL EXECUTE
BEGIN
SELECT ename into:name from emp
WHERE empno=7788l
END;
End-exec;
printf ("Employee Name:%s\n", name);

--Three small examples of sql*plus
1. Output text
Sql> DECLARE
2 txt VARCHAR2 (20);
3 BEGIN
4 txt:= ' Huangheshizgdemuqin ';
5 dbms_output.put_line (TXT);
6 END;
7/
Huangheshizgdemuqin

PL/SQL procedure successfully completed.

2. Substitution variables
Sql> DECLARE
2 a Binary_integer;
3 b Binary_integer;
4 BEGIN
5 a:=&x;
6 b:=&y;
7 Dbms_output.put_line (A/b);
8 END;
9/
Enter value for X:9
Old 5:a:=&x;
New 5:a:=9;
Enter value for Y:3
Old 6:b:=&y;
New 6:b:=3;
3

3, Sql*plus variable assignment, and output
sql> var zg VARCHAR2 (10)
Sql> BEGIN
2:zg:= ' Zhongguo ';
3 END;
4/

PL/SQL procedure successfully completed.

sql> print ZG;

ZG
--------------------------------
Zhongguo

--Examples of experiments
Declare
Stuname VARCHAR2 (10): = ' Zhang San '; --Assigning values in declarations
Stubir date;
Begin
Stubir:=to_date (' 2012-1-1 ', ' yyyy-mm-dd '); --Assigning values in the program body
The value of Dbms_output.put_line (' Stuname ' is: ' | | Stuname);
The value of Dbms_output.put_line (' Stubir ' is: ' | | Stubir);
End

Declare
Name Emp.ename%type; --A column type in the reference table of a variable type
Begin
name:= ' John ';
Dbms_output.put_line (' name is: ' | | name);
End

Declare
no001 Emp.empno%type;
name001 Emp.ename%type;
Begin
Select Empno,ename--store SQL query results in variable (one row must be returned, not multiple rows)
Into no001,name001
From EMP
where empno=7369;
Dbms_output.put_line (' no001 is: ' | | NO001);
Dbms_output.put_line (' name001 is: ' | | NAME001);
End

Declare
Type Emptype is record (--record variable (contains multiple variables)
No Emp.empno%type,
Name Emp.ename%type,
Date Emp.hiredate%type
);
e Emptype;
Begin
Select Empno,ename,hiredate
Into E
From EMP
where empno=7369;
Dbms_output.put_line (' No is: ' | | e.no);
Dbms_output.put_line (' name is: ' | | E.name);
Dbms_output.put_line (' date is: ' | | E.date);
End

Declare
e Emp%rowtype; --record variable another way of writing
Begin
SELECT *
Into E
From EMP
where empno=7369;
Dbms_output.put_line (' No is: ' | | E.EMPNO);
Dbms_output.put_line (' name is: ' | | E.ename);
Dbms_output.put_line (' date is: ' | | E.hiredate);
End

Declare
Type etype is table of emp.ename%type--table variable (multiple types of the same variable, equivalent to an array)
Index by Binary_integer;
Enames EType;
Begin
Enames (0): = ' SMITH ';
Enames (1): = ' ALLEN ';
Enames (2): = ' WARD ';
Dbms_output.put_line (enames (0));
Dbms_output.put_line (Enames (1));
Dbms_output.put_line (Enames (2));
End

--Table Variable Example 1

Declare
Type cc is table of sys_dmp01%rowtype;----table type
V_tab cc;
I number default 1;
Begin
SELECT * Bulk collect into V_tab-----------query results into a table type bulk collect (* Change to field will be an error?) )
From SYS_DMP01; -----------Four Records
For I in 1..v_tab.count loop
INSERT into AA (ml_id,
MLMC,
Mlyt
MLLB,
ZTMC,
ZTDX,
Qjs,
BJs
XT_ID)
VALUES (V_tab (i). ml_id,
V_tab (i). MLMC,
V_tab (i). Mlyt,
V_tab (i). MLLB,
V_tab (i). ZTMC,
V_tab (i). ZTDX,
V_tab (i). Qjs,
V_tab (i). BJS,
V_tab (i). xt_id);
End Loop;
End

--Table Variable Example 2

Declare
Type T is table of Abc_t.s%type;
BL T;
I number default 1;

Begin
Select s bulk collect into BL
From abc_t;

For I in 1..bl.count
Loop
Dbms_output.put_line (BL (i));
End Loop;
End

--Table Variable Example 3

Declare
Type T is table of Abc_t.s%type;
BL T;
I number default 1;
SM number default 0;
Begin
Select s bulk collect into BL
From abc_t;

Dbms_output.put_line (1);

While I<=bl.count
Loop
Loop
SM:=SM+BL (i);
i:=i+1;
Exit when sm>=60;
End Loop;
sm:=0;
Dbms_output.put_line (i-1);
End Loop;
End

--Table Variable Example 4 (judging 3 consecutive numbers in a field) TT2 is a sorted tt1 table, otherwise the judgment will be confusing
Declare
Type T is table of Tt2%rowtype;
V_tab T;
Begin
SELECT * Bulk collect into V_tab--* changed to field will be error
From TT2;
For I in 1.. V_tab.count-2 Loop-minus 2 here because the last 2 numbers don't have to be judged, or the loop will get an error.
If V_tab (i+2). Id-v_tab (i). id = 2 and V_tab (i+2). Name = V_tab (i). Name Then
Dbms_output.put_line (V_tab (i). name| | ' -' | | V_tab (i). ID);
Dbms_output.put_line (V_tab (i+1). name| | ' -' | | V_tab (i+1). ID);
Dbms_output.put_line (V_tab (i+2). name| | ' -' | | V_tab (i+2). ID);
Dbms_output.put_line ('----------');
End If;
End Loop;
End

PL/SQL 02 declaring variable declare