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Ù×÷ÃüÁîÕûÀí
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 ......
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¹ÜÀíÓëά»¤.Êý¾Ý¿âµÇ¼µÄÉíÖ¤ÑéÖ¤
²Ýľ¹Ï
20080505
Ò»¡¢Ð´ÔÚÇ°Ãæ
Ôø¾²¢²»ÖªµÀconn / as sysdbaΪºÎÎÔÚunixϸãÁ˰ëÌì·½Öª£¬»¹ÐèÒª¼ÓÉÏ"ºÅ£¬Oracle 10gÀï
ÖÕÓÚÒ²¿¼Âǵ½ÁËÕâ¸öÎÊÌ⣬²»ÔÙÊܵ½" as sysdba "µÄÏÞÖÆ¡£
ÈçÏÂËùʾ£º
[oracle@liwei admin]$ sqlplus / as sysdba
......
£±¡¢È·¶¨ÄãµÄ¼ÆËã»úÉÏ×°ÓÐoracleµÄ¿Í»§¶Ë£¬Èç¹ûûÓУ¬Çë°²×°oracle¿Í»§¶Ë
£²¡¢È·¶¨ÓÐÄãµÄoracle·þÎñÀïÓÐÄãÒªÐèÒª±¸·ÝµÄÊý¾Ý¿âµÄ·þÎñÃû¡£
£³¡¢ÖÆ×÷Ò»¸öÅú´¦ÀíÎļþ£¨oracleBackup.bat£©£¬¾ßÌå·½·¨£º´ò¿ª“¼Çʱ¾”£¬±£´æÎªoracleBackup.bat£¬
¡¡¡¡´Ëʱ±£´æµÄÎļþÀàÐÍÑ¡ÔñΪ“ËùÓÐÎļþ”¡£Îҵı£´æÄ¿Â ......
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}
ĬÈÏÖ»Óн ......