ÈçºÎ´Ó 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
Ïà¹ØÎĵµ£º
create PROCEDURE pagelist
@tablename nvarchar(50),
@fieldname nvarchar(50)='*',
@pagesize int output,--ÿҳÏÔʾ¼Ç¼ÌõÊý
@currentpage int output,--µÚ¼¸Ò³
@orderid nvarchar(50),--Ö÷¼üÅÅÐò
@sort int,--ÅÅÐò·½Ê½£¬1±íʾÉýÐò£¬0±íʾ½µÐòÅÅÁÐ
......
ÓйØSQLÖÐÂß¼²éѯ´¦ÀíµÄ¸÷¸ö½×¶Î£¨×Ô¼º¸ãµÄÀý×Ó£¬²»¶ÔµÄ»¶ÓÖ¸ÕýŶ£©
SQL²»Í¬ÓÚÆäËûµÄ±à³ÌÓïÑÔµÄ×î´ó×î´óÌØÕ÷ÓÐ3¸ö°É£¬
Ò»¸öÊÇËüÊÇÃæÏò¼¯ºÏµÄ±à³Ì˼Ï룬µÚ¶þ¸öÊÇÈýÖµÂß¼£¨Õâ¸öºóÃæ»á˵µ½£©£¬»¹ÓÐÒ»¸ö¾ÍÊǽñÌìÖ÷ҪҪ˵µÄ²éÑ¯ÔªËØµÄÂß¼´¦Àí´ÎÐò¡£
Çë¿´Ò»¸ö»ù±¾²éѯµÄÂß¼¹ý³Ì£º
(8) SELECT (9) DISTINCT ( ......
http://cheneyph.javaeye.com/blog/453492
ÓÃoracle sql¶ÔÊý×Ö½øÐвÙ×÷: È¡ÉÏÈ¡Õû¡¢ÏòÏÂÈ¡Õû¡¢±£ÁôNλСÊý¡¢ËÄÉáÎåÈë¡¢Êý×Ö¸ñʽ»¯
È¡Õû£¨ÏòÏÂÈ¡Õû£©£º
select floor(5.534) from dual;
select trunc(5.534) from dual;
ÉÏÃæÁ½ÖÖÓ÷¨¶¼¿ÉÒÔ¶ÔÊý×Ö5.534ÏòÏÂÈ¡Õû£¬½á¹ûΪ5.
Èç¹ûÒªÏòÉÏÈ¡Õû £¬µÃµ½½á¹ûÎ ......
½»²æ±íÓï¾äµÄʵÏÖ:
¡¡¡¡ÓÃÓÚ:½»²æ±íµÄÁÐÊýÊÇÈ·¶¨µÄ
¡¡¡¡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 ......
Ò»¡¢×Ö·ûת»»º¯Êý
1¡¢ASCII()
·µ»Ø×Ö·û±í´ïʽ×î×ó¶Ë×Ö·ûµÄASCII ÂëÖµ¡£ÔÚASCII£¨£©º¯ÊýÖУ¬´¿Êý×ÖµÄ×Ö·û´®¿É²»ÓÑ’À¨ÆðÀ´£¬µ«º¬ÆäËü×Ö·ûµÄ×Ö·û´®±ØÐëÓÑ’À¨ÆðÀ´Ê¹Ó㬷ñÔò»á³ö´í¡£
2¡¢CHAR()
½«ASCII Âëת»»Îª×Ö·û¡£Èç¹ûûÓÐÊäÈë0 ~ 255 Ö®¼äµÄASCII ÂëÖµ£¬CHAR£¨£© ·µ»ØNULL ¡£
3¡¢LOWER()ºÍ ......