ÔÚSQLÊý¾Ý¿âÖе¼ÈëExcel
--Excelµ¼Èësql´æ´¢¹ý³Ì£¨¼òµ¥£©
Create procedure import
@tablename varchar(100),
@filepath varchar(100)
as
EXEC( 'INSERT INTO ' + @tablename +
' SELECT * from
OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',''Data Source="' + @filepath + '";
User ID=Admin;Password=;Extended properties=Excel 5.0'')sheet1$'
) --sheet1$ Òª»»³ÉÄãÒªµ½µÄ¹¤×÷±íÃû³Æ, $·ûºÅÒ²Òª
--µ÷ÓÃ
exec import 'abc','D:\abc.xls'
˵Ã÷£º ÔÚÊý¾Ý¿âÖÐÖ´ÐÐExcelµ¼Èëµ¼³ö£¬¿ÉʵÏֶַε¼Èë¡£¶Ô´óÊý¾ÝÅúÁ¿µ¼ÈëÖ´ÐÐËٶȿìЩ¡£ºÍÔÚÒ³ÃæÉϵ¼Èë²î²»¶à£¬ËÙ¶ÈÊÇÖ÷ÒªÎÊÌâ¡£
Ïà¹ØÎĵµ£º
create PROCEDURE pagelist
@tablename nvarchar(50),
@fieldname nvarchar(50)='*',
@pagesize int output,--ÿҳÏÔʾ¼Ç¼ÌõÊý
@currentpage int output,--µÚ¼¸Ò³
@orderid nvarchar(50),--Ö÷¼üÅÅÐò
@sort int,--ÅÅÐò·½Ê½£¬1±íʾÉýÐò£¬0±íʾ½µÐòÅÅÁÐ
......
ËÄ£ºORACLEµÄÓÅ»¯Æ÷
ÓÅ»¯Æ÷ÓÐʱҲ±»³ÆΪ²éѯÓÅ»¯Æ÷£¬ÕâÊÇÒòΪ²éѯÊÇÓ°ÏìÊý¾Ý¿âÐÔÄÜ×îÖ÷ÒªµÄ²¿·Ö£¬²»ÒªÒÔΪֻÓÐSELECTÓï¾äÊDzéѯ¡£Êµ¼ÊÉÏ£¬´øÓÐÈκÎWHEREÌõ¼þµÄDML(INSERT¡¢UPDATE¡¢DELETE)Óï¾äÖж¼°üº¬²éѯҪÇó£¬ÔÚºóÃæµÄÎÄÕÂÖУ¬µ±Ëµµ½²éѯʱ£¬²»Ò»¶¨Ö»ÊÇÖ¸SELECTÓï¾ä£¬Ò²ÓпÉÄÜÖ¸DMLÓï¾äÖеIJéѯ²¿·Ö¡£ÓÅ»¯Æ÷ÊÇËùÓйØϵ ......
showºÍsetÃüÁîÊÇÁ½ÌõÓÃÓÚά»¤SQL*Plusϵͳ±äÁ¿µÄÃüÁî
SQL> show all --²é¿´ËùÓÐ68¸öϵͳ±äÁ¿Öµ
SQL> show user --ÏÔʾµ±Ç°Á¬½ÓÓû§
SQL> show error¡¡¡¡ --ÏÔʾ´íÎó
SQL> set heading off --½ûÖ¹Êä³öÁбêÌ⣬ĬÈÏֵΪON
SQL> set feedback off --½ûÖ¹ÏÔʾ×îºóÒ»ÐеļÆÊý·´À¡ÐÅÏ¢£¬Ä¬ÈÏֵΪ"¶Ô6¸ö» ......
create database test1
use test1
create table admin
(
id int primary key ,
name varchar(50),
pwd varchar(50),
)
insert into admin values(1,'aa','aa')
alter table admin add tel varchar(50) ......
--Èç¹û´æÔÚÊý¾Ý¿âprogrammerPay ¾Íɾ³ý
if exists (select * from sysdatabases where name='programmerPay')
drop database programmerPay
go
--´´½¨Êý¾Ý¿âprogrammerPay
create database programmerPay
on primary
(
name ='programmerPay_data',
filename='D:\programmerPay\programmerPay_data.mdf',
......