EXECUTE IMMEDIATE statement: It is used to execute a statement in dynamic SQL immediately. It uses only one host variable. Here are the steps which are performed in EXECUTE IMMEDIATE statement:
1) A SQL statement which does not retrieve data is generated in a buffer in the form of a text string.
2) Then, EXECUTE IMMEDIATE statement is applied.
3) Statement is executed and sqlcode, sqlstate value is set to specify that the statement has been executed.
EXECUTE IMMEDIATE :host-variable
strncpy(stbuf, “DELETE FROM employees WHERE empl_num=105);
EXEC SQL EXECUTE IMMEDIATE :stbuf;
PREPARE statement: PREPARE statement is used for preparing DML and DDL statements. It takes a host variable having a SQL statement and then sends the statement to DBMS, where the DBMS compiles it and creates an application plan for execution of the statement. The SQL statement is identified by using a statement name. The same name is used when execution of the statement is done by using EXECUTE statement.
PREPARE statement_name FROM :host_variable
EXEC SQL PREPARE stbuf_empl FROM :stbuf;
EXECUTE statement: It is used to execute a statement which was prepared by PREPARE statement. EXECUTE statement can’t be used to execute a SELECT statement.
EXECUTE statement_name [USING DESCRIPTOR descriptor_name | USING host_variable]
EXEC SQL EXECUTE stbuf_empl USING :emplid, :emplname;