PL/SQLʶȤ
×÷Õß: ÈýÊ®¶øÁ¢Ê±¼ä£º2009Äê10ÔÂ15ÈÕ 19:21:13±¾Îijö×Ô ¡°inthirties£¨ÈýÊ®¶øÁ¢£©¡±²©¿Í£¬×ªÔØÇëÎñ±Ø×¢Ã÷×÷Õߺͱ£Áô³ö´¦http://blog.csdn.net/inthirties/archive/2009/10/15/4673331.aspx ѧϰÊÇ¿ÝÔïµÄ£¬ËùÒÔ×÷Ϊһ¸öѧϰÕߣ¬ÒªÑ§»áÔÚѧϰÖÐÕÒµ½¿ìÀÖ£¬ÕâÑù²ÅÄܼ¤·¢ÐËȤ£¬ÐËȤÊÇ×îºÃµÄÀÏʦ£¬ÕâÑù£¬Ñ§Ï°¾ÍÂýÂýµÄ±ä³ÉÁËÒ»¼þ¿ìÀÖµÄÊÂÇéß·¡£Ô±¾Ò»Ö±×öOracleµÄ¹ÜÀíºÍά»¤£¬¶ÔPLSQL²»ÊǺܸÐÐËȤ£¬ÔÚCSDNÀï¾³£ÓÐЩÅóÓÑÎʵ½¿ª·¢µÄÎÊÌ⣬¿ªÊ¼ÊÇͨ¹ýgoogleÕÒµ½´ð°¸£¬ºóÀ´ÔÚÕÒÑ°µÄ¹ý³ÌÖУ¬Ñ§Ï°µ½ÁË֪ʶµã£¬Ò²¾ÍÂýÂýµÄ¿ªÊ¼°ïÖú±ðÈ˽øÐÐsqlµÄ¿ª·¢°ïÖú£¬ÕâÖÖ°ïÖúÒ²¸øÎÒÁËѧϰµÄ¶¯Á¦£¬ËùÒÔPLSQLµÄ¿ª·¢µÄ»ù±¾¶«Î÷Ò²¾ÍÕÆÎÕÁ˲»ÉÙÁË£¬¶øÇÒÓÉÓÚÊÇ¿ìÀÖµÄѧϰ£¬ËùÒÔÒ²±È½ÏÇáËÉ£¬ÕâÀï¾ÍÊÇÓøöÒ»¸öÍøÓѵÄÎÊÌâµÄÍêÕû½âÌâ¹ý³ÌÀ´¿´¿´ÎÒµ±Ê±¶ÔOverµÄѧϰ¡£ ÕâÊÇÒ»¸ö±È½ÏÓÐȤµÄSQLÌ⣬
ÓÐÒ»¸ö±í£¬½á¹¹ÈçÏ£º
±àºÅ£¬ÐÕÃû£¬Ê±¼ä£¬»ù±¾¹¤×Ê£¬½±½ð£¬¿Û¿î
1 ÕÅÈý 8-20 1000 1000 5
2 ÀîËÄ 8-20 1100 900 10
3 ÕÅÈý 9-20 900 1000 10
4 ÀîËÄ 9-20 1100 900 13
5 ÍõÎå 9-20 900 100 0 Èç¹ûÎÒ¶Ô8-20 ºÍ 9-20 µÄ»ù±¾¹¤×Ê×öÒ춯Êý¾Ý²éѯ ÎÒÒªÇóµÃµ½ÕâÑùµÄÒ»¸öÁÙʱÊý¾ÝÊÓͼ
±àºÅ£¬ÐÕÃû£¬»ù±¾¹¤×Ê ±¸×¢
1 ÕÅÈý -100
2 ÍõÎå 900 ÐÂÔö
ÔÌûÔÚhttp://topic.csdn.net/u/20090830/20/4f135d40-a52d-4346-ac15-599b2951af39.html?seed=709316630&r=59447116#r_59447116
Óöµ½ÓÐȤµÄÎÊÌ⣬һϾͼ¤·¢ÎÒµÄÐËȤ£¬¿´ÕâÑùÐèÒªÉÏϼǼ¶ÔÕյģ¬¾ÍÏëµ½lagºÍleadº¯ÊýÁË lagºÍleadµÄ¹¦ÄÜÊÇOracleÊý¾Ý¿â¶ÀÓеŦÄÜ£¬ÊÇ·Ö×麯ÊýÖеÄÒ»¸öºÜÓмÛÖµµÄ·½·¨¡£
ÕâÀïÒ²½èÕâ¸öÌ⣬ºÃºÃµÄ½âÊÍÒ»ÏÂlagºÍleadµÄÓ÷¨
Lag È¡Ç°ÃænÌõµÄ¼Ç¼
lag(field, n, defaultv) ÕâÀïfieldÊÇÎÒÃÇÐèҪȡµÄ×ֶΣ¬ nÊÇÈ¡¶àÉÙ¸ö£¬defaultVÊÇÈ¡²»µ½µÄĬÈÏÖµ¡£
LeadÈ¡ºóÃæÄÇÌõµÄ¼Ç¼
lead(field, n, defaultv) ºÍlagÒ»ÑùµÄ£¬fieldÊÇÈ¡ÖµµÄ×ֶΣ¬ nÊÇÇ°ºó¼¸Ìõ£¬ defualtvÊÇĬÈÏÖµ£¬ÀàËÆnvl(lag(field, n), defaultv)
lagºÍlead±ØÐëºÍ¿ª´°º¯ÊýoverÒ»ÆðÀ´Ê¹Óã¬
ÄÇô֪µÀOracleÀïÓÐÕâÑùʵÓõŦÄÜ£¬ÄÇôÎÒÃÇÈçºÎ¾ßÌåÀ´Ê¹ÓÃlag/leadºÍover£¬ÊµÕ½Ê¤ÓÚÒ»ÇÐ
ÏÈ×öÊý¾Ý
ÏÖÔÚÏ°¹ßÓÃwith asÀ´×öÊý¾ÝÁË
with temp as
Ïà¹ØÎĵµ£º
index.jsp
<%@ page language="java" import="java.sql.*" import="java.lang.*" import="java.util.*" pageEncoding="GB2312"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<%!
& ......
ʵ¼ÊÓ¦ÓÃÖУ¬ÎÒÃǾ³£ÐèÒª°ÑExcelÖеÄÊý¾Ýµ¼ÈëMS SQL Server »òMySQL OracleµÈÊý¾Ý¿âÖС£ÕâЩÊý¾Ý¿â¶¼ÌṩÁ˺ܺõŤ¾ß¹©Óû§Ö±½Ó½«ExcelÖеÄÊý¾Ýµ¼ÈëÊý¾Ý¿âÖС£µ«ÊǺܶàʱºòÎÒÃDz¢²»ÄÜÖ±½Ó²Ù×÷Êý¾Ý¿â¹ÜÀíÆ÷£¨ÒòΪ°²È«ÐèÒª»áÅäÖ÷À»ðǽÀ¹½ØÊý¾Ý¿â¶Ë¿Ú£©£¬ÎÒÃÇÖ»ÄÜͨ¹ýwebÒ³Ãæ¶ÔÊý¾Ý¿â½øÐÐÔ¶³Ì²Ù×÷¡£Õâ¸öʱºò£¬½«ExcelÖеÄÊý ......
Èç¹ûÄãÕýÔÚ¸ºÔðÒ»¸ö»ùÓÚSQL ServerµÄÏîÄ¿£¬»òÕßÄã¸Õ¸Õ½Ó´¥SQL Server£¬Ä㶼ÓпÉÄÜÒªÃæÁÙһЩÊý¾Ý¿âÐÔÄܵÄÎÊÌ⣬ÕâƪÎÄÕ»áΪÄãÌṩһЩÓÐÓõÄÖ¸µ¼£¨ÆäÖдó¶àÊýÒ²¿ÉÒÔÓÃÓÚÆäËüµÄDBMS£©¡£
ÔÚÕâÀÎÒ²»´òËã½éÉÜʹÓÃSQL ServerµÄÇÏÃÅ£¬Ò²²»ÄÜÌṩһ¸ö°üÖΰٲ¡µÄ·½°¸£¬ÎÒËù×öµÄÊÇ×ܽáһЩ¾Ñé----¹ØÓÚÈçºÎÐγÉÒ»¸öºÃµÄÉè¼Æ ......
¹Û²ì½üÀ´µÄһЩ°²È«Ê¼þ¼°Æäºó¹û£¬°²È«×¨¼ÒÃÇÒѾµÃµ½Ò»¸ö½áÂÛ£¬ÕâЩÍþвÖ÷ÒªÊÇͨ¹ýSQL×¢ÈëÔì³ÉµÄ¡£ËäȻǰÃæÓÐÐí¶àÎÄÕÂÌÖÂÛÁËSQL×¢È룬µ«½ñÌìËùÌÖÂÛµÄÄÚÈÝÒ²Ðí¿É°ïÖúÄã¼ì²é×Ô¼ºµÄ·þÎñÆ÷£¬²¢²ÉÈ¡ÏàÓ¦·À·¶´ëÊ©¡£
SQL×¢Èë¹¥»÷µÄÖÖÀà
Öª±ËÖª¼º£¬·½¿Éȡʤ¡£Ê×ÏÈÒªÇå³þSQL×¢Èë¹¥»÷ÓÐÄÄЩÖÖÀà¡£
1.ûÓÐÕýÈ·¹ýÂËתÒå× ......
ÓÃÏÂÃæµÄÊÓͼ£º
SQL> desc user_col_comments;
Name Null? Type
------------------------------- -------- ----
TABLE_NAME &nbs ......