Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

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ÖÐʹÓÃ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ÖеÄÊ÷ÐβÙ×÷

ÔÚ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 ......

oracle ֮ϵͳ±í µãµÎ »ýÀÛ

²é¿´ÕýÔÚÖ´ÐеÄ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Ö´Ðмƻ®µÄÏà¹Ø¸ÅÄî

OracleÖ´Ðмƻ®µÄÏà¹Ø¸ÅÄ
rowid£ºÏµÍ³¸øoracleÊý¾ÝµÄÿÐи½¼ÓµÄÒ»¸öαÁУ¬°üº¬Êý¾Ý±íÃû³Æ£¬Êý¾Ý¿âid£¬´æ´¢Êý¾Ý¿âidÒÔ¼°Ò»¸öÁ÷Ë®ºÅµÈÐÅÏ¢£¬rowidÔÚÐеÄÉúÃüÖÜÆÚÄÚΨһ¡£
recursive sql£ºÎªÁËÖ´ÐÐÓû§Óï¾ä£¬ÏµÍ³¸½¼ÓÖ´ÐеĶîÍâ²Ù×÷Óï¾ä£¬Æ©Èç¶ÔÊý¾Ý×ÖµäµÄά»¤µÈ¡£
row source£¨ÐÐÔ´£©£ºoracleÖ´Ðв½Öè¹ý³ÌÖУ¬ÓÉÉÏÒ» ......

±±´óÇàÄñoracleѧϰ±Ê¼Ç17

Êý¾ÝÀàÐÍʹÓÃ
1.ÉùÃ÷£º
a.¸ñʽ£ºVariable_name [constant] databyte [not null] [:=default expression]
b.±äÁ¿Óë³£Á¿ÉùÃ÷»ù±¾Ò»Ö£¬Ê¹ÓÃconstantÉùÃ÷µÄΪ³£Á¿£¬²»Ê¹ÓÃΪ±äÁ¿£¬²¢ÇÒ¸³ÖµÖ»ÄÜÔÚDECLAREÇøÓò¸³Öµ£»
c.ʹÓÃ%TYPEºÍ%ROWTYPEÉùÃ÷¿ÉÒÔʹ±äÁ¿µÄÀàÐÍÓë±íÖÐ×Ö¶ÎÀàÐÍ»òÕû¸ö¼Ç¼ÀàÐͱ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ