Home » Other » Training & Certification » Need help in Procedure
Need help in Procedure [message #299586] Tue, 12 February 2008 09:05 Go to next message
adilsami
Messages: 46
Registered: October 2007
Member
I HAVE 2 TABLES NAMED EMP,SAL
EMP Table:

EMPNO NUMBER(10) PK , 
SALARY NUMBER(10),
COMM NUMBER(3)
...........................

SAL Table:

EMPNO NUMBER(10) FK ,
SAL_PERIOD NUMBER(5),
SAL_YEAR   NUMBER(5),
AMOUNT    NUMBER(10,3)



I Want to Write a PROCEDURE to Insert into SAL Table
EMPNO , SAL_PERIOD , (SAL_YEAR from SYSDATE converted to number)
AMOUNT ( SALARY + COMM .. coming from the EMP table of the
provided EMPNO)

Can anyone help me writing the code.
Re: Need help in Procedure [message #299587 is a reply to message #299586] Tue, 12 February 2008 09:14 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Hmmm, looks like homework to me Wink

A few hints:

- Write a SELECT-statement that would report what you want to insert into your SAL-table:

SELECT EMPNO , ...
FROM EMP


If the output of that query looks exactly what you want to insert, then take a look at this hint:

INSERT INTO table (column-list) VALUES (column_list)
or
INSERT INTO table (column-list) SELECT ...


So basicall you don't need a PROCEDURE at all, but if that's your assignment:

CREATE OR REPLACE PROCEDURE xxxxx
BEGIN
  INSERT ...
END ;
/
Re: Need help in Procedure [message #299589 is a reply to message #299586] Tue, 12 February 2008 09:43 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
I Want to Write a PROCEDURE to Insert into SAL Table
EMPNO , SAL_PERIOD 

Post what you have tried.

Can anyone help me writing the code. 

Yes.But,first start writing the procedure and how you tried
Re: Need help in Procedure [message #299593 is a reply to message #299586] Tue, 12 February 2008 10:10 Go to previous messageGo to next message
adilsami
Messages: 46
Registered: October 2007
Member
ive been trying to write the procedure ,but i get stuck everytime

how can i use multiple select statement in the INSERT clause
coz i want to insert the date from dual , and the Amount
from emp table .. n take the (empno , sal_period) fields
from the user .

these r the select statements that ive achieved, but dunno
how to use them int he procedure .

1) insert into sal (sal_year) 
select (to_number(to_char(sysdate, 'YYYY')))
from dual;

2)  insert into sal (ammount) 
select (sal+comm) 
from emp 
where emp.empno=sal.empno;

3) insert into sal 
(empno,sal_period) 
values(&empno,&sal_period);


now how i can combined these 3 codes in the procedure .
Re: Need help in Procedure [message #299605 is a reply to message #299593] Tue, 12 February 2008 11:40 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Uh, oh ... not exactly like that. There's no need to select SYSDATE from dual - you can use it (almost) anywhere as it is a function.

I don't quite understand what 'sal.sal_period' is. You want to enter this value by your own?

Also, why would you need a procedure to do that? Use SQL whenever you can! Something like this:
insert into sal
  (empno, sal_period, sal_year, amount)a
  (select empno, 
          &sal_period, 
          to_number(to_char(sysdate, 'yyyy')),
          nvl(salary, 0) + nvl(comm, 0)
   from emp
   where empno = &empno
  );
Re: Need help in Procedure [message #299790 is a reply to message #299605] Wed, 13 February 2008 03:27 Go to previous message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Quote:
to_number(to_char(sysdate, 'yyyy'))

Or you could use
EXTRACT (YEAR FROM SYSDATE)
Previous Topic: which oracle software to be installed on vista?
Next Topic: ocp(developer track) books and exams
Goto Forum:
  


Current Time: Sun Dec 11 00:23:01 CST 2016

Total time taken to generate the page: 0.04768 seconds