开发者

Help with a DELETE function and dropping columns

I am writing a delete function for my jsp page, and I am running into some trouble because I want the user to be able to delete only certain columns from a table, but leave the other columns. My jsp page has 4 tables, and the user can delete from any one of those (note the "if statements")....I know what I have here is incorrect, but it is my attempt. Any help is appreciated, also if it would be better to handle some of this in the servlet, please let me know as well.

BEGIN

-- If a deletion is checked on the Lost/Found table.

  IF pLfEdit IS NOT NULL
THEN

  ALTER TABLE   BSSS
    DROP COLUMN LOST_FOUND
    DROP COLUMN LOST_LOC
  WHERE   EDIT_ID = pLfEditDel;
ELSE
  RAISE NO_DATA_FOUND;
END IF;

-- If a deletion is checked on the Returned Check 开发者_如何转开发table.
  IF pRcEdit IS NOT NULL
THEN

  ALTER TABLE   BSSS
    DROP COLUMN RETURN_CHECK
    DROP COLUMN RETURN_LOC
  WHERE   EDIT_ID = pRcEditDel;
ELSE
  RAISE NO_DATA_FOUND;
END IF;

-- If a deletion is checked on the Sponsor table.
  IF pSpnEdit IS NOT NULL
THEN

  ALTER TABLE   BSSS
      DROP COLUMN SPONSOR
      DROP COLUMN SPON_LOC
  WHERE   EDIT_ID = pSpnEditDel;
ELSE
  RAISE NO_DATA_FOUND;
END IF;

-- If a deletion is checked on the Comments table.
  IF pComEdit IS NOT NULL
THEN

  ALTER TABLE   BSSS
      DROP COLUMN COMMENTS
  WHERE   EDIT_ID = pComEditDel;
  ELSE
  RAISE NO_DATA_FOUND;
  END IF;

COMMIT;
RETURN 0;
EXCEPTION
WHEN OTHERS
THEN
  ROLLBACK;
  RETURN SQLCODE;
END DEL_BS_INFO;


You are mixing the concepts of DML and DDL. DDL (e.g. ALTER TABLE) is for system change and maintenance, usually done by a DBA; DML (e.g. SELECT, INSERT, DELETE, COMMIT) is for transactions which are done by the application.

I think you don't want to DROP the columns from the table - but you want to clear the values from individual columns in some rows. What you want to do is set them to NULL, I think. For example:

IF pLfEdit IS NOT NULL
THEN
   UPDATE BSSS
   SET LOST_FOUND = NULL,
       LOST_LOC = NULL
   WHERE   EDIT_ID = pLfEditDel;
ELSE
   RAISE NO_DATA_FOUND;
END IF;
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜