5.54.txt

delphi renyuanguanlixinxioxitong

/*创建对象类型规范*/
CREATE OR REPLACE TYPE person_type AS OBJECT(
name VARCHAR2(10),gender VARCHAR2(2),
birthdate DATE,address VARCHAR2(100),
MEMBER PROCEDURE change_address(new_addr VARCHAR2),
MEMBER FUNCTION get_info RETURN VARCHAR2);
/
/*创建对象类型体*/
CREATE OR REPLACE TYPE BODY person_type IS
MEMBER PROCEDURE change_address(new_addr VARCHAR2) IS
 BEGIN
  address:=new_addr;
 END;
MEMBER FUNCTION get_info RETURN VARCHAR2 IS
 v_info VARCHAR2(100);
 BEGIN
  v_info:='姓名：'||name||',出生日期：'||birthdate;
  RETURN v_info;
 END;
END;
/
/*创建创建对象表*/
CREATE TABLE person_table OF person_type--创建对象表
/
/*为行对象插入数据*/
INSERT INTO person_table VALUES
('马如龙','男','03-1月-50','江西财经大学')
/
INSERT INTO person_table VALUES
(person_type('杨意','女','03-12月-70','江西财经大学'))
/
/*检索行对象数据*/
DECLARE
 person person_type;
BEGIN
 SELECT VALUE(Pt) INTO person FROM person_table pt
 WHERE pt.name='&name';
dbms_output.put_line('性别:'||person.gender);
dbms_output.put_line('出生日期:'||person.birthdate);
END;
/
/*更新行对象数据*/
UPDATE person_table pt SET pt.birthdate='11-12月-51' WHERE pt.name='马如龙'
/
/*删除行对象数据*/
DELETE FROM person_table pt WHERE pt.name='马如龙'
/
/*创建列对象表emp_tab*/
CREATE TABLE emp_tab(eno NUMBER(6),person person_type,
   sal NUMBER(6,2),job VARCHAR2(10))
/
/*检索列对象数据*/
DECLARE
 emp person_type;
 sala NUMBER(6,2); 
BEGIN
 SELECT person,sal INTO emp,sala FROM emp_tab
 WHERE eno=&n;
dbms_output.put_line('员工名称:'||emp.name);
dbms_output.put_line('工资:'||sala);
END;
/
UPDATE emp_tab pt SET pt.person.birthdate='11-12月-81' WHERE pt.person.name='杨意'
/
DELETE FROM emp_tab pt WHERE pt.person.name='杨意'
/
/*使用对象方法*/
DECLARE
 v_p person_type;
BEGIN
 SELECT person INTo v_p from emp_tab WHERE eno=&&no;
 v_p.change_address('南昌大学信息工程学院');
 UPDATE emp_tab SET person=v_p WHERE eno=&no;
 dbms_output.put_line(v_p.get_info);
END;
/