oracle ´æ´¢¹ý³Ì
select myFunc(²ÎÊý1,²ÎÊý2..) to dual; --¿ÉÒÔÖ´ÐÐһЩҵÎñÂß¼
Ò»:OracleÖеĺ¯ÊýÓë´æ´¢¹ý³ÌµÄÇø±ð:
A:º¯Êý±ØÐëÓзµ»ØÖµ,¶ø¹ý³ÌûÓÐ.
B:º¯Êý¿ÉÒÔµ¥¶ÀÖ´ÐÐ.¶ø¹ý³Ì±ØÐëͨ¹ýexecuteÖ´ÐÐ.
C:º¯Êý¿ÉÒÔǶÈëµ½SQLÓï¾äÖÐÖ´ÐÐ.¶ø¹ý³Ì²»ÐÐ.
ÆäʵÎÒÃÇ¿ÉÒÔ½«±È½Ï¸´ÔӵIJéѯд³Éº¯Êý.È»ºóµ½´æ´¢¹ý³ÌÖÐÈ¥µ÷ÓÃÕâЩº¯Êý.
¶þ:ÈçºÎ´´½¨´æ´¢¹ý³Ì:
A:¸ñʽ
create or replace procedure <porcedure_name>
[(²ÎÊýÃû²ÎÊýÀàÐÍÒÔ¼°ÃèÊö,....)] ---×¢Òâ,ûÓзµ»ØÖµ
is
[±äÁ¿ÉùÃ÷]
begin
[¹ý³Ì´¦Àí];----------null;
exception
when Òì³£Ãû then
end;
×¢Òâ:²ÎÊýÖÐĬÈÏÊǰ´Öµ´«µÝ.ÊÇin·½Ê½.Ò²¿ÉÒÔÊÇoutºÍin out·½Ê½.ÕâÐ©ÌØµãºÍº¯ÊýÒ»Ñù.
B:¾ÙÀý1:
create or replace procedure myPro----create or replace proc myPro ³ö´í ²»Äܼòд
(a in int:=0,b in int:=0)
is
c int:=0;
begin
c:=a+b;
dbms_output.put_line('C is value'||c);
end;
Ö´ÐÐ:
execute myPro(10,20); ---ÔÚSql ServerÖÐ.Ö´Ðд洢¹ý³ÌÊDz»ÐèÒªÀ¨»¡µÄ.×¢Ò⠷ֺŲ»Òªµ÷ÁË.
exec myPro(10,20); --¿ÉÒÔ¼òд
C:¾ÙÀý2:
Èç¹ûÔÚÒ»¸öº¯ÊýÀïÃæ°üº¬SelectÓï¾äµÄ»°,ÄÇô¸ÃSelectÓï¾ä±ØÐëÓÐinto,¹ý³ÌͬÑùÒ²ÐèÒª.
create or replace procedure myPro1
(a int:=0,b int:=0)
is
c int:=0;
begin
select empno+a+b into c from emp where ename='FORD';
dbms_output.put_line('C is values '||c);
end;
Ö´ÐÐ:
execute myPro1(10,20)
D:¼ÙÈçÔÚÒ»¸ö¹ý³ÌÀïÃæÒª·µ»ØÒ»¸ö½á¹û¼¯£¬Ôõô°ì?´ó¼Ò×¢Òâ.¾Í±ØÐëÒªÓõ½ÓαêÁË!ÓÃÓαêÀ´´¦ÀíÕâ¸ö½á¹û¼¯.
create or replace procedure Test
(
varEmpName emp.ename%type
)
is begin ------»á±¨´í.´íÎóÔÒòûÓÐinto×Ó¾ä.
select * from emp where ename like '%'||varEmpName||'%';
end;
Õâ¸ö³ÌÐòÎÒÃÇÎÞ·¨ÓÃinto£¬ÒòΪÔÚOracleÀïÃæÃ»ÓÐÒ»¸öÀàÐÍÈ¥½ÓÊÜÒ»¸ö½á¹û¼¯.Õâ¸öʱºòÎÒÃÇ¿ÉÒÔÉùÃ÷Óαê¶ÔÏóÈ¥½ÓÊÜËû.
PL/SQLÓαê:
A:·ÖÀà:
1:ÒþʽÓαê:·ÇÓû§Ã÷È·ÉùÃ÷¶ø²úÉúµÄÓαê. Äã¸ù±¾¿´²»µ½cursorÕâ¸ö¹Ø¼ü×Ö.
2:ÏÔʾÓαê:Óû§Ã÷ȷͨ¹ýcursor¹Ø¼ü×ÖÀ´ÉùÃ÷µÄÓαê.
B:ʲôÊÇÒþʽÓαê:
1:ʲôʱºò²úÉú:
»áÔÚÖ´ÐÐÈκκϷ¨µÄSQLÓï¾ä(DML---INSERT UPDATE DELETE DQL-----SELECT)ÖвúÉú.Ëû²»Ò»¶¨´æ·ÅÊý¾Ý.Ò²ÓпÉÄÜ´æ·Å¼Ç¼¼¯ËùÓ°ÏìµÄÐÐÊý.
Èç¹ûÖ´ÐÐSELECTÓï¾ä,Õâ¸öʱºòÓαê»á´æ·ÅÊý¾Ý.Èç¹ûÖ´ÐÐINSERT UPDATE DELETE»á´æ·Å¼Ç¼ӰÏìµÄÐÐÊý.
C:Ò
Ïà¹ØÎĵµ£º
TO_DATE¸ñʽ(ÒÔʱ¼ä:2007-11-02 13:45:25ΪÀý)
Year:
yy two digits Á½Î»Äê ......
CSDNÀïµÄÒ»¸öÅóÓÑÎʵ½ÁËÕâ¸öË÷Òý¸²¸ÇµÄ¸ÅÄî¡£ Õâ¸ö¸ÅÄîºÜСµÄ֪ʶµã£¬ÔÚÎÒµÄÂÛ̳ÀïÓнâÊÍ“”£¬²»¹ý×÷ΪOracle°æÖ÷£¬²»ÄÜÔÚ»ØÌûÀï¼ÓÉÏÍøÍâµÄµØÖ·Á´½Ó£¬ËùÒÔÕâÀïÔÚCSDNÀïÌûÉÏÒ»·Ý
±ÈÈçÓи´ºÏË÷ÒýΪ3¸ö×ֶΣºf1 + f2 + f3,ÇëÎÊ:
1: select f1, f2, f3, f4 from table where f1 = 'XX' and f2 = 'XX'. ......
1¡¢Ä¬ÈÏÊÂÀý£º
Óû§Ãû£ºscottÃÜÂ룺tiger Ö÷»ú×Ö·û´®£º±¾»ú¿ÉÒÔΪ¿Õ
2¡¢Æô¶¯·½·¨£º
ÔËÐУºsqlplus scott/tiger@lhd
3¡¢SQLPLUS»ù±¾ÃüÁ
Desc£ºÏÔʾ±í¡¢ÊÓͼ½á¹¹ desc ±íÃû£¬ÊÓͼ
List£ºÁгöSQL»º³åÇøÇøÖеÄÒ»Ðлò¶àÐÐÃüÁîÓï¾ä
Exit£ºÍ˳ö
4¡¢³£ÓõÄÊý¾Ý×Öµä(ÈýÖÖǰ׺£ºUSER£¬ALL£¬DBA)
USER_TABLE ......
OracleµÄÔ¼Êø
* Èç¹ûij¸öÔ¼ÊøÖ»×÷ÓÃÓÚµ¥¶ÀµÄ×ֶΣ¬¼´¿ÉÒÔÔÚ×ֶμ¶¶¨ÒåÔ¼Êø£¬Ò²¿ÉÒÔÔÚ±í¼¶¶¨ÒåÔ¼Êø£¬µ«Èç¹ûij¸öÔ¼Êø×÷ÓÃÓÚ¶à¸ö×ֶΣ¬
±ØÐëÔÚ±í¼¶¶¨ÒåÔ¼Êø
* ÔÚ¶¨ÒåÔ¼ÊøÊ±¿ÉÒÔͨ¹ýCONSTRAINT¹Ø¼ü×ÖÎªÔ¼ÊøÃüÃû£¬Èç¹ûûÓÐÖ¸¶¨£¬ORACLE½«×Ô¶¯ÎªÔ¼Êø½¨Á¢Ä¬ÈϵÄÃû³Æ
¶¨Òåprimary keyÔ¼Êø(µ¥¸ö×ֶΣ©
create table ......
ǰ¼¸¸öÏîÄ¿×Ô¼ºÃ»ÓÐд¹ý½Ó¿Ú£¬¶¼ÊǸºÔðǰ̨µÄ¿ØÖÆ¡£À´µ½Õâ¸öÏîÄ¿ÉÏ£¬²Å¿ªÊ¼×Ô¼ºÐ´£¬ÎÒÊÇ6ÔÂÖÐÑ®¿ªÊ¼×öÕâ¸öÏîÄ¿µÄ£¬ÏÖÔÚ11Ô·ݣ¬ÏÖÔÚÔÚÈ¥¿´6Ô·ÝдµÄpackage,¼òÖ±ÓеãÏëÍÂÁË¡£ÔÒòÓÐÒÔϼ¸µã£º
1. ¼ò½à£¬¶óÒªµÄ˵Ã÷ûÓи½¼Ó¡£
2. n¶à¹¦ÄÜ×ۺϵ½Ò»Æð£¬È«²¿·Åµ½Ò»¸ö°üÀ¶ø²»ÊǸù¾Ý¹¦ÄÜÄ£¿é»®·Ö¡£
3. ûÓÐÀýÍâ¿ØÖÆ¡£ ......