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

ORACLEµÝ¹éͳ¼ÆËùÓÐ×ÓËï»ú¹¹£¡

select bid from t_branch c start with c.upbid  ='11000000'  connect by prior c.bid=c.upbid
˵Ã÷£º
1¡¢Í³¼ÆÄ³¸ö»ú¹¹ÏÂËùÓеÄ×ÓËï»ú¹¹¡£Èçͳ¼Æ±±¾©»ú¹¹ÏµÄ×ÓËï»ú¹¹£¬°üÀ¨£º³¯ÑôÇø»ú¹¹¡¢º£µíÓªÏú»ú¹¹¡¢¡¢¡¢ÇàÁúÇÅÏç»ú¹¹¡¢¡¢¡¢¡¢Ò»Ö±µ½×îµ×²ãµÄ»ú¹¹¡£
2¡¢upbidÊÇÖ¸Éϼ¶»ú¹¹µÄID¡£º£µíÇø»ú¹¹ID(bid)µÄÉϼ¶»ú¹¹ID(upbid)Ϊ11000000£¬Ò²¾Í˵ֱ½ÓËùÊôµÄ»ú¹¹ÎªÖ±½Ó»ú¹¹¡£ÇàÁúÇÅ»ú¹¹µÄÉϼ¶»ú¹¹Îªº£µíÇø»ú¹¹¡£
3¡¢connect by prior ΪoracleµÝ¹é·½·¨¡£
Ïà¹ØÖªÊ¶£º
OracleÖÐstart with...connect by prior×Ó¾äÓ÷¨ 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;
     ¼òµ¥ËµÀ´Êǽ«Ò»¸öÊ÷×´½á¹¹´æ´¢ÔÚÒ»ÕűíÀ±ÈÈçÒ»¸ö±íÖдæÔÚÁ½¸ö×Ö¶Î:
org_id,parent_idÄÇôͨ¹ý±íʾÿһÌõ¼Ç¼µÄparentÊÇË­£¬¾Í¿ÉÒÔÐγÉÒ»¸öÊ÷×´½á¹¹¡£
     ÓÃÉÏÊöÓï·¨µÄ²éѯ¿ÉÒÔÈ¡µÃÕâ¿ÃÊ÷µÄËùÓмǼ¡£
     ÆäÖУº
     Ìõ¼þ1 ÊǸù½áµãµÄÏÞ¶¨Óï¾ä£¬µ±È»¿ÉÒÔ·Å¿íÏÞ¶¨Ìõ¼þ£¬ÒÔÈ¡µÃ¶à¸ö¸ù½áµã£¬Êµ¼Ê¾ÍÊǶà¿ÃÊ÷¡£
     Ìõ¼þ2 ÊÇÁ¬½ÓÌõ¼þ£¬ÆäÖÐÓÃPRIOR±íʾÉÏÒ»Ìõ¼Ç¼£¬±ÈÈç CONNECT BY PRIOR org_id = parent_id¾ÍÊÇ˵ÉÏÒ»Ìõ¼Ç¼µÄorg_id ÊDZ¾Ìõ¼Ç¼µÄparent_id£¬¼´±¾¼Ç¼µÄ¸¸Ç×ÊÇÉÏÒ»Ìõ¼Ç¼¡£
     Ìõ¼þ3 ÊǹýÂËÌõ¼þ£¬ÓÃÓÚ¶Ô·µ»ØµÄËùÓмǼ½øÐйýÂË¡£
     ¼òµ¥½éÉÜÈçÏ£º
     ÔçɨÃèÊ÷½á¹¹±íʱ£¬ÐèÒªÒÀ´Ë·ÃÎÊÊ÷½á¹¹µÄÿ¸ö½Úµã£¬Ò»¸ö½ÚµãÖ»ÄÜ·ÃÎÊÒ»´Î£¬Æä·ÃÎʵIJ½ÖèÈçÏ£º
     µÚÒ»²½£º´Ó¸ù½Úµã¿ªÊ¼£»
     µÚ¶þ²½£º·ÃÎʸýڵ㣻
     µÚÈý²½£ºÅжϸýڵãÓÐÎÞδ±»·ÃÎʵÄ×ӽڵ㣬ÈôÓУ¬ÔòתÏòËü×î×ó²àµÄδ±»·ÃÎʵÄ×Ó½Ú£¬²¢Ö´Ðеڶþ²½£¬·ñÔòÖ´ÐеÚËIJ½£»
     µÚËIJ½£ºÈô¸Ã½ÚµãΪ¸ù½Úµã£¬Ôò·ÃÎÊÍê±Ï£¬·ñÔòÖ´ÐеÚÎå²½£»
     µÚÎå²½£º·µ»Øµ½¸Ã½ÚµãµÄ¸¸½Úµã£¬²¢Ö´ÐеÚÈý²½Öè¡£
     ×ÜÖ®£ºÉ¨ÃèÕû¸öÊ÷½á¹¹µÄ¹ý³ÌÒ²¼´ÊÇÖÐÐò±éÀúÊ÷µÄ¹ý³Ì¡£
   


Ïà¹ØÎĵµ£º

µãµãµÎµÎ(Oracle)

insert into
    select * into t_dest from t_src; -- ÒªÇóÄ¿±ê±í²»´æÔÚ
    insert into t_dest(a, b) select a, b from t_src; -- ÒªÇóÄ¿±ê±íÒÑ´æÔÚ
¶¯Ì¬SQL
    execute immediate ......

110¸öOracle ³£Óú¯ÊýµÄ×ܽá

SQLÖеĵ¥¼Ç¼º¯Êý
1.ASCII
·µ»ØÓëÖ¸¶¨µÄ×Ö·û¶ÔÓ¦µÄÊ®½øÖÆÊý;
SQL> select ascii(A) A,ascii(a) a,ascii(0) zero,ascii( ) space from dual;
A A ZERO SPACE
--------- --------- --------- ---------
65 97 48 32
2.CHR
¸ø³öÕûÊý,·µ»Ø¶ÔÓ¦µÄ×Ö·û;
SQL> select chr(54740) zhao,chr(65) chr65 from dual;
......

oracle¼¶ÁªÉ¾³ý

oracleÖÐʹÓÃon delete cascadeºÍon delete set nullÀ´½¨Á¢Íâ¼ü
ÆäÃæÎÒÃǽéÉÜÁË´´½¨Íâ¼üÔ¼ÊøÊ±Èç¹ûʹÓÃoracleĬÈϵĴ´½¨·½Ê½£¬ÔÚɾ³ý±»²ÎÕÕµÄÊý¾Ýʱ£¬½«ÎÞ·¨±»É¾³ý£¬ÕâÒ»µãÔÚoracle9iÖиøÁËÎÒÃǸü¶àÁé»îµÄÑ¡Ôñ£¬ÎÒÃÇ¿ÉÊÇʹÓÃon delete cascadeºÍ on delete set null¹Ø¼ü×ÖÀ´¾ö¶¨É¾³ý±»²ÎÕÕÊý¾ÝʱÊÇ·ñÒª½«²ÎÕÕÕâ¸öÊý¾ÝµÄÄÇÐ ......

OracleÖÐDecode()º¯ÊýʹÓü¼ÇÉ

OracleÖÐDecode()º¯ÊýʹÓü¼ÇÉ
¡¡¡¡decode()º¯ÊýÊÇORACLE PL/SQLÊǹ¦ÄÜÇ¿´óµÄº¯ÊýÖ®Ò»£¬Ä¿Ç°»¹Ö»ÓÐORACLE¹«Ë¾µÄSQLÌṩÁ˴˺¯Êý£¬ÆäËûÊý¾Ý¿â³§É̵ÄSQLʵÏÖ»¹Ã»Óд˹¦ÄÜ¡£
      DECODEº¯ÊýÊÇORACLE PL/SQLÊǹ¦ÄÜÇ¿´óµÄº¯ÊýÖ®Ò»£¬Ä¿Ç°»¹Ö»ÓÐORACLE¹«Ë¾µÄSQLÌṩÁ˴˺¯Êý£¬ÆäËûÊý¾Ý¿â³§É̵ÄSQLÊµÏ ......

Oracle 10gѧϰµãµÎ

°²×°ÍêOracle 10g£¬sql*plusµÇ½
  "Óû§Ãû³Æ£¨U£©:"ÖÐÊäÈë'system'
  "¿ÚÁP£©:"ÖÐÊäÈë'manager'
  "Ö÷»ú×Ö·û´®£¨H£©:" tnsname.oraÖÐÅäÖõķþÎñÃû(Èç¹ûÊÇϵͳĬÈÏÊý¾Ý¿â¿ÉÒÔ²»ÊäÈë)
  (×¢1£ºÕâ¸öÓû§Ãû/ÃÜÂëÊÇÔÚ°²×°¹ý³ÌÖÐ×Ô¼ºÉ趨µÄ)
  (×¢2:  Èç¹ûÉÏÊö²Ù×÷Å׳öûÓмàÌýÆ÷£¬ÔòÐè¿ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ