oracleÖÐtype
oracleÖÐ×Ô¶¨ÒåÊý¾ÝÀàÐÍ
oracleÖÐÓлù±¾µÄÊý¾ÝÀàÐÍ£¬Èçnumber£¬varchar2£¬date£¬numeric£¬float....µ«ÓÐʱºòÎÒÃÇÐèÒªÌØÊâµÄ¸ñʽ£¬È罫name¶¨ÒåΪ
£¨firstname,lastname£©µÄÐÎʽ£¬ÎÒÃÇÏë°ÑÕâ¸ö×÷Ϊһ¸ö±íµÄÒ»Áп´´ý£¬Õâʱºò¾Í²»ÒªÎÒÃÇ×Ô¼º¶¨ÒåÒ»¸öÊý¾ÝÀàÐÍ
create or replace type type_name as object(firstname varchar2(20),lastname varchar2(30));
ÕâÑù×Ó¶¨ÒåÖ®ºóÎÒÃǾÍÄÜÏñʹÓûù±¾ÀàÐÍÒ»ÑùʹÓÃ×Ô¶¨ÒåÀàÐÍÈ¥½¨±í:
È磺
create or replace type name_format as object(firstname varchar2(20),lastname varchar2(30));
¶¨ÒåÁËÀàÐÍÖ®ºó£¬ÎÒÃǾͿÉÒÔ½«Ëüµ±×÷ÆÕͨµÄ»ù±¾ÀàÐÍÓÃÁË£¬½¨±í£¬Ð´functionµÈµÈ£¬ÈçÓÃ×Ô¶¨ÒåÊý¾ÝÀàÐͽ¨±í£º
create table testtable(id number primary key,name name_format);
²åÈëÊý¾Ýʱ£¬¹¹ÔìÎÒÃǵÄÕâ¸ö×Ô¶¨ÒåÁÐʱ£¬¾ÍÏñ¹¹Ôì¶ÔÏóÒ»Ñù£¬ÈçÉÏÃæµÄ±í²åÈëÊý¾ÝµÄsqlÓï¾äÈçÏ£º
insert into testtable values(1,name_format('hust','lkq'));
²éѯ×Ô¶¨ÒåÁеÄij¸öÖµ¿ÉÒÔÓÃ.À´·ÃÎÊ,ÈçÉÏÃæÎÒÒª²éѯtesttable±íÖÐnameÁеÄfirstnameµÄÖµ¿ÉÒÔ°´ÕÕÈçϲéѯ£º
select t.name.firstname from testtable t;
×Ô¶¨ÒåÊý¾ÝÀàÐÍÒ²¿ÉÒÔÊǼ¯ºÏ
ÀýÈ磺create or replace type numers_set as array(10) of number;
Õâ¾Í±íʾ´´½¨ÁËÒ»¸ö°üº¬10¸önumberÊý¾ÝµÄ×Ô¶¨ÒåÀàÐÍ£¬
×¢£ºÕâÀïµÄ¼¯ºÏÀïÃæµÄ»ù±¾ÔªËØÒ²¿ÉÒÔÊÇÄã×Ô¶¨ÒåµÄÀàÐÍ£¬Ò²¾ÍÊÇ˵֧³ÖǶÌ×¶¨Òå
create or replace type name_list as array(10) of name_format:£¨´Ë´¦µÄname_format¾ÍÊÇÎÒÃÇÔÚÉÏÃæ¶¨ÒåµÄÒ»¸ö×Ô¶¨ÒåÀàÐÍ£©
Ïà¹ØÎĵµ£º
ÔÚOracleÖÐʹÓÃGuid
ÔÚOracleÖпÉÒÔÓÃSYS_GUID()À´Éú³ÉÒ»¸öguid,Ï൱ÓÚmsSqlÖеÄnewid()¡£
ÔÚOracle9iºÍOracle 10g ÀïSYS_GUID²úÉúµÃµ½µÄÊý¾ÝÊÇ32 λµÄ£¬È磺234E45F0077881AAE0430AA3034681AA
µ«OracleÖеÄguidÓëmsSqlÖеÄguid¸ñʽ²»ÍêÈ«Ïàͬ¡£
½«OracleÖеÄguid·Ö¸î³ÉΪ Windowsϵͳ ObjectId µÄ¸ñʽ£º{8-4-4-4-12} £ ......
ÔÚOracleÖеÄÊ÷ÐβÙ×÷
1.È¡×ӽڵ㼰·¾¶(ÕýÊ÷):
select t.id ,t.code, t.name ,t.pid
,SYS_CONNECT_BY_PATH(t.id,'.')||'.' as IdPath
from tas_catalog t
--where id!=110
start with id=110
connect by pid = prior id
order siblings by id
2.È¡¸÷¼¶¸¸½Úµã(µ¹Ê÷)£º
select t.id ,t.code, t.na ......
²é¿´ÕýÔÚÖ´ÐеÄsqlÓï¾ä
SELECT osuser, username, sql_text from v$session a, v$sqltext b where a.sql_address =b.address order by address, piece;
²¶×½ÔËÐкܾõÄSQL
select username,sid,opname, round(sofar*100 / totalwork,0) || '%' as progress, time_remaining,sql_text from v$session_longops , v$ ......
OracleÖ´Ðмƻ®µÄÏà¹Ø¸ÅÄ
rowid£ºÏµÍ³¸øoracleÊý¾ÝµÄÿÐи½¼ÓµÄÒ»¸öαÁУ¬°üº¬Êý¾Ý±íÃû³Æ£¬Êý¾Ý¿âid£¬´æ´¢Êý¾Ý¿âidÒÔ¼°Ò»¸öÁ÷Ë®ºÅµÈÐÅÏ¢£¬rowidÔÚÐеÄÉúÃüÖÜÆÚÄÚΨһ¡£
recursive sql£ºÎªÁËÖ´ÐÐÓû§Óï¾ä£¬ÏµÍ³¸½¼ÓÖ´ÐеĶîÍâ²Ù×÷Óï¾ä£¬Æ©Èç¶ÔÊý¾Ý×ÖµäµÄά»¤µÈ¡£
row source£¨ÐÐÔ´£©£ºoracleÖ´Ðв½Öè¹ý³ÌÖУ¬ÓÉÉÏÒ» ......
Êý¾ÝÀàÐÍʹÓÃ
1.ÉùÃ÷£º
a.¸ñʽ£ºVariable_name [constant] databyte [not null] [:=default expression]
b.±äÁ¿Óë³£Á¿ÉùÃ÷»ù±¾Ò»Ö£¬Ê¹ÓÃconstantÉùÃ÷µÄΪ³£Á¿£¬²»Ê¹ÓÃΪ±äÁ¿£¬²¢ÇÒ¸³ÖµÖ»ÄÜÔÚDECLAREÇøÓò¸³Öµ£»
c.ʹÓÃ%TYPEºÍ%ROWTYPEÉùÃ÷¿ÉÒÔʹ±äÁ¿µÄÀàÐÍÓë±íÖÐ×Ö¶ÎÀàÐÍ»òÕû¸ö¼Ç¼ÀàÐͱ ......