Oracle高級對象
一、存儲過程
/*
優點;
1、效率高，
2、減少和數據庫的交互次數
3、代碼複用
缺點：
1、移植性差
2、可讀性差
特點：
1、可以帶參數
2、不能有返回值
*/
-- 基本語法
create or replace procedure 存儲過程(參數)
is
BEGIN
-- 過程體（具體邏輯）
end;
--簡單認識存儲過程，加法的存儲過程
create or replace procedure p_add(a int,b int)
IS
c int;
BEGIN
c := a+b;
dbms_output.put_line(c);
end;
-- 2、調用存儲過程
DECLARE
BEGIN
-- 通過存儲過程的名字調用
p_add(199,233);
end;
-- 九九乘法表
create or replace procedure P_nine
is
begin
for x in 1..9 loop
for y in 1..x loop
dbms_output.put_line(x||'*'||y||'='||(x*y)||' ');
end loop;
end loop;
end;
-- 調用存儲過程
DECLARE
BEGIN
P_nine;
end;
-- 判斷是否素數
CREATE or replace procedure p_isSu(n int)
IS
flag boolean :=true;-- 假設是一個素數
BEGIN
dbms_output.put_line(n||'是一個素數');
for i in 2..n-1 loop
if mod(n,i)=0 THEN
flag:=false;
exit;-- break 退出循環
end if;
end loop;
if flag THEN
dbms_output.put_line(n||'是一個素數');
ELSE
dbms_output.put_line(n||'不是素數');
end if;
END;
-- 調用存儲過程
declare
begin
p_isSu(9);
end;
---- 參數類型
/*
in 用於接收，默認的參數模式
out 用於向調用程序返回值，等同於return
in out 用於接收調用程序傳遞進來的值，同時也可以向調用程序返回執行之後的結果
*/
create or replace procedure p_add2(a int,b int,c out int)
is
begin
c:= a+b;
end;
-- 調用有返回參數的存儲過程
declare
x int;
begin
p_add2(12,99,x);
dbms_output.put_line(x);
end;
-- 銀行案例
/*
請定義一個賬戶表（用戶id，姓名，餘額，開戶時間）和
操作日誌表（id、操作時間、用戶id、交易類別、金額），使用存儲過程完成如下功
能：
請實現支出的存儲過程，必須記錄操作日誌
請實現轉賬的存儲過程，必須記錄操作日誌
*/
--第一步：建表
create table account_1705(
aid number(9) primary key,
name varchar2(10),
balance number(19,2),
addtime timestamp
);
-- 交易流水錶
-- id、操作時間、用戶id、交易類型、金額
create table transfer(
tid number(12) primary key,
times timestamp,
types varchar2(10),-- 支出、存入、轉出、轉入
money number(19,2),
uids number(9) -- 用戶id，外鍵
);
-- 添加外鍵
alter table transfer add constraint fk_transfer_account
foreign key(uids) references account_1705(aid);
-- 添加測試數據
-- 添加自增的序列
create sequence sq_account
start with 2000001;-- 起始值
--
create sequence sq_transfer;
insert into account_1705(aid,name,balance,addtime)
values(sq_account.nextval,'陳大力',345,sysdate);
insert into account_1705(aid,name,balance,addtime)
values(sq_account.nextval,'陳建軍',12,sysdate);
insert into account_1705(aid,name,balance,addtime)
values(sq_account.nextval,'文東',1200,sysdate);
-- 第二步：支出的存儲過程，必須寫入操作日誌
create or replace procedure p_zhiChu(a_id int,money number)
is
mm number(19,2);
begin
--1、通過賬號查詢餘額(參數名儘量不要和表字段一致)
select balance into mm from account_1705 where aid=a_id;
--2、判斷餘額是否充足
if mm
dbms_output.put_line('對不起，您的餘額不足！');
return;-- 誤返回值的return，直接結束存儲過程
else
--3、更新賬戶餘額信息
update account_1705 set balance=balance-money where aid=a_id;
end if;
--4、添加交易記錄（日誌）
insert into transfer(tid,times,types,money,uids)
values(sq_transfer.nextval,systimestamp,'支出',money,a_id);
--5、提交事務
commit;
end;
--查詢表
select * from account_1705;
select * from transfer;
-- 調用支出的存儲過程
declare
begin
p_zhiChu(2000003,380);
end;
-- 轉賬的存儲過程，必須記錄操作日誌
create or replace procedure p_zhuan
(from_id int,to_id int,money number)
is
mm number(19,2);
begin
--1、根據from_id查詢該賬戶的餘額
select balance into mm from account_1705 where aid=from_id;
--2、判斷餘額是否充足
if mm < money then
dbms_output.put_line('對不起，您的餘額有限！轉賬失敗');
return;
else
--3、修改兩個賬戶的餘額信息 from 餘額減 to 餘額加
update account_1705 set balance=balance-money where aid=from_id;
update account_1705 set balance=balance+money where aid=to_id;
end if;
--4、往記錄表插入兩條記錄 轉出/轉入
insert into transfer(tid,times,types,money,uids)
values(sq_transfer.nextval,systimestamp,
'轉出',money,from_id);
insert into transfer(tid,times,types,money,uids)
values(sq_transfer.nextval,systimestamp,
'轉入',money,to_id);
--5、提交事務
commit;
end;
二、 函數
/*
特點：
和存儲過程類似
可以有返回值
兩者的區別
存儲過程 函數
1、關鍵字 procedure function
2、返回值 必須通過形參out 用函數名直接返回
3、賦值 不能賦值並定義類型 可以定義類型，也可以直接賦值
4、調用方式 獨立的過程名調用 可以用表達式的方式調用
5、目的 完成一系列的數據操作、處理 獲得函數的返回值
*/
-- 語法
create or replace function 函數名(參數)
return 返回類型
is|as
begin
end;
-- 加法的簡單案例
create or replace function fun_add(a int,b int)
return int
as
c int;
begin
c:=a+b;
return c;
end;
-- 調用函數（sql語句中）
select fun_add(9,8) from dual;
-- 調用函數（用於PL/SQL中）
declare
x int;
begin
x:=fun_add(99,102);
dbms_output.put_line(x);
end;
