ORACLE MODEL×Ó¾äѧϰ±Ê¼Ç
ORACLE 10GÖÐÐÂÔöµÄMODEL×Ó¾ä¿ÉÒÔÓÃÀ´½øÐÐÐмä¼ÆËã¡£MODEL×Ó¾äÔÊÐíÏñ·ÃÎÊÊý×éÖÐÔªËØÄÇÑù·ÃÎʼǼÖеÄij¸öÁС£Õâ¾ÍÌṩÁËÖîÈçµç×Ó±í¸ñ¼ÆËãÖ®ÀàµÄ¼ÆËãÄÜÁ¦¡£
1¡¢MODEL×Ó¾äʾÀý
ÏÂÃæÕâ¸ö²éѯ»ñÈ¡2003ÄêÄÚÓÉÔ±¹¤#21Íê³ÉµÄ²úÆ·ÀàÐÍΪ#1ºÍ#2µÄÏúÁ¿£¬²¢¸ù¾Ý2003ÄêµÄÏúÊÛÊý¾ÝÔ¤²â³ö2004Äê1Ô¡¢2Ô¡¢3ÔµÄÏúÁ¿¡£
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[1,2004]=sales_amount[1,2003],
Sales_amount[2,2004]=sales_amount[2,2003] + sales_amount[3,2003],
Sales_amount[3,2004]=ROUND(sales_amount[3,2003]*1.25,2)
)
Order by prd_type_id,year,month;
ÏÖÔÚС·ÖÎöÒ»ÏÂÉÏÃæÕâ¸ö²éѯ£º
partition by (prd_type_id)Ö¸¶¨½á¹ûÊǸù¾Ýprd_type_id·ÖÇøµÄ¡£
dimension by (month,year)¶¨ÒåÊý×éµÄά¶ÈÊÇmonthºÍyear¡£Õâ¾ÍÒâζ×űØÐëÌṩÔ·ݺÍÄê·Ý²ÅÄÜ·ÃÎÊÊý×éÖеĵ¥Ôª¡£
measures (amount sales_amount)±íÃ÷Êý×éÖеÄÿ¸öµ¥Ôª°üº¬Ò»¸öÊýÁ¿£¬Í¬Ê±±íÃ÷Êý×éÃûΪsales_amount¡£
MEASURESÖ®ºóµÄÈýÐÐÃüÁî·Ö±ðÔ¤²â2004Äê1Ô¡¢2Ô¡¢3ÔµÄÏúÁ¿¡£
Order by prd_type_id,year,month½ö½öÊÇÉèÖÃÕû¸ö²éѯ·µ»Ø½á¹ûµÄ˳Ðò¡£
ÉÏÃæÕâ¸ö²éѯµÄÊä³ö½á¹ûÈçÏ£º
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 1 10034.84
1 2003 2 15144.65
1 2003 3 20137.83
1 2003 &
Ïà¹ØÎĵµ£º
˵µ½Èí½âÎö£¨soft prase£©ºÍÓ²½âÎö£¨hard prase£©£¬¾Í²»Äܲ»ËµÒ»ÏÂOracle¶ÔsqlµÄ´¦Àí¹ý³Ì¡£µ±Äã·¢³öÒ»ÌõsqlÓï¾ä½»¸¶Oracle£¬ÔÚÖ´ÐкͻñÈ¡½á¹ûÇ°£¬Oracle¶Ô´Ësql½«½øÐм¸¸ö²½ÖèµÄ´¦Àí¹ý³Ì£º
1¡¢Óï·¨¼ì²é£¨syntax check£©
¼ì²é´ËsqlµÄƴдÊÇ·ñÓï·¨¡£
2¡¢ÓïÒå¼ì²é£¨semantic check£©
ÖîÈç¼ì²ésqlÓï¾äÖеķÃÎÊ¶Ô ......
±¾ÎÄÀ´×Ô£ºWWW.NC21.CN ÐÂÊÀ¼Íƽ̨Íø
±¾Îĸü¶à½«»á½éÉÜÈý˼ÔÚÈÕ³£Öо³£»áÓõ½µÄ£¬»òÕßËäÈ»ºÜÉÙÓõ½£¬µ«ÊǸоõͦÓÐÒâ˼µÄһЩº¯Êý¡£·Ö¶þÀà½éÉÜ£¬·Ö±ðÊÇ£º
¡¡¡¡ÖøÃûº¯Êýƪ¡¡£¾³£Óõ½µÄº¯Êý
¡¡¡¡·ÇÖøÃûº¯Êýƪ£¼´ËäÈ»ºÜÉÙÓõ½£¬µ«Ä³Ð©Çé¿öÏÂÈ´ºÜʵÓÃ
¡¡¡¡×¢£ºN±íʾÊý×ÖÐÍ£¬C±íʾ×Ö·ûÐÍ£¬D±íʾ ......
Name:JDBC-DEV1
JNDI Name:jdbc.datasource.DS1
AdminServer
Row Prefetch Size: 48
Stream Chunk Size:256
URL:jdbc:oracle:thin:@192.168.0.118:1521:AAT
Driver Class Name:oracle.jdbc.OracleDriver
Á¬½Óoracle¼°postgresqlµÄÁ¬½Ó×Ö·û´®
package com.lwf.eus.util;
import java.sql.Conn ......
֮ǰ¶ÔORACLEÖеıäÁ¿Ò»Ö±Ã»¸öÌ«Çå³þµÄÈÏʶ£¬±ÈÈç˵ʹÓ㺡¢&¡¢&&¡¢DEIFINE¡¢VARIABLE……µÈµÈ¡£½ñÌìÕýºÃÏÐÏÂÀ´£¬ÉÏÍøËÑÁËËÑÏà¹ØµÄÎÄÕ£¬»ã×ÜÁËһϣ¬ÌùÔÚÕâÀ·½±ãѧϰ¡£
==================================================================================
ÔÚoracle ÖУ¬¶ÔÓÚÒ»¸öÌá½ ......