Execute immediate statement

Source: Internet
Author: User
Syntax


Keyword and parameter description

Bind_argument

This can be an expression whose value is passed to the dynamic SQL statement or PL/SQL block, or it can be a variable that stores a value returned by the dynamic SQL statement or PL/SQL block.

Define_variable_name

This identifies a variable that stores a selected column value.

Dynamic_string

This is a string literal, variable, or expression that represents a SQL statement or PL/SQL block.

Into...

Used only for single-row queries, this clause specifies the variables or record into which column values are retrieved. for each value retrieved by the query, there must be a corresponding, type-compatible variable or field inINTOClause.

Record_name

This identifies a user-defined or%ROWTYPERecord that stores a selected row.

Returning...

Used only for DML statements that haveRETURNINGClause (withoutBULK COLLECTClause), this clause specifies the bind variables into which column values are returned. For each value returned by the DML statement, there must be a corresponding, type-compatible variable inRETURNING INTOClause.

Using...

This clause specifies a list of input and/or output bind arguments. If you do not specify a parameter mode, it defaultsIN.

Examples

The following PL/SQL block contains several examples of dynamic SQL:

DECLARE   sql_stmt    VARCHAR2(200);   plsql_block VARCHAR2(500);   emp_id      NUMBER(4) := 7566;   salary      NUMBER(7,2);   dept_id     NUMBER(2) := 50;   dept_name   VARCHAR2(14) := 'PERSONNEL';   location    VARCHAR2(13) := 'DALLAS';   emp_rec     emp%ROWTYPE;BEGIN   EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';   sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';   EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;   sql_stmt := 'SELECT * FROM emp WHERE empno = :id';   EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;   plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;';   EXECUTE IMMEDIATE plsql_block USING 7788, 500;   sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1      RETURNING sal INTO :2';   EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;   EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'      USING dept_id;   EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';END;

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.