------------------------2013-5-19------------------------
子程序的類型
? --過程，用于執行某項操作
? --函數，用于執行某項操作并返回值

create or replace procedure <proc_name>? --過程名稱
[parameter list]???????????????????????? --參數列表
is|as
?<local declarations>;?? --局部聲明
begin
?(executable statements)?? --可執行語句
end;

參數模式：
- in 接受值，默認模式
- out 將值返回給子程序的調用程序
- in out 接受值并返回已更新的值

執行過程
execute Procedure_name(list of parameters)
execute可以不寫。

刪除過程
drop procedure Procedure_name;

函數
create or replace function <function_name>? --函數名稱
[argument list]??????? --參數列表
return datatype is|as?????? --數據類型
?(local declaration)????? --局部聲明
begin
?(executable statements)? ???? --可執行語句
end;

訪問函數
--用兩種方式進行訪問
?使用pl/sql塊???????? variable := function_name(parameter list);
?使用sql語句????????? select function_name(parameter) from dual;
--僅接受in參數

過程與函數
過程????函數
作為pl/sql語句執行??作為表達式的一部分調用
在規格說明中不包含return子句 ?必須在規格說明中包含return子句
可以返回任何值???必須返回單個值
可以包含return語句，但是與函數? 必須包含至少一條return語句
不同，它不能用于返回值。

pragma autonomous_transaction? 用于標記子程序

程序包
相關對象的封裝
程序包的各部分
- 程序包規格說明? 聲明子程序
- 程序包主體????? 定義子程序

程序包規格說明
- 使用create package命令進行創建
- 包含公用對象和類型
- 聲明類型、常量、變量、異常、游標和子程序
- 可以在沒有程序包主體的情況下存在

程序包主體
- 使用create package body命令進行創建
- 包含子程序和游標的定義
- 包含私有聲明
- 不能在沒有程序包規格說明的情況下存在

程序包的執行
Package-name.type-name??????? 程序包名稱.類型名稱
Package-name.object-name????? 程序包名稱.對象名稱
Package-name.subprogram-name? 程序包名稱.子程序名稱

程序包優點：
模塊化，信息隱藏。

User_objects: 用于檢查對象是否存在
User_source: 用于獲取對象的代碼

程序包 重載。方法名相同，形參不同，與方法名，參數模式，或返回類型無關。

程序包可以理解為java里面的類。思想。
程序包是一種數據庫對象，它是相關對象的封裝。

select * from user_procedures;
select * from user_objects
select * from user_source;
select * from user_source where name = 'ADDNEW';

create or replace procedure proInsertT
(
a int,
b int
)
as
begin
?? --a := a + a;??????? --會報錯!! 錯誤：PLS-00363: 表達式 'A' 不能用作賦值目標
?? --b := b + b;??????? --錯誤：PLS-00363: 表達式 'B' 不能用作賦值目標
?? insert into c6 values (a, b);
end;

create or replace function GetCountBy(
x in int,
y in int,
z out int
)
return int
as
r int;
begin
z := x+y;
r :=0;
return (r);
end;

--調用--
declare
x int;
y int;
z int;
r int;
begin
? x := 1;
? y := 2;
? z := 0;
? r := GetCountBy(x,y,z);
? -- select GetCountBy(x, y, z) from dual;?? --PL/SQL: ORA-06572: 函數 GETCOUNTBY 具有輸出參數
? dbms_output.put_line('z:' || z);
? dbms_output.put_line('r:' || r);
end;

z:3
r:0

PL/SQL 過程已成功完成。

select sysdate from dual;

declare
d date;
begin
d := sysdate;
dbms_output.put_line('d:' || d);
end;

select * from user_source where name = upper('proGetEmpRecByEmpNo');
-- oracle是區分大小寫的，使用upper函數來轉換。--

variable g_price_num number;
variable g_type_char varchar2(12)?? --定義變量??

##包規格說明##
create or replace package pkg_Titles
is
type type_Title_rec is record
(
title titles.title%type,
price titles.price%type,
type titles.type%type
);
function FunGetMaxPrice
return type_Title_rec;
end pkg_Titles;

##包主體##
create or replace package body pkg_Titles
is
function FunGetMaxPrice
return type_Title_rec
is
/*type type_Title_rec is record
(
title titles.title%type,
price titles.price%type,
type titles.type%type
); */ --PLS-00498:在說明一個類型之前對其進行了非法使用。
-- 因為在包規格說明中已經進行了定義。
lv_title_rec type_Title_rec;
begin
select title, price, type into lv_title_rec
from titles
where price =
(
select max(price)
from titles
);
return lv_title_rec;
end FunGetMaxPrice;

end pkg_Titles;

##調用##
declare
lv_title_rec pkg_Titles.type_Title_rec;
begin
?lv_title_rec := pkg_Titles.FunGetMaxPrice;
?dbms_output.put_line(lv_title_rec.title);
?dbms_output.put_line(lv_title_rec.price);
?dbms_output.put_line(lv_title_rec.type);
end;

-- 使用scott架構(tiger)
create table emp as
select * from scott.emp;

-- 存儲過程
-- 根據員工編號返回一條員工記錄
create or replace procedure proGetEmpRec(
p_emp_no in emp.empno%type,
p_emp_rec out emp%rowtype
)
as
begin
? select * into p_emp_rec from emp where empno = p_emp_no;
end;

-- 調用存儲過程，接收返回的員工記錄，并輸出到緩存區
declare
lv_emp_rec emp%rowtype;
begin
? proGetEmpRec('7499',lv_emp_rec);
? dbms_output.put_line('EMP_NO:' || lv_emp_rec.empno);
? dbms_output.put_line('EMP_NAME:' || lv_emp_rec.ename);
? dbms_output.put_line('EMP_JOB:' || lv_emp_rec.job);
? dbms_output.put_line('EMP_SAL:' || lv_emp_rec.sal);
end;

-- 函數
-- 根據給定的員工編號，返回相應的員工記錄。
create or replace function funGetEmpRecByNo
(
p_emp_no emp.empno%type
)
return emp%rowtype
as
lv_emp_rec emp%rowtype;
begin
? select * into lv_emp_rec from emp where empno = p_emp_no;
? return (lv_emp_rec);
end;

-- 調用函數，接收返回的員工記錄，并輸出到緩存區
declare
lv_emp_rec emp%rowtype;
begin
? lv_emp_rec := funGetEmpRecByNo('7499');
? dbms_output.put_line('EMP_NO:' || lv_emp_rec.empno);
? dbms_output.put_line('EMP_NAME:' || lv_emp_rec.ename);
? dbms_output.put_line('EMP_JOB:' || lv_emp_rec.job);
? dbms_output.put_line('EMP_SAL:' || lv_emp_rec.sal);
end;

show errors? 查看錯誤

-- 視圖 + 函數(根據書籍編號獲取相應的總銷量)
-- 視圖 + 函數(根據書籍編號獲取相應的總銷量的排位)

##針對以上的過程和函數寫入程序包中##
create or replace package pkg_emp
is??????????????????????????????????????? --注意區別：is后面是沒有begin，否則報錯。 is as都可以。
procedure proGetEmpRec(
p_emp_no in emp.empno%type,
p_emp_rec out emp%rowtype
);

function funGetEmpRecByNo
(
p_emp_no emp.empno%type
)
return emp%rowtype;
end pkg_emp;

create or replace package body pkg_emp
is?????? --注意區別：is后面是沒有begin，否則報錯。 is as都可以。
procedure proGetEmpRec(
p_emp_no in emp.empno%type,
p_emp_rec out emp%rowtype
)as
begin
? select * into p_emp_rec from emp where empno = p_emp_no;
end;????????????????????? --可以寫成end proGetEmpRec;

function funGetEmpRecByNo
(
p_emp_no emp.empno%type
)
return emp%rowtype
as
lv_emp_rec emp%rowtype;
begin
? select * into lv_emp_rec from emp where empno = p_emp_no;
? return (lv_emp_rec);
end;???????????????????? --可以寫成end funGetEmpRecByNo;

end pkg_emp;

declare
lv_emp_rec emp%rowtype;
begin
? pkg_emp.proGetEmpRec('7499',lv_emp_rec);
? dbms_output.put_line('EMP_NO:' || lv_emp_rec.empno);
? dbms_output.put_line('EMP_NAME:' || lv_emp_rec.ename);
? dbms_output.put_line('EMP_JOB:' || lv_emp_rec.job);
? dbms_output.put_line('EMP_SAL:' || lv_emp_rec.sal);
?
? dbms_output.put_line('-------------我是分隔線-------------');
?
? lv_emp_rec := pkg_emp.funGetEmpRecByNo('7499');
? dbms_output.put_line('EMP_NO:' || lv_emp_rec.empno);
? dbms_output.put_line('EMP_NAME:' || lv_emp_rec.ename);
? dbms_output.put_line('EMP_JOB:' || lv_emp_rec.job);
? dbms_output.put_line('EMP_SAL:' || lv_emp_rec.sal);
?
end;

--定義關聯游標--
type cur_titles is ref cursor;
--給游標賦值的代碼--
open p_titles_cursor for select * from titles;

select * from v$sqlarea;? ?--查詢結果返回的特別多--

Oracle Class8. 子程序和程序包