删除物料编码sql

DECLARE
  i NUMBER;
BEGIN
  --创建备份删除数据的临时表
  SELECT COUNT(*) INTO i FROM USER_TABLES WHERE TABLE_NAME = 'QPO_JC_ITEM_BACKUP_TMP';
  IF i>0 THEN
    EXECUTE IMMEDIATE 'DROP TABLE QPO_JC_ITEM_BACKUP_TMP';
    EXECUTE IMMEDIATE 'CREATE TABLE QPO_JC_ITEM_BACKUP_TMP AS SELECT * FROM QPO_JC_ITEM_TMP WHERE 1=2';
    COMMIT;
  ELSE
    EXECUTE IMMEDIATE 'CREATE TABLE QPO_JC_ITEM_BACKUP_TMP AS SELECT * FROM QPO_JC_ITEM_TMP WHERE 1=2';
    COMMIT;
  END IF;
  --备份删除数据
  INSERT INTO QPO_JC_ITEM_BACKUP_TMP  SELECT * FROM QPO_JC_ITEM_TMP WHERE item_num LIKE '12%' OR item_num LIKE '11%';
  COMMIT; 
  --删除数据
  DELETE FROM QPO_JC_ITEM_TMP WHERE item_num LIKE '12%' OR item_num LIKE '11%';
  COMMIT; 
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('出错了');
    ROLLBACK;

END;

几点总结：

1、DDL语句指的是：CREATE DATABASE，CREATE TABLE，ALTER TABLE ，DROP TABLE，CREATE VIEW，ALTER VIEW ，DROP VIEW 等

2、PLSQL不能直接写DDL语句，必须加EXECUTE IMMEDIATE

3、包或存储过程编译后并不执行，而是调用后才执行，要直接执行用匿名块

4、包里不能用declare，匿名块可以