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;