请选择 进入手机版 | 继续访问电脑版

Excel VBa 连接Oracle数据库

[复制链接]
菜鸡 发表于 2021-1-1 18:29:15 | 显示全部楼层 |阅读模式 打印 上一主题 下一主题
需求:使用Excel做一个简单的数据库毗连工具(类似于java中的AWT和SWING)。


  • 可以查询数据库的表
  • 查询各个表内的数据
  • 插入/修改各个表的数据
步调:

  • 先创建一个UserForm作为主界面,并放置按钮,如下图所示。

    然后双击中间的“毗连数据库”按钮,举行代码编辑。
  1. Private Sub CommandButton1_Click()    Dim strConn As String      Dim dbConn As Object    Dim resSet As Object        Dim db_sid, db_user, db_pass As String        db_sid = "edu-server.yds.yd:1521/orcl.yds.yd"    db_user = "root"    db_pass = "123456"        Set dbConn = CreateObject("ADODB.Connection")    Set resSet = CreateObject("ADODB.Recordset")        strConn = "Provider=OraOLEDB.Oracle.1; user id=" & db_user & "; password=" & db_pass & "; data source = " & db_sid & "; Persist Security Info=True"        //清空sheet1       Sheets("Sheet1").Range("1:65536").ClearContents     dbConn.Open strConn        //查询当前数据库所有表名    Set resSet = dbConn.Execute("select table_name from user_tables")        //将表名放入sheet1中    For j = 0 To resSet.Fields.Count - 1      Cells(1, j + 1) = resSet.Fields(j).Name    Next        //将Sheet1中的A2:A20(A20写成了固定的,也可以写成变量)放到主界面中,下面是主界面    UserForm2.ListBox1.RowSource = "Sheet1!A2:A20"        Range("A2").CopyFromRecordset resSet            //跳转到主界面    UserForm2.Show 0    //隐藏当前界面    UserForm1.Hide        dbConn.Close    End Sub
复制代码

  • 使用UserForm创建一个主界面,并放入一些控件,如下图。

    左侧空缺部门是一个ListBox,点击步调1界面的“毗连数据库”按钮后,跳转到当前步调的界面,并将表显着示到左侧的ListBox中,效果图如下。

    双击“查询”按钮,进入到查询功能编写。
  1. Private Sub CommandButton1_Click()    Dim strConn As String    Dim strSql As String    Dim iCount As Integer    Dim whm As Worksheet        Dim dbConn As Object    Dim resSet As Object        Dim db_sid, db_user, db_pass As String        db_sid = "edu-server.yds.yd:1521/orcl.yds.yd"    db_user = "root"    db_pass = "123456"        Set dbConn = CreateObject("ADODB.Connection")    Set resSet = CreateObject("ADODB.Recordset")        strConn = "Provider=OraOLEDB.Oracle.1; user id=" & db_user & "; password=" & db_pass & "; data source = " & db_sid & "; Persist Security Info=True"                //根据鼠标选中的表名拼接sql    strSql = "select * from " & ListBox1.Value          //判定sheet2是否存在      If ActiveSheet.Name = "Sheet2" Then               //存在就清空sheet2         Sheets("Sheet2").Range("1:65536").ClearContents      Else         //不存在就创建sheet2         Worksheets.Add.Name = "Sheet2"      End If          dbConn.Open strConn        //执行sql    Set resSet = dbConn.Execute(strSql)        //将查询到的数据放入sheet2中    For j = 0 To resSet.Fields.Count - 1      Cells(1, j + 1) = resSet.Fields(j).Name    Next        Range("A2").CopyFromRecordset resSet        dbConn.CloseEnd Sub
复制代码
效果图如下。

3.双击“更新”按钮,进入更新功能代码编写。
这里我的想法是将插入和修改放到了一起,点击“更新”按钮会后,先清空当前表
,再将sheet2中的数据全部插入到表中,这样就将插入和修改整合到了一起,但是效率可能会低.
  1. Private Sub CommandButton2_Click()    Dim strConn1 As String    Dim truncateSql As String    Dim insertSql As String        Dim dbConn1 As Object    Dim resSet1 As Object        Dim maxRow As Integer    Dim maxColumn As Integer        Dim db_sid1, db_user1, db_pass1 As String        db_sid1 = "edu-server.yds.yd:1521/orcl.yds.yd"    db_user1 = "root"    db_pass1 = "123456"        Set dbConn1 = CreateObject("ADODB.Connection")    Set resSet1 = CreateObject("ADODB.Recordset")        //获取最大行数    maxRow = ActiveSheet.[A65536].End(xlUp).Row    //获取最大列数    maxColumn = ActiveSheet.[IV1].End(xlToLeft).Column        strConn1 = "Provider=OraOLEDB.Oracle.1; user id=" & db_user1 & "; password=" & db_pass1 & "; data source = " & db_sid1 & "; Persist Security Info=True"        //拼接清空表sql    truncateSql = "truncate table " & ListBox1.Value    //拼接插入sql    insertSql = "insert into " & ListBox1.Value & " values( "           dbConn1.Open strConn1        Set resSet1 = dbConn1.Execute(truncateSql)        //循环,第一行是表字段名,从第二行开始,j=2    For j = 2 To maxRow                //取每一行所有有效列的值,拼到插入语句的sql中        For i = 1 To maxColumn            If i = maxColumn Then                insertSql = insertSql & "'" & Cells(j, i) & "')"            Else                insertSql = insertSql & "'" & Cells(j, i) & "',"            End If        Next                //拼完插入语句的sql,就执行        Set resSet1 = dbConn1.Execute(insertSql)                //将sql重置成最初状态,以便下一行的拼接        insertSql = "insert into " & ListBox1.Value & " values( "    Next        //增删改需要提交事务,这里我不清楚需不需要,写上肯定没错    Set resSet1 = dbConn1.Execute("commit")        //弹出提示框,提示更新乐成!    MsgBox prompt:="     更新乐成!     "        dbConn1.CloseEnd Sub
复制代码
效果图如下。


  • 最后一步,在sheet1的ThisWorkBook中写入显示步调1的毗连界面代码。

至此,简单数据库毗连功能都已完成,有需要的可以添加,我这个每次执行sql都需要毗连数据库,关闭毗连,没有优化,可以自行修改。
第一次使用的话需要将sheet2删除!不删可能会报错。
例子放入在了百度网盘,有需要的自取。
链接: https://pan.baidu.com/s/1tCix95JM17FMttFO4sTGMg 提取码: adxf 复制这段内容后打开百度网盘手机App,操纵更方便哦
感谢阅读!

来源:https://blog.csdn.net/qq_42665745/article/details/112002289
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则


专注素材教程免费分享
全国免费热线电话

18768367769

周一至周日9:00-23:00

反馈建议

27428564@qq.com 在线QQ咨询

扫描二维码关注我们

Powered by Discuz! X3.4© 2001-2013 Comsenz Inc.( 蜀ICP备2021001884号-1 )