Can you let me know the difference between two anonymous blocks (merged 3) [message #402820] |
Tue, 12 May 2009 11:22  |
vamsikgummadi
Messages: 62 Registered: April 2006 Location: United States, MI
|
Member |
|
|
Hi,
Suppose I need to update the department of an employee to department_id=60 when his last_name ='Donald' and Salary>2500 then I write an anonymous block like:
--Block1: (This works fine)
DECLARE
v_lastname employees1.last_name%TYPE;
v_salary employees1.salary%TYPE;
v_department_id employees1.department_id%TYPE := 60;
CURSOR c1
IS
SELECT last_name, salary
FROM employees1;
BEGIN
OPEN c1;
FETCH c1
INTO v_lastname, v_salary;
CLOSE c1;
IF v_lastname = 'Donald' AND v_salary > 2500
THEN
UPDATE employees1
SET employees1.department_id = v_department_id;
END IF;
DBMS_OUTPUT.put_line ('updated');
COMMIT;
END;
--Block2:( Though it compiles successfully it does not perform
--the required action)
DECLARE
v_lastname employees1.last_name%TYPE;
v_salary employees1.salary%TYPE;
v_department_id NUMBER;
BEGIN
IF v_lastname = 'Donald' AND v_salary >= 2500
THEN
v_department_id := 60;
UPDATE employees1
SET department_id = v_department_id where v_lastname='Donald' and v_salary>2500;
END IF;
DBMS_OUTPUT.put_line ('updated');
COMMIT;
END;
Can anyone of you let me know the difference in the execution of the two blocks or where I am getting confused. It would be of great help if you could assist me. Thank you in anticipation.
Vamsi.
|
|
|
|
|
Re: Can you let me know the difference between two anonymous blocks [message #402827 is a reply to message #402824] |
Tue, 12 May 2009 11:55   |
vamsikgummadi
Messages: 62 Registered: April 2006 Location: United States, MI
|
Member |
|
|
Quote: | Block 1 does not work fine.
|
Block 1 updates employees1 table where last_name='Donald' and Salary>2500 by setting the department_id=60
Quote: | Block 1 updates ALL employees to dept_id 60 if the first employee found by the cursor is Donald, otherwise it does nothing.
|
and salary>2500 because I have used
IF v_lastname = 'Donald' AND v_salary > 2500
THEN
UPDATE employees1
SET employees1.department_id = v_department_id ;
END IF;
Quote: | Block 2 does nothing.
|
Yes, this is what I mean to say. It is doing nothing while a similar kind of code works fine though there is no use of cursor in this code.
DECLARE
v_deptno employees1.department_id%TYPE;
v_bonus NUMBER (6, 2);
v_employee_id employees1.employee_id%TYPE := 202;
BEGIN
IF v_deptno = 201
THEN
v_bonus := 3000;
ELSIF v_deptno = 204
THEN
v_bonus := 2000;
ELSE
v_bonus := 1000;
END IF;
UPDATE employees1
SET salary = salary + v_bonus
WHERE employee_id = v_employee_id;
--COMMIT;
END;
Quote: | Updates generally need where clauses.
|
O.K point taken. But I have included the where clause in the update statement of the second block.
Can you explain where and why I am going wrong or getting confused?
|
|
|
|
Re: Can you let me know the difference between two anonymous blocks [message #402831 is a reply to message #402827] |
Tue, 12 May 2009 12:10   |
cookiemonster
Messages: 13964 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
vamsikgummadi wrote on Tue, 12 May 2009 17:55 | Quote: | Block 1 does not work fine.
|
Block 1 updates employees1 table where last_name='Donald' and Salary>2500 by setting the department_id=60
|
Only if there's only 1 record in the employees1 table, assuming there's more than one it does what I said.
Quote: |
Quote: | Block 1 updates ALL employees to dept_id 60 if the first employee found by the cursor is Donald, otherwise it does nothing.
|
and salary>2500 because I have used
IF v_lastname = 'Donald' AND v_salary > 2500
THEN
UPDATE employees1
SET employees1.department_id = v_department_id ;
END IF;
|
I skipped the salary clause but the point remains. The update has no where clause so it'll either update every employee or none depending on whether or not the IF statement is true.
Which doesn't correspond to this:
Quote: |
Suppose I need to update the department of an employee to department_id=60 when his last_name ='Donald' and Salary>2500
|
Quote: |
Quote: | Block 2 does nothing.
|
Yes, this is what I mean to say. It is doing nothing while a similar kind of code works fine though there is no use of cursor in this code.
DECLARE
v_deptno employees1.department_id%TYPE;
v_bonus NUMBER (6, 2);
v_employee_id employees1.employee_id%TYPE := 202;
BEGIN
IF v_deptno = 201
THEN
v_bonus := 3000;
ELSIF v_deptno = 204
THEN
v_bonus := 2000;
ELSE
v_bonus := 1000;
END IF;
UPDATE employees1
SET salary = salary + v_bonus
WHERE employee_id = v_employee_id;
--COMMIT;
END;
|
Since you haven't supplied a vaule for v_deptno that's logically equivalent to:
DECLARE
v_employee_id employees1.employee_id%TYPE := 202;
BEGIN
UPDATE employees1
SET salary = salary + 1000
WHERE employee_id = v_employee_id;
--COMMIT;
END;
Did you commit the change (since you've commented the commit out) and are you sure you've got an employees record with that id?
Quote: |
Quote: | Updates generally need where clauses.
|
O.K point taken. But I have included the where clause in the update statement of the second block.
Can you explain where and why I am going wrong or getting confused?
|
Generally speaking:
1) you're not specifying which records you want to update.
2) You're not supplying values for all necessary variables.
|
|
|
|
|
Re: Can you let me know the difference between two anonymous blocks [message #402838 is a reply to message #402831] |
Tue, 12 May 2009 12:52   |
vamsikgummadi
Messages: 62 Registered: April 2006 Location: United States, MI
|
Member |
|
|
O.K now I understand and modified the BLOCK 1 to
DECLARE
v_lastname employees1.last_name%TYPE;
v_salary employees1.salary%TYPE;
v_department_id employees1.department_id%TYPE := 60;
CURSOR c1
IS
SELECT last_name, salary
FROM employees1;
BEGIN
OPEN c1;
FETCH c1
INTO v_lastname, v_salary;
CLOSE c1;
IF v_lastname = 'Donald' AND v_salary > 2500
THEN
UPDATE employees1
SET employees1.department_id = v_department_id where last_name='Donald' and salary>2500;--I guess better to use a primary key in WHERE clause.
END IF;
DBMS_OUTPUT.put_line ('updated');
COMMIT;
END;
Thank you. I realised where I went wrong.
Can I rewrite the same logic without the use of cursor and using if- then conditional statements....?
I got your point on
DECLARE
v_deptno employees1.department_id%TYPE;
v_bonus NUMBER (6, 2);
v_employee_id employees1.employee_id%TYPE := 202;
BEGIN
IF v_deptno = 201
THEN
v_bonus := 3000;
ELSIF v_deptno = 204
THEN
v_bonus := 2000;
ELSE
v_bonus := 1000;
END IF;
UPDATE employees1
SET salary = salary + v_bonus
WHERE employee_id = v_employee_id;
--COMMIT;
END;
and thank you for the clarification.
|
|
|
Re: Can you let me know the difference between two anonymous blocks [message #402892 is a reply to message #402838] |
Wed, 13 May 2009 00:13   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
You fetch a completely random record from your table and let your IF-THEN logic depend on that.
If you do this to learn PL/SQL, read up on cursor loops.
Try to update only the record you just fetched (since that is the one you checked with your IF condition)
If this is not for learning PL/SQL, then strip it all down to your current update-statement (in the first block). It does exactly what you want and you don't need the surrounding part.
|
|
|
|