execute

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

execute [2015/09/23 20:27]
execute [2015/09/23 20:27] (current)
Line 1: Line 1:
 +====== EXECUTE Statement ======
  
 +EXECUTE (EXEC or EXECUTE IMMEDIATE) statement executes a dynamic SQL statement and can return the scalar result to local variables. ​
 +
 +You can also use this statement to call a stored procedure.
 +
 +**Syntax:**
 +
 +<code language=sql>​
 +EXEC | EXECUTE | EXECUTE IMMEDIATE dynamic_sql_string [INTO var1, var2, ...];
 +|
 +EXEC | EXECUTE proc_name [parm1 = val1, ... ]
 +</​code>​
 +
 +**Parameters:​**
 +
 +| **Parameter** | **Type** | **Value** | **Description** |
 +| dynamic_sql_string | VARCHAR | Variable or expression | Dynamic SQL to execute |
 +| INTO var1, var2, ... | Any | Variable | Variables to assign, optional |
 +
 +**Notes**:
 +
 +  * If the query returns more than 1 row and the INTO clause is specified, only the columns from the first row are used in assignment
 +  * If the statement returns 1 or more rows and the INTO clause is not specified, the result set data is sent to the standard output
 +  * EXEC, EXECUTE are EXECUTE IMMEDIATE synonyms
 +
 +**Example:​**
 +
 +Return the result into a variable:
 +
 +<code language=sql>​
 +DECLARE cnt INT;
 +EXECUTE '​SELECT COUNT(*) FROM db.orders'​ INTO cnt;
 +</​code>​
 +
 +Execute a DML statement:
 +
 +<code language=sql>​
 +DECLARE tabname VARCHAR(100) DEFAULT '​tab1';​
 +EXECUTE IMMEDIATE '​CREATE TABLE ' || tabname || ' (c1 INT)';
 +</​code>​
 +
 +Print the results to standard output:
 +
 +<code language=sql>​
 +EXEC '​SELECT ''​A'',​ ''​B''​ FROM dual';
 +</​code>​
 +
 +Call a stored procedure:
 +
 +<code language=sql>​
 +ALTER PROCEDURE spOrders
 +  @lim INT
 +AS
 +  DECLARE @cnt INT = 0
 +  SELECT @cnt = COUNT(*) from src LIMIT @lim
 +  IF @cnt > 0
 +    SELECT * FROM src
 +GO
 +
 +EXEC spOrders @lim = 3
 +</​code>​
 +
 +**Compatibility:​** Oracle, IBM DB2 and Microsoft SQL Server.
 +
 +**Version:​** ​
 +  * HPL/SQL 0.3.11 - Execute a stored procedure
 +  * PL/HQL 0.01