[SQL] PL/SQL Syntax of Oracle and summary of Extended Data Types

Document directory

• PL/SQL syntax
• % Type of Custom Data Type
• Record of composite data type
• % Rowtype of composite data type
• Summary of Oracle Extended Data Types

PL/SQL syntax

The PL/SQL program consists of three parts: Declaration, execution, and exception handling.

Template:

Declare/* variable Declaration */begin/* Program subject */exception/* Exception Handling part */end;

Receive user input

PL/SQL blocks can also receive user input information. For example, you are required to enter an employee ID and then query the employee name based on the entered content.
Use "&" to complete user input.
Example:

Declaret_eno number; t_ename varchar2 (30); begin -- the input information is saved in the Eno t_eno: = & No; -- based on the value of Eno, select ename into t_ename from EMP where empno = t_eno; dbms_output.put_line ('Number: '| t_eno |' employee name: '| t_ename ); predictionwhen no_data_found then dbms_output.put_line ('no employee '); end;

% Type of Custom Data Type

Defines a variable that can be consistent with the data type of a column in the table.

The preceding example can also be modified as follows.

Declaret_eno EMP. empno % type; t_ename EMP. ename % type; begin -- the input information is saved in Eno t_eno: = & No; -- and then based on the value of Eno, select ename into t_ename from EMP where empno = t_eno; dbms_output.put_line ('Number: '| t_eno |' employee name: '| t_ename ); exception when no_data_found then dbms_output.put_line ('no employee '); end;

Declaret_eno EMP. empno % type; t_ename EMP. ename % type; begin -- the input information is saved in Eno t_eno: = & No; -- and then based on the value of Eno, select ename into t_ename from EMP where empno = t_eno; dbms_output.put_line ('Number: '| t_eno |' employee name: '| t_ename ); exception when no_data_found then dbms_output.put_line ('no employee '); end;

Record of composite data type

One or more scalar values are encapsulated into an object, which is a composite structure composed of scalar values in multiple columns in a single row. It is similar to a one-dimensional array.
In the following example, the two columns in the query table are put into the record type and the output is displayed.

Declaretype rec_test1 is record (t_empno EMP. empno % type, t_ename EMP. ename % type); -- defines a record type RT rec_test1; -- defines a variable RT using the record type you just defined. beginselect empno, ename into RT. t_empno, Rt. t_ename from EMP where empno = 1001; dbms_output.put_line ('empno: '| RT. t_empno); end;

DECLARE  type rec_test1 is record(  a1 number,  a2 varchar2(10)  );  rt1 rec_test1;  rt2 rec_test1;BEGIN  rt1.a1:=1;  rt1.a2:='china';  rt2.a1:=2;  rt2.a2:='usa';  dbms_output.put_line('rt1.a2:'||rt1.a2);  dbms_output.put_line('rt2.a2:'||rt2.a2);END ;

% Rowtype of composite data type

% Rowtype is a simplified version of record.
The difference is that the former structure is the table structure, and the latter is the custom structure.
There is no big difference between the two. The former is convenient while the latter is flexible.
It is used according to actual conditions.

Use record to update data. If you use record to update data, you can only use record Members. If you use % rowtype to update data, you can use % rowtype directly. For example:

DECLARE  vEmp emp%RowType;Begin  select * InTo vEmp from empa where empa.EMPNO = '7934';  update  empa set ROW = vEmp where EMPNO ='1111';   commit;End; 

[Note] before performing the preceding operations, you need to copy the table EMP to EMPA.
Copy the table structure

CREATE TABLE empa as SELECT * FROM emp WHERE 1=2;

Copy table data

INSERT INTO empa SELECT * FROM emp;

Summary of Oracle Extended Data Types

% Type: the variable type can be consistent with a column in the specified table;
T_empno table name. Column name % type;

% Record: multiple columns in a single row, similar to an array;
1. Create a type. 2. Create a variable of this type.
Type XXX is record (
Column 1 data type (length), column 2 data type (length )...
);
X xxx; -- X is a variable, and XXX is its type.
How to reference a column? X. Column name

% Rowtype: Upgrade simplified version of % record to make it exactly the same as the structure of a table.

X table name % rowtype; -- X is the variable name