oracleÖжÔÏó±í£¬ref¼°derefµÄÒ»¸öʵÀý¡£
ORACLEÔÚ¹ØϵÊý¾Ý¿âÍ⣬ÈÚÈëÁËÃæÏò¶ÔÏóµÄÔªËØ£¬±ÈÈç¿ÉÒÔ´´½¨type£¬typeÖ®¼ä¿ÉÒԼ̳У¬type¿ÉÒÔ´ø¹¹Ô캯Êý¡¢ÅÅÐòº¯Êý¡¢¸÷ÖÖ¸÷ÑùµÄ³ÉÔ±º¯Êý¡¢´æ´¢¹ý³ÌµÈµÈ¡£
¶ÔÏó±íÊÇÖ¸¸Ã±íµÄÒ»ÐоÍÊÇÒ»¸ö¶ÔÏó£¬ÓÐÒ»¸öOID(object ID)£¬¶ÔÏó±íÖ®¼äûÓÐÖ÷Íâ¼ü¹ØÁªµÄ¸ÅÄΪÁËÌåÏÖÕâ²ã¹Øϵ£¬oracleÖÐÓÃÁËref¶ÔÏóÀ´ÊµÏÖ¡£
ÏÂÃæÀý×Ó£¬´´½¨Ò»¸öµØÖ·ÀàÐÍ£¬Ò»¸öÈËÔ±ÀàÐÍ£¬ÈËÔ±ÓеØÖ·ÊôÐÔ£¬ËùÒÔÔÚÈËÔ±ÀàÐÍÖÐÉèÖÃÒ»¸öref addressÀ´È·¶¨Ö¸ÏòËûËùÔÚµØÖ·µÄÖ¸Õë¡£
--´´½¨µØÖ·ÀàÐÍ
create type address as object(
street varchar2(35),
city varchar2(15),
state char(2),
zip_code integer
);
create table addresses of address; --´´½¨µØÖ·¶ÔÏó±í
--´´½¨ÈËÔ±ÀàÐÍ
create type person as object(
first_name varchar2(15),
last_name varchar2(15),
birthday date,
home_address ref address, --Ö¸Ïò¶ÔÓ¦µÄµØÖ·£¬¸ÃµØÖ·Ó¦¸ÃÔÚÁíÍâÒ»¸ö¶ÔÏó±íÖеÄÒ»ÐÐ
phone_number varchar2(15)
);
CREATE TABLE persons of person; --´´½¨ÈËÔ±¶ÔÏó±í
--²åÈëÒ»¸öµØÖ·
insert into addresses values(address('nanhai','shenzhen','gd','518054'));
insert into addresses values(address('shennan','shenzhen','gd','518057'));
--²åÈëÒ»¸öÈËÔ±£¬×¢ÒâÕâÀïµÄhome_address²¿·ÖÊÇÈçºÎ²åÈëÒ»¸öref addressµÄ¡£
insert into persons values(person('shitou','haha',to_date('1982-07-05','yyyy-mm-dd'),
(select ref(a) from addresses a where street='nanhai'),
'1355555555'));
--Ò²¿ÉÒÔÓÃÏÂÃæµÄ¹ý³ÌÀ´²åÈëÒ»¸öÈËÔ±¼Ç¼
declare
addref ref address ;
begin
select ref(a) into addref from addresses a where street='nanhai';
insert into persons
values (person('shitou','haha',to_date('1982-07-05','yyyy-mm-dd'),
addref,'1355555555'));
commit;
end;
--²éѯijÈ˵ĵØÖ·ÐÅÏ¢
select first_name,deref(home_address) from persons;
--Ð޸ĵØÖ·
update persons set home_address=(select ref(a) from addresses a where street='shennan');
--ɾ³ýij¸
Ïà¹ØÎĵµ£º
1.´´½¨²âÊÔ±í
create table users(
userid int primary key,
username varchar2(20),
userpwd varchar2(20)
);
insert into users values(1,'test','test');
insert into users values(2,'test','test');
insert into users values(3,'test','test');
insert into users values(4,'test','test');
insert i ......
connect by Êǽṹ»¯²éѯÖÐÓõ½µÄ£¬Æä»ù±¾Óï·¨ÊÇ£º
select ... from tablename start with Ìõ¼þ1
connect by Ìõ¼þ2
where Ìõ¼þ3;
Àý£º
select * from table
start with org_id = 'HBHqfWGWPy'
connect by prior org_id = parent_id;
¼òµ¥ËµÀ´Êǽ«Ò»¸öÊ÷×´½á¹¹´æ´¢ÔÚÒ»ÕűíÀ±ÈÈçÒ»¸ö±íÖдæÔÚÁ½¸ö×Ö¶Î:
......
ÓÃoracleÊý¾Ý¿âµÄ´æ´¢¹ý³ÌʵÏÖ·µ»Ø½á¹û¼¯²¢ÊµÏÖ·ÖÒ³µÄ¹¦ÄÜ¡£
Óû§´«Èë²ÎÊý
Ò»ÏÂÊÇת±ðÈ˵ĴúÂë
--°üÉùÃ÷
create or replace package p_page is
-- Author : PHARAOHS
-- Created : 2006-4-30 14:14:14
-- Purpose : ·ÖÒ³¹ý³Ì
TYPE type_cur IS REF CURSOR; &n ......
OracleÖÐÓÃRowid²éÕÒºÍɾ³ýÖظ´¼Ç¼
¡¡¡¡Æ½Ê±¹¤×÷ÖпÉÄÜ»áÅöµ½µ±ÊÔͼ¶Ô¿â±íÖеÄijһÁлò¼¸Áд´½¨Î¨Ò»Ë÷Òýʱ£¬ÏµÍ³Ìáʾ ORA-01452 £º²»ÄÜ´´½¨Î¨Ò»Ë÷Òý£¬·¢ÏÖÖظ´¼Ç¼¡£
¡¡¡¡ÏÂÃæ×ܽáһϼ¸ÖÖ²éÕÒºÍɾ³ýÖظ´¼Ç¼µÄ·½·¨£¨ÒÔ±íCZΪÀý£©£º
¡¡¡¡±íCZµÄ½á¹¹ÈçÏ£º
¡¡¡¡SQL> desc cz
¡¡¡¡Name Null? Type
¡¡¡¡---------- ......