Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2014
    Posts
    4

    Answered: Cursor in db2 linux 9.7

    Hi people,

    I can't get my cursor to compile
    It is for db2 9.7 under Linux
    In the end I want to drop all table named like '%_QC'

    Code:
    CREATE PROCEDURE FIND_SALARY()
            RESULT SETS 1
            LANGUAGE SQL
    BEGIN
            DECLARE STMT VARCHAR(200);
            DECLARE T_NAME CHAR(32);
            DECLARE C1 CURSOR FOR
            DECLARE EOF INT DEFAULT 0;
    
            SELECT T_NAME
            FROM syscat.tables
            WHERE owner = 'SC4BAT' AND T_NAME LIKE  '%_QC%';
    
            DECLARE CONTINUE HANDLER FOR NOT FOUND
                    SET EOF = 1;
    
            -- SET STMT = 'DROP TABLE ?';
            SET STMT = 'SELECT count(*) FROM ?';
            PREPARE S1 FROM STMT;
            OPEN C1;
            WHILE EOF = 0 DO
                    FETCH FROM C1 INTO T_NAME;
                    EXECUTE S1 USING T_NAME;
            END WHILE;
            CLOSE C1;
    END
    Do I need to create a procedure for this?

    Thanks

  2. Best Answer
    Posted by mark.b

    "Hi,

    I hope you're trying to compile this procedure and not a cursor
    Too many errors.
    Your can't use a parameter marker for the names of the objects like table or column names.
    Try this.
    Code:
    BEGIN
            DECLARE T_NAME VARCHAR(128);
            DECLARE EOF INT DEFAULT 0;
    
            DECLARE C1 CURSOR FOR
            SELECT '"'||TABSCHEMA||'"."'||TABNAME||'"'
            FROM syscat.tables
            WHERE TABSCHEMA = 'SC4BAT' AND TABNAME LIKE  '%_QC%';
    
            DECLARE CONTINUE HANDLER FOR NOT FOUND
                    SET EOF = 1;
    
            OPEN C1;
            FETCH FROM C1 INTO T_NAME;
            WHILE EOF = 0 DO
              EXECUTE IMMEDIATE 'DROP TABLE '||T_NAME;
              FETCH FROM C1 INTO T_NAME;
            END WHILE;
            CLOSE C1;
    END@
    BTW:
    I hope you know the special meaning of the underscore symbol in the like predicate..."


  3. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    I hope you're trying to compile this procedure and not a cursor
    Too many errors.
    Your can't use a parameter marker for the names of the objects like table or column names.
    Try this.
    Code:
    BEGIN
            DECLARE T_NAME VARCHAR(128);
            DECLARE EOF INT DEFAULT 0;
    
            DECLARE C1 CURSOR FOR
            SELECT '"'||TABSCHEMA||'"."'||TABNAME||'"'
            FROM syscat.tables
            WHERE TABSCHEMA = 'SC4BAT' AND TABNAME LIKE  '%_QC%';
    
            DECLARE CONTINUE HANDLER FOR NOT FOUND
                    SET EOF = 1;
    
            OPEN C1;
            FETCH FROM C1 INTO T_NAME;
            WHILE EOF = 0 DO
              EXECUTE IMMEDIATE 'DROP TABLE '||T_NAME;
              FETCH FROM C1 INTO T_NAME;
            END WHILE;
            CLOSE C1;
    END@
    BTW:
    I hope you know the special meaning of the underscore symbol in the like predicate...
    Regards,
    Mark.

  4. #3
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Why use a stored procedure if all you want to do is " I want to drop all table named like '%_QC'".

    Why don't you just use the command-line: one SQL script to generate a second SQL script, then execute the second SQL script that contains the drop table statements?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •