如何加载大量字符串与oracle数据库匹配?

2021-12-01 00:00:00 sql oracle oracle-sqldeveloper

我目前正在学习 PL/SQL，所以我还是一个新手.假设您有一个生产数据库，您使用 Oracle SQL Developer 连接到该数据库.您只有对该数据库的读取权限.因此您不能创建或编辑任何表.

I am currently learning PL/SQL so i am still a newbie. Assume that you have a production database, which you connect to using Oracle SQL developer. You have ONLY READ privilges to that databases. Therefore you cannot create or edit any tables.

我的问题是，如果我有一个很大的 ID 列表，我必须将这些 ID 与该数据库中的一个表连接起来，我该怎么做?

My question is, if i have a big list of IDs, which i have to join with a table in that database, how can i do that?

显然，我可以将 ID 加载到临时表上，然后进行连接，但这会非常乏味，因为我只有 READ 权限.硬编码 ID 也不是一种选择，因为列表太大.

Obviously, I can load the IDs onto a temporary table and then do a join, but that would be really tedious as i have only READ privileges. Hardcoding the IDs is not an option also, because the list is too big.

还要注意的是，我知道临时表的概念.但不幸的是，我也没有创建这些的权限.

And also note that, i know the concept of TEMPORARY tables. But unfortunately, i also don't have privileges to create those.

SQL 开发人员是否有任何解决方案可以加载 ID 列表以与数据库中的表匹配?

Is there any solution in SQL developer where i can load the list of IDs, to match with the table in the database?

推荐答案

使用集合

VARIABLE cursor REFCURSOR;

DECLARE
  your_collection SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
BEGIN
  your_collection.EXTEND( 10000 );

  FOR i IN 1 .. 10000 LOOP
    -- Populate the collection.
    your_collection(i) := DBMS_RANDOM.STRING( 'x', 20 );
  END LOOP;

  OPEN :cursor FOR
  SELECT t.*
  FROM   your_table t
         INNER JOIN
         TABLE( your_collection ) c
         ON t.id = c.COLUMN_VALUE;
END;
/

PRINT cursor;

或者通过java做同样的事情:

Or doing the same thing via java:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OraclePreparedStatement;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

public class TestDatabase2 {
    public static void main(String args[]){
        try{
            Class.forName("oracle.jdbc.OracleDriver");

            Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","username","password");

            String[] ids = { "1", "2", "3" };

            ArrayDescriptor des = ArrayDescriptor.createDescriptor("SYS.ODCIVARCHAR2LIST", con);

            PreparedStatement st = con.prepareStatement("SELECT t.* FROM your_table t INNER JOIN TABLE( :your_collection ) c ON t.id = c.COLUMN_VALUE");

            // Passing an array to the procedure - 
            ((OraclePreparedStatement) st).setARRAYAtName( "your_collection", new ARRAY( des, con, ids ) );
            ResultSet cursor = st.executeQuery();

            while ( cursor.next() )
            {
                int id = cursor.getInt(1);
                double column1 = cursor.getDouble(2);
                double column2 = cursor.getDouble(3);

                System.out.println( String.format( "Id: %5d", id ) );
                System.out.println( String.format( "  Column1: %s", column1 ) );
                System.out.println( String.format( "  Column2: %s", column2 ) );
            }
        } catch(ClassNotFoundException | SQLException e) {
            System.out.println(e);
        }
    }
}

相关文章