OracleÊý¾Ý¿âÓαêʹÓôóÈ«
SQLÊÇÓÃÓÚ·ÃÎÊORACLEÊý¾Ý¿âµÄÓïÑÔ£¬PL/SQLÀ©Õ¹ºÍ¼ÓÇ¿ÁËSQLµÄ¹¦ÄÜ£¬Ëü ͬʱÒýÈëÁ˸üÇ¿µÄ³ÌÐòÂß¼¡£ PL/SQLÖ§³ÖDMLÃüÁîºÍSQLµÄÊÂÎñ¿ØÖÆÓï¾ä¡£DDLÔÚPL/SQLÖв»±»Ö§³Ö£¬Õâ¾ÍÒâζ×÷ÔÚPL/SQL³ÌÐò¿éÖв»ÄÜ´´½¨±í»òÆäËûÈκζÔÏ󡣽Ϻà µÄPL/SQL³ÌÐòÉè¼ÆÊÇÔÚPL/SQL¿éÖÐʹÓÃÏóDBMS_SQLÕâÑùµÄÄÚ½¨°ü»òÖ´ÐÐEXECUTE IMMEDIATEÃüÁÁ¢¶¯Ì¬SQLÀ´Ö´ÐÐDDLÃüÁPL/SQL±àÒëÆ÷±£Ö¤¶ÔÏóÒýÓÃÒÔ¼°Óû§µÄȨÏÞ¡£
¡¡¡¡ÏÂÃæÎÒÃǽ«ÌÖÂÛ¸÷ÖÖÓÃÓÚ·ÃÎÊORACLEÊý¾Ý¿âµÄDDLºÍTCLÓï¾ä¡£
¡¡¡¡²éѯ
¡¡¡¡SELECTÓï¾äÓÃÓÚ´ÓÊý¾Ý¿âÖвéѯÊý¾Ý£¬µ±ÔÚPL/SQLÖÐʹÓÃSELECTÓï¾äʱ£¬ÒªÓëINTO×Ó¾äÒ»ÆðʹÓ㬲éѯµÄ·µ»ØÖµ±»¸³ÓèINTO×Ó¾äÖеıäÁ¿£¬±äÁ¿µÄÉùÃ÷ÊÇÔÚDELCAREÖС£SELECT INTOÓï·¨ÈçÏ£º
SELECT [DISTICT|ALL]{*|column[,column,...]}
INTO (variable[,variable,...] |record)
from {table|(sub-query)}[alias]
WHERE............
¡¡¡¡PL/SQLÖÐSELECTÓï¾äÖ»·µ»ØÒ»ÐÐÊý¾Ý¡£Èç¹û³¬¹ýÒ»ÐÐÊý¾Ý£¬ÄÇô¾ÍҪʹÓÃÏÔʽÓα꣨¶ÔÓαêµÄÌÖÂÛÎÒÃǽ«ÔÚºóÃæ½øÐУ©£¬INTO×Ó¾äÖÐÒªÓÐÓëSELECT×Ó¾äÖÐÏàͬÁÐÊýÁ¿µÄ±äÁ¿¡£INTO×Ó¾äÖÐÒ²¿ÉÒÔÊǼǼ±äÁ¿¡£
¡¡¡¡%TYPEÊôÐÔ
¡¡¡¡ÔÚPL/SQLÖпÉÒÔ½«±äÁ¿ºÍ³£Á¿ÉùÃ÷ΪÄÚ½¨»òÓû§¶¨ÒåµÄÊý¾ÝÀàÐÍ£¬ÒÔÒýÓÃÒ»¸öÁÐÃû£¬Í¬Ê±¼Ì³ÐËûµÄÊý¾ÝÀàÐͺʹóС¡£ÕâÖÖ¶¯Ì¬¸³Öµ·½·¨ÊǷdz£ÓÐÓõ쬱ÈÈç±äÁ¿ÒýÓõÄÁеÄÊý¾ÝÀàÐͺʹóС¸Ä±äÁË£¬Èç¹ûʹÓÃÁË%TYPE,ÄÇôÓû§¾Í²»±ØÐ޸ĴúÂ룬·ñÔò¾Í±ØÐëÐ޸ĴúÂë¡£
¡¡Àý£º
v_empno SCOTT.EMP.EMPNO%TYPE;
v_salary EMP.SALARY%TYPE;
¡¡¡¡²»µ«ÁÐÃû¿ÉÒÔʹÓÃ%TYPE,¶øÇÒ±äÁ¿¡¢Óαꡢ¼Ç¼£¬»òÉùÃ÷µÄ³£Á¿¶¼¿ÉÒÔʹÓÃ%TYPE¡£Õâ¶ÔÓÚ¶¨ÒåÏàͬÊý¾ÝÀàÐ͵ıäÁ¿·Ç³£ÓÐÓá£
DELCARE
V_A NUMBER(5):=10;
V_B V_A%TYPE:=15;
V_C V_A%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE
('V_A='||V_A||'V_B='||V_B||'V_C='||V_C);
END
SQL>/
V_A=10 V_B=15 V_C=
PL/SQL procedure successfully completed.
SQL>
¡¡¡¡ÆäËûDMLÓï¾ä
¡¡ ¡¡ÆäËü²Ù×÷Êý¾ÝµÄDMLÓï¾äÊÇ:INSERT¡¢UPDATE¡¢DELETEºÍLOCK TABLE,ÕâЩÓï¾äÔÚPL/SQLÖеÄÓï·¨ÓëÔÚSQLÖеÄÓï·¨Ïàͬ¡£ÎÒÃÇÔÚÇ°ÃæÒѾÌÖÂÛ¹ýDMLÓï¾äµÄʹÓÃÕâÀï¾Í²»ÔÙÖØ¸´ÁË¡£ÔÚDMLÓï¾äÖпÉÒÔʹÓÃÈÎ ºÎÔÚDECLARE²¿·ÖÉùÃ÷µÄ±äÁ¿£¬Èç¹ûÊÇǶÌ׿飬ÄÇôҪעÒâ±äÁ¿µÄ×÷Ó÷¶Î§¡£
¡¡¡¡Àý£º
CREATE OR REPLACE PROCEDURE FIRE_EMPLOYEE (pempno in number)
¡¡AS
¡¡¡¡v_ename EMP.ENAME%TYPE;
¡¡BEGIN
¡¡¡¡SELECT ename INTO v
Ïà¹ØÎĵµ£º
ËäȻѧϰJavaºÜ¾ÃÁË£¬×Ô¼ºÒ²Á¬½Ó¹ýһЩÊý¾Ý¿â£¬±ÈÈçmysqlÖ®ÀàµÄ£¬Èç½ñÄØ£¬Ò²Ñ§Ï°ÁËÒ»¶Îʱ¼äµÄOracle£¬È»¶øÄØ£¬½ñÌìÊÇÎÒµÚÒ»´ÎÁ¬½ÓOracle£¬ºÙºÙ£¬Ó¦¸Ã»¹²»ËãÌ«³Ù°É¡£
½ñÌìÄØ£¬Óе㱿׾£¬´ó¼ÒĪЦ£¡
ÎÒÕâÊÇÒ»¸ö²éѯÀý×Ó
Ê×ÏÈ£¬Ô ......
ËäÈ»ÒÔǰÓÃÁ˼¸ÄêµÄORACLE µÄpl/sql µ«°²×°½ñÌ컹ÊǵÚÒ»´Î£º
1£º »¹ÊÇÏȲο¼Ò»ÏÂÍøÕ¾£ºhttp://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
1. Oracle 10g windows ƽ̨ϰ²×°
¡¡¡¡ÏÂÔØµØÖ·£º http://download.csdn.net/source/1850497¡¡¡¡
¡¡¡¡ÔÚÏß¹Û¿´µØÖ·£ºhttp://v.youku.com ......
¸ßÐÔÄÜ Oracle JDBC ±à³Ì
Á˽âÈçºÎÀûÓÃÁ¬½ÓºÍÓï¾ä³ØÌØÐÔÀ´Ìá¸ß Oracle Çý¶¯µÄ JDBC ³ÌÐòµÄÐÔÄÜ¡£
×÷ÕߣºYuli Vasiliev
2009 Äê 4 Ô·¢²¼
ʹÓÃÖîÈçÁ¬½Ó³ØºÍÓï¾ä³ØµÈ³Ø¼¼Êõ¿ÉÒÔÏÔÖøÌá¸ßÊý¾Ý¿âÃܼ¯ÐÍÓ¦ÓóÌÐòµÄÐÔÄÜ£¬ÒòΪÕâÑù¿ÉÒÔʵÏÖ¶ÔÏóÖØÓ㬶øÎÞÐ軨·Ñʱ¼äºÍ×ÊÔ´ÖØÐ´´½¨¶ÔÏó¡£
Èç¹ûÓ¦ÓóÌÐòÓëÊý¾Ý¿âƵ·±½»»¥²¢Ç ......
Oracleɾ³ýÖØ¸´¼Ç¼µÄ×îºÃµÄ·½·¨£º
DELETE from EMP E WHERE E.ROWID > (SELECT MIN(y.ROWID)
from EMP y
WHERE y.EMPNO = E.EMPNO )
1¡¢×Ó²éѯÕÒ³öijԱ¹¤×îµÍµÄROWID£¨¿Ï¶¨Ö»ÓÐÒ»¸ö£©£¬ÆäËü´óÓÚÕâÌõ¼Ç¼ROWIDµÄ£¬È«²¿É¾³ý¡£
2¡¢OracleÖÐʹÓÃROWIDɾ³ýÖØ¸´¼Ç¼×î¿ì,ÒòΪRowid¶ÔÓ¦ÕâÌõ¼Ç¼ÔÚ´ÅÅÌÉϵÄλÖÃ
ÃæÊÔ£º ......
ʹÓÃultraedit32´ò¿ªµ¼³öµÄdmpÎļþ£¬ÎļþÄÚÈÝÒÔÊ®Áù½øÖÆÏÔʾÊý¾Ý£¬ÆäÖеڶþ¡¢Èý×Ö½ÚµÄÊý¾Ý¼Ç¼µÄÊÇ´ËÎļþµÄ×Ö·û¼¯¡£
ÀýÈ磺
03 54
½«Æäת»»Îª10½øÖÆ£¬ÖµÎª852£¬Ê¹ÓÃÃüÁî
select nls_charset_name(852) from dual
µÃµ½½á¹û
ZHS16GBK
µ±Ç°Ê¹ÓÃwinXP¼òÌåÖÐÎIJÙ×÷ϵͳ£¬Oracle¿Í»§¶ËĬÈÏ×Ö·û¼¯ÎªGBK¡£
´´½¨Êý¾Ý¿â£¬É趨 ......