PLSQL±à³ÌÓïÑÔµÄʹÓÃÓë³ÌÐòÉè¼Æ ÏÂÔØ±¾ÎÄ

ʵÑéÎå PL/SQL±à³ÌÓïÑÔµÄʹÓÃÓë³ÌÐòÉè¼Æ

¡¾¿ª·¢ÓïÑÔ¼°ÊµÏÖÆ½Ì¨»òʵÑé»·¾³¡¿

Oracle10g

¡¾ÊµÑéÄ¿µÄ¡¿

£¨1£©Á˽âPL/SQLÔÚOracleÖеĻù±¾¸ÅÄ £¨2£©ÕÆÎÕPL/SQLµÄ¸÷×é³É²¿·Ö£» £¨3£©PL/SQLµÄÔËÓá£

¡¾ÊµÑéÔ­Àí¡¿ 1£®PL/SQL×Ö·û¼¯

ºÍËùÓÐÆäËû³ÌÐòÉè¼ÆÓïÑÔÒ»Ñù£¬PL/SQLÒ²ÓÐÒ»×Ö·û¼¯¡£¶ÁÕßÄÜ´Ó¼üÅÌÉÏÊäÈëµÄ×Ö·û¼¯ÊÇPL/SQLµÄ×Ö·û¡£´ËÍ⣬ÔÚijЩ³¡ºÏ£¬»¹ÓÐʹÓÃijЩ×Ö·ûµÄ¹æ¶¨¡£ÎÒÃǽ«ÒªÏêϸ½éÉÜ£º

? ÓÃPL/SQL±à³Ìʱ¿ÉÄÜʹÓõÄ×Ö·û ? ËãÊõÔËËã·û ? ¹ØÏµÔËËã·û ? ÔÓ·ûºÅ 1£©ºÏ·¨×Ö·û

ÓÃPL/SQL³ÌÐòʱ£¬ÔÊÐíʹÓÃÏÂÁÐ×Ö·û£º ? ËùÓдó¡¢Ð¡Ð´×Öĸ ? Êý×Ö0µ½9 ? ·ûºÅ£º£¨£©+-*/¡´ ¡µ=£¡~£»£º.¡®@ %£¬¡°# $ ^ & _ | { } £¿ [ ] 2£©ËãÊõÔËËã·û

ÏÂÃæÁгöÁËPL/SQLÖг£ÓõÄËãÊõÔËËã·û¡£Èç¹û¶ÁÕßʹÓùýÆäËû¸ß¼¶³ÌÐòÉè¼ÆÓïÑÔ£¬Ïë±Ø²»»áİÉú£º

±í6 ËãÊõÔËËã·û ÔËËã·û ÒâÒå + ¼Ó·¨ - ¼õ·¨ * ³Ë·¨ / ³ý·¨ ** ÃÝ

¹ØÏµÔËËã·û

ÏÂÃæÁгöÁËPL/SQLÖг£ÓõĹØÏµÔËËã·û¡£Èç¹û¶ÁÕßÓÐʹÓÃÆäËû³ÌÐòÉè¼ÆÓïÑԵľ­Ñ飬һ¶¨¼û¹ýÕâЩ·ûºÅ£º

±í7 ¹ØÏµÔËËã·û ÔËËã·û ÒâÒå <> ²»µÈÓÚ != ²»µÈÓÚ ^= ²»µÈÓÚ < СÓÚ > ´óÓÚ = µÈÓÚ

3£©ÔÓ·ûºÅ

PL/SQLΪ֧³Ö±à³Ì£¬»¹Ê¹ÓÃÏÂÊö·ûºÅ¡£ÏÂÃæÁгöÁ˲¿·Ö·ûºÅ£¬ËüÃÇÊÇ×î³£Óõģ¬Ò²ÊÇʹÓÃPL/SQLµÄËùÓжÁÕß¶¼±ØÐëÁ˽âµÄ¡£

±í8 ÔÓ·ûºÅ ·ûºÅ ( ) ; . , := || -- /*Óë*/ Áбí·Ö¸ô Óï¾ä½áÊø Ïî·Ö¸ô£¨ÔÚÀý×ÓÖУ¬Ó÷ָôaccountÓëtable_name£© ×Ö·û´®½ç¶¨·û ¸³Öµ ²¢Öà עÊÍ·û ×¢ÊͶ¨½ç·û ÒâÒå ÑùÀý (¡®Jones¡¯,¡¯Roy¡¯,¡¯Abramson¡¯) Procedure_name(arg1,arg2) Select * from account.table_name; if var1 = ¡®SANDRA¡¯ Rec_read := rec_read+1 Full_name:=¡¯Nahtan¡¯||¡¯¡¯||¡¯Yebba¡¯ --This is a comment /*This too is a comment */ 4£©±äÁ¿

±äÁ¿ÊÇPL/SQLÖÐÓÃÀ´´¦ÀíÊý¾ÝÏîËùÓõÄÃû×Ö¡£¶ÁÕ߸ù¾ÝÏÂÁйæÔòÑ¡Ôñ±äÁ¿Ãû³Æ£º ? ±äÁ¿±ØÐëÒÔ×Öĸ£¨A~Z£©¿ªÍ·¡£

? Æäºó¸ú¿ÉÑ¡µÄÒ»¸ö»ò¶à¸ö×Öĸ£¬Êý×Ö£¨0~9£©»òÌØÊâ×Ö·û$¡¢# »ò_¡£ ? ±äÁ¿³¤¶È²»³¬¹ý30¸ö×Ö·û¡£ ? ±äÁ¿ÃûÖв»ÄÜÓпոñ¡£ 5£©±£Áô×Ö

±£Áô×Ö¿ÉÊÓΪPL/SQL°æÈ¨ËùÓеÄ×Ö·û´®¡£ÔÚ¶¨Òå±äÁ¿Ãûʱ£¬¶ÁÕß²»ÄÜʹÓÃÕâЩ±£Áô×Ö¡£ÀýÈ磬´Ê¡°loop¡±ÔÚPL/SQLÖÐÓÐÌØÊ⺬Ò壬Òò´ËÏÂÁдúÂëÊÇ·Ç·¨µÄ£º

declare employee varchar2(30); loop number;

±£Áô×Ö²»ÄÜÓÃ×÷±äÁ¿Ãû¡£¾¡¹ÜÎÒÃDz»ÍƼö£¬µ«Èç¹û¶ÁÕß¼á³Ö£¬Ò²¿ÉÒÔÁ¬½ÓÁ½¸ö±£Áô×ÖÐγɱäÁ¿Ãû£¨Èçloop_varchar2£©¡£ÍêÕûµÄPL/SQL±£Áô×Ö±í¿ÉÒÔÔÚOracle8ÎĵµÖÐÕÒµ½¡£

2¡¢³£ÓÃÊý¾ÝÀàÐÍ

µ½Ä¿Ç°ÎªÖ¹£¬ÎÒÃÇÌÖÂÛÁËÔÚPL/SQLÖбà³ÌʱÔÊÐíʹÓõÄ×Ö·û£¬±äÁ¿µÄÃüÃûºÍ±£Áô×Ö¡£ÏÂÃæ×ÅÖØÌÖÂÛÊý¾ÝÀàÐÍ¡£PL/SQL³ÌÐòÓÃÓÚ´¦ÀíºÍÏÔʾ¶àÖÖÀàÐ͵ÄÊý¾Ý¡£ºÍËùÓмÆËã»úÈí¼þÒ»Ñù£¬OracleÒ²½«Êý¾ÝÀàÐͷֳɴóÁ¿µÄ×ÓÀà¡£ÀýÈ磺Êý¿É·ÖΪÕûÐÍ£¨²»ÔÊÐíÓÐСÊý£©ºÍСÊý£¨ÓÐһλСÊý»ò¶àλСÊý£©Àà¡£PL/SQLÖ§³Ö¶àÖÖÊý¾ÝÀàÐÍ£¬´Ë´¦ÌÖÂÛ´úÂëÖÐ×î³£Óò¢ÇÒ×îʵÓõÄÊý¾ÝÀàÐÍ¡£ ? Varchar2 ? Number ? Date ? Boolean

3£®PL/SQLµÄ×é¼þ

1£©¿é½á¹¹(Block structure)

PL/SQL³ÌÐòÊÇÓɶÀÁ¢µÄ±äÁ¿ÉùÃ÷¡¢Ö´ÐдúÂëºÍÒì³£´¦ÀíµÈ²¿·Ö´úÂë¿éд³ÉµÄ¡£PL/SQL¿ÉÒÔ×÷Ϊһ¸öÃüÃûµÄ×Ó³ÌÐò´æ·ÅÔÚÊý¾Ý¿âÖУ¬»òÖ±½ÓÔÚSQL*Plus´°¿ÚÖÐ×÷Ϊһ¸öÄäÃûµÄ¿é

±àÂë¡£µ±ÔÚÊý¾Ý¿âÖд洢PL/SQLʱ£¬×Ó³ÌÐò°üÀ¨´æ´¢µ¥ÔªÃüÃûµÄÍ·²¿·Ö£¬³ÌÐòÀàÐ͵ÄÉùÃ÷£»ÒÔ¼°¿ÉÑ¡µÄin, out ºÍin out²ÎÊýµÄ¶¨Òå¡£Ö»ÊÇ¿ÉÖ´Ðв¿·Ö¶¨ÒåΪbegin ºÍendÓï¾äÊǹ̶¨µÄ¡£Declare ºÍexception²¿·ÖÊÇ¿ÉÑ¡µÄ¡£

ÏÂÃæÔÚÒ»¸öÎÞÃû¿éÉϺÍÒ»¸ö´æ´¢¹ý³ÌµÄÀý×Ó¡£ --ÎÞÃû¿é declare ¡­ begin ¡­ end;

--´æ´¢¹ý³Ì

create or replace precodure_name as --ÉùÃ÷²¿·Ö×Ô¶¯¸ú×ÅÓï¾ä¶ø²»ÐèÒª±àÂë¡£ ¡­ begin ¡­ exception ¡­ end;

2£©ÉùÃ÷²¿·Ö£¨Declare section£©

´ËPL/SQL¿éÓÃÓÚ¶¨Òå±äÁ¿¡£ÔÚdeclare¶ÎÖУ¬ÎÒÃÇ¿ÉÕÒµ½Ç°Ãæ½²¹ýµÄ³£ÓÃÊý¾ÝÀàÐÍ£¬ÒÔ¼°ÏÂÒ»½ÚÒª½éÉܵÄcursor£¨¹â±ê£©±äÁ¿ÀàÐÍ¡£ÏÂÃæµÄ³ÌÐòÊÇÒ»¸ö¹ý³ÌµÄdeclare¶ÎÀý×Ó¡£ µ±´æ´¢¶ÔÏó£¨ÃüÃû¿é£©²úÉúʱ£¬declare ¶Î×Ô¶¯¸ú×Åas¹Ø¼ü×Ö¡£ÔÚSQL*Plus±àдһ¸öPL/SQL´úÂë¿é£¨ÄäÃû¿é£©Ê±£¬Óû§±ØÐëÖ¸¶¨DECLARE¡£ Create or replace procedure samp(I_salary in number, I_city in number) as

--ÕâÊÇDECLARE¶Î£»ÒòΪÎÒÃÇÕýÔÚ½øÐÐÃüÃû´æ´¢¶ÔÏóµÄ±àÂ룬declareÊÇÒþº¬µÄ£¬²»Ðèд---³ö¡£

Accum1 number; Accum2 number;

H_date date := sysdate; -±äÁ¿ÄÜÔڴ˳õʼ»¯ Status_flag varchar2(1); Mess_text varchar2(80); Temp_buffer varchar2(1); 3£©¿ØÖƽṹ A£©ifÂß¼­½á¹¹

ÔÚ±àд¼ÆËã»ú³ÌÐòʱ£¬Óи÷ÖÖ¸÷ÑùµÄÇé¿öÐèÒª´¦Àí¡£Õâʱ±ØÐë²âÊÔÌõ¼þ£¬Èç¹û²âÊÔֵΪTRUE£¬×öijÊ£¬ÎªFALSE£¬×öÁíÒ»¼þ²»Í¬µÄÊ¡£PL/SQLÌṩÈýÖÖifÂß¼­½á¹¹¹©Óû§²âÊÔTRUE/FALSEÒÔÍê³ÉÏàÓ¦µÄ¹¤×÷¡£ b1£©if-then

Õâ¸ö½á¹¹ÓÃÓÚ²âÊÔÒ»¸ö¼òµ¥Ìõ¼þ¡£Èç¹û¸ÃÌõ¼þΪTRUE£¬ÔòÖ´ÐÐÒ»Ðлò¶àÐдúÂ룻Èç¹ûÌõ¼þ

²âÊÔΪFALSE£¬Ôò³ÌÐò¿ØÖÆ×ªµ½ºóÃæµÄ´úÂë¡£ ÔÚPL/SQLÖÐʵÏÖifÂß¼­ÓÐÁ½Ìõ¹æÔò£º ¹æÔò1

ÿ¸öifÓï¾ä¶¼ÓÐ×Ô¼ºµÄthen£¬ÒÔif¿ªÊ¼µÄÓï¾äÐв»¸úÓï¾ä½áÊø·û£¨£»£©¡£ ¹æÔò2

ÿ¸öifÓï¾ä¿éÒÔÏàÓ¦µÄend if½áÊø¡£ b2£©if-then-else

ÕâÖֽṹÓëifÓï¾ä·Ç³£ÏàËÆ£¬Î¨Ò»²»Í¬µÄÊÇÔÚÌõ¼þΪFALSEʱ£¬Ö´ÐиúÔÚelseºóµÄÒ»Ìõ»ò¶àÌõÓï¾ä¡£

ÏÂÃæÊÇPL/SQLÖÐifÂß¼­µÄÁíÍâÁ½Ìõ¹æÔò£º ¹æÔò3

ÿ¸öifÓï¾äÓÐÇÒÖ»ÓÐÒ»¸öelse¡£ ¹æÔò4

elseÓï¾äÐв»¸úÓï¾ä½áÊø·û¡£

if-then-elsifÕâÖֽṹÓÃÓÚÌæ´úǶÌ×if-then-else½á¹¹¡£ PL/SQLÖÐÓйØifÂß¼­µÄ×îºóÒ»Ìõ¹æÔò£º ¹æÔò5

elsifÎÞÆ¥ÅäµÄend if¡£

ʵ¼ÊÉÏ£¬¸Ãend ifÊõÓï±¾Óï¾ä¿é¿ªÊ¼µÄif£¬¶ø²»ÊôÓÚelsif¹Ø¼ü×Ö¡£×¢ÒâÉÏÁи÷Çåµ¥µÄ´úÂëËõ½ø²¿·ÖÊÇÈçºÎ±íʾËüÃÇÊôÓÚÄÇÒ»Ìõ¼þµÄ¡£

ǰЩÀý×Óֻ˵Ã÷ÁËÒ»¸öelsifµÄÓï¾ä£»µ«ÊÇ£¬ÔÚÈκÎifÓï¾äÖпÉÒÔÓÐÐí¶àelsifÓï¾ä¡£ÖµµÃ×¢ÒâµÄÊÇelseÓï¾äÊDz»ÐèÒªµÄ¡£

¹ØÓÚÇ°Ãæ¾Ù¹ýµÄÖ´ÕÕ¹ÜÀí²¿ÃÅ(DMV)µÄÀý×Ó£¬ÓÃPL/SQL±à³Ì±íʾÆäÂß¼­ÈçÏ¡£¸ù¾Ý¡°the_act¡±µÄÖµ£¬¹ý³Ì 12a,12b,12g½«±»µ÷Óãº

create or replace procedure license_transaction(the_act in varchar2 as begin if the_act = ¡®DCT¡¯ then 12a;

elseif the_act = ¡®DT¡¯ then 12b; else 12g; end if; end; B£©Ñ­»·

ÏÂÃæ¼¸½Ú½éÉÜPL/SQLÖÐʹÓõļ¸ÖÖÑ­»·ÐÎʽ¡£ LOOP-EXIT-ENDÑ­»·£º

´ËÑ­»·½á¹¹ÓÉÈý²¿·Ö×é³É£¬ÆäÓ÷¨²Î¼ûÏÂÃæÊ¾ÀýÖеÄ×¢ÊÍ£º cnt:=1; --ÔÚÑ­»·¿ªÊ¼Ç°£¬³õʼ»¯Ñ­»·¼ÆÊýÆ÷

loop --µÚÒ»²¿·Ö£ºÒÔÑ­»·¹Ø¼ü×Öloop¿ªÊ¼Ñ­»· cnt:=cnt+1; --µÚ¶þ²¿·Ö£ºÔö¼ÓÑ­»·¼ÆÊýÆ÷µÄÖµ if cnt>=100 then -- ²âÊÔcntÊÇ·ñ·ûºÏÍ˳öÌõ¼þ exit; -- Âú×ãÍ˳öÌõ¼þ£¬Í˳öÑ­»· end if; -- End ifÓëÇ°ÃæµÄifÆ¥Åä ¡­