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

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.

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.