VB链接Oracle、Oracle基本操作、Oracle高级操作、VB调用存储过程、VB调用程序包等

(自学学习笔记)

按学习过程笔记如下：

一、oracle本地文件配置相关

二、SQL/PLUS链接数据库

三、创建表空间

四、创建表

五、添加数据：

六、添加列

七、查看表结构

八、删除数据表

九、ADO控件链接Oracle数据库成功

十、创建存储过程

十一、Oracle  中时间区间查询

十二、改变字段属性-字符长度

十三、修改修改记录

十四、根据已有表结构创建新表

十五、删除列

十六、联合查询

十七、把子查询用作表达式

十八、获得前10条记录

十九、记录中重量最大的所有记录

二十、存储过程创建

二十一、调用带参数的存储过程（即调用上面的存储过程）

二十二、调用无参数的存储过程

二十三、oracle封包，头，体

二十四、VB 调用程序包的过程

一、本地文件配置相关

安装Oracle 9i  选择管理员模式，不选运行模式

本地tnsnames.ora文件配置，服务名

D:\\oracle\\ora92\\network\\ADMIN

MYZSYY =

  (DESCRIPTION =

    (LOAD_BALANCE = yes)

    (FAILOVER = ON)

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.30.77.253)(PORT = 1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.30.77.253)(PORT = 1526))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = ORC2)

      (FAILOVER_MODE =

        (TYPE = SELECT)

        (METHOD = BASIC)

        (RETRIES = 20)

        (DELAY = 15)

      )

    )

  )

配置监听

# LISTENER.ORA Network Configuration File: D:\\oracle\\ora92\\NETWORK\\ADMIN\\listener.ora

# Generated by Oracle configuration tools.

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.30.77.253)(PORT = 1521))

    )

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.30.77.253)(PORT = 1526))

    )

  )

二、SQL/PLUS链接数据库

开始运行中输入sqlplus bzk22/zxcvbn@myzsyy

用户名：bzk22

密码：zxcvbn

本地服务名：myzsyy

本处不用服务器数据库名ORC2

三、创建表空间

create tablespace test datafile 'E:\\Database\\data\est_data.dbf' size 20M;

可以创建表，语句结束用分号  ；

四、创建表

Create table test_table (id number, name varchar2(20));

SQL> Create table FYHTXS (

  2  WWHTH  VARCHAR2(40),

  3  JYHTH  VARCHAR2(40),

  4  GFHTH  VARCHAR2(40),

  5  KHQC   VARCHAR2(80),

  6  CPMC   VARCHAR2(50),

  7  CHES   NUMBER,

  8  XTZL   NUMBER,

  9  SJZL   NUMBER,

 10  QZYD   VARCHAR2(50),

 11  YUNJ   NUMBER,

 12  GCLF   VARCHAR2(80),

 13  WAIWJ   NUMBER,

 14  CPLX   VARCHAR2(20),

 15  JLDATE DATE,

 16  YSFS   VARCHAR2(20),

 17  BEIZHU VARCHAR2(150));

Table created

五、添加数据：

SQL> insert into test_table values('10','张三','男');

1 row inserted

六、添加列

SQL> alter table test_table add (sex varchar(50));

Table altered

七、查看表结构

SQL> desc test_table;

Name Type         Nullable Default Comments 

---- ------------ -------- ------- -------- 

ID   NUMBER       Y                         

NAME VARCHAR2(20) Y                         

SEX  VARCHAR2(50) Y    

八、删除数据表

SQL> drop table test_table;

Table droped

九、ADO控件链接Oracle数据库成功

链接串如下

Provider=MSDAORA.1;User ID=bzk22;Data Source=myzsyy;Persist Security Info=False

该链接后表显示

汉字为乱码

需要在工程中引用D:\\oracle\\ora92\\bin\\OraOLEDB.dll

连接串中将Provider=MSDAORA.1;改为Provider=OraOLEDB.Oracle.1;方可正确显示汉字

新链接字符串为

Provider=OraOLEDB.Oracle.1;

Persist Security Info=False;User ID=bzk22;Password=zxcvbn;Data Source=myzsyy

十、创建存储过程

SQL> execute updateStatus;

PL/SQL procedure successfully completed

十一、Oracle  中时间区间查询

select * from test_table where BIRTHDAY between to_date('1980-1-1','yyyy-mm-dd') and to_date('1990-1-1','yyyy-mm-dd');

Adodc1.RecordSource = "select * from test_table " & "where BIRTHDAY>=to_date('" & CStr(DTPicker1.Value) & "','yyyy-mm-dd')" & " and BIRTHDAY<=to_date('" & CStr(DTPicker2.Value) & "','yyyy-mm-dd')"

十二、改变字段属性-字符长度

SQL> alter table JSK_GLK_SBJL modify BEIZHU varchar2(100);

Table altered

十三、修改修改记录

Update jsk_user set uname=’曹喜军’ where uname =’曹喜君’

十四、根据已有表结构创建新表

create table test_table2 as select * from test_table;

*可以替换为需要选择的字段

create table test_table2 as select * from test_table;

十五、删除列

SQL> alter table fyhtmx drop column WWHTJE;

Table altered

十六、联合查询

SELECT * FROM bzfy.FY_STOWBILLCARLIST @FQORA.US.ORACLE.COM where w1='FYGL402020120405263'

union

SELECT * FROM bzfy.FY_STOWBILLCARLIST @FQORA.US.ORACLE.COM where w1='FYGL402020120405259'

十七、把子查询用作表达式

SELECT w1,(select ZHUANGT from test_table3 where id=w2) as a1,w3,w4,w5,w6,w7,w8,w9 FROM bzfy.FY_STOWBILLCAR @FQORA.US.ORACLE.COM

把子查询用作表达式

使用表别名ta1 ta2 作为相等条件

SELECT W1,W2,W3,W4,W5,W6,(select sum(w11) from bzfy.FY_STOWBILLCARLIST @FQORA.US.ORACLE.COM ta1 where ta1.w1=ta2.w1) as a1,w9 FROM bzfy.FY_STOWBILLCAR @FQORA.US.ORACLE.COM ta2 ORDER BY W3 DESC,W1 DESC

十八、获得前10条记录

select * from (SELECT * FROM bzfy.FY_STOWBILLCAR @FQORA.US.ORACLE.COM order by w1) where rownum<=10

select * from (SELECT w7 FROM bzfy.FY_STOWBILLCARLIST @FQORA.US.ORACLE.COM order by w1) where rownum<=5

SELECT W1,W2,W3,W4,W5,W6,(select sum(w11) from bzfy.FY_STOWBILLCARLIST @FQORA.US.ORACLE.COM ta1 where ta1.w1=ta2.w1) as a1,

(select * from (SELECT w7 FROM bzfy.FY_STOWBILLCARLIST @FQORA.US.ORACLE.COM order by w1) where rownum<=1) as a2

FROM bzfy.FY_STOWBILLCAR @FQORA.US.ORACLE.COM ta2 

ORDER BY W3 DESC,W1 DESC

SELECT W1,W2,W3,W4,W5,W6,

(select sum(w11) from bzfy.FY_STOWBILLCARLIST @FQORA.US.ORACLE.COM ta1 where ta1.w1=ta2.w1) as a1,

(select w7 from bzfy.FY_STOWBILLCARLIST @FQORA.US.ORACLE.COM ta3 where w11 in(select max (w11) from bzfy.FY_STOWBILLCARLIST @FQORA.US.ORACLE.COM ta4)) as a2

FROM bzfy.FY_STOWBILLCAR @FQORA.US.ORACLE.COM ta2 

ORDER BY W3 DESC,W1 DESC

十九、记录中重量最大的所有记录

(SELECT * FROM bzfy.FY_STOWBILLCARLIST @FQORA.US.ORACLE.COM d

WHERE d.w11=(select max(w11) from bzfy.FY_STOWBILLCARLIST @FQORA.US.ORACLE.COM t where t.w1=d.w1 ) 

 AND d.rowid=(select max(rowid)from bzfy.FY_STOWBILLCARLIST @FQORA.US.ORACLE.COM t where t.w1=d.w1 )) order by w15 desc ,w1 desc

表2重量最大的记录所对应的w7作为表1的w3

SELECT x.w1,x.w3,y.w7 FROM bzfy.FY_STOWBILLCAR @FQORA.US.ORACLE.COM x,

(SELECT * FROM bzfy.FY_STOWBILLCARLIST @FQORA.US.ORACLE.COM d

WHERE d.w11=(select max(w11) from bzfy.FY_STOWBILLCARLIST @FQORA.US.ORACLE.COM t where t.w1=d.w1 ) 

 AND d.rowid=(select max(rowid)from bzfy.FY_STOWBILLCARLIST @FQORA.US.ORACLE.COM t where t.w1=d.w1 ) ) y

 where x.w1=y.w1(+) order by x.w3 desc, x.w1 desc

二十、存储过程创建

在Procedure下直接创建即可

create or replace procedure addPro(

   a_Pid  Product.Pid%type,

   a_Pname  Product.Pname%type,

   a_TypeId  Product.TypeId%type,

   a_TypeName  Product.TypeName%type,

   a_UpperId  Product.UpperId%type,

   a_Pstyle  Product.Pstyle%type,

   a_Punit  Product.Punit%type,

   a_Pprice  Product.Pprice%type,

   a_Plow  Product.Plow%type,

   a_Phigh  Product.Phigh%type,

   a_Cid  Product.Cid%type,

   a_Cname  Product.Cname%type)

as 

begin

insert into Product (Pid,Pname, TypeId,TypeName, UpperId,Pstyle,Punit,Pprice, Snum,Plow,Phigh,Cid,Cname)             values(a_Pid,a_Pname,a_TypeId,a_TypeName,a_UpperId,a_Pstyle,a_Punit,a_Pprice,0,a_Plow,a_Phigh,a_Cid,a_Cname);

   commit; 

end;

上面存储过程中有个“0”，容易影响编程，共12各参数，实际表中字段为13个

二十一、调用带参数的存储过程（即调用上面的存储过程）

Dim conn As New ADODB.Connection

Dim rs, rs1 As New ADODB.Recordset

Dim str As String

Private Sub Form_Load()

 Set conn = New ADODB.Connection

str="Provider=MSDAORA.1;Password=a123;UserID=kucun_admin;DataSource=kucun"

 conn.Open str

Text1.Enabled = False

Text2.Enabled = False

Text3.Enabled = False

Text4.Enabled = False

Text5.Enabled = False

Text6.Enabled = False

Text7.Enabled = False

Text8.Enabled = False

Text9.Enabled = False

Text10.Enabled = False

Text11.Enabled = False

Text12.Enabled = False

Combo1.Enabled = False

End Sub

----------------------------------------------------------------

Private Sub cmd_save_Click()

If Text1.Text = "" Or Text2.Text = "" Or Text6.Text = "" Or Text4.Text = "" Or Text7.Text = "" Or Text8.Text = "" Or Text9.Text = "" Or Text10.Text = "" Or Text11.Text = "" Or Text12.Text = "" Or Combo1 = "" Then

MsgBox "请输入完整信息！"

Else

s1 = Text1.Text

s2 = Text2.Text

s3 = Text3.Text

s4 = Text4.Text

s5 = Text5.Text

s6 = Text6.Text

s7 = Text7.Text

s8 = Text8.Text

s9 = Text9.Text

s10 = Text10.Text

s11 = Text11.Text

s12 = Text12.Text

s13 = Combo1.Text

str = "select * from Product where Pid='" + s1 + "'"

Set rs1 = conn.Execute(str)

If rs1.EOF Or rs1.BOF Then

str2 = "kucun_admin.addPro('" + s1 + "','" + s2 + "','" + s11 + "','" + s12 + "','" + s13 + "','" + s3 + "','" + s4 + "','" + s6 + "','" + s7 + "','" + s8 + "','" + s9 + "','" + s10 + "')"

Set rs2 = conn.Execute(str2)      ‘ {kucun_admin为用户名}

MsgBox "添加成功！"

Adodc1.Refresh

Unload Product

Else

MsgBox "已有此记录，请重新输入！"

End If

End If

End Sub

二十二、调用无参数的存储过程

Dim conn As ADODB.Connection

Dim rs, rs1, rs2 As ADODB.Recordset

Dim str As String

        Set conn = New ADODB.Connection

        str = "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=bzk22;Password=zxcvbn;Data Source=myzsyy"

        conn.Open str

str1 = "bzk22.updateStatus"

Set rs = conn.Execute(str1)

二十三、oracle封包，头，体

头（Procedure）

create or replace package PACK_Wink is

procedure addPro_TEST(a_Pid  Product.Pid%type,

   a_Pname  Product.Pname%type,

   a_TypeId  Product.TypeId%type,

   a_TypeName  Product.TypeName%type,

   a_UpperId  Product.UpperId%type,

   a_Pstyle  Product.Pstyle%type,

   a_Punit  Product.Punit%type,

   a_Pprice  Product.Pprice%type,

   a_Plow  Product.Plow%type,

   a_Phigh  Product.Phigh%type,

   a_Cid  Product.Cid%type,

   a_Cname  Product.Cname%type);

end PACK_Wink;

体（Package bodies）

create or replace package body PACK_Wink is

--{*******************************************************}

--{                                                       }

--{                 项目名称：包测试                      }

--{                                                       }

--{           版权所有 (c) 2011,2012 中实运业             }

--{                                                       }

--{*******************************************************}

--项目:中实运业成本核算管理信息系统

--模块:添加

--描述:

--版本:1.0

--日期:2012-6-4

--作者:吴长平

--更新:

--*******************************************************}

----------------------------------------------------------------------------------

   -------------------------------------

   --测试 2010-9-15

   -------------------------------------

procedure addPro_TEST(

   a_Pid  Product.Pid%type,

   a_Pname  Product.Pname%type,

   a_TypeId  Product.TypeId%type,

   a_TypeName  Product.TypeName%type,

   a_UpperId  Product.UpperId%type,

   a_Pstyle  Product.Pstyle%type,

   a_Punit  Product.Punit%type,

   a_Pprice  Product.Pprice%type,

   a_Plow  Product.Plow%type,

   a_Phigh  Product.Phigh%type,

   a_Cid  Product.Cid%type,

   a_Cname  Product.Cname%type)

as 

begin

insert into Product (Pid,Pname, TypeId,TypeName, UpperId,Pstyle,Punit,Pprice, Snum,Plow,Phigh,Cid,Cname)             values(a_Pid,a_Pname,a_TypeId,a_TypeName,a_UpperId,a_Pstyle,a_Punit,a_Pprice,0,a_Plow,a_Phigh,a_Cid,a_Cname);

   commit; 

end;

end PACK_Wink;

二十四、VB 调用程序包的过程

str2 = "bzk22.PACK_Wink.addPro_TEST('" + s1 + "','" + s1 + "','" + s1 + "','" + s1 + "','" + s1 + "','" + s1 + "','" + s1 + "','" + s1 + "','" + s1 + "','" + s1 + "','" + s1 + "','" + s1 + "')"