|
我目前正在学习PL / SQL,所以我仍然是新手。假设您有一个生产数据库,您可以使用Oracle SQL
0 E% `0 ]+ u! D. @8 [1 iDeveloper将其连接到该数据库。您只有该数据库的READ特权。因此,您不能创建或编辑任何表。; _ v3 J2 i' h: q0 r9 D* |$ G
我的问题是,如果我有大量的ID,我必须将其与该数据库中的表连接起来,该怎么办?
1 B5 ]( Z) l& d, X* n9 ^- ]: j显然,我可以将ID加载到临时表上,然后进行联接,但这确实很乏味,因为我只有READ特权。硬编码ID也不是一种选择,因为列表太大。8 t& C# Q) R* k- h
另外请注意,我知道TEMPORARY表的概念。但不幸的是,我也没有创建这些特权。. U& \0 R: b1 f' b
SQL Developer中有什么解决方案可以加载ID列表以与数据库中的表匹配?% ^* Q4 D. E& [) S+ d$ H
/ Q. T$ Y, p9 L, f/ z解决方案:9 x1 J' S! {- A4 a! B. x% |
; w+ ^1 j$ U& y- U! |; i: }4 { W' o" H5 v0 v! k
/ V1 o$ {3 v u9 u* X1 L 使用收藏
9 \8 z. p- S; @" kVARIABLE cursor REFCURSOR;" H6 s" C* u- g5 n$ D
DECLARE
* V7 [0 g' o& p4 o% T& n* R your_collection SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
& k1 }3 ]! f5 M o0 O) s0 h. R$ p9 xBEGIN
3 z4 d7 e# U, |& S+ g your_collection.EXTEND( 10000 );
$ A9 U" J+ I# h/ h% _- P FOR i IN 1 .. 10000 LOOP$ d, k) G2 K; G2 p
-- Populate the collection.
7 k! T; c, p' d& A" \. u your_collection(i) := DBMS_RANDOM.STRING( 'x', 20 );$ ^4 V9 Z" K6 J0 ~( K6 C
END LOOP;+ k2 V8 z3 }$ ]' G' k
OPEN :cursor FOR
6 i* V3 z6 r. i# X SELECT t.*. D& q: \- z4 F7 T2 p3 p1 K! _
FROM your_table t
% C4 l. h d+ @+ ]/ j INNER JOIN
5 X8 h% c" ]1 F8 f! M7 k TABLE( your_collection ) c% U, c7 V+ x% d6 _( Y8 A8 |
ON t.id = c.COLUMN_VALUE;
/ L" K3 k; O$ I( o a. ZEND;: p9 ~- ?- f( q# I
/4 Y* S! V* n& {+ L( l
PRINT cursor;
- Z2 p2 y C) f( ?+ v或通过java做同样的事情:- X/ u4 A& K2 Z2 P" p
import java.sql.Connection;3 [) P c; x6 P5 V" H
import java.sql.DriverManager;6 z8 @; \6 {+ ~4 Q' Z" Y) [3 i
import java.sql.PreparedStatement;9 t$ y( b _; f
import java.sql.ResultSet;1 u) x7 @1 r6 Z( G5 j
import java.sql.SQLException;0 a2 c6 m0 Z' ?' S: G
import oracle.jdbc.OraclePreparedStatement;/ j9 B8 ] E5 D" q: T5 C, o
import oracle.sql.ARRAY;+ X, W# ?/ D$ Z- Z" ^ {
import oracle.sql.ArrayDescriptor;
* ?' h0 y; i3 j8 ]6 J. U9 C/ Q7 h. Jpublic class TestDatabase2 {: p: Z6 N' U5 g
public static void main(String args[]){
- U3 k' p+ b" t; X6 o, n! { try{( F# R9 S9 V; C) |1 T- g
Class.forName("oracle.jdbc.OracleDriver");
7 ~$ n( g4 I0 n3 j/ {( g3 c; `+ N, S Connection con = DriverManager.getConnection("jdbcracle:thinlocalhost:1521:XE","username","password");& C1 C8 {/ _. _2 Q1 o! t
String[] ids = { "1", "2", "3" };
/ }" A" y+ E; v1 I ArrayDescriptor des = ArrayDescriptor.createDescriptor("SYS.ODCIVARCHAR2LIST", con);
% A- h, }1 `! V$ `& W% z PreparedStatement st = con.prepareStatement("SELECT t.* FROM your_table t INNER JOIN TABLE( :your_collection ) c ON t.id = c.COLUMN_VALUE");# |' X7 \# y5 G4 L, B" x
// Passing an array to the procedure -
+ b7 B8 a! p3 }; A: L& E. y ((OraclePreparedStatement) st).setARRAYAtName( "your_collection", new ARRAY( des, con, ids ) );0 G: p1 J5 L* H. z: j5 |% P4 E
ResultSet cursor = st.executeQuery();
- |% J& `9 w# v4 N. w/ a; a while ( cursor.next() )5 ^" I8 W1 `: S4 [( M, b
{) C5 ]' p; g( d J) c
int id = cursor.getInt(1);
$ I" \$ `! d0 k: k6 \' o7 J double column1 = cursor.getDouble(2);7 r8 n- `# U) ]+ f5 h4 v
double column2 = cursor.getDouble(3);& t0 D% r1 n% ~! F2 Z( b+ G
System.out.println( String.format( "Id: %5d", id ) );
& R" I) Z) A4 S% A- f System.out.println( String.format( " Column1: %s", column1 ) );9 L' B6 a0 Q6 o* t7 ]
System.out.println( String.format( " Column2: %s", column2 ) );
2 ]+ [9 s* |$ t }: v* O" i! H2 d6 q3 \# {
} catch(ClassNotFoundException | SQLException e) {
+ a1 P# q) O: E0 t System.out.println(e);, O' q/ h1 X6 s" F
}4 \6 o6 X. Q, I% H6 c7 r( |% A$ C
}
! T# d8 x7 h5 |# l$ p} |
|