Shulou(Shulou.com)06/01 Report--

Compound variables can store multiple values of different data types in a single unit. Because compound types can be defined by users as needed, compound data types are also called custom data types.

In PL/SQL, the variable type declared with% TYPE is the same as the data type of the field in the data table, and when the field data type in the data table is modified, the type of the corresponding variable in the PL/SQL program automatically changes.

For example, there is an ENAME field in the EMP table whose data type is VARCHAR2 (30). The declaration variable v_name is used to store the data of the ENAME field, declared as follows:

V_name emp.ename%TYPE

Then the data type of the variable v_name is always the same as that of the ENAME field, that is, VARCHAR2 (30). When the data type of the ENAME field is changed to VARCHAR2 (50), the data type of the v_name variable is automatically changed to VARCHAR2 (50).

DECLARE

V_name emp.ename%TYPE

BEGIN

SELECT ename INTO v_name FROM emp

WHERE empno=7788

DBMS_OUTPUT.PUT_LINE (v_name)

END

/

PL/SQL provides% ROWTYPE to store a row of data. For example, to define a row of records in the DEPT table, and the data type is consistent with each column type, you can use% ROWTYPE, declared as follows:

Dept_record dept%ROWTYPE

Here is an example of using a variable of type% ROWTYPE to store a row of data in the DEPT table:

DECLARE

V_dept dept%ROWTYPE

BEGIN

SELECT * INTO v_dept FROM dept

WHERE deptno=30

DBMS_OUTPUT.PUT_LINE (v_dept.deptno)

DBMS_OUTPUT.PUT_LINE (v_dept.dname)

DBMS_OUTPUT.PUT_LINE (v_dept.loc)

END

/

Record types are very similar to the row structure in a database, and variables defined by record types can store a row of data consisting of multiple column values. When using a variable of a record type, you first need to define the structure of the record, and then define the variable of the record type. When defining a record type, you must use the type statement to indicate the fields that will be included in the record and their data types. The syntax is as follows:

DECLARE

TYPE emp_record_type IS RECORD (

Name emp.ename%TYPE

Salary emp.sal%TYPE

Dno emp.deptno%TYPE);-- Custom variable type emp_record_type

Emp_record emp_record_type;-A variable emp_record that defines the type of emp_record_type

BEGIN

SELECT ename,sal,deptno INTO emp_record

FROM emp WHERE empno=7788;-query ename,sal,deptno assigns values to variable emp_record

DBMS_OUTPUT.PUT_LINE (emp_record.name)

END

/

DECLARE

TYPE emp_record_type IS RECORD (

Name emp.ename%TYPE

Salary emp.sal%TYPE

Dno emp.deptno%TYPE);-- Custom variable type emp_record_type

Emp_record emp_record_type;-A variable emp_record that defines the type of emp_record_type

BEGIN

SELECT ename,sal INTO emp_record.name,epm_record.salary

FROM emp WHERE empno=7788;-query ename,sal,deptno assigns values to variable emp_record

DBMS_OUTPUT.PUT_LINE (emp_record.name)

END

/

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.