游标游标名称未处于准备状态

本文介绍了游标游标名称未处于准备状态的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我创建了用于选择的动态存储过程。我将通过传递参数，使用该通用查询执行多个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 = ?