Access中使用Create Procedure创建存储过程第1/2页

ttx9n · 发表于 2006-9-7 00:00:00

在Access自身的帮助中看到了Create Procedure语句，测试了老半天，终于发现了诀窍：必须使用OleDb连接才能使用Create Procedure语句。ODBC连接不支持该语句，提示Create Table语法错误。
　　创建了存储过程后，使用Office Access工具打开数据库，在“对象 - 查询”中能够看到你创建的存储过程。
创建存储过程的语法：
复制代码代码如下:
Create Procedure YourProc
(
        @param1 varchar(254),
        @param2 int
)
As
(
        select * from Table1 where ID>@param2 and username=@param1
)
查询数据时只需要使用：
        Rs.Open "YourProc admin,1", Conn

myproc.vbs
复制代码代码如下:
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 "创建了表和存储过程"
DB.NonQuery "Execute MyProc Rimifon,90"
DB.NonQuery "Exec MyProc FengYun,93"
Set Ds = DB.DataSet("Result")
MsgBox "执行了存储过程"
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

http://www.zope.org/Members/Rimifon/DbHelper.sct的内容
复制代码代码如下:
<?xml version="1.0" encoding="utf-8"?>
<package>
    <?component error="false" debug="false"?>
    <comment>
        <![CDATA[            風雲数据库操作组件
            Author: Rimifon
            LastDate: 10:50 2006-08-19]]>
    </comment>
    <scriptlet id="DataHelper">
        <registration classid="{12345678-aabb-ccdd-eeff-012345678901}" progid="Rimifon.DataHelper" description="風雲数据库操作组件" version="1.2" remotable="true">
            <script language="vbscript">
                <![CDATA[Function Register()
    MsgBox "欢迎使用Rimifon.DataHelper组件，你已经成功注册了本组件。", 0, "组件注册提示"
End Function
Function Unregister()
    MsgBox "感谢使用Rimifon.DataHelper组件，你已经成功卸载了本组件。", 0, "组件卸载提示"
End Function]]>
            </script>
        </registration>
        <public>
            <property name="Name" dispid="0" description="组件名称，只读"><get internalName="ProductName" /></property>
            <property name="ConnStr" description="数据库连接字符串设置" />
            <method name="CreateAccess" description="创建空白Access数据库，成功后返回连接字符串">
                <parameter name="DbPath" description="新数据库的位置，如果不指定则使用ConnStr中设置的数据库位置" />
            </method>
            <method name="RecordSet" description="返回一个数据库记录集对象">
                <parameter name="ConnectionString" description="设置新记录集的连接字符串（为空则使用公共连接字符串）" />
            </method>
            <method name="ParseSql" description="解析Sql字符串，vb和vbs中不推荐使用">
                <parameter name="Sql" description="代Sql语句" />
                <parameter name="Rule" description="代Sql语句中替代单引号的字符串，默认为Chr(12)" />
            </method>
            <method name="NonQuery" description="执行一个不返回结果集的查询">
                <parameter name="Sql" description="查询语句" />
                <parameter name="Rec" description="记录集对象，为空则生成临时记录集对象" />
            </method>
            <method name="Scalar" description="执行一个返回单行单字段的数据">
                <parameter name="Sql" description="查询语句" />
                <parameter name="Rec" description="记录集对象，为空则生成临时记录集对象" />
            </method>
            <method name="DataSet" description="执行查询并返回指定页码的数据集">
                <parameter name="Sql" description="查询语句" />
                <parameter name="Size" description="设置每页显示的条数，为0则全部显示" />
                <parameter name="Page" description="设值页码（第几页）" />
                <parameter name="Rec" description="记录集对象，为空则生成临时记录集对象" />
            </method>
            <method name="CompactAccess" description="压缩指定Access数据库，压缩失败后返回失败原因">
                <parameter name="DbPath" description="被压缩的数据库，不指定则使用ConnStr中设置的数据库位置" />
            </method>
        </public>
        <script language="javascript">
            <![CDATA[var Nothing;
var ConnStr = "Driver=Microsoft Excel Driver (*.xls);ReadOnly=0;DBQ=Rimifon.xls";
function ProductName()
{
    return "風雲数据库操作组件";
}
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 "未指定数据库位置。";
        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>
在Access自身的帮助中看到了Create Procedure语句，测试了老半天，终于发现了诀窍：必须使用OleDb连接才能使用Create Procedure语句。ODBC连接不支持该语句，提示Create Table语法错误。
　　创建了存储过程后，使用Office Access工具打开数据库，在“对象 - 查询”中能够看到你创建的存储过程。
创建存储过程的语法：
复制代码代码如下:
Create Procedure YourProc
(
        @param1 varchar(254),
        @param2 int
)
As
(
        select * from Table1 where ID>@param2 and username=@param1
)
查询数据时只需要使用：
        Rs.Open "YourProc admin,1", Conn

myproc.vbs
复制代码代码如下:
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 "创建了表和存储过程"
DB.NonQuery "Execute MyProc Rimifon,90"
DB.NonQuery "Exec MyProc FengYun,93"
Set Ds = DB.DataSet("Result")
MsgBox "执行了存储过程"
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

http://www.zope.org/Members/Rimifon/DbHelper.sct的内容
复制代码代码如下:
<?xml version="1.0" encoding="utf-8"?>
<package>
    <?component error="false" debug="false"?>
    <comment>
        <![CDATA[            風雲数据库操作组件
            Author: Rimifon
            LastDate: 10:50 2006-08-19]]>
    </comment>
    <scriptlet id="DataHelper">
        <registration classid="{12345678-aabb-ccdd-eeff-012345678901}" progid="Rimifon.DataHelper" description="風雲数据库操作组件" version="1.2" remotable="true">
            <script language="vbscript">
                <![CDATA[Function Register()
    MsgBox "欢迎使用Rimifon.DataHelper组件，你已经成功注册了本组件。", 0, "组件注册提示"
End Function
Function Unregister()
    MsgBox "感谢使用Rimifon.DataHelper组件，你已经成功卸载了本组件。", 0, "组件卸载提示"
End Function]]>
            </script>
        </registration>
        <public>
            <property name="Name" dispid="0" description="组件名称，只读"><get internalName="ProductName" /></property>
            <property name="ConnStr" description="数据库连接字符串设置" />
            <method name="CreateAccess" description="创建空白Access数据库，成功后返回连接字符串">
                <parameter name="DbPath" description="新数据库的位置，如果不指定则使用ConnStr中设置的数据库位置" />
            </method>
            <method name="RecordSet" description="返回一个数据库记录集对象">
                <parameter name="ConnectionString" description="设置新记录集的连接字符串（为空则使用公共连接字符串）" />
            </method>
            <method name="ParseSql" description="解析Sql字符串，vb和vbs中不推荐使用">
                <parameter name="Sql" description="代Sql语句" />
                <parameter name="Rule" description="代Sql语句中替代单引号的字符串，默认为Chr(12)" />
            </method>
            <method name="NonQuery" description="执行一个不返回结果集的查询">
                <parameter name="Sql" description="查询语句" />
                <parameter name="Rec" description="记录集对象，为空则生成临时记录集对象" />
            </method>
            <method name="Scalar" description="执行一个返回单行单字段的数据">
                <parameter name="Sql" description="查询语句" />
                <parameter name="Rec" description="记录集对象，为空则生成临时记录集对象" />
            </method>
            <method name="DataSet" description="执行查询并返回指定页码的数据集">
                <parameter name="Sql" description="查询语句" />
                <parameter name="Size" description="设置每页显示的条数，为0则全部显示" />
                <parameter name="Page" description="设值页码（第几页）" />
                <parameter name="Rec" description="记录集对象，为空则生成临时记录集对象" />
            </method>
            <method name="CompactAccess" description="压缩指定Access数据库，压缩失败后返回失败原因">
                <parameter name="DbPath" description="被压缩的数据库，不指定则使用ConnStr中设置的数据库位置" />
            </method>
        </public>
        <script language="javascript">
            <![CDATA[var Nothing;
var ConnStr = "Driver=Microsoft Excel Driver (*.xls);ReadOnly=0;DBQ=Rimifon.xls";
function ProductName()
{
    return "風雲数据库操作组件";
}
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 "未指定数据库位置。";
        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>

i1efp6c · 发表于 2022-9-9 04:27:48

看到这帖子真是高兴！

nwk53kbd · 发表于 2023-2-5 15:26:34

66666666666

czoz5y · 发表于 2023-3-29 04:48:41

问问问企鹅哇哇哇哇哇

Anthonylam · 发表于 2023-6-4 01:43:55

需要很久了终于找到了

95nd4ba · 发表于 2023-8-19 10:42:06

还有什么好东西没

u7yxn9 · 发表于 2023-9-30 06:35:20

终于找到了，我擦

回复用户1 · 发表于 2024-1-29 23:52:34

天天源码社区。。。。

qzdhcgs · 发表于 2024-4-26 13:22:35

女生看了弄丢了卡萨诺的卡洛斯

zzhxxx · 发表于 2024-5-30 00:31:28

感谢楼主分享

账号		自动登录	找回密码
密码			立即注册

[ASP编程] Access中使用Create Procedure创建存储过程第1/2页

相关帖子