oracle操作实例

记录oracle操作实例,不过查询的较多。由于同时涉及多种数据库,会混入其它库的记录。

搭建oracle服务

使用docker搭建。
使用navicat连接,成功后,“其它”->“表空间”->“新建表空间”。
“常规”:名称:fee.ora,大小10,单位G,路径:/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/ (注意后面的/)。自动扩展:ON。
“存储”:文件类型BIGFILE,大小:10,单位G,扩展区管理LOCAL。
“保存”。

用户->用户,“新建用户”,输入用户名称,密码,“保存”。

(似乎要创建用户才能看到数据库)

连接工具

(注:本节待移动)
navicat,该工具可连接多种数据库。

可在不同数据库中迁移,“工具”->“数据传输”。实际发现,此方式迁移的表的字段类型,可能会与原表不一致,如把char改为text,会出错。
导出:可导出不同类型文件,如sql文件,txt文件,xml、json,等等。可选要导出的列。

1、原数据库导出为txt,再在新数据库导入。在导出过程,默认字段为text,不一定符合要求。
2、原数据库导出为sql,再在新数据库导入。sql文件格式不一定通用,要修改,再导入。

问题

创建的用户没有权限,提示user test lacks create session privilege logon denied。解决:

1
2
3
sqlplus /nolog
onn /as sysdba
grant create session to test

create table xxx ()缺失右括号。

1
alter user FEE default tablespace FEE quota 500 M on FEE;

类型对应关系

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
SQL Server 数据类型	Oracle 数据类型
bigint NUMBER(19,0)
binary(1-2000) RAW(1-2000)
binary(2001-8000) BLOB
bit NUMBER(1)
char(1-2000) CHAR(1-2000)
char(2001-4000) VARCHAR2(2001-4000)
char(4001-8000) CLOB
date DATE
datetime DATE
datetime2(0-7) TIMESTAMP(7)(对于 Oracle 9 和 Oracle 10);VARCHAR(27)(对于 Oracle 8)
datetimeoffset(0-7) TIMESTAMP(7) WITH TIME ZONE(对于 Oracle 9 和 Oracle 10);VARCHAR(34)(对于 Oracle 8)
decimal(1-38, 0-38) NUMBER(1-38, 0-38)
float(53) FLOAT
float FLOAT
地理 BLOB
geometry BLOB
hierarchyid BLOB
图像 BLOB
int NUMBER(10,0)
money NUMBER(19,4)
nchar(1-1000) CHAR(1-1000)
nchar(1001-4000) NCLOB
ntext NCLOB
numeric(1-38, 0-38) NUMBER(1-38, 0-38)
nvarchar(1-1000) VARCHAR2(1-2000)
nvarchar(1001-4000) NCLOB
nvarchar(max) NCLOB
real real
smalldatetime DATE
int NUMBER(5,0)
smallmoney NUMBER(10,4)
sql_variant N/A
sysname VARCHAR2(128)
text CLOB
time(0-7) VARCHAR(16)
timestamp RAW(8)
tinyint NUMBER(3,0)
uniqueidentifier CHAR(38)
varbinary(1-2000) RAW(1-2000)
varbinary(2001-8000) BLOB
varchar(1-4000) VARCHAR2(1-4000)
varchar(4001-8000) CLOB
varbinary(max) BLOB
varchar(max) CLOB
xml NCLOB

实例

查询

常用查询

模糊查找:

1
select * from MYDB.STATION where name like '%出口%'

查找交易表中指定的ID,日期和类型,限制5条:

1
2
3
4
5
select * from MYDB.TRADE_INFO 
where ID='001250'
and to_char(trans_time, 'yyyy-MM-dd HH24:MI:SS') like '2020-08-08 %'
and TYPE = 1
and rownum < 5

注:日期转换要用to_char(trans_time, 'yyyy-MM-dd HH24:MI:SS')这种形式。

交易时间最早:

1
2
升序
select * from MYDB.TRADE_INFO g where rownum < 10 order by g.TRANS_TIME

交易时间最新:

1
2
降序
select * from MYDB.TRADE_INFO g where rownum < 10 order by g.TRANS_TIME desc

注:这是错误的,先取10条,再排序,不符合逻辑。但未找到合适的。特别在几千万条的记录中找,耗时。

多表查询:

1
2
3
4
5
select a.id, a.id_hex, a.name from TableA A, TableB B where a.name =  b.name and A.id_hex='510156'

select a.id, a.id_hex, a.name from TableA A, TableB B where a.name like "%" || b.name || "%" and A.id_hex='510156'
注:A、B表,各有各的字段,但包含共同的name字段,以此为条件查两表,另一条件为指定id_hex。
第二语句为a的name有多个,但b的name只有一个,属于包含关系。第一语句为相等

查询去重
如果结果有多条,在语句后续添加group by A.id_hex

导出

导出查询,将结果导出sql。
按条件查询,得到结果,再点击“导出结果”(在“运行”附近),可保存得到的结果。此法适用同一表中有不同版本的记录(即同一条记录存在多个版本,重复)。如要保存所有记录,直接导出即可。

直接右键某个表,“导出向导…”,可得不同格式文件。“转储SQL文件”,可得含结构和数据,或仅含结构。

==临时

前置条件:
安装mingw(取其gcc及库,因为要用cgo编译),安装git(取其bash)。安装oci。

安装,下载OCI:
https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html
版本 SDK 版本、Basic 版本。压缩包不同,但内含目录一致,解压到当前目录即可,拷贝到指定目录,示例:D:\oracle\instantclient

PKG_CONFIG_PATH环境变量 (失败)
D:\mingw64\lib\pkg-config

执行echo $PKG_CONFIG_PATH查看目录,映射到 E:\Program Files\Git\mingw64\lib 下,不存在pkgconfig,创建之。


获取oci8.pc。
执行:

1
2
3
4
5
6
7
go get github.com/wendal/go-oci8
提示:
# pkg-config --cflags -- oci8
Package oci8 was not found in the pkg-config search path.
Perhaps you should add the directory containing `oci8.pc'
to the PKG_CONFIG_PATH environment variable
No package 'oci8' found

无须理会错误,此处是下载源码,拿到oci8.pc文件。在下载包的windows目录,将pkg-config.exe拷贝到git的bin目录,oci8.pc拷贝到E:\Program Files\Git\mingw64\lib\pkgconfig目录。
修改实际目录。

再次执行:

1
2
3
4
5
go get github.com/wendal/go-oci8
提示:
# github.com/wendal/go-oci8
D:/mingw64/bin/../lib/gcc/x86_64-w64-mingw32/8.1.0/../../../../x86_64-w64-mingw32/bin/ld.exe: cannot find -lclntsh
collect2.exe: error: ld returned 1 exit status

一说:把oci8.pc文件的lclntsh改为oci,修改后,再执行,通过。得到pkg\windows_amd64\github.com\wendal\go-oci8.a目录。

由作者说明得知,wendal是从mattn仓库fork得到的,也下载:

1
go get github.com/mattn/go-oci8

对比两者源码和生成的.a文件,mattn的文件也多,库也比较大。但对比使用者而言无区别。

附:

1
2
3
4
5
6
7
8
9
#prefix=D:/oracle/instantclient
includedir=D:/oracle/instantclient/sdk/include
libdir=D:/oracle/instantclient/sdk/lib/msvc

Name: oci8
Description: Oracle Instant Client
Version: 19.8
Cflags: -I${includedir}
Libs: -L${libdir} -loci

注:使用全路径,不使用prefix(因为git安装的目录带有空格),将库修改为oci。

后续
编写测试代码。go build dbmain.go,出错:

1
2
3
4
# command-line-arguments
D:\go\pkg\tool\windows_amd64\link.exe: running gcc failed: exit status 1
D:/mingw64/bin/../lib/gcc/x86_64-w64-mingw32/8.1.0/../../../../x86_64-w64-mingw32/bin/ld.exe: cannot find -loci
collect2.exe: error: ld returned 1 exit status

猜想:官方oci是.lib格式,gcc不认。而golang的驱动得到.a但没有包括oci里面的函数。将得到的go-oci8.a改名为liboci.a,再次go build,会提示未定义的函数,用nm查之,函数前为U

1
2
$ pkg-config --cflags -- oci8
-ID:/oracle/instantclient/sdk/include

在:\oracle\instantclient目录找oci.dll,放到临时目录。从dll生成.a。

1
2
3
4
$ gendef.exe oci.dll  # 注:从dll生产def(下一步要用到)
* [oci.dll] Found PE image

$ dlltool.exe -D oci.dll -d oci.def -l liboci.a # 从dll和def生成.a文件。

生成的文件为liboci.a,可用nm查函数。将其放到mingw的lib目录(这样就不用再额外设置路径),本文为D:\mingw64\lib。

疑惑1:
似乎golang生成的go-oci8.a没什么用,可能内部链接了里面的函数,但最终的oci函数,还是从官方的oci库中获取,如OCIStmtPrepare。
疑惑2:
go get github.com/mattn/go-oci8处,会使用pc文件指定的参数,但似乎只针对头文件,也没有链接,具体未详细研究(因为.lib和.a格式已然不同,不通用,链接不上的,没报错,应该没链接)。

参考资料:
https://www.cnblogs.com/good-temper/p/3791874.html
https://blog.csdn.net/yh_coco/article/details/78068610

作者笔记:

1
2
3
4
5
6
7
8
9
10
11
12
//假设的GOPATH指向C:\gohome
0. 执行 go get github.com/wendal/go-oci8 ,然后肯定是报错了,没关系,代码会下载下来.
1. 首先,你需要安装mingw到C:\mingw
2. 然后,到Oracle官网,下载OCI及其SDK,解压到instantclient_11_2 -- 当前最新版
3. 从我的go-oci8库的windows文件夹,拷贝pkg-config.exe到C:\mingw\bin\,拷贝oci8.pc到C:\mingw\lib\pkg-config\
4. 设置环境变量 PATH ,值为 原有PATH;C:\instantclient_11_2;C:\mingw\bin;
5. 设置环境变量 PKG_CONFIG_PATH,值为 C:\mingw\lib\pkg-config
6. 接下来,就最重要的,就是再执行一次,这次应该能成功的: go get github.com/wendal/go-oci8
7. 测试一下:
cd %GOPATH%/src/github.com/wendal/go-oci8/example
go run oracle.go
#提醒一句, oracle.go里面的写的密码是system/123456, 实例名XE

Win7 64bit 连接数据库

1、下载 64 版本的 OCI
https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html
版本 SDK 版本(有静态库,用于开发)、Basic 版本(基础包)、ODBC 版本(有用于安装的exe)。压缩包不同,但内含目录一致,解压到当前目录即可。本文用最新版本instantclient_19_8。将压缩后的目录拷贝到指定目录,示例:D:\oracle\instantclient,下用 ORACLE_HOME 代替。

2、设置环境变量
ORACLE_HOME D:\oracle\instantclient
NLS_LANG SIMPLIFIED CHINESE_CHINA.ZHS16GBK
TNS_ADMIN D:\oracle\instantclient
Path D:\oracle\instantclient

修改编辑ORACLE_HOME\tnsnames.ora,如果没有,从别处拷贝一份。格式类似:

1
2
3
4
5
6
7
8
oracle_inner =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.234.100)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = helowin)
)
)

3、安装
进入ORACLE_HOME目录,执行odbc_install.exe,最好使用管理员打开cmd,切换该目录,再执行。

4、配置
打开控制面板,找到:管理工具ODBC数据源或者 ODBC数据源(32 位),打开之。
添加用户DSN: 点击添加按钮,找到对应版本的ODBC,本文为Oracle in instantclient,输入信息,测试连接。

5、除外
如果ODBC数据源没有找到版本,打开目录 C:\Windows\SysWOW64。双击运行odbcad32.exe。
点击添加按钮,找到对应版本的ODBC。点击完成,添加数据源、服务名、用户名(如oracle1、oracle2、my),点击Test Connection,输入密码。成功后,点击“OK”保存。

注0:ODBC数据源界面看到的名称,可认为是数据源,在代码中可直接使用,如上述的oracle1等。
注1:不同系统,不同版本,结果可能不同。
注2:本文安装目录为instantclient,是因为要尝试多种版本,每次改环境变量麻烦。在选择源时只显示Oracle in instantclient,如目录带版本号,则会显示出来。