我创建了用于选择的动态存储过程。我将通过传递参数,使用该通用查询执行多个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_BWhen 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 = ?
更多推荐
游标游标名称未处于准备状态
发布评论