Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö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;



Ïà¹ØÎĵµ£º

jdbcÁ¬½ÓOracle

      ËäȻѧϰJavaºÜ¾ÃÁË£¬×Ô¼ºÒ²Á¬½Ó¹ýһЩÊý¾Ý¿â£¬±ÈÈçmysqlÖ®ÀàµÄ£¬Èç½ñÄØ£¬Ò²Ñ§Ï°ÁËÒ»¶Îʱ¼äµÄOracle£¬È»¶øÄØ£¬½ñÌìÊÇÎÒµÚÒ»´ÎÁ¬½ÓOracle£¬ºÙºÙ£¬Ó¦¸Ã»¹²»ËãÌ«³Ù°É¡£
    ½ñÌìÄØ£¬Óе㱿׾£¬´ó¼ÒĪЦ£¡
    ÎÒÕâÊÇÒ»¸ö²éѯÀý×Ó
    Ê×ÏÈ£¬Ô ......

¡¾×ªÌû¡¿SQL Oracleɾ³ýÖØ¸´¼Ç¼

1.Oracleɾ³ýÖØ¸´¼Ç¼.
ɾ³ý±íÖжàÓàµÄÖØ¸´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ýµ¥¸ö×ֶΣ¨peopleId£©À´Åжϣ¬Ö»ÁôÓÐrowid×îСµÄ¼Ç¼.
delete from people
where peopleId  in (select  peopleId  from people  group  by  peopleId   having  count(peopleId) > 1)
and rowid not i ......

oracle 10gÊý¾Ý¿â±¸·ÝÓ뻹ԭ×ܽá(×ªÔØ£©

1.Êý¾Ý¿â¹éµµÄ£Ê½:
        * ·Ç¹éµµÄ£Ê½:µ±Êý¾Ý¿âÊý¾ÝÖ»¶Á²»»á¸Ä±äʱ,Êý¾Ý²»»á¸Ä±ä,Êý¾Ý¿âÊʺÏÓ÷ǹ鵵ģʽ,                     ÕâÑùÌá¸ßÐÔÄÜ
    &nb ......

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ºÅ