EXECUTE IMMEDIATE Statements in Java Integrate code 128 barcode in Java EXECUTE IMMEDIATE Statements

EXECUTE IMMEDIATE Statements using barcode generating for none control to generate, create none image in none applications. Code 11 THE EXECUTE IMME none for none DIATE STATEMENT The EXECUTE IMMEDIATE statement parses a dynamic statement or a PL/SQL block for immediate execution. Its structure is as follows (the reserved words and phrases in square brackets are optional):. EXECUTE IMMEDIATE dynamic_SQL_string [INTO defined_variable1, defined_variable2, ...] [USING [IN OUT IN OUT] bind_argument1, bind_argument2, ...][{RETURNING RETURN} field1, none for none field2, ...

INTO bind_argument1, bind_argument2, ...

]. dynamic_SQL_stri ng is a string that contains a valid SQL statement or a PL/SQL block. The INTO clause contains the list of predefined variables that hold values returned by the SELECT statement. This clause is used when a dynamic SQL statement returns a single row similar to a static SELECT INTO statement.

Next, the USING clause contains a list of bind arguments whose values are passed to the dynamic SQL statement or PL/SQL block. IN, OUT, and IN OUT are modes for bind arguments. If no mode is specified, all bind arguments listed in the USING clause are in IN mode.

Finally, the RETURNING INTO or RETURN clause contains a list of bind arguments that store values returned by the dynamic SQL statement or PL/SQL block. Similar to the USING clause, the RETURNING INTO clause may also contain various argument modes; however, if no mode is specified, all bind arguments are in OUT mode..

DID YOU KNOW Wh none for none en an EXECUTE IMMEDIATE statement contains both USING and RETURNING INTO clauses, the USING clause may specify only IN arguments.. FOR EXAMPLE DECLARE sql_stmt none none VARCHAR2(100); plsql_block VARCHAR2(300); v_zip VARCHAR2(5) := "11106"; v_total_students NUMBER; v_new_zip VARCHAR2(5); v_student_id NUMBER := 151; BEGIN -- Create table MY_STUDENT sql_stmt := "CREATE TABLE my_student ". "AS SELECT * FR OM student WHERE zip = ". v_zip; EXECUTE I none none MMEDIATE sql_stmt; -- Select total number of records from MY_STUDENT table -- and display results on the screen EXECUTE IMMEDIATE "SELECT COUNT(*) FROM my_student" INTO v_total_students; DBMS_OUTPUT.PUT_LINE ("Students added: ". v_total_students);. EXECUTE IMMEDIATE Statements FOR EXAMPLE (continued). -- Select curren none for none t date and display it on the screen plsql_block := "DECLARE " . " v_date DATE; " . "BEGIN " . " SELECT SYSDAT E INTO v_date FROM DUAL; ". " DBMS_OUTPUT.P none none UT_LINE (TO_CHAR(v_date, ""DD-MON-YYYY"")) ;". "END;"; EXECUTE IMMEDIATE plsql_block; -- Update record in MY_STUDENT table sql_stmt := "UPDATE my_student SET zip = 11105 WHERE student_id = :1 ". "RETURNING zip none none INTO :2"; EXECUTE IMMEDIATE sql_stmt USING v_student_id RETURNING INTO v_new_zip; DBMS_OUTPUT.PUT_LINE ("New zip code: ". v_new_zip); END;. This script cont none for none ains several examples of dynamic SQL. First, you create the table MY_STUDENT and populate it with records for a specified value of zip code. It is important to note that the variable v_zip is concatenated with the CREATE statement instead of being passed in as a bind argument.

This point is illustrated in the next example. Second, you select the total number of students added to the MY_STUDENT table and display it on the screen. You use the INTO option with the EXECUTE IMMEDIATE statement because the SELECT statement returns a single row.

Third, you create a simple PL/SQL block in which you select the current date and display it on the screen. Because the PL/SQL block does not contain any bind arguments, the EXECUTE IMMEDIATE statement is used in its simplest form. Finally, you update the MY_STUDENT table for a given student ID and return a new value of zip code using the RETURNING statement.

So, the EXECUTE IMMEDIATE command contains both USING and RETURNING INTO options. The USING option allows you to pass a value of student ID to the UPDATE statement at runtime, and the RETURNING INTO option allows you to pass a new value of zip code from the UPDATE statement into your program. When run, this example produces the following output:.

Copyright © . All rights reserved.