ÈçºÎ´Ó 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±íʾ½µÐòÅÅÁÐ
......
Ëæ×ÅB/SģʽӦÓÿª·¢µÄ·¢Õ¹£¬Ê¹ÓÃÕâÖÖģʽ±àдӦÓóÌÐòµÄ³ÌÐòÔ±Ò²Ô½À´Ô½¶à¡£µ«ÊÇÓÉÓÚÕâ¸öÐÐÒµµÄÈëÃÅÃż÷²»¸ß£¬³ÌÐòÔ±µÄˮƽ¼°¾ÑéÒ²²Î²î²»Æë£¬Ï൱´óÒ»²¿·Ö³ÌÐòÔ±ÔÚ±àд´úÂëµÄʱºò£¬Ã»ÓжÔÓû§ÊäÈëÊý¾ÝµÄºÏ·¨ÐÔ½øÐÐÅжϣ¬Ê¹Ó¦ÓóÌÐò´æÔÚ°²È«Òþ»¼¡£Óû§¿ÉÒÔÌá½»Ò»¶ÎÊý¾Ý¿â²éѯ´úÂ룬¸ù¾Ý³ÌÐò·µ»ØµÄ½á¹û£¬»ñµÃijЩËûÏëµÃÖªµÄÊ ......
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
/// <summary>
/ ......
Ò»
SQLÖØ¸´¼Ç¼²éѯ£¨×ª×Ôhttp://blog.csdn.net/RainyLin/archive/2009/02/17/3901956.aspx£©
SQLÖØ¸´¼Ç¼²éѯ
1¡¢²éÕÒ±íÖжàÓàµÄÖØ¸´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ýµ¥¸ö×ֶΣ¨peopleId£©À´ÅжÏ
select * from people
where peopleId in (select peopleId from people group ......