Oracle中PL/SQL的用法總結(jié)
一、PLSQL基本結(jié)構(gòu)
1、基本數(shù)據(jù)類型變量
1. 基本數(shù)據(jù)類型
- Number 數(shù)字型
- Int 整數(shù)型
- Pls_integer 整數(shù)型，產(chǎn)生溢出時(shí)出現(xiàn)錯(cuò)誤
- Binary_integer 整數(shù)型，表示帶符號(hào)的整數(shù)
- Char 定長(zhǎng)字符型，最大255個(gè)字符
- Varchar2 變長(zhǎng)字符型，最大2000個(gè)字符
- Long 變長(zhǎng)字符型，最長(zhǎng)2GB
- Date 日期型
- Boolean 布爾型(TRUE、FALSE、NULL三者取一)
在PL/SQL中使用的數(shù)據(jù)類型和Oracle數(shù)據(jù)庫(kù)中使用的數(shù)據(jù)類型，有的含義是完全一致的，有的是有不同的含義的。
2. 基本數(shù)據(jù)類型變量的定義方法
變量名 類型標(biāo)識(shí)符[not null]:=值;
declare
age number(3):=26; --長(zhǎng)度為3，初始值為26
begin
commit;
end;其中，定義常量的語(yǔ)法格式：
常量名 constant 類型標(biāo)識(shí)符 [not null]:=值;
declare
pi constant number(9):=3.1415926;--為pi的數(shù)字型常量，長(zhǎng)度為9，初始值為3.1415926
begin
commit;
end;2、表達(dá)式
變量、常量經(jīng)常需要組成各種表達(dá)式來(lái)進(jìn)行運(yùn)算，下面介紹在PL/SQL中常見(jiàn)表達(dá)式的運(yùn)算規(guī)則。
1. 數(shù)值表達(dá)式
PL/SQL程序中的數(shù)值表達(dá)式是由數(shù)值型常數(shù)、變量、函數(shù)和算術(shù)運(yùn)算符組成的，
可以使用的算術(shù)運(yùn)算符包括+(加法)、-(減法)、*(乘法)、/(除法)和**(乘方)等。
命令窗口中執(zhí)行下列PL/SQL程序，該程序定義了名為result的整數(shù)型變量，計(jì)算的是10+3*4-20+5**2的值，理論結(jié)果應(yīng)該是27。
set serveroutput on
Declare
result integer;
begin
result:=10+3*4-20+5**2;
dbms_output.put_line('運(yùn)算結(jié)果是：'||to_char(result));
end;dbms_output.put_line函數(shù)輸出只能是字符串，因此利用to_char函數(shù)將數(shù)值型結(jié)果轉(zhuǎn)換為字符型。
2. 字符表達(dá)式
字符表達(dá)式由字符型常數(shù)、變量、函數(shù)和字符運(yùn)算符組成，唯一可以使用的字符運(yùn)算符就是連接運(yùn)算符“||”。
3. 關(guān)系表達(dá)式
關(guān)系表達(dá)式由字符表達(dá)式或數(shù)值表達(dá)式與關(guān)系運(yùn)算符組成，可以使用的關(guān)系運(yùn)算符包括以下9種。
< 小于
> 大于
= 等于(不是賦值運(yùn)算符:=)
like 類似于
in 在……之中
<= 小于等于
>= 大于等于
!= 不等于 或<>
between 在……之間
關(guān)系型表達(dá)式運(yùn)算符兩邊的表達(dá)式的數(shù)據(jù)類型必須一致。
4. 邏輯表達(dá)式
邏輯表達(dá)式由邏輯常數(shù)、變量、函數(shù)和邏輯運(yùn)算符組成，常見(jiàn)的邏輯運(yùn)算符包括以下3種。
NOT：邏輯非
OR：邏輯或
AND：邏輯與
運(yùn)算的優(yōu)先次序?yàn)镹OT、AND和OR。
3、PLSQL函數(shù)
PL/SQL程序中提供了很多函數(shù)供擴(kuò)展功能，除了標(biāo)準(zhǔn)SQL語(yǔ)言的函數(shù)可以使用外，最常見(jiàn)的數(shù)據(jù)類型轉(zhuǎn)換函數(shù)有以下3個(gè)。
To_char：將其他類型數(shù)據(jù)轉(zhuǎn)換為字符型。
To_date：將其他類型數(shù)據(jù)轉(zhuǎn)換為日期型。
To_number：將其他類型數(shù)據(jù)轉(zhuǎn)換為數(shù)值型。
繼續(xù)追加中..
4、系統(tǒng)輸出打印
利用pl/sql在數(shù)據(jù)庫(kù)服務(wù)器端打印一句話：
set serveroutput on--設(shè)置數(shù)據(jù)庫(kù)輸出，默認(rèn)為關(guān)閉，每次重新打開(kāi)窗口需要重新設(shè)置。
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello PL/SQL');
END;pl/sql程序中對(duì)大小寫(xiě)不敏感(打印聲明的變量)
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語(yǔ)句塊是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;5、PL/SQL中的變量聲明
所有變量必須在declare中聲明，程序中不允許聲明。
沒(méi)有初始化的變量默認(rèn)值為null，屏幕上null是看不見(jiàn)的，命名習(xí)慣：PL/SQL中變量一般以v_開(kāi)頭
(等同于存儲(chǔ)過(guò)程中as和begin區(qū)域的變量定義習(xí)慣)。
注意number也能存小數(shù)，最長(zhǎng)38位，所以以后建議整數(shù)都用binary_integer存。
long是字符類型，boolean類型不能打印。
標(biāo)準(zhǔn)變量類型：數(shù)字，字符，時(shí)間，布爾。
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); --執(zhí)行該句ORACLE提示“調(diào)用'PUT_LINE' 時(shí)參數(shù)個(gè)數(shù)或類型錯(cuò)誤”
end;備注：
關(guān)于聲明number(4,3)中括號(hào)中的兩個(gè)數(shù)字的意義，前面的數(shù)字叫精度，后面的叫刻度。
刻度：
當(dāng)刻度為正數(shù)的時(shí)候，表示四舍五入到小數(shù)點(diǎn)后面的位數(shù)
當(dāng)刻度為負(fù)數(shù)的時(shí)候，表示四舍五入到小數(shù)點(diǎn)前面的位數(shù)
精度：
從數(shù)字的最前面不為零開(kāi)始到刻度精確到的位置
v_Number number(4,3):=123.12312
1、按刻度進(jìn)行四舍五入得到123.123
2、確定刻度精確到的位置123123處，精度為6位(.符號(hào)不算)
2、根據(jù)精度進(jìn)行判斷6位(>4)精度上限值 --報(bào)錯(cuò)不能存儲(chǔ)
number(3,-3):=44445
1、根據(jù)刻度－3進(jìn)行四舍五入得到44000
2、小數(shù)點(diǎn)向前移動(dòng)3位44.此位置為刻度精確到的位置
3、根據(jù)精度進(jìn)行判斷2位(<3)精度上限值 --不報(bào)錯(cuò)可存儲(chǔ)結(jié)果為44000
DECLARE v_Number number(4,3):=123.12312;--實(shí)際精度6位大于上限精度值4位，提示“ORA-06502:PL/SQL: 數(shù)字或值錯(cuò)誤 : 數(shù)值精度太高” BEGIN DBMS_OUTPUT.PUT_LINE(v_Number); END; DECLARE v_Number number(7,3):=4555; --實(shí)際精度7位等于上限精度值，可以存儲(chǔ) 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復(fù)合類型
1、記錄類型record
record類型最常用，聲明的時(shí)候可以加not null，但必須給初始值，如果record類型一致可以相互賦值，如果類型不同，里面的字段恰好相同，不能互相賦值。引用記錄型變量的方法是“記錄變量名.基本類型變量名”。
declare
type t_first is record(
idnumber(3),
namevarchar2(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;2、表類型變量table
語(yǔ)法如下：
type 表類型 is table of 類型 index by binary_integer;
表變量名 表類型;
類型可以是前面的類型定義，index by binary_integer子句代表以符號(hào)整數(shù)為索引，這樣訪問(wèn)表類型變量中的數(shù)據(jù)方法就是“表變量名(索引符號(hào)整數(shù))”。table類型，相當(dāng)于Java中的Map容器，就是一個(gè)可變長(zhǎng)的數(shù)組，key(符號(hào)整數(shù)索引)必須是整數(shù)，可以是負(fù)數(shù)，value(類型)可以是標(biāo)量，也可以是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,namevarchar2(20));
type t_tb is table of t_rd index bybinary_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的多維表類型，相當(dāng)于多維數(shù)組，table1是多維表類型變量，將數(shù)據(jù)表tempuser.testtable中recordnumber為60的記錄提取出來(lái)
存放在table1中并顯示。
declare
type tabletype1 is table of testtable%rowtypeindex by binary_integer;
table1 tabletype1;
begin
select * into table1(60) fromtempuser.testtable where recordnumber=60;
dbms_output.put_line(table1(60).recordnumber||table1(60).currentdate);
end;備注：在定義好的表類型變量里，可以使用count、delete、first、last、next、exists和prior等屬性進(jìn)行操作，使用方法為“表變量名.屬性”，返回的是數(shù)字。
set serveroutput on
declare
type tabletype1 is table ofvarchar2(9) index by binary_integer;
table1 tabletype1;
begin
table1(1):='成都市';
table1(2):='北京市';
table1(3):='青島市';
dbms_output.put_line('總記錄數(shù)：'||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中變量的類型和數(shù)據(jù)表中的字段的數(shù)據(jù)類型一致，Oracle 9i提供了%type定義方法。這樣當(dāng)數(shù)據(jù)表的字段類型修改后，PL/SQL程序中相應(yīng)變量的類型也自動(dòng)修改。
create table student(
id number,
name varchar2(20),
age number(3,0)
);
insert into student(id,name,age) values(1,'susu',23);
--查找一個(gè)字段的變量
declare
v_name varchar2(20);
v_name2 student.name%type;
begin
select name into v_name2 from studentwhere rownum=1;
dbms_output.put_line(v_name2);
end;
--查找多個(gè)字段的變量
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 fromstudent where rownum=1;
dbms_output.put_line(v_id||' '||v_name||' '||v_age);
end;
--查找一個(gè)類型的變量，推薦用*
declare
v_student student%rowtype;
begin
select * into v_student from student whererownum=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 whererownum=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 intov_student.id,v_student.name,v_student.age from student where id=1;
--select * intov_student.id,v_student.name,v_student.age from student where id=1;
dbms_output.put_line();
end;備注：insert，update，delete，select都可以，createtable，droptable不行。DPL，DML，和流程控制語(yǔ)句可以在pl/sql里用，但DDL語(yǔ)句不行。
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' whereid=2;
end;五、PLSQL變量的可見(jiàn)空間
變量的作用域和可見(jiàn)性，變量的作用域?yàn)樽兞可昝鏖_(kāi)始到當(dāng)前語(yǔ)句塊結(jié)束。
當(dāng)外部過(guò)程和內(nèi)嵌過(guò)程定義了相同名字的變量的時(shí)候，在內(nèi)嵌過(guò)程中如果直接寫(xiě)這個(gè)變量名是沒(méi)有辦法訪問(wèn)外部過(guò)程的變量的，可以通過(guò)給外部過(guò)程定義一個(gè)名字<<outername>>,通過(guò)outername變量名來(lái)訪問(wèn)外部過(guò)程的變量(待測(cè)試..)。
declare
v_i1binary_integer:=1;
begin
declare
v_i2binary_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); 解開(kāi)后執(zhí)行Oracle會(huì)提示“必須說(shuō)明標(biāo)識(shí)符'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循環(huán)
注意推出exit是推出循環(huán)，而不是推出整個(gè)代碼塊
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簡(jiǎn)化寫(xiě)法
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循環(huán)
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循環(huán)
注意不需要聲明變量
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、處理異常
拋出異常后的邏輯代碼不會(huì)被繼續(xù)執(zhí)行
異常的定義使用
begin
dbms_output.put_line(1/0);
exception
when othersthen
dbms_output.put_line('error');
end;
declare
e_myException exception;
begin
dbms_output.put_line('hello');
raisee_myException; --raise拋出異常,用此關(guān)鍵字，拋出后轉(zhuǎn)到自定義的e_myException ，執(zhí)行其里面的putline函數(shù)后，再跳到end處，結(jié)束PL/SQL塊，raise接下面的2句不會(huì)繼續(xù)執(zhí)行。
dbms_output.put_line('world');
dbms_output.put_line(1/0);
exception
whene_myException then
dbms_output.put_line(sqlcode); --當(dāng)前會(huì)話執(zhí)行狀態(tài)，錯(cuò)誤編碼
dbms_output.put_line(sqlerrm); --當(dāng)前錯(cuò)誤信息
dbms_output.put_line('my error');
when othersthen
dbms_output.put_line('error');
end;八、PLSQL游標(biāo)和goto語(yǔ)句
備注：下面提到的游標(biāo)為靜態(tài)cursor，包括顯示和隱式。
游標(biāo)，從declare、open、fetch、close是一個(gè)完整的生命旅程。當(dāng)然了一個(gè)這樣的游標(biāo)是可以被多次open進(jìn)行使用的，顯式cursor是靜態(tài)cursor，她的作用域是全局的，但也必須明白，靜態(tài)cursor也只有pl/sql代碼才可以使用它。靜態(tài)游標(biāo)變量是在定義時(shí)就必須指定SQL語(yǔ)句。
cursor 游標(biāo)(結(jié)果集)用于提取多行數(shù)據(jù)，定義后不會(huì)有數(shù)據(jù)，使用后才有。一旦游標(biāo)被打開(kāi)，就無(wú)法再次打開(kāi)(可以先關(guān)閉，再打開(kāi))。
declare
cursor c_studentis select * from book;
begin
open c_student;
close c_student;
end;第二種游標(biāo)的定義方式，用變量控制結(jié)果集的數(shù)量。
declare
v_id binary_integer;
cursor c_student isselect * from book where id>v_id;
begin
v_id:=10;
open c_student;
close c_student;
end;第三種游標(biāo)的定義方式，帶參數(shù)的游標(biāo)，用的最多。
declare
cursor c_student(v_idbinary_integer) is select * from book where id>v_id;
begin
open c_student(10);
close c_student;
end;游標(biāo)的使用，一定別忘了關(guān)游標(biāo)。
declare
v_student book%rowtype;
cursor c_student(v_idbinary_integer) is select * from book where id>v_id;
begin
open c_student(10);
fetch c_student intov_student;
close c_student;
dbms_output.put_line(v_student.name);
end;如何遍歷游標(biāo)fetch
游標(biāo)的屬性 %found，%notfound,%isopen,%rowcount。
- %found:若前面的fetch語(yǔ)句返回一行數(shù)據(jù)，則%found返回true，如果對(duì)未打開(kāi)的游標(biāo)使用則報(bào)ORA-1001異常。
- %notfound，與%found行為相反。
- %isopen，判斷游標(biāo)是否打開(kāi)。
- %rowcount:當(dāng)前游標(biāo)的指針位移量，到目前位置游標(biāo)所檢索的數(shù)據(jù)行的個(gè)數(shù)，若未打開(kāi)就引用，返回ORA-1001。
注：
no_data_found和%notfound的用法是有區(qū)別的，小結(jié)如下
1)SELECT. . . INTO 語(yǔ)句觸發(fā) no_data_found；
2)當(dāng)一個(gè)顯式光標(biāo)(靜態(tài)和動(dòng)態(tài))的 where 子句未找到時(shí)觸發(fā) %notfound；
3)當(dāng)UPDATE或DELETE語(yǔ)句的where 子句未找到時(shí)觸發(fā) sql%notfound；
4)在光標(biāo)的提取(Fetch)循環(huán)中要用 %notfound 或%found 來(lái)確定循環(huán)的退出條件，不要用no_data_found。
下面是幾個(gè)實(shí)例：
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' wherebookname = i_name;
delete from book where bookname =i_name;
if sql%notfound then
raisee_myException;
end if;
/*
if sql%rowcount = 0 then--寫(xiě)法2
raisee_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 bookwhere bookname = i_name;
o_result_msg := 'success';
exception
when no_data_found then
rollback;
o_result_msg := 'select into dail';
end;loop方式遍歷游標(biāo)
declare
v_bookname varchar2(100);
cursor c_book(i_idnumber) is select bookname from book where id = i_id;
begin
Open c_book(i_id);
Loop
Fetch c_bookinto v_bookname;
exit whenc_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_idnumber) is select bookname from book where id = i_id;
begin
Open c_book(i_id);
Fetch c_book intov_bookname;
While c_book%Found
Loop
update book set price = '33' where bookname = v_bookname;
Fetch c_book intov_bookname;
End Loop;
Close c_book;
end;while循環(huán)遍歷游標(biāo)，注意，第一次游標(biāo)剛打開(kāi)就fetch，%found為null，進(jìn)不去循環(huán)
解決方法：while nvl(c_student%found,true) loop
declare
v_bookname varchar2(100);
cursor c_book(i_id number) isselect bookname from book where id = i_id;
begin
Open c_book(i_id);
while nvl(c_book%found,true)--或這種寫(xiě)法：while c_book%found is null or c_book%foundloop
Fetchc_book into v_bookname;
updatebook set price = '33' where bookname = v_bookname;
End Loop;
Close c_book;
end;for循環(huán)遍歷，最簡(jiǎn)單，用的最多，不需要 聲明v_student,Open和Close游標(biāo)和fetch操作(不用打開(kāi)游標(biāo)和關(guān)閉游標(biāo)，實(shí)現(xiàn)遍歷游標(biāo)最高效方式)
declare
cursor c_book(i_id number) isselect bookname from book where id = i_id;
begin
for cur in c_book(i_id) --直接將入?yún)_id傳入cursor即可
loop
updatebook set price = '53' where bookname = cur.bookname;
end loop;
end;goto例子，一般不推薦使用goto，會(huì)使程序結(jié)構(gòu)變亂
declare
i number:=0;
begin
if i=0 then
gotohello;
end if;
<<hello>>
begin
dbms_output.put_line('hello');
gotoover;
end;
<<world>>
begin
dbms_output.put_line('world');
gotoover;
end;
<<over>>
dbms_output.put_line('over');
end;九、Oracle存儲(chǔ)過(guò)程
在談存儲(chǔ)過(guò)程書(shū)寫(xiě)中的一些規(guī)則時(shí)，先看一下執(zhí)行它的規(guī)則，在命令窗口執(zhí)行存儲(chǔ)過(guò)程sp_get_product_prompt
set serveroutput on
var ret1 varchar2(200);
var ret2 varchar2(200);
exec sp_get_product_prompt(83,:ret1,:ret2); --或execute
print ret1;
print ret2;
或
set serveroutput on
declare
ret1 varchar2(200);
ret2 varchar2(200);
begin
sp_get_product_prompt(83,ret1,ret2);
dbms_output.put_line(ret1);
dbms_output.put_line(ret2);
end;存儲(chǔ)過(guò)程入?yún)?，不論類型，缺省情況下值都為null,入?yún)⒑统鰠⒉荒苡虚L(zhǎng)度,其中關(guān)鍵字as可以替換成is,存儲(chǔ)過(guò)程中變量聲明在as和begin之間，同時(shí)，存儲(chǔ)過(guò)程中可以再調(diào)用其它的存儲(chǔ)過(guò)程，如果要保證存儲(chǔ)過(guò)程之間的事務(wù)處理不受影響，可以定義為自治事務(wù)。
create or replace procedure say_hello(
v_name in varchar2,
v_flag number,
o_ret out number
)
as
begin
if v_name is null and v_flag is null then --v_name和v_flag都等于null
o_ret := 10;
else
o_ret := 100;
end if;
end;對(duì)于入?yún)閚ull情況下給予缺省值
create or replace procedure say_hello(
i_name in varchar2,
i_flag number,
o_ret out number
)
as
v_name varchar2(100);
begin
if i_name is null then
v_name := '0';
else
v_name := i_name;
end if;
insert into phone(..,wname..,)values(..,v_name,..);
end;或直接在insert語(yǔ)句中調(diào)用nvl函數(shù)賦缺省值
insert intophone(..,wname..,) values(..,nvl(v_name,' '),..); ----如果將' '寫(xiě)成'',則insert進(jìn)來(lái)的v_name值還是為''等價(jià)于null值
帶一個(gè)參數(shù)的存儲(chǔ)過(guò)程
輸入?yún)?shù)in，輸入?yún)?shù)不能進(jìn)行:=賦值，但可以將它賦給as后面定義的變量；
輸入?yún)?shù)in，可以作為變量進(jìn)行條件判斷；
默認(rèn)不寫(xiě)就是in；
存儲(chǔ)過(guò)程沒(méi)有重載，這個(gè)有參的say_hello會(huì)替代已經(jīng)存在的無(wú)參say_hello。
create or replace procedure say_hello(v_name in varchar2)
as
begin
--v_name:='a'; --存儲(chǔ)過(guò)程入?yún)_name不能做為賦值目標(biāo)
dbms_output.put_line('hello '||v_name);
end;存儲(chǔ)過(guò)程輸入?yún)?shù)作為變量進(jìn)行條件判斷
create or replace procedure say_hello(
i_opFlag in number
)
as
v_name varchar2(100);
begin
if i_opFlag = 1 then
v_name :='0';
else
v_name :='haha';
end if;
dbms_output.put_line('hello'||v_name);
end;利用存儲(chǔ)過(guò)程中定義的變量對(duì)入?yún)⒌目罩堤幚恚?/h3>
create or replace procedure say_hello(
i_name in varchar2
)
as
v_name varchar2(100);
begin
if i_name is null then
v_name :='0';
else
v_name :=i_name;--將入賦值給定義變量
end if;
dbms_output.put_line('hello'||v_name);
end;
create or replace procedure say_hello(
i_name in varchar2
)
as
v_name varchar2(100);
begin
if i_name is null then
v_name :='0';
else
v_name :=i_name;--將入賦值給定義變量
end if;
dbms_output.put_line('hello'||v_name);
end;多個(gè)參數(shù)的存儲(chǔ)過(guò)程
create or replace procedure say_hello(
v_first_name in varchar2,
v_last_name in varchar2)
as
begin
dbms_output.put_line('hello'||v_first_name||'.'||v_last_name);
end;out輸出參數(shù)
用于利用存儲(chǔ)過(guò)程給一個(gè)或多個(gè)變量賦值，類似于返回值
create or replaceprocedure say_hello(
v_name in varchar2,
v_content out varchar2
)
begin
v_content:='hello'||v_name;
end;調(diào)用：
declare
v_con varchar2(200);
v_in varchar2(20):='wang';
begin
say_hello(v_in,v_con);
dbms_output.put_line(v_con);
end;in out參數(shù)，既賦值又取值
create or replace procedure say_hello(v_name in outvarchar2)
as
begin
v_name:='hi '||v_name;
end;調(diào)用：
declare
v_inout varchar2(20):='wangsu';
begin
say_hello(v_inout);
dbms_output.put_line(v_inout);
end;對(duì)存儲(chǔ)過(guò)程入?yún)①x缺省值
create or replace procedure say_hello(
v_name varchar2 default 'susu',
v_content varchar2 default 'hello'
)
as
begin
dbms_output.put_line(v_name||''||v_content);
end;調(diào)用：(用指明形參名的方式調(diào)用更好)
begin
say_hello();
end;或
begin
say_hello('cheng');
end;或
begin say_hello(v_name=>'cheng'); end;
十、PLSQL中的function
FUNCTION和PROCEDURE的區(qū)別
1、函數(shù)有返回值，過(guò)程沒(méi)有
2、函數(shù)調(diào)用在一個(gè)表達(dá)式中，過(guò)程則是作為pl/sql程序的一個(gè)語(yǔ)句
過(guò)程和函數(shù)都以編譯后的形式存放在數(shù)據(jù)庫(kù)中，函數(shù)可以沒(méi)有參數(shù)也可以有多個(gè)參數(shù)并有一個(gè)返回值。
過(guò)程有零個(gè)或多個(gè)參數(shù)，沒(méi)有返回值。
函數(shù)和過(guò)程都可以通過(guò)參數(shù)列表接收或返回零個(gè)或多個(gè)值，函數(shù)和過(guò)程的主要區(qū)別不在于返回值，而在于他們的調(diào)用方式，過(guò)程是作為一個(gè)獨(dú)立執(zhí)行語(yǔ)句調(diào)用的，函數(shù)以合法的表達(dá)式的方式調(diào)用。
create or replace function func(v_name in varchar2)
return varchar2
is
begin
return(v_name||' hello');
end;調(diào)用：
declare
v_name varchar2(20);
begin
v_name:=func('cheng');
dbms_output.put_line(v_name);
end;帶out參數(shù)的函數(shù)
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;調(diào)用：
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結(jié)果
dbms_output.put_line(v_name);--打印v_name結(jié)果
end;帶in out 參數(shù)的函數(shù)
create or replace function func(
v_name in out varchar2)
return varchar2
is
begin
v_name:=v_name||' hello';
return 'cheng';
end;調(diào)用：
declare
v_inout varchar2(20):='world';
v_ret varchar2(20);
begin
v_ret:=func(v_inout);--返回調(diào)用v_inout值(作為出參)
dbms_output.put_line(v_ret);--打印func結(jié)果
dbms_output.put_line(v_inout);--返回v_name結(jié)果
end;到此這篇關(guān)于Oracle中PL/SQL用法的文章就介紹到這了。希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。
相關(guān)文章
Oracle rac環(huán)境的數(shù)據(jù)庫(kù)導(dǎo)入操作步驟
Oracle RAC是一種基于共享存儲(chǔ)和共享數(shù)據(jù)庫(kù)的集群解決方案，可以將多個(gè) Oracle 數(shù)據(jù)庫(kù)實(shí)例連接成一個(gè)邏輯上的單一數(shù)據(jù)庫(kù)，提供高可用性、靈活性和可伸縮性，本文給大家介紹Oracle rac環(huán)境的數(shù)據(jù)庫(kù)導(dǎo)入操作，感興趣的朋友一起看看吧2023-06-06
在Oracle數(shù)據(jù)庫(kù)中,AS和IS是兩個(gè)常用的關(guān)鍵字,它們分別表示“命名別名”和“判斷是否相同”,AS和IS的主要區(qū)別在于它們的功能,AS是一種授權(quán)服務(wù),它負(fù)責(zé)授予和管理用戶訪問(wèn)數(shù)據(jù)庫(kù)特定對(duì)象和操作的權(quán)限,IS則是一種身份驗(yàn)證服務(wù),它負(fù)責(zé)管理用戶的身份和訪問(wèn)權(quán)限2024-01-01
這篇文章主要介紹了Oracle分頁(yè)查詢的實(shí)例詳解的相關(guān)資料,希望通過(guò)本文能幫助到大家實(shí)現(xiàn)理解這部分內(nèi)容，需要的朋友可以參考下2017-10-10
EBS xml publisher中文亂碼問(wèn)題及解決辦法
這篇文章主要介紹了EBS xml publisher中文亂碼問(wèn)題及解決辦法的相關(guān)資料,需要的朋友可以參考下2015-09-09
Oracle?VM?VirtualBox?Ubuntu設(shè)置共享文件夾方式
這篇文章主要介紹了Oracle?VM?VirtualBox?Ubuntu設(shè)置共享文件夾方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2024-06-06
Oracle數(shù)據(jù)庫(kù)安裝教程并實(shí)現(xiàn)公網(wǎng)遠(yuǎn)程連接(內(nèi)網(wǎng)穿透)
Oracle數(shù)據(jù)庫(kù)是用于處理數(shù)據(jù)存儲(chǔ)及大量數(shù)據(jù)計(jì)算的常用數(shù)據(jù)庫(kù),下面這篇文章主要給大家介紹了關(guān)于Oracle數(shù)據(jù)庫(kù)安裝教程并實(shí)現(xiàn)公網(wǎng)遠(yuǎn)程連接(內(nèi)網(wǎng)穿透)的相關(guān)資料,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-06-06
回收站(Recycle Bin）從原理上來(lái)說(shuō)就是一個(gè)數(shù)據(jù)字典表，放置用戶刪除(drop)掉的數(shù)據(jù)庫(kù)對(duì)象信息，接下來(lái)詳細(xì)介紹下ORACLE 回收站方方面面感興趣的你可以參考下哈2013-03-03
Oracle數(shù)據(jù)庫(kù)如何獲取當(dāng)前自然周,當(dāng)前周的起始和結(jié)束日期
Oracle數(shù)據(jù)庫(kù)如何獲取當(dāng)前自然周，當(dāng)前周的起始和結(jié)束日期問(wèn)題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-12-12
前些天在做Oracle數(shù)據(jù)庫(kù)恢復(fù)測(cè)試時(shí)，因?yàn)橐恍┊惓２僮鲗?dǎo)致控制文件出了問(wèn)題，數(shù)據(jù)庫(kù)無(wú)法正常使用，下面這篇文章就來(lái)給大家介紹了關(guān)于Oracle重建控制文件的相關(guān)資料，需要的朋友可以參考借鑒，下面來(lái)一起看看吧2018-05-05
Oracle數(shù)據(jù)庫(kù)正則表達(dá)式純數(shù)字例子
正則表達(dá)式已經(jīng)在很多軟件中得到廣泛的應(yīng)用,包括*nix（Linux, Unix等）,HP等操作系統(tǒng),PHP,C#,Java等開(kāi)發(fā)環(huán)境,這篇文章主要給大家介紹了關(guān)于Oracle數(shù)據(jù)庫(kù)正則表達(dá)式純數(shù)字的相關(guān)資料,需要的朋友可以參考下2024-08-08