Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

ÈçºÎ´Ó PL/SQL ´æ´¢º¯Êý·µ»ØÊý×é

ÈÕÆÚ£º2003 Äê 2 Ô 19 ÈÕ
Íê³É´Ë·½·¨Ö¸ÄϺó£¬ÄúÓ¦¸ÃÄܹ»£º
ÔÚ Oracle Êý¾Ý¿âÖд´½¨ VARRAY
ʹÓà oracle.sql.ARRAY Àà
´Ó Java ·ÃÎÊ VARRAY
¼ò½é
±¾ÎĵµÑÝʾÈçºÎ´Ó PL/SQL º¯Êý·µ»ØÊý×é²¢´Ó java Ó¦ÓóÌÐò·ÃÎÊËü¡£Êý×éÊÇÒ»×éÓÐÐòµÄÊý¾ÝÔªËØ¡£ VARRAY ÊÇ´óС¿É±äµÄÊý×é¡£Ëü¾ßÓÐÊý¾ÝÔªËØµÄÅÅÁм¯£¬²¢ÇÒËùÓÐÔªËØÊôÓÚͬһÊý¾ÝÀàÐÍ¡£Ã¿¸öÔªËØ¶¼¾ßÓÐË÷Òý£¬ËüÊÇÓëÔªËØÔÚ VARRAY ÖеÄλÖÃÏà¶ÔÓ¦µÄÒ»¸öÊý×Ö¡£ VARRAY ÖÐÔªËØµÄÊýÁ¿ÊÇ VARRAY µÄ“´óС”¡£ÔÚÉùÃ÷ VARRAY ÀàÐÍʱ£¬±ØÐëÖ¸¶¨Æä×î´óÖµ¡£
ÔÚ´Ë·½·¨Ö¸ÄÏÖУ¬PL/SQL ´æ´¢º¯Êý´Ó SCOTT ģʽµÄ EMP ±íÖÐÈ¡³öËùÓйÍÔ±µÄÐÕÃû£¬ÒÔÕâЩÐÕÃû´´½¨Ò»¸öÊý×é²¢½«Æä·µ»Ø¡£´Ó Java Ó¦ÓóÌÐòµ÷ÓÃ´Ë PL/SQL ´æ´¢º¯Êý£¬ÏòÓû§ÏÔʾ¹ÍÔ±µÄÐÕÃû¡£
Èí¼þÐèÇó
Oracle9i Database version 9.0.1 »ò¸üа汾¡£Äú¿É´Ó Oracle ¼¼ÊõÍøÏÂÔØ Oracle9i Êý¾Ý¿â¡£
JDK1.2.x »ò¸ü¸ß°æ±¾¡£¿É´Ó´Ë´¦ÏÂÔØ¡£
Oracle9i JDBC Çý¶¯³ÌÐò¡£JDBC Çý¶¯³ÌÐò¿É´Ó ORACLE_HOME/jdbc/lib ´¦»ñµÃ¡£Ò²¿É´Ó´Ë´¦ÏÂÔØ¡£
ÔÚÊý¾Ý¿âÖд´½¨Ò»¸ö SQLVARRAY ÀàÐÍ£¬ÔÚ±¾ÀýÖУ¬ËüÊÇ VARCHAR2 ÀàÐÍ¡£ ×÷Ϊ scott/tiger Óû§Á¬½Óµ½Êý¾Ý¿â£¬²¢ÔÚ SQL Ìáʾ·û´¦Ö´ÐÐÒÔÏÂÃüÁî¡£
SQL>CREATE OR REPLACE TYPE EMPARRAY is VARRAY(20) OF VARCHAR2(30)
SQL>/
È»ºó´´½¨ÏÂÃæµÄº¯Êý£¬Ëü·µ»ØÒ»¸ö VARRAY¡£
CREATE OR REPLACE FUNCTION getEmpArray RETURN EMPARRAY
AS
l_data EmpArray := EmpArray();
CURSOR c_emp IS SELECT ename from EMP;
BEGIN
FOR emp_rec IN c_emp LOOP
l_data.extend;
l_data(l_data.count) := emp_rec.ename;
END LOOP;
RETURN l_data;
END;
ÔÚÊý¾Ý¿âÖд´½¨º¯Êýºó£¬¿ÉÒÔ´Ó java Ó¦ÓóÌÐòµ÷ÓÃËü²¢ÔÚÓ¦ÓóÌÐòÖлñµÃÊý×éÊý¾Ý¡£ÏÂÃæ¸ø³ö´úÂë¶Î£¬´Ó Java Ó¦ÓóÌÐòÖ´ÐÐ PL/SQL ´æ´¢º¯Êý¡£µ¥»÷´Ë´¦²é¿´ÍêÕûµÄÓ¦ÓóÌÐòÔ´´úÂë¡£
public static void main( ) {
.........
.........
OracleCallableStatement stmt =(OracleCallableStatement)conn.prepareCall
( "begin ?:= getEMpArray; end;" );
// The name we use below, EMPARRAY, has to match the name of the
// type defined in the PL/SQL Stored Function
stmt.registerOutParameter( 1, OracleTypes.ARRAY,"EMPARRAY" );
stmt.executeUpdate();
// Get the ARRAY


Ïà¹ØÎĵµ£º

SQL×¢Èë

Ëæ×ÅB/SģʽӦÓÿª·¢µÄ·¢Õ¹£¬Ê¹ÓÃÕâÖÖģʽ±àдӦÓóÌÐòµÄ³ÌÐòÔ±Ò²Ô½À´Ô½¶à¡£µ«ÊÇÓÉÓÚÕâ¸öÐÐÒµµÄÈëÃÅÃż÷²»¸ß£¬³ÌÐòÔ±µÄˮƽ¼°¾­ÑéÒ²²Î²î²»Æë£¬Ï൱´óÒ»²¿·Ö³ÌÐòÔ±ÔÚ±àд´úÂëµÄʱºò£¬Ã»ÓжÔÓû§ÊäÈëÊý¾ÝµÄºÏ·¨ÐÔ½øÐÐÅжϣ¬Ê¹Ó¦ÓóÌÐò´æÔÚ°²È«Òþ»¼¡£Óû§¿ÉÒÔÌá½»Ò»¶ÎÊý¾Ý¿â²éѯ´úÂ룬¸ù¾Ý³ÌÐò·µ»ØµÄ½á¹û£¬»ñµÃijЩËûÏëµÃÖªµÄÊ ......

ÓÃJavaдµÄMy SQL²Ù×÷·â×°Àà

Kagula
2009-11-17
 
¹Ø¼ü´Ê
    Java My SQL
 
¸ÅÒª£º
    ¶ÔMy SQL²Ù×÷·â×°µÄJavaÔ´ÂëʾÀý¡£
 
ÒªÇó»·¾³£º
    J2SE1.5 + Eclipse3.4.2
 
ÕýÎÄ£º
Ïȵ½ÍøÉÏÏÂÔØmysql-connector-java-5.0.8-bin.jar£¬²¢°ÑËü·Åµ½ÄãµÄJava ProjectµÄUser Lib ......

SQL²éѯÓï¾ä´óÈ« T

 --Óï ¾ä                                ¹¦ ÄÜ
1¡¢Êý¾Ý²Ù×÷
Select      --´ÓÊý¾Ý¿â±íÖмìË÷Êý¾ÝÐкÍÁÐ
Insert & ......

sql ½»²æ±í

½»²æ±íÓï¾äµÄʵÏÖ:
¡¡¡¡ÓÃÓÚ:½»²æ±íµÄÁÐÊýÊÇÈ·¶¨µÄ
¡¡¡¡select name,sum(case subject when 'Êýѧ' then source else 0 end) as 'Êýѧ',
¡¡¡¡sum(case subject when 'Ó¢Óï' then source else 0 end) as 'Ó¢Óï',
¡¡¡¡sum(case subject when 'ÓïÎÄ' then source else 0 end) as 'ÓïÎÄ'
¡¡¡¡from test
¡¡¡¡group by name ......

SQL³£ÓÃ×Ö·û´®º¯Êý

Ò»¡¢×Ö·ûת»»º¯Êý
1¡¢ASCII()
·µ»Ø×Ö·û±í´ïʽ×î×ó¶Ë×Ö·ûµÄASCII ÂëÖµ¡£ÔÚASCII£¨£©º¯ÊýÖУ¬´¿Êý×ÖµÄ×Ö·û´®¿É²»ÓÑ’À¨ÆðÀ´£¬µ«º¬ÆäËü×Ö·ûµÄ×Ö·û´®±ØÐëÓÑ’À¨ÆðÀ´Ê¹Ó㬷ñÔò»á³ö´í¡£
2¡¢CHAR()
½«ASCII Âëת»»Îª×Ö·û¡£Èç¹ûûÓÐÊäÈë0 ~ 255 Ö®¼äµÄASCII ÂëÖµ£¬CHAR£¨£© ·µ»ØNULL ¡£
3¡¢LOWER()ºÍ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ