ORACLE PL/SQL ´æ´¢¹ý³Ì½Ì³Ì
(1)SEQNAME.NEXTVALÀïÃæµÄÖµÈçºÎ¶Á³öÀ´£¿¿ÉÒÔÖ±½ÓÔÚinsert into test values(SEQNAME.NEXTVAL) ÊÇ¿ÉÒÔÓà ÕâÑù£º
SELECT tmp#_seq.NEXTVAL
INTO id_temp
from DUAL; È»ºó¿ÉÒÔÓÃid_temp
(2)PLS-00103: ³öÏÖ·ûºÅ ">"ÔÚÐèÒªÏÂÁÐ֮һʱ£º
´úÂëÈçÏ£º
IF (sum>0)
THEN
begin
INSERT INTO emesp.tp_sn_production_log
VALUES (r_serial_number, , id_temp);
EXIT;
end;
Ò»Ö±±¨sum>0 ÕâÊǸöºÜÓôÃƵÄÎÊÌâ ÒòΪ±äÁ¿ÓÃÁËsum ËùÒÔ²»ÐУ¬ºó¸ÄΪi_sum>0
(3)oracle Óï·¨
1. OracleÓ¦Óñ༷½·¨¸ÅÀÀ
´ð£º1) Pro*C/C++/... : CÓïÑÔºÍÊý¾Ý¿â´ò½»µÀµÄ·½·¨£¬±ÈOCI¸ü³£ÓÃ;
2) ODBC
3) OCI: CÓïÑÔºÍÊý¾Ý¿â´ò½»µÀµÄ·½·¨£¬ºÍProCºÜÏàËÆ£¬¸üµ×²ã£¬ºÜÉÙÓÃ;
4) SQLJ: ºÜеÄÒ»ÖÖÓÃJava·ÃÎÊOracleÊý¾Ý¿âµÄ·½·¨£¬»áµÄÈ˲»¶à;
5) JDBC
6) PL/SQL: ´æ´¢ÔÚÊý¾ÝÄÚÔËÐÐ, ÆäËû·½·¨ÎªÔÚÊý¾Ý¿âÍâ¶ÔÊý¾Ý¿â·ÃÎÊ;
2. PL/SQL
´ð£º1) PL/SQL(Procedual language/SQL)ÊÇÔÚ±ê×¼SQLµÄ»ù´¡ÉÏÔö¼ÓÁ˹ý³Ì»¯´¦ÀíµÄÓïÑÔ;
2) Oracle¿Í»§¶Ë¹¤¾ß·ÃÎÊOracle·þÎñÆ÷µÄ²Ù×÷ÓïÑÔ;
3) Oracle¶ÔSQLµÄÀ©³ä;
4. PL/SQLµÄÓÅȱµã
´ð£ºÓŵ㣺
1) ½á¹¹»¯Ä£¿é»¯±à³Ì£¬²»ÊÇÃæÏò¶ÔÏó;
2) Á¼ºÃµÄ¿ÉÒÆÖ²ÐÔ(²»¹ÜOracleÔËÐÐÔÚºÎÖÖ²Ù×÷ϵͳ);
3) Á¼ºÃµÄ¿Éά»¤ÐÔ(±àÒëͨ¹ýºó´æ´¢ÔÚÊý¾Ý¿âÀï);
4) ÌáÉýϵͳÐÔÄÜ;
µÚ¶þÕÂ
PL/SQL³ÌÐò½á¹¹
1. PL/SQL¿é
´ð£º1) ÉêÃ÷²¿·Ö, DECLARE(²»¿ÉÉÙ);
2) Ö´Ðв¿·Ö, BEGIN...END;
3) Òì³£´¦Àí£¬EXCEPTION(¿ÉÒÔûÓÐ);
2. PL/SQL¿ª·¢»·¾³
´ð£º¿ÉÒÔÔËÓÃÈκδ¿Îı¾µÄ±à¼Æ÷±à¼£¬ÀýÈ磺VI &
Ïà¹ØÎĵµ£º
1¡¢²éÕÒ±íµÄËùÓÐË÷Òý£¨°üÀ¨Ë÷ÒýÃû£¬ÀàÐÍ£¬¹¹³ÉÁУ©£º
select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name = Òª²éѯµÄ±í
2¡¢²éÕÒ±íµÄÖ÷¼ü£¨°üÀ¨Ãû³Æ£¬¹¹³ÉÁУ©£º
select cu.* from user_cons_columns cu, user_con ......
Ò»¡¢ ³£ÓÃÈÕÆÚÊý¾Ý¸ñʽ
1.Y»òYY»òYYY ÄêµÄ×îºóһ룬Á½Î»»òÈýλ
SQL> Select to_char(sysdate,'Y') from dual;
TO_CHAR(SYSDATE,'Y')
--------------------
7
SQL> Select to_char(sysdate,'YY') from dual;
TO_CHAR(SYSDATE,'YY')
---------------------
07
SQL> Select to_char(sysdate,'YYY') from ......
create or replace procedure p //ÓоÍÌæ»»£¬Ã»Óоʹ´½¨
is
cursor c is
select * from emp for update;
begin
for v_emp in c loop
if (v_emp.deptno =10) then
&nb ......
½â¾ö·½·¨...
¹ØÓÚSQL°²×°±»¹ÒÆðµÄÐÞ¸´´ó¼Ò¿ÉÄܶ¼Óöµ½¹ýµ±SQL±»É¾³ýºóÐèÒªÖØа²×°Ê±£¬½øÈë°²×°½çÃæ¾Í»á³öÏÖSQL´íÎóÌáʾ£º
´ÓÇ°µÄ°²×°³ÌÐò²Ù×÷ʹ°²×°³ÌÐò²Ù×÷¹ÒÆð£¬ÐèÒªÖØÐÂÆô¶¯¼ÆËã»ú
È»¶øÕâЩ¶¼Êǰ׷Ѿ¢£¬²»¹ÜÄãÖØÐÂÆô¶¯¶àÉÙ´ÎÒ²¶¼ÎÞ¼ÃÓÚÊ£¬ÏÂÃæÎҾͽ²½²ÔõÑùÄܹ»½â³ý°²×°»òÉý¼¶Ê±Îļþ±»¹ÒÆðµÄ²Ù×÷¡£
µã»÷ ¿ ......