oracle 預存程序（二）

 select myFunc(參數1,參數2..) to dual; --可以執行一些商務邏輯
一:Oracle中的函數與預存程序的區別:
A:函數必須有傳回值,而過程沒有.
B:函數可以單獨執行.而過程必須通過execute執行.
C:函數可以嵌入到SQL語句中執行.而過程不行.
其實我們可以將比較複雜的查詢寫成函數.然後到預存程序中去調用這些函數.
二:如何建立預存程序:
A:格式
create or replace procedure <porcedure_name>
[(參數名參數類型以及描述,....)] ---注意,沒有傳回值
is
[變數聲明]
begin
[過程處理];----------null;
exception
when 異常名 then
end;
注意:參數中預設是按值傳遞.是in方式.也可以是out和in out方式.這些特點和函數一樣.
B:舉例1:
create or replace procedure myPro----create or replace proc myPro 出錯 不能簡寫
(a in int:=0,b in int:=0)
is
c int:=0;
begin
c:=a+b;
dbms_output.put_line('C is value'||c);
end;
執行:
execute myPro(10,20); ---在Sql Server中.執行預存程序是不需要括弧的.注意 分號不要調了.
exec myPro(10,20); --可以簡寫
C:舉例2:
如果在一個函數裡麵包含Select語句的話,那麼該Select語句必須有into,過程同樣也需要.
create or replace procedure myPro1
(a int:=0,b int:=0)
is
c int:=0;
begin
select empno+a+b into c from emp where ename='FORD';
dbms_output.put_line('C is values '||c);
end;
執行:
execute myPro1(10,20)

D:假如在一個過程裡面要返回一個結果集，怎麼辦?大家注意.就必須要用到遊標了!用遊標來處理這個結果集.
create or replace procedure Test
(
varEmpName emp.ename%type
)
is begin ------會報錯.錯誤原因沒有into子句.
select * from emp where ename like '%'||varEmpName||'%';
end;

這個程式我們無法用into，因為在Oracle裡面沒有一個類型去接受一個結果集.這個時候我們可以聲明遊標對象去接受他.

PL/SQL遊標:
A:分類:
1:隱式遊標:非使用者明確聲明而產生的遊標. 你根本看不到cursor這個關鍵字.
2:顯示遊標:使用者明確通過cursor關鍵字來聲明的遊標.

B:什麼是隱式遊標:
1:什麼時候產生:
會在執行任何合法的SQL語句(DML---INSERT UPDATE DELETE DQL-----SELECT)中產生.他不一定存放資料.也有可能存放記錄集所影響的行數.
如果執行SELECT語句,這個時候遊標會存放資料.如果執行INSERT UPDATE DELETE會存放記錄影響的行數.
C:隱式遊標叫什麼名字:
名字叫sql
關於sql的遊標變數到底有哪些呢?
作用:返回最近一次執行SQL語句所涉及的遊標相關資訊.因為每執行一次SQL語句，都會產生一個隱式遊標.那麼當前執行的SQL語句就是當前的隱式遊標.
sql%found
sql%notfound
sql%rowcount
sql%isopen
D:關於隱式遊標的例子:

create table 學生基本資料表
(
StuID int,
StuName varchar2(20)
)

alter table 學生基本資料表 add constraint PK_STUID primary key(StuID)

declare
num int:=0;
begin
num:=&num;
delete from 學生基本資料表 where StuID=num;
if sql%notfound then
dbms_output.put_line('該行資料沒有發現');
else
dbms_output.put_line('資料被發現並刪除,影響的行數為:'||sql%rowcount);
end if;
end;

E:關於顯示遊標的例子:
1:如何定義顯示遊標
declare cursor <cursor_name> is [select語句];
declare cursor mycur is select empno,ename,job from scott.emp;
2:如何開啟遊標:
open <cursor_name>;
open mycur;
3:如何通過遊標來讀取資料
fetch <cursor_name> into <variable_list>
4:如何關閉遊標:
close <cursor_name>;
close mycur;
注意:在Oracle中,不需要顯示銷毀遊標.因為在Oracle中,很多東西是由JAVA寫的.Oracle會自動銷毀遊標.
5:舉例:

declare
cursor mycur is select empno,ename,job from emp;
vempno emp.empno%type;
vename emp.ename%type;
vjob emp.job%type;
begin
open mycur;
fetch mycur into vempno,vename,vjob;
dbms_output.put_line('I Found You!'||mycur%rowcount||'行');
dbms_output.put_line('讀取的資料為'||vempno||' '||vename||' '||vjob);
close mycur;
end;

因為唯讀出來一條,所以要遍曆一下:
declare
cursor mycur is select empno,ename,job from emp;
vempno emp.empno%type;
vename emp.ename%type;
vjob emp.job%type;
begin
open mycur;
loop
fetch mycur into vempno,vename,vjob;
exit when mycur%notfound;
if mycur%found then
dbms_output.put_line('讀取的資料為'||vempno||' '||vename||' '||vjob);
end if;
end loop;
dbms_output.put_line('I Found You!'||mycur%rowcount||'行');
close mycur;
end;
6:通常情況下我們在讀取表資料的時候,我們需要動態去查詢.所以能不能在Oracle中給遊標帶參數呢?可以!
1):如何定義帶參數的遊標:
declare cursor <cursor_name>(參數名稱 參數類型描述) is select xxxxx from bbbbb where aaa==??? and ccc=???;
2):例子:
遊標是一個集合,讀取資料有兩種方式
第一種方式: open fetch close
第二種方式: for 一但使用了for迴圈 在迴圈剛剛開始的時候,相當於執行open,在處理迴圈的時候,相當於執行fetch,
在退出迴圈的時候,相當於執行了close
declare
cursor query(vname varchar) is select empno,ename,job from emp where ename like'%'||vname||'%';

begin
for line in query('A')
loop
dbms_output.put_line(line.empno||' '||line.ename||' '||line.job);
end loop;
end;

實現動態輸入:
declare
cursor query(vname varchar) is select empno,ename,job from emp where ename like'%'||vname||'%';

name1 varchar(10);
begin
name1:=upper('&name1');
for line in query(name1)
loop
dbms_output.put_line(line.empno||' '||line.ename||' '||line.job);
end loop;
end;

轉自：http://hi.baidu.com/qingcao_xiaohei/blog/item/f0d1b5997aca62016f068cae.html