Oracle 瀛樺偍鍑芥暟鍜屽瓨鍌ㄨ繃绋?PL/SQL瀛愮▼搴

鏂囩珷鏉ユ簮锛?a href="http://www.itnose.net/detail/6033960.html" style="color: #2d64b3;">http://www.itnose.net/detail/6033960.html
鏇村鏂囩珷锛?a href="http://www.itnose.net/type/97.html" style="color: #2d64b3;">http://www.itnose.net/type/97.html

PL/SQL瀛愮▼搴?鍖呮嫭鍑芥暟鍜岃繃绋嬨€傝繖閲岀殑鍑芥暟鎸囩殑鏄敤鎴疯嚜宸卞畾涔夌殑鍑芥暟锛屽拰绯荤粺鍑芥暟鏄笉鍚岀殑銆傚瓙绋嬪簭涓€鑸槸瀹屾垚鐗瑰畾鍔熻兘鐨凱L/SQL绋嬪簭鍧楋紝骞朵笖鍏锋湁涓€瀹氱殑閫氱敤鎬э紝鍙互琚笉鍚岀殑搴旂敤绋嬪簭澶氭璋冪敤銆侽racle鎻愪緵鍙互鎶奝L/SQL绋嬪簭瀛樺偍鍦ㄦ暟鎹簱涓紝骞跺彲浠ュ啀浠讳綍鍦版柟鏉ヨ繍琛屽畠銆傝繖鏍峰氨鍙仛瀛樺偍杩囩▼鎴栬€呮槸鍑芥暟銆傝繃绋嬪拰鍑芥暟鐨勫敮涓€鍖哄埆灏辨槸鍑芥暟鎬绘槸鍚戣皟鐢ㄨ€呰繑鍥炴暟鎹紝鑰岃繃绋嬪垯涓嶈繑鍥炴暟鎹€?/p>

鍑芥暟

濡傛灉鐢ㄦ埛瑕佺粡甯告墽琛屾煇浜涙搷浣滐紝骞朵笖闇€瑕佽繑鍥炵壒瀹氱殑鏁版嵁锛岄偅涔堝氨鍙互灏嗚繖浜涙搷浣滄瀯閫犳垚涓€涓嚱鏁般€?/p>

鍙互浣跨敤SQL璇彞瀹氫箟鍑芥暟銆?/p>

鍩烘湰璇硶锛?/p>

create or replace function fun_name(argument [in | out | in out ]datatype 聽...)
return datatype聽
is | as
聽 聽 聽 聽-- 闇€瑕佸畾涔夌殑鍙橀噺 锛岃褰曠被鍨?锛?娓告爣
begin
聽 聽 聽 聽--鍑芥暟鐨勬墽琛屼綋
exception
聽 聽 聽 聽--澶勭悊鍑虹幇鐨勫紓甯?br>end ;

鍏朵腑锛宺eturn datatype 锛?鏄杩斿洖鏁版嵁鐨勭被鍨嬨€侷N鍙傛暟鏍囪琛ㄧず浼犻€掔粰鍑芥暟鐨勫€煎湪鍑芥暟鎵ц涓笉鏀瑰彉锛汷UT鏍囪琛ㄧず涓€涓€煎湪鍑芥暟杩涜璁＄畻骞堕€氳繃璇ュ弬鏁颁紶閫掔粰璋冪敤璇彞锛汭N OUT 鏍囪琛ㄧず浼犻€掔粰鍑芥暟鐨勫€煎彲浠ュ彉鍖栧苟浼犻€掔粰璋冪敤璇彞銆?/p>

渚嬶細瀹氫箟涓€涓繑鍥瀐ello 鐨勫嚱鏁?/p>

create or replace function hello
return varchar2聽
is
begin
聽 聽 聽 聽return 'Hello World';
end ;

鎵ц 锛孎unction created 锛屽嚱鏁板垱寤烘垚鍔熴€?/p>

鍑芥暟鐨勮皟鐢紝涓嶰racle鍐呯疆鍑芥暟鐨勮皟鐢ㄧ浉鍚岋紝鍙互浣跨敤select hello from dual ;杩涜璋冪敤锛屼篃鍙互浣跨敤PL/SQL璇彞杩涜璋冪敤 锛?/p>

begin聽

聽 dbms_output.put_line(hello);

end ;

渚?鍒涘缓甯﹀弬鏁扮殑helloworld鍑芥暟

create or replace function helloworld(str varchar2)
return varchar2
is
begin
聽 聽 聽 聽return 'Hello' || '_' ||str ;
end ;

鍑芥暟鐨勮皟鐢ㄨ鍦ㄥ嚱鏁板悕绉板悗闈㈠姞涓婂弬鏁?鍗筹細select helloworld('World') from dual ;浣跨敤Pl/sql鐨勮皟鐢ㄩ櫎浜嗗姞涓婂弬鏁板涓庝笂闈㈢浉鍚岋紝涓嶅湪璧樿堪銆?/p>

渚嬶細姹備竴涓儴闂ㄤ腑锛屽憳宸ョ殑宸ヨ祫鎬绘暟鐨勫嚱鏁?/p>

create or replace function get_sal(dept number)
return number聽
is
聽 聽 聽 聽v_sum number(10) := 0 ;
聽 聽 聽 聽cursor sal_cursor is select sal from emp where deptno = dept ;
begin聽
聽 聽 聽 聽for c in sal_cursor loop
聽 聽 聽 聽 聽 聽v_sum := v_sum + c.sal ;
聽 聽 聽 聽end loop ;
聽 聽 聽 聽return v_sum ;
end ;

瀛樺偍杩囩▼

瀛樺偍杩囩▼锛屽彲浠ヨ澶氫釜搴旂敤绋嬪簭璋冪敤锛屼篃鍙互鍚戝瓨鍌ㄨ繃绋嬩紶閫掑弬鏁帮紝鍚戝瓨鍌ㄨ繃绋嬩紶鍥炲弬鏁般€?/p>

鍩烘湰璇硶

create or replace procedure pro_name(argument [in | out | in out ]datatype 聽...)聽
is | as
聽 聽 聽 聽-- 闇€瑕佸畾涔夌殑鍙橀噺 锛岃褰曠被鍨?锛?娓告爣
begin
聽 聽 聽 聽--鍑芥暟鐨勬墽琛屼綋
exception
聽 聽 聽 聽--澶勭悊鍑虹幇鐨勫紓甯?br>end ;

渚嬶細浣跨敤瀛樺偍杩囩▼锛屾眰閮ㄩ棬鐨勫伐璧勬€诲拰

create or replace procedure get_sal1(dept number ,sumsal out 聽number)
is
聽 聽 聽 聽cursor sal_cursor is select sal from emp where deptno = dept ;
begin聽
聽 聽 聽 聽sumsal := 0 ;
聽 聽 聽 聽for c in sal_cursor loop
聽 聽 聽 聽 聽 聽sumsal := sumsal + c.sal ;
聽 聽 聽 聽end loop ;
聽 聽 聽 聽dbms_output.put_line(sumsal);
end ;

瀛樺偍杩囩▼鐨勮皟鐢細

declare聽
聽 v_sum number(10) := 0 ;
begin聽
聽 get_sal1(30 , v_sum);
end ;

璋冪敤鏍煎紡锛?/p>

聽

CALL | EXCEUTE procedure_name(arg_list) ;

鍙互浣跨敤show error 鍛戒护鏉ユ彁绀烘簮鐮佺殑閿欒浣嶇疆銆備娇鐢╱ser_error 鏁版嵁瀛楀吀鏉ユ煡鐪嬪悇瀛樺偍杩囩▼鐨勯敊璇綅缃€?

聽

鍒犻櫎杩囩▼鍜屽嚱鏁?/strong>

鍒犻櫎杩囩▼

璇硶濡備笅锛?/p>

DROP PROCEDURE[USER.]procedure_name ;

鍒犻櫎鍑芥暟

璇硶濡備笅锛?/p>

DROP FUNCTION [USER.]function_name ;