Основы (асослар)

Массивларга мисоллар

Фараз қиламиз SQL*Plus Project объект турини қуйидагича аниқладингиз:
SQL> CREATE TYPE Project AS OBJECT (
2 project_no NUMBER(2),
3 title VARCHAR2(35),
4 cost NUMBER(7,2));

Сўнг Project объектларини ўз ичида сақловчи VARRAY туридаги ProjectList аниқладингиз:

SQL> CREATE TYPE ProjectList AS VARRAY(50) OF Project;

Ниҳоят, берилганлар базасидаги ProjectList турдаги устун қатнашган department жадвали қуйидагича ташкил этилади:

SQL> CREATE TABLE department (
2 dept_id NUMBER(2),
3 name VARCHAR2(15),
4 budget NUMBER(11,2),
5 projects ProjectList);

Энди department жадвалини тўлдиришингиз мумкин:

BEGIN
INSERT INTO department
VALUES(30, 'Accounting', 1205700,
ProjectList(Project(1, 'Design New Expense Report', 3250),
Project(2, 'Outsource Payroll', 12350),
Project(3, 'Evaluate Merger Proposal', 2750),
Project(4, 'Audit Accounts Payable', 1425)));
INSERT INTO department
VALUES(50, 'Maintenance', 925300,
ProjectList(Project(1, 'Repair Leak in Roof', 2850),
Project(2, 'Install New Door Locks', 1700),
Project(3, 'Wash Front Windows', 975),
Project(4, 'Repair Faulty Wiring', 1350),
Project(5, 'Winterize Cooling System', 1125)));
INSERT INTO department
VALUES(60, 'Security', 750400,
ProjectList(Project(1, 'Issue New Employee Badges', 13500),
Project(2, 'Find Missing IC Chips', 2750),
Project(3, 'Upgrade Alarm System', 3350),
Project(4, 'Inspect Emergency Exits', 1900)));
END;

Қийматни янгилашга доир мисол:

DECLARE
new_projects ProjectList :=
ProjectList(Project(1, 'Issue New Employee Badges', 13500),
Project(2, 'Develop New Patrol Plan', 1250),
Project(3, 'Inspect Emergency Exits', 1900),
Project(4, 'Upgrade Alarm System', 3350),
Project(5, 'Analyze Local Crime Stats', 825));
BEGIN
UPDATE department
SET projects = new_projects WHERE dept_id = 60;
END;

Қийматларни локал массивга олишга доир мисол:

DECLARE
my_projects ProjectList;
BEGIN
SELECT projects INTO my_projects FROM department
WHERE dept_id = 30;
...
END;

Department: жадвалидан қийматларни ўчиришга доир мисол:

BEGIN
DELETE FROM department WHERE dept_id = 30;
END;