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

1、表结构

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
)

 

2、无参形式

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;

 

3、有参形式

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;