例項總結Oracle知識點大全

M_Enthusiasm發表於2020-05-07
Oracle

文章目錄

Oracle知識點

表空間、使用者、角色

表空間

獲取表空間與資料檔案的關係

select t.name, d.name from v$tablespace t, v$datafile d;

檢視資料塊大小

show parameter db_block_size;

查詢當前使用者所擁有的表的資訊、可以訪問的表的資訊、當前資料庫所有表的資訊

select * from user_tables;
select * from all_tables;
select * from dba_table;

查詢系統中所有表空間的資訊

select tablespace_name, status, alloction_type from dba_tablespaces;

建立表空間

create tablespace demospace
logging
datafile 'D:\Oracle\orsx\oradata\demospace\demospace.dbf' size 20M
autoextend on;

改變表空間的大小,新增新的資料檔案

alter tablespace newsmgm_space
add datafile 'D:\Oracle\orsx\oradata\orcl\pdborcl\newsmgm_space_1.dbf' size 10M；

修改表空間的已有的資料檔案大小

alter database datafile 'D:\Oracle\orsx\oradata\orcl\pdborcl\newsmgm_space_1.dbf' resize 20M；

設定預設表空間

select property_name, property_value from database_properties where property_name = 'DEFAULT_PERMANENT_TABLESPACE';

檢視當前資料庫所有使用者資訊

select username, account_status, default_tablespace from dba_users;

查詢資料庫中有哪些容器

select con_id,dbid,name,open_mode from v$pdbs;

開啟pdb–

alter pluggable database pdborcl open;

將當前公共區的會話轉到容器–

alter session set container = pdborcl;

建立新使用者test01並繫結到demospace–

create user c##test01 identified by 123456 default tablespace demospace;

賦予text01連線資料庫的許可權–

grant create session to c##test01;

賦予test01查詢scott.emp表的許可權(並獲得許可權的傳遞性)–

grant select on scott.emp to test01 with grant option;

再建立新使用者test02–

create user c##test02 identified by 123456;

檢視所有物件–

select username from dba_users;

為使用者授予登陸許可權–

grant create session to marc;

為使用者授予建立表、建立序列的系統許可權–

grant create table, create sequence to marc;

為使用者授予表空間’USERS’許可權–

alter user c##marcs quota unlimited on USERS;

將marcs模式的id表的部分物件許可權授予使用者test–

grant select, insert, update on marcs.identified to test;

角色

建立一個角色 role_emp–

create role role_emp;

將對emp表的查詢許可權賦予角色role_mep–

grant select on scott.emp to role_emp;

將對emp表的所有許可權賦予角色role_mep–

grant all on scott.emp to role_emp;

把role_emp角色賦予給test01–

grant role_emp to test01;

把role_emp角色從test01回收–

revoke role_emp from test01;

查詢test01所具有的所有角色–

select grantee, granted_role, admin_option, default_role
from dba_role_privs where grantee = 'C##TEST01';

表管理

表的建立

grant create table, create sequence to c##test01;
alter user c##test01 quota unlimited on DEMOSPACE;

在當前使用者預設表空間建立department表–

create table department(
  deptno number(2)primary key,
  dname varchar2(14),
  loc varchar2(13)
);

檢視錶結構–

desc department;

在users表空間建立employee表–

create table employee(
  empno number(4)primary key,
  ename varchar2(10),
  job varchar2(20),
  mgr number(4),
  hiredate date,
  sal number(7,2),
  comm number(7,2),
  deptno number(2)not null
         constraint fk_emp_dept references department(deptno)
)
  tablespace users;

表的維護

新增列–

alter table employee add(
  sex char(3),
  registdate date default sysdate not null
);

修改列定義–

alter table employee modify job varchar2(10);
alter table employee modify sex default'男';

修改列

alter table employee rename column registdate to regist_date;

刪除列–

alter table employee drop column regist_date; alter table employee add registdate date;
alter table employee drop (sex, registdate);

將列標記為unused狀態(禁用某一列)，然後刪除unused狀態列–

alter table employee add registdate date;
alter table employee set unused column registdate;

為表新增註釋–

comment on table employee is '員工表';

為列新增註釋–

comment on column employee.empno is '工號';

修改表名–

comment on table employee rename to tb_employee;

刪除department表及對其的外來鍵引用–

drop table department cascade constraint;

查詢當前使用者的所有表的名稱、表空間及狀態

select table_name, tablespace_name, status from user_tables;

查詢當前使用者所有表的註釋資訊–

select table_name, comments from user_tab_comments;

查詢某個表中某列的描述資訊–

select column_name, data_type, data_length, nullable
from user_tab_columns
where table_name = 'EMPLOYEE';

完整性約束

  • 主鍵約束 primary key
  • 外來鍵約束 foreign key
  • 唯一性約束 unique 可以定義在一列或多列上
  • 檢查約束 check
  • 非空約束 not null

建立表時為各表新增列級約束–

create table board(
  bid number(2) primary key,  
  bname varchar2(20) not null,  
  status number check(status in(0,1))  
);

create table register(
  rid number(10) primary key,
  logname varchar2(20) unique,
  password varchar2(10) not null,
  age number(3) check (age>=13 and age<=80),
  registboard number(2) not null references board(bid)
);

建立工資發放記錄表時新增的表級約束–

create table payroll(
  empno number(4) references employee(empno),
  payrolldate date not null,
  deptno number(2),
  sal number(7,2),
  comm number(7,2),
  constraint pk_payroll primary key(empno, payrolldate),
  constraint fk_dept foreign key(deptno) references department(deptno)
);

查詢payroll表的所有約束資訊–

select constraint_name, constraint_type, status
from user_constraints
where table_name='PAYROLL';

查詢payroll表中的各個約束所作用的列–

select constraint_name, table_name, column_name
from user_cons_columns
where table_name='PAYROLL';

建立職位表job和部門表deptment–

create table job(
  jobid number,
  jobname varchar2(20),
  jobdesc clob,
  workplace varchar2(20),
  minsalary number(6),
  maxsalary number(6),
  department number(2)
)tablespace users;

create table department(
  deptno number(2) primary key,
  dname varchar2(14),
  loc varchar2(13)
);

新增主鍵約束–

alter table job add primary key(jobid);

新增唯一性約束–

alter table job add unique(jobname);

新增外來鍵約束–

alter table job add constraint fk_job_dept foreign key(department)
references department(deptno);

新增檢查約束 需要建立表級約束(起個名字)–

alter table job add constraint ck_job_salary check( maxsalary > minsalary);

新增非空約束–

alter table job modify jobname not null;

對外來鍵約束fk_job_dept重新命名–

alter table job rename constraint fk_job_dept to fk_jobdept;

通過名稱刪除外來鍵約束–

alter table job drop constraint fk_jobdept;

刪除主鍵約束–

alter table job drop primary key;

刪除主鍵約束時保留唯一索引–

alter table job drop primary key keep index;

刪除唯一性約束–

alter table job drop unique(jobname);

SQL基礎

  • 資料定義語言：DDL
  • 資料查詢語言：DQL
  • 資料操縱語言：DML
  • 事務控制語言：TCL

資料管理

向部門表插入一行記錄–

insert into department(deptno, dname, loc) values(50,'研發部'，'青島');

向員工表插入一條記錄–

insert into employee values(
7210,'jenny','programmer',null,to_date('20150302','yyyy-MM-dd'),
3000,null,50);

將scott模式的dept表的記錄插入deparment表中–

insert into department select * from scott.dept;

將scott模式的emp表的記錄插入employee表中–

insert into employee select * from scott.emp;

查詢部門表中所有列的記錄–

select * from department;

查詢符合條件的員工的部分列的資訊表

select empno, ename, job, sal, comm
from employee
where comm is null;

將員工表中所有員工的獎金更新為200元–

update employee set comm = 200;

將工號為7210的員工的工資更新為原有工資與獎金的和–

update employee set sal = sal + comm where empno = 7210;
select empno,ename,job,sal,comm from employee;

依據條件字句刪除記錄–

delete from employee where empno = 7210;

刪除有外來鍵約束引用的表中的所有記錄(表中內容)–

delete from employee;
delete from department;

使用truncate語句刪除有外來鍵約束引用的表中的所有記錄(無法用rollba恢復)–

alter table employee drop constraint fk_emp_dept;
truncate table department;

事務控制

  • 事務四大特性：原子性、一致性、隔離性、永續性

事務的開始與結束

  • 開始：dml: insert；insert； update； delete；
  • 結束：commit； dll:create table；
create table account(
  account_id varchar2(16),
  account_name varchar2(10),
  account_balance number(16,3),
  constraint pk_accountid primary key(account_id)
);

insert into account values('1001', '張三', 1000);

第一個dml語句執行，事務A開始

insert into account values('1002', '李四', 1);
select * from account;
commit;

事務提交，事務A結束

update account set account_balance = account_balance-1000
where account_id='1001';

執行dml語句，新的事務B開始

update account set account_balance = account_balance+1000
where account_id='1002';
alter table account add constraint ck_accountbalance check(account_balance>=0);

執行ddl語句，事務B自動提交，事務B結束

select * from account;
delete from account where account_id='1001';

新的事務C開始

exit;

正常退出，事務C被自動提交，事務C結束

commit命令顯示提交事務–

create table department(
  deptno number(2) primary key,
  dname varchar2(14),
  loc varchar2(13)
)tablespace users;

insert into department values(50, '研發部', '青島');
commit;
select * from department;

insert into department values(60, '產品部', '青島');
update department set loc = '高新區'where deptno = 60;
commit;
select * from department;

rollback命令回滾單條dml語句事務–

select * from department;
delete from department where deptno = 60;
rollback;
select * from department;

rollback命令回滾多條dml語句事務–

insert into department values(70, '市場部', '青島');
update department set loc = '高新區'where deptno = 70;
rollback;
select * from department;

設定儲存點–

select * from department;
update department set loc = 'QING DAO'where deptno = 50;
savepoinit sp1;

delete department where deptno = 50;
savepoinit sp2;

rollback to sp1;
select * from department;

rollback;
select * from department;

併發事務

併發事務可能導致的3類問題

  • 髒讀：一個事務對資料的修改在提交之前被其他事務讀取
  • 不可重複讀：在某個事務讀取一次資料之後，其他事務修改了這些資料並進行了
    提交，當該事務重新讀取了這些資料時就會得到與之前一次不一樣的結果。
  • 幻讀：同一查詢在同一事務中多次進行，由於其他提交事務所做的插入或刪除操作，
    每次返回不同的結果集。

事務的隔離級別

  • read uncommitted:稱為未授權讀取或讀取未提交。 (髒、不、幻)
  • read committed：稱為授權讀取或讀提交。 (不、幻)
  • repeatable read：稱為可重複讀取。 (幻)
  • serializable：稱為序列化。

設定資料庫事務為serializable隔離級別–

set transaction isolation level serializable;

設定資料庫事務為read committed隔離級別–

set transaction isolation level read committed;

設定資料庫事務為read only隔離級別–

set transaction read only;

修改資料庫事務為serializable隔離級別–

alter session set isolation_level = serializable;

修改資料庫事務為read committede隔離級別–

alter session set isolation_level = read committed;

幻讀例項–

會話1

create table tran_test(num number);
set transaction isolation level read committed;
insert into tran_test values(10);
select * from tran_test;

會話2

insert into tran_test values(20);
commit;

會話1

select * from tran_test;

修改事務的隔離級別為序列化例項–

會話1

delete from tran_test;
exit;
alter session set isolation_level = serializable;
insert into tran_test values(10);
select * from tran_test;

會話2

insert into tran_test values(20);
commit;

會話1

select * from tran_test;

修改事務的隔離級別為read only例項–

會話1

delete from tran_test;
exit;
set transaction read only;
insert into tran_test values(10); ×
select * from tran_test; ×

會話2

insert into tran_test values(20); 
commit;

會話1

select * from tran_test; ×

資料查詢

基本查詢

select * from emp;

distinct運算子（去除重複資料）–

select distinct * from emp;

連線運算子||

限定查詢與排序(where)

查詢工資在1500到2900之間的員工資訊

select empno, ename, sal from emp where sal>=1500 and sal<=2900;

查詢職位為salesman的人員名單

select empno, ename, job from emp where job='SALESMAN'

between and 操作符(閉區間)|not between and

查詢在1981年1月1日和1981年12月31日之間加入公司的員工–

select empno, ename, hiredate
from emp where hiredate between '01-1月-81' and '31-12月-81';

in：用來測試某些值是否在列表中|not in

查詢職位為銷售、文員或經理的人員

select empno, ename, job from emp where job in('SALESMAN','CLEAR','MANAGER');

null(表示為空)–

  • 和任何值進行算術運算，結果為null
  • 和任何值進行比較運算，結果為unknown

like模糊查詢–

查詢員工姓名是以M開頭的所有員工的資訊–

select empno, ename, job from emp where like 'M%';

查詢姓名以A開頭、姓名最後連個字元為E和一個任意字元的員工資訊–

select empno, ename, job from emp where like 'A%E_';

使用轉義操作符escape,使字元表示本意–

select * from department where dname like'IT\_%' escape' \ ';

order by 排序-- asc升序(預設)–desc(降序)–

select ename, job, sal from emp where deptno = 30 order by sal desc;

單行函式

字元函式

lower(列|字串)的使用–轉換為小寫

select lower('Structural|Query language')from dual;

upper(列|字串)的使用–轉換為大寫

select empno, ename, job from emp where job = upper('clerk');

length(列|字串)的使用–返回長度*

select length('SQL is an english like language')from dual;

initcap(列|字串)的使用–開頭首字母大寫

select empno, ename, initcap(ename) from emp;

substr(列|字串,開始點[,長度])的使用–擷取*

select substr('SQL is an english like language', 3)from dual;
select substr('SQL is an english like language', 1, 3)from dual;

replace(列|字串,要搜尋的字串，替換的字串)的使用–替換*

select replace('SQL Plus supports loops or if statements','supports','not supports')from dual;

concat(列|字串,列|字串)的使用–拼接*

select concat('hello,','world!')from dual;

rpad(列|字串,長度，填充字元)的使用–填充

select rpad(ename, 10, '*'), lpad(ename, 10, '*')from emp;

ltrim(字串)、rtrim(字串)的使用–去左空格、去右空格

select ' QST ', ltrim(' QST '), rtrim(' QST ')from dual;

trim(列|字串)的使用–去左右空格

select’ QST ‘, trim(’ QST ')from dual;

instr(列|字串, 要查詢的字串[,開始位置])的使用–查詢出現位置*

select distinct job, instr(job, upper('man'))from emp;

select distinct job, instr(job, upper('man'), 2)from emp;

數值函式

  • round(列|數字[，保留位數])–四捨五入*
  • trunc(列|數字[，擷取位數])–截斷*
  • mod(列|數字,數字)–取餘*

日期函式 預設:DD-MON-RR

  • 獲得系統當前時間
select sysdate from dual;
  • 修改日期顯示格式
alter session set nls_format='yyyy-mm-dd hh24:mi:ss';

  • add_months(日期, 數字)–計算增加月數日期

  • last_day(日期)–計算此月最後一天

  • next_day(日期, 星期)–計算下一個星期幾的日期

  • months_between(日期1, 日期2)–計算兩個日期相隔月數

轉換函式

  • to_char(列|日期|數字，轉換格式)–轉換為字串

  • to_date(字串，轉換格式)–將字串轉換為日期型(有分秒：to_timestamp(字串，轉換格式))

  • to_number(字串)–將數字內容的字串轉變為字元型

其他函式

  • nvl(列,替換值)–
  • nvl2(列, 替換值1, 替換值2)-
  • nullif(表示式1, 表示式2)–
  • decode(列值, 判斷值1, 顯示結果1, ···)–

分組函式

count()函式–

查詢公司裡有多少員工是由經理管理的、多少員工是有獎金的–

select count(mgr), count(comm)from emp;

  • avg(列)函式：求平均值–

  • sum(列)函式：求和–

  • max(列)函式：求最大值–

  • min(列)函式：求最小值–

group by–分組

查詢公司中按照職位分類後，每類員工的平均工資、最高工資、最低工資

select job, avg(sal), max(sal), min(sal) from emp group by job;

執行邏輯

  • select…from…where…group by…order by…
  • from->where->group by->select->order by

having–在分組後對組進行篩選

查詢平均工資高於2000的職位–

select job, avg(sal)from emp group by job having avg(sal)>2000;

多表查詢

等值連線–兩張表之間的連線查詢

查詢每位員工所屬部門的名稱和地點

select empno, ename, emp.deptno, dname, loc
from emp, dept
where emp.deptno = dept.deptno;

查詢工資為2000元或以上的員工所屬部門和所在地點–

select e.empno, e.ename, e.deptno, d.dname, d.loc
from emp e, dept d
where e.deptno = d.deptno and e.sal >= 2000
order by d.loc;

自連線(同一張表之間的連線查詢，主要用於顯示上下級或層次關係)

查詢每一位銷售人員的直屬領導資訊–

select e.empno, e.ename, e.job, e.mgr, e.ename 經理, m.job 職位
from emp e, emp m
where e.mgr = m.empno and e.job like 'sal%';

查詢職位為文員和分析員的員工的姓名、職位、直屬經理姓名和部門名稱、顯示結果按員工編號升序排序。

select e.ename 員工名稱, e.job 職位, m.ename 經理名稱, d.dname 部門名稱
from emp e, emp m, dept d 
where e.mgr = m.empno
and e.deptno = d.deptno
and e.job in('CLERK', 'ANALYS')
order by e.empno;

內連線 (表)inner join(表)on(條件)

查詢部門號為20的部門的部門名稱及員工姓名。

select e.ename, d.dname
from emp e inner join dept d 
on e.deptno = d.deptno and d.deptno = 20;

使用內連線查詢職位為文員和分析員的員工的姓名、職位、直屬經理姓名和部門名稱、顯示結果按員工編號升序排序。

select e.ename 員工名稱, e.job 職位, m.ename 經理名稱, d.dname 部門名稱
from emp e
join emp m on e.mgr = e.empno
join dept d on e.deptno = d.deptno
where e.job in('CLERK', 'ANALYS')
order by e.empno;

外連線 --沒有“+”那邊全返

左外連線(左邊全返)

查詢所有部門資訊及其對應的員工資訊(保括沒有員工的部門)–

select e.empno, e.ename, d.deptno, d.dname, d.loc
from dept d left join emp e
on d.deptno = e.deptno
order dy d.deptno desc;

select e.empno, e.ename, d.deptno, d.dname, d.loc
from dept d left join emp e
where d.deptno = e.deptno(+)
order by dy d.deptno desc;

右外連線(右邊全返)

查詢所有部門資訊及其對應的員工資訊(保括沒有員工的部門)–

select e.empno, e.ename, d.deptno, d.dname, d.loc
from emp e left join dept d 
on e.deptno = d.deptno
order dy d.deptno desc;

select e.empno, e.ename, d.deptno, d.dname, d.loc
from emp e left join dept d
where d.deptno(+) = e.deptno
order by dy d.deptno desc;

全外連線(左右全返)

依據employee表和dept表查詢員工及其所有部門的資訊，無對應關係的員工及其部門用空值填充

select e.ename, e.deptno, d.deptno, d.dname, d.loc
from employee e full join dept d
on e.deptno = d.deptno
order by d.deptno;

不等連線 “>” “<” “between…and”

查詢員工資訊及其工資所對應的級別。其中工資級別儲存在Scott的模式物件salgrade(工資等級表)中

select e.empno, e.ename, e.job, e.sal, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal;

子查詢

查詢工資高於平均工資的員工資訊–

select empno, ename, sal 
from emp 
where sal >(select avg(sal)from emp);

查詢每個部門最高工資的員工資訊–

select a.empno, a.ename, a.sal, a.deptno
from emp a
where (select count(*)from emp where deptno = a.deptno and sal>a.sal)=0;

查詢與ward職位和工資等級都相同的員工–

select e.empno, e.ename, e.job, e.sal, s.grade
from emp e, salgrade s
where e.ename != 'WARD' and (e.sal, s.grade)=(
select sal, grade from emp, salgrade
where ename = 'WARD' and sal between losal and hisal);

查詢哪些員工的工資為所任職位最高的-- in 與子查詢返回結果中任何一個值相等 (not in 不相等)

select empno, ename, job, sal 
from emp 
where sal in(select max(sal)from emp group by job);

查詢哪些員工的工資高於最低的職位平均工資-- > any 比子查詢的返回結果中某一個值大 all(比所有值都大)

select empno, ename, job, sal 
from emp 
where sal > any(select avg(sal)from emp group by job);

查詢有所屬員工的部門資訊-- exists比較運算子 子查詢若滿足條件返回true (not exists 不返回任何一行true)

select deptno, dname, loc
from dept d
where exists(select * from emp e where d.deptno = e.deptno);

查詢各部門資訊

select d.deptno, d.dname, e.amount, a.avgsal
from dept d, 
(select deptno, count(*) amount, avg(sal) avgsal from emp group by deptno) e,
where e.deptno = d.deptno;

with起名引用

with subdept as(
  select deptno, count(*)count
  from emp 
  group by deptno)
select deptno, dname, loc
from dept
where deptno in(
select deptno from subdept
where count = (select max(count)from subdept));

集合查詢

並集操作:union(不包括重複的)、union all(包括重複出現的內容)

查詢20號部門和30號部門所擁有的職位

select job from emp where deptno = 30
union 
select job from emp where deptno = 20;

交集操作:intersect

補集操作:minus (第一個有第二個沒有)

cross join 產生笛卡爾積現象

select * from emp, dept;

select empno, ename, emp.deptno, dept.deptno, dname
from emp cross join dept;

TopN查詢

查詢員工表的第1行員工資訊–

select * from emp where rownum = 1;

查詢員工表的前2行員工資訊–

select * from emp where rownum < 3;

先rownum(優先順序高)再order by

查詢工資最高的前3名員工的資訊(先排序再偽列限定)–temp為臨時表

select temp.*from(
 select empno, ename, job, sal
 from emp
 order by sal desc)temp
where rownum <= 3;

查詢員工表的第2行員工資訊–

select temp.*from(
 select rownum rn, empno, ename, job, sal 
 from emp 
 where rownum < 3)temp
where temp.rn > 1;

通過補集minus–

select * from emp where rownum < 3;
minus
select * from emp where rownum < 2;

查詢第6行到第10行的記錄–

select temp.*from(
 select rownum rn, empno, ename, job, sal
 from emp 
 where rownum <= 10)temp
where temp.rn >= 6;

fetch子句–

fetch first 3 row only :表示取得前3行記錄(放在排序後)

offset 2 rows fetch next 6 row only :表示從第2行開始取得6行記錄，即第3行到第8行記錄(不包括邊界值)

fetch next per percent row only :表示按照指定的百分比per%取得相關行數的記錄

層次化查詢

查詢員工之間的領導關係，職位由高到低排列

select empno, ename, mgr
from emp 
start with job = 'PRESIDENT'
connect by prior empno = mgr;

常用模式物件

模式物件

檢視

create view許可權(自己模式中建立檢視)

grant create view to scott;

create any view許可權(其他模式中建立檢視)

建立一個包含員工基本資訊，並按員工升序排序的員工基本資訊檢視–

create view scott.v_emp_base
as 
select empno, ename, sal, deptno from emp order by empno;

對v_emp_base檢視的查詢–

select * from scott.v_emp_base;

檢視v_emp_base檢視–

select ename, deptno from v_emp_base;

建立一個簡單隻讀檢視–

create view scott.v_emp_base_read
as
select empno, ename, sal, deptno from emp order by empno
with read only;
  • or replace 表示若所創檢視存在，自動重建該檢視
  • force 不管基表是否存在都建立檢視

建立一個包含多表連線，以及分組查詢的檢視–

grant create view to scott;
create or replace view v_dept_sal(name, minsal, maxsal, avgsal)
as
select d.dname, min(e.sal), max(e.sal), avg(e.sal)
from dept d, emp e
where d.deptno = e.deptno
group by d.dname;

-with check option選項–使修改檢視要滿足where條件

建立一個工資大於2000的員工年薪資訊的檢視–

create view v_emp_salsry
as
select empno, ename, sal*12 salsay
from emp
where sal > 2000
with check option;

修改檢視v_emp_salsry，增減對部門的限制條件

create or replace view v_emp_salsry
as
select empno, ename, sal*12 salsay
from emp
where sal > 2000 and deptno = 10
with check option;
  • dba_views：包含資料庫中所有檢視的資訊
  • all_views：包含當前使用者可以訪問的所有檢視的資訊
  • user_views：包含當前使用者擁有的所有檢視的資訊
select view_name, text from user_views;

刪除檢視v_emp_salsry–需要有drop view許可權

drop view v_emp_salsry;

建立一個僱員表employee,建立一個對此表進行查詢操作的檢視v_test,演示對此檢視資料的修改操作(對檢視的更新，實際上對基表的更新)

create table emplyee as select * from emp;
create or replace view v_test
as
select empno, ename, sal, deptno
from employee 
where deptno = 20
with check option;

update v_test set sal = 1000 where ename = 'SMITH';
select empno, ename, sal, deptno from employee where ename = 'SMITH'

無法對一個包含表示式列的檢視進行跟新和插入操作(with check option選項)–

create or replace view v_test
(empno, ename, salsry, deptno)
as
select empno, enamen, sal * 12, deptno
from employee
where deptno = 20
with check option;

對包含多表連線查詢的檢視也無法進行資料更新操作

create or replace view v_emp_dept
as
select empno, ename, dname
from enp e, dept d
where e.deptno = d.deptno;

update v_emp_dept set sal = 800 where ename = ‘SMITH’ √
update v_emp_dept set dname = ‘XXX’ where ename = ‘SMITH’ ×

emp是鍵值儲存表(外來鍵所在的表叫做從表,從表會作為鍵值儲存表)，dept是非鍵值儲存表

  • 對檢視的DML操作，只能操作屬於鍵值儲存表的列

序列

建立一個初始值為1、最大值為1000、步長為1的序列–

create sequence seq_emp
start with 1
increment by 1
maxvalue 1000;

建立一個初始值為10、步長為10、最大值為50、最小值為10、達到最大值時繼續迴圈產生值、伺服器預先快取3個值的序列

create sequence seq_dept
start with 10
increment by 10
maxvalue 50
minvalue 10
cycle
cache 3;
  • nextval:返回序列的下一個值
  • currval:返回序列當前值，並且只有在發出至少一個nextval之後才能使用

利用序列seq_dept向表department中新增、查詢、修改資料

create table department as select * from scott.dept where 1 = 2;
  • 只複製表結構，不復製表內容
insert into department(deptno, dname, loc)
values(seq_dept.nextval,'RESEARCH', 'QINGDAO');

select seq_dept.currval from department;

利用序列seq_dept向表department中新增、查詢、修改資料

update department set deptno = seq_dept.nextval where deptno = 10;

檢視當前使用者的序列資訊-- user_sequences

select sequence_name, min_value, max_value,
increment_by, cycle_flag, cache_size
from user_sequences;

檢視所有使用者的所有序列資訊-- dba_sequencrs

刪除seq_dept序列–

drop sequence seq_dept;

同義詞

使用者在自己模式下建立私有同義詞：create synonym 許可權

grant create synonym to scott;

在其它使用者模式下建立私有同義詞：create any synonym 許可權

建立公有同義詞許可權：create public synonym 許可權

為scott模式下的emp表建立同義詞scottemp

grant create synonym to scott;
create synonym scottemp for emp;

建立或替換現有同義詞：create or replace 語句(替換此同義詞下的表)–

替換公有同義詞scottdept所對應的表，將對應的表由dept改為department

create or replace public synonym scottemp for scott.department;

檢視當前使用者所建立的同義詞–user_synonyms

select synonym_name, table_owner, table_name from user_synonyms

檢視所有使用者所建立的同義詞–all_synonyms

刪除私有同義詞scottemp–drop synonym語句(刪除公有同義詞drop public synonym語句)

drop synonym scottemp;

索引

emp表中所有記錄的rowid、empno值

select rowid, empno, ename, from emp order by empno;
  • unique：建立唯一性索引
  • bitmap：建立點陣圖索引

建立唯一性B樹索引

create index index_employee_ename on employee(ename);

對儲存空間的顯示錶示–

create index index_employee_ename
on pmployee(ename)
tablespce users storage(initial 20k next 10k pctincrease 65);

建立new_employee表的主鍵約束時，為產生的索引指定儲存空間分配

create table new_employee(
  empno number(4) primary key using index tablespace users pactfree 0,
  ename varchar2(10));

為employee表的deptno建立一個點陣圖索引

create bitmap index index_employee_deptno on employee(deptno);

使用點陣圖索引

select empno, ename, deptno from employee where deptno = 10 or deptno;

為employee表中的hiredate列建立一個基於函式to_char()的函式索引

create index index_employee_hiredate
on employee(to_char(hiredate,'YYYY-MM-DD'));

使用index_employee_hiredates索引

select empno, ename, hiredate
from employee
where ton_char(hiredate,'YYYY-MM-DD')=='1981-11-17';

為employee表的empno列和ename列建立複合索引

create index index_empno_ename on employee(empno, ename);

  • 修改索引：alter index (合併、重建、重新命名)

  • 合併索引：alter index…coalesce

  • 重建索引：alter index…rebuild

  • 索引重新命名：alter index…rename to

  • 開啟索引監視狀態：alter index…monitoring usage

  • 關閉索引監視狀態：alter index…nomonitoring usage

  • 檢視dba_indexes:描述資料庫中的所有索引資訊

  • 檢視user_indexes:描述資料庫中的當前使用者索引資訊

刪除索引：drop index

PL/SQL基礎

PL/SQL

根據輸入的員工編號進行員工姓名查詢–

declare
  v_empno number;
  v_ename varchar2(10);
begin
  dbms_output.put_line('請輸入員工編號:');
  v_empno:=&input_empno;
  select ename into v_ename from emp where empno=v_empno;
  dbms_output.put_line('編號為:'||v_empno||' 姓名為:'||v_ename);
exception
  when no_data_found then
  dbms_output.put_line('此編號員工不存在');	   
end;

程式結構

開啟輸出

set serveroutput on;

包含宣告、執行部分的PL/SQL塊

declare
  v_num number;
begin
  v_num:=100/0;
exception
  when zero_divide then
    dbms_output.put_line('除0異常');
end;

包含子塊的PLSQL塊

declare
  v_x number:=10;
begin
  declare
    v_x number:=20;
  begin    
    dbms_output.put_line('子塊變數v_x='||v_x);
  end;
  dbms_output.put_line('外部語句塊變數v_x='||v_x);
end;

根據使用者輸入的員工編號，計算該員工所屬部門的平均工資

declare
  v_empno number;
  v_deptno number;
  v_sal number;
begin
  begin
    v_empno:=&員工編號;
	  select deptno into v_deptno from emp where empno = v_empno;
  end;
  select round(avg(sal),2)into v_sal from emp where deptno = v_deptno;
  dbms_output.put_line(v_empno||'員工所在部門的平均工資為'||v_sal);
end;

定義常量–constant

declare
  v_cons constant varchar2(20):='Hello,world.';
begin
  dbms_output.put_line('v_cons常量值為:'||v_cons);
end;

數字型別：number、binary_integer、binary_float、binary_double–

declare
  v_num1 number(3):=100;
  v_num2 number(5,2):=100.99;
  v_binary binary_integer:=-100;
  v_float binary_float:=1000.99F;
  v_double binary_double:=10000.99D;
begin
  v_num1:=v_num1+v_num2;
  v_binary:=v_binary+100;
  v_float:=v_float+1000.99;
  v_double:=v_double+10000.99;
  dbms_output.put_line('number型別資料計算和:'||v_num1);
  dbms_output.put_line('binary_integer型別資料計算和:'||v_binary);
  dbms_output.put_line('binary_float型別資料計算和:'||v_float);
  dbms_output.put_line('binary_double型別資料計算和:'||v_double);
end;

字元型別：char、varchar2、nchar、nvarchar2、rowid

declare 
  v_char char(20);
  v_varchar2 varchar2(20);
  v_nchar nchar(20);
  v_nvarchar2 nvarchar2(20);
  v_dept_rowid rowid;
begin
  v_char:='靜水流深';
  v_varchar2:='靜水流深';
  v_nchar:='靜水流深';
  v_nvarchar2:='靜水流深';
  select rowid into v_dept_rowid from scott.dept where deptno=10;
  dbms_output.put_line('v_char內容長度:'||length(v_char));
  dbms_output.put_line('v_varchar2內容長度:'||length(v_varchar2));
  dbms_output.put_line('v_nchar內容長度:'||length(v_nchar));
  dbms_output.put_line('v_nvarchar2內容長度:'||length(v_nvarchar2));
  dbms_output.put_line('10號部門的rowid:'||v_dept_rowid);
end;

日期型別：date、timestamp

declare
  v_date1 date:=sysdate;
  v_date2 date:=systimestamp;
  v_date3 date:='04-03月-2020';
  v_timestamp1 timestamp:=systimestamp;
  v_timestamp2 timestamp:=sysdate;
  v_timestamp3 timestamp:='04-03月-2020 12.20.40 上午';
begin
  dbms_output.put_line('v_date1:'||to_char(v_date1,'yyyy-MM-dd hh24:mi:ss'));
  dbms_output.put_line('v_date2:'||v_date2);
  dbms_output.put_line('v_date3:'||v_date3);
  dbms_output.put_line('v_timestamp1:'||v_timestamp1);
  dbms_output.put_line('v_timestamp2:'||v_timestamp2);
  dbms_output.put_line('v_timestamp3:'||v_timestamp3);
end;

布林型別：

declare
  v_flag boolean;
begin 
  v_flag:=true;
  if v_flag then
    dbms_output.put_line('條件為真');
  end if;
end;

%type、%rowtype型別：表示表中某一列的型別、表示一行記錄的型別

declare
  v_name emp.ename%type;
  v_salary emp.sal%type;
  v_hiredate emp.hiredate%type;
begin
  select ename, sal, hiredate, into v_name, v_salary, v_hiredate
  from emp where empno=&empno;
  dbms_output.put_line('僱員號:'||v_name);
  dbms_output.put_line('工資:'||v_salary);
  dbms_output.put_line('入職日期:'v_hiredate);
exception
  when no_date_found then
    dbms_output.put_line('你輸入的員工號不存在');
end;

使用%rowtype裝載一行記錄

declare
  emp_record emp%rowtype;
begin
  select * into emp_record from emp where empno=&empno;
  dbms_output.put_line('僱員號:'||emprecoord.ename);
  dbms_output.put_line('工資:'||emp_record.sal);
  dbms_output.put_line('入職日期:'||emp_record.hiredate);
exception
  when no_data_found then
    dbms_output.put_line('你輸入的員工號不存在');
end;

記錄型別：type

控制結構

if語句：if、if…else、if…elsif…else

declare
  v_sal number;
begin
  select sal into v_sal from emp where empno=7934;
  if v_sal<1000 then
    update emp set sal=sal+200 where empno=7934;
  elsif v_sal>=1000 and v_sal<2000 then
    update emp set sal=sal+150 where empno=7934;  
  else
    update emp set sal=sal+100 where empno=7934;
  end if;
end;

case語句：

declare
  v_sal number;
begin
  case 
    when XXX then
	  update...where...;
	when YYY then
	  update...where...;
	when MMM then
	  update...where...;
	when NNN then
      update...where...;
  end case;
end;

loop迴圈：loop、while…loop

declare
  v_i number:=1;
  v_sum number:=0;
begin   
  loop
    v_sum:=v_sum+v_i;
	v_i:=v_i;
    exit when v_i>100;
  end loop;
  dbms_output.put_line('1~100的和為:'||v_sum);
end;

while…loop

declare
  v_i number:=1;
  v_sum number:=0;
begin
  while v_i<=100 loop
    v_sum:=v_sum+v_i;
	v_i:=v_i+1;
  end loop;
  dbms_output.put_line('1~100的和為:'||v_sum);
end;

for迴圈–reverse遞減

declare
  v_sum number:=0;
  for v_i in 1..100 loop
    v_sum:=v_sum+v_i;
  end loop;
  dbms_output.put_line('1~100的和為:'||v_sum);

實現一個倒三角的九九乘法表

begin
 for v_i reverse 1..9 loop
   for v_j in 1..v_i loop
     dbms_output.put_line(v_i||'*'||v_j||'='||v_i*v_j||'');
   end loop;
   dbms_output.put_line('');
   end loop;
end;

exit：結束整個迴圈

continue：結束當次迴圈

goto語句：跳轉語句

declare
begin
  for v_i in 1..5 loop
    if
	  goto
	  
	end if;
  end loop;

end;

異常處理

exception 
  when...others then

end;
  • others：捕獲所有異常
  • sqlerrm：輸出異常資訊
  • sqlcode：輸出異常程式碼

使用者自定義異常

XXX exception;
pragma exception_init(XXX, -20000~-29999);
  • raise：丟擲異常
declare 
  v_myexp

遊標

隱式遊標：系統自動操作。通過隱式遊標屬性獲得SQL語句

sql%isopen
sql%found
sql%notfound
sql%rowcount

資料更新影響行數判斷–

begin
  update employee set deptno=20 where ename like'%s%';
  if sql%rowcount = 0 then
    dbms_output.put_line('資料更新失敗');
  else
    dbms_output.put_line('資料已更新'||sql%rowcount||'行');
  end if;
end;

根據員工編號查詢員工資訊–

declare
  v_empno emp.empno%type;
  v_emp emp%rowtype;
begin
  v_empno:=&職員編號;
  select * into v_emp from emp where empno=v_empno;
  if sql%found then
    dbms_output.put_line('職員的姓名是'||v_emp.ename);
  end if;
exception
  when no_data_found then
    dbms_output.put_line('該編號的職員未找到');
end;

顯示遊標：使用者建立。用於處理select語句返回多行資料

定義遊標

  • cursor 遊標名稱 is 查詢語句;

開啟遊標

  • open 遊標名稱;

檢索遊標

  • fetch 遊標名稱 into 變數;

關閉遊標

  • close 遊標名稱；

查詢emp表中的員工編號和姓名

declare
  cursor cursor_emp is select empno, ename from emp;  --宣告遊標
  v_empno emp.empno%type;
  v_ename emp.ename%type;
begin
  open cursor_emp;   --開啟遊標
  loop
    fetch cursor_emp into v_empno, v_ename;   --檢索遊標指向資料給變數
    exit when cursor_emp%notfound;
	dbms_output.put_line('員工號:'||v_empno||'姓名:'||v_ename);
  end loop;
  close cursor_emp;
end;

使用loop迴圈檢索emp表

declare
  cursor cursor_emp is select empno, ename, sal from emp;
  v_emp cursor_emp%rowtype;
begin
  open cursor_emp;
  loop
    fetch cursor_emp into v_emp;
	exit when cursor_emp%notfound;
	dbms_output.put_line(cursor_emp%rowcount||''||v_emp.empno||''||v_emp.ename||''||v_emp.sal);
  end loop;
  close cursor_emp;
end;

使用while迴圈檢索遊標

declare
  cursor cursor_sal is
    select deptno, round(avg(sal),2)avgsal from emp group by deptno;
  v_sal sursor_sal%rowtype;
begin
  if cursor_sal%isopen then
    null;
  else
    open cursor_sal;
  end if;
  fetch cursor_sal into v_sal;   --遊標指向第一行
  while cursor_sal%found loop
    dbms_output.put_line(v_sal.deptno||' '||v_sal.avgsal);
	fetch cursor_sal into v_sal;   --把遊標指向下一行
  end loop;
  close cursor_sal;  
end;

使用for迴圈檢索遊標

declare
  cursor cursor_emp is select * from emp;
begin
  for v_emp in cursor_emp loop
    dbms_output.put_line(cursor_emp%rowcount||' '||v_emp.ename);
  end loop;
end;

引數化顯示遊標的使用–

declare
  cursor cursor_emp(p_empno emp.empno%type)is
    select * from emp where empno = p_empno;
  v_emp cursor_emp%rowtype;
begin
  open cursor_emp(7369);
  loop
    fetch cursor_emp into v_emp;
	exit when cursor_emp%notfound;
	dbms_output.put_line(v_emp.empno||''||v_emp.ename);
  end loop;
  close cursor_emp;
  open cursor_emp(7499);
  loop
    fetch cursor_emp into v_emp;
	exit when cursor_emp%notfound;
	dbms_output.put_line(v_emp.empno||''||v_emp.ename);
  end loop;
  close cursor_emp;
end;

需要修改遊標資料的遊標定義–for update 子句 行級鎖定

cousor cursor_emp is;
select * from emp for update;

需要修改多表查詢遊標資料的遊標定義

cursor cursor_emp is
select e.empno, e.ename, e.sal, d.dname from emp e, dept d
where e.deptno = d.deptno for update of e.sal;

避免死鎖的遊標定義

cursor cursor_emp is
select * from emp for update nowait;

修改遊標資料–where currend of 子句

declare
  cursor cursor_emp is select * from employee where comm is null for update;
begin 
  for v_emp in cursor_emp loop
    update employee set comm = 500 where current of currsor_emp;
  end loop;
  commit;
end;

遊標變數

定義遊標引用型別語法

  • type 遊標引用型別名稱 is ref cursor ;

宣告遊標變數語法

  • 遊標變數名稱 遊標引用型別；

開啟遊標變數語法

  • open 遊標變數 for 查詢語句;

檢索遊標變數語法

loop
  fetch 遊標變數 into 變數;
  exit when 遊標變數%notfound;
  ...
end loop;

通過遊標變數動態繫結

declare
  type ref_cursor_type is ref cursor;
  ref_cursor ref_cursor_type;
  v_emp emp%rowtype;
  v_dept dept%rowtype;
  v_choose varchar2(1):=upper(substr('&D或E',1,1));
begin
  if v_choose='E' then
    open ref_cursor for select * from emp;
    dbms_output.put_line('員工資訊');
    loop
      fetch ref_cursor into v_emp;
      exit when ref_cursor%notfound;
      dbms_output.put_line(v_emp.empno||' '||v_emp.ename);
    end loop;
  close ref_cursor;
  elsif v_choose='D'then
    open ref_cursor for select * from dept;
	dbms_output.put_line('部門資訊');
    loop
      fetch ref_cursor into v_dept;
      exit when ref_cursor%notfound;
      dbms_output.put_line(v_dept.deptno||' '||v_dept.dname);
    end loop;
    close ref_cursor;
  else 
    dbms_output.put_line('請輸入E或D'); 
  end if;
end;

PL/SQL高階應用

儲存過程的建立與管理procedure

create or replace procedure

各種許可權

-create any procedure 任意使用者建立儲存過程

  • create procedure
  • alter procedure
  • execute any procedure 執行任意儲存過程
  • execute procedure
  • drop any procedure

呼叫儲存過程–

  • execute|exec|call

通過儲存名稱對儲存過程的呼叫

create or replace procedure proc_test(p_str1 varchar2, p_str2 varchar2)
as
begin
  dbms_output.put_line(p_str1||p_str2);
end;

declare
  v_var1 varchar2(20):='Hello,';
  v_var2 varchar2(20):='Prodcedure!';
begin
  proc_test(v_var1, v_var2);
end;

引數模式

  • in：數值傳遞，常量或表示式，子程式中修改不會影響原始數值
  • out：初始值為null，只能是變數，子程式可以通過此變數將數值返回給呼叫處
  • in out：只能是變數，將值傳遞到子程式，同時將子程式中對變數的修改返回到呼叫處

in引數模式

create or replace procedure proc_in(p_empno in number)
as
  v_ename scott.emp.ename%type;
  v_sal scott.emp.sal%type;
begin
  select ename, sal into v_ename, v_sal from scott.emp where empno=p_empno;
  dbms_output.put_line('僱員的姓名是：'||v_ename||'工資是：'||v_sal);
exception
  when no_data_found then
    dbms_output.put_line('僱員編號未找到');    
end;

begin
  proc_in(7369);
end;

out引數模式

create or replace procedure proc_out(
  p_deptno number,
  p_num out number,
  p_avgsal out number)
as
begin
  select count(*)num, round(avg(sal),2)avgsal into p_num, p_avgsal
  from scott.emp where deptno=p_deptno;
exception
  when no_data_found then
    raise_application_error(-2000,'該部門編號不存在');
end;

declare
  v_num number;
  v_avgsal number;
begin
  proc_out(10, v_num, v_avgsal);
  dbms_output.put_line('10號部門的總人數為'||v_num||'平均工資為'||v_avgsal);
end;

in out引數模式

create or replace procedure proc_dept_dname_exist(
  p_io_value in out varchar2) is v_count number;
begin
  select count(*)into v_count from scott.dept where dname=p_io_value; 
  if(v_count>0)then
    p_io_value:='已存在';
  else
    p_io_value:='不存在'；
  end if;
end;  

declare
  v_io_value varchar2(20):='ACCOUNTING';
begin
  proc_dept_dname_exist(v_io_value);
  dbms_output.put_line('部門名稱ACCOUNTING'||v_io_value||'!');
end;

檢視儲存過程及其原始碼資訊

檢視當前使用者的子程式資訊–user_procedures：檢視當前使用者所有的儲存過程、函式資訊

select object_name, object_type from user_procedures;

檢視儲存過程定義內容–user_source：檢視當前使用者所有物件的原始碼

select name, text from user_source where type='PROCEDURE';
  • user_errors：檢視當前所有的儲存過程或函式的錯誤資訊

查詢儲存過程與資料庫物件的依賴關係–user_dependencies

select name, type, referenced_name from user_dependencies
where referenced_name='EMP'or referenced_name='DEPT';

重新編譯儲存過程–alter procedure…compile語句

alter procedure proc_dept_insert compile;

刪除儲存過程–drop procedure

drop procedure proc_test;

函式的建立與管理function

建立無參函式–

create or replace function func_hello
return varchar2
as
begin
  return'hello function!';
end;

建立有參函式–

create or replace function func_get_dname(p_deptno dept.deptno%type)
return varchar2
as
  v_dname dept.dname%type;
begin
  select dname into v_dname from scott.dept where deptno=p_deptno;
  return v_dname;
end;

呼叫函式

SQL語句中呼叫

select func_hello from dual;

select func_get_dname(10)from dual;

在PL/SQL塊中呼叫

declare
  v_info varchar2(100);
begin
  v_info:=func_hello;
  dbms_output.put_line('返回：'||v_info);
end;

declare
  v_no number;
  v_info varchar2(50);
begin
  v_no:=&no;
  v_info:=func_get_dname(v_no);
  dbms_output.put_line('部門名稱：'||v_info);
end;

with函式建立簡單函式

with function fun_isnumber(param in varchar2)
return varchar2
is
begin 
  if(to_number(param)is not null)then
    return 'Y';
  else
    retuen 'N';
  end if;
exception
  when others then 
    return'N';
end;
select fun_isnumber('abcd')from dual;

檢視當前使用者的所有函式和原始碼–user_source

select name, text from user_source where type='FUNCTION';

函式重編譯

alter function func_get_dname compile;

刪除函式

drop function func_hello;

觸發器

  • DML觸發器
  • 替代觸發器
  • 系統觸發器

dml觸發器

  • before|after：前觸發|後觸發

  • for each row：行級觸發器

建立一個針對emp表的語句級dml觸發器，要求在非工作時間禁止對emp表進行dml操作

create or replace trigger trig_emp_dml
  before insert or update or delete on scott.emp
begin
  if(to_char(sysdate,'DAY'))in('星期六','星期日')
    or(to_char(sysdate, 'HH24:MI')not between'08:30'and'17:30')then
    raise_application_error(-20002,'只要在工作時間進行操作。')；
  end if;
end trig_emp_dml;

通過scott使用者建立一個語句級dml觸發器，實現只有scott使用者才可以對emp表進行dml操作的許可權驗證功能

create or replace trigger trig_emp_authority
  before insert or update or delete on emp
begin
  if user <> 'SCOTT' then
    raise_application_error(-20001,'您無權操作emp表')
  end if;
end trig_emp_authority;

行級觸發器

  • :old. :new.

建立一個行級觸發器，對emp表工資的更新進行限制，要求加薪比例最高不能超過10%

create or replace trigger trig_emp_addsal
  before update of sal on emp
  for each row
declare
  v_scale number;
begin
  v_scale:=(:new.sal-:old.sal)/:old.sal;
  if v_scale>0.1 then
    :new.sal:=:old.sal*1.1;
	dbms_output.put_line('加薪不能超過10%，薪水更新成:'||：new.sal);
  end if;
end;

觸發器謂詞inserting、updating、deleting

對dept表執行一個操作日誌功能，當使用者對dept表操作時，自動在dept的日誌記錄表dept_log中儲存

create table dept dept_log(
  logid number,
  type varchar2(20)not null,
  logdate date,
  deptno number(2),
  dname varchar2(20)not null,
  loc varchar2(30)not null,
  constraint pk_logid primary key(logid));

create sequence seq_dept_log;

create or replace trigger trig_dept_dml
before insert or update or delete on dept
for each row
begin
  when inserting then
    insert into dept_log(log, type, logdate, deptno, dname, loc)
	  values(seq_dept_log.nextval,'INSERT',
	  sysdate,:new.deptno,:new,dname,:new.loc);
  when updating then
    insert into dept_log(log, type, logdate, deptno, dname, loc)
	  values(seq_dept_log.nextval,'UODATE',
	  sysdate,:new.deptno,:new,dname,:new.loc);
  when deleting then
    insert into dept_log(log, type, logdate, deptno, dname, loc)
	  values(seq_dept_log.nextval,'DELETE',
	  sysdate,:new.deptno,:new,dname,:new.loc);
  end case;

end;

執行順序：

語句前觸發器(before statement)->行級前觸發器(before row)
->更新操作
->行級後觸發器(after row)->語句級後觸發器(after statement)

follows子句指定觸發器的執行順序

create or replace trigger dept_insert_one
before insert on dept 
for each row
begin
  dbms_output.put_line('執行第1個觸發器');
end;

create or replace trigger dept_insert_two
before insert on dept
for each row
follows dept_insert_one
begin
  dbms_output.put_line('執行第2個觸發器');
end;

複合觸發器：

  • before statement is 語前
  • before each row is 行前
  • after statement is 語後
  • after each row is 行後

複合觸發器的使用

替代觸發器instead of ：建立在檢視上的觸發器

建立替代觸發器來實現試圖資料的刪除操作

系統觸發器

  • on schema：表示對一個具體模式的的觸發
  • on database：對資料庫級的觸發，需要管理員許可權

實現對資料庫所有模式物件的ddl操作的日誌記錄

create table operate_log(
  logid number constraint pk_logid primary key, --主鍵標識
  operater varchar2(50),  --操作者名稱
  operate_date date,  --操作時間
  object_name varchar2(50),  --物件名稱
  object_type varchar2(50),  --物件型別
  object_owner varchar2(50)  --物件所有者名稱
  );
create sequence seq_operate_log;
  
create or replace trigger trig_object_ddl
  after ddl on database
begin
  insert into operate_log(logid, operate_date, object)
end;

建立一個監控使用者登入及登出的系統觸發器

create table 


create or replace trigger trig_userlogon
  after logon

監控使用者登出的系統觸發器

觸發器的查詢

  • user_triggers：當前的
  • all_triggers：可以訪問的
  • bda_triggers：所有的

禁用觸發器

alter trigger trig_object_ddl disable;

重新編譯觸發器

alter trigger trig_object_ddl compile;

刪除觸發器

drop trigger trig_object_ddl;

重點：

  • 什麼是資料庫、關係型資料庫
  • 表空間的建立、管理
  • 使用者的建立、管理
  • 許可權的授予、回收
  • 表
  • 事務四大特性
  • 事務的併發
  • 資料庫的隔離級別
  • 查詢
  • 模式、檢視、序列
  • PL\SQL語法

輸出所有比本部門平均工資高的員工資訊

create or replace procedure p_test(v_deptno in number)
as
  cursor cursor_emp is select empno, ename, sal from emp where sal > (select avg(sal) from emp where deptno=v_deptno) and deptno = v_deptno;
  v_empno emp.empno%type;
  v_ename emp.ename%type;
  v_sal emp.sal%type;
  a_sal number;
begin
  select avg(sal)into a_sal from emp where deptno=v_deptno;
  dbms_output.put_line('本部門平均為:'||a_sal);
  dbms_output.put_line('比本部門平均工資高的員工為');
  open cursor_emp; 
  loop
    fetch cursor_emp into v_empno, v_ename, v_sal;  
    exit when cursor_emp%notfound;
	dbms_output.put_line('員工號:'||v_empno||' 姓名:'||v_ename||' 工資:'||v_sal);
  end loop;
  close cursor_emp;
end;

begin
  p_test(10);
end;

相關文章