Oracle TYPE OBJECT详解(三)
-- We will store up to 50 projects at a time in a database column.
CREATE TYPE ProjectList AS VARRAY(50) OF VARCHAR2(16);
--创建表
CREATE TABLE dept_projects ( -- create database table
dept_id NUMBER(2),
name VARCHAR2(15),
budget NUMBER(11,2),
-- Each department can have up to 50 projects.
projects ProjectList);
--插入数据：
INSERT INTO dept_projects
VALUES(60, 'Security', 750400,
ProjectList('New Badges', 'Track Computers', 'Check Exits'));
8. 在动态SQL中使用对象：
8.1 定义对象类型person_typ和数组类型hobbies_var,并创建报TEAMS:
[sql]
CREATE TYPE person_typ AS OBJECT (name VARCHAR2(25), age NUMBER);
CREATE TYPE hobbies_var AS VARRAY(10) OF VARCHAR2(25);
CREATE OR REPLACE PACKAGE teams
AUTHID CURRENT_USER AS
PROCEDURE create_table (tab_name VARCHAR2);
PROCEDURE insert_row (tab_name VARCHAR2, p person_typ, h hobbies_var);
PROCEDURE print_table (tab_name VARCHAR2);
END;
CREATE OR REPLACE PACKAGE BODY teams AS
PROCEDURE create_table (tab_name VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE ' || tab_name ||
' (pers person_typ, hobbs hobbies_var)';
END;
PROCEDURE insert_row (
tab_name VARCHAR2,
p person_typ,
h hobbies_var) IS
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO ' || tab_name ||
' VALUES (:1, :2)' USING p, h;
END;
PROCEDURE print_table (tab_name VARCHAR2) IS
TYPE refcurtyp IS REF CURSOR;
v_cur refcurtyp;
p person_typ;
h hobbies_var;
BEGIN
OPEN v_cur FOR 'SELECT pers, hobbs FROM ' || tab_name;
LOOP
FETCH v_cur INTO p, h;
EXIT WHEN v_cur%NOTFOUND;
-- print attributes of 'p' and elements of 'h'
DBMS_OUTPUT.PUT_LINE('Name: ' || p.name || ' - Age: ' || p.age);
FOR i IN h.FIRST..h.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('Hobby(' || i || '): ' || h(i));
END LOOP;
END LOOP;
CLOSE v_cur;
END;
END;
8.2 调用TEAMS包中的存储过程：
[sql]
DECLARE
team_name VARCHAR2(15);
BEGIN
team_name := 'Notables';
TEAMS.create_table(team_name);
TEAMS.insert_row(team_name, person_typ('John', 31),
hobbies_var('skiing', 'coin collecting', 'tennis'));
TEAMS.insert_row(team_name, person_typ('Mary', 28),
hobbies_var('golf', 'quilting', 'rock climbing', 'fencing'));
TEAMS.print_table(team_name);
END;
=================================================
output:
Name: John - Age: 31
Hobby(1): skiing
Hobby(2): coin collecting
Hobby(3): tennis
Name: Mary - Age: 28
Hobby(1): golf
Hobby(2): quilting
Hobby(3): rock climbing
Hobby(4): fencing
PL/SQL 过程已成功完成。