Dynamic SQL-flexible operations on various types of data!

Static SQL is used to complete specific functions, and dynamic SQL is used to flexibly process various data

PS: To execute a dynamic SQL statement, you must store the SQL statement in a string variable. The SQL statement can contain placeholders (starting with a colon)
Placeholder example: Select * from test where name =: 1; -- red indicates a placeholder
1. Definition:
Dynamic SQL is an SQL statement that is dynamically entered when PL/SQL blocks are run.
2. Conditions for using dynamic SQL:
1) execute DDL statements such as create, alter, drop
2) execute DCL statements such as grant and revoke.
3) execute more flexible SQL statements, such as where conditions that cannot be used in select statements
3. Handling of dynamic SQL statements
1) Use the execute immediate statement ( Most used )
A. Process DDL operations -- Only dynamic SQL can be used in PL/SQL blocks.
B. Process DCL operations -- Only dynamic SQL can be used in PL/SQL blocks.
C. Process DML operations M = manipulation processing, Operation
D. Process single row query operations
2) use the open-for, fetch, and close statements ( Yes )
3) Use batch dynamic SQL ( Basically not )
Example:
(1) Return Value
Declare
User_number number (3 );
V_ SQL varchar2 (1000 );
Begin
V_ SQL: = 'Update hkb_test3
Set a. Age =: A + 15
Where a. user_id =: B returning a. Age into: user_number ';
Execute immediate v_ SQL
Using & A, & B
Returning into user_number;(Three lines in red are an SQL statement)
Dbms_output.put_line ('user _ number: '| user_number );
End;
(2) No Return Value
Declare
V_ SQL varchar2 (100 );
Begin
V_ SQL: = 'Update hkb_test3
Set a. Age =: a' |'
Where a. user_id =: B ';
Execute immediate v_ SQL
Using & A, & B;
End;
(3) multi-row query with a cursor
Declare
Type agecursor is ref cursor;
V_age agecursor; -- defines the cursor variable
V_name hkb_test % rowtype;(Inherit the row attributes of the table)
V_ SQL varchar2 (1000 );
Begin
V_ SQL: = 'select * From hkb_test where age =: ';
Open v_age for v_ SQL
Using &; -- Open the cursor variable
Loop
Fetch v_age
Into v_name;
Exit when v_age % notfound;
Dbms_output.put_line (v_name.name | 'Age: '| v_name.age );
End loop; -- extract data cyclically
Close v_age;-- Close the cursor variable
End;

PS: using the bulk clause in dynamic SQL is useless for the moment. I will study it later!