因为有delete语句,如果写函数,是不能直接调用的存储过程先用个游标 select table_name,column_name from all_tab_columns where column_name='eccode'然后打开游标,拼个delete语句 再执行动态sql
[code=SQL]CREATE OR REPLACE PROCEDURE sp_del_eccode (in_data IN VARCHAR2) IS v_table VARCHAR2 (100); delect_sql VARCHAR2 (1000); CURSOR c_tbl_names IS SELECT table_name FROM user_tab_columns WHERE column_name = 'eccode'; BEGIN OPEN c_tbl_names; LOOP FETCH c_tbl_names INTO v_table, v_column; EXIT WHEN c_tbl_names%NOTFOUND; delete_sql := 'delete ' || v_table || ' where eccode=' || in_data; EXECUTE IMMEDIATE delete_sql; END LOOP; COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; WHEN OTHERS THEN RAISE; END sp_del_eccode;[code]
没有测试过,可能有些地方需要修改下,你自己去测试下CREATE OR REPLACE PROCEDURE sp_del_eccode (in_data IN VARCHAR2) IS v_table VARCHAR2 (100); delect_sql VARCHAR2 (1000); CURSOR c_tbl_names IS SELECT table_name FROM user_tab_columns WHERE column_name = 'eccode'; BEGIN OPEN c_tbl_names; LOOP FETCH c_tbl_names INTO v_table, v_column; EXIT WHEN c_tbl_names%NOTFOUND; delete_sql := 'delete ' ¦ ¦ v_table ¦ ¦ ' where eccode=' ¦ ¦ in_data; EXECUTE IMMEDIATE delete_sql; END LOOP; COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; WHEN OTHERS THEN RAISE; END sp_del_eccode;
上面的有点小错误CREATE OR REPLACE PROCEDURE sp_del_eccode (in_data IN VARCHAR2) IS v_table VARCHAR2 (100); delect_sql VARCHAR2 (1000); CURSOR c_tbl_names IS SELECT table_name FROM user_tab_columns WHERE column_name = 'eccode'; BEGIN OPEN c_tbl_names; LOOP FETCH c_tbl_names INTO v_table; EXIT WHEN c_tbl_names%NOTFOUND; delete_sql := 'delete ' ¦ ¦ v_table ¦ ¦ ' where eccode=' ¦ ¦ in_data; EXECUTE IMMEDIATE delete_sql; END LOOP; COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; WHEN OTHERS THEN RAISE; END sp_del_eccode;
select table_name,column_name from all_tab_columns
where column_name='eccode'然后打开游标,拼个delete语句
再执行动态sql
IS
v_table VARCHAR2 (100);
delect_sql VARCHAR2 (1000); CURSOR c_tbl_names
IS
SELECT table_name
FROM user_tab_columns
WHERE column_name = 'eccode';
BEGIN
OPEN c_tbl_names; LOOP
FETCH c_tbl_names
INTO v_table, v_column; EXIT WHEN c_tbl_names%NOTFOUND;
delete_sql := 'delete ' || v_table || ' where eccode=' || in_data; EXECUTE IMMEDIATE delete_sql;
END LOOP; COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
RAISE;
END sp_del_eccode;[code]
IS
v_table VARCHAR2 (100);
delect_sql VARCHAR2 (1000); CURSOR c_tbl_names
IS
SELECT table_name
FROM user_tab_columns
WHERE column_name = 'eccode';
BEGIN
OPEN c_tbl_names; LOOP
FETCH c_tbl_names
INTO v_table, v_column; EXIT WHEN c_tbl_names%NOTFOUND;
delete_sql := 'delete ' ¦ ¦ v_table ¦ ¦ ' where eccode=' ¦ ¦ in_data; EXECUTE IMMEDIATE delete_sql;
END LOOP; COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
RAISE;
END sp_del_eccode;
IS
v_table VARCHAR2 (100);
delect_sql VARCHAR2 (1000); CURSOR c_tbl_names
IS
SELECT table_name
FROM user_tab_columns
WHERE column_name = 'eccode';
BEGIN
OPEN c_tbl_names; LOOP
FETCH c_tbl_names
INTO v_table; EXIT WHEN c_tbl_names%NOTFOUND;
delete_sql := 'delete ' ¦ ¦ v_table ¦ ¦ ' where eccode=' ¦ ¦ in_data; EXECUTE IMMEDIATE delete_sql;
END LOOP; COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
RAISE;
END sp_del_eccode;