Oracle's function to delete a record of a word Kewet in all tables ____oracle

Source: Internet
Author: User
 /** * 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;


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.