oracle宏,VBA宏调用Oracle PL/SQL Procedure

1.定义一个测试Procedure,这个Procedure用于给临时表插入一条记录

create sequence pt_debug_sequence

INCREMENT BY 1 -- 每次加几个

START WITH 1 -- 从1开始计数

NOMAXVALUE -- 不设置最大值

NOCYCLE -- 一直累加，不循环

CACHE 10;

--1. Create temp table.

create table pt_debug_tab (seq INTEGER,text Varchar2(300),datetag Varchar2(30));

--2. Create procedure.

create or replace

procedure pt_debug(inStr Varchar2) as

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

INSERT INTO pt_debug_tab VALUES(pt_debug_sequence.NEXTVAL,inStr,to_char(sysdate,\'hh:mi:ss\'));

COMMIT;

END;

2.VBA中的代码，参照：VBA - Hello World 样式，在Sheet中插入一个测试按钮，然后在按钮事件中加入下边的Macro代码

Private Sub CommandButton1_Click()

Dim rs As Object, com As Object, cn As Object

Set cn = CreateObject("ADODB.Connection")

cn.Open ("Provider=MSDAORA.1;Password=password;Persist Security Info=True;User ID=user;Data Source=instance_name")

\'Update the connectionstring

\'Set com = CreateObject("ADODB.Command")

Set rs = CreateObject("ADODB.Recordset")

Set com = CreateObject("ADODB.Command")

Set com.ActiveConnection = cn

com.CommandText = "BEGIN pt_debug(\'PTIAN:Sysdate is \'||sysdate); END;"

com.CommandType = 1

Set rs = com.Execute

MsgBox ("Done!")

End Sub

执行后,能够看到pt_debug_tab表插入了一条新的记录。