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

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
---------- ---------- ----------
        10 KING &nb


Ïà¹ØÎĵµ£º

oracleÊý¾Ý¿âʱ³£ÓõIJÙ×÷ÃüÁîÕûÀí

µÇ¼oracleÊý¾Ý¿âʱ³£ÓõIJÙ×÷ÃüÁîÕûÀí
1¡¢su – oracle ²»ÊDZØÐ裬ÊʺÏÓÚûÓÐDBAÃÜÂëʱʹÓ㬿ÉÒÔ²»ÓÃÃÜÂëÀ´½øÈësqlplus½çÃæ¡£
2¡¢sqlplus /nolog »òsqlplus system/manager »ò./sqlplus system/manager@ora9i
;
3¡¢SQL>connect / as sysdba ;£¨as sysoper£©»ò
connect internal/oracle AS SYSDBA ;(scott/ti ......

java´úÂëÁ¬½Óµ½oracleÊý¾Ý¿â

package com.deng.oracle;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestOracle {
public static void main(String[] args) {
try {
Class.forName("oracle.jdbc.driver.OracleDriv ......

Oracle¹ÜÀíÓëά»¤.Êý¾Ý¿âµÇ¼µÄÉíÖ¤ÑéÖ¤

Oracle¹ÜÀíÓëά»¤.Êý¾Ý¿âµÇ¼µÄÉíÖ¤ÑéÖ¤
²Ýľ¹Ï
20080505
Ò»¡¢Ð´ÔÚÇ°Ãæ
 Ôø¾­²¢²»ÖªµÀconn / as sysdbaΪºÎÎÔÚunixϸãÁ˰ëÌì·½Öª£¬»¹ÐèÒª¼ÓÉÏ"ºÅ£¬Oracle 10gÀï
ÖÕÓÚÒ²¿¼Âǵ½ÁËÕâ¸öÎÊÌ⣬²»ÔÙÊܵ½" as sysdba "µÄÏÞÖÆ¡£
 ÈçÏÂËùʾ£º
 
 [oracle@liwei admin]$ sqlplus / as sysdba
  ......

Oracle ÔÚwindowÏÂ×Ô¶¯±¸·Ý

£±¡¢È·¶¨ÄãµÄ¼ÆËã»úÉÏ×°ÓÐoracleµÄ¿Í»§¶Ë£¬Èç¹ûûÓУ¬Çë°²×°oracle¿Í»§¶Ë
£²¡¢È·¶¨ÓÐÄãµÄoracle·þÎñÀïÓÐÄãÒªÐèÒª±¸·ÝµÄÊý¾Ý¿âµÄ·þÎñÃû¡£
£³¡¢ÖÆ×÷Ò»¸öÅú´¦ÀíÎļþ£¨oracleBackup.bat£©£¬¾ßÌå·½·¨£º´ò¿ª“¼Çʱ¾”£¬±£´æÎªoracleBackup.bat£¬
¡¡¡¡´Ëʱ±£´æµÄÎļþÀàÐÍÑ¡ÔñΪ“ËùÓÐÎļþ”¡£Îҵı£´æÄ¿Â ......

Oracle sqlplus set

oracel sqlplus
1). ÉèÖõ±Ç°sessionÊÇ·ñ¶ÔÐ޸ĵÄÊý¾Ý½øÐÐ×Ô¶¯Ìá½»
SQL>SET AUTO[COMMIT] {ON|OFF|IMM[EDIATE]| n}
2)£®ÔÚÓÃstartÃüÁîÖ´ÐÐÒ»¸ösql½Å±¾Ê±£¬ÊÇ·ñÏÔʾ½Å±¾ÖÐÕýÔÚÖ´ÐеÄSQLÓï¾ä
SQL> SET ECHO {ON|OFF}
3).ÊÇ·ñÏÔʾµ±Ç°sqlÓï¾ä²éѯ»òÐ޸ĵÄÐÐÊý
SQL> SET FEED[BACK] {6|n|ON|OFF}
ĬÈÏÖ»Óн ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ