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 ;