ORACLE EXECUTE IMMEDIATE Usage

Reprint the first look, then finishing it ~ ~ ~

ORACLE EXECUTE IMMEDIATE Usage
EXECUTE IMMEDIATE replaces the previous Oracle8i Dbms_sql package package.
It resolves and immediately executes a dynamic SQL statement or a pl/sql block created by the runtime. Dynamic creation and execution of SQL statements ahead of time, the goal of execute immediate is to reduce enterprise costs and achieve higher performance, which is fairly easy to encode compared to the past. Although Dbms_ SQL is still available, but it is recommended that execute IMMEDIATE be used because it gains a benefit on top of the package.
-Use Tips
1. Execute immediate will not submit a DML transaction execution and should explicitly commit
If the DML command is processed through execute immediate,
Then you need to explicitly commit or as part of execute immediate yourself before you finish.
If the DDL command is processed through execute immediate, it submits all previously changed data
2. Queries that return multiple rows are not supported, and this interaction uses a temporary table to store the records (refer to the example below) or use ref cursors.
3. When executing SQL statements, do not use semicolons, when the Pl/sql block is executed, with a semicolon at its tail.
4. In the Oracle manual, these features are not covered in detail.
The following example shows all the possible aspects of using execute immediate. Hope to bring you convenience.
5. For forms developers, the forms 6i cannot use this feature when in Pl/sql 8.0.6.3. Version.
EXECUTE immediate– Usage Example
1. Run DDL statements in Pl/sql
SQL code

Begin 
   Execute immediate ' set role all '; 
End

To pass a value to a dynamic statement (USING clause)
SQL code

DECLARE 
   L_depnam VARCHAR2 (m): = ' testing '; 
   L_loc     Varchar2 (a): = ' d?i '; 
   Begin 
   Execute immediate ' INSERT INTO Dept val?s   (: 1,: 2,: 3) ' 
     using, L_depnam, L_loc; 
   commit; 
End

Retrieving values from dynamic statements (into clauses)
SQL code

DECLARE 
   l_cnt  varchar2 (); 
Begin 
   Execute immediate ' select COUNT (1) from the EMP ' into 
     l_cnt; 
   Dbms_output.put_line (l_cnt); 
End

Dynamic invocation routines. The parameter type must be specified for the binding variable argument used in the routine.
黓 thinks in type, other types must explicitly specify
SQL code

DECLARE 
   l_routin    varchar2 (MB): = ' gen2161.get_rowcnt '; 
   L_tblnam    varchar2: = ' emp '; 
   l_cnt number       ; 
   L_status    varchar2 (); 
Begin 
   execute immediate ' begin ' | | l_routin | | ' (: 2,: 3,: 4); End; ' 
     Using in L_tblnam, out l_cnt, in out l_status;

   If L_status!= ' OK ' then 
      dbms_output.put_line (' error '); 
   End If; 
End

Pass the return value to the Pl/sql record type;%rowtype variables are also available
SQL code

Declare 
   type Empdtlrec is record (empno number   (4), 
                            ename   varchar2 (), 
                            deptno   number (2)); 
   EMPDTL Empdtlrec; 
Begin 
   Execute immediate ' select Empno, ename, Deptno ' | | 
                    From the emp where empno = 7934 ' into 
     Empdtl; 
End

Passing and retrieving values. The INTO clause is used before the using clause
SQL code

DECLARE 
   l_dept     pls_integer: =; 
   L_nam      varchar2 (a); 
   L_loc      varchar2 (a); 
Begin 
   Execute immediate ' select Dname, loc from dept where deptno =: 1 ' into 
     L_nam, L_loc 
     using L_dept; 
End

Multiline query options. Populate the temporary table with an INSERT statement for this option.
Use a temporary table for further processing, or you can use ref cursors to correct this shortcoming.
SQL code

DECLARE 
   l_sal  pls_integer: =; 
Begin 
   Execute immediate ' insert into temp (empno, ename) ' | | ' Select Empno, ename from emp ' | | ' Where sal >: 1 ' 
     using L_sal; 
   commit; 
End

    For processing dynamic statements, EXECUTE IMMEDIATE is easier and more efficient than previously possible. 

When attempting to execute a dynamic statement, it is more important to handle the exception appropriately. Attention should be paid to capturing all possible exceptions. 15:40 | Add Comment | Fixed link | Write to log | Use of INSERT into select in Sqloracle
/* Formatted on 2008/06/02 15:37 (Formatter Plus v4.8.7) * *
INSERT into T_work
(F_recid, F_jobid, F_ruleid, F_jobtype, F_operid, F_opername,
F_b?time, F_assigntime, F_downtime, F_worktimes, F_finishtime)
SELECT Sys_guid () as F_recid, F_jobid, F_ruleid, F_jobtype, F_operid,
F_opername, F_b?time, F_assigntime,f_downtime,
DECODE (F_downtime, null,1, F_downtime + 1) as F_worktimes,
To_char (sysdate, ' Yyyy-mm-dd hh:mm:ss ') as Finishtime
From T_b?fer
WHERE f_jobid = ' 21 '
Note Adds a record from the T_b?fer to the T_work table.
F_recid and T_finishtime are not included in the B_b?fer;
F_worktimes need to add 1.
Sys_guid () means to generate a 32-bit string that never repeats.
DECODE (F_downtime, null,1, F_downtime + 1) means that if the f_downtime is null (NULL) The value is 1, otherwise it is f_downtime+1.
To_char (sysdate, ' Yyyy-mm-dd hh:mm:ss ') is a formatted time format; T_work is f_finishtime in the table VARCHAR2 data type.
15:22 | Add Comment | Fixed link | Write to log | Using method of dynamic SQL in SQLPL/SQL development
Original from: http://blog.chinaunix.net/u/19673/showart_272022.html

In general Pl/sql programming, SQL can be used directly in DML and transaction control statements, but DDL statements and system control statements cannot be used directly in Pl/sql, and in order to realize the use of DDL statements and system control statements in Pl/sql, they can be implemented by using dynamic SQL.
First we should understand what is dynamic SQL, in the Oracle database development Pl/sql Block We use the SQL is divided into: Static SQL statements and dynamic SQL statements. The so-called static SQL refers to the SQL statement used in the Pl/sql block is clear at compile time, and the execution is to determine the object. Dynamic SQL means that SQL statements are indeterminate when pl/sql blocks are compiled, such as performing different operations depending on the parameters entered by the user. The compiler does not process the dynamic statement part, simply creates the statement dynamically when the program is run, parsing the statement, and executing the statement.
Dynamic SQL in Oracle can be executed either through local dynamic SQL or through a dbms_sql package. The following two situations are described separately:
One, local dynamic SQL
Local dynamic SQL is implemented using the EXECUTE IMMEDIATE statement.
1. Local Dynamic SQL Execution DDL statement:
Requirements: Based on the user Input table name and field name and other parameters dynamic table.

Create or Replace procedure Proc_test
(
table_name in VARCHAR2,--table name
field1 in Varchar2,--field name
Datatype1 In Varchar2,--field type
field2 in Varchar2,--field name
datatype2 in varchar2--field type
) as
str_sql varchar2 (500); C8/>begin
str_sql:= ' CREATE table ' | | table_name| | ' (' | | field1| | ' ' | | datatype1| | ', ' | | field2| | ' ' | | datatype2| | ') ';
Execute immediate str_sql; --Dynamically executing DDL statements
exception when
others then
null;
End;

These are the stored procedure codes that are compiled through. The following executes a stored procedure dynamic build table.

Sql> Execute proc_test (' dinya_test ', ' id ', ' number (8) NOT null ', ' name ', ' varchar2 ');
Pl/sql procedure s?ssfully completed sql> desc
;
Name Type Nullable Default Comments 
---------------------------------------- 
ID Number (8)
name VARCHAR2 Y
sql>

Here, we have achieved our requirements, using local dynamic SQL to implement the dynamic execution of DDL statements based on parameters such as the table name and field name entered by the user, field type, and so on.
2, local dynamic SQL execution DML statement.
Requirements: Inserts the value entered by the user into the Dinya_test table built in the previous example.

Create or Replace procedure Proc_insert
(
ID in number,--input ordinal
name in Varchar2--enter name
) as
str_sql VA Rchar2 ();
Begin
str_sql:= ' insert INTO Dinya_test val?s (: 1,:2) ';
Execute immediate str_sql using Id,name; --Dynamically performs insert operation
Exception when
others then
null;
End;

Executes the stored procedure and inserts the data into the test table.

Sql> Execute Proc_insert (1, ' Dinya ');
Pl/sql procedure s?ssfully completed
sql> select * from Dinya_test;
ID NAME
1 Dinya

In the example above, local dynamic SQL executes a DML statement using a using clause, which binds the input value to a variable sequentially, and if an output parameter is required, you can use the returning into clause when executing dynamic SQL, such as:

DECLARE
p_id number:=1;
V_count number;
Begin
v_string:= ' SELECT COUNT (*) from table_name a where a.id=:id ';
Execute immediate v_string into V_count using p_id; 
End;

For more information about the return value in dynamic SQL and the mode of parameter execution for the output input binding variable, ask the reader to do the test on their own.
Two, using the Dbms_sql package
to implement dynamic SQL using Dbms_sql package is as follows: A, the first SQL statement to be executed or a block of statements is placed in a string variable. B, use the parse procedure of the Dbms_sql package to parse the string. C, use the Dbms_sql package bind_variable process to bind variables. D, use the Execute function of the Dbms_sql package to execute the statement.
1, using the Dbms_sql package to execute DDL statements
Requirements: Use the Dbms_sql package to build a table based on the table name, field name, and field type entered by the user.

Create or Replace procedure Proc_dbms_sql
(
table_name in VARCHAR2,--table name
field_name1 in Varchar2,--field name
Datatype1 in Varchar2,--field type
field_name2 in Varchar2,--field name
datatype2 in varchar2--field type
) as
v_cursor Number --Define the cursor
v_string VARCHAR2 (200);--Define the string variable
v_row number;--line
begin
V_cursor:=dbms_sql.open_cursor ; --Open the cursor for processing
v_string:= ' CREATE table ' | | table_name| | ' (' | | field_name1| | ' ' | | datatype1| | ', ' | | field_name2| | ' ' | | datatype2| | ') ';
Dbms_sql.parse (v_cursor,v_string,dbms_sql.native); --Parse Statement
V_row:=dbms_sql.execute (v_cursor);--Execute Statement
dbms_sql.close_cursor (v_cursor);--Close the cursor
Exception when
others then
dbms_sql.close_cursor (v_cursor);--close cursor
raise;
End

After the process has been compiled, the execution process creates the table structure:

Sql> Execute proc_dbms_sql (' dinya_test2 ', ' id ', ' number (8) NOT null ', ' name ', ' varchar2 ');
Pl/sql procedure s?ssfully completed sql> desc
;
The name Type Nullable Default Comments 
---------------------------------------- 
ID Number (8) 
NAME VARCHAR2 ( Y 
sql>

2. Use Dbms_sql package to execute DML statements
Requirements: Use the Dbms_sql package to update the corresponding records in the table based on the values entered by the user.
To view existing records in a table:

Sql> select * from Dinya_test2;
ID NAME
1 Oracle
2 CSDN
3 ERP
sql>

Build the stored procedure and compile it through:

Create or Replace procedure Proc_dbms_sql_update
(
ID number,
name Varchar2
) as
v_cursor number;- -Defines the cursor
v_string VARCHAR2 (200);--The string variable
v_row number;--rows
begin
v_cursor:=dbms_sql.open_cursor;-- For processing open cursor
v_string:= ' Update dinya_test2 a set a.name=:p _name where a.id=:p _id ';
Dbms_sql.parse (v_cursor,v_string,dbms_sql.native); --Parsing statement
dbms_sql.bind_variable (v_cursor, ':p _name ', name);--binding variable
dbms_sql.bind_variable (v_cursor, ':p _ ID ", id);　　　　　　　　　　　--Binding variable
v_row:=dbms_sql.execute (v_cursor); --Execute Dynamic SQL
dbms_sql.close_cursor (v_cursor);--Close cursor
exception when
others then
dbms_sql.close_ Cursor (v_cursor); --close cursor
raise;
End

Executes the procedure to update the data in the table based on the parameters entered by the User:

Sql> Execute proc_dbms_sql_update (2, ' Csdn_dinya ');
Pl/sql procedure s?ssfully completed
sql> select * from Dinya_test2;
ID NAME
1 Oracle
2 Csdn_dinya
3 ERP
sql>

Updates the Name field of the second article to the new value Csdn_dinya after the procedure is executed. This completes the function of using the Dbms_sql package to perform DML statements.
Using Dbms_sql, if the dynamic statement to be executed is not a query statement, use Dbms_sql. Execute or Dbms_sql. Variable_val? To execute, if you want to execute a dynamic statement that is a query statement, use Dbms_sql.define_column to define the output variable, and then use Dbms_sql. Execute, Dbms_sql. Fetch_rows, Dbms_sql. Column_val and Dbms_sql. Variable_val to execute the query and get the results.
Summary description:

During the Oracle development process, we can use dynamic SQL to execute DDL statements, DML statements, transaction control statements, and system control statements. It should be noted, however, that the use of dynamic SQL to execute DDL statements in a pl/sql block is different from the use of binding variables in DDL (bind_variable (v_cursor, ':p _name ', name), and does not need to be performed after parsing Dbms_ Sql. Bind_variable, add the input variable directly to the string. In addition, the DDL is invoked in the Dbms_sql. Parse when executed, so dbms_sql. Execute can also not be used, that is, the V_row:=dbms_sql.execute (v_cursor) section in the previous example can not.
15:14 | Add Comment | Fixed link | Write to log | SQL11 Month 17th
ResultSet Daquan
Development of friends, in the operation of the database, I believe most people have used it. But it is not only a pure carrier of a result set, only the function of storage. Here is a brief comment on its function.
The result set can be divided into four categories, each of which has its own characteristics, and is basically related to the way the statement statement is created.
1, basic type (most basic, most widely used)
How to create: no parameter creation type
Statement ST = Conn. Createstatement
ResultSet rs = Statement.excuteq?ry (SQLSTR);
Features: This simplest, no special features (no support for scrolling, updates, and so on), you can only use the next () single direction to read the data.
2, Rolling type
How to create: parameter creation type
Statement st = conn.createstatement (int resultsettype, int resultsetconcurrency)
ResultSet rs = st.executeq?ry (SQLSTR)
Parameters:
ResultsetType is to set the type of ResultSet object to scroll
Resultsetconcurency is the set of ResultSet objects that can be modified
The specific parameter values are shown in resultset.
Characteristics: (and the setting of specific parameters.) This type supports scrolling fetch records, which can be manipulated backwards, similar to how paging functions are operated. such as support: Next (), previous (), "a" (), absolute (int n), and so on.
3, more new
How to create: (same as 2, but with different parameter selection)
Statement st = createstatement (result.type_scroll_insensitive,result.concur_updatable)
Features: You can complete the update operation on the database. The update is to move the resultset cursor to the row you want to update, call UPDATEXXX (), and then use Updaterow () to complete the write to the database.
Restrictions/Requirements: (1) to a single table operation. (2) does not contain a join or a GROUP BY clause.
4, maintain the type
How to create:
Statement st=createstatement (int resultsetscrollable,int resultsetupdateable,int resultsetsetholdability)
ResultSet rs = St.excuteq?ry (SQLSTR);
Parameters: Only the third resultsetsetholdability is introduced, indicating whether the result set is open after the result set is committed, and its value is
Resultset.hold_cursors_over_commit: Indicates that the database is not closed when a commit is modified.
Resultset.close_cursors_at_commit: Indicates that ResultSet is closed when a modification is committed
Features: In general, the result set of the first query is closed when a query is executed using statement and another query is executed. This method is free to set whether the result set is closed.
Restrictions/Requirements: (1) only in the JDBC3.0 drive to set up.

Updatable result sets:

To update a row:

Updatexxx () ècancelrowupdates () Èupdaterow () èrowupdated ().

The Upadatexxx () method can only modify the data in the current row and cannot modify the data in the database, so after calling Updatexxx (), call the Updaterow () method to update the database with the new data in the current row.

Cancelrowupdates () method to discard changes to the current row, note that to make this method valid, you must call the Updterow () method before calling it.

RowUpdated () method to determine whether the current row is updated.

Insert row:

The Movetoinsertrow () method moves the cursor to the insert row, which is a special cache row associated with an updatable result set.

Movetoinsertrow () èupdatexxx () ègetxxx () Èinsertrow () èrowinserted ()

InsertRow () passes a new row to the database to actually insert a row of data in the new database

Rowinserted () method to determine whether the current row is an insert row.

Delete a row:

Deletrow () Deletes a row from the result set and the database, and this method cannot be invoked when the cursor points to the inserted row. A deleted row may leave an empty position in the result set, and you can call the RowDeleted () method to determine whether a row was deleted.

The use of an updatable result set must meet the following 3 conditions:

1, only queries against a single form in the database

2, the query statement cannot contain any join operations.

3, the table in the query operation must have a primary key, and the field that is the primary key must be included in the query's result set.

The result set performs the insert operation, and the following two conditions should also be met

1, the query operation must select all columns that cannot be empty in the database table

2, the query operation must select all columns that do not have default values.

To determine whether resultset is empty

ResultSet rs=stmt.executeq?ry (SQL);
RS = = null;
Rs.size () This method does not have
RS for resultset reference, even if there are 0 records in the result set, RS is not empty, so use RS = null to determine whether there are 0 records in the result set is wrong, and to use Rs.getrow (); 0 means no record – there are also problems when used
Using Rs.next () is a TR? To determine that the cursor is moved forward one bit, may lose a record, inappropriate
Boolean hasrows = false;
The following method is more appropriate
while (Rs.next ()) {
HasRows = tr?;
Do your st?f
}
if (!hasrows) {
Empty result set
}
You are dont have to test for null result set
19:50 | Add Comment | Fixed link | Write to log | JAVA10 month 14th
Store images from local hard drives in Oracle database BLOB implementation
Turn from: black hair

The

http://heiseto?a.javaeye.com
Database is Oracle, the field is a blob, the code language is Java, the upload can be a picture or other large file, displayed in the browser and saved on the hard drive not written in this article, written in the next chapter

Import Java.io.b?feredinputstream;
Import Java.io.FileInputStream;
Import Java.io.OutputStream;
Import Java.sql.Blob;
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import Java.sql.ResultSet;
Import java.sql.SQLException;

Import java.sql.Statement;

Import Javax.swing.JFileChooser;

Import oracle.jdbc.driver.OracleConnection;
   P Lic class Write {p lic static void Main (string[] args) {try {writewatdb = new Write ();
  Watdb.sendtodb ();
  catch (SQLException e) {e.printstacktrace ();
  catch (ClassNotFoundException e) {e.printstacktrace ();
 }} Connection conn;

 Statement stmt;
 ResultSet rs;

 int b?fersize;
  P Lic Write () throws SQLException, classnotfoundexception {String connectstring;
  Class.forName ("Oracle.jdbc.driver.OracleDriver");
  ConnectString = "JDBC:ORACLE:THIN:@192.168.1.134:1521:ZHPT";

  conn = (OracleConnection) drivermanager.getconnection (connectstring, "DZJC", "DZJC");
 stmt = Conn.createstatement (); } p lic void Sendtodb() {int amount = 0;
  OutputStream out = null;
  B?feredinputstream in = null;
  JFileChooser chooser = new JFileChooser ();
  int returnval = Chooser.showopendialog (null);
   if (ReturnVal = = jfilechooser.approve_option) {pathname = Chooser.getselectedfile (). GetAbsolutePath ();
   Name = Chooser.getselectedfile (). GetName ();
  Chooser = null; ' Else {System.out.println (' no file found.
   ");
   SYSTEM.OUT.PRINTLN ("Abnormal End of program ...");
  Return
   try {Conn.setautocommit (false);
   Stmt.executeupdate ("INSERT INTO Dzjc_img (IMG) val?s (Empty_blob ())");
   rs = Stmt.executeq?ry ("Select img from dzjc_img for update");
    if (Rs.next ()) {blob blob = RS.GETBLOB ("img");
    out = ((Oracle.sql.BLOB) BLOB). Getbinaryoutputstream ();
    B?fersize = ((Oracle.sql.BLOB) BLOB). Getb?fersize ();

    in = new B?feredinputstream (new FileInputStream (pathname), b?fersize);
    Byte[] B = new Byte[b?fersize];
    int count = In.read (b, 0, b?fersize); 
    while (count!=-1){Out.write (b, 0, Count);
     Amount + = count;
     SYSTEM.OUT.PRINTLN ("processed" + Amount + "byte.");
     Count = In.read (b, 0, b?fersize);
    SYSTEM.OUT.PRINTLN ("processed" + Amount + "byte, successful.");
    } out.close ();
    out = null;
    In.close ();
    in = null;
   Conn.commit ();
   } catch (Exception e) {e.printstacktrace ();
   try {conn.rollback (); The catch (Exception ignored) {}} is finally {if (out!=null) Try{out.close ();} catch (Exception igored) {} if ( In!=null) Try{in.close (), catch (Exception igored) {}}}

Can run directly

The database is built on its own, build a type of BLOB type on the line
18:24 | Add Comment | Fixed link | Write to log | Java takes the picture out of the Blob field in the Oracle database and exists on the hard drive
Turn:

Import Java.io.b?feredoutputstream;
Import Java.io.FileOutputStream;
Import Java.io.InputStream;
Import Java.sql.Blob;
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import Java.sql.ResultSet;
Import java.sql.SQLException;

Import java.sql.Statement;

Import Javax.swing.JFileChooser;

Import oracle.jdbc.driver.OracleConnection;
   P Lic class Read {p lic static void Main (string[] args) {try {Read rfdb = new Read ();
  Rfdb.readfromdb ();
  catch (SQLException e) {e.printstacktrace ();
  catch (ClassNotFoundException e) {e.printstacktrace ();
 }} Connection conn;

 Statement stmt;
 ResultSet rs;

 int b?fersize;
  P Lic Read () throws SQLException, classnotfoundexception {String connectstring;
  Class.forName ("Oracle.jdbc.driver.OracleDriver");
  ConnectString = "JDBC:ORACLE:THIN:@192.168.1.134:1521:ZHPT";
  conn = (OracleConnection) drivermanager.getconnection (connectstring, "DZJC", "DZJC");
 stmt = Conn.createstatement (); } p lic void ReadfromDB () {String pathname, name;
  int amount = 0;
  InputStream in = null;

  B?feredoutputstream out = null;
  JFileChooser chooser = new JFileChooser ();
  int returnval = Chooser.showopendialog (null);
   if (ReturnVal = = jfilechooser.approve_option) {pathname = Chooser.getselectedfile (). GetAbsolutePath ();
   Name = Chooser.getselectedfile (). GetName ();  Chooser =