PL/SQL - insert record only if record does not exist

Question

I got no good answers to my question, found out how to do it. This is how you would insert a record to a table only if the record does not already exist:

1) Create or Replace function on your schema (this is checking 2 parameters, you can set it to check as many as you wish) PL/SQL is very specific , copying and pasting as I have written should compile successfully. It took many tries to get the syntax just right. This function checks the Table to be written to, and the corresponding column names to be checked if they already exist together.

create or replace function Found(
   var1 type, 
   var2 type)
 return number 
 is             
   numberOfSelectedRows number := 0;
 begin 
   select count(*) 
     into numberOfSelectedRows 
     from TABLE 
    where COLUMN_NAME = var1 
      and COLUMN_NAME = var2;

   return numberOfSelectedRows;
 end Found;

2) Write the java to execute the pl/sql function: this is done with NetBeans. When the button is clicked, it takes FORM data- loaded from other tables, and determines whether or not the record already exists in the table to be inserted into.

  try {             
       DriverManager.registerDriver (new oracle.jdbc.OracleDriver());   
       con = DriverManager.getConnection(""+LOGIN.url+"");

       String str1 = jTextField_VariableName.getText();
       String str2 = jTextField_VariableName.getText();

       String q = "insert into TABLE (var1 type, var2 type) VALUES ('"+str1+"', '"+str2+"')" ; 

       cs = con.prepareCall("{?=call Found(?, ?)}");              // cs = CallableStatement - defined in class  CallableStatement cs = null; 

       cs.setString(2, str1);
       cs.setString(3, str2);
       cs.registerOutParameter(1, Types.INTEGER);
       cs.execute();

       if(cs.getInt(1)>= 1)
            {
            JOptionPane.showMessageDialog(null, " this record already exists");
            }
       else 
           {
            try{
                DriverManager.registerDriver (new oracle.jdbc.OracleDriver());  
                con = DriverManager.getConnection(""+LOGIN.url+"");

                pst = con.prepareStatement(q);

                pst.execute();

                }catch(SQLException ex)  {Logger.getLogger(REGISTER_STUDENT.class.getName()).log(Level.SEVERE, null, ex);}
           }

      } catch (SQLException ex) {Logger.getLogger(REGISTER_STUDENT.class.getName()).log(Level.SEVERE, null, ex);}

You can have a look at Oracle MERGE statement.

Jacob
– Jacob

2013-09-29 17:35:22 +00:00
Commented Sep 29, 2013 at 17:35 — Jacob
– Jacob, Commented Sep 29, 2013 at 17:35

krokodilko · Accepted Answer · 2013-10-01 00:32:31Z

2

Just do:

String q = 
"insert into TABLE (var1, var2) " +
"SELECT ?, ? FROM dual "+
"WHERE NOT EXISTS(
"   SELECT 1 FROM table
"   WHERE var1 = ? AND var2 = ? )";
pst = con.prepareStatement(q);
pst.setString( 1, str1 );
pst.setString( 2, str2 );
pst.setString( 3, str1 );
pst.setString( 4, str2 );
int status = pst.execute();
if( status > 0 )
    System.out.println("New row inserted");
else
    System.out.println("Row already exists, insert skipped");

--- EDIT ---

INSERT INTO .... SELECT .... syntax is suported on most DBMS, see this link for more details: http://www.w3schools.com/sql/sql_insert_into_select.asp
It simply selects rows from one table and inserts a result into another table.

dual is a "dummy" table on Oracle databases, it has one column and one row, here is an example: http://www.sqlfiddle.com/#!4/d41d8/11
Dual table is helpful in many cases where we need to create a row with some values. In other databases - esspecially in MySql and PostGreSQL - this can be done with simple SELECT x, y, unfortunately Oracle doesn't support this syntax, and we must use SELECT x, y FROM dual.

In our case it is used to create a row of values that we want to insert into the table:

INSERT INTO table
SELECT x, y FROM dual;

However, the row must be inserted only when some condition is meet - this condition is described in the WHERE clause:

INSERT INTO table
SELECT x, y FROM dual
WHERE NOT EXISTS( 
   SELECT 1 FROM table
   WHERE var1 = X and var2 = Y
)

if the subquery SELECT 1 FROM ... returns a row (a row exists) - then the condition NOT EXISTS ... is false, and SELECT x, y returns nothing (empty resultset), and nothing is inserted into the table. On the other hand, when the subquery doesn't find anything, the whole condition is true, and the SELECT x,y statement creates a row with two columns: X+Y, and this row is inserted into the table.

In the PreparedStatement (in java code) we are using ? instead of X,Y variables - ? is a placeholder (a substitution variable). This link: http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html describes in details how PreparedStatement works and how to use substitution variables.

Saying it simply - our query has 4 placeholders ?:

 insert into TABLE (var1, var2)  
 SELECT ?, ? FROM dual  
 WHERE NOT EXISTS(
    SELECT 1 FROM table
    WHERE var1 = ? AND var2 = ? ) ;

and setXXX statements binds values to consecutive placeholders:

pst.setString( 1, str1 );
pst.setString( 2, str2 );
pst.setString( 3, str1 );
pst.setString( 4, str2 );

thus after binding values to substitution marks, the query (which is finally executed) looks like:

 INSERT INTO TABLE (var1, var2)  
 SELECT 'str1', 'str2' FROM dual  
 WHERE NOT EXISTS(
    SELECT 1 FROM table
    WHERE var1 = 'str1' AND var2 = 'str2' ) ;

edited Oct 1, 2013 at 0:32

answered Sep 29, 2013 at 15:34

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user2826554 Over a year ago

Thanks Kordirko, I asked this question before and got some smartalek remark about how I should just pray that it works. I have little training in PL/SQL, could you recommend a good website which shows this notation? Thank you

krokodilko Over a year ago

@user2826554 I added a shoth explanation, hope this helps

user2826554 Over a year ago

Okay, but where is the part about the VALUES being inserted? Know a good page which shows this notation?

krokodilko Over a year ago

Take a look at this link: docs.oracle.com/cd/B28359_01/server.111/b28286/… , at examples part. It is Oracle documentation (very detailed, sometimes even too much detailed and hard to read), but there are nice examples of INSERT INTO ... VALUES and INSERT INTO ... subquery commands there.

Collectives™ on Stack Overflow

PL/SQL - insert record only if record does not exist

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related