oracle学习

oracle学习笔记

知识点

1. oracle数据库的端口是1521，mysql是3306，redis是6379
2. 在oracle安装中，出现内存检查不通过，这一步可以忽略，继续安装会出现注册表注册问题，继续忽略。最终可以安装成功，这些错误也没有看到有什么影响。
3. oracle 中默认有三个用户 scott/tiger system/managerss sys/change_on_install 其中scott是默认普通用户，system是系统管理员dba用户，sys用户是系统管理员dba具有启停数据库的权限。
4. 新建用户 create user aa identitied by aa 创建aa用户密码是aa
5. oracle 导出数据，可以直接使用plsql工具进行导数据，可以导出sql文件，dmp文件，但是导出dmp文件时需要本地安装oracle，利用本地oracle的imp.exe和 exp.exe进行导出，如果只是导出一个表的部分数据，可以直接选中表的内容然后导出sql，
   exp devtest/devtest@gcsdb file=devtest20180705.dmp owner=devtest
   导出devtest用户下的所有表，用户名/密码@数据库实例 文件路径 表的用户
   imp posp/posp@gcsdb file=/home/oracle/posp-20181116.dmp full=y ignore=y
   导入dmp文件到数据库实例中。
6. 在oracle中没有自增字段，可以通过sequence来代替，可以设置最小值、最大值、初始值来产生自增序列；
7. nvl(sum(a.FEE_CREDIT),0)如果字段内容为空，把0赋给它；
8. dual是orecal内部的一个虚表，表里只有一条记录，可以用来查询系统时间 select date from dual；
9. oracle中所有的表名和字段名都默认大写，在表名和字段都是大写时，sql语句中的表名和字段可以大写，可以小写，小写会自动转为大写，如果在建表时为小写，那么sql语句中直接小写就会出错，需要在表名和字段名上加双引号，这样也就变得麻烦了，所以为了方便，建表的时候就直接大写。
10. insert into test1 （name，age，address） select name，age，address from test2 where name = ‘a’ select语句和insert语句的嵌套，把符合条件的查询结果插入指定表中，不论查询结果是一条还是多条都可以。
11. bbb=‘x’
    ccc=20

print '----------------------------------------------------------------------'

sql = """insert into test1(name,age,address)  select '%s','%s',t.address from test2 t where t.address='A'""" \% (bbb, ccc)
cursor.execute(sql)
conn.commit()
select语句中有占位符，一般情况下我们用占位符来代替sql语句中的变量，而此时占位符出现在字段名的位置上，在上面的语句中我理解为占位符以变量的形式先代替表test1中的对应字段，然后，最后面的变量代替占位符。

12. “select mi from TB where mcht_no=’%s’” %(MCHT_CD) 当sql语句中有变量时用占位符表示，；

13. 建表 create table mytest(name varchar2(30),passwd varchar2(30))
    create or replace procedure lg_pro1 is
    begin
    insert into mytest values(‘刘权’,‘123’);
    end;
    / 加回车执行

    调用存储存储过程，exec lg_pro1；

14. merge 命令 MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression] and […]…)
    WHEN MATCHED THEN
    [UPDATE sql]
    WHEN NOT MATCHED THEN
    [INSERT sql]
    如果两个表的某些字段匹配，将进行update操作，否则进行insert插入操作，操作目标表A，
    可参考：https://blog.csdn.net/jeryjeryjery/article/details/70047022

15. Python连接Oracle时报错"64-bit Oracle Client library cannot be loaded"问题
    无法载入64位Oracle客户端库，首先考虑本地是否有64位Oracle客户端，如果没有，需要先下载，然后把客户端文件夹里的dll文件复制到Python的安装路径下就可以了。（在其他博客中看到把64位Oracle客户端的路径加入环境变量也可以实现，但是按照这一步骤去做并没有成功）

16. 之前在oracle中遇sysdate 与北京时间相差几个小时，后来把时区设置为香港，问题解决。

17. oracle中每一句sql的执行都有一个返回码，通常执行正常返回‘0000’，就如同web中返回‘200’一样，如果查询结果为空，可能返回‘100’，‘1403’，‘-1405’，但有时查询数据为空也会返回‘0000’。

18. select to_char(sysdate - interval ‘10’ minute,‘YYYYMMDDHH24MISS’) from dual
    获取当前时间之前的十分钟的时间，然后格式化输出， 减号改为加号表示十分钟以后的时间，当然这里也可以是对 month、year、day、hour、second的操作，使用很简单，十分有用，不需要考虑闰年、时间加减中的进位问题。

19. sequence是oracle中用来记录id的特殊对象，如同MySQL中的自增id，如果不用sequence，可以在一个单独的表中记id的当前值，每次查询后对它进行加1，数据库中大部分字段都是varchar类型，所以要对id进行转换，to_number,然后进行加一，然后再变为字符类型to_char, update cmp_sys_id set id_value = (select to_char(to_number(id_value)+1) from cmp_sys_id where id_key = ‘dep_id’) where id_key = ‘dep_id’

20. oracle 中如果中文为乱码，需要把oracle的字符集改为gbk或者utf-8

21. oracle 中select * from (select s.,t. from cmp_white_list s,cmp_canteen_info t)程序报错，ora-00918 column ambiguosly 起初以为select * 不能嵌套，因为执行select s.,t. from cmp_white_list s,cmp_canteen_info t 成功，两个表中有相同字段名的字段，没嵌套之前分别select的每个表的各个字段，所以即使有重名现象，也没有报错，但是嵌套后，外层select无法判断 重名的字段来自哪个表，所以报错了，解决方法，给重名字段重命名 select sss as newname，问题解决。

22. select 1 from table 结果为原记录数行数的1，select ‘’ from table ，
    select t.supdpt_id,s.dpt_name,t.dpt_name,t.dpt_id
    from cmp_department t,cmp_department s
    where 1=1 and t.supdpt_id = s.dpt_id %s%s%s%s)union (select ‘’,’’,dpt_name,dpt_id from cmp_department t where supdpt_id is null %s%s%s%s)"""
    %(pidsql,namesql,idsql,cmpidsql,pidsql,namesql,idsql,cmpidsql)
    查询部门的名字、和上级部门的名字，由于原表中只有上级部门的id，所以要做自己关联自己，关联条件是t的上级部门id等于s表的部门id，由于最高部门没有上级部门，所以就关联不到，但是我们还是希望显示出来，这样就可以单独对最高部门进行处理，再和其他部门进行 union all ，这样就全部显示了，但是最高部门由于没有上级就有一个字段不存在，和其他部门进行合并时要求表结构一致，这样可以有select ‘’ 来替代。

23. merge 经常会有这样的场景，判断数据库中这个记录是否存在，不存在就插入，存在就更新。
    MERGE INTO TBL_MCHNT_SSID T1
    USING (SELECT ‘%s’ as id from dual) T2 on (T1.id=T2.id)
    WHEN MATCHED THEN UPDATE SET T1.session_id=?,T1.session_date = to_char(sysdate,‘YYYYMMDDHH24MISS’)
    WHEN NOT MATCHED THEN INSERT (T1.id,T1.session_id,T1.session_date) VALUES (?,?,to_char(sysdate,‘YYYYMMDDHH24MISS’))"""
    %(VarMap[‘username_oracle’])