| Access中使用Create Procedure創(chuàng)建存儲過程 |
| 2009-4-17 9:31:08 哈爾濱百姓網(wǎng) 來源:百度空間 瀏覽 次 【大 中 小】【打印】【關(guān)閉】 |
|
在Access自身的幫助中看到了Create Procedure語句，測試了老半天，終于發(fā)現(xiàn)了訣竅：必須使用OleDb連接才能使用Create Procedure語句。ODBC連接不支持該語句，提示Create Table語法錯(cuò)誤。
創(chuàng)建了存儲過程后，使用Office Access工具打開數(shù)據(jù)庫，在“對象 - 查詢”中能夠看到你創(chuàng)建的存儲過程。
創(chuàng)建存儲過程的語法：
Create Procedure YourProc
(
@param1 varchar(254),
@param2 int
)
As
(
select * from Table1 where ID>@param2 and username=@param1
)
查詢數(shù)據(jù)時(shí)只需要使用：
Rs.Open "YourProc admin,1", Conn
myproc.vbs
[復(fù)制此代碼]CODE:
Set DB = GetObject("script:http://www.zope.org/Members/Rimifon/DbHelper.sct")
DB.ConnStr = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=MyProc.mdb"
DB.NonQuery "create table Sheet1(id counter,name varchar(254),score int)"
DB.NonQuery "Create Procedure MyProc(@name varchar(254), @score int) as (insert into Sheet1(name,score) values(@name, @score))"
DB.NonQuery "Create Procedure Result as (select * from Sheet1)"
MsgBox "創(chuàng)建了表和存儲過程"
DB.NonQuery "Execute MyProc Rimifon,90"
DB.NonQuery "Exec MyProc FengYun,93"
Set Ds = DB.DataSet("Result")
MsgBox "執(zhí)行了存儲過程"
DB.NonQuery "drop Procedure MyProc"
DB.NonQuery "drop Procedure Result"
DB.NonQuery "drop table Sheet1"
MsgBox "刪除了表和存儲過程"
Set DB = Nothing
Dim Result
Result="所有記錄：" & Chr(13)
For Each Item In Ds
If IsObject(Item) Then
Result = Result & Item.ID & Chr(9) &_
Item.Name & Chr(9) & Chr(9) &_
Item.Score & Chr(13)
End If
Next
Set Ds = Nothing
MsgBox Result風(fēng)雲(yún)數(shù)據(jù)庫操作組件
http://www.zope.org/Members/Rimifon/DbHelper.sct的內(nèi)容CODE:
<?xml version="1.0" encoding="utf-8"?>
<package>
<?component error="false" debug="false"?>
<comment>
<![CDATA[ 風(fēng)雲(yún)數(shù)據(jù)庫操作組件
Author: Rimifon
LastDate: 10:50 2006-08-19]]>
</comment>
<scriptlet id="DataHelper">
<registration classid="{12345678-aabb-ccdd-eeff-012345678901}" progid="Rimifon.DataHelper" description="風(fēng)雲(yún)數(shù)據(jù)庫操作組件" version="1.2" remotable="true">
<script language="vbscript">
<![CDATA[Function Register()
MsgBox "歡迎使用Rimifon.DataHelper組件，你已經(jīng)成功注冊了本組件。", 0, "組件注冊提示"
End Function
Function Unregister()
MsgBox "感謝使用Rimifon.DataHelper組件，你已經(jīng)成功卸載了本組件。", 0, "組件卸載提示"
End Function]]>
</script>
</registration>
<public>
<property name="Name" dispid="0" description="組件名稱，只讀"><get internalName="ProductName" /></property>
<property name="ConnStr" description="數(shù)據(jù)庫連接字符串設(shè)置" />
<method name="CreateAccess" description="創(chuàng)建空白Access數(shù)據(jù)庫，成功后返回連接字符串">
<parameter name="DbPath" description="新數(shù)據(jù)庫的位置，如果不指定則使用ConnStr中設(shè)置的數(shù)據(jù)庫位置" />
</method>
<method name="RecordSet" description="返回一個(gè)數(shù)據(jù)庫記錄集對象">
<parameter name="ConnectionString" description="設(shè)置新記錄集的連接字符串（為空則使用公共連接字符串）" />
</method>
<method name="ParseSql" description="解析Sql字符串，vb和vbs中不推薦使用">
<parameter name="Sql" description="代Sql語句" />
<parameter name="Rule" description="代Sql語句中替代單引號的字符串，默認(rèn)為Chr(12)" />
</method>
<method name="NonQuery" description="執(zhí)行一個(gè)不返回結(jié)果集的查詢">
<parameter name="Sql" description="查詢語句" />
<parameter name="Rec" description="記錄集對象，為空則生成臨時(shí)記錄集對象" />
</method>
<method name="Scalar" description="執(zhí)行一個(gè)返回單行單字段的數(shù)據(jù)">
<parameter name="Sql" description="查詢語句" />
<parameter name="Rec" description="記錄集對象，為空則生成臨時(shí)記錄集對象" />
</method>
<method name="DataSet" description="執(zhí)行查詢并返回指定頁碼的數(shù)據(jù)集">
<parameter name="Sql" description="查詢語句" />
<parameter name="Size" description="設(shè)置每頁顯示的條數(shù)，為0則全部顯示" />
<parameter name="Page" description="設(shè)值頁碼（第幾頁）" />
<parameter name="Rec" description="記錄集對象，為空則生成臨時(shí)記錄集對象" />
</method>
<method name="CompactAccess" description="壓縮指定Access數(shù)據(jù)庫，壓縮失敗后返回失敗原因">
<parameter name="DbPath" description="被壓縮的數(shù)據(jù)庫，不指定則使用ConnStr中設(shè)置的數(shù)據(jù)庫位置" />
</method>
</public>
<script language="javascript">
<![CDATA[var Nothing;
var ConnStr = "Driver=Microsoft Excel Driver (*.xls);ReadOnly=0;DBQ=Rimifon.xls";
function ProductName()
{
return "風(fēng)雲(yún)數(shù)據(jù)庫操作組件";
}
function CreateAccess(DbPath)
{
DbPath = DbPath?("Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + DbPath):ConnStr;
var Catalog = new ActiveXObject("AdoX.Catalog");
try
{
Catalog.Create(DbPath);
Catalog.ActiveConnection.Close();
}
catch(err)
{
DbPath = new Boolean;
DbPath.message = err.message;
}
Catalog = Nothing;
CollectGarbage();
return DbPath;
}
function RecordSet(ConnectionString)
{
var Rec = new ActiveXObject("Adodb.RecordSet");
Rec.ActiveConnection = ConnectionString?ConnectionString:ConnStr;
Rec.CursorType = 1;
Rec.CursorLocation = 2;
Rec.LockType = 3;
return Rec;
}
function ParseSql(Sql, Rule)
{
if(!Rule) Rule="\f";
if(Sql.indexOf(Rule)<0) return Sql;
return Sql.replace(/'/g,"''").replace(new RegExp(Rule,"g"),"'");
}
function NonQuery(Sql, Rec)
{
if(!Rec) Rec = new RecordSet;
Rec.Source = Sql;
Rec.Open();
if(!arguments[1])
{
Dispose(Rec);
}
else
{
if(Rec.State) Rec.Close();
}
}
function Scalar(Sql, Rec)
{
if(!Rec) Rec = new RecordSet;
Rec.Source = Sql;
Rec.Open();
var Result = Rec.EOF?null:Rec.Fields.Item(0).Value;
if(Rec.State) Rec.Close();
if(!arguments[1]) Dispose(Rec);
return Result;
}
function DataSet(Sql, Size, Page, Rec)
{
var dataArr = new Array;
if(Size instanceof ActiveXObject)
{
Rec = arguments[3] = Size;
Size = 0;
}
if(!Rec) Rec = new RecordSet;
Rec.Source = Sql;
Rec.Open();
Size = parseInt(Size);
if(isNaN(Size) || !Size) Size = Rec.RecordCount;
if(Size) Rec.PageSize = Size;
if(!Page || isNaN(Page) || Page<0) Page = 1;
if(Page > Rec.PageCount) Page = Rec.PageCount;
dataArr.AbsolutePage = Page;
if(Page > 0) Rec.AbsolutePage = Page;
dataArr.RecordCount = Rec.RecordCount;
dataArr.PageSize = Size;
dataArr.PageCount = Rec.PageCount;
var C = 0;
while(!Rec.EOF && C++ < Size)
{
var Fields = new Object;
for(var x=0; x<Rec.Fields.Count; x++)
{
Fields[x] = Fields[Rec.Fields(x).Name.replace(/\s/g,"_")] = Rec.Fields.Item(x).Value;
}
dataArr.push(Fields);
Rec.MoveNext();
}
Rec.Close();
if(!arguments[3]) Dispose(Rec);
return dataArr;
}
function CompactAccess(DbPath)
{
if(!DbPath)
{
DbPath = ConnStr.match(/(dbq|data source)=([^;]*)/i);
if(!DbPath) return "未指定數(shù)據(jù)庫位置。";
DbPath = DbPath[2];
}
DbPath = DbPath.replace(/\//g,"\\");
var DbName = DbPath.slice(DbPath.lastIndexOf("\\")+1);
var BackDb = DbName + Math.random() + ".MyDocs";
DbPath = DbPath.substr(0, DbPath.lastIndexOf("\\"));
var DBQ = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + DbPath;
try
{
var Jro = new ActiveXObject("Jro.JetEngine");
var Fso = new ActiveXObject("Scripting.FileSystemObject");
Jro.CompactDatabase(DBQ+DbName, DBQ+BackDb);
var File = Fso.GetFile(DbPath+DbName);
File.Delete();
File = Fso.GetFile(DbPath+BackDb);
File.Name = DbName;
Jro = Fso = Nothing;
CollectGarbage();
}
catch(err)
{
return err.message;
}
}
function Dispose(Rec)
{
Rec.ActiveConnection.Close();
Rec = Nothing;
CollectGarbage();
}]]>
</script>
</scriptlet>
</package>
轉(zhuǎn)自：http://hi.baidu.com/totaobao/blog/item/9549c1176c791e09c83d6d72.html |
|
| [責(zé)任編輯：佚名] |
|
| 【信息發(fā)布】【論壇交流】【留言反饋】【打印網(wǎng)頁】【大 中 小】【↑頂部】 |
|
|
|
|
|
|
