游标游标名称未处于准备状态 [英] THE CURSOR CURSOR NAME IS NOT IN A PREPARED STATE

问题描述

我创建了用于选择的动态存储过程。我将通过传递参数，使用该通用查询执行多个select语句。

  DECLARE DESC_CSR游标，并保留V_SQL； 
 
 SET V_SELECT = 
‘SELECT’|| SELECT_FIELDS || 
’FROM’|| TABLE_NAME || 
’WHERE’|| WHERE_CLAUSE || 
’WITH UR’； 
 
从V_SELECT中准备V_SQL; 
 OPEN DESC_CSR; 
  

我将通过以下值。

  SELECT_FIELDS = B.COLUMN_A INTO HOST_VAR_A 
 TABLE_NAME = TABLE_A内部联接TABLE_B B 
 ON A.ROW_ID = B.ROW_ID 
 WHERE_CLAUSE = A.COLUMN_B = HOST_VAR_B 
  

执行此操作时，我得到光标DESC_CSR不在准备好的状态。 p>

如果我做错了任何人，请帮助我准备或纠正我。

解决方案

我使用了以下代码，并且工作正常。我可以使用Dynamic运行多个选择。

  DECLARE DESC_CSR CURSOR HOLD FOR V_SQL; 
 
 SET V_SELECT = 
‘SELECT’|| SELECT_FIELDS || 
’FROM’|| TABLE_NAME || 
’WHERE’|| WHERE_CLAUSE || 
’WITH UR’； 
 
 
从V_SELECT中准备V_SQL; 
 
使用HOST_VAR_B打开DESC_CSR； 
 
读取DESC_CSR到HOST_VAR_A; 
  

我通过了如下的parm。使用where子句HOST_VAR_B作为参数标记？并在OPEN处分配字段。

  SELECT_FIELDS = B.COLUMN_A INTO HOST_VAR_A 
 TABLE_NAME = TABLE_A内部联接TABLE_B B 
 ON A.ROW_ID = B.ROW_ID 
 WHERE_CLAUSE = A.COLUMN_B =？ 
  

I created Dynamic Stored procedure for select. I'm going to use this generic query for execute multiple select statement, by passing the parameter.

DECLARE DESC_CSR CURSOR WITH HOLD FOR V_SQL; 

SET V_SELECT =                               
'SELECT ' || SELECT_FIELDS ||                
' FROM '  || TABLE_NAME    ||                
' WHERE ' || WHERE_CLAUSE  ||                
' WITH UR';                                  

PREPARE V_SQL FROM V_SELECT;  
OPEN DESC_CSR;   

I will pass below values.

SELECT_FIELDS = B.COLUMN_A  INTO HOST_VAR_A
TABLE_NAME =  TABLE_A A   INNER JOIN TABLE_B B      
              ON A.ROW_ID = B.ROW_ID
WHERE_CLAUSE = A.COLUMN_B = HOST_VAR_B

When I did this I got "THE CURSOR DESC_CSR IS NOT IN A PREPARED STATE".

Could anyone please help me on how to PREPARE or correct me, if I'm doing it wrong.

解决方案

I used below code and it working fine. I was able to run multiple select's using Dynamic.

DECLARE DESC_CSR CURSOR WITH HOLD FOR V_SQL;  

  SET V_SELECT =                              
  'SELECT ' || SELECT_FIELDS ||               
  ' FROM '  || TABLE_NAME    ||               
  ' WHERE ' || WHERE_CLAUSE  ||               
  ' WITH UR';                                 


  PREPARE V_SQL FROM V_SELECT;  

  OPEN DESC_CSR USING HOST_VAR_B;

  FETCH DESC_CSR INTO HOST_VAR_A;

I pass parm as below. using the where clause HOST_VAR_B as Parameter marker "?" and assigning the field at OPEN.

SELECT_FIELDS = B.COLUMN_A  INTO HOST_VAR_A
TABLE_NAME =  TABLE_A A   INNER JOIN TABLE_B B      
              ON A.ROW_ID = B.ROW_ID
WHERE_CLAUSE = A.COLUMN_B = ?

这篇关于游标游标名称未处于准备状态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！