Oracle PL/SQL±à³Ì¹æ·¶Ö¸ÄÏ
Ò»¡¢PL/SQL±à³Ì¹æ·¶Ö®´óСд
¾ÍÏñÔÚSQLÖÐÒ»Ñù£¬PL / SQLÖÐÊDz»Çø·Ö´óСдµÄ¡£ÆäÒ»°ã×¼ÔòÈçÏ£º
¹Ø¼ü×Ö(BEGIN, EXCEPTION, END, IF THEN ELSE,LOOP, END LOOP)¡¢Êý¾ÝÀàÐÍ(VARCHAR2, NUMBER)¡¢ÄÚ²¿º¯Êý(LEAST, SUBSTR)ºÍÓû§¶¨ÒåµÄ×Ó³ÌÐò(procedures, functions,packages)£¬Ê¹Óôóд¡£
±äÁ¿ÃûÒÔ¼°SQLÖеÄÁÐÃûºÍ±íÃû£¬Ê¹ÓÃСд¡£
¶þ¡¢PL/SQL±à³Ì¹æ·¶Ö®¿Õ°×
¿Õ°×£¨¿ÕÐкͿոñ£©ÔÚPL/SQLÖÐÈçͬÔÚSQLÖÐÒ»ÑùÖØÒª£¬ÒòΪËüÊÇÌá¸ß´úÂë¿É¶ÁÐÔµÄÒ»¸öÖØÒªÒòËØ¡£»»¾ä»°Ëµ£¬¿ÉÒÔͨ¹ýÔÚ´úÂëÖÐʹÓÃËõ½øÀ´ÌåÏÖ³ÌÐòµÄÂß¼½á¹¹¡£ÒÔÏÂÊÇһЩ½¨Ò飺
ÔڵȺŻò±È½Ï²Ù×÷·ûµÄ×óÓÒ¸÷ÁôÒ»¸ö¿Õ¸ñ£»
½á¹¹´Ê£¨DECLARE, BEGIN, EXCEPTION, END,IF and END IF, LOOP and END LOOP£©¾Ó×óÅÅÁС£ÁíÍ⣬½á¹¹ÖеÄǶÌ׽ṹҪËõ½øÈý¸ö¿Õ¸ñ£¨Ê¹Óÿոñ¼ü£¬¶ø²»ÊÇTab¼ü£©£»
Ö÷Òª´úÂë¶ÎÖ®¼äÓÿÕÐиô¿ª£»
°Ñͬһ½á¹¹µÄ²»Í¬Âß¼²¿·Ö·Ö¿ªÐ´ÔÚ¶ÀÁ¢µÄÐУ¬¼´Ê¹Õâ¸ö½á¹¹ºÜ¶Ì¡£ÀýÈ磬IFºÍTHEN±»·ÅÔÚͬһÐУ¬¶øELSE ºÍEND IFÔò·ÅÔÚ¶ÀÁ¢µÄÐС£
Èý¡¢PL/SQL±à³Ì¹æ·¶Ö®ÃüÃûÔ¼¶¨
ʹÓÃÒÔÏÂǰ׺¶ÔÓÚ±ÜÃâÓë¹Ø¼ü×ֺͱíÃûÁÐÃûÏà³åÍ»ÊǺÜÓаïÖúµÄ£º
v_±äÁ¿Ãû
con_³£Á¿Ãû
i_ÊäÈë²ÎÊýÃû£¬o_Êä³ö²ÎÊýÃû£¬io_ÊäÈëÊä³ö²ÎÊýÃû
c_ÓαêÃû »òÕß ÓαêÃû_cur
rc_ Ref CursorÃû
r_RecordÃû »òÕß RecordÃû_rec
FOR r_stud IN c_stud LOOP…
FOR stud_rec IN stud_cur LOOP
type_Ãû³Æ£¬Ãû³Æ_type (Óû§¶¨ÒåµÄÀàÐÍ)
t_±íÃû£¬±íÃû_tab £¨PL/SQL ±í£©
rec_RecordÃû£¬RecordÃû_rec £¨Record±äÁ¿£©
e_Òì³£Ãû £¨Óû§¶¨ÒåµÄÒì³££©
°üµÄÃû³ÆÓ¦¸ÃÃèÊö°üÄڵĴ洢¹ý³ÌºÍº¯ÊýÖ÷ÒªËùÍê³ÉµÄ¹¦ÄÜ
´æ´¢¹ý³ÌµÄÃû³ÆÓ¦¸ÃÃèÊö¸Ã´æ´¢¹ý³ÌËùÖ´ÐеĶ¯×÷
º¯ÊýµÄÃû³ÆÓ¦¸ÃÃèÊöËù·µ»ØµÄ±äÁ¿
ÀýÈ磺
PACKAGE student_admin
– admin ºó׺¿ÉÄÜÊÇÓÃÓÚ±íʾ¹ÜÀí¹¦ÄÜ.
PROCEDURE remove_student (i_student_id IN student.studid%TYPE);
FUNCTION student_enroll_count (i_student_id student.studid%TYPE)
RETURN INTEGER;
ËÄ¡¢PL/SQL±à³Ì¹æ·¶Ö®×¢ÊÍ
PL/SQLÖеÄ×¢ÊÍÈçͬSQLÖеÄ×¢ÊÍÒ»ÑùÖØÒª¡£ËûÃÇÓ¦¸Ã½âÊͳÌÐòµÄÖ÷Òª²¿·ÖºÍËùÓйؼüµÄÂß¼²½Öè¡£
ʹÓõ¥ÐÐ×¢ÊÍ(–)¶ø²»ÊǶàÐÐ×¢ÊÍ(/*)¡£¼´Ê¹PL/SQL¶ÔÕâЩעÊÍ×öͬÑù´¦Àí£¬ÕâÑùÔÚ´úÂëÍê³Éºó½øÐе÷ÊÔÒ²»áÈÝÒ×Щ£¬ÒòΪÄã²»ÄÜÔÚ¶àÐÐ×¢ÊÍÖÐǶÈë¶àÐÐ×¢ÊÍ¡£»»¾ä»°Ëµ£¬µ¥ÐÐ×¢ÊÍ´úÂëÖпÉÒÔ²¿·ÖÈ¡Ïû×¢ÊÍ£¬¶øÔÚ¶àÐÐ×¢ÊÍ´úÂëÖÐÔò²»ÐС£
Îå¡¢ÆäËûµÄ½¨Òé
¶ÔÓÚPL/SQLÖÐǶÈëµÄSQLÉùÃ÷
Ïà¹ØÎĵµ£º
¶Ô×óÁ¬½Ó¡¢ÓÒÁ¬½ÓµÄÒÉ»óµÃµ½½â¾ö¡£
ÔÚOracle PL-SQLÖУ¬×óÁ¬½ÓºÍÓÒÁ¬½ÓÒÔÈçÏ·½Ê½À´ÊµÏÖ
²é¿´ÈçÏÂÓï¾ä£º
SELECT emp_name, dept_name
FORM Employee, Department
WHERE Employee.emp_deptid(+) = Department.deptid
´ËSQLʹÓÃÁËÓÒÁ¬½Ó£¬¼´“(+)”ËùÔÚλÖõÄÁíÒ»²àΪÁ¬½Óµ ......
ÔÚÒ»°ãSelectÓï¾äÖÐÎÒÃÇͨ¹ýOrder by ...Asc/DescÀ´½øÐÐÅÅÐò¡£µ«ÊÇÕâÖÖÅÅÐò·½Ê½ÔÚÊä³öʱ£¬²»°üº¬ÅÅÐòºóµÄÐòºÅÐÅÏ¢¡£
ÏÖÔÚ½éÉÜÒ»ÏÂOracleÖпÉÒÔÓÃÓÚÅÅÐòÊä³öµÄ·½·¨¡£
1. RANK ( ) OVER ( [query_partition_clause] order_by_clause )
RANK ( ) OVER ( [query_p ......
ÔÚ Oracle 10g ÖÐ
¿ÉÒÔͨ¹ý http://localhost:5560/isqlplus ·ÃÎÊ isqlplus
ÔÚ isqlplus ÖÐ ¿ÉÒÔÖ´ÐÐ plsql
set serveroutput on size 100000 // ´ò¿ª ·þÎñÆ÷µÄÊä³ö on ºóÃæÊÇ »º´æµÄ´óС ·¶Î§ÊÇ (2000 ÖÁ 1000000)
begin
dbms_output.put_line('hel ......
1¡¢°´Ê±¼äÅÅÐò
select * from tbl_lms_loginhistory order by loginhistory_logintime desc
2¡¢
ÔÚÒ»¸öÊý¾Ý¿âÖÐÊÇÏÂÁеÄÑù×Ó
³µ´Î Éí·ÝÖ¤ºÅ
27 1
27 2
45 1
25 1
45 2
ÏÖÔÚÎÒÏë »ñµÃµÄÊÇ
³µ´Î ³Ë×ø´ÎÊý
25 1
27 3
45 2
ÇëÎÊ£¬ÎÒµÄSQLÓï¾ä¸ÃÔõôд£¿
select ³µ´ ......
The aspnet_Profile table contains the following fields: UserId, PropertyNames, PropertyValuesString, PropertyValuesBinary, and LastUpdatedDate. The PropertyNames field contains a string delimited with colons (:) that identify which profile fields are stored, what their datatype is and their of ......