存儲過程是一種命名pl/sql程序塊,它可以被賦予參數,存儲在數據庫中,可以被用戶調用.由於存儲過程是已編譯好的代碼,所以在調用的時候不必再次編譯代碼，從而提高程序的運行效率。另外存儲過程可以實現程序的模塊化設計.
語法：
Create [or replace] procedure procedure_name
[ (parameter[{in|in out}]) data_type，
(parameter[{in|in out}]) data_type，
……
]
{ is|as}
Decoration section
Begin
Executable section;
Exception
Exception handlers;
End;
Procedure_name存儲過程的名稱
Parameter 參數
In 向存儲過程傳遞參數
Out：從存儲過程返回參數
In out:傳遞和返回參數
Data_type:參數的類型 不能夠指明長度
As|is後聲明的變量主要過程體,且不能加declare語句。
//創建一個插入emp中記錄的存儲過程
SQL> create procedure insert_emp as
begin
insert into emp(empno,ename,job,mgr,sal,comm,deptno)
values('7777','redarmy','teacher','7369',9000,1000,20);
commit;
end insert_emp;
/
2、調用存儲過程
SQL> set serveroutput on;
SQL> begin
insert_emp;
end;
/
3、修改存儲過程
SQL> create or replace procedure insert_emp as
//修改時只需加 or replace就可以了 裡邊的存儲過程就可以修改了
begin
insert into emp(empno,ename,job,mgr,sal,comm,deptno)
values('7777','redarmy','teacher','7369',9000,1000,20);
commit;
end insert_emp;
/
4、參數Oracle中有三種參數模型 in、out、in out
(1).in參數
該類型的參數值有調用者傳入,並且只能被存儲過程讀取,也是默認格式.
案例：
SQL> create or replace procedure insert_emp(
cempno in number,
cename in varchar2,
cjob in varchar2,
cmgr in number,
chiredate in date,
csal in number,
ccomm in number,
cdeptno in number
) as
begin
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(cempno,cename,cjob,cmgr,chiredate,csal,ccomm,cdeptno);
end insert_emp;
/
Procedure created
上面創建的存儲過程需要出入參數,在oralce有如下三種方式傳入參數
名稱表示法 語法如下：
參數名稱=>參數值;多個之間用逗號隔開
SQL> set serveroutput on;
SQL> begin
insert_emp(cempno=>7377,cename=>'mjjj',cjob=>'stu',cmgr=>7777,chiredate=>to_date('2011-01-01','YYYY-MM-dd'),csal=>5000,ccomm=>1000,cdeptno=>20);
end;
/
位置表示法 當參數比較多時,名稱表示法可能會比較長,為克服名稱表示法的弊端,可以采用位置表示法,注意參數一定要對應。
SQL> set serveroutput on;
SQL> begin
insert_emp(3333,'mjjj','stu',7777,to_date('2011-01-01','YYYY-MM-dd'),5000,1000,20);
end;
/
混合表示法
SQL> set serveroutput on;
SQL> begin insert_emp(9999,cename=>'mjjj',cjob=>'stu',cmgr=>7777,chiredate=>to_date('2011-01-01','YYYY-MM-dd'),csal=>5000,ccomm=>1000,cdeptno=>20);
end;
/
注意：當用戶使用的混合表示法時,分界線之前必須一致,分界線之後必須一致,並且不能穿插。
(2).out參數
該類型的參數值是有存儲過程寫入.out類型的參數適用於存儲過程向調用者返回多條信息的情況。
//創建一個根據員工編號查詢員工名稱及薪資存儲過程
SQL> create or replace procedure emp_select(cempno in number,cename out emp.ename%type,csal out emp.sal%type
) is
begin
select ename,sal into cename,csal from emp where empno=cempno;
exception
when NO_DATA_FOUND then
cename:='NULL';
csal:=0;
end emp_select;
/
Procedure created
調用存儲過程：
out輸出的參數是返回值,也就說在調用存儲過程的時候必須有提供能夠接受返回值的變量。
在這裡我們需要使用variable命令綁定參數
SQL> variable ename varchar2(20); //綁定參數的聲明
SQL> variable sal number;
SQL> begin
emp_select('7777',:ename,:sal); //執行存儲過程
end;
/
PL/SQL procedure successfully completed
ename
---------
redarmy
sal
---------
9000
SQL> print ename; //打印相應的參數
ename
---------
redarmy
SQL> print sal; //打印相應的參數
sal
---------
9000
(3).in out參數
in參數可以接收一個值,但是不能在存儲過程中修改這個值,而對於out參數,它在調用過程時為空,在過程執行中將為為這個參數指定一個值，並在執行後返回.
而in out參數同時具有了in參數和out參數的特性,在過程中可以讀取和寫入該類型的參數。
//作業實現 案例交換兩個數
create or replace procedure test_pro(num1 in out number,num2 in out number) as
num3 number;
begin
dbms_output.put_line('調換前:'||num1||' '||num2);
num3:=num1;
num1:=num2;
num2:=num3;
dbms_output.put_line('調換後:'||num1||' '||num2);
end test_pro;
//執行結果
SQL> set serveroutput on;
SQL> declare
num1 number;
num2 number;
begin
num1:=1;
num2:=2;
test_pro(num1,num2);
end;
/
調換前:1 2
調換後:2 1
PL/SQL procedure successfully completed
