/** * Deletes the name of the field passed in to all tables in the current database, and the record of the corresponding tagged value * MR_CHENPH (2012-1-12) * Invocation Way Select Deletetag (' Isdel ', 1) from Dual * to The above statement deletes records in all tables where the Isdel field value is 1 **/CREATE OR REPLACE FUNCTION deletetag (V_col in VARCHAR2, Del_tag in number) return VARCHAR2
IS--the variable v_expense VARCHAR2 (200) that the current function uses;
V_tablename VARCHAR2 (100);
V_tablenamecol VARCHAR2 (100);
--Gets all the tables in the current database CURSOR Table_loop is SELECT table_name from user_tables; --Gets all the fields in a table CURSOR Col_loop (v_tablename varchar2) is SELECT column_name from User_tab_columns t WHERE t.table_name=v
_tablename;
--Business PRAGMA autonomous_transaction;
BEGIN--Set return value 0 normal other for abnormal v_expense: = ' 0 ';
--Opens the first cursor open table_loop;
LOOP FETCH table_loop into V_tablename;
EXIT when Table_loop%notfound;
--Opens the second cursor open col_loop (v_tablename);
LOOP FETCH col_loop into V_tablenamecol;
EXIT when Col_loop%notfound; --To determine if the field name is consistent if (V_tablenamecol = UPPER (v_col)) THEN EXECUTE IMMEDIATE ' Delete From ' | | v_tablename| | ' WHERE ' | | v_col| | ' ='||
Del_tag;
End IF;
End LOOP;
Close Col_loop;
End LOOP;
Close Table_loop;
-Commits a transaction commit;
--Exception handling EXCEPTION when others THEN ROLLBACK; V_expense: = sqlcode| | ' ---'||
SQLERRM;
--Returns the result return v_expense;
End Deletetag;