Oracle小知识点

胖子k
226 阅读6分钟
1.count(字段名) ，如果字段对应的值为null，不计入在内

2.between ...and...  包括上下2个边界点

3.默认用户密码

  • sys/change_on_install sysdba 或 sysoper 不能以 normal 登录，可作为默认的系统管理员
  • system/manager sysdba 或 normal 不能以 sysoper 登录，可作为默认的系统管理员
  • sysman/oem_temp sysman 为 oms 的用户名
  • scott/tiger normal 普通用户
  • aqadm /aqadm sysdba 或 normal 高级队列管理员
  • dbsnmp/dbsnmp sysdba 或 normal 复制管理员
4.角色权限
  • 查看用户角色select*from user_role_privs;
  • 授予角色或权限 grant dba/resource/connect to 用户名;
  • 授予调试存储过程权限grant debug any procedure, debug connect session to 用户名;
  • 剥离角色或权限revoke create any table from 用户名;
  • 表空间权限alter user 用户名 quota unlimited on 表空间名;
  • 解锁用户 alter user scott account unlock;
5.问题： 迁移数据库时，如果表中存在LOB类型字段（BLOB、CLOB等），IMP时失败，提示表空间不存在。
原因： 是LOB类型的数据全部存储在表空间中，表中只存放指针，即使在建表时没指定表空间，数据也全部存入该数据库默认表空间中
  • 1)exp和imp是客户端工具程序，它们既可以在客户端使用，也可以在服务端使用。exp命令: exp username/pwd@ip:port/实例 owner=用户名 file=导出路径
  • 2)expdp和impdp是服务端的工具程序，他们只能在oracle服务端使用，不能在客户端使用。
  • 3)imp只适用于exp导出的文件，不适用于expdp导出文件；impdp只适用于expdp导出的文件，而不适用于exp导出文件。
  • 4)对于10g以上的服务器，使用exp通常不能导出0行数据的空表，而此时必须使用expdp导出
6.v_user_account_info link_transfer_account_info%rowtype; //定义“类似对象”

7.订单号可用日期+流水号组成, 比如使用数据库函数

create or replace function FN_GENERATE_QR_ORDER_WATER  --生成QR订单流水号
return varchar2
is       
water_no   varchar2(14);       
v_sysdate  varchar2(8);
begin       
   select to_char(sysdate,'yyyyMMdd') into v_sysdate from dual;       
   water_no := v_sysdate || lpad(seq_qr_order_water.nextval, 6, '0');       
   return water_no;
end FN_GENERATE_QR_ORDER_WATER;
8.驱动包包的使用
  • classes12.jar - for Java 1.2 and 1.3
  • ojdbc14.jar - for Java 1.4 and 1.5
  • ojdbc5.jar - for Java 1.5
  • ojdbc6.jar - for Java 1.6
9.建议使用oracle.jdbc.OracleDriver类，不建议使用oracle.jdbc.driver.OracleDriver

10.row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的

rank() OVER (PARTITION BY COL1 ORDER BY COL2)用法和上面类似
row_number()和rank()有个区别：row_number()不会出现相同排序，一直递增；就算两条记录参与排序的字段的数值一样，排序也是递增

11.trim 函数:去除数据中的空格。 rtrim代表去除右边的空格，ltrim代表去除左边的空格
substr是按字符来计算，一个字母或汉字都按一个字符计算如：
substr('智能ABC',2,2)='能A'
如果想要按字节来计算则可以采用substrb函数，用法一样
substrb('智能ABC',3,4)='能AB'
power(m,n)=m^n

12.sys_refcursor相比cursor可以作为存储过程的返回参数

13.存储过程加for update可以锁表进而锁存储过程，可防止多个会话执行存储过程产生脏数据

14.rownum 是对结果集加的一个伪列，即先查到结果集之后再加上去的一个列(注意：先要有结果集)。

rownum 的值是oracle 顺序分配的从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推。查询表中第n条记录，使用rownum=n 作为条件时

select rownum,id,name from student where rownum =n //如果n>1会没有结果集返回。 
因为rownum 是一个总是从1 开始的伪列，是oracle 从数据文件或缓冲区中读取数据的顺序。它取得第一条记录则rownum 值为1，第二条为2，因为从缓冲区或数据文件中得到的第一条记录的rownum 为1，会被删除，那么下一条记录的rownum变为1，接着取
这条，它的rownum 还是1，又被删除，依次类推，便没有了数据。
可以使用子查询解决：
select*from (select rownum no ,id,name from student) where no=2;

说明：子查询中的rownum 必须要有别名，否则还是查不出记录。因为rownum 不是某个表的列，如果不起别名，就无法知道rownum 是子查询的列还是主查询的列。

个人觉得，使用子查询主要是使rownum像表中的真正列一样 出现在from后面
ps:rownum总从第一行开始读取，满足条件，则再读rownum=2,第一行不满足就把第一行删掉把第二行的rownum改为1
类似:select t.*,rownum rn from 表 t where rn.... 这种同级情况识别不了别名rn，需要在更外一层才能识别别名rn
select t.*,rownum rn from 表 t where rownum... 这种在同级别下可识别

rownum<n的时候与常规思维预期一致

rowid也是伪列，但是它是物理地址，不会动态变化，可以认为是记录的唯一标识

15.查看当前数据库是否处于归档模式

select name, log_mode from v$database;

log_mode的值为 NOARCHIVELOG 表示数据库处于非归档模式

log_mode的值为 ARCHIVELOG 表示数据库处于归档模式
数据库使用归档方式运行时可以进行灾难性恢复。
归档日志模式和非归档日志模式的区别
非归档模式只能做冷备份,并且恢复时只能做完全备份.最近一次完全备份到系统出错期间的数据不能恢复.
归档模式可以做热备份,并且可以做增量备份,可以做部分恢复.
16.PRAGMA AUTONOMOUS_TRANSACTION 自治事务
用于存储过程，不依赖，不干涉调用它的过程，自己事务的管理和主程序无关

eg:常用于工具类存储过程

17.select..into 如果为null会报异常

18.grant select on S to A

其中grant是授权，select是查询权限，S是被授权的表或视图名称，A是被授权的用户

19.start with....connect by...专门用于处理树形结构查询

20.on中的条件是在连接前进行过滤，where中的条件是连接后对临时表过滤

21.full join(full outer join)全连接，即左连接，右连接的并集

22.with  临时表名a  as... 常用于union all中，相当于把union的结果集放到a中，之后对a操作就行了，避免重复执行union，代码结构更清晰


avatar
文章
阅读
粉丝