第一篇：Oracle之PLSQL總結
基本數據類型變量 1.基本數據類型
Number 數字型
Int 整數型
Pls_integer 整數型，產生溢出時出現錯誤
Binary_integer 整數型，表示帶符號的整數
Char 定長字符型，最大255個字符
Varchar2 變長字符型，最大2000個字符
Long 變長字符型，最長2GB
Date 日期型
Boolean 布爾型（TRUE、FALSE、NULL三者取一）
在PL/SQL中使用的數據類型和Oracle數據庫中使用的數據類型，有的含義是完全一致的，有的是有不同的含義的。
2.基本數據類型變量的定義方法
變量名 類型標識符 [not null]:=值;
declare
age number(3):=26;--長度為3，初始值為26
commit;
begin
end;
其中，定義常量的語法格式：常量名 constant 類型標識符 [not null]:=值;
declare
pi constant number(9):=3.1415926;--為pi的數字型常量，長度為9，初始值為3.1415926
begin
表達式
變量、常量經常需要組成各種表達式來進行運算，下面介紹在PL/SQL中常見表達式的運算規則。
1.數值表達式
PL/SQL程序中的數值表達式是由數值型常數、變量、函數和算術運算符組成的，可以使用的算術運算符包括+（加法）、-（減法）、*（乘法）、/（除法）和**（乘方）等。
命令窗口中執行下列PL/SQL程序，該程序定義了名為result的整數型變量，計算的是10+3*4-20+5**2的值，理論結果應該是27。
set serveroutput on
Declare
result integer;
result:=10+3*4-20+5**2;
begin
commit;
end;dbms_output.put_line('運算結果是：'||to_char(result));
end;
dbms_output.put_line函數輸出只能是字符串，因此利用to_char函數將數值型結果轉換為字符型。
2.字符表達式
字符表達式由字符型常數、變量、函數和字符運算符組成，唯一可以使用的字符運算符就是連接運算符“||”。
3.關系表達式
關系表達式由字符表達式或數值表達式與關系運算符組成，可以使用的關系運算符包括以下9種。
4.邏輯表達式
邏輯表達式由邏輯常數、變量、函數和邏輯運算符組成，常見的邏輯運算符包括以下3種。
PLSQL函數
PL/SQL程序中提供了很多函數供擴展功能，除了標準SQL語言的函數可以使用外，最常見的數據類型轉換函數有以下3個。
系統輸出打印
利用pl/sql在數據庫服務器端打印一句話：
set serveroutput on--設置數據庫輸出，默認為關閉，每次重新打開窗口需要重新設置。
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello PL/SQL');
END;To_char：將其他類型數據轉換為字符型。
To_date：將其他類型數據轉換為日期型。
To_number：將其他類型數據轉換為數值型。
繼續追加中..NOT：邏輯非
OR：邏輯或
AND：邏輯與
運算的優先次序為NOT、AND和OR。< 小于
> 大于
= 等于（不是賦值運算符:=）
like 類似于
in 在??之中
<= 小于等于
>= 大于等于
!= 不等于 或<>
between 在??之間
關系型表達式運算符兩邊的表達式的數據類型必須一致。
pl/sql程序中對大小寫不敏感（打印聲明的變量）
set serveroutput on
DECLARE
pl語句塊是pl/sql里最小的編程塊，其中可以再嵌套 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;
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' 時參數v_char varchar2(20):='a';
v_char1 varchar2(20):='b';
DBMS_OUTPUT.PUT_LINE(v_char);
DBMS_OUTPUT.PUT_LINE(v_char1);
BEGIN
END;begin
個數或類型錯誤”
end;
備注：關于聲明number(4,3)中括號中的兩個數字的意義，前面的數字叫精度，后面的叫刻度。刻度：當刻度為正數的時候，表示四舍五入到小數點后面的位數,當刻度為負數的時候，表示四舍五入到小數點前面的位數
精度：從數字的最前面不為零開始到刻度精確到的位置
―――――――――――――――――――――――――――――――――――――
v_Number number(4,3):=123.12312
1、按刻度進行四舍五入得到123.123
2、確定刻度精確到的位置123123處，精度為6位（.符號不算）
3、根據精度進行判斷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
DECLARE
v_Number number(7,3):=4555;--實際精度7位等于上限精度值，可以存儲
DBMS_OUTPUT.PUT_LINE(v_Number);
BEGIN
END
;
―――――――――――――――――――――――――――――――――――――
變量賦值方式
oracle中變量賦值方式是值拷貝而非引用
declare
v_number1 number:=100;
v_number2 number;
v_number2:=v_number1;
v_number1:=200;
dbms_output.put_line(v_number1);--200
dbms_output.put_line(v_number2);--100
DBMS_OUTPUT.PUT_LINE(v_Number);
END
;
begin
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;
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);
begin
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;
v_tb(100):='hello';
v_tb(98):='world';
dbms_output.put_line(v_tb(100));
dbms_output.put_line(v_tb(98));
begin
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;
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);
begin
end;
――――――――――――――――――――――――――――――――――――
2.定義多維表類型變量
該程序定義了名為tabletype1的多維表類型，相當于多維數組，table1是多維表類型變量，將數據表tempuser.testtable中recordnumber為60的記錄提取出來存放在table1中并顯示。
declare
type tabletype1 is table of testtable%rowtype index by binary_integer;
table1 tabletype1;
select * into table1(60)from tempuser.testtable where recordnumber=60;
dbms_output.put_line(table1(60).recordnumber||table1(60).currentdate);
begin
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;
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));
begin
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
查找多個字段的變量
declare
查找一個類型的變量，推薦用* declare
也可以按字段查找，但是字段順序必須一樣，不推薦這樣做
declare
v_student student%rowtype;
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);
begin
v_student student%rowtype;
select * into v_student from student where rownum=1;
dbms_output.put_line(v_student.id||''||v_student.name||' '||v_student.age);
begin
v_id student.id%type;
v_name student.name%type;
v_age student.age%type;
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);
v_name varchar2(20);
v_name2 student.name%type;
select name
into
v_name2
from
student
where
rownum=1;
begin
dbms_output.put_line(v_name2);
end;begin
end;end;end;
declare
備注：insert，update，delete，select都可以，create table，drop table不行。DPL，DML，和流程控制語句可以在pl/sql里用，但DDL語句不行。
declare
v_name student.name%type:='wang';
insert into student(id,name,age)values(2,v_name,26);
begin
end;
begin
declare
v_name student.name%type:='hexian';
update student set name=v_name where id=1;
begin
end;
begin
update student set name='qinaide' where id=2;
end;
―――――――――――――――――――――――――――――――――――――
PLSQL變量的可見空間
變量的作用域和可見性，變量的作用域為變量申明開始到當前語句塊結束。
當外部過程和內嵌過程定義了相同名字的變量的時候，在內嵌過程中如果直接寫這個變量名是沒有辦法訪問外部過程的變量的，可以通過給外部過程定義一個名字<
declare
v_i1 binary_inteer:=1;
begin
declare
v_i2 binary_integer:=2;
begin
insert into student(id,name,age)values(5,'hehe',25);
end;v_student student%rowtype;
select id,name,age into v_student.id,v_student.name,v_student.age from--select * into v_student.id,v_student.name,v_student.age from student dbms_output.put_line();
begin
student where id=1;
where id=1;
end;
dbms_output.put_line(v_i1);
dbms_output.put_line(v_i2);
dbms_output.put_line(v_i1);
--dbms_output.put_line(v_i2);解開后執行Oracle會提示“必須說明標識符 'V_I2'”
end;
end;
―――――――――――――――――――――――――――――――――――――
PLSQL流程控制
if判斷
declare
if else判斷
declare
if elsif else判斷
declare
v_name varchar2(20):='cheng';
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;begin
v_b boolean:=true;
if v_b then dbms_output.put_line('ok');
else dbms_output.put_line('false');
end if;begin
v_b boolean:=true;
if v_b then dbms_output.put_line('ok');end if;begin end;end;end;――――――――――――――――――――――――――――――――――――― loop循環，注意推出exit是推出循環，而不是推出整個代碼塊
declare
v_i binary_integer:=0;loop
if v_i>10 then exit;end if;
v_i:=v_i+1;
begin
dbms_output.put_line('hehe');
end loop;
dbms_output.put_line('over');
end;loop簡化寫法
declare
while循環
declare
for循環，注意不需要聲明變量
begin
for v_i in 0..10 loop
dbms_output.put_line('hello'||v_i);
end loop;
dbms_output.put_line('over');v_i binary_integer:=0;while v_i<10 loop
dbms_output.put_line('hello'||v_i);
v_i:=v_i+1;
end loop;
dbms_output.put_line('over');begin
v_i binary_integer :=0;loop
exit when v_i>10;
v_i :=v_i+1;
dbms_output.put_line('hehe');
end loop;
dbms_output.put_line('over');begin
end;end;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;
dbms_output.put_line('hello');
raise e_myException;
--raise拋出異常,用此關鍵字，拋出后轉到自定義的e_myException，執行其里面的--再跳到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');
begin
putline函數后，--當前會話執行狀態，錯誤編碼
end;
―――――――――――――――――――――――――――――――――――――
PLSQL游標和goto語句
備注：下面提到的游標為靜態cursor，包括顯示和隱式。
游標，從declare、open、fetch、close是一個完整的生命旅程。當然了一個這樣的游標是可以被多次open進行使用的，顯式cursor是靜態cursor，她的作用域是全局的，但也必須明白，靜態cursor也只有pl/sql代碼才可以使用它。
靜態游標變量是在定義時就必須指定SQL語句。
cursor 游標(結果集)用于提取多行數據，定義后不會有數據，使用后才有。一旦游標被打開，就無法再次打開(可以先關閉，再打開)。
declare
第二種游標的定義方式，用變量控制結果集的數量。
declare
cursor c_student is select * from book;
open c_student;
close c_student;
begin
end;v_id binary_integer;
cursor c_student is select * from book where id>v_id;
v_id:=10;
begin
第三種游標的定義方式，帶參數的游標，用的最多。
declare
cursor c_student(v_id binary_integer)is select * from book where id>v_id;
open c_student(10);
游標的使用，一定別忘了關游標。
declare
如何遍歷游標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，v_student book%rowtype;
cursor c_student(v_id binary_integer)is select * from book where id>v_id;
open c_student(10);fetch c_student into v_student;
close c_student;
dbms_output.put_line(v_student.name);
close c_student;
end;begin
open c_student;
close c_student;
end;begin
end;
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
--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
--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
--loop方式遍歷游標
declare
v_bookname varchar2(100);
cursor c_book(i_id number)is select bookname from book where id = i_id;
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';
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';
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;end;end;begin
或
declare
while循環遍歷游標，注意，第一次游標剛打開就fetch，%found為null，進不去循環
解決方法：
while nvl(c_student%found,true)loop
declare
for循環遍歷，最簡單，用的最多，不需要聲明v_student,Open和Close游標和fetch操作(不用打開游標和關閉游標，實現遍歷游標最高效方式)
declare
v_bookname varchar2(100);
cursor c_book(i_id number)is select bookname from book where id = i_id;
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
update book set price = '33' where bookname = v_bookname;
End Loop;
Close c_book;
v_bookname varchar2(100);
cursor c_book(i_id number)is select bookname from book where id = i_id;
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;
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;
begin
end;
begin
into v_bookname;
end;
cursor c_book(i_id number)is select bookname from book where id = i_id;
for cur in c_book(i_id)--直接將入參i_id傳入cursor即可
loop
update book set price = '53' where bookname = cur.bookname;
end loop;
begin
end;goto例子，一般不推薦使用goto，會使程序結構變亂
declare
Oracle存儲過程
在談存儲過程書寫中的一些規則時，先看一下執行它的規則，在命令窗口執行存儲過程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);
sp_get_product_prompt(83,ret1,ret2);
dbms_output.put_line(ret1);
i number:=0;
if i=0 then
goto hello;
end if;
<
begin
dbms_output.put_line('hello');
goto over;
begin
end;
<
begin
dbms_output.put_line('world');
goto over;
end;
<
dbms_output.put_line('over');
end;begin
dbms_output.put_line(ret2);
end;存儲過程入參，不論類型，缺省情況下值都為null,入參和出參不能有長度,其中關鍵字as可以替換成is,存儲過程中變量聲明在as和begin之間，同時，存儲過程中可以再調用其它的存儲過程，如果要保證存儲過程之間的事務處理不受影響，可以定義為自治事務。
create or replace procedure say_hello(v_name in varchar2, v_flag number, o_ret out number)as
begin
對于入參為null情況下給予缺省值
create or replace procedure say_hello(i_name in varchar2, i_flag number, o_ret out number)
as
或直接在insert語句中調用nvl函數賦缺省值
insert into phone(..,wname..,)values(..,nvl(v_name,' '),..);----如果將' '寫成'',則insert進來的v_name值還是為''等價于null值
帶一個參數的存儲過程
輸入參數in，輸入參數不能進行:=賦值，但可以將它賦給as后面定義的變量；
輸入參數in，可以作為變量進行條件判斷；默認不寫就是in； 存儲過程沒有重載，這個有參的say_hello會替代已經存在的無參say_hello。
create or replace procedure say_hello(v_name in varchar2)
as
begin
--v_name:='a';--存儲過程入參v_name不能做為賦值目標
dbms_output.put_line('hello '||v_name);
v_name varchar2(100);
if i_name is null then v_name := '0';
else
v_name := i_name;
end if;
insert into phone(..,wname..,)values(..,v_name,..);
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;end;end;
存儲過程輸入參數作為變量進行條件判斷
create or replace procedure say_hello(i_opFlag in number)
as
利用存儲過程中定義的變量對入參的空值處理：
create or replace procedure say_hello(i_name in varchar2)
as
多個參數的存儲過程
create or replace procedure say_hello(v_first_name in varchar2, v_last_name in varchar2)
as
begin
out輸出參數，用于利用存儲過程給一個或多個變量賦值，類似于返回值
create or replace procedure say_hello(v_name in varchar2，v_content out varchar2)begin
調用：
declare
v_con varchar2(200);
v_in varchar2(20):='wang';
v_content:='hello'||v_name;
end;dbms_output.put_line('hello '||v_first_name||'.'||v_last_name);
end;v_name varchar2(100);
if i_name is null then v_name :='0';
else v_name :=i_name;--將入賦值給定義變量
end if;
dbms_output.put_line('hello '||v_name);
begin
v_name varchar2(100);
if i_opFlag = 1 then
v_name :='0';
else
v_name :='haha';
end if;
dbms_output.put_line('hello '||v_name);
begin
end;end;begin
in out參數，既賦值又取值
create or replace procedure say_hello(v_name in out varchar2)
as
begin
v_name:='hi '||v_name;
end;調用：
declare
對存儲過程入參賦缺省值
create or replace procedure say_hello（v_name varchar2 default 'susu', v_content varchar2 default 'hello')
as
begin
調用：(用指明形參名的方式調用更好)
begin
say_hello();
end;
或
begin
say_hello('cheng');
end;
或
begin
PLSQL中的function FUNCTION和PROCEDURE的區別
1、函數有返回值，過程沒有
2、函數調用在一個表達式中，過程則是作為pl/sql程序的一個語句
say_hello(v_name=>'cheng');
end;dbms_output.put_line(v_name||' '||v_content);
end;v_inout varchar2(20):='wangsu';
say_hello(v_inout);
dbms_output.put_line(v_inout);
begin
say_hello(v_in,v_con);
dbms_output.put_line(v_con);
end;end;
過程和函數都以編譯后的形式存放在數據庫中，函數可以沒有參數也可以有多個參數并有一個返回值。
過程有零個或多個參數，沒有返回值。函數和過程都可以通過參數列表接收或返回零個或多個值，函數和過程的主要區別不在于返回值，而在于他們的調用方式，過程是作為一個獨立執行語句調用的，函數以合法的表達式的方式調用
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);
v_ret:=func(v_inout);--返回調用v_inout值(作為出參)
dbms_output.put_line(v_ret);--打印func結果
dbms_output.put_line(v_inout);--返回v_name結果
begin
end;
第二篇：oracle plsql 開窗函數over學習總結
連續求和與求總和的區別 D 為天，S 為銷售業績為每天計算銷售總額。
SELECTSUM(s)OVER(ORDERBY d),SUM(s)OVER()
FROM(SELECT'A'“A”,1 D, 20 SFROM DUAL
UNIONALL
SELECT'A'“A”,2 D, 15 SFROM DUAL
UNIONALL
SELECT'A'“A”,3 D, 14 SFROM DUAL
UNIONALL
SELECT'A'“A”,4 D, 18 SFROM DUAL
UNIONALL
SELECT'A'“A”,5 D, 30 SFROM DUAL)；
各種求和舉例 CREATE TABLETEST_ZHUXP(DEPTNOVARCHAR2(10), ENAME VARCHAR2(10), SAL VARCHAR2(10));--部門姓名薪水
SELECTtest_zhuxp.*,sum(sal)over(partitionbydeptnoorderbyename)部門連續求和,--各部門的薪水“連續”求和
sum(sal)over(partitionbydeptno)部門總和,--部門統計的總和，同一部門總和不變
100*round(sal/sum(sal)over(partitionbydeptno),4)“部門份額(%)”, sum(sal)over(orderbydeptnoDESC,ename)連續求和,--所有部門的薪水“連續”求和
sum(sal)over()總和--此處sum(sal)over()等同于sum(sal)，所有員工的薪水總和
100*round(sal/sum(sal)over(),4)“總份額(%)”
FROMtest_ZHUXP
注意求和后可以排序不影響結果
SELECT DEPTNO,ENAME,SAL,SUM(SAL)OVER(PARTITIONBY DEPTNO ORDERBY DEPTNO DESC, SAL DESC)部門連續求和,SUM(SAL)OVER(ORDERBY DEPTNO DESC, SAL DESC)公司連續求和
FROM TEST_ZHUXP
排序
1.在求第一名成績的時候，不能用row_number()，因為如果同班有兩個并列第一，row_number()只返回一個結果
2.rank()和dense_rank()的區別是：
rank()是跳躍排序，有兩個第二名時接下來就是第四名dense_rank()l是連續排序，有兩個第二名時仍然跟著第三名
SELECTt.*,RANK()OVER(PARTITIONBYCLASSORDERBY S DESC),dense_rank()OVER(PARTITIONBYCLASSORDERBY S DESC),ROW_NUMBER()OVER(PARTITIONBYCLASSORDERBY S DESC)
FROM(SELECT'a' “NAME”,1 “CLASS”,80 “S”FROM DUAL
UNIONALL
SELECT'b' “NAME”,1 “CLASS”,89 “S” FROM DUAL
UNIONALL
SELECT'c' “NAME”,1 “CLASS”,89 “S” FROM DUAL
UNIONALL
SELECT'e' “NAME”,3 “CLASS”,100 “S” FROM DUAL
UNIONALL
SELECT'f' “NAME”,3 “CLASS”,100 “S” FROM DUAL
UNIONALL
SELECT'g' “NAME”,3 “CLASS”,79 “S” FROM DUAL)t
統計
和group by的區別是可以看到每一行數據的所有信息
注意加NAME后的區別
SELECTt.*,SUM(1)OVER(PARTITIONBYCLASSORDERBYCLASS/*NAME*/)
FROM(SELECT'a' “NAME”,1 “CLASS”,80 “S”FROM DUAL
UNIONALL
SELECT'b' “NAME”,1 “CLASS”,89 “S” FROM DUAL
UNIONALL
SELECT'c' “NAME”,1 “CLASS”,89 “S” FROM DUAL
UNION ALL
SELECT'e' “NAME”,1 “CLASS”,100 “S” FROM DUAL
UNION ALL
SELECT'f' “NAME”,3 “CLASS”,100 “S” FROM DUAL
UNION ALL
SELECT'g' “NAME”,3 “CLASS”,79 “S” FROM DUAL)t
開窗函數
開窗函數
開窗函數指定了分析函數工作的數據窗口大小，這個數據窗口大小可能會隨著行的變化而變化，舉例如下：
1：
over（orderby xxx）按照xxx排序進行累計，order by是個默認的開窗函數
over（partitionbyxxx）按照部門分區
2：
over（orderby salary rangebetween5precedingand5following）
每行對應的數據窗口是之前行幅度值不超過5，之后行幅度值不超過5
例如:對于以下列
aa
sum(aa)over（orderbyaarangebetween2precedingand2following）
得出的結果是
AASUM
214
214
214
318
418
522
618
722
就是說，對于aa=5的一行，sum為5-1<=aa<=5+2的和
對于aa=2來說，sum=1+2+2+2+3+4=14；
又如對于aa=9，9-1<=aa<=9+2只有9一個數，所以sum=9；
3：其它：
over（orderby salary rowsbetween2precedingand4following）
每行對應的數據窗口是之前2行，之后4行
4：下面三條語句等效：
over（orderby salary rowsbetweenunboundedprecedingandunboundedfollowing）每行對應的數據窗口是從第一行到最后一行，等效：
over（orderby salary
rangebetweenunboundedprecedingandunboundedfollowing）
等效over(partitionbynull)
任意刪除重復行
在這個表中如果class與score相同，就考慮這行數據多余，刪除多余行，就隨便保留一行。
NAMECLASSSCORE
------------------------------
1.ff197
2.gg189
3.ll196
4.jj289
5.oo287
6.ii198
7.kk293
8.uu397
9.rr395
10.ee392
11.yy290
12.mm4100
13.nn498
14.pp198
15.fft197
16.ggt189
17.oot287
18.kkt293
19.ffff197
SQL> delete from c_score t where rowid in(select rowid from(select rowid ,row_number()over(partition by class,score order by class)dup_num from c_score)t where t.dup_num>1);
第三篇：Oracle使用工具plsql遠程連接問題
使用PL/SQL Developer遠程連接Oracle數據庫，本地機器上已經安裝了Oracle數據庫只要
1.配置tnsnames.ora（我的安裝在D:oracleproduct10.2.0db_1networkADMIN）
2.登錄plsql時填寫相應信息
第一步：配置tnsnames.ora
找到tnsnames.ora文件，用記事本方式打開，你可以看到像如下配置信息
ORCL =
(DESCRIPTION =
(ADDRESS =(PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)))
上面這段是連接本地（安裝好Oracle默認的,你也可以修改數據庫別名，將“ORCL”換成其他的）的數據庫的要連接遠程的Oracle數據庫則再增加配置信息如下
自己定義遠程數據庫名稱(別名)=
(DESCRIPTION =
(ADDRESS =(PROTOCOL = TCP)(HOST = 遠程服務器IP)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = 遠程服務器上數據庫名稱)))
注意：別名不能與其他數據庫（本地、遠程）的別名相同，它可以跟相應遠程服務器上的數據庫名稱不一樣，要連接多個遠程Oracle數據庫，照樣子在來幾段上述配置信息即可。
第二步：登錄plsql
Username:
Password:
Database:要登錄的數據庫名稱(別名)
Connect as:
注意：一定要寫對上面面的紅色部分（其余的3個字段我就不說了），且跟tnsnames.ora配置文件中的別名相同
知道這兩步后，以后不管是遠程還是本地上的數據庫你想怎么連就怎么連！只是tnsnames.ora文件中多了幾段配置信息。
第四篇：PLSQL學習總結
PL/SQL集合批量綁定（Bulk binds）減少循環開銷PL/SQL引擎會執行過程化語句，但它把SQL語句傳送給SQL引擎處理，然后SQL引擎把處理的結果返回給PL/SQL引擎。
PL/SQL和SQL引擎間的頻繁切換會大大降低效率。典型的情況就是在一個循環中反復執行SQL語句。
批量綁定如何提高性能？
在SQL語句中為PL/SQL變量賦值稱為綁定，PL/SQL綁定操作分為三種：
1.內綁定(in-bind)：用INSERT或UPDATE語句將PL/SQL發量或主變量保存到數據庫。
2.外綁定(out-bind)：途過INSERT、UPDATE或DELETE語句的RETURNING子句返回值為PL/SQL變量或主變量賦值。
3.定義(define)：使用SELECT或FETCH語句為PL/SQL變量或主變量賦值.DML語句可以一次性傳遞集合中所有的元素，這個過程就是批量綁定。如果集合有20個元素，批量綁定的一次操作就相當于執行20次SELECT、INSERT、UPDATE或DELETE語句。綁定技術是靠減少PL/SQL和SQL引擎間的切換次數來提高性能的。
批量綁定（Bulk binds）包括：
(i)Input collections, use the FORALL statement，一般用來改善DML（INSERT、UPDATE和DELETE)操作的性能
(ii)Output collections, use BULK COLLECT clause，一般用來提高查詢（SELECT）的性能
例一：對DELETE語句應用批量綁定
下面的DELETE語句只往SQL引擎中發送一次，即使是執行了三次DELETE操作： DECLARE
TYPE numlist IS VARRAY(20)OF NUMBER;
depts numlist := numlist(10, 30, 70);
BEGIN
FORALL i IN depts.FIRST..depts.LAST
DELETE FROM emp WHERE deptno = depts(i);
END;
當執行DELETE的數據數量非常大時，使用上述批量綁定方法能大大的提高運行效率。注意集合變長數組類型只能通過構造函數初始化，不能像索引表和嵌套表一樣通過FOR循環直接賦值，具體可見下例。
例二：對INSERT語句應用批量綁定
CREATE TABLE blktest(num NUMBER(20), name varchar2(50));
DECLARE
type numtab is table of number(20)INDEX by binary_integer;
type nametab is TABLE of VARCHAR2(20)INDEX by binary_integer;
pnums
numtab;
pnames
nametab;
t1
number;
t2
number;
t3
number;
begin
for j in 1..100000?為集合索引表變量循環賦值
loop
pnums(j):= j;
pnames(j):= 'Seq No' || to_char(j);
end loop;
select Dbms_Utility.get_time into t1 from dual;--獲得FOR運行前時間
for i in 1..100000?使用普通FOR循環插入10000條數據
loop
insert into blktest values(pnums(i),pnames(i));
end loop;
select dbms_utility.get_time into t2 FROM dual;獲得FOR運行后時間和FORALL運行前時間
forall i in 1..100000?使用FORALL循環即集合批量綁定方法插入10000條數據
insert into blktest values(pnums(i),pnames(i));
select dbms_utility.get_time
into t3 from dual;--獲得FORALL運行后時間
dbms_output.put_line('Execution Time(secs)');
dbms_output.put_line('------------------------');
dbms_output.put_line('FOR loop: ' || TO_CHAR(t2t2));
END;
運行結果：
Execution Time(secs)
------------------------
FOR loop: 982
FORALL loop: 49
總結：
1.在PL/SQL DEVELOPER的SQL WINDOWS中獲得數據庫當前時間的方法是：dbms_utility.get_time；如果需要測試某一段PLSQL的性能，則只需在開始和結束的時候分別獲取數據庫時間并做差即可，如上例中分別獲取兩種循環的耗時。
2.上例中，我們把10000個編號和名稱放到索引表中。所有的表元素都向數據庫插入兩次：第一次使用FOR循環，然后使用FORALL語句。實際上，FORALL版本的代碼執行速度要比FOR語句版本的快得多。
3.上述測試過程也可以通過在PL/SQL DEVELOPER的COMMAND WINDOWS中先編寫PROCEDURE再運行的方法實現。在COMMAND WINDOWS中設置顯示OUTPUT方法如下：
本文章未結束，請繼續查看 “PLSQL學習總結：批量綁定減少循環開銷--張凱(2)”
第五篇：解決64位win7下plsql連接64位oracle的問題
解決win7 x64系統下PL/SQL無法連接64位Oracle數據庫的方法
#問題描述：確定安裝好Oracle 11g 64位客戶端、PL/SQL developer（不區分32/64）后，打開PL/SQL，無法選擇數據庫實例，如下圖：
#解決辦法：
1.下載oracle官網提供的文件instantclient-basic-win32-11.2.0.1.0； 下載地址：http://pan.baidu.com/s/1c0CMOwS
2.將instantclient-basic-win32-11.2.0.1.0 文件加壓至oracle文件目錄product下：
3.復制oracle安裝文件夾..networkadmin下的文件 listener.ora和tnsnames.ora 到..productinstantclient_11_2的下面
4.進入PL/SQL Developer安裝目錄，新建start.bat文件，用記事本編輯寫入： @echo off
set path=D:appAdministratorproductinstantclient_11_2 set ORACLE_HOME=D:appAdministratorproductinstantclient_11_2 set TNS_ADMIN=D:appAdministratorproductinstantclient_11_2 set NLS_LANG=AMERICAN_AMERICA.AL32UTF8 start plsqldev.exe
##此處的所有文件目錄需要修改成自己的目錄.5.點擊運行start.bat, 正常情況會打開PL/SQL Developer（此時已經可以用來正常登錄）
6.第5步正常開打登錄窗戶后，點擊取消或登錄進入PL/SQL主界面：
1)點擊“Tools--Preferences”后，修改Oracle主目錄名以及OCI庫目錄，目錄中都應包含instantclient_11_2目錄
此時保存退出后，完成對PL/SQL的修改
修改電腦環境變量，默認為oracle安裝目錄，需添加/修改為instantclient_11_2目錄：
path=D:appAdministratorproductinstantclient_11_2 ORACLE_HOME=D:appAdministratorproductinstantclient_11_2 TNS_ADMIN=D:appAdministratorproductinstantclient_11_2 編輯完成保存后，PL/SQL即可正常使用。
點擊咨詢 