å¨oracleä¸ï¼å½æ°ååå¨è¿ç¨æ¯ç»å¸¸ä½¿ç¨å°çï¼ä»ä»¬çè¯æ³ä¸æå¾å¤ç¸ä¼¼çå°æ¹ï¼ä½ä¹æèªå·±çç¹ç¹ãåå¦å®å½æ°ååå¨è¿ç¨ï¼ä¸é¢æ¥å大家å享ä¸ä¸èªå·±æ»ç»çå
³äºå½æ°ååå¨è¿ç¨çåºå«ã
ä¸ãåå¨è¿ç¨
1.å®ä¹
ããåå¨è¿ç¨æ¯åå¨å¨æ°æ®åºä¸æä¾ææç¨æ·ç¨åºè°ç¨çåç¨åºï¼å®ä¹åå¨è¿ç¨çå
³é®å为procedureã
2.å建åå¨è¿ç¨
ããcreate [or replace] procedure åå¨è¿ç¨å
ãã[(åæ°1 ç±»åï¼åæ°2 out ç±»åâ¦â¦)]ããã
ããas
ããããåéåããç±»åï¼
ããbegin
ããããç¨åºä»£ç ä½
ããend;
ãã示ä¾ä¸ï¼æ åæ è¿
create or replace procedure p1
--or replace代表å建该åå¨è¿ç¨æ¶ï¼è¥åå¨ååå¨ï¼åæ¿æ¢ååå¨è¿ç¨ï¼éæ°å建
--æ åæ°å表æ¶ï¼ä¸éè¦å()
as
begin
dbms_output.put_line('hello world');
end;
--æ§è¡åå¨è¿ç¨æ¹å¼1
set serveroutput on;
begin
p1();
end;
--æ§è¡åå¨è¿ç¨æ¹å¼2
set serveroutput on;
execute p1();
ãã示ä¾äºï¼æåæè¿
create or replace procedure p2
(name in varchar2,age int,msg out varchar2)
--åæ°å表ä¸ï¼å£°æåéç±»åæ¶åè®°ä¸è½å大å°ï¼åªåç±»ååå³å¯ï¼ä¾å¦åæ°å表ä¸çnameåéç声æ
--åæ°å表ä¸ï¼è¾å
¥åæ°ç¨in表示ï¼è¾åºåæ°ç¨out表示ï¼ä¸åæ¶é»è®¤ä¸ºè¾å
¥åæ°ã
------------è¾å
¥åæ°ä¸è½æºå¸¦å¼åºå»ï¼è¾åºåæ°ä¸è½æºå¸¦å¼è¿æ¥ï¼å½æ¢æ³æºå¸¦å¼è¿æ¥ï¼åæ³æºå¸¦å¼åºå»ï¼å¯ä»¥ç¨in out
as
begin
msg:='å§å'||name||',å¹´é¾'||age;
--èµå¼æ¶é¤äºå¯ä»¥ä½¿ç¨ï¼=ï¼è¿å¯ä»¥ç¨intoæ¥å®ç°
--ä¸é¢åå¥çä»·äºselect 'å§å'||name||',å¹´é¾'||age into msg from dual;
end;
--æ§è¡åå¨è¿ç¨
set serveroutput on;
declare
msg varchar2(100);
begin
p2('å¼ ä¸',23,msg);
dbms_output.put_line(msg);
end;
ãã示ä¾ä¸ï¼åæ°å表ä¸æin outåæ°
create or replace procedure p3
(msg in out varchar2)
--å½æ¢æ³æºå¸¦å¼è¿æ¥ï¼åæ³æºå¸¦å¼åºå»ï¼å¯ä»¥ç¨in out
as
begin
dbms_output.put_line(msg); --è¾åºç为æºå¸¦è¿æ¥çå¼
msg:='ææ¯ä»åå¨è¿ç¨ä¸æºå¸¦åºæ¥çå¼';
end;
--æ§è¡åå¨è¿ç¨
set serveroutput on;
declare
msg varchar2(100):='ææ¯ä»æºå¸¦è¿å»çå¼';
begin
p3(msg);
dbms_output.put_line(msg);
end;
ãã示ä¾åï¼åå¨è¿ç¨ä¸å®ä¹åæ°
create or replace procedure p4
as
--åå¨è¿ç¨ä¸å®ä¹çåæ°å表
name varchar(50);
begin
name := 'hello world';
dbms_output.put_line(name);
end;
---æ§è¡åå¨è¿ç¨
set serveroutput on;
execute p4();
æ»ç»ï¼1.å建åå¨è¿ç¨çå
³é®å为procedureã
ãã 2.ä¼ åå表ä¸çåæ°å¯ä»¥ç¨inï¼outï¼in out修饰ï¼åæ°ç±»åä¸å®ä¸è½å大å°ãå表ä¸å¯ä»¥æå¤ä¸ªè¾å
¥è¾åºåæ°ã
ãã 3.åå¨è¿ç¨ä¸å®ä¹çåæ°å表ä¸éè¦ç¨declare声æï¼å£°æåæ°ç±»åæ¶éè¦å大å°çä¸å®è¦å¸¦ä¸å¤§å°ã
ããã 4.aså¯ä»¥ç¨isæ¿æ¢ã
ãã 5.è°ç¨å¸¦è¾åºåæ°çè¿ç¨å¿
é¡»è¦å£°æåéæ¥æ¥æ¶è¾åºåæ°å¼ã
ãã 6.æ§è¡åå¨è¿ç¨æ两ç§æ¹å¼ï¼ä¸ç§æ¯ä½¿ç¨executeï¼å¦ä¸ç§æ¯ç¨beginåendå
ä½ã
ãã åå¨è¿ç¨è½ç¶æå¾å¤ä¼ç¹ï¼ä½æ¯å®å´ä¸è½ä½¿ç¨returnè¿åå¼ãå½éè¦ä½¿ç¨returnè¿åå¼æ¶ï¼æ们å¯ä»¥ä½¿ç¨å½æ°ã
äºãåå¨å½æ°
1.å½æ°ä¸åå¨è¿ç¨çç»æ类似ï¼ä½æ¯å½æ°å¿
é¡»æä¸ä¸ªreturnåå¥ï¼ç¨äºè¿åå½æ°å¼ã
create or replace function f1
return varchar--å¿
é¡»æè¿åå¼ï¼ä¸å£°æè¿åå¼ç±»åæ¶ä¸éè¦å 大å°
as
msg varchar(50);
begin
msg := 'hello world';
return msg;
end;
--æ§è¡å½æ°æ¹å¼1
select f1() from dual;
--æ§è¡å½æ°æ¹å¼2
set serveroutput on;
begin
dbms_output.put_line(f1());
end;
ä¸ãåå¨è¿ç¨ä¸åå¨å½æ°çåºå«åèç³»
ããç¸åç¹ï¼1.å建è¯æ³ç»æç¸ä¼¼ï¼é½å¯ä»¥æºå¸¦å¤ä¸ªä¼ å
¥åæ°åä¼ åºåæ°ã
ãããã 2.é½æ¯ä¸æ¬¡ç¼è¯ï¼å¤æ¬¡æ§è¡ã
ããä¸åç¹ï¼1.åå¨è¿ç¨å®ä¹å
³é®åç¨procedureï¼å½æ°å®ä¹ç¨functionã
ãããããã2.åå¨è¿ç¨ä¸ä¸è½ç¨returnè¿åå¼ï¼ä½å½æ°ä¸å¯ä»¥ï¼èä¸å½æ°ä¸å¿
é¡»æreturnåå¥ã
ãããããã3.æ§è¡æ¹å¼ç¥æä¸åï¼åå¨è¿ç¨çæ§è¡æ¹å¼æ两ç§ï¼1.使ç¨execute2.使ç¨beginåendï¼ï¼å½æ°é¤äºåå¨è¿ç¨ç两ç§æ¹å¼å¤ï¼è¿å¯ä»¥å½å表达å¼ä½¿ç¨ï¼ä¾å¦æ¾å¨selectä¸ï¼select f1() form dual;ï¼ã
æ»ç»ï¼å¦æåªæä¸ä¸ªè¿åå¼ï¼ç¨åå¨å½æ°ï¼å¦åï¼ä¸è¬ç¨åå¨è¿ç¨ã
温馨提示:答案为网友推荐,仅供参考