Re: Problem while inserting a record into a table

From: ddf <oratune_at_msn.com>
Date: Tue, 7 Sep 2010 09:18:02 -0700 (PDT)
Message-ID: <0153f1c2-444a-4e39-a9c6-60fac5bf03d6_at_l32g2000prn.googlegroups.com>

On Sep 7, 7:44 am, Sandy80 <svarshneym..._at_gmail.com> wrote:
> hi,
>
> I am trying to insert a record in a employee table 1 based on whether
> a record already exists in table 1. If a record for that employee
> already exists in table 1, I need to insert the record into table 2
> else into table 1 itself. I am trying to do it through a pl/sql db
> procedure.
> So the code I have for insert statement is as follows:
>
> insert when (empno = 1234)
>         then into table 2 (empname,empno)
>         values (
>         AAAA,
>         1234
>         )
>         else
>         into table 1(empname,empno)
>         values (
>         AAAA,
>         1234
>         )
>         select * from table 1 where empno = 1234;
>
> The insert statement works when there are no records in table 1 for
> that employee but fails when there are no records. Can anyone help me
> as what do I need to add/change in this query for it to work in both
> the conditions. Any help would be appreciated!

The second insert needs to be done in an exception block since you raise a NO_DATA_FOUND error:

SQL> create or replace procedure cond_insrt(p_empno IN varchar2)   2 is
  3 v_empname emp.empname%type;   4 begin
  5 select empname into v_empname from table_1 where empno = p_empno;
  6

  7          if v_empname is not null then
  8                  insert into table_2 (empname, empno)
  9                  values (v_empname, p_empno);
 10          end if;
 11  exception
 12          when no_data_found then
 13                  insert into table_1 (empname, empno)
 14                  values ('AAAA', p_empno);
 15          when others then
 16              dbms_output.put_line('Displaying the error stack:');
 17              dbms_output.put(dbms_utility.format_error_stack);

 18
dbms_output.put_line(dbms_utility.format_error_backtrace);  19 end;
 20 /

Procedure created.

SQL>
SQL> select * From table_1;

     EMPNO EMPNAME    JOB              MGR HIREDATE         SAL
COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      1234 SMITHY     CLERK           7902 17-DEC-80
800                    20
      7369 SMITH      CLERK           7902 17-DEC-80
800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600
300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250
500         30
      7566 JONES      MANAGER         7839 02-APR-81
2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250
1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81
2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81
2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82
3000                    20
      7839 KING       PRESIDENT            17-NOV-81
5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81
1500          0         30

11 rows selected.

SQL>
SQL> select * From table_2;

no rows selected

SQL>
SQL> exec cond_insrt(1234)

PL/SQL procedure successfully completed.

SQL>
SQL> select * From table_1;

     EMPNO EMPNAME    JOB              MGR HIREDATE         SAL
COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      1234 SMITHY     CLERK           7902 17-DEC-80
800                    20
      7369 SMITH      CLERK           7902 17-DEC-80
800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600
300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250
500         30
      7566 JONES      MANAGER         7839 02-APR-81
2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250
1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81
2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81
2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82
3000                    20
      7839 KING       PRESIDENT            17-NOV-81
5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81
1500          0         30

11 rows selected.

SQL>
SQL> select * From table_2;

     EMPNO EMPNAME    JOB              MGR HIREDATE         SAL
COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      1234 SMITHY SQL>
SQL> exec cond_insrt(2345);

PL/SQL procedure successfully completed.

SQL>
SQL> select * From table_1;

     EMPNO EMPNAME    JOB              MGR HIREDATE         SAL
COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      1234 SMITHY     CLERK           7902 17-DEC-80
800                    20
      7369 SMITH      CLERK           7902 17-DEC-80
800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600
300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250
500         30
      7566 JONES      MANAGER         7839 02-APR-81
2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250
1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81
2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81
2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82
3000                    20
      7839 KING       PRESIDENT            17-NOV-81
5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81
1500          0         30

     EMPNO EMPNAME    JOB              MGR HIREDATE         SAL
COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      2345 AAAA 12 rows selected.

SQL>
SQL> select * From table_2;

     EMPNO EMPNAME    JOB              MGR HIREDATE         SAL
COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      1234 SMITHY SQL> Received on Tue Sep 07 2010 - 11:18:02 CDT