oracle基本知识

1.游标的定义

a.显示游标

用户自定义的游标，打开方式有两种：
①用open，比如 open 游标名 … close 游标名;
②用for，比如 for item in 游标名 loop … end loop;

create or replace procedure P_DEMO is

    Cursor demo_cursor is
    select t.user_id,t.user_name from tab_user;

begin 

   for item in demo_cursor loop
      select t.* from tab_role t where t.user_id=item.user_id;
   end loop;
   
end P_DEMO ;

b.隐式游标

PL/SQL为所有SQL数据操纵语句(包括返回一行的SELECT)隐士声明游标,称为隐式游标的原因是用户不能直接命名和控制此类游标。当用户在PL/SQL中使用数据操纵语言(DML)时,Oracle预先定义一个名为SQL的隐士游标,通过检查隐式游标的属性可以获取与最近执行的SQL语句相关的信息。

DECLARE
 
BEGIN
  UPDATE EMP SET SAL = SAL + 100 WHERE JOB = 'SALESMAN';
  IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('没有符合条件的雇员');
  ELSE
    DBMS_OUTPUT.PUT_LINE('上调了：' || SQL%ROWCOUNT || '个雇员的工资');
  END IF;
END;

c.动态游标

静态游标在使用时,使用的查询语句已经确定,如果需要在运行的时候动态的绝对执行哪种查询,可以使用REF游标(动态游标)和游标变量。

1).声明游标的语法
声明REF游标需要2个步骤：声明REF的游标类型和声明使用此类型的游标变量。

声明游标类型的语法：
type 游标类型名称 is ref cursor
[return 游标返回值类型]

游标类型名称：定义的一个游标变量的类型,一般采用ref_type_类型名 这种格式。
游标返回值类型：可选项,定义游标变量的返回值类型,注意必须为记录变量。

REF游标分为强类型和弱类型俩种,在定义游标变量类型时,如果指定了游标变量的返回值类型,那么就是强类型,如果没指定,就是弱类型。并且一旦指定了返回值类型,在打开游标时,绑定的查询结果的返回集一定是return中定义的类型.

声明游标变量的语法：
游标变量名称 游标类型名称

2).打开游标的语法：
open 游标变量名 for 查询语句

3).提取游标,同显式游标。
4).关闭游标同显式游标。

--弱类型
--将薪水低于3000的雇员薪水增加500,增加后最高不超过3000
DECLARE
  TYPE REF_CURSOR_TYPE IS REF CURSOR; --声明一个弱类型的动态游标类型
  REF_CURSOR REF_CURSOR_TYPE; --定义一个游标为声明的弱类型游标
  V_SAL      EMP.SAL%TYPE;--声明一个变量用来接收雇员薪水
  V_EMPNO    EMP.EMPNO%TYPE;--声明一个变量用来接收雇员编号
BEGIN
  OPEN REF_CURSOR FOR
    SELECT SAL, EMPNO FROM EMP;--打开游标并指定使用的SQL语句
  LOOP
    FETCH REF_CURSOR
      INTO V_SAL, V_EMPNO;--将游标指向一行数据并给变量赋值
    EXIT WHEN REF_CURSOR%NOTFOUND;--当游标无数据时退出
    IF V_SAL < 3000 THEN--薪水低于3000进入IF,这个IF最后会执行更新SQL
      IF V_SAL >= 2500 THEN--薪水低于3000又大于2500进入这个IF
        V_SAL := 3000;
      ELSE
        V_SAL := V_SAL + 500;
      END IF;--结束一个IF,一个IF对应一个END IF；
      UPDATE EMP SET SAL = V_SAL WHERE EMPNO = V_EMPNO;--更新雇员薪水
    END IF;--结束最外层IF
  END LOOP;--结束LOOP循环
  CLOSE REF_CURSOR;--关闭游标
END;
 
--强类型
--查询所有雇员姓名
DECLARE
  TYPE EMP_REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;--声明一个强类型(指定返回类型)的动态游标
  REF_CURSOR   EMP_REF_CURSOR;--定义一个声明的强类型游标
  V_EMP_RECORD EMP%ROWTYPE;--定义一个接收变量
BEGIN
  OPEN REF_CURSOR FOR
    SELECT * FROM EMP;
  LOOP
    FETCH REF_CURSOR
      INTO V_EMP_RECORD;
    EXIT WHEN REF_CURSOR%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(V_EMP_RECORD.ENAME);
  END LOOP;
  CLOSE REF_CURSOR;
END;

d.大数据量下如何处理游标

使用 fetch，bulk

DECLARE
    CURSOR cur IS
      SELECT * FROM t_table;
    TYPE rec IS TABLE OF t_table%ROWTYPE;
    recs rec;
  BEGIN
    OPEN cur;
    WHILE (TRUE) LOOP
      FETCH cur BULK COLLECT
        INTO recs LIMIT 100;
      FORALL i IN 1 .. recs.COUNT
        INSERT INTO t_table VALUES recs (i);
      COMMIT;
      EXIT WHEN cur%NOTFOUND;
    END LOOP;
    CLOSE cur;
  END;

2.常用异常处理

NO_DATA_FOUND

未找到相关数据，代码示例如下：

v_user_name VARCHAR2(30);
begin
      select t.user_name
        into v_user_name
        from tab_user
       where user_id=p_user_id;
    exception
      when NO_DATA_FOUND then
        v_user_name := '';
    end;

OTHERS

其它异常

v_user_name VARCHAR2(30);
begin
      update tab_user t
      set t.user_name=v_user_name 
      where t.user_id=p_user_id;
    exception
      when others then
        rollback;
    end;

3.触发器

CREATE OR REPLACE TRIGGER T_OPLOG_TAB_ORD_ORDER
  after update or delete of user_name
  on TAB_ORD_ORDER for each row
/*
  Description:
  Author: 
  Date: 
  Version: 1.0
  */

declare
  V_USER_NAME     VARCHAR2(30); --操作网点名称
begin

  --修改操作
  if updating then
  
  
  elsif deleting then
  
  end if;
  
exception
  when others then
    
end TAB_ORD_ORDER;

4.基本数据类型

可变长度和固定长度的区别：
比如 某数据类型(10)，如果是可变长度，则占用的空间大小取决于存储的字符长度，如果是固定长度，则无论存储的字符多长，都占用10个字符的磁盘空间。

varchar2(10)
汉字占两个字节，所以最大可以存放10个非汉字，5个汉字

nvarchar2(10)
所有字符占2个字节，所以最大可以存放10个字符

number(m,n)
小数位最大可以存储n位，整数位最大可以存储m-n位

5.索引扫描的类别

INDEX UNIQUE SCAN（索引唯一扫描）
INDEX RANGE SCAN（索引范围扫描）
INDEX FULL SCAN（索引全扫描）
INDEX FAST FULL SCAN（索引快速扫描）
INDEX SKIP SCAN（索引跳跃扫描）
索引跳跃扫描一般针对于复合索引，比如存在复合索引（A,B,C），当使用查询条件where B > ? and c > ?，就会使用索引跳跃扫描