일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | 3 | ||||
4 | 5 | 6 | 7 | 8 | 9 | 10 |
11 | 12 | 13 | 14 | 15 | 16 | 17 |
18 | 19 | 20 | 21 | 22 | 23 | 24 |
25 | 26 | 27 | 28 | 29 | 30 | 31 |
- node.js
- 전자정부프레임워크
- Eclipse
- PHP
- swingx
- Ajax
- PLSQL
- tomcat
- phonegap
- Spring
- WebLogic
- GPS
- Struts
- MFC
- sencha touch
- jsr 296
- ibsheet
- 가우스
- JDOM
- oracle
- appspresso
- jQuery
- JSON
- 선택적조인
- iBATIS
- MySQL
- Google Map
- rowspan
- Android
- dock
- Today
- Total
Where The Streets Have No Name
Variable in list with Oracle JDBC and RDBMS 본문
참고 : http://theblasfrompas.blogspot.com/2008/02/variable-in-list-with-oracle-jdbc-and.html
I always wanted to create a variable in list query using a single parameter and found that it wasn't possible with JDBC.
SELECT * FROM DEPT WHERE like DEPTNO IN (?);
Then I stumbled upon this on steve's blog, which he shows how he did it in ADF BC using a SQL function.
http://radio.weblogs.com/0118231/stories/2004/09/23/notYetDocumentedAdfSampleApplications.html
126. Using Comma-Separated String Bind for Variable IN List [10.1.3.3] 10-JAN-2008
So his my simple JDBC program method showing how it works, using the DEPT table here.
public void run () throws SQLException
{
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rset = null;
String queryInList =
"SELECT DEPTNO, " +
" DNAME, " +
" LOC " +
"FROM DEPT " +
"WHERE DEPTNO IN " +
"(SELECT * FROM TABLE(CAST(in_number_list(?) as num_table)))";
try
{
conn = getConnection();
stmt = conn.prepareStatement(queryInList);
stmt.setString(1, "10, 20, 30");
rset = stmt.executeQuery();
while (rset.next())
{
System.out.println("Dept [" + rset.getInt(1) + ", " +
rset.getString(2) + "]");
}
}
catch (SQLException e)
{
System.out.println("SQLException occurred");
e.printStackTrace();
}
finally
{
if (rset != null)
rset.close();
if (stmt != null)
stmt.close();
if (conn != null)
conn.close();
}
}
So the output is as follows showing that only records 10, 20, 30 will be returned.
Dept [10, ACCOUNTING]
Dept [20, RESEARCH]
Dept [30, SALES]
Note: SQL being used is as follows
CREATE TYPE num_table AS TABLE OF NUMBER;
/
CREATE OR REPLACE FUNCTION in_number_list (p_in_list IN VARCHAR2)
RETURN num_table
AS
l_tab num_table := num_table();
l_text VARCHAR2(32767) := p_in_list || ',';
l_idx NUMBER;
BEGIN
LOOP
l_idx := INSTR(l_text, ',');
EXIT WHEN NVL(l_idx, 0) = 0;
l_tab.extend;
l_tab(l_tab.last) := to_number(TRIM(SUBSTR(l_text, 1, l_idx - 1)));
l_text := SUBSTR(l_text, l_idx + 1);
END LOOP;
RETURN l_tab;
END;
/
show errors