Oracle数据库 知识点记录

• 1、数据类型
  Oracle不支持Bool类型，遇到需要设值为0和1的情况，可以将字段设为Number（0,1）类型。
  在C#中映射该字段时，属性数据类型设为Int型。
  对应的在页面部分设值时，可以用option标签传值。例如：

  <div class="">
  <select id="" name="" class="">
       <option value="1">有效</option>
       <option value="0">无效</option>
  </select>
  </div>
  

• 2、查询关键字记录
  not in 查询某个字段不存在于某个表中
  (+):表示外连接

• 3、整条记录复制插入数据库
  Insert into b select * from a where id = xx

• 4、对于表结构不一致的表，插入语句
  Insert into a(a1,b1,c1) select a2,b2,c2 from b where 条件
  insert into 新表 select 字段1，字段2 from 旧表
  insert into 新表（新表中字段1，新字段2…） select (旧表中对应字段1，旧字段2) from 旧表

• 5、函数

  • 多条记录合并成一条字符串
    wm_concat(“name”)(以逗号分隔)（此函数弃用）
    字段拼接改用函数 listagg()，说明如下
    将字段ename用逗号拼接成一个值 select listagg(ename, ',') within group(order by sal)name from emp; 分组 select deptno, listagg(ename,',') within group(order by sal)name from emp group by deptno;
  • NVL(表达式1，表达式2)
    如果表达式1为空值，NVL返回值为表达式2的值，否则返回表达式1的值。该函数的目的是把一个空值转换成一个实际的值。其表达式的值可以是数字型、字符型和日期型。但是表达式1和表达式2的数据类型必须为同一类型。
    比NVL效率更高的函数 COALESCE(表达式1, 表达式2)

• 6、数据库授权
  选择语句授权 Grant select on 表名 to 用户名
  更新语句授权 Grant update on 表名 to 用户名

• 7、日期查询

   select count(*)  
   from TABLE_NAME t
   where to_char( t.接单日期 , 'yyyy') = '2018'  
   and t.接单日期>=to_date('31-03-2018','dd-mm-yyyy') 
   and t.接单日期<=to_date('30-06-2018','dd-mm-yyyy')
  

• 8、rownum 实现分页

  (内层排序，外层选)不超过20条记录
  select * from(
  	select id, customer, name, customer_no, rownum as rn 
  	from crm_customer
  	where 1=1 and lower(customer_name) like '%aaa%'
  	order by id asc
  )
  Where rn>=1 and rn<=20
  
  求和
  select sum(decla_qty) from(
  select decla_qty from (
  select * from crm_customs_manual_mat order by decla_qty desc)
  where rownum <= 5)
  
  select * from (
  select manual_id,decla_qty,rownum as rn
  from crm_customs_manual_mat 
  order by decla_qty desc)
  where rn = 6
  
  select a1-b1
  from (
    select (select sum(decla_qty) from crm_customs_manual_mat)as a1,
    select (select sum(decla_qty) from(
            select decla_qty from (
                        select * from crm_customs_manual_mat order by decla_qty desc)
                                  where rownum <= 5))as b1
  

• 9、在oracle中创建一个表使之表结构与已知表相同

  不包括表数据
  create table newt as 
  select * from old 
  where 1<>1
  包含数据
  create table newt as 
  select * from old 
  where 1=1
  

• 10、异常分析

  • 值过多
    当表结构变化后，跟原表表结构相同的表的变更会受到影响，报值过多
  • 输入字符串的格式不对：有可能传参出现undefined
  • 标识符无效（查询出重名字段）
  • 数据库查询语句 如果创建的是StringBuilder的对象，在添加到序列中时，一定要转化为String，否则传入的就一个引用
  • 并非所有变量都已绑定：没有设置参数的值
  • 未明确定义列：当查询语句中由相同的字段名（id）但是没有明确表示是哪一个
  • 无效数字：数字类型和字符串类型的对应问题

• 11、名词

  • 视图：
    视图是一个虚拟的表，是一个表中的数据经过某种筛选后的显示方式，视图由一个预定义
    的查询select语句组成。
    视图中的数据并不属于视图本身，而是属于基本的表。
    视图不能被修改。表修改或者删除后应该删除视图再重建。
    视图的数量没有限制，但是命名不能和视图及表重复，具有唯一性。
    视图可以被嵌套，一个视图中可以嵌套另一个视图。
    视图不能索引，不能有相关联的触发器和 默认值。
    创建视图
    create or replace view v1 as
    select *（字段） from t1
    where 条件
    删除视图
    drop view v1

  • 事务
    事务是一个操作序列，这些操作“要么不做，要么都做”，是数据库环境中不可分割的逻辑工作单元。
    定义事务的语句：
    BEGIN TRANSACTION ：事务开始
    COMMIT：事务提交
    ROLLBACK：事务回滚
    事务具有原子性、一致性、隔离性和持久性

  • 存储过程
    存储过程是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。
    Oracle存储过程基本语法
    1.create or replace procedure 存储过程名
    2.is 表明后面将跟随一个PL/SQL体
    3.begin 表明PL/SQL 体的开始
    4.null 表明什么都不做，这句不能删除
    5.end 表明PL/SQL体的结束
    创建语法
    create or replace procedure 存储过程名 （param1 in type, param2 out type）as
    变量1 类型 （值范围）;
    变量2 类型 （值范围）;

    create or replace
    procedure p_test is
    i number;
    begin
    for i in 1801..5000 loop
    insert into dm_real_location values (i,'',1,34);
    end loop ;
    commit;
    end p_test;
    以上存储过程的含义	
    从1801开始，每次插入一条记录，插入完自增1，一直插入到5000为止。
    

  • 触发器
    是SQL Server提供给程序员和数据分析员来保证数据完整性的一种方法，他是与表事件相关的存储过程，触发器和存储过程唯一区别是触发器不能执行EXECUTE语句调用，而是在用户执行Transact-SQL 语句时自动触发执行。