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 all scalar, that is, scalars, such as × × ×, 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 compound variables in PL/SQL.
There are two main types of compound variables in PL/SQL:
1. Records: record
2. Collection: assemble. Collection can be divided into three types.
A. Associative Array (INDEXTBY table): associative array (or index table, which is not the same concept as INDEX OF TABLE in SQL)
B. Nested Table: nested tables
C. Varray: variable length list.
Second, the difference between records and collections
Records can be vividly understood as a container for storing many other data types, but each data type appears only once in a record. For example, the following data types are saved in a record:
PL/SQL Record:
Boolean, date, character and blob variables are stored in this record, and each variable appears only once, and each space where other data types are stored is called a Field.
SQL > edit
DECLARE
TYPE myrecord IS RECORD
(id number (8)
Name varchar2 (20) default 'Wilson'
Birthday DATE
);
Person myrecord
BEGIN
Person.id: = 12345
Person.birthday: = SYSDATE
DBMS_OUTPUT.PUT_LINE ('Name:' | | person.name)
END
/
SQL > /
Name: Wilson
= =
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: = vroommyrec.vumbminsal500
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.
CREATE TABLE retired_emps (
EMPNO NUMBER (4)
ENAME VARCHAR2 (25)
JOB VARCHAR2 (9)
MGR NUMBER (4)
HIREDATE DATE
LEAVEDATE DATE
SAL NUMBER (7 dint 2)
COMM NUMBER (7 dint 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 (vested emptiness rec.employeeroomidmvain emptiness rec.lastproof 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 100 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 100 16-NOV-07 24-AUG-14 5800 50
127 Landry ST_CLERK 120 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 100 16-NOV-07 24-AUG-14 5800 50
127 Landry ST_CLERK 120 14-JAN-07 24-AUG-14 3200 50
SQL > edit
DECLARE
Myrec employees%ROWTYPE
BEGIN
Myrec: = NULL
END
/
SQL > @ notes/s43.sql
PL/SQL procedure successfully completed.
