Oracle ÐÐתÁÐÎÊÌâ
Êý¾Ý¿â²éѯÖÐÄÑÃâ»áÓöµ½ÐÐÁÐת»»µÄÇé¿ö£¬ÕªÁÐһЩ½Ï¾«µäµÄ½â¾ö·½°¸
--####################################################################
Ò»¡¢²ÉÓÃSQL decodeºÍPL/SQLº¯ÊýʵÏÖ
--####################################################################
1¡¢¹Ì¶¨ÁÐÊýµÄÐÐÁÐת»»
Èç
student subject grade
---------------------------
student1 ÓïÎÄ 80
student1 Êýѧ 70
student1 Ó¢Óï 60
student2 ÓïÎÄ 90
student2 Êýѧ 80
student2 Ó¢Óï 100
……
ת»»Îª
ÓïÎÄ Êýѧ Ó¢Óï
student1 80 70 60
student2 90 80 100
……
Óï¾äÈçÏ£º
select student,sum(decode(subject,'ÓïÎÄ', grade,null)) "ÓïÎÄ",
sum(decode(subject,'Êýѧ', grade,null)) "Êýѧ",
sum(decode(subject,'Ó¢Óï', grade,null)) "Ó¢Óï"
from table
group by student
2¡¢²»¶¨ÁÐÐÐÁÐת»»
Èç
c1 c2
--------------
1 ÎÒ
1 ÊÇ
1 Ë
2 Öª
2 µÀ
3 ²»
……
ת»»Îª
1 ÎÒÊÇË
2 ÖªµÀ
3 ²»
ÕâÒ»ÀàÐ͵Äת»»±ØÐë½èÖúÓÚPL/SQLÀ´Íê³É£¬ÕâÀï¸øÒ»¸öÀý×Ó
CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)
RETURN VARCHAR2
IS
Col_c2 VARCHAR2(4000);
BEGIN
FOR cur IN (SELECT c2 from t WHERE c1=tmp_c1) LOOP
Col_c2 := Col_c2||cur.c2;
END LOOP;
Col_c2 := rtrim(Col_c2,1);
RETURN Col_c2;
END;
/
SQL> select distinct c1 ,get_c2(c1) cc2 from table;¼´¿É
ժ¼×Ô£ºhttp://www.360doc.com/showWeb/0/0/18772.aspx
--####################################################################
¶þ¡¢Ê¹Ó÷ÖÎöº¯Êý½øÐÐÐÐÁÐת»»
--####################################################################
ÆäʵʹÓ÷ÖÎöº¯Êý½øÐд¦ÀíÊǺܺõķ½Ê½£¬·Ò»ÏÂTomµÄÊ飬½«ÆäÖеÄÒ»¸öÀý×ÓÊÕ¼ÔÚÕâÀï. ±ÈÈç²éѯscott.emp±íµÄÓû§SALÅÅÐòÐÅÏ¢£¬¿ÉÒÔʹÓÃÈçϲéѯ£º
SQL> SELECT deptno, ename,
2 ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY sal DESC) seq
3 from emp;
DEPTNO ENAME SEQ
---------- ---------- ----------
&
Ïà¹ØÎĵµ£º
oracle±í¿Õ¼ä²Ù×÷Ïê½â
1
2
3×÷Õߣº À´Ô´£º ¸üÐÂÈÕÆÚ£º2006-01-04
5
6
7½¨Á¢±í¿Õ¼ä
8
9CREATE TABLESPACE data01
10DATAFILE '/ora ......
1¡¢´¥·¢Æ÷µÄ¸ÅÄî
´¥·¢Æ÷Ò²ÊÇÒ»ÖÖ´øÃûµÄPL/SQL¿é¡£´¥·¢Æ÷ÀàËÆÓÚ¹ý³ÌºÍº¯Êý£¬ÒòΪËüÃǶ¼ÊÇÓµÓÐÉùÃ÷¡¢Ö´ÐкÍÒì³£´¦Àí¹ý³ÌµÄ´øÃûPL/SQL¿é¡£Óë°üÀàËÆ£¬´¥·¢Æ÷±ØÐë´æ´¢ÔÚÊý¾Ý¿âÖв¢ÇÒ²»Äܱ»¿é½øÐб¾µØ»¯ÉùÃ÷¡£
¶ÔÓÚ´¥·¢Æ÷¶øÑÔ£¬µ±´¥·¢Ê¼þ·¢ÉúµÄʱºò¾Í»áÏÔʽµØÖ´Ðиô¥·¢Æ÷£¬²¢ÇÒ´¥·¢Æ÷²»½ÓÊܲÎÊý¡£
´´½¨´¥·¢Æ÷µÄÓï·¨È ......
ROLLUPºÍCUBEÓï¾ä¡£
OracleµÄGROUP
BYÓï¾ä³ýÁË×î»ù±¾µÄÓï·¨Í⣬»¹Ö§³ÖROLLUPºÍCUBEÓï¾ä¡£Èç¹ûÊÇROLLUP(A, B, C)µÄ»°£¬Ê×ÏÈ»á¶Ô(A¡¢B¡¢C)½øÐÐGROUP
BY£¬È»ºó¶Ô(A¡¢B)½øÐÐGROUP BY£¬È»ºóÊÇ(A)½øÐÐGROUP BY£¬×îºó¶ÔÈ«±í½øÐÐGROUP BY²Ù×÷¡£Èç¹ûÊÇGROUP BY
CUBE(A, B, C)£¬ÔòÊ×ÏÈ»á¶Ô(A¡¢B¡¢C)½øÐÐGROUP
BY£¬È»ºóÒÀ´ÎÊÇ( ......
This article shows how Oracle's Heterogeneous Services can be configured to allow a database to connect to a Microsoft Access database using standard databases links. The method described can be used to connect to MS-Access from about any platform - Unix/ Linux or Windows.
MS-Access 2003 and Orac ......
ORACLEÏà¹ØÓï·¨ ÊÕ²Ø
Ò»¡¢OracleÈëÃÅ
ÀíÂÛ֪ʶ£º
OracleµÄÎïÀí×é¼þÓÐÈý¸ö£º
(1)Êý¾ÝÎļþ Êý¾ÝÎļþÊÇÓÃÓÚ´æ´¢Êý¾Ý¿âÊý¾ÝµÄÎļþ£¬Èç±í¡¢Ë÷ÒýÊý¾Ý¡£Ã¿¸öOracleÊý¾Ý¿âÓÐÒ»¸ö»ò¶à¸öÎïÀíÊý¾ÝÎļþ£¬
&nbs ......