Oracle Stored Procedure exercise Series 2 keywords execute dynamic SQL

Source: Internet
Author: User
    • How can I execute SQL statements in PLSQL stored procedures?

Use dynamic SQL Execution:Execute immediate

    • Table Information
 
 Select * FromEmp_temp;

    • Stored Procedure
 Create   Or   Replace   Procedure F_delete_rows (table_name In   Varchar2 , Condition In   Varchar2   Default  Null ) As  Where_clause  Varchar2 ( 100 ): =   '  Where  '   |  Condition; v_table  Varchar2 ( 30  ); V_ SQL  Varchar2 (100  );  /*  * Name: f_calculate_dataitem * purpose: dynamically Delete table data * imput: table_name condition for deleting the condition table to which the data will be deleted * Author: -- Cici * createdate: -- 2012, 12, 30 * updatedate: --*************************************** ********************  */  Begin    --  First make sure that the table actually exists; if not, raise an exception    Select O. Object_name      Into V_table  From  SYS. all_objects o  Where O. Object_name   =   Upper  (Table_name)  And O. object_type =   '  Table  '  ;  If Condition Is  Null   Then  Where_clause:  =   Null  ;  End   If  ;  If Table_name Is   Not   Null   Then  V_table:  =  Table_name; End   If  ;  --  Spelling the dynamic SQL statement to be executed V_ SQL: =   '  Delete from  '   | V_table |  Where_clause;  --  Execute the delete statement.    Execute  Immediate v_ SQL; Exception When No_data_found Then  Dbms_output.put_line (  '  Invalid table:  '   |  Table_name );  End ;

 

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.