1.關系型資料庫和結構化查詢語言sql
2.Oracle,DB2,Sybase,MS SQL ,Mysql
3.實體,屬性,關系 1521
4.oracle的服務:監聽服務(遠端通路需要)和資料庫服務(要啟動)
5.oracle的配置檔案:
listener.ora(監聽器服務的配置檔案)和
tnsname.ora(監聽器服務通路的配置檔案)連接配接資料庫的主機字元串(自定義:主機字元串包括遠端IP
和資料庫名了)
6.測試遠端連接配接
net manager(配置主機字元串和監聽器)
找安裝路徑的快捷方式:右擊net manager打開檔案
plsql工具:字型設定和顯示行号 關鍵字大寫
7.oracle的實體檔案
分為三類:資料檔案(.dbf)、控制檔案(.ctl)、日志檔案(.log)
8.oracle系統表(資料字典)
9.表空間(對應一個實體的磁盤存儲位置)
CREATE TABLESPACE j1706
DATAFILE 'E:\oracle\product\10.2.0\oradata\orcl\j1706.dbf'
SIZE 20M
AUTOEXTEND ON ;
使用者
授權
表
10.sql語句
SQL語句主要可以劃分為以下幾類:
DDL(Data Definition Language):資料定義語言,定義對資料庫對象(庫、表、列、索引)的操作。
CREATE、DROP、ALTER、RENAME、 TRUNCATE等
DML(Data Manipulation Language): 資料操作語言,定義對資料庫記錄的操作。
INSERT、DELETE、UPDATE
DCL(Data Control Language): 資料控制語言,定義對資料庫、表、字段、使用者的通路權限和安全級别。
GRANT、REVOKE等
Transaction Control:事務控制
COMMIT、ROLLBACK、SAVEPOINT等
(1)常用的資料類型 數值型(int、number)、字元型(char(固定長度)、varchar(可變長度)
varchar2(可變長度))、日期型date
(2)DDL(資料定義語言:表結構) 定義對資料庫對象(庫、表、列、索引)的操作
建表:
create table tb_person(
id int,
name varchar2(15),
sex char(3),
age int
);
查詢表:
select * from tb_person;
删除表:
drop table tb_person;
修改表;
增加列:
alter table tb_person add(phone varchar2(18),address varchar2(30));
改變列長度:
alter table tb_person modify(phone varchar2(20));
删除列:
alter table tb_person drop(phone,address);
修改列名:
alter table tb_person rename column phone to iphone7s;
修改表名:
rename tb_person to tb_people;
截斷表
當表結構必須保留, 而表資料不再需要的時候
TRUNCATE TABLE tb_person
(3)DML(資料操作語言) 定義對資料庫記錄的操作
插入:
insert into tb_person(id,name,sex,age)
values(1,'tom','男',20);
删除資料:
delete from tb_personn;或者DDL中的截斷: truncate table tb_person;
修改資料:
update tb_person set age=21;
update tb_person set age=21,id=2 where sex='男' and name='tom';
sql語句删除資料有幾種方式?分别是什麼?
答:
1. delete
-- DML語句,delete語句最小機關是行,可以通過where選擇删除,删除資料可以復原,
保留表結構。最慢
2. truncate
-- DDL語句,TRUNCATE是截斷表的所有資料,删除資料不可以復原,保留表結構。較快
3. drop
-- DDL語句,删除表結構和表所有資料,并且不能復原,慎用。最快
check 檢查限制
-- not null 非空限制(特殊的檢查限制)
unique 唯一限制
primary key 主鍵限制(非空且唯一)
foreign key 外鍵限制(解決資料備援問題)
列級限制:
create table tb_person(
id int primary key,
name varchar2(18) not null,
sex char(3) check(sex='男' or sex='女'),
age int check(age>=18 and age<60),
phone varchar2(20) unique,
address varchar2(30),
clazz_id int references tb_clazz(id) on delete cascade
);
create table tb_clazz(
id int primary key,
code varchar2(15),
name varchar2(15),
bzr varchar2(20)
);
外鍵中,當主表的記錄被子表參照時,主表記錄不允許被删除。
解決方案:
先删除字表資料,在删除父表資料。
先将字表關聯資料設為null,在删除父表資料
先将子表資料修改,在删除父表資料。
on delete cascade 當删除父表資料時,級聯删除子表資料。
on set null 當删除父表資料時,将關聯的資料設定為null
表級限制:
create table tb_student(
id int,
name varchar2(18),
sex char(3),
age int,
phone varchar2(18),
email varchar2(18),
address varchar2(18),
clazz_id int,
constraints tb_students_pk primary key(id),
check(name is not null),
check(sex='男' or sex='女'),
check(age>18 and age<50),
unique(phone),
unique(email),
constraints tb_students_fk foregin key(clazz_id) reference tb_clazz(id)
);
自定義限制名:(限制 限制名 限制類型)
constraints tb_students_pk
資料庫建議建表:
外鍵以表級限制,其他用列級限制。
限制的維護(添加、删除):
alter table tb_student add primary key(id);
删除限制根據限制名删除:
alter table tb_student drop constraints tb_students_pk;
複合限制(隻能在表級中定義):
primary key(year,month)
(5)nvl函數 nvl(第一個參數,第二個參數),
如果第一個參數為null,則取第二個參數
定義字段的别名as
select empno as eID,ename,sal,sal*12 as yearsal from scott.emp;
distinct關鍵字去除重複資料:
select distinct deptno from scott.emp;
比較運算符:
select *from scott.emp where sal>=800 and sal<=1600;
select * from scott.emp where sal between 800 and 1600;
select * from scott.emp where deptno=20 or deptno=30;
select * from scott.emp where deptno in(20,30);
模糊查詢like:
%比對所有 _比對一個字元
select * from scott.emp where ename like 's%';
select * from scott.emp where ename like '%s';
select * from scott.emp where ename like '%s%';
select * from scott.emp where ename like '_s%';
優先級規則:先and後or
對結果排序 order by asc(升序 預設) desc(降序)
select * from scott.emp order by sal asc;
select * from scott.emp order by sal desc;
(6)多表連接配接
内連接配接(等值連接配接)
兩個表(或連接配接)中某一資料項相等的連接配接稱為内連接配接。
SELECT FROM dept d INNER JOIN emp e ON d.deptno = e.deptno';
外連接配接(非等值連接配接)
用于查詢一張表在另一張表中沒有關聯資料的資訊
外連接配接分為三種:
左外連接配接(LEFT OUTER JOIN)
右外連接配接(RIGHT OUTER JOIN)
全外連接配接(FULL OUTER JOIN)
-- 左外連接配接:+号在右邊,左邊的表的所有資料都要顯示,如果右邊表沒有對應的資料,則補Null
-- 右外連接配接:+号在左邊,右邊的表的所有資料都要顯示,如果左邊表沒有對應的資料,則補Null
-- 全外連接配接:兩張表的所有資料都要全部顯示
備份一張表(隻備份資料,不備份限制):
create table tb_emp as select * from scott.emp;
select e.empno,e.ename,e.mgr,t.ename
from scott.emp e,tb_emp t
where e.mgr=t.empno and e.empno='7369';
自連接配接:
select e.empno,e.ename,e.mgr,t.ename
from scott.emp e,scott.emp t
where e.mgr=t.empno and e.empno='7369';
(7)組函數:(組函數都會忽略NULL值)
對一組值進行運算,并傳回單個值,也叫聚合函數。
count(*|列名) 統計行數:
select count(*) from scott.emp;
select count(comm) from scott.emp;
sum(數值類型列名) 求和:
select sum(sal) from scott.emp;
avg(數值類型列名) 平均值:
select avg(sal) from scott.emp;
max(列名) 最大值:
select max(sal) from scott.emp;
min(列名) 最小值:
select min(sal) from scott.emp;
(8)分組 group by
把該列具有相同值得多條記錄當成一條記錄處理,最後隻輸出一條記錄。
分組函數忽略空值。結果集隐式按升序排列,
如果需要改變排序方式可以使用order by子句。
按部門分組:
select deptno from scott.emp group by deptnot;
按工作崗位分組:
select job from scott.emp group by job;
group by子句的真正作用在于與各種組函數配合使用:
select deptno,count(*),sum(sal),avg(sal),max(sal),min(sal)
from scott.emp group by deptno;
限定組的結果:having子句
having子句用來對分組後的結果進行條件過濾。
having和where的差別;
where和having都是用于條件過濾。
where在分組前進行條件過濾,having子句是在分組後進行條件過濾,
where子句中不能使用聚合函數,having子句可以使用聚合函數。
select deptno,count(*),sum(sal),avg(sal),max(sal),min(sal)
from scott.emp group by deptno having sum(sal)>9000
order by sum(sal) desc;
(9)子查詢
子查詢就是把多條語句寫成一條,子查詢執行的時候先執行子查詢
再執行主查詢:
select * from scott.emp where sal>(
select sal from scott.emp where ename='allen'
);
子查詢需要注意的問題:單行子查詢傳回多行
多行比較運算符:
In:與清單中的一個值相等(包含)
any:與子查詢傳回的每一個值比較
all:與子查詢傳回的所有值比較
in:
select * from scott.emp where sal in(
select min(sal) from scott.emp group by deptno
);
any與子查詢傳回的每一個值比較 >any 大于最小的 <any 小于最大的
select * from scott.emp where sal>any(
select min(sal) from scott.emp group by deptno
);
all與子查詢傳回的所有值進行比較 >all 大于最大的 <all 小于最小的
select * from scott.emp where sal>all(
select min(sal) from scott.emp group by deptno
);
(10)集合運算:從多個結果集中提取資料
union 把兩個結果集的資料合起來,然後幹掉重複的
select deptno from scott.dept union
select deptno from scott.emp;
union all:把兩個結果集的資料合起來
select deptno from scott.dept union all
select deptno from scott.emp;
intersect:把兩個結果集的資料合起來,然後幹掉重複的,
再找兩個查詢中都出現的記錄
select deptno from scott.dept intersert
slect deptno from scott.emp;
minus:判斷資料存在第一查詢的結果集,而·不存在第二查詢的資料集
select deptno from scott.dept minus
slect deptno from scott.emp;
面試題:
select r.id,r.R1,nvl(b.R2,'null') from r,b where r.id=b.id(+)
union
selet b.id,nvl(r.R1,'null'),b.R2 from r,b where r.id(+)=b.id;
标準外連接配接:
select r.id,r.R1,b.R2 from r left outer join b on r.id=b.id
union
select b.id,r.R1,b.R2 from r right outer join b on b.id=r.id;
全外連接配接:
select nvl(r.id,b.id) as id,r.R1,b.R2 from r full outer join b on
r.id=b.id;
(11)rownum 僞列 ‘結果集’中産生的序列
select rownum,deptno,dname,loc from scott.dept;
隻有存在rownum=1的記錄,才可能存在rownum=2的記錄
利用rownum分頁:
select * from(
select rownum as tempid,empno,ename from scott.emp
)t1
where t1.tempid between 6 and 10;
rowid:一般來說每一行資料對應一個rowid,而且固定且唯一。
在這一行存入資料庫時就确定了。可以了解為java對象中的記憶體位址。
可以利用rowid來查詢記錄,而且通過rowid查詢速度最快的查詢方法。
rowid隻有在表發生移動(比如表空間變化,資料導入/導出後)才會發生變化。
面試題:
删除重複資料(删除所有重複資料
/删除重複資料但保留一條(保留最大rowid或者最小)):
删除所有資料;
delete from tb_test where name in(
select name from tb_test group by name,age having count(*)>1
);
删除資料,保留一條資料(第一種方法);
create table tb_tmp as select distinct name,age from tb_test;
truncate table tb_test;
insert into tb_test(name,age) select name,age from tb_tmp;
删除資料,保留一條資料(方法二):
delete from tb_test where rowid not in(
select max(rowid) from tb_test group by name,age
);
(12)常用函數
dual是oracle提供的一個虛表
select length('hello') from dual;
常用函數:
lower把大寫轉成小寫 upper把小寫轉大寫:
select upper('helloworld') from dual;
select lower('HELLOWORLD') from dual;
select * from scott.emp where ename='smith';
select * from scott.emp where lower(ename)='smith';
initcap使串中的所有單詞首字母變成大寫:
select initcap('sql course') from dual;
concat 連接配接兩個字元串:
select concat('Hello','World') from dual;
substr 取字元串,從start開始,取count個:
select substr('HelloWorld',1,5) from dual;
從4開始取到末尾:
select substr('Helloworld',4) from dual;
length 傳回字元串的長度:
select length('HelloWorld') from dual;
instr 在一個字元串中搜尋指定的字元,傳回發現指定的字元的位置,從1開始:
select instr('HelloWorld','l') from dual;
trim 删除首尾的空字元串
select trim(' HelloWorld ') from dual;
replace 替換:
select replace('HelloWorld','ll','ff') from dual;
round 四舍五入;
select round(45.926,2) from dual;
trunc 截斷:
select trunc(45.926,2) from dual;
mod 取模:
select mod(1600,300) from dual;
decode函數:
select ename,job,sal 基本工資,decode(job,
'salesman', sal*0.9,
'manager',sal*0.85,
'clerk',sal+100,
'pre
sident',sal
)as 實發工資
from scott.emp;
面試題:
select name,
sum(decode(t.course,'JDBC',t.grade,0)) JDBC,
sum(decode(t.course,'Hibernate',t.grade,0)) Hibernate,
sum(decode(t.course,'Spring',t.grade,0)) Spring
from tb_course t
group by t.name;
11.資料庫對象
(1)資料庫對象包括: 表、序列、同義字、資料庫連結、視圖等。
對象的特點:可以給其他對象使用,名字不能重複。
(2)序列:自動生成的唯一序列号,常用在主鍵自動生成。
建立序列:
create seqeuence s_tb_student;
删除序列:
drop seqeuence s_tb_student;
序列的兩個屬性currval,nextval:
select s_tb_student.currval from dual;
select s_tb_student.nextval from dual;
使用序列:
insert into tb_student(id)values(s_tb_student.nextval);
序列不會随着rollback復原,下面這些情況時sequence值可能産生間隙:
復原發生;
序列用于多個表(不建議),建議一張表使用一個序列;
系統當機。
(3)索引
面試:如何優化你的資料庫查詢?
1.資料庫的查詢方式?
--全表掃描 select * from tb_student 慢
--利用索引掃描 快
--共享語句 最快(oracle有個復原段,臨時表空間)
索引 index
作用:在資料庫中用來加速對表的查詢
原理:通過使用快速路徑通路方法快速定位資料,減少了磁盤的I/O
特點:與表獨立存放,但不能獨立存在,必須屬于某個表
由資料庫自動維護,表被删除時,該表上的索引自動被删除。
索引的建立:
自動:當在表上定義一個primary key或者unique限制1條件時,資料庫會
自動建立一個對應的索引。
手動:使用者可以建立索引以加速查詢。
create index i_tb_student_name on tb_student(name);
查詢的時候使用索引:
select * from tb_student where name='Alice';
删除索引:
drop index i_tb_student_name;
當建立索引的時候,oracle會預設建立一個和目前表相關的索引頁,
而索引頁中儲存了索引字段和真實的磁盤位址,當使用者發送sql語句帶了索引時,
oracle會到索引頁中查詢索引字段,直接定位磁盤IO,提取資料。
是以索引資料快于全表掃描。
索引的維護
1.建立索引後,查詢的時候需要在where條件中帶索引字段才可以使用索引。
2.在經常查詢的字段上面建立索引。不要在所有字段上建立索引。
3.因為索引是用來加速查詢速度的,如果一張表經常做insert,delete,
update ,而很少做select,不建議建立索引
如果一張表字段很少,不建議建立索引。
4.索引是由oracle自動維護的。索引使用久了會産生索引碎片
(磁盤碎片),影響查詢效果,是以使用久了需要手動進行維護(删除在重建)
sql語句的優化:
多使用共享語句,盡量使你的sql語句能夠使用索引。
怎樣使sql語句能夠使用到索引呢?
當sql語句中包含not in,<>,is null,is not null,like'%%'的時候
不會使用索引。
in:可以使用索引。
優化方案: a<>0 改為a>0 or a<0
a is not null改為 a>0 或a>''
is null 用一個預設值代替空值
like'%%'不能使用索引
like'001%'可以使用索引
(4)同義字 :通過建立同義字(對象的另外的一個名字)簡化通路對象的操作。
create synonym tb_emp for scott.emp;
select * from tb_emp;
删除:drop synonym tb_emp;
資料庫連結--database link
(5)視圖
建立使用者時通過dba角色賦予權限,而不是手動賦予權限,則會建立
視圖會報:沒有權限
解決方案:使用system使用者登入,在給j1703賦予權限
grant create any table to j1703 with admin option;
grant create any view to j1703 with admin option;
grant select any table to j1703 with admin option;
所有資料字典都是視圖:
select * from User_Tables;
視圖可以使複雜的查詢變得簡單
建立簡單視圖(一個表):
create view v_deptinfo as
select deptno as 部門,count(*) 人數,sum(sal) 總工資,avg(sal)
平均工資,max(sal) 最高工資,min(sal) 最低工資
from scott.emp group by deptno;
使用視圖:select *from v_deptinfo;
複雜視圖(多個表)
删除視圖:drop view v_deptinfo;
(6)資料模組化(試規範題)
軟體開發過程:
1. 需求調研,與客戶進行溝通
2. 需求分析,将現實工作中的動作模拟到計算機
資料模組化
3. 開發
4. 測試
5. 上線部署
從關系資料庫的表中删除備援資訊的過程稱為規範化,
是得到高效的關系型資料庫表的邏輯結構最好和最容易的方法。
規範化資料時:應執行以下操作:
将資料庫的結構精簡為最簡單的形式
從表中删除備援值
辨別所有依賴與其他資料的資料
獲得資料化的三種方法:三範式
步驟1:
第一範式:必須要有主鍵,并且每個屬性值都是不可分的最小資料單元
,則稱為是第一範式。
第二範式:所有非主關鍵字都完全依賴于主關鍵字(通常用于聯合主鍵)
第三範式:非主關鍵字不能依賴于其他非主關鍵字(通常用于一個主鍵)
資料模組化
1.根據三個範式
2.分析實體之間的關系
1對1:一個人隻有一個××× 唯一外鍵關聯或者主鍵關聯
一對多:一個班級可以有多個學生 一個學生隻屬于一個班級
關聯:一對多使用主外鍵關聯,通常在多方建立外鍵
多對多:一個學生可以選擇多門課程 一門課程可以被多個學生選修
關聯:多對多通常使用中間表(再多建一張表存儲)關聯資料
通常中間表會有兩張表的id作為聯合主鍵,并且
作為外鍵指向關聯表
訂單和使用者之間的關系是多對一:
create table tb_order(
id int primary key,
code varchar2(50),
user_id int,
foregin key(user_id) references tb_user(id)
);
訂單和書籍是多對多關系:
create table tb_item(
order_id int,
book_id int,
count int,
primary key(order_id,book_id),
foregin key(order_id) references tb_order(id),
foregin key(book_id) references tb_book(id)
);
select * from tb_user u,tb_book b,tb_order o,tb_item i
where u.id=o.user_id
and o.id=i.order_id,
and b.id=i.book_id
and u.name='jack';
12.plsql(資料庫程式設計語言)
PL/SQL=傳統SQL+結構化流程控制
好處:1.有結構化的流程控制,可以完成複雜的操作
2.性能高于sql
sql:頁面輸入資訊 -- servlet接收資料,調用持久層生成sql - sql語句發送到資料庫(DBMS),資料庫編譯 -- 執行sql。
plsql:頁面輸入資訊 -- servlet接收資料,調用持久層傳入參數(調用存儲過程) - 執行sql。
3.可以對程式中的異常進行處理
PL/SQL程式由三個塊組成。
(1) 聲明部分: 在此聲明PL/SQL用到的變量,類型及遊标,以及局部的存儲過程和函數
(2)執行部分: 過程及SQL 語句 , 即程式的主要部分
(3) 異常處理部分: 錯誤處理
文法:declare --聲明部分,用來定義變量等
begin--可以了解成java的花括号,編寫代碼的地方
exception--處理程式抛出異常
end;
第一個plsql,在控制台輸出HelloWorld
BEGIN
--包名.過程('參數')
dbms_output.put_line('HelloWorld!');
END;
定義變量,變量初始值都是null
常量constant
declare
v_id int;
v_name varchar2(18):='jack';
v_clazz constant varchar2(5):='j1703';
begin
v_id:=100;
dbms_output.put_line('v_id='||v_id);
dbms_output.put_line('v_name='||v_name);
end;
複合變量(資料類型) record
1.包含多個内部元件,用于存放多個值
2.需要先定義類型,然後用該類型可重複定義多個變量
注意:複合變量屬于資料類型,定義時前面要加TYPE
TYPE是不能直接使用的,使用前需要定義變量引用
declare
type r_tb_clazz is record
(
id int,
code varchar2(18)
);
v_clazz r_tb_clazz;
begin
v_clazz.id:=1;
v_clazz.code:='j1703';
end;
plsql分兩大類:匿名塊,帶名塊
begin
insert into tb_clazz(id,code) values(3,'j1703');
commit;
end;
select
1.缺少into子句,plsql目的是操作資料,需要定義變量
2.實際傳回行數超過請求行數(隻能查詢一條資料,如果需要查詢
多條資料,使用遊标)
3.未找到資料
declare
v_id int;
v_code varchar2(18);
begin
select id,code into v_id,v_code from tb_clazz where id=1;
dbms_output.put_link(v_id || '' || v_code);
end;
%type屬性:
定義某個變量的資料類型與已經存在的變量資料類型,
某個列的資料類型相同。
declare
v_id tb_clazzz.id%type;
v_code tb_clazz.code%type;
begin
select id,code into v_id,v_code from tb_clazz where id=1;
dems_output.put_line(v_id || '' || v_code);
end;
%rowtype屬性:
用于定義不确定的類型的變量
當資料庫中表字段的個數和資料類型會在運作中改變,
程式中的變量也會自動随之改變。
rt_tb_clazz tb_clazz%rowtype
rt_tb_clazz 變量和tb_clazz表結構一緻,相當于表結構的副本。
declare
v_tb_clazz tb_clazz%rowtype;
begin
select id,code into v_tb_clazz.id,v_tb_clazz.code from
tb_clazz where id=1;
dbms_output.put_line(v_tb_clazz.id || '' || v_tb_clazz.code);
end;
declare
v_tb_clazz tb_clazz%rowtype;
begin
select * into v_tb_clazz from tb_clalzz where id=1;
dbms output.put_line(v_tb_clazz.id || '' || v_tb_clazz.code);
end;
使用recode
declare
type r_tb_clazzz is recode
(
id tb_clazz.id%type,
code tb_clazz.code%type;
);
v_tb_clazz r_tb_clazz;
begin
select * into v_tb_clazz from tb_clazz where id=1;
dbms_output.putline(v_tb_clazz.id || '' || v_tb_clazz.code);
end;
循環和判斷
if分支
declare
v_i number:=10;
begin
if(v_i=10)then
dbms_output.putline('進入if塊');
end if;
end;
if else分支
if ()then...else ...end if
if elsif else分支
if() then...elsif()then ...else ...end if
plsql的循環有三種:loop for while
loop循環:
loop
v_i:=v_i+1;--自增
exit when v_i=5;
end loop;
第二種結束方法:
loop
v_i:=v_i+1;
if(v_i=5) then
exit;
end if;
end loop;
for循環
for v_i in 1..5
loop
end loop;
--重點 項目當中最常用的異常處理
--實際開發中異常的處理 重點
create table tb_error(
id int primary key,
errorObj varchar2(18),--抛出異常的對象
sqlcode varchar(50),--異常編碼
sqlerrm varchar(200),--異常資訊
currdate date --發生時間
);
create sequence s_tb_error;
--重點掌握
--orale當抛出異常時會将異常編碼存儲到sqlcode函數,異常資訊
-- 存儲到sqlerrm
declare
v_id tb_clazz.id%type;
v_code tb_clazz.code%type;
begin
select id,code into v_id,v_code from tb_clazz;
dbms_output.put_line(v_id || '' || v_code );
exception
when others then
v_sqlcode :=sqlcode;
v_sqlerrm:=sqlerrm;
insert into tb_error(id,errorobj,sqlcode,sqlerrm,currdate)
values(s_tb_error.nextval,'default',v_sqlcode,v_sqlerrm,sysdate);
commit;
end;
--遊标:用來提取多行資料
--oracle打開一個工作區(緩存)來儲存多行查詢的結果集,
-- 遊标就是給這個工作區命的名稱,并能用于處理由多行查詢而傳回的
記錄行
--隐式遊标:預設的DML語句和select語句都有隐式遊标
--顯示遊标:開發中給工作區命名,并且可以進行操作
--%isopen boolean 遊标打開,則傳回true
--%nofound boolean 如果最近抓取沒有獲得記錄,傳回true
--%found boolean 如果最近抓取獲得記錄,傳回true
--%rowcount number 傳回到目前為止擷取的記錄數
--使用遊标的步驟
--1.定義遊标 cursor c_tb_clazz is select * from tb_clazz;
--2.打開遊标 open c_tb_clazz;
--3.fetch遊标 fetch c_tb_clazz into r_tb_clazz;
-- 遊标有個指針,預設指向第一行之上,fetch将指針向下移動,指向第n
行資料,如果有資料,notfound 傳回false,found傳回true
如果到末尾,抓取不到資料,一直顯示最後一條資料
--4.關閉遊标 close c_tb_clazz;
--第一個例子 ;使用遊标提取tb_clazz的所有資料
declare
cursor c_tb_clazz is select * from tb_clazz;
r_tb_clazz tb_clazz%rowtype;
begin
open c_tb_clazz;
loop
fetch c_tb_clazz into r_tb_clazz;
dbms_output.put_line(r_tb_clazz.id || '' || r_ tb_clazz.code);
end loop;
close c_tb_clazz;
end;
--帶參數的遊标
第二個例子:使用遊标提取tb_clazz的所有資料,同時提取每個班級的學生資料
--注意:傳遞是形參,形參是不用長度的
declare
--班級遊标
cursor c_tb_clazz is select * from tb_clazz;
r_tb_clazz tb_clazz%rowtype;
--學生遊标
cursor c_tb_student(v_clazz_id tb_clazz.id%type)
is select * from tb_student where clazz_id=v_clazz_id;
r_tb_student tb_student%rowtype;
begin
open c_tb_clazz;
loop
fetch c_tb_clazz into r_tb_clazz;
exit when c_tb_clazz%notfound;
dbms_output.put_.line('班級' || r_tb_clazz.id || '' || r_tb_clazz.code);
--目前班級的學生資料
--打開學生遊标,參數是班級id
open c_tb_student(r_tb_clazz.id);
loop
fetch c_tb_student into r_tb_student;
exit when c_tb_student%nofound;
dbms_output.putline(r_tb_student.id || '' || r_tb_student.name);
end loop;
close c_tb_student;
end loop;
close c_tb_clazz;
end;
--遊标for循環
1.不需要顯式打開遊标
2.for循環自動隐式地定義recode變量
3.不需要使用fetch抓取資料
4.循環結束後,不需要使用close來關閉遊标
declare
cursor c_dept is select * from scott.dept;
begin
for re_dept in c_dept
loop
dbms_output.put_line();
end loop;
end;
--帶名塊(declare begin end; 就是匿名塊)
是資料庫中命名的PL/SQL塊,作為資料庫對象儲存在資料庫裡。
主要四類:
存儲過程:執行特定操作,無傳回值
函數:進行複雜計算,有傳回值
包:邏輯上相關的過程和函數組織在一起
觸發器:事件觸發,執行相應操作
過程和函數統稱為PL/SQL子程式,過程和函數的唯一差別是函數總向調用者傳回資料,
而過程則不傳回資料。
--存儲過程
文法:(直接建立)
procedure name
[(parameters)形參]
is
局部變量聲明
begin
語句;
[exception]
end;
--建立存儲過程,可以被多個程式調用(java或者c++),可以向
--存儲過程傳遞參數,也可以讓存儲過程傳出參數
--參數:
--in:預設,值被傳遞給子程式。子程式不能改變參數值。
--out:值被傳回調用環境(java或者c++),子程式會改變參數值。
--調用存儲過程
begin
insertclazz();--存儲過程名
end;
--調用存儲過程通過帶參數傳給存儲過程
--函數與過程文法差異:
(1)在函數的聲明中,必須包含一個帶有資料類型的return字句,
傳回值表示的類型,在函數體中必須有一個有效的return語句。
(2)隻能使用in模式參數傳入參數值
--文法:
function name
[(parameters)形參]
return datatype
is
局部變量聲明
begin
語句;
return value;
[exception]
end;
包類似于C++和Java語言中的類,其中變量相當于類中的成員變量,過程和函數相當于類方法。把相關的程式單元歸納到包裡
通過使用包,可使開發人員利用面向對象的方法進行存儲過程的開發,進而提高系統性能。
--觸發器
--觸發器是當某個事件發生時自動隐式運作。而且,觸發器不能
接收參數。
--觸發器最主要作用:提供更靈活的完整性校驗規則。
--文法:
trigger name
after|berfore
is
局部變量聲明;
begin
語句;
[exception]
end;
--觸發器的組成成分:
觸發事件:在任何情況下觸發trigger(insert,undate,delete等)
觸發時間:before或者after
觸發器本身:該trigger被觸發後的執行體
觸發頻率:動作被執行的次數(主要行級觸發器)
當行級觸發器被觸發時,如果要通路插入,更新或删除的記錄的值,
可以使用:
:new 通路操作完成後的值
:old 通路操作前的資料
--特性 insert update delete
:old Null 有效 有效
:new 有效 有效 null
當删除tb_tran的資料時,将資料備份到tb_back:
create or replace trigger t_del_tran
before delete
on tb_tran
for each row
declare
begin
insert into tb_back(id,accout,amount,currdate)
values(:old.id,:old.Accout,:old.Amount,:old:Currdate);
end t_del_tran;
觸發器還可以用來維護資料完整性:
create or replace trigger t_del_clazz
before delete
on tb_clazz
for each row
declarer
begin
delete from tb_student_course
where student_id in
(
select id from tb_student where clazz_id=:old.id
);
delete from tb_student where clazz_id=:old.id;
end t_del_clazz;