7.Oracle7

1. 序列sequ ence：自增主键的问题

-- Create sequence 
create sequence SEQ_STUNO
minvalue 1      -- 最小值
maxvalue 999999 -- 最大值
start with 1    -- 开始值
increment by 1  -- 增量（别的数据有auto increament字段）
cache 10;       -- 防止oracle挂掉缓存个数

补: 建立外键的时候指定删除模式：

• – cascade主表删除，关联的子表一起删除
• – 主表删除，关联的子表字段变为null
• – 开发时不设置：要先删子表，再删主表

2. 视图：虚拟表

经常用的复杂查询可以定义视图。视图一般不做修改，只查询

create view 视图名 as (select ....)

3. 索引

提高查询效率

• unique: 唯一索引
• nomal; 普通（范围索引）
• bitmap: 重复多的数据快

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

4. 同义词

• 起个别名代替emp

CREATE SYNONYM emp1 for emp;
select * from emp1;

---

5. PL

行类型：record

select * from emp1;

begin
   
   DBMS_OUTPUT.put_line('hell');
end;

declare
  v_empno number(5);
  v_ename varchar2(10);
  
begin
  select empno, ename into v_empno, v_ename
  from emp
  where empno = 7369;
  DBMS_OUTPUT.put_line(v_empno || v_ename);
end;


declare
begin 
  for i in 1..100 loop
     DBMS_OUTPUT.put_line(i);
  end loop;
end;
----
declare
a emp%rowtype; -- 行类型 emp表中一行的数据类型
begin
  select * into a
  from emp where empno = 7369;
  DBMS_OUTPUT.put_line(a.empno || a.ename);
end;
--
declare
type type1 is record(empno1 number, ename1 varchar2(200)); -- 自定义record类型レコード
a type1;
begin
  select empno, ename into a
  from emp where empno = 7369;
  DBMS_OUTPUT.put_line(a.empno1 || a.ename1);
end;

5.2 游标cursor

游标指向一个检索结果,

要打开，要关闭，通过fetch指向下一行。

for可以自动开关和fetch.

---游标
declare
a emp%rowtype;
cursor CUR_EMP is select * from emp;
begin
   open CUR_EMP;
        -- 循环获取
        fetch cur_emp into a; -- 游标指向新的一行
        loop
          exit when not CUR_EMP%FOUND; -- 游标没有指向数据
          DBMS_OUTPUT.put_line(a.empno || a.ename || a.job);
          
          fetch cur_emp into a; -- 指向新的一行
        end loop;
   close CUR_EMP;
end;
--
declare
a emp%rowtype;
cursor CUR_EMP is select * from emp;
begin
    for a in cur_emp loop -- for 自动开关游标和fetch游标
        DBMS_OUTPUT.put_line(a.empno || a.ename || a.job);
    end loop;
end;

5.3 存储过程procedure

参数可以是， in或out或in out组合。

create or replace procedure getCount(in_job in EMP.JOB%type, in_outcount in out number) is
v_count number;
begin
  if in_job is not null then
    select count(*) into in_outcount
    from emp
    where job = in_job;
  else
    in_outcount:=0;
  end if;
  -- Dbms_Output.put_line(v_count);
end getCount;
-------
-- Created on 2021/4/20 by ASUS 
declare 
  -- Local variables here
  i integer;
  in_job varchar(20) := 'SALESMAN';
  v_count number;
begin
  -- Test statements here
  getCount(in_job => 'SALESMAN', in_outcount =>:v_count );
  DBMS_OUTPUT.put_line(v_count);
end;

5.4. 函数Function

• 有返回值 （存储过程可以用out参数做返回）

create or replace function add11(inp1 in number, inp2 in number) return number is -- FUnction特有
  v_result number;
begin
  v_result := nvl(inp1, 0) + nvl(inp2, 0);

  return v_result;
end add11;

-- Created on 2021/4/20 by ASUS 
declare 
  -- Local variables here
  i integer;
  in_job varchar(20) := 'SALESMAN';
  v_count number;
  rec emp%rowtype;
  cursor CUR_EMP is select * from emp;
begin
  -- Test statements here
  getCount(in_job => 'SALESMAN', in_outcount =>:v_count );
  for rec in CUR_EMP loop
      select add11(sal, comm) into v_count
      from emp where empno = rec.empno;
      DBMS_OUTPUT.put_line(v_count);
  end loop;
end;

5.5 触发器triger

• 第一种：DML触发器※
• 第二种：DDL触发器

create or replace trigger EMP_TRI
  AFTER delete
  on emp 
  FOR EACH ROW
declare
  -- local variables here
begin
  insert into emp_bak1(empno, ename) values (:old.empno,:old.ename);
end EMP_TRI;

replace trigger EMP_TRI
AFTER delete
on emp
FOR EACH ROW
declare
– local variables here
begin
insert into emp_bak1(empno, ename) values (:old.empno,:old.ename);
end EMP_TRI;