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;
Ïà¹ØÎĵµ£º
ËäȻѧϰJavaºÜ¾ÃÁË£¬×Ô¼ºÒ²Á¬½Ó¹ýһЩÊý¾Ý¿â£¬±ÈÈçmysqlÖ®ÀàµÄ£¬Èç½ñÄØ£¬Ò²Ñ§Ï°ÁËÒ»¶Îʱ¼äµÄOracle£¬È»¶øÄØ£¬½ñÌìÊÇÎÒµÚÒ»´ÎÁ¬½Ó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 ......
1.Êý¾Ý¿â¹éµµÄ£Ê½:
* ·Ç¹éµµÄ£Ê½:µ±Êý¾Ý¿âÊý¾ÝÖ»¶Á²»»á¸Ä±äʱ,Êý¾Ý²»»á¸Ä±ä,Êý¾Ý¿âÊʺÏÓ÷ǹ鵵ģʽ, ÕâÑùÌá¸ßÐÔÄÜ
&nb ......
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 ......