Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

Oracle´æ´¢¹ý³Ì£¬º¯Êý¡£

--ÔÚÓ¦ÓóÌÐòÖÐÍùÍùÓÐЩ¹Ì¶¨µÄÊý¾Ý¿â²Ù×÷£¬ÎÒÃdz£³£ÎªÖÂл¹Ì¶¨µÄ²Ù×÷¶¨ÒåÁËһЩ´æ´¢¹ý³Ì¡£ÕâЩ´æ´¢¹ý³Ì²»½ö
--¿ÉÒÔ¼ò»¯¿Í»§¶ËµÄÓ¦ÓóÌÐòµÄ¿ª·¢ºÍά»¤¡£¶øÇÒ»¹¿ÉÒÔÌá¸ßÓ¦ÓõÄÔËÐÐÐÔÄÜ¡£
-- ´´½¨´æ´¢¹ý³ÌµÄÓï·¨¡£
create [or replace] procedure <¹ý³ÌÃû>[(²ÎÊý)]
is|as
[¾Ö²¿±äÁ¿ÉùÃ÷]
begin
¿ÉÖ´ÐÐÓï¾ä
exception
Òì³£´¦Àí
end
--ÔÚ¶¨ÒåÖÐ IS|AS ¿ÉÒÔ¸ù¾Ýϰ¹ßÑ¡ÔñIS »òÕßAS ³ýÁ˶¨Ò岿·Ö¡£ÆäÓàµÄÓ﷨ͬPL/SQLµÄÓï·¨¡£

Àý£º ¡£
-- 1 ¢ÙÒ»¸öÎ޲εĴ洢¹ý³Ì¡£

create or replace procedure proc_1--proc_1´æ´¢¹ý³ÌÃû³Æ
is
i number(3);--¶¨Òå±äÁ¿
begin
i:=100;
dbms_output.put_line(i);
end;
--¢Úµ÷Óô洢¹ý³Ì¡£
begin
proc_1;
end;
--Ò»¸öÎÞ²ÎÊýµÄ´æ´¢¹ý³Ì´´½¨Íê±Ï
2 Ò»¸öÓвÎÊýµÄ´æ´¢¹ý³Ì
´æ´¢¹ý³Ì¶¨ÒåµÄʱºò¿ÉÒÔʹÓòÎÊýÒ²¿ÉÒÔ²»ÊµÓòÎÊý¡£

²ÎÊýÀàÐÍ
· ÔÚPL/SQL¹ý³ÌÖУ¬¿ÉÒÔÓÐ3ÖÖÀàÐ͵IJÎÊý¡£
· IN²ÎÊý£º¶ÁÈë²ÎÊý£¬Ö÷³ÌÐòÏò¹ý³ÌÖд«µÝ²ÎÊý¡£
· OUT²ÎÊý£ºÊä³ö²ÎÊý£¬¹ý³ÌÏòÖ÷³ÌÐò´«µÝ²ÎÊý¡£
· IN OUT²ÎÊý£ºÕâ¸ö²ÎÊý£¬¹ý³ÌÓëÖ÷³ÌÐòË«Ïò½»Á÷Êý¾Ý¡£
Èç¹û´æ´¢¹ý³ÌÓÐOUT »òÕßIN OUT ÀàÐ͵IJÎÊý£¬ÄÇô´æ´¢¹ý³Ì¾ÍÖ»ÄÜÔÚPL/SQL
Öб»µ÷Óᣲ»ÄÜʹÓÃEXECUTE »òÕßCALLµ÷Óá£

Ò»¸öÊäÈë²ÎÊýµÄÀý×Ó

create or replace procedure proc_2(myno in number)
is emprow emp%rowtype;--¶¨ÒåÒ»¸öÐÐÀàÐ͵ıäÁ¿
begin
select * into emprow from emp where empno=myno;
dbms_output.put_line('Ô±¹¤ÐÕÃû£º'||emprow.ename||'Ô±¹¤Ð½Ë®'||emprow.sal);
end;
--µ÷ÓÃÕâ¸ö´æ´¢¹ý³Ì
begin
proc_2(7369);
end;

¼ÈÓÐÊäÈë²ÎÊý ÓÖÓÐÊä³ö²ÎÊý

create or replace procedure proc_3(myno in number,mysal out emp.sal%type, myname out emp.ename%type)
is
emprow emp%rowtype;
begin
select sal,ename into mysal,myname from emp where empno=myno;
end;
--µ÷ÓÃÕâ¸ö´æ´¢¹ý³Ì
declare
tempsal emp.sal%type;
empname emp.ename%type;
begin
proc_3(7369,tempsal,empname);
dbms_output.put_line('Ô±¹¤ÐÕÃû'||empname||'Ô±¹¤Ð½Ë®'||tempsal);
end;



Ïà¹ØÎĵµ£º

oracle LogMiner¼ò½é

Ô­ÎĵØÖ·£ºhttp://www.phontol.com/20090719_525808_1.html
1  LogMiner µÄÓÃ;
¡¡¡¡Oracle LogMiner ÊÇ Oracle¹«Ë¾´Ó²úÆ· 8iÒÔºóÌṩµÄÒ»¸öʵ¼Ê·Ç³£ÓÐÓõķÖÎö¹¤¾ß£¬Ê¹Óøù¤¾ß¿ÉÒÔÇáËÉ»ñµÃ OracleÖØ×÷ÈÕÖ¾Îļþ£¨¹éµµÈÕÖ¾Îļþ£©ÖеľßÌåÄÚÈÝ£¬ÌرðÊÇ£¬¸Ã ¹¤¾ß¿ÉÒÔ·ÖÎö³öËùÓжÔÓÚÊý¾Ý¿â²Ù×÷µÄ DML£¨insert¡¢update¡¢ ......

OracleÖÐrownumµÄʹÓÃ

ÔÚʹÓÃOracleÖÐʹÓÃRownum£¬»áÓÐЩÎó½â£¬ÏÖÕûÀíÏÂÍøÓÑÃǵÄÎÄÕ£¬Ï£ÍûÄÜÓÐËù°ïÖú£¬ºÇºÇ
¶ÔÓÚ Oracle µÄ rownum ÎÊÌ⣬ºÜ¶à×ÊÁ϶¼Ëµ²»Ö§³Ö>,>=,=,between...and£¬Ö»ÄÜÓÃÒÔÉÏ·ûºÅ(<¡¢<=¡¢!=)£¬²¢·Ç˵ÓÃ>,>=,=,between..and ʱ»áÌáʾSQLÓï·¨´íÎ󣬶øÊǾ­³£ÊDz鲻³öÒ»Ìõ¼Ç¼À´£¬»¹»á³öÏÖËÆºõÊÇĪÃûÆäÃîµÄ½á¹û ......

ORACLE ÖÐROWNUMÓ÷¨×ܽá


    ¶ÔÓÚ Oracle µÄ rownum ÎÊÌ⣬ºÜ¶à×ÊÁ϶¼Ëµ²»Ö§³Ö>,>=,=,between...and£¬Ö»ÄÜÓÃÒÔÉÏ·ûºÅ(<¡¢<=¡¢!=)£¬²¢·Ç˵ÓÃ>,>=,=,between..and ʱ»áÌáʾSQLÓï·¨´íÎ󣬶øÊǾ­³£ÊDz鲻³öÒ»Ìõ¼Ç¼À´£¬»¹»á³öÏÖËÆºõÊÇĪÃûÆäÃîµÄ½á¹ûÀ´£¬ÆäʵÄúÖ»ÒªÀí½âºÃÁËÕâ¸ö rownum αÁеÄÒâÒå¾Í²»Ó¦¸Ã¸Ðµ½¾ªÆæ£ ......

OracleÓαêʵÓÃ

  declare
sqlstring varchar2(100);
emprow emp_bak%rowtype;
myno emp_bak.empno%type;
begin

--sqlstring:='create table emp_bak as select * from emp';

sqlstring:='select * from emp_bak where empno=:myno and sal>:mysal';

myno:='&ÇëÊäÈëÔ±¹¤±àºÅ';

--¶¯Ì¬SQ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ