- 论坛徽章:
- 0
|
存储过程中 动态sql的 问题
顶一下
我的代码, 建立成功, 运行时报错EL_CUR 未定义
- DROP PROCEDURE sp_DelTable@
- CREATE PROCEDURE sp_DelTable(IN as_TableName VARCHAR(128) ,
- IN as_Condition VARCHAR(1024) ,
- IN abi_CommitCount BIGINT,
- OUT SV_RTNTEXT VARCHAR(128))
- LANGUAGE SQL
- BEGIN
- DECLARE sm_End SMALLINT DEFAULT 0;
- DECLARE retcode INTEGER DEFAULT 0;
- DECLARE SQLCODE INTEGER DEFAULT 0;
- DECLARE ls_TableName VARCHAR(128);
- DECLARE ls_IntactStmt VARCHAR(1308);
- DECLARE ls_DelStmt VARCHAR(128);
- DECLARE ls_Stmt VARCHAR(128);
- DECLARE CC VARCHAR(128);
- DECLARE lbi_CommitCount BIGINT;
- DECLARE li_tmp INTEGER;
- DECLARE DEL_CUR Cursor For CC;
-
- DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
- BEGIN
- SET sm_End = 1;
- SET retcode = SQLCODE;
- SET SV_RTNTEXT = 'retcode = '||char(retcode) ;
- RESIGNAL; --给调用者发出原错误信息
- END;
- --DECLARE DEL_CUR Cursor For CC;
- SET ls_IntactStmt = 'SELECT 1 FROM ' || as_TableName || ' WHERE ' || as_Condition ;
- PREPARE CC FROM ls_IntactStmt;
- OPEN DEL_CUR;
-
- SET ls_DelStmt = 'DELETE FROM tb_lane WHERE CURRENT OF DEL_CUR';
- --PREPARE ls_Stmt from ls_DelStmt;
-
- -- del all data loop.
- Del_Loop:
- Loop
- -- when reach the commit count then commit/
- SET lbi_CommitCount = 0 ;
-
- Commit_Loop:
- Loop
- Fetch DEL_CUR INTO li_tmp ;
-
- If (sm_End = 1) Then
- -- has del all data , go to end
- SET sm_End = 0 ;
- Leave Del_Loop ;
- End If ;
- EXECUTE immediate ls_DelStmt ;
-
- Set lbi_CommitCount = lbi_CommitCount + 1 ;
- If (lbi_CommitCount = abi_CommitCount) Then
- -- has reached the commit count
- Leave Commit_Loop;
- End If ;
- End Loop Commit_Loop;
- COMMIT;
- End Loop Del_Loop;
- COMMIT;
-
- CLOSE DEL_CUR;
- set SV_RTNTEXT=ls_DelStmt;
- END@
复制代码 |
|