如何将select语句的IN子句中的参数作为具有多个值的参数传递？

2024-05-21 • 问答

我在UNIX中编写脚本的地方，我要在其中尝试实现以下内容

1）连接到数据库 2）运行选择查询，然后将结果提取到文件中进行验证

现在我写了以下

#!/bin/bash

file="./database.properties"

if [ -f "$file" ]
then
    echo "$file found."
 . $file

  echo "User Id       = " ${username}
  echo "user password = " ${password}
  echo "schema  = " ${schema}

  sqlplus -S ${username}/${password}@${schema}
  set feedback off trimspool on 
spool workflow_details.txt;
SELECT WORKFLOW_NAME,START_TIME,END_TIME,(END_TIME-START_TIME)*24*60 as TIME_TAKEN
 FROM schema1.table1
 WHERE WORKFLOW_NAME IN ('arguement1,arguement2,argument3,arguement4')
 AND WORKFLOW_RUN_ID IN (SELECT MAX(WORKFLOW_RUN_ID) FROM schema2.table3
 WHERE WORKFLOW_NAME IN ('arguement1'));
spool off;
exit;

else
    echo "$file not found."
fi

要求是iam在In子句中使用的值，即文件中存在（struggment1，argument2 ....等），并且应该修改脚本，以便通过逗号将参数提取并放入In子句中分离。参数的数量是动态的。如何修改代码。

简而言之，我需要在运行时从文件中获取有关IN子句的论点，以获取论点的详细信息。该文件看起来像只有一列包含争论的内容。

如我的评论中所述，您需要使用Collection来满足您的要求。请参见下面的演示和内联说明。

在PLSQL

中

-- Declare a Nested table of type Number. You can declare it of type of your argument1,argument2..

    Create or replace type var is table of number;
    /

    DECLARE
        v_var   var := var ();
        v_num number;
    BEGIN
        --Fetching rows to collection
        SELECT * BULK COLLECT INTO
            v_var
        FROM (
            SELECT 1 FROM dual
            UNION ALL
            SELECT 2 FROM dual
        );

        --Printing values of collection 
        FOR rec IN 1..v_var.count LOOP
            dbms_output.put_line(v_var(rec) );
        END LOOP;

        --Using in Where clause.       
        Select count(1)
        into v_num
        from dual where 1 Member of v_var; --<-- this is how you pass the collection of number in `IN` clause.

        dbms_output.put_line(v_num );
    END;

根据您的情况： UNIX脚本

#!/bin/bash

#read from file and prepare the "in clause" --<--Put a loop to read through the file
in_clause=argument1,argument2  #--Prepare your `in_clause`


file="./database.properties"

if [ -f "$file" ]
then
    echo "$file found."
 . $file

  echo "User Id       = " ${userName}
  echo "user password = " ${password}
  echo "schema  = " ${schema}

  sqlplus -S ${userName}/${password}@${schema}
  set feedback off trimspool on 
  spool workflow_details.txt;

   SELECT workflow_name,start_time,end_time,( end_time - start_time ) * 24 * 60 AS time_taken
    FROM schema1.table1
    WHERE workflow_name IN ($in_clause )      #<--Use in clause
          AND   workflow_run_id IN (SELECT MAX(workflow_run_id) FROM schema2.table3 WHERE workflow_name IN ( 'arguement1' )
    );

spool off;
exit; 
else 
echo "$file not found." 
fi

PS：未经测试

如何将select语句的IN子句中的参数作为具有多个值的参数传递？

qilixiang41 回答：如何将select语句的IN子句中的参数作为具有多个值的参数传递？

大家都在问