1. Execute DDL and DDL statements (except select)
A) open a cursor.
B) analyze the statements to be executed
C) bind any input variables that may be required
D) execute the statement
E) Close the cursor.
The execution of the SELECT statement is as follows:
Set serveroutput on;
Declare
V_cursorid number;
V_selectrecords varchar2 (500 );
V_numrows integer;
V_mynum integer;
V_mytext varchar (50 );
Begin
V_cursorid: = dbms_ SQL .open_cursor;
V_selectrecords: = 'select * From mytable ';
Dbms_ SQL .parse (v_cursorid, v_selectrecords, dbms_ SQL .native );
Dbms_ SQL .define_column (v_cursorid, 1, v_mynum );
Dbms_ SQL .define_column (v_cursorid, 2, v_mytext, 50 );
V_numrows: = dbms_ SQL .execute (v_cursorid );
Loop
If dbms_ SQL .fetch_rows (v_cursorid) = 0 Then exit;
End if;
Dbms_ SQL .column_value (v_cursorid, 1, v_mynum );
Dbms_ SQL .column_value (v_cursorid, 2, v_mytext );
Dbms_output.put_line (v_mynum | ''| v_mytext );
End loop;
Dbms_ SQL .close_cursor (v_cursorid );
End;
/
2. dynamic SQL Implementation of select queries
A) open a cursor.
B) analyze the statements to be executed
C) bind any input variables that may be needed (if needed)
4) define output variables
D) execute the statement
5) retrieve records
6) store the retrieved record results in PL/SQL Variables
E) Close the cursor.
The example of the create statement is as follows:
Declare
V_cursorid number;
V_createtablestring varchar2 (500 );
V_numrows integer;
Begin
V_cursorid: = dbms_ SQL .open_cursor;
V_createtablestring: = 'create table mytable (myrow integer, mydesc varchar2 (50) tablespace tabs ';
Dbms_ SQL .parse (v_cursorid, v_createtablestring, dbms_ SQL .native );
V_numrows: = dbms_ SQL .execute (v_cursorid );
Exception
When others then
If sqlcode! =-955 then raise;
Else
Dbms_output.put_line ('talbe already exists ');
End if;
Dbms_ SQL .close_cursor (v_cursorid );
End;
/
An example of an insert statement is as follows:
Set serveroutput on;
Declare
V_cursorid number;
V_insertrecords varchar2 (500 );
V_numrows integer;
Begin
V_cursorid: = dbms_ SQL .open_cursor;
V_insertrecords: = 'insert into mytable values (: mynum,: mytext )';
Dbms_ SQL .parse (v_cursorid, v_insertrecords, dbms_ SQL .native );
Dbms_ SQL .bind_variable (v_cursorid, ': mynum', 1 );
Dbms_ SQL .bind_variable (v_cursorid, ': mytext', 'one ');
V_numrows: = dbms_ SQL .execute (v_cursorid );
Dbms_output.put_line (v_numrows );
-- Di 2 Tiao Ji Lu
Dbms_ SQL .bind_variable (v_cursorid, ': mynum', 2 );
Dbms_ SQL .bind_variable (v_cursorid, ': mytext', 'two ');
V_numrows: = dbms_ SQL .execute (v_cursorid );
Dbms_output.put_line (v_numrows | '2 ');
Exception
When others then raise;
Dbms_ SQL .close_cursor (v_cursorid );
Commit;
End;
/
3. Execute PL/SQL anonymous Blocks
A) open a cursor.
B) analyze the statements to be executed
C) bind any input variables that may be needed (if needed)
D) execute the statement
5) retrieve records
6) store the retrieved record results in PL/SQL Variables
E) Close the cursor.
Execute anonymous Blocks
Set serveroutput on;
Declare
V_cursorid number;
V_matchrecord varchar2 (500 );
V_numrows integer;
V_mynum integer;
V_mytext varchar2 (50 );
Begin
V_cursorid: = dbms_ SQL .open_cursor;
V_matchrecord: = 'begin'
Select myrow, mydesc into: myrow,: mytext from mytable
Where myrow = 2;
End ;';
Dbms_ SQL .parse (v_cursorid, v_matchrecord, dbms_ SQL .native );
Dbms_ SQL .bind_variable (v_cursorid, ': myrow', v_mynum );
Dbms_ SQL .bind_variable (v_cursorid, ': mytext', v_mytext, 50 );
V_numrows: = dbms_ SQL .execute (v_cursorid );
Dbms_ SQL .variable_value (v_cursorid, ': myrow', v_mynum );
Dbms_ SQL .variable_value (v_cursorid, ': mytext', v_mytext );
Dbms_output.put_line (v_mynum | ''| v_mytext );
Dbms_ SQL .close_cursor (v_cursorid );
End;
/
Check the progress of the retrieved record
Set serveroutput on;
Declare
V_cursorid number;
V_matchrecord varchar2 (500 );
V_numrows integer;
V_mynum integer;
V_mytext varchar2 (50 );
V_myrowid rowid;
V_totrow integer;
Begin
V_cursorid: = dbms_ SQL .open_cursor;
V_matchrecord: = 'select * From mytable for Update ';
Dbms_ SQL .parse (v_cursorid, v_matchrecord, dbms_ SQL .native );
Dbms_ SQL .define_column (v_cursorid, 1, v_mynum );
Dbms_ SQL .define_column (v_cursorid, 2, v_mytext, 50 );
V_numrows: = dbms_ SQL .execute (v_cursorid );
Loop
If dbms_ SQL .fetch_rows (v_cursorid) = 0 Then exit;
End if;
V_totrow: = dbms_ SQL .last_row_count;
V_myrowid: = dbms_ SQL .last_row_id;
Dbms_output.put_line ('the last row count is: '| v_totrow |
'The last rowid is: '| v_myrowid );
Dbms_ SQL .column_value (v_cursorid, 1, v_mynum );
Dbms_ SQL .column_value (v_cursorid, 2, v_mytext );
Dbms_output.put_line (v_mynum | ''| v_mytext );
End loop;
Dbms_ SQL .close_cursor (v_cursorid );
End;
/
