oracle 在存储过程中定义动态sql

--表结构
create table ITEM_PROP
(
  PID           INTEGER,
  NAME          VARCHAR2(20),
  IS_KEY_PROP   INTEGER,
  IS_SALE_PROP  INTEGER,
  IS_COLOR_PROP INTEGER,
  PARENT_PID    INTEGER,
  PARENT_VID    VARCHAR2(20),
  PROP_VALUES   PROPVALUES
)

--无参形式

CREATE OR REPLACE PROCEDURE pro_drop IS
  sql_str VARCHAR2(1000) := '';
BEGIN
  FOR v_cat IN (SELECT pid FROM item_prop WHERE pid = 1627207) LOOP
    sql_str := 'select * from item_prop where pid =' || v_cat.pid;
    dbms_output.put_line(v_cat.pid);
    dbms_output.put_line(sql_str);
    EXECUTE IMMEDIATE sql_str;
  END LOOP;
END;

--有参形式
CREATE OR REPLACE PROCEDURE pro_drop
(
   id in int,
   name in varchar2
) IS
  sql_str VARCHAR2(1000) := '';
BEGIN
  FOR v_cat IN (SELECT pid FROM item_prop WHERE pid = id) LOOP
    sql_str := 'update item_prop set name = name where pid =' || v_cat.pid;
    dbms_output.put_line(v_cat.pid);
    dbms_output.put_line(sql_str);
    EXECUTE IMMEDIATE sql_str;
  END LOOP;
  commit;
END;