#1 02 Jan 2011 18:43

dsatya10
Member
Registered: 02 Jan 2011
Posts: 1

update insert procedure

Can any solve my problem in this procedure update is working but insert in not??????


create or replace procedure dept2
(
p_deptno in dept.deptno%type,
p_dname in dept.dname%type,
p_loc in dept.loc%type
)
as
begin
update dept set dname=p_dname,loc=p_loc where deptno=p_deptno;
commit;
exception
when no_data_found then
insert into dept values (p_deptno,'p_dname','p_loc');
commit;
end;
/

Offline

#2 03 Jan 2012 10:37

sanjay
Member
Registered: 03 Jan 2012
Posts: 3

Re: update insert procedure

don't use single quote in insert query...
use
insert into dept values (p_deptno,p_dname,p_loc);
instead of
insert into dept values (p_deptno,'p_dname','p_loc');

Offline

#3 17 Jan 2012 22:25

Opus
Member
Registered: 15 Dec 2011
Posts: 17

Re: update insert procedure

dsatya10

Is it really your intention to update all rows in the table with the values passed to the procedure?  The update has no predicate, so it will modify the entire table instead of a particular row.

Cheers,
Opus

Offline

#4 18 Jan 2012 00:11

Opus
Member
Registered: 15 Dec 2011
Posts: 17

Re: update insert procedure

In any event, this can also be done in SQL using a merge statement:

merge
 into dept d
 using (select * from dual)
 on (d.dname='&p_dname' and d.loc='&p_loc' and d.deptno='&p_deptno')
 when matched then
  update set some_column='some_value' where d.dname='&p_dname', d.loc='&p_loc', d.deptno='&p_deptno'
 when not matched then
  insert (dname, loc, deptno, some_column)
  values ('&p_dname', '&p_loc', '&p_deptno', some_value);

It is necessary to dummy-up the using clause since we are not deriving the data from a view, table, or query.  Using cannot be left out altogether.  This works in 11.2, but the merge statement goes back to at least 9.0.   The values are passed using bind variables per Oracle SQL best practices.

Cheers,
Opus

Offline

#5 18 Jan 2012 03:22

Opus
Member
Registered: 15 Dec 2011
Posts: 17

Re: update insert procedure

Actually, my code needs a small correction.  The update statement embedded in the merge statement does not require a predicate because it derives this from the "on" clause.  Here is the code:

def p_deptno='010'
def p_dname='HR'
def p_loc='LA'
def p_deptmgr='BOB'

drop table dept;

create table dept
 (dept_no  varchar2(3)
 ,dept_nm  varchar2(10)
 ,dept_loc varchar2(10)
 ,dept_mgr varchar2(10));

insert into dept values ('010','HR','LA',null);
insert into dept values ('020','AC','NY',null);
commit;

select * from dept;

merge
 into dept d
 using (select * from dual)
 on (d.dept_no='&p_deptno' and d.dept_nm='&p_dname' and d.dept_loc='&p_loc')
 when matched then
  update set dept_mgr='&p_deptmgr'
 when not matched then
  insert (dept_no, dept_nm, dept_loc, dept_mgr)
  values ('&p_deptno', '&p_dname', '&p_loc', '&p_deptmgr');

commit;

select * from dept;

Here are the results for updating a row of data:

SQL> def p_deptno='010'
SQL> def p_dname='HR'
SQL> def p_loc='LA'
SQL> def p_deptmgr='BOB'

SQL> drop table dept;

Table dropped.

SQL> create table dept
  2   (dept_no  varchar2(3)
  3   ,dept_nm  varchar2(10)
  4   ,dept_loc varchar2(10)
  5   ,dept_mgr varchar2(10));

Table created.

SQL> insert into dept values ('010','HR','LA',null);

1 row created.

SQL> insert into dept values ('020','AC','NY',null);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dept;

DEP DEPT_NM    DEPT_LOC   DEPT_MGR
--- ---------- ---------- ----------
010 HR         LA
020 AC         NY

SQL> merge
  2   into dept d
  3   using (select * from dual)
  4   on (d.dept_no='&p_deptno' and d.dept_nm='&p_dname' and d.dept_loc='&p_loc')
  5   when matched then
  6    update set dept_mgr='&p_deptmgr'
  7   when not matched then
  8    insert (dept_no, dept_nm, dept_loc, dept_mgr)
  9    values ('&p_deptno', '&p_dname', '&p_loc', '&p_deptmgr');
old   4:  on (d.dept_no='&p_deptno' and d.dept_nm='&p_dname' and d.dept_loc='&p_loc')
new   4:  on (d.dept_no='010' and d.dept_nm='HR' and d.dept_loc='LA')
old   6:   update set dept_mgr='&p_deptmgr'
new   6:   update set dept_mgr='BOB'
old   9:   values ('&p_deptno', '&p_dname', '&p_loc', '&p_deptmgr')
new   9:   values ('010', 'HR', 'LA', 'BOB')

1 row merged.

SQL> commit;

Commit complete.

SQL> select * from dept;

DEP DEPT_NM    DEPT_LOC   DEPT_MGR
--- ---------- ---------- ----------
010 HR         LA         BOB
020 AC         NY

And here is an insert:

SQL> def p_deptno='030'
SQL> def p_dname='AP'
SQL> def p_loc='FW'
SQL> def p_deptmgr='ANN'

SQL> merge
  2   into dept d
  3   using (select * from dual)
  4   on (d.dept_no='&p_deptno' and d.dept_nm='&p_dname' and d.dept_loc='&p_loc')
  5   when matched then
  6    update set dept_mgr='&p_deptmgr'
  7   when not matched then
  8    insert (dept_no, dept_nm, dept_loc, dept_mgr)
  9    values ('&p_deptno', '&p_dname', '&p_loc', '&p_deptmgr');
old   4:  on (d.dept_no='&p_deptno' and d.dept_nm='&p_dname' and d.dept_loc='&p_loc')
new   4:  on (d.dept_no='030' and d.dept_nm='AP' and d.dept_loc='FW')
old   6:   update set dept_mgr='&p_deptmgr'
new   6:   update set dept_mgr='ANN'
old   9:   values ('&p_deptno', '&p_dname', '&p_loc', '&p_deptmgr')
new   9:   values ('030', 'AP', 'FW', 'ANN')

1 row merged.

SQL> commit;

Commit complete.

SQL> select * from dept;

DEP DEPT_NM    DEPT_LOC   DEPT_MGR
--- ---------- ---------- ----------
010 HR         LA         BOB
020 AC         NY
030 AP         FW         ANN

Cheers,
Opus

Offline

Board footer

Powered by FluxBB