Home » SQL & PL/SQL » SQL & PL/SQL » Can you let me know the difference between two anonymous blocks (merged 3)
Can you let me know the difference between two anonymous blocks (merged 3) [message #402820] Tue, 12 May 2009 11:22 Go to next message
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 #402823 is a reply to message #402820] Tue, 12 May 2009 11:28 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
In Block 2 no values ever get into the variables; no FETCH & no assignment.
Re: Can you let me know the difference between two anonymous blocks [message #402824 is a reply to message #402820] Tue, 12 May 2009 11:37 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Block 1 does not work fine.

Block 1 updates ALL employees to dept_id 60 if the first employee found by the cursor is Donald, otherwise it does nothing.

Block 2 does nothing.

Updates generally need where clauses.
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 Go to previous messageGo to next message
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 (merged 3) [message #402830 is a reply to message #402820] Tue, 12 May 2009 12:08 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
PL/SQL is not required to do what you claim you desire to be done.
The solution can be done with plain SQL.
Why do you insist on complicating the solution with PL/SQL?
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 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 (merged 3) [message #402832 is a reply to message #402830] Tue, 12 May 2009 12:13 Go to previous messageGo to next message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Yes, I know that PL/SQL is not required but suppose if I have multiple conditions I need to use PL/SQL. I thought of adding some more conditions once I can get ahead of this problem and hence used PL/SQL.
Re: Can you let me know the difference between two anonymous blocks (merged 3) [message #402833 is a reply to message #402820] Tue, 12 May 2009 12:21 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>if I have multiple conditions I need to use PL/SQL.
Yes, no, maybe.
Sooner or later you'll learn the error of your approach.
With more "conditions", you will need to make more changes to SQL.
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: Can you let me know the difference between two anonymous blocks [message #403049 is a reply to message #402892] Wed, 13 May 2009 10:14 Go to previous message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Thank you all for your pieces of advice and suggestions. I am very greatful to all of you.
Previous Topic: dropping the tables
Next Topic: number of tables refering a particular column
Goto Forum:
  


Current Time: Sat Dec 10 18:53:26 CST 2016

Total time taken to generate the page: 0.23768 seconds