The records of compound data types in 10.pl_sql--pl_sql

I. Overview of records and collections

The data types of the variables introduced so far are scalar, thatis, scalar, such as shaping, string, date, and so on. Any programming language needs to provide some means for users to create more complex variables,and PL/SQL is no exception, this chapter introduces the compound variables in PL/SQL.

the composite variables of PL/SQL consist of two main types:

1. Records: Record

2. Collection: collection. Collection can also be divided into three kinds,

A. Associativearray (indextby table): associative array (or Index table, and Index of table in SQL not a concept)

B. Nestedtable: Nested tables

c. varray: variable-length list.

Ii. differences in records and collections

Records can be visually understood as a container to hold many other data types, but each data type appears only once in a record, such as a record in which the following data types are stored:

PL/SQL Record :

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/49/1C/wKioL1QPATjxf_eiAAAdTByg0cY451.png "title=" 1.PNG " alt= "Wkiol1qpatjxf_eiaaadtbyg0cy451.png"/>

This record holds the Boolean, date, character, and BLOB -type variable, and each variable appears only once, and each space that holds another data type is called a domain (field)

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/49/1C/wKioL1QPAZuR3wL7AAC_MwPYfO0099.jpg "style=" float: none; "title=" 2.png "alt=" Wkiol1qpazur3wl7aac_mwpyfo0099.jpg "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/49/1A/wKiom1QPAY_jfEnyAAF22DV7n7E844.png "style=" float: none; "title=" 3.PNG "alt=" Wkiom1qpay_jfenyaaf22dv7n7e844.png "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/49/1C/wKioL1QPAZyS9zgFAAGD_WCrkiM251.jpg "style=" float: none; "title=" 4.png "alt=" Wkiol1qpazys9zgfaagd_wcrkim251.jpg "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/49/1A/wKiom1QPAZDgUU8EAAELPODtTI4578.png "style=" float: none; "title=" 5.PNG "alt=" Wkiom1qpazdguu8eaaelpodtti4578.png "/>

Sql> Edit

DECLARE

TYPE Myrecord is RECORD

(ID number (8),

Name VARCHAR2 (default ' Wilson '),

Birthday DATE

);

Person Myrecord;

BEGIN

Person.id: = 12345;

Person.birthday: =sysdate;

Dbms_output. Put_Line (' Name: ' | | person.name);

END;

/

Sql>/

Name:wilson

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/49/1A/wKiom1QPAeXTTtUBAAERl3WwbeI118.png "style=" float: none; "title=" 6.PNG "alt=" Wkiom1qpaextttubaaerl3wwbei118.png "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/49/1A/wKiom1QPAebByn2BAAFImHmM4wQ044.jpg "style=" float: none; "title=" 7.png "alt=" Wkiom1qpaebbyn2baafimhmm4wq044.jpg "/>

===============================

Sql> Edit

DECLARE

Person Employees%rowtype;

BEGIN

SELECT * into person

From Employees whereemployee_id = 100;

Dbms_output. Put_Line (' Name: ' | | person.first_name);

END;

/

Sql> @notes/s37.sql

Name:steven

PL/SQL procedure successfully completed.

===============================================

Eg3.

Sql> Edit

DECLARE

TYPE T_rec is RECORD

(V_sal number (8),

V_minsal Number (8) Default 1000,

V_hire_dateemployees.hire_date%type,

V_reclemployees%rowtype);

V_myrec T_rec;

BEGIN

V_myrec.v_sal: = v_myrec.v_minsal+ 500;

V_myrec.v_hire_date: =sysdate;

SELECT * into V_myrec.v_recl

From Employees whereemployee_id = 100;

Dbms_output. Put_Line (V_myrec.v_recl.last_name | |     "| | To_char (v_myrec.v_hire_date) | |     "| | To_char (v_myrec.v_sal));

END;

/

Sql> @notes/s38.sql

King 24-aug-14 1500

PL/SQL procedure successfully completed.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/49/1C/wKioL1QPAkODyntOAAEsN0oho4o337.jpg "style=" float: none; "title=" 8.png "alt=" Wkiol1qpakodyntoaaesn0oho4o337.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/49/1A/wKiom1QPAjix1SiOAAI3nQHONrk784.jpg "style=" float: none; "title=" 9.png "alt=" Wkiom1qpajix1sioaai3nqhonrk784.jpg "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/49/1C/wKioL1QPAkTS69p7AAHBwks03qs126.png "style=" float: none; "title=" 10.PNG "alt=" Wkiol1qpakts69p7aahbwks03qs126.png "/>

CREATE TABLE Retired_emps (

EMPNO Number (4),

Ename VARCHAR2 (25),

JOB VARCHAR2 (9),

MGR Number (4),

HireDate DATE,

Leavedate DATE,

SAL number (7,2),

COMM number (7,2),

DEPTNO Number (2)

);

Sql> @notes/s39.sql

Table created.

Sql> SELECT * from retired_emps where empno = 124;

No rows selected

Sql> Edit

DECLARE

V_employee_number Number: = 124;

V_emp_rec Employees%rowtype;

BEGIN

SELECT * into V_emp_rec fromemployees

WHERE employee_id =v_employee_number;

INSERT intoretired_emps (empno, ename, job, Mgr,

Hiredate,leavedate, Sal, Comm, Deptno)

VALUES (V_emp_rec.employee_id,v_emp_rec.last_name,

V_EMP_REC.JOB_ID,V_EMP_REC.MANAGER_ID,

V_emp_rec.hire_date,sysdate,

v_emp_rec.salary,v_emp_rec.commission_pct,

V_EMP_REC.DEPARTMENT_ID);

COMMIT;

END;

/

Sql> @notes/s40.sql

PL/SQL procedure successfully completed.

Sql> SELECT * from retired_emps where empno = 124;

EMPNO ename JOB MGR hiredate leavedate SAL COMM DEPTNO

---------- ----------      --------------- --------- ------------------- ---------      ---------- ---------- ----------

124 Mourgos St_man 16-nov-07 24-aug-14 5800 50

=========================insert============================

Sql> Edit

DECLARE

V_employee_number Number: = 127;

V_emp_rec Retired_emps%rowtype;

BEGIN

SELECT employee_id, last_name, job_id,manager_id,

Hire_date, Hire_date, salary,commission_pct,

department_id into V_emp_rec

From Employees

WHERE employee_id = V_employee_number;

INSERT into Retired_emps

VALUES V_emp_rec;

-- Package query results into v_emp_rec

END;

/

SELECT * FROMRETIRED_EMPS;

Sql> @notes/s41.sql

PL/SQL proceduresuccessfully completed.

EMPNO ename JOB MGR hiredate leavedate SAL COMM DEPTNO

----------------------------------- --------- ---------- --------- --------- -------------------- ----------

124 Mourgos St_man 16-nov-07 24-aug-14 5800 50

127 Landry St_clerk 14-jan-07 14-jan-07 3200 50

=========================update============================

Sql> Edit

SET VERIFY OFF

DECLARE

V_employee_number number:= 127;

V_emp_recretired_emps%rowtype;

BEGIN

SELECT *

Into V_emp_rec

From Retired_emps

WHERE empno =v_employee_number;

V_emp_rec.leavedate: =current_date;

UPDATE retired_emps Setrow = V_emp_rec

WHERE empno = V_employee_number;

END;

/

SELECT * from Retired_emps;

Sql> @notes/s42.sql

PL/SQL procedure successfully completed.

EMPNO ename JOB MGR hiredate leavedate SAL COMM DEPTNO

---------- --------------  ---------- --------- ---------- ------------------    ---------- ---------- ----------

124 Mourgos St_man 16-nov-07 24-aug-14 5800 50

127 Landry St_clerk 14-jan-07 24-aug-14 3200 50

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/49/1C/wKioL1QPArXQDxNRAAGsD3v0LPI205.png "style=" float: none; "title=" 11.PNG "alt=" Wkiol1qparxqdxnraagsd3v0lpi205.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/49/1A/wKiom1QPAqmjuVqOAAC-yXK7CQg657.png "style=" float: none; "title=" 12.PNG "alt=" Wkiom1qpaqmjuvqoaac-yxk7cqg657.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/49/1C/wKioL1QPArbS6aFbAAGRwZQ_BQQ632.png "style=" float: none; "title=" 13.PNG "alt=" Wkiol1qparbs6afbaagrwzq_bqq632.png "/>

Sql> Edit

DECLARE

Myrec Employees%rowtype;

BEGIN

Myrec: = NULL;

END;

/

Sql> @notes/s43.sql

PL/SQL procedure successfully completed.

This article is from the "Big sword without front of the great Qiao Not Work" blog, please make sure to keep this source http://wuyelan.blog.51cto.com/6118147/1550342

The records of compound data types in 10.pl_sql--pl_sql