Oracle inside (7)

There are two ways to execute dynamic SQL: dbms_ SQL package and local dynamic SQL.
7.1 dbms_ SQL package
Powerful functions and a large number of functions are rarely used.
7.2 local dynamic SQL
7.2.1 execute DDL statements
Begin
Advanced PL/SQL programming
29th pages 29 pages
Execute immediate 'create table yang_test (Eid varchar2 (30) primary key )';
Execute immediate 'alter table yang_test add (ephoto clob )';
Execute immediate 'alter table yang_test modify (Eid number )';
Execute immediate 'drop table yang_test ';
End;
/
7.2.2 bind a variable
Declare
L_ SQL varchar2 (200 );
Rochelle photo yang_test.ephoto % type;
Rochelle ID yang_test.eid % type;
Begin
Rochelle SQL: = 'insert into yang_test values (: 1,: 2 )';
Execute immediate l_ SQL using 10, 'Welcome to our couty ';
Commit;
Rochelle SQL: = 'select ephoto, Eid from yang_test where eid =: 1 ';
Execute immediate l_ SQL into l_photo, l_id using 10;
Dbms_output.put_line (l_id );
Dbms_output.put_line (l_photo );
End;
/
Note: run the above Code to view the result !!!
7.2.3 execute PL/SQL Blocks
Declare
Rochelle ID number;
L_ SQL varchar2 (200): = 'in in select Eid into: 1 from yang_test; end ;';
Begin
Execute immediate l_ SQL into l_id;
Dbms_output.put_line (to_char (l_id ));
End;