Home » SQL & PL/SQL » SQL & PL/SQL » Query
Query [message #185109] Mon, 31 July 2006 01:09 Go to next message
b_chugh
Messages: 68
Registered: August 2005
Location: delhi
Member
hi everybody

CREATE TABLE BH_EMP(ENO NUMBER,ENMAE VARCHAR2(20),SAL NUMBER);

INSERT INTO BH_EMP VALUES(1,'BHASKAR',2000);
INSERT INTO BH_EMP VALUES(2,'NIDHEESH',3000);
INSERT INTO BH_EMP VALUES(3,'SACHIN',500);
INSERT INTO BH_EMP VALUES(4,'VIKAS',200);
INSERT INTO BH_EMP VALUES(5,'NITINM',3000);
INSERT INTO BH_EMP VALUES(6,'NITINA',4000);
INSERT INTO BH_EMP VALUES(7,'NEHA',5000);
INSERT INTO BH_EMP VALUES(8,'MINAKSHI',2000);
INSERT INTO BH_EMP VALUES(9,'RAHUL',7000);
INSERT INTO BH_EMP VALUES(10,'SAURABH',2500);
INSERT INTO BH_EMP VALUES(11,'VINAYAK',3500);
INSERT INTO BH_EMP VALUES(12,'RAJEEV',7000);
INSERT INTO BH_EMP VALUES(13,'GAURAV',1200);
INSERT INTO BH_EMP VALUES(14,'ABHISHEK',1300);
INSERT INTO BH_EMP VALUES(15,'RAJ',6000);


I want to write a function which updates the salary of all employees whose salary >1000 by 500 and after every 5 updates it should display a message

5 row updated

or if no row satisfy the criteria then then display a message

No rows updated


Can You help regarding this.

I tried something of this sort



CREATE OR REPLACE FUNCTION BH_FUNC1
RETURN VARCHAR2
as
CURSOR curs is SELECT ENO,SAL from BH_EMP WHERE SAL>1000 FOR UPDATE OF SAL;
v_eno bh_emp.eno%type;
v_sal bh_emp.sal%type;
BEGIN
OPEN CURS;
LOOP
FETCH CURS INTO v_eno,v_sal;
EXIT WHEN curs%NOTFOUND;

UPDATE bh_emp SET SAL=v_sal+500 WHERE CURRENT OF curs;

END LOOP;

IF CURS%ROWCOUNT=0 THEN
RETURN 'NO UPDATE DONE';
ELSE
RETURN 'UPDATE COMPLETE';
END IF;
CLOSE CURS;
END;




Thanks in advance






Re: Query [message #185118 is a reply to message #185109] Mon, 31 July 2006 01:41 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

b_chugh,
Here u go....

CREATE OR REPLACE FUNCTION BH_FUNC1
RETURN VARCHAR2
as
CURSOR curs is SELECT ENO,SAL from BH_EMP WHERE SAL>1000 FOR UPDATE OF SAL;
v_eno bh_emp.eno%type;
v_sal bh_emp.sal%type;
v_cnt number;
v_status varchar2(20);
v_sta number;
BEGIN
v_sta :=0;
OPEN CURS;
LOOP
FETCH CURS INTO v_eno,v_sal;
EXIT WHEN curs%NOTFOUND;

UPDATE bh_emp SET SAL=v_sal+500 WHERE CURRENT OF curs;
v_cnt:= CURS%ROWCOUNT;
if v_cnt <> 0 then
  v_sta := v_sta +1;
  if v_sta = 5 then 
     dbms_output.put_line('UPDATED 5 ROWS');
	 v_sta := 0;
  end if;
end if;
END LOOP;

IF CURS%ROWCOUNT=0 THEN
RETURN 'NO UPDATE DONE';
ELSE 
RETURN 'UPDATE COMPLETE';
END IF;
CLOSE CURS;
END;
/



Naveen
Re: Query [message #185121 is a reply to message #185118] Mon, 31 July 2006 01:46 Go to previous messageGo to next message
b_chugh
Messages: 68
Registered: August 2005
Location: delhi
Member


Thanks Navkrish
Re: Query [message #185128 is a reply to message #185121] Mon, 31 July 2006 02:02 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Or something like .. [No Need for More variables ]

CREATE OR REPLACE FUNCTION Update_Sal1 RETURN VARCHAR2 IS
CURSOR curs IS SELECT EMPNO,SAL FROM EMP WHERE SAL>1000 FOR UPDATE OF SAL ;
rcount NUMBER := 0;
BEGIN
FOR rec IN curs
LOOP
	UPDATE EMP SET sal = sal+500 WHERE CURRENT OF curs ;
		  rcount := CURS%ROWCOUNT;
	IF MOD(rcount,5)=0 THEN
	   DBMS_OUTPUT.PUT_LINE ('5 row updated');
	END IF;    
END LOOP;
			
IF rcount  =0 THEN
   RETURN 'No row updated' ;
ELSE
   DBMS_OUTPUT.PUT_LINE ( MOD(rcount ,5)||' ROW updated');
   RETURN 'Updation Completed ';
END IF;   
END;


Thumbs Up
Rajuvan.
Re: Query [message #185129 is a reply to message #185128] Mon, 31 July 2006 02:05 Go to previous messageGo to next message
b_chugh
Messages: 68
Registered: August 2005
Location: delhi
Member


Thanks Rajuvan
Re: Query [message #185140 is a reply to message #185129] Mon, 31 July 2006 03:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I hope you realise that the dbms_output lines are shown AFTER completion of the entire procedure.
You will not see anything while the procedure is running.
Re: Query [message #185326 is a reply to message #185140] Tue, 01 August 2006 04:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
So, lets use DBMS_APPLICATION_INFO instead.
I'd have used set_session_longops, but that would be too much coding for someone elses homework, and I don't think the tutor would think it was independent work anyway.

CREATE OR REPLACE FUNCTION Update_Sal1 RETURN VARCHAR2 IS
CURSOR curs IS SELECT EMPNO,SAL FROM EMP WHERE SAL>1000 FOR UPDATE OF SAL ;
rcount NUMBER := 0;
BEGIN
dbms_application_info.set_client_info('Rows Updated := 0');
FOR rec IN curs
LOOP
	UPDATE EMP SET sal = sal+500 WHERE CURRENT OF curs ;
		  rcount := CURS%ROWCOUNT;
	IF MOD(rcount,5)=0 THEN
	   dbms_application_info.set_client_info('Rows Updated := '||rcount);

	END IF;    
END LOOP;
			
IF rcount  =0 THEN
   RETURN 'No row updated' ;
ELSE
   dbms_application_info.set_client_info('Rows Updated := '||rcount);
   RETURN 'Update Completed ';
END IF;   
END;
Re: Query [message #185625 is a reply to message #185109] Wed, 02 August 2006 14:24 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
My big question is why do you want to return a message every 5 rows. It makes no sense and will slow down the updates quite a bit. Simply run

UPDATE EMP
SET sal = sal+500
WHERE sal > 1000;

When it is done, it will tell you how many it updated.

Previous Topic: decode/case
Next Topic: finding a charachter in a string
Goto Forum:
  


Current Time: Tue Dec 06 14:19:22 CST 2016

Total time taken to generate the page: 0.11468 seconds