Oracle 10g ÒªµãÕûÀí Ö® ¹ý³Ì¡¢º¯Êý¡¢³ÌÐò°ü¡¢´¥·¢Æ÷
¹ý³Ì¡¢º¯Êý¡¢³ÌÐò°ü¡¢´¥·¢Æ÷
1 ¹ý³Ì
£¨1£©¶¨ÒåÓï·¨
create [or replace ] procedure ¹ý³ÌÃû
[(²ÎÊý1 [in | out | in out] ²ÎÊýÀàÐÍ£¬
²ÎÊý2 [in | out | in out] ²ÎÊýÀàÐÍ£¬
……)]
is | as
begin
Ö´Ðв¿·Ö
exception
Òì³£´¦Àí²¿·Ö
end [¹ý³ÌÃû];
¹ý³ÌÖÐûÓÐdeclare²¿·Ö£¬ÉùÃ÷²¿·ÖÔÚbegin֮ǰ¡£
£¨2£©µ÷ÓÃÓï·¨
Óû§¼È¿ÉÒÔ´ÓÄäÃûµÄPL/SQL³ÌÐò¿éÖе÷Óã¬Ò²¿ÉÒÔÔÚSQL*PlusÖÐʹÓÃexecº¯Êý +¹ý³ÌÃûÀ´µ÷Óá£
Ö´Ðйý³ÌÖл¹Éè¼Æµ½È¨ÏÞÎÊÌ⣬¹ý³ÌµÄȨÏÞÊÇexecute£¬Ê¹ÓÃÏÂÁÐÓï¾ä½øÐÐÊÚȨ£º
grant execute on ¹ý³ÌÃû to Óû§Ãû£»
ÔÚSQL*PLUSÖе÷ÓÃ
EXEC procedure_name(parameter_list)
EXECUTE show_emp(10)
ÔÚPL/SQL¿éÖе÷ÓÃ
BEGIN
procedure_name(parameter_list);
END£»
£¨3£©Ê¹ÓòÎÊý
¢Ù ²ÎÊýģʽ
in£ºÊäÈë²ÎÊý£¬Óɹý³Ì¶ÁÈ¡
out£ºÊä³ö²ÎÊý£¬Óɹý³ÌдÈ룬¸Ã²ÎÊýÊʺÏÓÃÓÚ¹ý³ÌÏòµ÷ÓÃÕß·µ»Ø¶à¸öÐÅÏ¢¡£
in out£º¹ý³ÌÓÃÀ´¶ÁÈ¡ºÍдÈëËüÃǵÄÖµ¡£
¢Ú ²ÎÊý´«µÝ
ÔÚOracleÖУ¬¿ÉÒÔʹÓÃÈýÖÖ·½Ê½´«µÝ²ÎÊý£¬¼´Ê¹ÓÃÃû³Æ±íʾ·¨£¬Ê¹ÓÃλÖñíʾ·¨£¬Ê¹ÓûìºÏ±íʾ·¨¡£
A ʹÓÃÃû³Æ±íʾ·¨£º
¹ý³ÌÃû£¨²ÎÊýÃû => Öµ£¬……£©
²ÎÊýµÄʹÓôÎÐòºÍÉùÃ÷´ÎÐò¿ÉÒÔ²»Ò»Ñù¡£
B ʹÓÃλÖñíʾ·¨£º
¹ý³ÌÃû£¨²ÎÊýÖµ1£¬²ÎÊýÖµ2£¬……£©
»ùÓÚ²ÎÊýÔÚ¹ý³ÌÖж¨ÒåµÄ´ÎÐò½øÐд«µÝ¡£
C ʹÓûìºÏ±íʾ·¨£º
¹ý³ÌÃû£¨²ÎÊýÃû => Öµ£¬²ÎÊýÖµ£¬……£©
¢Û out²ÎÊý
±ØÐëÔÚÖ´Ðйý³ÌÇ°£¬¸ù¾Ý¹ý³Ì½«·µ»ØµÄ²ÎÊý¸öÊý£¬Ïȶ¨ÒåÏàÓ¦µÄ±äÁ¿¼°ÕýÈ·µÄ±äÁ¿ÀàÐÍÀ´½ÓÊÕ·µ»ØÖµ¡£
±ÈÈç˵¶¨ÒåÁËÁ½¸ö±äÁ¿AºÍB£¬ÄÇôA¡¢BÓÃÀ´½ÓÊÕ·µ»ØÖµµÄÓ÷¨ÊÇ£º
exec ¹ý³ÌÃû£¨:A, :B£©;
ΪÁ˲鿴AºÍBµÄÖµ£¬¿ÉÒÔʹÓÃprintÃüÁî
print A£»
print B£»
£¨4£©¹ý³ÌµÄ¹ÜÀí
¢Ù Ð޸Ĵ洢¹ý³Ì
CREATE OR REPLACE PROCEDURE
¢Ú ÖØбàÒë´æ´¢¹ý³Ì
ALTER PROCEDURE procedure_name COMPILE£»
¢Û ɾ³ý´æ´¢¹ý³Ì
DROP PROCEDURE procedure_nameÃû£»
¢Ü ²é¿´¹ý³ÌÔ´´úÂë
select text from user_source where name=procedure_name ;
2 º¯Êý
£¨1£©¸ÅÊö
º¯ÊýºÍ¹ý³ÌºÜÀàËÆ£¬ÊÇÊý¾Ý¿âÖд洢µÄÃüÃûPL/SQL³ÌÐò¿é¡£
´´½¨º¯ÊýÓë´´½¨¹ý³ÌµÄ¹æÔòÒ»Ñù¡£
º¯ÊýÓë¹ý³ÌµÄ°²È«·½Ê
Ïà¹ØÎĵµ£º
¹ÜÀíÎļþ
1 ¹ÜÀí¿ØÖÆÎļþ
£¨1£©¸ÅÊö
¢Ù ¿ØÖÆÎļþÊÇOracleÊý¾Ý¿â×îÖØÒªµÄÎïÀíÎļþ¡£
¢Ú ÿ¸öOracleÊý¾Ý¿â¶¼±ØÐëÓÐÒ»¸ö¿ØÖÆÎļþ¡£
¢Û ÔÚ×°ÔØ£¨mount£©Êý¾Ý¿âʱ£¬Oracle»á¸ù¾Ý³õʼ»¯²ÎÊý¶¨Î»¿ØÖÆÎļþ¡£
¢Ü ÔÚÆô¶¯Àý³Ìʱ£¬Oracle»á¸ù¾Ý¿ØÖÆÎļþÔÚÀý³ÌºÍÊý¾Ý¿âÖ®¼ä½¨Á¢¹ØÁª¡£
¢Ý ´ò¿ªÊý¾Ý¿âʱ£¬Oracle»á¸ù¾Ý¿ØÖÆÎļþËù¼ÇÔ ......
oracleµ¼³öµ¼ÈëÊý¾Ý²Ù×÷
OracleÊý¾Ýµ¼Èëµ¼³öimp/expÃüÁî
Oracle Êý¾Ýµ¼Èëµ¼³öimp/exp¾ÍÏ൱ÓÚoracleÊý¾Ý»¹ÔÓ뱸·Ý¡£expÃüÁî¿ÉÒÔ°ÑÊý¾Ý´ÓÔ¶³ÌÊý¾Ý¿â·þÎñÆ÷µ¼³öµ½±¾µØµÄdmpÎļþ£¬impÃüÁî¿ÉÒÔ°Ñ dmpÎļþ´Ó±¾µØµ¼Èëµ½Ô¶´¦µÄÊý¾Ý¿â·þÎñÆ÷ÖС£ ÀûÓÃÕâ¸ö¹¦ÄÜ¿ÉÒÔ¹¹½¨Á½¸öÏàͬµÄÊý¾Ý¿â£¬Ò»¸öÓÃÀ´²âÊÔ£¬Ò»¸öÓà ......
Ëæ×Åʱ¼äºÍÁ¬½Ó£¬OracleµÄlistener.log»á²»¶ÏÔö³¤,Õâʱ¾ÍÐèÒªÎÒÃǶÔÆä½øÐд¦Àí¡£ÈôÊÔͼֱ½ÓÔÚlistenerÆô¶¯Ê±Ö±½Óɾ³ý£¬¿ÉÄÜ»áÊÕµ½±¨´íÐÅÏ¢ÀàËÆ£ºThe process cannot access the file because it is being used by another process¡£ÈôÖ±½ÓÍ£µô¼àÌýÔÙɾ³ý£¬¶ÔÓÚÉú²ú¿â,Õâ»ù±¾ÊDz»±»ÔÊÐíµÄ²Ù×÷,ÔÚUnixÏÂÃ浱Ȼ¿ÉÒÔrm,µ«Õâ¸öÎÄ ......
PL/SQL
1 ¸ÅÊö
PL/SQLÊÇÒ»ÖÖ¹ý³Ì»¯±à³ÌÓïÑÔ£¬Óû§¿ÉÒÔʹÓÃPL/SQL±àд¹ý³Ì¡¢º¯Êý¡¢³ÌÐò°ü¡¢´¥·¢Æ÷²¢ÇÒ´æ´¢ÕâЩ´úÂë¡£
PL/SQL²»Çø·Ö´óСд¡£
PL/SQL·ÖΪ¼¸²¿·Ö£º
declare ¿ÉÑ¡ &nbs ......
OracleÊý¾Ýµ¼Èëµ¼³öimp/exp¾ÍÏ൱ÓÚoracleÊý¾Ý»¹ÔÓ뱸·Ý¡£expÃüÁî¿ÉÒÔ°ÑÊý¾Ý´ÓÔ¶³ÌÊý¾Ý¿â·þÎñÆ÷µ¼³öµ½±¾µØµÄdmpÎļþ£¬impÃü Áî¿ÉÒÔ°ÑdmpÎļþ´Ó±¾µØµ¼Èëµ½Ô¶´¦µÄÊý¾Ý¿â·þÎñÆ÷ÖС£ ÀûÓÃÕâ¸ö¹¦ÄÜ¿ÉÒÔ¹¹½¨Á½¸öÏàͬµÄÊý¾Ý¿â£¬Ò»¸öÓÃÀ´²âÊÔ£¬Ò»¸öÓÃÀ´ÕýʽʹÓá£
¡¡¡¡Ö´Ðл·¾³£º¿ÉÒÔÔÚSQLPLUS.EXE»òÕßDOS£¨ÃüÁîÐУ©ÖÐÖ´ÐУ¬
¡¡ ......