第一篇：Oracle之PLSQL總結(jié)

基本數(shù)據(jù)類型變量 1.基本數(shù)據(jù)類型

Number 數(shù)字型

Int 整數(shù)型

Pls_integer 整數(shù)型，產(chǎn)生溢出時出現(xiàn)錯誤

Binary_integer 整數(shù)型，表示帶符號的整數(shù)

Char 定長字符型，最大255個字符

Varchar2 變長字符型，最大2000個字符

Long 變長字符型，最長2GB

Date 日期型

Boolean 布爾型（TRUE、FALSE、NULL三者取一）

在PL/SQL中使用的數(shù)據(jù)類型和Oracle數(shù)據(jù)庫中使用的數(shù)據(jù)類型，有的含義是完全一致的，有的是有不同的含義的。

2.基本數(shù)據(jù)類型變量的定義方法

變量名 類型標識符 [not null]:=值;

declare

age number(3):=26;--長度為3，初始值為26

commit;

begin

end;

其中，定義常量的語法格式：常量名 constant 類型標識符 [not null]:=值;

declare

pi constant number(9):=3.1415926;--為pi的數(shù)字型常量，長度為9，初始值為3.1415926

begin

表達式

變量、常量經(jīng)常需要組成各種表達式來進行運算，下面介紹在PL/SQL中常見表達式的運算規(guī)則。

1.數(shù)值表達式

PL/SQL程序中的數(shù)值表達式是由數(shù)值型常數(shù)、變量、函數(shù)和算術(shù)運算符組成的，可以使用的算術(shù)運算符包括+（加法）、-（減法）、*（乘法）、/（除法）和**（乘方）等。

命令窗口中執(zhí)行下列PL/SQL程序，該程序定義了名為result的整數(shù)型變量，計算的是10+3*4-20+5**2的值，理論結(jié)果應(yīng)該是27。

set serveroutput on

Declare

result integer;

result:=10+3*4-20+5**2;

begin

commit;

end;dbms_output.put_line('運算結(jié)果是：'||to_char(result));

end;

dbms_output.put_line函數(shù)輸出只能是字符串，因此利用to_char函數(shù)將數(shù)值型結(jié)果轉(zhuǎn)換為字符型。

2.字符表達式

字符表達式由字符型常數(shù)、變量、函數(shù)和字符運算符組成，唯一可以使用的字符運算符就是連接運算符“||”。

3.關(guān)系表達式

關(guān)系表達式由字符表達式或數(shù)值表達式與關(guān)系運算符組成，可以使用的關(guān)系運算符包括以下9種。

4.邏輯表達式

邏輯表達式由邏輯常數(shù)、變量、函數(shù)和邏輯運算符組成，常見的邏輯運算符包括以下3種。

PLSQL函數(shù)

PL/SQL程序中提供了很多函數(shù)供擴展功能，除了標準SQL語言的函數(shù)可以使用外，最常見的數(shù)據(jù)類型轉(zhuǎn)換函數(shù)有以下3個。

系統(tǒng)輸出打印

利用pl/sql在數(shù)據(jù)庫服務(wù)器端打印一句話：

set serveroutput on--設(shè)置數(shù)據(jù)庫輸出，默認為關(guān)閉，每次重新打開窗口需要重新設(shè)置。

BEGIN

DBMS_OUTPUT.PUT_LINE('Hello PL/SQL');

END;To_char：將其他類型數(shù)據(jù)轉(zhuǎn)換為字符型。

To_date：將其他類型數(shù)據(jù)轉(zhuǎn)換為日期型。

To_number：將其他類型數(shù)據(jù)轉(zhuǎn)換為數(shù)值型。

繼續(xù)追加中..NOT：邏輯非

OR：邏輯或

AND：邏輯與

運算的優(yōu)先次序為NOT、AND和OR。< 小于

> 大于

= 等于（不是賦值運算符:=）

like 類似于

in 在??之中

<= 小于等于

>= 大于等于

!= 不等于 或<>

between 在??之間

關(guān)系型表達式運算符兩邊的表達式的數(shù)據(jù)類型必須一致。

pl/sql程序中對大小寫不敏感（打印聲明的變量）

set serveroutput on

DECLARE

pl語句塊是pl/sql里最小的編程塊，其中可以再嵌套 PL/SQL中的變量聲明

所有變量必須在declare中聲明，程序中不允許聲明。沒有初始化的變量默認值為null，屏幕上null是看不見的，命名習(xí)慣：PL/SQL中變量一般以v_開頭（等同于存儲過程中as和begin區(qū)域的變量定義習(xí)慣）。

注意number也能存小數(shù)，最長38位，所以以后建議整數(shù)都用binary_integer存。

long是字符類型，boolean類型不能打印。

標準變量類型：數(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;

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ù)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

個數(shù)或類型錯誤”

end;

備注：關(guān)于聲明number(4,3)中括號中的兩個數(shù)字的意義，前面的數(shù)字叫精度，后面的叫刻度?？潭龋寒斂潭葹檎龜?shù)的時候，表示四舍五入到小數(shù)點后面的位數(shù),當刻度為負數(shù)的時候，表示四舍五入到小數(shù)點前面的位數(shù)

精度：從數(shù)字的最前面不為零開始到刻度精確到的位置

―――――――――――――――――――――――――――――――――――――

v_Number number(4,3):=123.12312

1、按刻度進行四舍五入得到123.123

2、確定刻度精確到的位置123123處，精度為6位（.符號不算）

3、根據(jù)精度進行判斷6位（>4）精度上限值

--報錯不能存儲

―――――――――――――――――――――――――――――――――――――

number(3,-3):=44445

1、根據(jù)刻度－3進行四舍五入得到44000

2、小數(shù)點向前移動3位44.此位置為刻度精確到的位置

3、根據(jù)精度進行判斷2位（<3）精度上限值

--不報錯可存儲結(jié)果為44000

DECLARE

v_Number number(4,3):=123.12312;--實際精度6位大于上限精度值4位，提示“ORA-06502: PL/SQL: 數(shù)字或值錯誤 : 數(shù)值精度太高”

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復(fù)合類型

記錄類型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子句代表以符號整數(shù)為索引，這樣訪問表類型變量中的數(shù)據(jù)方法就是“表變量名(索引符號整數(shù))”。

table類型，相當于java中的Map容器，就是一個可變長的數(shù)組，key（符號整數(shù)索引）必須是整數(shù)，可以是負數(shù)，value（類型）可以是標量，也可以是record類型?？梢圆话错樞蛸x值，但必須先賦值后使用。

――――――――――――――――――――――――――――――――――――― 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的多維表類型，相當于多維數(shù)組，table1是多維表類型變量，將數(shù)據(jù)表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等屬性進行操作，使用方法為“表變量名.屬性”，返回的是數(shù)字。

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('總記錄數(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));

begin

end;

――――――――――――――――――――――――――――――――――――― %type和%rowtype

使用%type定義變量，為了讓PL/SQL中變量的類型和數(shù)據(jù)表中的字段的數(shù)據(jù)類型一致，Oracle 9i提供了%type定義方法。

這樣當數(shù)據(jù)表的字段類型修改后，PL/SQL程序中相應(yīng)變量的類型也自動修改。

―――――――――――――――――――――――――――――――――――――

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變量的可見空間

變量的作用域和可見性，變量的作用域為變量申明開始到當前語句塊結(jié)束。

當外部過程和內(nèi)嵌過程定義了相同名字的變量的時候，在內(nèi)嵌過程中如果直接寫這個變量名是沒有辦法訪問外部過程的變量的，可以通過給外部過程定義一個名字<>,通過outername變量名來訪問外部過程的變量（待測試..）。

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);解開后執(zhí)行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循環(huán)，注意推出exit是推出循環(huán)，而不是推出整個代碼塊

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循環(huán)

declare

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');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、處理異常

拋出異常后的邏輯代碼不會被繼續(xù)執(zhí)行

異常的定義使用 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拋出異常,用此關(guān)鍵字，拋出后轉(zhuǎn)到自定義的e_myException，執(zhí)行其里面的--再跳到end處，結(jié)束PL/SQL塊，raise接下面的2句不會繼續(xù)執(zhí)行。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函數(shù)后，--當前會話執(zhí)行狀態(tài)，錯誤編碼

end;

―――――――――――――――――――――――――――――――――――――

PLSQL游標和goto語句

備注：下面提到的游標為靜態(tài)cursor，包括顯示和隱式。

游標，從declare、open、fetch、close是一個完整的生命旅程。當然了一個這樣的游標是可以被多次open進行使用的，顯式cursor是靜態(tài)cursor，她的作用域是全局的，但也必須明白，靜態(tài)cursor也只有pl/sql代碼才可以使用它。

靜態(tài)游標變量是在定義時就必須指定SQL語句。

cursor 游標(結(jié)果集)用于提取多行數(shù)據(jù)，定義后不會有數(shù)據(jù)，使用后才有。一旦游標被打開，就無法再次打開(可以先關(guān)閉，再打開)。

declare

第二種游標的定義方式，用變量控制結(jié)果集的數(shù)量。

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

第三種游標的定義方式，帶參數(shù)的游標，用的最多。

declare

cursor c_student(v_id binary_integer)is select * from book where id>v_id;

open c_student(10);

游標的使用，一定別忘了關(guān)游標。

declare

如何遍歷游標fetch

游標的屬性 %found，%notfound,%isopen,%rowcount。

%found:若前面的fetch語句返回一行數(shù)據(jù)，則%found返回true，如果對未打開的游標使用則報ORA-1001異常。

%notfound，與%found行為相反。

%isopen，判斷游標是否打開。

%rowcount:當前游標的指針位移量，到目前位置游標所檢索的數(shù)據(jù)行的個數(shù)，若未打開就引用，返回ORA-1001。

注： no_data_found和%notfound的用法是有區(qū)別的，小結(jié)如下

1）SELECT...INTO 語句觸發(fā) no_data_found；

2）當一個顯式光標(靜態(tài)和動態(tài))的 where 子句未找到時觸發(fā) %notfound；

3）當UPDATE或DELETE 語句的where 子句未找到時觸發(fā) sql%notfound；

4）在光標的提取(Fetch)循環(huán)中要用 %notfound 或%found 來確定循環(huán)的退出條件，不要用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循環(huán)遍歷游標，注意，第一次游標剛打開就fetch，%found為null，進不去循環(huán)

解決方法：

while nvl(c_student%found,true)loop

declare

for循環(huán)遍歷，最簡單，用的最多，不需要聲明v_student,Open和Close游標和fetch操作(不用打開游標和關(guān)閉游標，實現(xiàn)遍歷游標最高效方式)

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)--直接將入?yún)_id傳入cursor即可

loop

update book set price = '53' where bookname = cur.bookname;

end loop;

begin

end;goto例子，一般不推薦使用goto，會使程序結(jié)構(gòu)變亂

declare

Oracle存儲過程

在談存儲過程書寫中的一些規(guī)則時，先看一下執(zhí)行它的規(guī)則，在命令窗口執(zhí)行存儲過程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;存儲過程入?yún)?，不論類型，缺省情況下值都為null,入?yún)⒑统鰠⒉荒苡虚L度,其中關(guān)鍵字as可以替換成is,存儲過程中變量聲明在as和begin之間，同時，存儲過程中可以再調(diào)用其它的存儲過程，如果要保證存儲過程之間的事務(wù)處理不受影響，可以定義為自治事務(wù)。

create or replace procedure say_hello(v_name in varchar2, v_flag number, o_ret out number)as

begin

對于入?yún)閚ull情況下給予缺省值

create or replace procedure say_hello(i_name in varchar2, i_flag number, o_ret out number)

as

或直接在insert語句中調(diào)用nvl函數(shù)賦缺省值

insert into phone(..,wname..,)values(..,nvl(v_name,' '),..);----如果將' '寫成'',則insert進來的v_name值還是為''等價于null值

帶一個參數(shù)的存儲過程

輸入?yún)?shù)in，輸入?yún)?shù)不能進行:=賦值，但可以將它賦給as后面定義的變量；

輸入?yún)?shù)in，可以作為變量進行條件判斷；默認不寫就是in； 存儲過程沒有重載，這個有參的say_hello會替代已經(jīng)存在的無參say_hello。

create or replace procedure say_hello(v_name in varchar2)

as

begin

--v_name:='a';--存儲過程入?yún)_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;

存儲過程輸入?yún)?shù)作為變量進行條件判斷

create or replace procedure say_hello(i_opFlag in number)

as

利用存儲過程中定義的變量對入?yún)⒌目罩堤幚恚?/p>

create or replace procedure say_hello(i_name in varchar2)

as

多個參數(shù)的存儲過程

create or replace procedure say_hello(v_first_name in varchar2, v_last_name in varchar2)

as

begin

out輸出參數(shù)，用于利用存儲過程給一個或多個變量賦值，類似于返回值

create or replace procedure say_hello(v_name in varchar2，v_content out varchar2)begin

調(diào)用：

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參數(shù)，既賦值又取值

create or replace procedure say_hello(v_name in out varchar2)

as

begin

v_name:='hi '||v_name;

end;調(diào)用：

declare

對存儲過程入?yún)①x缺省值

create or replace procedure say_hello（v_name varchar2 default 'susu', v_content varchar2 default 'hello')

as

begin

調(diào)用：(用指明形參名的方式調(diào)用更好)

begin

say_hello();

end;

或

begin

say_hello('cheng');

end;

或

begin

PLSQL中的function FUNCTION和PROCEDURE的區(qū)別

1、函數(shù)有返回值，過程沒有

2、函數(shù)調(diào)用在一個表達式中，過程則是作為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;

過程和函數(shù)都以編譯后的形式存放在數(shù)據(jù)庫中，函數(shù)可以沒有參數(shù)也可以有多個參數(shù)并有一個返回值。

過程有零個或多個參數(shù)，沒有返回值。函數(shù)和過程都可以通過參數(shù)列表接收或返回零個或多個值，函數(shù)和過程的主要區(qū)別不在于返回值，而在于他們的調(diào)用方式，過程是作為一個獨立執(zhí)行語句調(diào)用的，函數(shù)以合法的表達式的方式調(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);

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é)果

begin

end;

第二篇：oracle plsql 開窗函數(shù)over學(xué)習(xí)總結(jié)

連續(xù)求和與求總和的區(qū)別 D 為天，S 為銷售業(yè)績?yōu)槊刻煊嬎沅N售總額。

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)部門連續(xù)求和,--各部門的薪水“連續(xù)”求和

sum(sal)over(partitionbydeptno)部門總和,--部門統(tǒng)計的總和，同一部門總和不變

100*round(sal/sum(sal)over(partitionbydeptno),4)“部門份額(%)”, sum(sal)over(orderbydeptnoDESC,ename)連續(xù)求和,--所有部門的薪水“連續(xù)”求和

sum(sal)over()總和--此處sum(sal)over()等同于sum(sal)，所有員工的薪水總和

100*round(sal/sum(sal)over(),4)“總份額(%)”

FROMtest_ZHUXP

注意求和后可以排序不影響結(jié)果

SELECT DEPTNO,ENAME,SAL,SUM(SAL)OVER(PARTITIONBY DEPTNO ORDERBY DEPTNO DESC, SAL DESC)部門連續(xù)求和,SUM(SAL)OVER(ORDERBY DEPTNO DESC, SAL DESC)公司連續(xù)求和

FROM TEST_ZHUXP

排序

1.在求第一名成績的時候，不能用row_number()，因為如果同班有兩個并列第一，row_number()只返回一個結(jié)果

2.rank()和dense_rank()的區(qū)別是：

rank()是跳躍排序，有兩個第二名時接下來就是第四名dense_rank()l是連續(xù)排序，有兩個第二名時仍然跟著第三名

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

統(tǒng)計

和group by的區(qū)別是可以看到每一行數(shù)據(jù)的所有信息

注意加NAME后的區(qū)別

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

開窗函數(shù)

開窗函數(shù)

開窗函數(shù)指定了分析函數(shù)工作的數(shù)據(jù)窗口大小，這個數(shù)據(jù)窗口大小可能會隨著行的變化而變化，舉例如下：

1：

over（orderby xxx）按照xxx排序進行累計，order by是個默認的開窗函數(shù)

over（partitionbyxxx）按照部門分區(qū)

2：

over（orderby salary rangebetween5precedingand5following）

每行對應(yīng)的數(shù)據(jù)窗口是之前行幅度值不超過5，之后行幅度值不超過5

例如:對于以下列

aa

sum(aa)over（orderbyaarangebetween2precedingand2following）

得出的結(jié)果是

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一個數(shù)，所以sum=9；

3：其它：

over（orderby salary rowsbetween2precedingand4following）

每行對應(yīng)的數(shù)據(jù)窗口是之前2行，之后4行

4：下面三條語句等效：

over（orderby salary rowsbetweenunboundedprecedingandunboundedfollowing）每行對應(yīng)的數(shù)據(jù)窗口是從第一行到最后一行，等效：

over（orderby salary

rangebetweenunboundedprecedingandunboundedfollowing）

等效over(partitionbynull)

任意刪除重復(fù)行

在這個表中如果class與score相同，就考慮這行數(shù)據(jù)多余，刪除多余行，就隨便保留一行。

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數(shù)據(jù)庫，本地機器上已經(jīng)安裝了Oracle數(shù)據(jù)庫只要

1.配置tnsnames.ora（我的安裝在D:oracleproduct10.2.0db_1networkADMIN）

2.登錄plsql時填寫相應(yīng)信息

第一步：配置tnsnames.ora

找到tnsnames.ora文件，用記事本方式打開，你可以看到像如下配置信息

ORCL =

(DESCRIPTION =

(ADDRESS =(PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)))

上面這段是連接本地（安裝好Oracle默認的,你也可以修改數(shù)據(jù)庫別名，將“ORCL”換成其他的）的數(shù)據(jù)庫的要連接遠程的Oracle數(shù)據(jù)庫則再增加配置信息如下

自己定義遠程數(shù)據(jù)庫名稱(別名)=

(DESCRIPTION =

(ADDRESS =(PROTOCOL = TCP)(HOST = 遠程服務(wù)器IP)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = 遠程服務(wù)器上數(shù)據(jù)庫名稱)))

注意：別名不能與其他數(shù)據(jù)庫（本地、遠程）的別名相同，它可以跟相應(yīng)遠程服務(wù)器上的數(shù)據(jù)庫名稱不一樣，要連接多個遠程Oracle數(shù)據(jù)庫，照樣子在來幾段上述配置信息即可。

第二步：登錄plsql

Username:

Password:

Database:要登錄的數(shù)據(jù)庫名稱(別名)

Connect as:

注意：一定要寫對上面面的紅色部分（其余的3個字段我就不說了），且跟tnsnames.ora配置文件中的別名相同

知道這兩步后，以后不管是遠程還是本地上的數(shù)據(jù)庫你想怎么連就怎么連！只是tnsnames.ora文件中多了幾段配置信息。

第四篇：PLSQL學(xué)習(xí)總結(jié)

PL/SQL集合批量綁定（Bulk binds）減少循環(huán)開銷PL/SQL引擎會執(zhí)行過程化語句，但它把SQL語句傳送給SQL引擎處理，然后SQL引擎把處理的結(jié)果返回給PL/SQL引擎。

PL/SQL和SQL引擎間的頻繁切換會大大降低效率。典型的情況就是在一個循環(huán)中反復(fù)執(zhí)行SQL語句。

批量綁定如何提高性能？

在SQL語句中為PL/SQL變量賦值稱為綁定，PL/SQL綁定操作分為三種：

1.內(nèi)綁定(in-bind)：用INSERT或UPDATE語句將PL/SQL發(fā)量或主變量保存到數(shù)據(jù)庫。

2.外綁定(out-bind)：途過INSERT、UPDATE或DELETE語句的RETURNING子句返回值為PL/SQL變量或主變量賦值。

3.定義(define)：使用SELECT或FETCH語句為PL/SQL變量或主變量賦值.DML語句可以一次性傳遞集合中所有的元素，這個過程就是批量綁定。如果集合有20個元素，批量綁定的一次操作就相當于執(zhí)行20次SELECT、INSERT、UPDATE或DELETE語句。綁定技術(shù)是靠減少PL/SQL和SQL引擎間的切換次數(shù)來提高性能的。

批量綁定（Bulk binds）包括：

(i)Input collections, use the FORALL statement，一般用來改善DML（INSERT、UPDATE和DELETE)操作的性能

(ii)Output collections, use BULK COLLECT clause，一般用來提高查詢（SELECT）的性能

例一：對DELETE語句應(yīng)用批量綁定

下面的DELETE語句只往SQL引擎中發(fā)送一次，即使是執(zhí)行了三次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;

當執(zhí)行DELETE的數(shù)據(jù)數(shù)量非常大時，使用上述批量綁定方法能大大的提高運行效率。注意集合變長數(shù)組類型只能通過構(gòu)造函數(shù)初始化，不能像索引表和嵌套表一樣通過FOR循環(huán)直接賦值，具體可見下例。

例二：對INSERT語句應(yīng)用批量綁定

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?為集合索引表變量循環(huán)賦值

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循環(huán)插入10000條數(shù)據(jù)

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循環(huán)即集合批量綁定方法插入10000條數(shù)據(jù)

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;

運行結(jié)果：

Execution Time(secs)

------------------------

FOR loop: 982

FORALL loop: 49

總結(jié)：

1.在PL/SQL DEVELOPER的SQL WINDOWS中獲得數(shù)據(jù)庫當前時間的方法是：dbms_utility.get_time；如果需要測試某一段PLSQL的性能，則只需在開始和結(jié)束的時候分別獲取數(shù)據(jù)庫時間并做差即可，如上例中分別獲取兩種循環(huán)的耗時。

2.上例中，我們把10000個編號和名稱放到索引表中。所有的表元素都向數(shù)據(jù)庫插入兩次：第一次使用FOR循環(huán)，然后使用FORALL語句。實際上，F(xiàn)ORALL版本的代碼執(zhí)行速度要比FOR語句版本的快得多。

3.上述測試過程也可以通過在PL/SQL DEVELOPER的COMMAND WINDOWS中先編寫PROCEDURE再運行的方法實現(xiàn)。在COMMAND WINDOWS中設(shè)置顯示OUTPUT方法如下：

本文章未結(jié)束，請繼續(xù)查看 “PLSQL學(xué)習(xí)總結(jié)：批量綁定減少循環(huán)開銷--張凱(2)”

第五篇：解決64位win7下plsql連接64位oracle的問題

解決win7 x64系統(tǒng)下PL/SQL無法連接64位Oracle數(shù)據(jù)庫的方法

#問題描述：確定安裝好Oracle 11g 64位客戶端、PL/SQL developer（不區(qū)分32/64）后，打開PL/SQL，無法選擇數(shù)據(jù)庫實例，如下圖：

#解決辦法：

1.下載oracle官網(wǎng)提供的文件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.復(fù)制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（此時已經(jīng)可以用來正常登錄）

6.第5步正常開打登錄窗戶后，點擊取消或登錄進入PL/SQL主界面：

1)點擊“Tools--Preferences”后，修改Oracle主目錄名以及OCI庫目錄，目錄中都應(yīng)包含instantclient_11_2目錄

此時保存退出后，完成對PL/SQL的修改

修改電腦環(huán)境變量，默認為oracle安裝目錄，需添加/修改為instantclient_11_2目錄：

path=D:appAdministratorproductinstantclient_11_2 ORACLE_HOME=D:appAdministratorproductinstantclient_11_2 TNS_ADMIN=D:appAdministratorproductinstantclient_11_2 編輯完成保存后，PL/SQL即可正常使用。