SQL ServerÓëExcelÊý¾Ý»¥µ¼
SQL ServerÓëExcelÊý¾Ý»¥µ¼
´ÓSQL ServerÖе¼Èë/µ¼³ö Excel µÄ»ù±¾·½·¨
/*=================== µ¼Èë/µ¼³ö Excel µÄ»ù±¾·½·¨ ===================*/
´ÓExcelÎļþÖÐ,µ¼ÈëÊý¾Ýµ½SQLÊý¾Ý¿âÖÐ,ºÜ¼òµ¥,Ö±½ÓÓÃÏÂÃæµÄÓï¾ä:
/*========================================================*/
--Èç¹û½ÓÊÜÊý¾Ýµ¼ÈëµÄ±íÒѾ´æÔÚ
insert into ±í select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:test.xls',sheet1$)
--Èç¹ûµ¼ÈëÊý¾Ý²¢Éú³É±í
select * into ±í from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:test.xls',sheet1$)
/*========================================================*/
--Èç¹û´ÓSQLÊý¾Ý¿âÖÐ,µ¼³öÊý¾Ýµ½Excel,Èç¹ûExcelÎļþÒѾ´æÔÚ,¶øÇÒÒѾ°´ÕÕÒª½ÓÊÕµÄÊý¾Ý´´½¨ºÃ±íÍ·,¾Í¿ÉÒÔ¼òµ¥µÄÓÃ:
insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:test.xls',sheet1$)
select * from ±í
--Èç¹ûExcelÎļþ²»´æÔÚ,Ò²¿ÉÒÔÓÃBCPÀ´µ¼³ÉÀàExcelµÄÎļþ,×¢Òâ´óСд:
--µ¼³ö±íµÄÇé¿ö
EXEC master..xp_cmdshell 'bcp Êý¾Ý¿âÃû.dbo.±íÃû out "c:test.xls" /c -/S"·þÎñÆ÷Ãû" /U"Óû§Ãû" -P"ÃÜÂë"'
--µ¼³ö²éѯµÄÇé¿ö
EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname from pubs..authors ORDER BY au_lname" queryout "c:test.xls" /c -/S"·þÎñÆ÷Ãû" /U"Óû§Ãû" -P"ÃÜÂë"'
/*--˵Ã÷:
c:test.xls Ϊµ¼Èë/µ¼³öµÄExcelÎļþÃû.
sheet1$ ΪExcelÎļþµÄ¹¤×÷±íÃû,Ò»°ãÒª¼ÓÉÏ$²ÅÄÜÕý³£Ê¹ÓÃ.
--*/
--ÉÏÃæÒѾ˵¹ý,ÓÃBCPµ¼³öµÄÊÇÀàExcelÎļþ,ÆäʵÖÊΪÎı¾Îļþ,
--Òªµ¼³öÕæÕýµÄExcelÎļþ.¾ÍÓÃÏÂÃæµÄ·½·¨
/*--Êý¾Ýµ¼³öEXCEL
µ¼³ö±íÖеÄÊý¾Ýµ½Excel,°üº¬×Ö¶ÎÃû,ÎļþΪÕæÕýµÄExcelÎļþ
,Èç¹ûÎļþ²»´æÔÚ,½«×Ô¶¯´´½¨Îļþ
,Èç¹û±í²»´æÔÚ,½«×Ô¶¯´´½¨±í
»ùÓÚͨÓÃÐÔ¿¼ÂÇ,½öÖ§³Öµ¼³ö±ê×¼Êý¾ÝÀàÐÍ
--×Þ½¨ 2003.10--*/
/*--µ÷ÓÃʾÀý
p_exporttb @tbname='µØÇø×ÊÁÏ',@path='c:',@fname='aa.xls'
--*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_exporttb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_exporttb]
GO
create proc p_exporttb
@tbname sysname, --Òªµ¼³öµÄ±íÃû
@path nvarchar(1000), --Îļþ´æ·ÅĿ¼
@f
Ïà¹ØÎĵµ£º
create PROCEDURE pagelist
@tablename nvarchar(50),
@fieldname nvarchar(50)='*',
@pagesize int output,--ÿҳÏÔʾ¼Ç¼ÌõÊý
@currentpage int output,--µÚ¼¸Ò³
@orderid nvarchar(50),--Ö÷¼üÅÅÐò
@sort int,--ÅÅÐò·½Ê½£¬1±íʾÉýÐò£¬0±íʾ½µÐòÅÅÁÐ
......
PL/SQL DEVELOPER »ù±¾Ó÷¨Ïê½â(½¨Òéд¹ýµÚÒ»¸ö´æ´¢¹ý³ÌºóµÄ³õÊֱضÁ)
ÓùýoracleµÄ¶¼±§Ô¹£¬ÎªÁËÎȶ¨ ËüÌṩµÄͼÐλ¯²Ù×÷ ËÙ¶ÈÂýµÄÈÃÈËÉËÐÄѽ£¬p4+128MµÄ»úÆ÷Ö»ÒªÆô¶¯Ò»¸ö
oracle·þÎñ¾Í¹»ÈÃÈËÉËÐĵģ¬ÔÙÔÚdba studioÀïÃæ²Ù×÷ÕæÄÜÈÃÈË¿Þ¡£
pl/sql developerÕýÊǽâ¾öÕâ¸öÎÊÌâµ ......
PL/SQL¿é
declare
begin
--SQLÓï¾ä
--Ö±½ÓдµÄSQLÓï¾ä(DML/TCL)
--¼ä½Óдexecute immediate <DDL/DCLÃüÁî×Ö·û´®>
--select Óï¾ä
<1>±ØÐë´øÓÐinto×Ó¾ä
......
ÎÒÃÇÔÚÊý¾Ý¿âÖÐʹÓñíµÄʱºò,¾³£»áÓöµ½Á½ÖÖʹÓñíµÄ·½·¨,·Ö±ð¾ÍÊÇʹÓÃÁÙʱ±í¼°±í±äÁ¿¡£ÔÚʵ¼ÊʹÓõÄʱºò£¬ÎÒÃÇÈçºÎÁé»îµÄÔÚ´æ´¢¹ý³ÌÖÐÔËÓÃËüÃÇ£¬ËäÈ»ËüÃÇʵÏֵŦÄÜ»ù±¾ÉÏÊÇÒ»ÑùµÄ£¬¿ÉÈçºÎÔÚÒ»¸ö´æ´¢¹ý³ÌÖÐÓÐʱºòȥʹÓÃÁÙʱ±í¶ø²»Ê¹Óñí±äÁ¿£¬ÓÐʱºòȥʹÓñí±äÁ¿¶ø²»Ê¹ÓÃÁÙʱ±íÄØ?
¡¡¡¡ÁÙʱ±í
¡¡¡¡ÁÙʱ±íÓëÓÀ¾Ã±íÏàËÆ£¬ ......
ÏÈÀ´Ò»¶Î´úÂ룺
WITH OrderedOrders AS
(SELECT *,
ROW_NUMBER() OVER (order by [id])as RowNumber¡¡¡¡--idÊÇÓÃÀ´ÅÅÐòµÄÁÐ
from table_info ) --table_infoÊDZíÃû
SELECT *
from OrderedOrders
WHERE RowNumber between 50 and 60;
ÔÚwindows server 2003, sql server 2005 CTP,P4 2.66GHZ,1GB ÄÚ´æϲâÊÔ£¬Ö´ÐÐʱ ......