PLSQL開發精品總結（轉）

--不知道出處了，絕對是入門的精品，收藏之

2. 字元表示式
字元表示式由字元型常數、變數、函式和字元運算子組成，唯一可以使用的字元運算子就是連線運算子“||”。
3. 關係表示式
關係表示式由字元表示式或數值表示式與關係運算子組成，可以使用的關係運算子包括以下9種。
< 小於
> 大於
= 等於（不是賦值運算子:=）
like 類似於
in 在……之中
<= 小於等於
>= 大於等於
!= 不等於
between 在……之間
關係型表示式運算子兩邊的表示式的資料型別必須一致。
4. 邏輯表示式
邏輯表示式由邏輯常數、變數、函式和邏輯運算子組成，常見的邏輯運算子包括以下3種。
NOT：邏輯非
OR：邏輯或
AND：邏輯與
運算的優先次序為NOT、AND和OR。

函式

PL/SQL程式中提供了很多函式供擴充套件功能，除了標準SQL語言的函式可以使用外，最常見的資料型別轉換函式有以下3個。
To_char：將其他型別資料轉換為字元型。
To_date：將其他型別資料轉換為日期型。
To_number：將其他型別資料轉換為數值型。
以上介紹了PL/SQL中最基本的語法要素，下面介紹體現PL/SQL過程化程式設計思想的流程控制語句。

pl/sql程式中對大小寫不敏感（列印宣告的變數）
―――――――――――――――――――――――――――――――――――――
set serveroutput on
DECLARE
v_char varchar2(20):='a';
v_char1 varchar2(20):='b';
BEGIN
DBMS_OUTPUT.PUT_LINE(v_char);
DBMS_OUTPUT.PUT_LINE(v_char1);
END;

pl語句塊是pl/sql裡最小的程式設計塊，其中可以再巢狀begin end
begin
dbms_output.put_line('Hello World');
dbms_output.put_line('2*3='||(2*3));
dbms_output.put_line('what''s');
end;
―――――――――――――――――――――――――――――――――――――

PL/SQL中的變數宣告

所有變數必須在declare中宣告，程式中不允許宣告。
沒有初始化的變數預設值為null，螢幕上null是看不見的，命名習慣：PL/SQL中變數一般以v_開頭（等同於儲存過程中as和begin區域的變數定義習慣）。
注意number也能存小數，最長38位，所以以後建議整數都用binary_integer存。
long是字元型別，boolean型別不能列印。
標準變數型別：數字，字元，時間，布林。
―――――――――――――――――――――――――――――――――――――
declare
v_number1 number;
v_number2 number(3,2) ;
v_number3 binary_integer :=1;
v_name varchar2(20) :='kettas';
v_date date :=sysdate;
v_long long :='ni hao';
v_b boolean := true;
begin
if (v_number1 is null) then
dbms_output.put_line( 'hello');
end if;
dbms_output.put_line(v_number1);
dbms_output.put_line(v_number2);
dbms_output.put_line(v_number3);
dbms_output.put_line(v_name);
dbms_output.put_line(v_date);
dbms_output.put_line(v_long);
--dbms_output.put_line(v_b); --執行該句ORACLE提示“呼叫 'PUT_LINE' 時引數個數或型別錯誤”
end;
―――――――――――――――――――――――――――――――――――――

備註：
關於宣告number(4,3)中括號中的兩個數字的意義，前面的數字叫精度，後面的叫刻度。
刻度：
當刻度為正數的時候，表示四捨五入到小數點後面的位數
當刻度為負數的時候，表示四捨五入到小數點前面的位數
精度：
從數字的最前面不為零開始到刻度精確到的位置
v_Number number(4,3):=123.12312
1、按刻度進行四捨五入得到123.123
2、確定刻度精確到的位置123123處，精度為6位（.符號不算）
2、根據精度進行判斷6位（>4）精度上限值 --報錯不能儲存
number(3,-3):=44445
1、根據刻度－3進行四捨五入得到44000
2、小數點向前移動3位44.此位置為刻度精確到的位置
3、根據精度進行判斷2位（<3）精度上限值 --不報錯可儲存結果為44000

DECLARE
v_Number number(4,3):=123.12312;--實際精度6位大於上限精度值4位，提示“ORA-06502: PL/SQL: 數字或值錯誤 : 數值精度太高”
BEGIN
DBMS_OUTPUT.PUT_LINE(v_Number);
END
;

DECLARE
v_Number number(7,3):=4555; --實際精度7位等於上限精度值，可以儲存
BEGIN
DBMS_OUTPUT.PUT_LINE(v_Number);
END
;

*****************************************
變數賦值方式
*****************************************
oracle中變數賦值方式是值複製而非引用

declare
v_number1 number:=100;
v_number2 number;
begin
v_number2:=v_number1;
v_number1:=200;
dbms_output.put_line(v_number1); --200
dbms_output.put_line(v_number2); --100

end;

*****************************************
PLSQL複合型別
*****************************************
記錄型別record

record型別最常用，宣告的時候可以加not null，但必須給初始值，如果record型別一致可以相互賦值，如果型別不同，裡面的欄位恰好相同，不能互相賦值。引用記錄型變數的方法是“記錄變數名.基本型別變數名”。
―――――――――――――――――――――――――――――――――――――
declare
type t_first is record(
id number(3),
name varchar2(20)
);
v_first t_first;
begin
v_first.id:=1;
v_first.name:='cheng';
dbms_output.put_line(v_first.id);
dbms_output.put_line(v_first.name);
end;

record型別變數間賦值
declare
type t_first is record(
id number,
name varchar2(20)
);
v_first t_first;
v_second t_first;
begin
v_first.id:=1;
v_first.name:='susu';

v_second:=v_first;--相互賦值

v_first.id:=2;
v_first.name:='kettas';
dbms_output.put_line(v_first.id);
dbms_output.put_line(v_first.name);
dbms_output.put_line(v_second.id);
dbms_output.put_line(v_second.name);
end;
―――――――――――――――――――――――――――――――――――――
表型別變數table
語法如下：
type 表型別 is table of 型別 index by binary_integer;
表變數名 表型別;
型別可以是前面的型別定義，index by binary_integer子句代表以符號整數為索引，這樣訪問表型別變數中的資料方法就是“表變數名(索引符號整數)”。table型別，相當於 java中的Map容器，就是一個可變長的陣列，key（符號整數索引）必須是整數，可以是負數，value（型別）可以是標量，也可以是record類 型。可以不按順序賦值，但必須先賦值後使用。

1. 定義一維表型別變數
―――――――――――――――――――――――――――――――――――――
declare
type t_tb is table of varchar2(20) index by binary_integer;
v_tb t_tb;
begin
v_tb(100):='hello';
v_tb(98):='world';
dbms_output.put_line(v_tb(100));
dbms_output.put_line(v_tb(98));
end;

型別為record的表型別變數
declare
type t_rd is record(id number,name varchar2(20));
type t_tb is table of t_rd index by binary_integer;
v_tb2 t_tb;
begin
v_tb2(100).id:=1;
v_tb2(100).name:='hello';
--dbms_output.put_line(v_tb2(100).id);
--dbms_output.put_line(v_tb2(100).name);
dbms_output.put_line(v_tb2(100).id||' '||v_tb2(100).name);
end;
―――――――――――――――――――――――――――――――――――――

2. 定義多維表型別變數
該程式定義了名為tabletype1的多維表型別，相當於多維陣列，table1是多維表型別變數，將資料表tempuser.testtable中recordnumber為60的記錄提取出來

存放在table1中並顯示。
―――――――――――――――――――――――――――――――――――――
declare
type tabletype1 is table of testtable%rowtype index by binary_integer;
table1 tabletype1;
begin
select * into table1(60) from tempuser.testtable where recordnumber=60;
dbms_output.put_line(table1(60).recordnumber||table1(60).currentdate);
end;

備註：在定義好的表型別變數裡，可以使用count、delete、first、last、next、exists和prior等屬性進行操作，使用方法為“表變數名.屬性”，返回的是數字。

set serveroutput on
declare
type tabletype1 is table of varchar2(9) index by binary_integer;
table1 tabletype1;
begin
table1(1):='成都市';
table1(2):='北京市';
table1(3):='青島市';
dbms_output.put_line('總記錄數：'||to_char(table1.count));
dbms_output.put_line('第一條記錄：'||table1.first);
dbms_output.put_line('最後條記錄：'||table1.last);
dbms_output.put_line('第二條的前一條記錄：'||table1.prior(2));
dbms_output.put_line('第二條的後一條記錄：'||table1.next(2));
end;
―――――――――――――――――――――――――――――――――――――

*****************************************
%type和%rowtype
*****************************************
使用%type定義變數，為了讓PL/SQL中變數的型別和資料表中的欄位的資料型別一致，Oracle 9i提供了%type定義方法。這樣當資料表的欄位型別修改後，PL/SQL程式中相應變數的型別也自動修改。
―――――――――――――――――――――――――――――――――――――
create table student(
id number,
name varchar2(20),
age number(3,0)
);

insert into student(id,name,age) values(1,'susu',23);
--查詢一個欄位的變數

declare
v_name varchar2(20);
v_name2 student.name%type;
begin
select name into v_name2 from student where rownum=1;
dbms_output.put_line(v_name2);
end;

--查詢多個欄位的變數
declare
v_id student.id%type;
v_name student.name%type;
v_age student.age%type;
begin
select id,name,age into v_id,v_name,v_age from student where rownum=1;
dbms_output.put_line(v_id||' '||v_name||' '||v_age);
end;

--查詢一個型別的變數，推薦用*

declare
v_student student%rowtype;
begin
select * into v_student from student where rownum=1;
dbms_output.put_line(v_student.id||' '||v_student.name||' '||v_student.age);
end;

--也可以按欄位查詢，但是欄位順序必須一樣，不推薦這樣做
declare
v_student student%rowtype;
begin
select id,name,age into v_student from student where rownum=1;
dbms_output.put_line(v_student.id||' '||v_student.name||' '||v_student.age);
end;

declare
v_student student%rowtype;
begin
select id,name,age into v_student.id,v_student.name,v_student.age from student where id=1;
--select * into v_student.id,v_student.name,v_student.age from student where id=1;
dbms_output.put_line();
end;
―――――――――――――――――――――――――――――――――――――
備註：insert，update，delete，select都可以，create table，drop table不行。DPL，DML，和流程控制語句可以在pl/sql裡用，但DDL語句不行。

declare
v_name student.name%type:='wang';
begin
insert into student(id,name,age) values(2,v_name,26);
end;

begin
insert into student(id,name,age) values(5,'hehe',25);
end;

declare
v_name student.name%type:='hexian';
begin
update student set name=v_name where id=1;
end;

begin
update student set name='qinaide' where id=2;
end;
―――――――――――――――――――――――――――――――――――――
*****************************************
PLSQL變數的可見空間
*****************************************
變數的作用域和可見性，變數的作用域為變數申明開始到當前語句塊結束。當外部過程和內嵌過程定義了相同名字的變數的時候，在內嵌過程中如果直接寫這個變數 名是沒有辦法訪問外部過程的變數的，可以透過給外部過程定義一個名字<>,透過outername變數名 來訪問外部過程的變數（待測試..）。
―――――――――――――――――――――――――――――――――――――
declare
v_i1 binary_integer:=1;
begin
declare
v_i2 binary_integer:=2;
begin
dbms_output.put_line(v_i1);
dbms_output.put_line(v_i2);
end;
dbms_output.put_line(v_i1);
--dbms_output.put_line(v_i2); 解開後執行Oracle會提示“必須說明識別符號 'V_I2'”
end;
―――――――――――――――――――――――――――――――――――――

*****************************************
PLSQL流程控制
*****************************************
if判斷
declare
v_b boolean:=true;
begin if v_b then
dbms_output.put_line('ok');
end if;
end;

if else判斷
declare
v_b boolean:=true;
begin
if v_b then
dbms_output.put_line('ok');
else
dbms_output.put_line('false');
end if;
end;

if elsif else判斷
declare
v_name varchar2(20):='cheng';
begin
if v_name='0701' then
dbms_output.put_line('0701');
elsif v_name='cheng' then
dbms_output.put_line('cheng');
else
dbms_output.put_line('false');
end if;
end;

loop迴圈，注意推出exit是推出迴圈，而不是推出整個程式碼塊
declare
v_i binary_integer:=0;
begin
loop
if v_i>10 then
exit;
end if;
v_i:=v_i+1;
dbms_output.put_line('hehe');
end loop;
dbms_output.put_line('over');
end;

loop簡化寫法
declare
v_i binary_integer :=0;
begin
loop
exit when v_i>10;
v_i :=v_i+1;
dbms_output.put_line('hehe');
end loop;
dbms_output.put_line('over');
end;

while迴圈
declare
v_i binary_integer:=0;
begin
while v_i<10 loop
dbms_output.put_line('hello'||v_i );
v_i:=v_i+1;
end loop;
dbms_output.put_line('over');
end;

for迴圈，注意不需要宣告變數
begin
for v_i in 0..10 loop
dbms_output.put_line('hello'||v_i);
end loop;
dbms_output.put_line('over');
end;

*****************************************
PLSQL異常處理
*****************************************
1、宣告異常
異常名 EXCEPTION；
2、丟擲異常
RAISE 異常名
3、處理異常
丟擲異常後的邏輯程式碼不會被繼續執行

異常的定義使用
―――――――――――――――――――――――――――――――――――――
begin
dbms_output.put_line(1/0);
exception
when others then
dbms_output.put_line('error');
end;

declare
e_myException exception;
begin
dbms_output.put_line('hello');
raise e_myException; --raise丟擲異常,用此關鍵字，丟擲後轉到自定義的e_myException ，執行其裡面的putline函式後，再跳到end處，結束PL/SQL塊，raise接下面的2句不會繼續執行。
dbms_output.put_line('world');
dbms_output.put_line(1/0);
exception
when e_myException then
dbms_output.put_line(sqlcode); --當前會話執行狀態，錯誤編碼
dbms_output.put_line(sqlerrm); --當前錯誤資訊
dbms_output.put_line('my error');
when others then
dbms_output.put_line('error');
end;
―――――――――――――――――――――――――――――――――――――
*****************************************
PLSQL遊標和goto語句
*****************************************
備註：下面提到的遊標為靜態cursor，包括顯示和隱式。
遊標，從declare、open、fetch、close是一個完整的生命旅程。當然了一個這樣的遊標是可以被多次open進行使用的，顯式 cursor是靜態cursor，她的作用域是全域性的，但也必須明白，靜態cursor也只有pl/sql程式碼才可以使用它。靜態遊標變數是在定義時就必須指定SQL語句。

cursor 遊標(結果集)用於提取多行資料，定義後不會有資料，使用後才有。一旦遊標被開啟，就無法再次開啟(可以先關閉，再開啟)。
declare
cursor c_student is select * from book;
begin
open c_student;
close c_student;
end;

第二種遊標的定義方式，用變數控制結果集的數量。
declare
v_id binary_integer;
cursor c_student is select * from book where id>v_id;
begin
v_id:=10;
open c_student;
close c_student;
end;

第三種遊標的定義方式，帶引數的遊標，用的最多。
declare
cursor c_student(v_id binary_integer) is select * from book where id>v_id;
begin
open c_student(10);
close c_student;
end;

遊標的使用，一定別忘了關遊標。
declare
v_student book%rowtype;
cursor c_student(v_id binary_integer) is select * from book where id>v_id;
begin
open c_student(10);
fetch c_student into v_student;
close c_student;
dbms_output.put_line(v_student.name);
end;

如何遍歷遊標fetch
遊標的屬性 %found，%notfound,%isopen,%rowcount。
%found:若前面的fetch語句返回一行資料，則%found返回true，如果對未開啟的遊標使用則報ORA-1001異常。
%notfound，與%found行為相反。
%isopen，判斷遊標是否開啟。
%rowcount:當前遊標的指標位移量，到目前位置遊標所檢索的資料行的個數，若未開啟就引用，返回ORA-1001。

注：
no_data_found和%notfound的用法是有區別的，小結如下
1）SELECT . . . INTO 語句觸發 no_data_found；
2）當一個顯式游標(靜態和動態)的 where 子句未找到時觸發 %notfound；
3）當UPDATE或DELETE 語句的where 子句未找到時觸發 sql%notfound；
4）在游標的提取(Fetch)迴圈中要用 %notfound 或%found 來確定迴圈的退出條件，不要用no_data_found。

下面是幾個例項：
create table BOOK
(
ID VARCHAR2(10) not null,
BOOKNAME VARCHAR2(10) not null,
PRICE VARCHAR2(10) not null,
CID VARCHAR2(10) not null
);

--insert
create or replace procedure say_hello(
i_name in varchar2,
o_result_msg out varchar2
)
as
v_price varchar2(100);
e_myException exception;

begin
insert into book(id,bookname,price) values (1,2,3);
o_result_msg := 'success';
exception
when others then
rollback;
o_result_msg := substr(sqlerrm, 1, 200);
end;

--update or delete
create or replace procedure say_hello(
i_name in varchar2,
o_result_msg out varchar2
)
as
v_price varchar2(100);
e_myException exception;

begin
update book set price = '55' where bookname = i_name;
delete from book where bookname = i_name;
if sql%notfound then
raise e_myException;
end if;
/*
if sql%rowcount = 0 then--寫法2
raise e_myException;
end if;
*/
o_result_msg := 'success';
exception
when e_myException then
rollback;
o_result_msg := 'update or delete dail';
end;

--select
create or replace procedure say_hello(
i_name in varchar2,
o_result_msg out varchar2
)
as
v_price varchar2(100);
e_myException exception;

begin
select price into v_price from book where bookname = i_name;
o_result_msg := 'success';
exception
when no_data_found then
rollback;
o_result_msg := 'select into dail';
end;

loop方式遍歷遊標
declare
v_bookname varchar2(100);
cursor c_book(i_id number) is select bookname from book where id = i_id;
begin
Open c_book(i_id);
Loop
Fetch c_book into v_bookname;
exit when c_student%notfound;
update book set price = '33' where bookname = v_bookname;
End Loop;
Close c_book;
end;
或
declare
v_bookname varchar2(100);
cursor c_book(i_id number) is select bookname from book where id = i_id;
begin
Open c_book(i_id);
Fetch c_book into v_bookname;
While c_book%Found
Loop
update book set price = '33' where bookname = v_bookname;
Fetch c_book into v_bookname;
End Loop;
Close c_book;
end;

while迴圈遍歷遊標，注意，第一次遊標剛開啟就fetch，%found為null，進不去迴圈
解決方法：while nvl(c_student%found,true) loop
declare
v_bookname varchar2(100);
cursor c_book(i_id number) is select bookname from book where id = i_id;
begin
Open c_book(i_id);
while nvl(c_book%found,true) --或這種寫法：while c_book%found is null or c_book%found loop
Fetch c_book into v_bookname;
update book set price = '33' where bookname = v_bookname;
End Loop;
Close c_book;
end;

for迴圈遍歷，最簡單，用的最多，不需要 宣告v_student,Open和Close遊標和fetch操作(不用開啟遊標和關閉遊標，實現遍歷遊標最高效方式)
declare
cursor c_book(i_id number) is select bookname from book where id = i_id;
begin
for cur in c_book(i_id) --直接將入參i_id傳入cursor即可
loop
update book set price = '53' where bookname = cur.bookname;
end loop;
end;

*****************************************
PLSQL中的function
*****************************************
FUNCTION和PROCEDURE的區別
1、函式有返回值，過程沒有
2、函式呼叫在一個表示式中，過程則是作為pl/sql程式的一個語句
過程和函式都以編譯後的形式存放在資料庫中，函式可以沒有引數也可以有多個引數並有一個返回值。過程
有零個或多個引數，沒有返回值。函式和過程都可以透過引數列表接收或返回零個或多個值，函式和過程的
主要區別不在於返回值，而在於他們的呼叫方式，過程是作為一個獨立執行語句呼叫的，函式以合法的表達
式的方式呼叫
create or replace function func(v_name in varchar2)
return varchar2
is
begin
return(v_name||' hello');
end;

呼叫：
declare
v_name varchar2(20);
begin
v_name:=func('cheng');
dbms_output.put_line(v_name);
end;

帶out引數的函式
create or replace function func(
v_name in varchar2,
v_content out varchar2
)
return varchar2
is
begin
v_content:=v_name||' hello';
return v_content;
end;

呼叫：
declare
v_name varchar2(20);
v_name1 varchar2(20);
begin
v_name1:=func('susu',v_name);--返回v_name值
dbms_output.put_line(v_name1);--列印func結果
dbms_output.put_line(v_name);--列印v_name結果
end;

帶in out 引數的函式
create or replace function func(
v_name in out varchar2)
return varchar2
is
begin
v_name:=v_name||' hello';
return 'cheng';
end;

呼叫：
declare
v_inout varchar2(20):='world';
v_ret varchar2(20);
begin
v_ret:=func(v_inout);--返回撥用v_inout值(作為出參)
dbms_output.put_line(v_ret);--列印func結果
dbms_output.put_line(v_inout);--返回v_name結果
end;

[@more@]PL