VB链接Oracle、Oracle基本操作、Oracle高级操作、VB调用存储过程、VB调用程序包等(自学学习笔记).doc

VB链接Oracle、Oracle基本操作、Oracle高级操作、VB调用存储过程、VB调用程序包等(自学学习笔记).doc 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\test_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='FYGL' union SELECT * FROM bzfy.FY_STOWBILLCARLIST @FQORA.US.ORACLE.COM where w1='FYGL' 十七、把子查询用作表达式 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=kuc un" 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 + "')" Set rs2 = conn.Execute(str2)