Results 1 to 2 of 2

Thread: sql procedure

  1. #1
    Join Date
    Jul 2003
    Location
    porto - Portugal
    Posts
    74

    Unanswered: sql procedure

    Hi,
    i have to delete 20 000 rows of one table where str_id= 75 and yr_wk_id= 200128...can i do like that?
    And how can i do this if i want to delete rows for N yr_wk_id?

    Thanks!


    CREATE PROCEDURE RMLEITE.popularlojas ( )
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    DECLARE v_cont INTEGER DEFAULT 0;
    DECLARE v_CO_ID SMALLINT ;
    DECLARE v_STR_ID SMALLINT ;
    DECLARE v_YR_WK_ID INTEGER;
    DECLARE v_SKU_ID INTEGER ;
    DECLARE v_TS_VAL DECIMAL(11,2) ;
    DECLARE v_TS_QTY INTEGER ;
    DECLARE v_ROS_SLS_QTY INTEGER DEFAULT 0;
    DECLARE v_NUM_SKU_STRS INTEGER DEFAULT 0;
    DECLARE v_TPOS_MD_VAL DECIMAL(11,2) ;
    DECLARE v_CLR_MD_VAL DECIMAL(11,2) ;
    DECLARE v_TS_VAL_EUR DECIMAL(11,2) ;
    DECLARE v_TPOS_MD_VAL_EUR DECIMAL(11,2) ;
    DECLARE v_CLR_MD_VAL_EUR DECIMAL(11,2) ;
    DECLARE v_BASE_CCY CHAR(3);
    DECLARE at_end SMALLINT DEFAULT 0;
    DECLARE not_found CONDITION FOR SQLSTATE '02000';

    -- Exception Handlers
    DECLARE CONTINUE HANDLER FOR not_found
    SET at_end = 1;

    --create table basereft.sls_tmp as select * from basereft.sls where 1=0

    DECLARE c_DEL CURSOR FOR
    SELECT
    SLS.CO_ID,
    SLS.STR_ID,
    SLS.YR_WK_ID,
    SLS.SKU_ID,
    SLS.TS_VAL,
    SLS.TS_QTY,
    SLS.ROS_SLS_QTY,
    SLS.NUM_SKU_STRS,
    SLS.TPOS_MD_VAL,
    SLS.CLR_MD_VAL,
    SLS.TS_VAL_EUR ,
    SLS.TPOS_MD_VAL_EUR,
    SLS.CLR_MD_VAL_EUR,
    SLS.BASE_CCY
    FROM BASEDETT.SLS SLS
    WHERE SLS.str_id = 75 AND
    SLS.yr_wk_id = 200128
    FOR READ ONLY;

    OPEN c_DEL;
    FETCH c_DEL INTO v_CO_ID ,
    v_STR_ID ,
    v_YR_WK_ID ,
    v_SKU_ID ,
    v_TS_VAL ,
    v_TS_QTY ,
    v_ROS_SLS_QTY ,
    v_NUM_SKU_STRS ,
    v_TPOS_MD_VAL ,
    v_CLR_MD_VAL ,
    v_TS_VAL_EUR ,
    v_TPOS_MD_VAL_EUR ,
    v_CLR_MD_VAL_EUR ,
    v_BASE_CCY;
    WHILE at_end = 0 DO
    SET v_cont = v_cont+1;
    if v_cont<20000
    then
    delete from basereft.sls
    COMMIT;
    set v_cont=0;
    END IF;
    END WHILE;
    FETCH c_DEL INTO v_CO_ID ,
    v_STR_ID ,
    v_YR_WK_ID ,
    v_SKU_ID ,
    v_TS_VAL ,
    v_TS_QTY ,
    v_ROS_SLS_QTY ,
    v_NUM_SKU_STRS ,
    v_TPOS_MD_VAL ,
    v_CLR_MD_VAL ,
    v_TS_VAL_EUR ,
    v_TPOS_MD_VAL_EUR ,
    v_CLR_MD_VAL_EUR ,
    v_BASE_CCY;
    close c_DEL;

    END P1

  2. #2
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: sql procedure

    You can DELETE FROM .... WHERE OF CURRENT OF (cursor -- in your code that's c_DEL) in your loop.

    As for N yr_wk_id, you can specify an input parameter to the procedure then in the SELECT statement specify in the WHERE SLS.yr_wk_id = input parameter variable.


    Originally posted by rmleite
    Hi,
    i have to delete 20 000 rows of one table where str_id= 75 and yr_wk_id= 200128...can i do like that?
    And how can i do this if i want to delete rows for N yr_wk_id?

    Thanks!


    CREATE PROCEDURE RMLEITE.popularlojas ( )
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    DECLARE v_cont INTEGER DEFAULT 0;
    DECLARE v_CO_ID SMALLINT ;
    DECLARE v_STR_ID SMALLINT ;
    DECLARE v_YR_WK_ID INTEGER;
    DECLARE v_SKU_ID INTEGER ;
    DECLARE v_TS_VAL DECIMAL(11,2) ;
    DECLARE v_TS_QTY INTEGER ;
    DECLARE v_ROS_SLS_QTY INTEGER DEFAULT 0;
    DECLARE v_NUM_SKU_STRS INTEGER DEFAULT 0;
    DECLARE v_TPOS_MD_VAL DECIMAL(11,2) ;
    DECLARE v_CLR_MD_VAL DECIMAL(11,2) ;
    DECLARE v_TS_VAL_EUR DECIMAL(11,2) ;
    DECLARE v_TPOS_MD_VAL_EUR DECIMAL(11,2) ;
    DECLARE v_CLR_MD_VAL_EUR DECIMAL(11,2) ;
    DECLARE v_BASE_CCY CHAR(3);
    DECLARE at_end SMALLINT DEFAULT 0;
    DECLARE not_found CONDITION FOR SQLSTATE '02000';

    -- Exception Handlers
    DECLARE CONTINUE HANDLER FOR not_found
    SET at_end = 1;

    --create table basereft.sls_tmp as select * from basereft.sls where 1=0

    DECLARE c_DEL CURSOR FOR
    SELECT
    SLS.CO_ID,
    SLS.STR_ID,
    SLS.YR_WK_ID,
    SLS.SKU_ID,
    SLS.TS_VAL,
    SLS.TS_QTY,
    SLS.ROS_SLS_QTY,
    SLS.NUM_SKU_STRS,
    SLS.TPOS_MD_VAL,
    SLS.CLR_MD_VAL,
    SLS.TS_VAL_EUR ,
    SLS.TPOS_MD_VAL_EUR,
    SLS.CLR_MD_VAL_EUR,
    SLS.BASE_CCY
    FROM BASEDETT.SLS SLS
    WHERE SLS.str_id = 75 AND
    SLS.yr_wk_id = 200128
    FOR READ ONLY;

    OPEN c_DEL;
    FETCH c_DEL INTO v_CO_ID ,
    v_STR_ID ,
    v_YR_WK_ID ,
    v_SKU_ID ,
    v_TS_VAL ,
    v_TS_QTY ,
    v_ROS_SLS_QTY ,
    v_NUM_SKU_STRS ,
    v_TPOS_MD_VAL ,
    v_CLR_MD_VAL ,
    v_TS_VAL_EUR ,
    v_TPOS_MD_VAL_EUR ,
    v_CLR_MD_VAL_EUR ,
    v_BASE_CCY;
    WHILE at_end = 0 DO
    SET v_cont = v_cont+1;
    if v_cont<20000
    then
    delete from basereft.sls
    COMMIT;
    set v_cont=0;
    END IF;
    END WHILE;
    FETCH c_DEL INTO v_CO_ID ,
    v_STR_ID ,
    v_YR_WK_ID ,
    v_SKU_ID ,
    v_TS_VAL ,
    v_TS_QTY ,
    v_ROS_SLS_QTY ,
    v_NUM_SKU_STRS ,
    v_TPOS_MD_VAL ,
    v_CLR_MD_VAL ,
    v_TS_VAL_EUR ,
    v_TPOS_MD_VAL_EUR ,
    v_CLR_MD_VAL_EUR ,
    v_BASE_CCY;
    close c_DEL;

    END P1

Posting Permissions

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