Oracle ²Ù×÷º¯Êý
º¯Êý£º
1.ʹÓÃCreate Function Óï¾ä´´½¨
2.Óï
·¨£º
Create or replace Function º¯ÊýÃû[²ÎÊýÁбí]
Return Êý¾ÝÀàÐÍ
IS|AS
¾Ö²¿±äÁ¿
Begin
¿ÉÖ´ÐÐÓï¾ä
end£»
3.·ÃÎʺ¯Êý
-ÓÃÁ½ÖÖ·½Ê½½øÐзÃÎÊ
ʹÓÃPL/SQL¿é
ʹÓÃSQLÓï¾ä
-½ö½ÓÊÜIn²ÎÊý
ʾÀý£º
create or replace function
GetName(sNo varchar2)
return varchar Is --²»ÄÜÖ¸¶¨¾«¶È
name
varchar(12);
Begin
select ename into name from emp
where empno=sNo;
return name;
Exception
when
Too_many_rows then
DBMS_output.put_line('·µ»ØÖµ¶àÓàÒ»Ìõ');
when
Others then
DBMS_output.put_line('ÔÚÖ´Ðк¯ÊýGetNameʱ³öÏÖÒâÍâ´íÎó!');
End;
/
µ÷ÓÃ
declare
name varchar(12);
begin
name:=getname('7369');
DBMS_output.put_line('½á¹ûΪ:'||name);
end;
/
Êý¾Ý×ֵ䣺user_source
±àÒëʱ³ö´íµÄÊý¾Ý×Ö
µä£ºuser_errors;
»ò show errors£»
select * from user_source from where
name='GETNAME'; --×¢Òâ´óд
¹ý³ÌÓ뺯Êý
¹ý
³Ì º¯Êý
×÷Ϊ
PL/SQLÓï¾äÖ´ÐÐ &nb
Ïà¹ØÎĵµ£º
µ¼³öºÍµ¼ÈëʵÓóÌÐò
q µ¼³öºÍµ¼ÈëʵÓóÌÐòÓÃÓÚʵʩÊý¾Ý¿âµÄÂß¼±¸·ÝºÍ»Ö¸´
q µ¼³öʵÓóÌÐò½«Êý¾Ý¿âÖеĶÔÏó¶¨ÒåºÍÊý¾Ý±¸·Ýµ½Ò»¸ö²Ù×÷ϵͳ¶þ½øÖÆÎļþÖÐ
q µ¼ÈëʵÓóÌÐò¶ÁÈ¡¶þ½øÖƵ¼³öÎļþ²¢½«¶ÔÏóºÍÊý¾ÝÔØÈëÊý¾Ý¿âÖÐ ......
¸ãoracle¶¼»á¾³£Åöµ½rowid£¬±¾ÎÄÊDZÊÕ߸ù¾ÝÍøÉϸ÷λµÄÎÄÕ£¬¼ÓÉÏ×Ô¼ºÑ§Ï°ÖеÄÌå»á£¬×ܽá¶ø³É¡£
Ò».rowid¼ò½é
rowid¾ÍÊÇΨһ±êÖ¾¼Ç¼ÎïÀíλÖõÄÒ»¸öid£¬ÔÚoracle 8°æ±¾ÒÔÇ°£¬rowidÓÉfile#+block#+row#×é³É£¬Õ¼ÓÃ6¸öbytesµÄ¿Õ¼ä£¬10 bit µÄ file# ......
Oracle SQL PLUS »ù±¾²Ù×÷1
怬
c:\>sqlplus "sys/test1234 as sysdba" ÒÔsysdbaÉí·ÝµÇ¼
c:\>sqlplus/nolog ÒÔnologÉí·ÝµÇ¼
sql> connect sys/test1234 as sysdba
Connected.
Æô¶¯
SQL> startup &nb ......
SQLµÄÓÅ»¯Ó¦¸Ã´Ó5¸ö·½Ãæ½øÐе÷Õû£º
1.È¥µô²»±ØÒªµÄ´óÐͱíµÄÈ«±íɨÃè
2.»º´æСÐͱíµÄÈ«±íɨÃè
3.¼ìÑéÓÅ»¯Ë÷ÒýµÄʹÓÃ
4.¼ìÑéÓÅ»¯µÄÁ¬½Ó¼¼Êõ
5.¾¡¿ÉÄܼõÉÙÖ´Ðмƻ®µÄCost
SQLÓï¾ä£º
ÊǶÔÊý¾Ý¿â(Êý¾Ý)½øÐвÙ×÷µÄΩһ;¾¶£»
ÏûºÄÁË70%~90%µÄÊý¾Ý¿â×ÊÔ´£»¶ÀÁ¢ÓÚ³ÌÐòÉè¼ÆÂß¼£¬Ïà¶ÔÓÚ¶Ô³ÌÐòÔ´´úÂëµÄÓÅ»¯£¬¶ÔSQLÓï¾äµÄÓÅ»¯Ô ......
×î½ü·¢ÏÖÒ»¸öÎÊÌ⣬һ¸ö9iÅÜÔÚredhat5µÄ£¬¾ÓÈ» select sysdate from dual ³öÀ´µÄʱ¼äºÍʵ¼Êʱ¼ä²îÁ˼¸·ÖÖÓ£¬ÒòΪ»ùÓÚÕâ¸öoracleµÄϵͳʱ¼äÕýÈ·ÐÔ»¹ÊÇÓеãÒªÇóµÄ¡£ËùÒÔÖ»ÄÜ #date -s 2010-2-10 8:55:00 ÁË£¬ÔÙ#clock -w дÈëCOMS ×¢ÒâÐÞ¸Äʱ¼äµÃÔÚrootÏ¡£
ÔÚϵͳÆô¶¯Ê±£¬Linux²Ù×÷ϵͳ½«Ê±¼ä´ÓCMOSÖжÁµ½ÏµÍ³Ê ......