Oracle復習筆記
/*----------------------------------------常用命令(sqlplus環境)----------------------------------------*/
conn scott/tiger@orcl [as sysdba] || [as sysoper] --切換用戶
show user --當前用戶
passw[ord] --修改密碼
disc --斷開連接
clear scr --清屏
start || @ --運行sql腳本
edit --編輯指定sql腳本
spool --sqlplus截屏(sql>spool d:\spool.sql; sql>spool off;)
exit || quit --退出(執行後會commit)
& --動態輸入值
set linesize --設置顯示行寬度(默認80字節)
set pagesize --設置每頁顯示的行數目(默認14行)
set colsep | --設置列與列之間的分割符號
set echo on --設置運行命令是是否顯示語句
set feedback on --設置顯示“已選擇XX行”
set serveroutput on --啟用輸出(/表示結束PL-SQL塊)
set heading on --設置顯示列名
set timing on --顯示執行速度
set time on --顯示當前時間
set autotrace on --設置允許對執行的sql進行分析
call pro||fun; --調用存儲過程或函數
--給一個事物命名
set transaction name tname
--指定一個事物使用回滾段
set transaction use rollback segment 回滾段名
--如果在設置隔離級前有更新表t1沒有提交，在設置隔離級後，更新表會等待，先前的提交後，隔離級裡的會報錯
set transaction isolation level serializable
--默認情況的設置，如果在設置隔離級前有更新表t1沒有提交，在設置隔離級後，更新表會等待，先前的提交後，隔離級裡的會執行
set transaction level read commited
--只能讀，不能進行dml操作
set transaction read only
--可以進行dml操作默認(默認)
set transaction read write
/*----------------------------------------導入(imp)、導出(exp)----------------------------------------*/
exp
將數據庫內的各對象以二進制方式下載成damp文件，方便數據遷移。
buffer：下載數據緩沖區，以字節為單位，缺省依賴操作系統
consistent：下載期間所涉及的數據保持read only，缺省為n
direct：使用直通方式 ，缺省為n
feedback：顯示處理記錄條數，缺省為0，即不顯示
file：輸出文件，缺省為expdat.dmp
filesize：輸出文件大小，缺省為操作系統最大值
indexes：是否下載索引，缺省為n，這是指索引的定義而非數據，exp不下載索引數據
log：log文件，缺省為無，在標准輸出顯示
owner：指明下載的用戶名
query：選擇記錄的一個子集
rows：是否下載表記錄
tables：輸出的表名列表
導出整個實例
exp system/admin file=oradb.dmp log=oradb.log full=y consistent=y direct=y;
導出指定用戶所有對象
exp dbuser/oracle file=dbuser.dmp log=dbuser.log owner=dbuser buffer=4096000 feedback=10000
導出表
exp dbuser/oracle file=dbuser.dmp log=dbuser.log tables=table1,table2 buffer=4096000 feedback=10000
imp
將exp下載的dmp文件上載到數據庫內。
buffer：上載數據緩沖區，以字節為單位，缺省依賴操作系統
commit：上載數據緩沖區中的記錄上載後是否執行提交
feeback：顯示處理記錄條數，缺省為0，即不顯示
file：輸入文件，缺省為expdat.dmp
filesize：輸入文件大小，缺省為操作系統最大值
fromuser：指明來源用戶方
ignore：是否忽略對象創建錯誤，缺省為n，在上載前對象已被建立往往是一個正常現象，所以此選項建議設為y
indexes：是否上載索引，缺省為n，這是指索引的定義而非數據，如果上載時索引已建立，此選項即使為n也無效，imp自動更新索引數據
log：log文件，缺省為無，在標准輸出顯示
rows：是否上載表記錄
tables：輸入的表名列表
touser：指明目的用戶方
導入整個實例
imp system/admin file=oradb.dmp log=oradb.log full=y buffer=4096000 commit=y ignore=y feedback=10000
導入指定用戶所有對象
imp dbuser/oracle file=dbuser.dmp log=dbuser.log fromuser=dbuser touser=dbuser2 buffer=2048000 commit=y ignore=y feedback=10000
導入表
imp dbuser2/oracle file=user.dmp log=user.log tables=table1,table2 fromuser=dbuser touser=dbuser2 buffer=2048000 commit=y ignore=y feedback=10000
/*----------------------------------------用戶操作----------------------------------------*/
1)創建用戶並指定表空間
create user lee identified by 123 [default tablespace users];
2)修改用戶(密碼或指定表空間)
alter user lee [identified by newpwd] || [default tablespace users];
3)鎖定用戶
alter user lee account lock;
4)解鎖用戶
alter user lee account unlock;
5)刪除用戶[含所有對象]
drop lee [cascade];
/*----------------------------------------權限操作----------------------------------------*/
授權時加with admin option可以使權限傳遞,當最上級回收權限時,系統權限不會被級聯回收,對象權限會被級聯回收.
1)授予系統權限
grant connect,resource to lee [with admin option];
2)授對象權限
grant all on scott.emp to lee [with admin option];
3)撤銷權限
revoke resource from lee;
revoke update,delete on scott.emp from lee;
/*----------------------------------------角色操作----------------------------------------*/
connect角色具有一般應用開發人員需要的大部分權限，當建立了一個用戶後，
多數情況下，只要給用戶授予connect和resource角色就夠了。
connect角色具有以下系統權限：
alter session create cluster create database link
create session create view create sequence
resource角色具有應用開發人員所需要的其他權限，比如建立存儲過程、觸發器等。
這裡需要注意的是resource角色隱含了unlimited tablespace系統權限。
resource角色包含以下系統權限：
create cluster create indextype create table
create sequence create type create procedure
create trigger
1)創建角色
create role lee not identified;
create role lee identified by tiger;
2)刪除角色
drop role 角色名;
/*----------------------------------------序列操作----------------------------------------*/
--創建序列
create sequence orcl_seq
increment by 1 --每次增長幅度[默認:1]
start with 1 --開始時的序列號[默認:1]
maxvalue 999 --限制生成的最大值
minvalue 1 --限制生成的最小值
nocycle --達到最大值後,重新生成序列,[默認:nocycle]
cache 20; --預先分配20個空間以便更快生成序列
--刪除序列
drop sequence orcl_seq;
--使用序列
select orcl_seq.nextval from dual;
--查看序列的當前值(至少使用1次後才可查詢)
select orcl_seq.currval from dual;
/*----------------------------------------表操作----------------------------------------*/
--建表
create table tb(
id number(10) primary key,
name varchar2(20),
age number(3) default 0 not null
--constraint CK_name check(name not in ('@','#','$'))
) tablespace system;
--插入數據
insert into tb values(orcl_seq.nextval,'lee',26);
--修改列
alter table tb modify age default 1
modify name not null;
--添加約束
alter table tb add constraint CK_name check(name not in ('@','#','$'))
add constraint UQ_name unique(name);
--alter table tb add constraint FK_? foreign key(?) references tb2(?);
--刪除約束
alter table tb drop constraint UQ_name;
--修改列名
alter table tb rename column name to tname;
--修改表名(sqlplus)
rename tb to tab;
--刪除表
drop table tab;
--復制表(不會復制約束)
create table copy_tb as select ename,sal from scott.emp [where 1=2];
--多行插入(將查詢結果集直接插入現有表中)
insert into copy_tb select ename,sal from emp where ename = 'KING';
/*----------------------------------------視圖操作----------------------------------------*/
視圖是一張虛擬表,是對基表數據的引用,
如果基表數據發生改變，視圖中的數據也隨之改變,
對視圖成功的增刪改操作，也將影響基表的數據.
實際開發中，一般只對視圖作查詢.
--創建視圖
create or replace view v_emp
as
select * from emp
with read only;
--刪除視圖(注：對視圖的DML操作會影響原表的數據)
drop view v_emp
/*----------------------------------------索引操作---------------------------------------*/
索引基於表的列創建，給經常用於where條件、分組、排序的列添加索引
1)標准索引
create index 索引名 on 表名(列名);
2)唯一索引，Oracle自動為主鍵、唯一鍵創建唯一索引
create unique index 索引名 on 表名(列名);
3)組合索引，一般用於多個條件的組合查詢
create index 索引名 on 表名(列名1,列名2...);
4)反向鍵索引，按字節形式將數據反轉,用於經常從後匹配的列
create index 索引名 on 表名(列名) reverse;
5)基於函數的索引，在應用函數的列上創建索引
--eg 經常按入職月份查詢員工信息，請創建索引
create index ix_hiredate1 on emp(to_char(hiredate,'fmmm'));
--注意：Oracle自動根據查詢語句應用索引進行優化
select * from emp where to_char(hiredate,'fmmm')='2';
6)刪除索引
drop index 索引名;
/*----------------------------------------事務提交----------------------------------------*/
1、概念
1）事務是1個最小的執行單元（邏輯單元）
2）該單元包含1個或1組數據庫操作（增刪改）
3）該組操作要麼同時成功，要麼同時失敗
2、特點（ACID）
A- 原子性，事務是不允許再分的單元
C- 一致性，事務前後的數據應該保持一致
I- 隔離性，事務之間是相互獨立的
D- 永久性，事務提交後，對數據庫的影響是永久的，無法回滾
Oracle中的事務
1）1次連接=1次會話session=1個事務
2）一旦遇到commit或rollback，則提交或回滾該會話中所有未提交的操作
3）對於DDL語句（create/alter/drop），執行成功後相當於執行了commit
--事務提交
commit;
--設置事務回滾點
savepoint p1;
--事務回滾
rollback;
rollback to p1;
--刪除表所有數據(不允許回滾)
truncate table tb;
/*----------------------------------------行級鎖----------------------------------------*/
1）Oracle自動為 insert/delete/update/select...for update 操作應用行級鎖
一旦數據被鎖住,就不允許其他會話進行操作,直到commit或rollback.
2）用於鎖定查詢結果集 select * from emp for update;
3）用於檢測數據是否被鎖定,以及限制用戶等待的時間.
select * from emp for update nowait; --不等待
select * from emp for update wait 10; --等待10S
/*----------------------------------------表空間操作----------------------------------------*/
--授權創建表空間系系統權限
grant create tablespace to test2;
--創建表空間
create tablespace myspace
datafile 'd:\mydata.dbf'
size 20m
autoextend on
next 3m
maxsize 100m;
--擴展表空間
alter tablespace myspace add datafile 'd:\mydata2.dbf' size 30m;
alter database datafile 'd:\mydata.dbf' resize 35m;
alter database datafile 'd:\mydata2.dbf' autoextend on next 3m maxsize 100m;
--刪除表空間(including contents and datafiles物理文件會一起刪除)
drop tablespace myspace including contents and datafiles;
/*----------------------------------------並集、交集、減集----------------------------------------*/
/*union並集(或的關系,不包含重復記錄),union all包含重復記錄 */
select * from emp where sal > 2500 union select * from emp where job = 'MANAGER';
/*intersect交集(與的關系)*/
select * from emp where sal > 2500 intersect select * from emp where job = 'MANAGER';
/*munus減集 */
select * from emp where sal > 2500 minus select * from emp where ename = 'MANAGER';
/*----------------------------------------常用函數----------------------------------------*/
--分組函數：針對每組數據返回1個結果
select count(*) 總人數,max(sal) 最高工資,min(sal) 最低工資,sum(sal) 工資總和,avg(sal) 平均工資 from emp;
select count(*) 公司總人數,count(mgr) 有上級的人數 from emp;
select deptno,avg(sal) ageSal from emp group by deptno having avg(sal)>=2000 order by avg(sal) desc;
--日期函數：針對每行數據返回1個結果
select to_date('1-5月-13') - sysdate 天 from dual;
select ename,months_between(sysdate,hiredate)/12 "工齡(年)" from emp;
select ename,hiredate 入職時間,add_months(hiredate,3) 轉正日期 from emp;
select * from emp where hiredate = last_day(hiredate);
select * from emp where extract(month from hiredate) = 5;
--字符函數
select length('oracle') from dual;
select lengthb('你好') from dual;
select substr('[email protected]',7,4) from dual;
select instr('oracle','a') from dual;
select substr('&email',1,instr('&email','@')-1) 用戶名 from dual;
select replace('ddl','dl','ml') from dual;
select length('abcabca') - length(replace('abcabca','a')) from dual;
select chr(65) from dual;
select ascii('A') from dual;
select lpad('A',5,0),rpad('A',5,0) from dual;
--數學函數
select ceil(9.1) from dual;
select floor(9.1) from dual;
select round(1.5),round(1.55,1),round(155,-1) from dual;
select trunc(155.55) from dual;
--分析函數(用於數據的排名統計)
select ename,sal,
row_number() over(order by sal desc) row_number,
rank() over(order by sal desc) rank,
dense_rank() over(order by sal desc) dense_rank
from emp;
--轉換函數
select to_char(sysdate,'yyyy-MM-dd hh24:mm:ss day') from dual;
select to_char(sysdate,'yyyy"年"fmmm"月"dd"日"') from dual;
select to_date('2013-5-1 15:33:40','yyyy-mm-dd hh24:mi:ss') from dual;
select to_number('000123') from dual;
select nvl(null,'is null'),nvl('lee','is null') from dual;
select nvl2(null,'is null','not is null') from dual;
select '星期' || decode(7,1,'一',2,'二',3,'三',4,'四',5,'五',6,'六',7,'日') from dual;
--case when的使用
select ename,sal,
case
when sal = (select sal from emp where ename = 'KING') then 'BOSS'
when sal between 3000 and 4999 then '太牛了'
else '太少了'
end 工資水准
from emp;
select ename,sal,
case ename
when 'KING' then 'BOSS'
else '員工'
end 工資水准
from emp;
/*----------------------------------------數據庫字典----------------------------------------*/
--查詢當前數據庫
select * from global_name;
--查詢當前用戶可以訪問的數據庫字典
select * from dict where comments like '%grant%';
--查詢用戶
select * from user_users;
select * from dba_users;
--查看序列
select sequence_name,sequence_owner from user_sequences;
select sequence_name,sequence_owner from all_sequences
[where sequence_owner = 'SCOTT'];
--查詢表(all_tables當前用戶表以及可操作表)
select table_name,tablespace_name from user_tables;
select table_name,tablespace_name from dba_tables;
select table_name,tablespace_name from all_tables;
--查看視圖
select * from user_views;
select * from dba_views;
--查看索引
select * from user_indexes;
select * from dba_indexes;
--查詢表空間
select tablespace_name from dba_tablespaces;
select tablespace_name from user_tablespaces;
--查詢當前用戶包含的角色
select * from user_role_privs;
--查詢所有系統權限
select * from system_privilege_map;
--查詢對象權限
select distinct privilege from user_tab_privs;
select distinct privilege from dba_tab_privs [where grantee = 'CONNECT'];
--查詢所有角色(dba)
select * from dba_roles;
--查詢指定用戶包含的角色(dba)
select * from dba_role_privs where grantee = 'SCOTT';
--查詢指定角色包含的系統權限(dba)
select * from dba_sys_privs where grantee = 'CONNECT';
--查詢指定角色包含的對象權限(dba)
select * from dba_tab_privs where grantee = 'CONNECT';
/*----------------------------------------PL-SQL----------------------------------------*/
SQL：結構化查詢語言（數據庫行業的標准）
T-SQL：事務的結構化查詢語言，是SQLServer對SQL的擴展
PL-SQL：過程語言和結構化查詢語言，是Oracle對SQL的擴展：
1、使用變量，使用流程控制結構
2、使用過程、函數封裝復雜的業務邏輯（變量、流程控制、SQL操作）
3、對Oracle基本的數據類型進行了擴展（屬性類型）
數據類型：
e_name emp.ename%type; --列類型(引用表中指定列的類型)
e_emp_row emp%rowtype; --行類型(引用表中行的類型)
v_bool boolean; --布爾類型 boolean(取值true,false,null,僅用於邏輯判斷，不能直接輸出)
異常處理：exception
1、others 能處理所有異常
2、select..into.. 如果未找到數據，則引發no_data_found異常
3、select..into.. 如果返回值過多，則引發too_many_rows異常
4、使用raise_application_error引發應用程序異常
1）raise_application_error(錯誤號,錯誤信息)
2）錯誤號在[-20999,-20000]之間
3）錯誤信息必須小於2048字節
語法：
[declare
--任何類型的變量在未賦值之前，默認為null
聲明變量、類型、游標...;]
begin
實現功能的代碼...;
--others可以捕獲所有異常
[exception when 異常類型
then 異常處理;]
end;
--查詢員工KING的編號和薪水(匿名塊)
declare
v_empno number;
v_sal number(10,2);
begin
select empno,sal into v_empno,v_sal from emp where ename = 'KING';
dbms_output.put_line('EMPNO:' || v_empno || ' SAL:' || v_sal);
end;
--loop、while、for循環
declare
v_num number(2) := 1;
begin
loop
dbms_output.put_line(v_num);
v_num := v_num + 1;
exit when v_num > 10;
--if v_num > 10 then exit; end if;
end loop;
end;
declare
v_num number(10) := 1;
begin
while v_num 1=1 loop
dbms_output.put_line(v_num);
v_num := v_num + 1;
end loop;
end;
declare
begin
--in後加reverse倒序,i值不可更改
for i in 1..10 loop
dbms_output.put_line(i);
end loop;
end;
/*----------------------------------------游標使用----------------------------------------*/
游標：指向查詢結果集的指針（對查詢結果集的引用）
一、隱式游標(例：SQL%rowcount)
1、Oracle自動為增刪改操作創建隱式游標，名稱統一為SQL
2、用於獲取最近的增刪改操作對數據庫的影響（在提交或回滾之前）
3、游標屬性
1）%rowcount 返回受影響的行數
2）%found 如果影響了數據，則返回true否則返回false
3）%notfound 如果沒影響數據，則返回true否則返回false
4）%isopen 如果游標打開，則返回true否則返回false（隱式游標始終返回false）
二、顯式游標(例：cursor my_cursor is select * from emp)
1、顯示游標必須在聲明部分顯式聲明
2、用於處理返回多行的查詢結果集，便於用戶逐行處理數據
3、游標屬性
1）%rowcount 返回游標所在的行號
2）%found 如果找到了數據（fetch成功），則返回true否則返回false
3）%notfound 如果沒找到數據（fetch失敗），則返回true否則返回false
4）%isopen 如果游標打開，則返回true否則返回false
三、for循環游標：簡化游標的操作（自動打開、提取數據、關閉）
declare
cursor mycursor
is
select * from emp;
begin
--'r'表示游標中結果集對應的行類型變量
for r in mycursor
loop
if(r.sal>=3000) then
dbms_output.put_line(mycursor%rowcount||'-'||r.ename||'-'||r.sal);
end if;
end loop;
end;
四、帶參數的游標：提高游標的靈活性
--eg 根據部門編號查詢員工信息
declare
--聲明參數，類型不能指定長度
cursor mycursor(d_no number)
is
select * from emp where deptno=d_no;
begin
for r in mycursor('&d_no') --傳參
loop
dbms_output.put_line(r.ename);
end loop;
end;
五、使用游標更新數據
1）使用select..for update nowait;給游標加鎖
2）使用where current of 游標名;限定更新游標所在行
--eg 給員工加薪（10+100,20+200,30+300）
declare
money number;
cursor mycursor
is
select * from emp for update nowait;--給游標加鎖
begin
for r in mycursor
loop
if(r.deptno=10) then
money:=100;
elsif(r.deptno=20) then
money:=200;
elsif(r.deptno=30) then
money:=300;
end if;
--where current of 游標名; 限定更新游標所在行
update emp set sal=sal+money where current of mycursor;
end loop;
end;
六、REF游標（動態游標）
1、用於處理運行時才能確定的查詢結果集
2、REF游標必須在聲明部分：
1）聲明REF游標類型，type 類型名 is ref cursor;
2）聲明REF游標變量，變量名 類型名;
3) 動態游標不能帶參數？
--查詢每個員工的姓名和薪水
declare
type my_refcursor_type is ref cursor;
my_refcursor my_refcursor_type;
emp_row emp%rowtype;
begin
open my_refcursor for select * from emp;
loop
fetch my_refcursor into emp_row;
exit when my_refcursor%notfound;
dbms_output.put_line(emp_row.ename || ':' ||emp_row.sal);
end loop;
close my_refcursor;
end;
/*----------------------------------------存儲過程、函數、復合類型(record,table)、包----------------------------------------*/
一、分類
1）過程 procedure，完成特定功能
2）函數 function，完成特定功能並返回1個結果
2、優點
1）模塊化，按業務功能進行封裝
2）重用性好，易於維護
3）執行效率高，減少網絡流量的占用
4）安全性高（涉及權限管理）
二、組成
1）聲明部分（必須的） create [or replace]...
2）可執行部分（必須的） begin...end;
3）異常處理部分（可選的） exception...
三、參數模式
1）輸入參數 in
接收用戶輸入，不允許在過程中修改，如果未指定參數模式默認為輸入參數
2）輸出參數 out
向用戶返回結果，必須聲明變量傳參
3）輸入輸出參數 in out
既接收用戶輸入，又向用戶返回結果
四、創建語法
過程：
create [or replace]
procedure 過程名[(參數列表)]
as | is
[變量列表;]
begin
可執行代碼;
[exception
when others then null;]
end;
函數：
create [or replace]
function 函數名[(參數列表)]
return 返回類型
as | is
[變量列表;]
begin
可執行代碼;
return 返回值;
[exception
when others then return null;]
end;
五、刪除子程序
drop procedure 過程名;
drop function 函數名;
--根據員工姓名查詢工資(過程)
create or replace procedure myproc1(e_ename in varchar2,e_sal out number)
is
v_sal emp.sal%type;
begin
select sal into e_sal from emp where ename = e_ename;
end;
declare
sal number(20);
begin
myproc1('KING',sal);
dbms_output.put_line('SAL:' || sal);
end;
--根據員工姓名查詢編號(函數)
create or replace function myfun1(e_ename varchar2) return number
is
v_empno emp.empno%type;
begin
select empno into v_empno from emp where ename = e_ename;
return v_empno;
end;
declare
empno emp.empno%type;
begin
empno := myfun1('KING');
dbms_output.put_line('EMPNO:' || empno);
end;
六、復合類型
記錄(record)：
--根據編號查詢員工姓名和薪水(復合類型——記錄)
create or replace procedure myproc2(e_empno number)
is
type record_type is record(v_ename emp.ename%type,v_sal emp.sal%type);
ename_sal_recode record_type;
begin
select ename,sal into ename_sal_recode from emp where empno = e_empno;
dbms_output.put_line('ENAME,SAL:' || ename_sal_recode.v_ename || ' ' || ename_sal_recode.v_sal);
end;
表(table)：
--根據員工姓名查詢該員工上級(復合類型——表)
create or replace procedure myproc3(e_ename varchar2)
is
type emp_table_type is table of emp.ename%type index by binary_integer;
emp_table emp_table_type;
begin
select e2.ename into emp_table(0) from emp e1,emp e2 where e2.empno = e1.mgr and e1.ename = upper(e_ename);
dbms_output.put_line('MGR:' || emp_table(0));
exception when no_data_found then
dbms_output.put_line('該員工不存在或沒有上級！');
end;
--取出emp表所有員工姓名(復合類型——表)
create or replace procedure myproc4
as
type emp_table_type is table of emp.ename%type index by binary_integer;
emp_table emp_table_type;
num number;
begin
select count(*) into num from emp;
dbms_output.put_line('EMP TABLE ALL ENAME:');
for i in 1..num loop
select e2.ename into emp_table(i) from (select rownum rn,e1.* from (select * from emp order by empno) e1) e2 where e2.rn = i;
dbms_output.put_line(emp_table(i));
end loop;
end;
七、程序包：用於封裝子程序、游標、變量等對象
1、組成
1）包規范 package
用於聲明公共成員和子程序規范（定義接口）
2）包主體 package body
用於聲明私有成員和實現子程序（定義類實現接口）
--包規范
create or replace package mypack1
is
procedure test1;
procedure test2;
end;
--包主體
create or replace package body mypack1
is
--test1
procedure test1
is
begin
dbms_output.put_line('my is test1');
end;
--test2
procedure test2
is
begin
test1();
dbms_output.put_line('my is test2');
end;
end;
begin
mypack1.test2();
end;
/*----------------------------------------動態sql----------------------------------------*/
注：execute immediate sql執行結果只能取出單行單列或多列,如多行必須使用游標
--根據員工姓名查詢薪水
declare
type my_record_type is record(e varchar2(20),s number);
strSql varchar2(100);
my_record my_record_type;
begin
strSql := 'select ename,sal from emp where ename = ''KING''';
execute immediate strSql into my_record;
dbms_output.put_line(my_record.e || ' ' || my_record.s);
end;
/*----------------------------------------分頁存儲過程----------------------------------------*/
--定義包
create or replace package pagePack as
type pageCursorType is ref cursor; --游標類型
procedure pageProc(
tableName varchar2, --表名
showField varchar2, --查詢字段
whereText varchar2, --查詢條件
orderText varchar2, --排序字段[asc] || [desc]
pageIndex number, --當前頁碼
pageSize number, --每頁顯示幾條
counter out number, --總記錄數
pageCount out number, --總頁數
pageCursor out pageCursorType --結果集
);
end pagePack;
--實現包體
create or replace package body pagePack as
procedure pageProc(
tableName varchar2, --表名
showField varchar2, --查詢字段
whereText varchar2, --查詢條件
orderText varchar2, --排序字段[asc] || [desc]
pageIndex number, --當前頁碼
pageSize number, --每頁顯示幾條
counter out number, --總記錄數
pageCount out number, --總頁數
pageCursor out pageCursorType --結果集
)
as
strSql varchar2(500);
wText varchar2(100);
oText varchar2(100);
begin
--判斷是否有where條件
if whereText is not null then
wText := ' where ' || whereText;
end if;
--判斷是否有order by排序
if orderText is not null then
oText := ' order by ' || orderText;
end if;
--拼接分頁sql語句
strSql := 'select * from (select rownum rn,' || showField || ' from (select * from ' || tableName || wText || oText ||' ) t where rownum <= :1) where rn > :2';
dbms_output.put_line(strSql);
--打開游標並取值(不用關閉)
open pageCursor for strSql using pageIndex * pageSize,(pageIndex - 1)* pageSize;
--拼接記錄數sql語句
strSql := 'select count(1) from ' || tableName || wText || oText;
dbms_output.put_line(strSql);
--執行動態sql獲取記錄數
execute immediate strSql into counter;
--計算總頁數
pageCount := floor((counter + pageSize - 1) / pageSize);
end;
end pagePack;
--創建視圖(rowtype需要rownun列,因此需要創建視圖,視圖中其他列需要制定別名否則報錯)
create or replace view viewEmp
as
select rownum rn,emp.* from emp;
--調用分頁存儲過程
declare
counter number;
pageCount number;
pageCursor pagePack.pageCursorType;
emp viewEmp%rowtype;
begin
pagePack.pageProc('emp','t.*','','',1,5,counter,pageCount,pageCursor);
dbms_output.put('COUNT:' || counter || ' PAGE_COUNT:' || pageCount);
dbms_output.new_line();
--這裡使用for循環遍歷游標會報異常,因為調用過程返回的游標已是打開的
loop
fetch pageCursor into emp; --類似java中ResultSet中rs.next();
exit when pageCursor%notfound;
dbms_output.put_line(emp.ename);
end loop;
close pageCursor;
end;
/*----------------------------------------觸發器----------------------------------------*/
語法：
create [or replace] trigger 觸發器名稱
after | before | instead of
[insert] [[or] update [of 字段列表]]
[[or] delete]
on 表名 | 視圖名 --觸發器語句（事件）
[referencing {OLD [as] old / NEW [as] new}] --指定old表和new表的別名
[for each row] --行級觸發器，每行都觸發
[when (條件)] --觸發器條件
begin
觸發器操作;
end;
--限制只有scott才能對emp進行DML操作
create or replace trigger trig1
before
insert or update or delete
on emp
begin
if user <> 'SCOTT' then
raise_application_error(-20000,'只有SCOTT才能對此表進行操作！');
end if;
end;
--記錄對scott用戶下emp表操作(用戶、操作時間、操作類型)
--擴展：如果是update操作記錄該用戶操作了哪些列？
create or replace trigger trig2
after
insert or update or delete
on emp
declare
action varchar2(10);
begin
if inserting then
action := 'insert';
elsif deleting then
action := 'update';
elsif updating then
action := 'update';
end if;
--system用戶有一張scott_emp_log表並賦予public
insert into system.scott_emp_log values(user,sysdate,action,orcl_seq.nextval);
end;
--使用觸發器對scott_emp_log表自動生成序號(插入數據id列寫0或不指定id列)
create or replace trigger trig3
before insert
on system.scott_emp_log
for each row
begin
select orcl_seq.nextval into :new.id from dual;
end;
--創建觸發器限制不允許修改員工獎金
create or replace trigger trig4
before update of comm
on emp
for each row
begin
if :new.comm <> :old.comm then
raise_application_error(-20000,'不允許修改comm字段！');
end if;
end;
示例6：使用觸發器更新視圖
--創建視圖
create or replace view view_test
as
select empno,empno||ename newname,sal from emp;
--創建觸發器
create or replace
trigger eg_trigger6
instead of update on view_test
begin
--將修改視圖的操作替換成修改基表的操作
update emp set ename=substr(:new.newname,5) where empno=:new.empno;
end;
update view_test set newname='7369LISHU' where empno=7369;
示例7：記錄刪除的對象
--創建日志表
create table droped_objects
(
object_name varchar2(30),
object_type varchar2(30),
drop_date date
);
--創建觸發器
create or replace
trigger eg_trigger7
after drop
on scott.schema --誰的操作
begin
insert into droped_objects values
(ora_dict_obj_name,ora_dict_obj_type,sysdate);
end;
create table T(tid number);
create sequence sq;
drop table T;
drop sequence sq;
--禁用觸發器
alter trigger 觸發器名稱 disable;
--啟用觸發器
alter trigger 觸發器名稱 enable;
--查看觸發器的內容
select line,text from user_source where name=upper('觸發器名稱');
/*----------------------------------------Oracle SQL：經典查詢練手第一篇----------------------------------------*/
--1. 列出至少有一個員工的所有部門。
select dname from dept where deptno in(select distinct deptno from emp);
select distinct d.dname from dept d,emp e where d.deptno = e.deptno;
select distinct d.dname from dept d inner join emp e on d.deptno = e.deptno;
--2. 列出薪金比“SMITH”多的所有員工。
select * from emp where sal > (select sal from emp where ename = 'SMITH');
--3. 列出所有員工的姓名及其直接上級的姓名。
select ename,(select ename from emp b where b.empno = a.mgr) from emp a;
select e1.ename,e2.ename from emp e1,emp e2 where e2.empno = e1.mgr
--4. 列出受雇日期早於其直接上級的所有員工。
select * from emp a where a.hiredate < (select hiredate from emp b where b.empno = a.mgr);
--5. 列出部門名稱和這些部門的員工信息，同時列出那些沒有員工的部門
select d.dname,e.* from dept d left join emp e on d.deptno = e.deptno;
--6. 列出所有“CLERK”(辦事員)的姓名及其部門名稱。
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno and e.job = 'CLERK';
--7. 列出最低薪金大於1500的各種工作。
select job,min(sal) from emp group by job having min(sal) > 1500;
--8. 列出在部門“SALES”(銷售部)工作的員工的姓名，假定不知道銷售部的部門編號。
select ename from emp where deptno = (select deptno from dept where dname = 'SALES');
select e.ename from emp e,dept d where e.deptno = d.deptno and d.dname = 'SALES';
--9. 列出薪金高於公司平均薪金的所有員工。
select ename from emp where sal > (select avg(sal) from emp);
--10. 列出與“SCOTT”從事相同工作的所有員工。
select ename from emp where job = (select job from emp where ename = 'SMITH');
--11. 列出薪金等於部門30中員工的薪金的所有員工的姓名和薪金。
select ename,sal,deptno from emp where sal in(select sal from emp where deptno = 30) and deptno <> 30;
--12. 列出薪金高於在部門30工作的所有員工的薪金的員工姓名和薪金。
select ename,sal from emp where sal > (select sum(sal) from emp where deptno = 30);
--13. 列出在每個部門工作的員工數量、平均工資和平均服務期限。
select (select dname from dept where deptno = emp.deptno) dname,count(*),round(avg(sal),2),round(avg(sysdate-hiredate)) from emp group by deptno;
--14. 列出所有員工的姓名、部門名稱和工資。
select ename,(select dname from dept where deptno = emp.deptno) dname,sal from emp;
--15. 列出所有部門的詳細信息和部門人數。
select deptno,dname,(select count(*) from emp where deptno = dept.deptno group by deptno) dnum from dept;
--16. 列出各種工作的最低工資。
select job,min(sal) from emp group by job;
--17. 列出各個部門的MANAGER(經理)的最低薪金。
select (select dname from dept where deptno = emp.deptno) dname,min(sal) from emp where job = 'MANAGER' group by deptno;
--18. 列出所有員工的年工資,按年薪從低到高排序。
select ename,(sal*12) yearsal from emp order by sal asc;
/*----------------------------------------Oracle SQL：經典查詢練手第二篇----------------------------------------*/
--1. 找出EMP表中的姓名（ENAME）第三個字母是A 的員工姓名。
select ename from emp where ename like '__A%';
--2. 找出EMP表員工名字中含有A 和N的員工姓名。
select ename from emp where ename like('%A%') and ename like('%N%');
--3. 找出所有有傭金的員工，列出姓名、工資、傭金，顯示結果按工資從小到大，傭金從大到小。
select ename,sal,comm from emp where comm is not null order by sal,comm desc;
4. 列出部門編號為20的所有職位。
select job from emp where deptno = 20;
--5. 列出不屬於SALES 的員工。
select ename from emp where deptno in(select deptno from dept where dname <> 'SALES');
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno and d.dname <> 'SALES';
6. 顯示工資不在1000 到1500 之間的員工信息：名字、工資，按工資從大到小排序。
select ename,sal from emp where sal not between 1000 and 1500 order by sal desc;
select ename,sal from emp where sal < 1000 or sal > 1500 order by sal desc;
7. 顯示職位為MANAGER 和SALESMAN，年薪在15000 和20000 之間的員工的信息：名字、職位、年薪。
select ename,job,(sal*12) yearsal from emp where job in('MANAGER','SALESMAN') and (sal*12) between 15000 and 20000;
8. 說明以下兩條SQL語句的輸出結果：
SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL; --有結果
SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL; --無結果
9. 讓SELECT 語句的輸出結果為
1. SELECT * FROM SALGRADE;
2. SELECT * FROM BONUS;
3. SELECT * FROM EMP;
4. SELECT * FROM DEPT;
5. ……
列出當前用戶有多少張數據表，結果集中存在多少條記錄。
select 'SELECT * FROM ' || tab.tname || ';' from tab;
select * from tab;
10. 判斷SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否報錯，為什麼？
--不會報錯，Oracle會檢測是否可轉為數字格式，如果可以將隱士轉換，不可則報錯。
/*----------------------------------------Oracle SQL：經典查詢練手第三篇----------------------------------------*/
/*Oracle分頁查詢*/
--三層嵌套分頁
select e2.* from (select e1.*,rownum rn from (select * from emp order by empno) e1 where rownum <= 5) e2 where e2.rn >0;
--減集分頁(不能排序？)
select * from emp where rownum<=5 minus select * from emp where rownum<=0;
--多表連接分頁查詢(第一層需要指定顯示列,列名不能用重復)
select b.* from
(select rownum rn,a.* from (select e.*,d.dname,d.loc from emp e,dept d where e.deptno = d.deptno order by e.empno) a
where rownum <=5) b where b.rn >=1;
/*查詢高於自己部門平均工資的員工信息*/
select e2.empno,e2.ename,e2.sal,e2.deptno,e1.deptavgsal
from emp e2,(select deptno,avg(sal) deptavgsal from emp group by deptno) e1
where e2.deptno = e1.deptno and e2.sal > e1.deptavgsal;
