如题所述
åå¨è¿ç¨åå½æ°ç®çæ¯ä¸ºäº å¯éå¤å° æ§è¡æä½æ°æ®åºçsqlè¯å¥çéåã
åºå«æ¯åæ³åè°ç¨ä¸ã
åæ³ä¸ï¼åå¨è¿ç¨çåæ°å表å¯ä»¥æè¾å ¥åæ°ãè¾åºåæ°ãå¯è¾å ¥è¾åºçåæ°ï¼
å½æ°çåæ°å表åªæè¾å ¥åæ°ï¼å¹¶ä¸æreturn <è¿åå¼ç±»åï¼æ é¿åº¦è¯´æ>ã
è¿åå¼ä¸ï¼
åå¨è¿ç¨çè¿åå¼ï¼å¯ä»¥æå¤ä¸ªå¼ï¼
å½æ°çè¿åå¼ï¼åªæä¸ä¸ªå¼ã
è°ç¨æ¹å¼ä¸ï¼
åå¨è¿ç¨çè°ç¨æ¹å¼æï¼
1)ãexec <è¿ç¨å>;
2)ãexecute <è¿ç¨å>;
3)ãå¨PL/SQLè¯å¥åä¸ç´æ¥è°ç¨ã
å½æ°çè°ç¨æ¹å¼æï¼
å¨PL/SQLè¯å¥åä¸ç´æ¥è°ç¨ã
å ·ä½å为ï¼
----è°ç¨FUNCTION add_three_numbers
----1. ä½ç½®è¡¨ç¤ºæ³è°ç¨å½æ°
BEGIN
dbms_output.put_line(add_three_numbers(2,4,5));
END;
----2. å½å表示æ³è°ç¨å½æ°
BEGIN
dbms_output.put_line(add_three_numbers(b=>3, a=>4,c=>2));
END;
----3. æ··å使ç¨ä½ç½®è¡¨ç¤ºæ³åå½å表示æ³è°ç¨å½æ°
BEGIN
dbms_output.put_line(add_three_numbers(3, b=>4,c=>2));
END;
----4. æé¤è¡¨ç¤ºæ³
BEGIN
dbms_output.put_line(add_three_numbers(12,c=>2));
END;
----5. sqlè°ç¨è¡¨ç¤ºæ³ --æ··å表示æ³
SELECT add_three_numbers(3, b=>4,c=>2) FROM DUAL;
----1. 该å½æ°æ¥å3个å¯éåæ°,è¿å3个æ°åçå
CREATE OR REPLACE FUNCTION add_three_numbers
(
a NUMBER:=0, b NUMBER:=0, c NUMBER:=0
)
RETURN NUMBER IS
BEGIN
RETURN a+b+c;
END;
åå¨è¿ç¨ï¼
åºæ¬è¯æ³ï¼
create procedure <è¿ç¨å>(<åæ°å表,æ åæ¶å¿½ç¥>)
as|is
åé声æãåå§å
begin
ä¸å¡å¤çãé»è¾ä»£ç
exception
å¼å¸¸æè·ã容éå¤ç
end <è¿ç¨å>;
åæ°:<åæ°å> in|out|in out <åæ°ç±»åï¼æ é¿åº¦è¯´æ> ï¼å¦ï¼v_name varchar2
inï¼å ¥å
outï¼åºå
in outï¼åºå ¥å
注ï¼as|is表示asæis
è°ç¨è¯æ³ï¼
1)ãexec <è¿ç¨å>;
2)ãexecute <è¿ç¨å>;
3)ãå¨PL/SQLè¯å¥åä¸ç´æ¥è°ç¨ã
ä¾ï¼
create or replace procedure up_wap(v_param1 in out varchar2,v_param2 in out varchar2)
is
v_temp varchar2(20);
begin
dbms_output.put_line('交æ¢ååæ°1ï¼'||v_param1||' åæ°2ï¼'||v_param2);
v_temp:=v_param1;
v_param1:=v_param2;
v_param2:=v_temp;
dbms_output.put_line('交æ¢ååæ°1ï¼'||v_param1||' åæ°2ï¼'||v_param2);
exception
when others then dbms_output.put_line('There is a error when the procedure up_wap executing!');
end up_wap;
/
-- è°ç¨åå¨è¿ç¨
declare
v_param1 varchar2(20):='param1';
v_param2 varchar2(20):='param2';
begin
up_wap(v_param1 => v_param1,v_param2 => v_param2);
end;
/
èªå®ä¹å½æ°(function)
åºæ¬è¯æ³ï¼
create function <å½æ°å>(<åæ°å表,æ åæ¶å¿½ç¥>)
return <è¿åå¼ç±»åï¼æ é¿åº¦è¯´æ>
as|is
åé声æãåå§å
begin
ä¸å¡å¤çãé»è¾ä»£ç
return <è¿åçå¼>;
exception
å¼å¸¸æè·ã容éå¤ç
end <å½æ°å>;
åæ°ï¼in å ¥å
注ï¼åªæå ¥åçç±»åã
å¨åå¨è¿ç¨åèªå®ä¹å½æ°ä¸çåæ°çä¼ éï¼å ¥åååºåï¼ä¸è½ä½¿ç¨%typeæ%rowtypeå¹é ï¼ä¸è½ä½¿ç¨ç©ºå¼nullï¼ä½æ¯åå¨è¿ç¨å¯ä»¥è¿å空å¼ã
ä¾ï¼
create function uf_select_name_by_id_test(v_id in number)
return varchar2
is
v_name t_test.t_name%type;
begin
select t_name into v_name from t_test where t_id=v_id;
return v_name;
exception
when others then dbms_output.put_line('error');
end uf_select_name_by_id_test;
/
select uf_select_name_by_id_test(1) å§å from dual;-- selectè°ç¨
declare --pl/sqlè¯å¥åè°ç¨
v_name varchar2(20);
begin
v_name:=uf_select_name_by_id_test(1);
dbms_output.put_line('name = '||v_name);
end;
/
åºå«æ¯åæ³åè°ç¨ä¸ã
åæ³ä¸ï¼åå¨è¿ç¨çåæ°å表å¯ä»¥æè¾å ¥åæ°ãè¾åºåæ°ãå¯è¾å ¥è¾åºçåæ°ï¼
å½æ°çåæ°å表åªæè¾å ¥åæ°ï¼å¹¶ä¸æreturn <è¿åå¼ç±»åï¼æ é¿åº¦è¯´æ>ã
è¿åå¼ä¸ï¼
åå¨è¿ç¨çè¿åå¼ï¼å¯ä»¥æå¤ä¸ªå¼ï¼
å½æ°çè¿åå¼ï¼åªæä¸ä¸ªå¼ã
è°ç¨æ¹å¼ä¸ï¼
åå¨è¿ç¨çè°ç¨æ¹å¼æï¼
1)ãexec <è¿ç¨å>;
2)ãexecute <è¿ç¨å>;
3)ãå¨PL/SQLè¯å¥åä¸ç´æ¥è°ç¨ã
å½æ°çè°ç¨æ¹å¼æï¼
å¨PL/SQLè¯å¥åä¸ç´æ¥è°ç¨ã
å ·ä½å为ï¼
----è°ç¨FUNCTION add_three_numbers
----1. ä½ç½®è¡¨ç¤ºæ³è°ç¨å½æ°
BEGIN
dbms_output.put_line(add_three_numbers(2,4,5));
END;
----2. å½å表示æ³è°ç¨å½æ°
BEGIN
dbms_output.put_line(add_three_numbers(b=>3, a=>4,c=>2));
END;
----3. æ··å使ç¨ä½ç½®è¡¨ç¤ºæ³åå½å表示æ³è°ç¨å½æ°
BEGIN
dbms_output.put_line(add_three_numbers(3, b=>4,c=>2));
END;
----4. æé¤è¡¨ç¤ºæ³
BEGIN
dbms_output.put_line(add_three_numbers(12,c=>2));
END;
----5. sqlè°ç¨è¡¨ç¤ºæ³ --æ··å表示æ³
SELECT add_three_numbers(3, b=>4,c=>2) FROM DUAL;
----1. 该å½æ°æ¥å3个å¯éåæ°,è¿å3个æ°åçå
CREATE OR REPLACE FUNCTION add_three_numbers
(
a NUMBER:=0, b NUMBER:=0, c NUMBER:=0
)
RETURN NUMBER IS
BEGIN
RETURN a+b+c;
END;
åå¨è¿ç¨ï¼
åºæ¬è¯æ³ï¼
create procedure <è¿ç¨å>(<åæ°å表,æ åæ¶å¿½ç¥>)
as|is
åé声æãåå§å
begin
ä¸å¡å¤çãé»è¾ä»£ç
exception
å¼å¸¸æè·ã容éå¤ç
end <è¿ç¨å>;
åæ°:<åæ°å> in|out|in out <åæ°ç±»åï¼æ é¿åº¦è¯´æ> ï¼å¦ï¼v_name varchar2
inï¼å ¥å
outï¼åºå
in outï¼åºå ¥å
注ï¼as|is表示asæis
è°ç¨è¯æ³ï¼
1)ãexec <è¿ç¨å>;
2)ãexecute <è¿ç¨å>;
3)ãå¨PL/SQLè¯å¥åä¸ç´æ¥è°ç¨ã
ä¾ï¼
create or replace procedure up_wap(v_param1 in out varchar2,v_param2 in out varchar2)
is
v_temp varchar2(20);
begin
dbms_output.put_line('交æ¢ååæ°1ï¼'||v_param1||' åæ°2ï¼'||v_param2);
v_temp:=v_param1;
v_param1:=v_param2;
v_param2:=v_temp;
dbms_output.put_line('交æ¢ååæ°1ï¼'||v_param1||' åæ°2ï¼'||v_param2);
exception
when others then dbms_output.put_line('There is a error when the procedure up_wap executing!');
end up_wap;
/
-- è°ç¨åå¨è¿ç¨
declare
v_param1 varchar2(20):='param1';
v_param2 varchar2(20):='param2';
begin
up_wap(v_param1 => v_param1,v_param2 => v_param2);
end;
/
èªå®ä¹å½æ°(function)
åºæ¬è¯æ³ï¼
create function <å½æ°å>(<åæ°å表,æ åæ¶å¿½ç¥>)
return <è¿åå¼ç±»åï¼æ é¿åº¦è¯´æ>
as|is
åé声æãåå§å
begin
ä¸å¡å¤çãé»è¾ä»£ç
return <è¿åçå¼>;
exception
å¼å¸¸æè·ã容éå¤ç
end <å½æ°å>;
åæ°ï¼in å ¥å
注ï¼åªæå ¥åçç±»åã
å¨åå¨è¿ç¨åèªå®ä¹å½æ°ä¸çåæ°çä¼ éï¼å ¥åååºåï¼ä¸è½ä½¿ç¨%typeæ%rowtypeå¹é ï¼ä¸è½ä½¿ç¨ç©ºå¼nullï¼ä½æ¯åå¨è¿ç¨å¯ä»¥è¿å空å¼ã
ä¾ï¼
create function uf_select_name_by_id_test(v_id in number)
return varchar2
is
v_name t_test.t_name%type;
begin
select t_name into v_name from t_test where t_id=v_id;
return v_name;
exception
when others then dbms_output.put_line('error');
end uf_select_name_by_id_test;
/
select uf_select_name_by_id_test(1) å§å from dual;-- selectè°ç¨
declare --pl/sqlè¯å¥åè°ç¨
v_name varchar2(20);
begin
v_name:=uf_select_name_by_id_test(1);
dbms_output.put_line('name = '||v_name);
end;
/
温馨提示:答案为网友推荐,仅供参考
第1个回答 2020-04-27
MySQL教程4 MySQL8运算符、函数、存储过程及新增数据类型 19.MySQL之存储过程与自定义函数的区别 学习猿地