1 / 32

More on IF statements

More on IF statements. Use speaker notes for additional information!. PL/SQL - IF. This is the code for donor2 as it appears in the editor (shown in two columns for space considerations). BEGIN SELECT yrgoal, state INTO v_yrgoal, v_state FROM donornew WHERE idno = v_idno;

yori
Download Presentation

More on IF statements

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. More on IF statements Use speaker notes for additional information!

  2. PL/SQL - IF This is the code for donor2 as it appears in the editor (shown in two columns for space considerations). BEGIN SELECT yrgoal, state INTO v_yrgoal, v_state FROM donornew WHERE idno = v_idno; IF v_state = 'MA' THEN IF v_yrgoal > 300 THEN v_newgoal := v_yrgoal * 1.3; ELSE IF v_yrgoal > 100 THEN v_newgoal := v_yrgoal * 1.2; ELSE v_newgoal := v_yrgoal * 1.1; END IF; END IF; ELSE IF v_state = 'RI' THEN IF v_yrgoal > 250 THEN v_newgoal := v_yrgoal * 1.25; ELSE v_newgoal := v_yrgoal * 1.15; END IF; ELSE v_newgoal := v_yrgoal; END IF; END IF; UPDATE donornew SET yrgoal = v_newgoal WHERE idno = v_idno; COMMIT; END; / SET VERIFY ON SET VERIFY OFF DECLARE v_idno VARCHAR2(5) :=&input_idno; v_yrgoal NUMBER(7,2); v_newgoal NUMBER(7,2); v_state VARCHAR2(2); This code uses a complex IF structure with IF statements embedded in other IF statements. The flowchart for the logic is shown on the next slide.

  3. PL/SQL - IF v_state = MA N Y v_state = RI N Y v_yrgoal > 300 v_newgoal := v_yrgoal Y N v_yrgoal > 300 N Y v_newgoal := v_yrgoal * 1.15 v_newgoal := v_yrgoal * 1.25 v_yrgoal > 100 v_newgoal := v_yrgoal * 1.3 N Y v_newgoal := v_yrgoal * 1.1 v_newgoal := v_yrgoal * 1.2

  4. PL/SQL - IF SQL> SELECT * FROM donornew; IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT ----- --------------- --------------- ---------- -- ----- --------- --------- ------------ 11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 845 John Smith 12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 440 Susan Jones 22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones 23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 158.4 Amy Costa 33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 50 John Adams 34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 63.53 Amy Costa This is the segment of code that was executed to cause donor 12121 to increase from 440 to 550. IF v_state = 'RI' THEN IF v_yrgoal > 250 THEN v_newgoal := v_yrgoal * 1.25; SQL> @ donor2 Enter value for input_idno: 12121 PL/SQL procedure successfully completed. Input truncated to 13 characters SQL> SELECT * FROM donornew; IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT ----- --------------- --------------- ---------- -- ----- --------- --------- ------------ 11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 845 John Smith 12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 550 Susan Jones 22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones 23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 158.4 Amy Costa 33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 50 John Adams 34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 63.53 Amy Costa

  5. SET VERIFY OFF DECLARE v_pay_id first_pay_new.pay_id%TYPE :=&input_pay_id; v_salary first_pay_new.salary%TYPE; v_jobcode first_pay_new.jobcode%TYPE; v_bonus first_pay_new.bonus%TYPE; v_new_bonus first_pay_new.bonus%TYPE; BEGIN SELECT salary, jobcode, bonus INTO v_salary, v_jobcode, v_bonus FROM first_pay_new WHERE pay_id = v_pay_id; IF v_jobcode = 'CI' THEN IF v_salary > 40000 THEN v_new_bonus := v_bonus + 500; ELSE v_new_bonus := v_bonus + 250; END IF; ELSE IF v_jobcode = 'IN' THEN IF v_salary > 30000 THEN v_new_bonus := v_bonus + 250; ELSE IF v_salary > 20000 THEN v_new_bonus := v_bonus + 100; ELSE v_new_bonus := v_bonus + 50; END IF; END IF; ELSE IF v_jobcode = 'AP' THEN IF v_salary > 25000 THEN v_new_bonus := v_bonus + 100; END IF; END IF; END IF; END IF; IF v_new_bonus != v_bonus THEN UPDATE first_pay_new SET bonus = v_new_bonus WHERE pay_id = v_pay_id; END IF; END; / SET VERIFY ON PL/SQL - IF This is firstpay5. It is a series of embedded IF statements some with an ELSE and some without. NOTE that there is an IF before the update to see if a new bonus was calculated. If it was, then the update is done.

  6. PL/SQL - IF SET VERIFY OFF DECLARE v_pay_id first_pay_new.pay_id%TYPE :=&input_pay_id; v_salary first_pay_new.salary%TYPE; v_jobcode first_pay_new.jobcode%TYPE; v_bonus first_pay_new.bonus%TYPE; v_new_bonus first_pay_new.bonus%TYPE; BEGIN SELECT salary, jobcode, bonus INTO v_salary, v_jobcode, v_bonus FROM first_pay_new WHERE pay_id = v_pay_id; IF v_jobcode = 'CI' THEN IF v_salary > 40000 THEN v_new_bonus := v_bonus + 500; ELSE v_new_bonus := v_bonus + 250; END IF; ELSE IF v_jobcode = 'IN' THEN IF v_salary > 30000 THEN v_new_bonus := v_bonus + 250; ELSE IF v_salary > 20000 THEN v_new_bonus := v_bonus + 100; ELSE v_new_bonus := v_bonus + 50; END IF; END IF; ELSE IF v_jobcode = 'AP' THEN IF v_salary > 25000 THEN v_new_bonus := v_bonus + 100; END IF; END IF; END IF; END IF; IF v_new_bonus != v_bonus THEN UPDATE first_pay_new SET bonus = v_new_bonus WHERE pay_id = v_pay_id; END IF; END; / SET VERIFY ON

  7. PL/SQL - IF v_jobcode = CI N Y v_salary > 40000 N Y v_jobcode = IN N Y Increase bonus by 250 Increase bonus by 500 v_jobcode = AP N Y v_salary > 25000 Y N v_salary > 30000 Increase bonus by 100 N Y Increase bonus by 250 v_salary > 20000 N Y Increase bonus by 50 Increase bonus by 100

  8. SQL> SELECT * FROM first_pay_new; PAY_ NAME JO STARTDATE SALARY BONUS ---- -------------------- -- --------- --------- --------- 1111 Linda Costa CI 15-JAN-97 47250 1000 2222 John Davidson IN 25-SEP-92 40800 1500 3333 Susan Ash AP 05-FEB-00 25250 500 4444 Stephen York CM 03-JUL-97 42420 2000 5555 Richard Jones CI 30-OCT-92 52500 2000 6666 Joanne Brown IN 18-AUG-94 49440 2000 7777 Donald Brown CI 05-NOV-99 45000 8888 Paula Adams IN 12-DEC-98 46350 2000 PL/SQL - IF This is the code that was executed to make the salary for employee 5555 increase by 500. IF v_jobcode = 'CI' THEN IF v_salary > 40000 THEN v_new_bonus := v_bonus + 500; SQL> @ firstpay5 Enter value for input_pay_id: 5555 PL/SQL procedure successfully completed. SQL> SELECT * FROM first_pay_new; PAY_ NAME JO STARTDATE SALARY BONUS ---- -------------------- -- --------- --------- --------- 1111 Linda Costa CI 15-JAN-97 47250 1000 2222 John Davidson IN 25-SEP-92 40800 1500 3333 Susan Ash AP 05-FEB-00 25250 500 4444 Stephen York CM 03-JUL-97 42420 2000 5555 Richard Jones CI 30-OCT-92 52500 2500 6666 Joanne Brown IN 18-AUG-94 49440 2000 7777 Donald Brown CI 05-NOV-99 45000 8888 Paula Adams IN 12-DEC-98 46350 2000

  9. PL/SQL - IF SQL> @ firstpay5 Enter value for input_pay_id: 7777 PL/SQL procedure successfully completed. SQL> SELECT * FROM first_pay_new; PAY_ NAME JO STARTDATE SALARY BONUS ---- -------------------- -- --------- --------- --------- 1111 Linda Costa CI 15-JAN-97 47250 1000 2222 John Davidson IN 25-SEP-92 40800 1500 3333 Susan Ash AP 05-FEB-00 25250 500 4444 Stephen York CM 03-JUL-97 42420 2000 5555 Richard Jones CI 30-OCT-92 52500 2500 6666 Joanne Brown IN 18-AUG-94 49440 2000 7777 Donald Brown CI 05-NOV-99 45000 8888 Paula Adams IN 12-DEC-98 46350 2000 SQL> @ firstpay5 Enter value for input_pay_id: 3333 PL/SQL procedure successfully completed. SQL> SELECT * FROM first_pay_new; PAY_ NAME JO STARTDATE SALARY BONUS ---- -------------------- -- --------- --------- --------- 1111 Linda Costa CI 15-JAN-97 47250 1000 2222 John Davidson IN 25-SEP-92 40800 1500 3333 Susan Ash AP 05-FEB-00 25250 600 4444 Stephen York CM 03-JUL-97 42420 2000 5555 Richard Jones CI 30-OCT-92 52500 2500 6666 Joanne Brown IN 18-AUG-94 49440 2000 7777 Donald Brown CI 05-NOV-99 45000 8888 Paula Adams IN 12-DEC-98 46350 2000 Bonus was null so no activity. Bonus was increased by 100 because v_jobcode = AP and v_salary > 25000.

  10. PL/SQL - IF SQL> edit donor3 SET VERIFY OFF DECLARE v_idno VARCHAR2(5) :=&input_idno; v_yrgoal NUMBER(7,2); v_newgoal NUMBER(7,2); v_state VARCHAR2(2); BEGIN SELECT yrgoal, state INTO v_yrgoal, v_state FROM donornew WHERE idno = v_idno; IF v_yrgoal > 250 AND v_state = 'MA' THEN v_newgoal := v_yrgoal * 1.1 ; ELSE v_newgoal := v_yrgoal * 1.2; END IF; UPDATE donornew SET yrgoal = v_newgoal WHERE idno = v_idno; END; / SET VERIFY ON This is an example of a compound AND.

  11. PL/SQL - Compound AND v_yrgoal > 250 N Y v_state = MA N Y Increase goal by 20% Increase goal by 20% Increase goal by 10% IF v_yrgoal > 250 AND v_state = 'MA' THEN v_newgoal := v_yrgoal * 1.1; ELSE v_newgoal := v_yrgoal * 1.2; END IF;

  12. PL/SQL - Compound AND SQL> SELECT * FROM donornew; IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT ----- --------------- --------------- ---------- -- ----- --------- --------- ------------ 11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 845 John Smith 12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 550 Susan Jones 22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones 23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 158.4 Amy Costa 33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 50 John Adams 34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 63.53 Amy Costa SQL> @ donor3 Enter value for input_idno: 23456 PL/SQL procedure successfully completed. SQL> SELECT * FROM donornew; IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT ----- --------------- --------------- ---------- -- ----- --------- --------- ------------ 11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 845 John Smith 12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 550 Susan Jones 22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones 23456Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 190.08 Amy Costa 33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 50 John Adams 34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 63.53 Amy Costa Before After Since goal was not greater than 250 the ELSE was executed and the goal was increased by 20%. IF v_yrgoal > 250 AND v_state = 'MA' THEN v_newgoal := v_yrgoal * 1.1; ELSE v_newgoal := v_yrgoal * 1.2; END IF;

  13. PL/SQL - Compound AND SQL> edit firstpay6 SET VERIFY OFF DECLARE v_pay_id first_pay_new.pay_id%TYPE :=&input_pay_id; v_new_sal first_pay_new.salary%TYPE; v_jobcode first_pay_new.jobcode%TYPE; v_bonus first_pay_new.bonus%TYPE; BEGIN SELECT salary, jobcode, bonus INTO v_new_sal, v_jobcode, v_bonus FROM first_pay_new WHERE pay_id = v_pay_id; IF v_jobcode = 'CI' AND v_bonus > 1000 THEN v_new_sal := v_new_sal * 1.05; ELSE v_new_sal := v_new_sal * 1.02; END IF; UPDATE first_pay_new SET salary = v_new_sal WHERE pay_id = v_pay_id; END; / SET VERIFY ON

  14. PL/SQL - Compound AND v_jobcode=CI N Y v_bonus > 1000 N Y Increase salary by 2% Increase salary by 2% Increase salary by 5% IF v_jobcode = 'CI' AND v_bonus > 1000 THEN v_new_sal := v_new_sal * 1.05; ELSE v_new_sal := v_new_sal * 1.02; END IF;

  15. SQL> SELECT * FROM first_pay_new; PAY_ NAME JO STARTDATE SALARY BONUS ---- -------------------- -- --------- --------- --------- 1111 Linda Costa CI 15-JAN-97 47250 1000 2222 John Davidson IN 25-SEP-92 40800 1500 3333 Susan Ash AP 05-FEB-00 25250 600 4444 Stephen York CM 03-JUL-97 42420 2000 5555Richard Jones CI 30-OCT-92 525002500 6666 Joanne Brown IN 18-AUG-94 49440 2000 7777 Donald Brown CI 05-NOV-99 45000 8888 Paula Adams IN 12-DEC-98 46350 2000 PL/SQL - Compound AND SQL> @ firstpay6 Enter value for input_pay_id: 1111 PL/SQL procedure successfully completed. SQL> @ firstpay6 Enter value for input_pay_id: 2222 PL/SQL procedure successfully completed. SQL> @ firstpay6 Enter value for input_pay_id: 5555 PL/SQL procedure successfully completed. SQL> SELECT * FROM first_pay_new; PAY_ NAME JO STARTDATE SALARY BONUS ---- -------------------- -- --------- --------- --------- 1111Linda Costa CI 15-JAN-97 48195 1000 2222 John Davidson IN 25-SEP-92 41616 1500 3333 Susan Ash AP 05-FEB-00 25250 600 4444 Stephen York CM 03-JUL-97 42420 2000 5555 Richard Jones CI 30-OCT-92 55125 2500 6666 Joanne Brown IN 18-AUG-94 49440 2000 7777 Donald Brown CI 05-NOV-99 45000 8888 Paula Adams IN 12-DEC-98 46350 2000

  16. PL/SQL - Compound OR SQL> edit donor3a SET VERIFY OFF DECLARE v_idno VARCHAR2(5) :=&input_idno; v_yrgoal NUMBER(7,2); v_newgoal NUMBER(7,2); v_state VARCHAR2(2); BEGIN SELECT yrgoal, state INTO v_yrgoal, v_state FROM donornew WHERE idno = v_idno; IF v_yrgoal > 250 OR v_state = 'MA' THEN v_newgoal := v_yrgoal * 1.1 ; ELSE v_newgoal := v_yrgoal * 1.2; END IF; UPDATE donornew SET yrgoal = v_newgoal WHERE idno = v_idno; END; / SET VERIFY ON

  17. PL/SQL - Compound OR v_yrgoal > 250 N Y Increase goal by 10% v_state = MA N Y Increase goal by 20% Increase goal by 10% IF v_yrgoal > 250 OR v_state = 'MA' THEN v_newgoal := v_yrgoal * 1.1; ELSE v_newgoal := v_yrgoal * 1.2; END IF;

  18. PL/SQL - Compound OR SQL> SELECT * FROM donornew; IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT ----- --------------- --------------- ---------- -- ----- --------- --------- ------------ 11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 845 John Smith 12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 550 Susan Jones 22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones 23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 190.08 Amy Costa 33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 50 John Adams 34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 63.53 Amy Costa SQL> @ donor3a Enter value for input_idno: 12121 PL/SQL procedure successfully completed. SQL> @ donor3a Enter value for input_idno: 33333 PL/SQL procedure successfully completed. SQL> SELECT * FROM donornew; IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT ----- --------------- --------------- ---------- -- ----- --------- --------- ------------ 11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 845 John Smith 12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 605Susan Jones 22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones 23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 190.08 Amy Costa 33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 55 John Adams 34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 63.53 Amy Costa

  19. PL/SQL - Compound OR SQL> edit firstpay7 SET VERIFY OFF DECLARE v_pay_id first_pay_new.pay_id%TYPE :=&input_pay_id; v_new_sal first_pay_new.salary%TYPE; v_jobcode first_pay_new.jobcode%TYPE; v_bonus first_pay_new.bonus%TYPE; BEGIN SELECT salary, jobcode, bonus INTO v_new_sal, v_jobcode, v_bonus FROM first_pay_new WHERE pay_id = v_pay_id; IF v_jobcode = 'CI' OR v_bonus > 1000 THEN v_new_sal := v_new_sal * 1.05; ELSE v_new_sal := v_new_sal * 1.02; END IF; UPDATE first_pay_new SET salary = v_new_sal WHERE pay_id = v_pay_id; END; / SET VERIFY ON

  20. SQL> SELECT * FROM first_pay_new; PAY_ NAME JO STARTDATE SALARY BONUS ---- -------------------- -- --------- --------- --------- 1111 Linda Costa CI 15-JAN-97 48195 1000 2222 John Davidson IN 25-SEP-92 41616 1500 3333 Susan Ash AP 05-FEB-00 25250 600 4444 Stephen York CM 03-JUL-97 42420 2000 5555 Richard Jones CI 30-OCT-92 55125 2500 6666 Joanne Brown IN 18-AUG-94 49440 2000 7777 Donald Brown CI 05-NOV-99 45000 8888 Paula Adams IN 12-DEC-98 46350 2000 PL/SQL - Compound OR SQL> @firstpay7 Enter value for input_pay_id: 3333 PL/SQL procedure successfully completed. SQL> @firstpay7 Enter value for input_pay_id: 1111 PL/SQL procedure successfully completed. SQL> @firstpay7 Enter value for input_pay_id: 6666 PL/SQL procedure successfully completed. SQL> SELECT * FROM first_pay_new; PAY_ NAME JO STARTDATE SALARY BONUS ---- -------------------- -- --------- --------- --------- 1111 Linda Costa CI 15-JAN-97 50604.75 1000 2222 John Davidson IN 25-SEP-92 41616 1500 3333 Susan Ash AP 05-FEB-00 25755 600 4444 Stephen York CM 03-JUL-97 42420 2000 5555 Richard Jones CI 30-OCT-92 55125 2500 6666Joanne Brown IN 18-AUG-94 51912 2000 7777 Donald Brown CI 05-NOV-99 45000 8888 Paula Adams IN 12-DEC-98 46350 2000

  21. PL/SQL - compound AND / OR SQL> edit donor3b SET VERIFY OFF DECLARE v_idno VARCHAR2(5) :=&input_idno; v_yrgoal donornew.yrgoal%TYPE; v_newgoal donornew.yrgoal%TYPE; v_state donornew.state%TYPE; v_datefst donornew.datefst%TYPE; BEGIN SELECT yrgoal, state, datefst INTO v_yrgoal, v_state, v_datefst FROM donornew WHERE idno = v_idno; IF v_state = 'MA' AND (v_yrgoal < 100 OR v_datefst > '01-JAN-98') THEN v_newgoal := v_yrgoal * 1.1 ; ELSE v_newgoal := v_yrgoal; END IF; UPDATE donornew SET yrgoal = v_newgoal WHERE idno = v_idno; END; / SET VERIFY ON

  22. PL/SQL - compound AND / OR v_state=MA N Y v_yrgoal < 100 N Y No change to goal Increase goal by 10% v_datefst > 01-JAN-98 N Y No change to goal Increase goal by 10% IF v_state = 'MA' AND (v_yrgoal < 100 OR v_datefst > '01-JAN-98') THEN v_newgoal := v_yrgoal * 1.1; ELSE v_newgoal := v_yrgoal;

  23. SQL> SELECT * FROM donornew; IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT ----- --------------- --------------- ---------- -- ----- --------- --------- ------------ 11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 845 John Smith 12121Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 605 Susan Jones 22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones 23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 190.08 Amy Costa 33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 55 John Adams 34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 63.53 Amy Costa SQL> @ donor3b Enter value for input_idno: 33333 PL/SQL procedure successfully completed. SQL> @ donor3b Enter value for input_idno: 12121 PL/SQL procedure successfully completed. SQL> @ donor3b Enter value for input_idno: 23456 PL/SQL procedure successfully completed. SQL> SELECT * FROM donornew; IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT ----- --------------- --------------- ---------- -- ----- --------- --------- ------------ 11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 845 John Smith 12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 605 Susan Jones 22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones 23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 190.08 Amy Costa 33333Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 60.5 John Adams 34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 63.53 Amy Costa Record 33333 met the criteria and a change was made to the goal. Records 12121 and 23456 did not meet the criteria.

  24. PL/SQL - compound AND / OR SQL> edit donor3c SET VERIFY OFF DECLARE v_idno VARCHAR2(5) :=&input_idno; v_yrgoal donornew.yrgoal%TYPE; v_newgoal donornew.yrgoal%TYPE; v_state donornew.state%TYPE; v_datefst donornew.datefst%TYPE; BEGIN SELECT yrgoal, state, datefst INTO v_yrgoal, v_state, v_datefst FROM donornew WHERE idno = v_idno; IF v_state = 'MA' AND v_yrgoal < 100 OR v_datefst > '01-JAN-98' THEN v_newgoal := v_yrgoal * 1.1 ; ELSE v_newgoal := v_yrgoal; END IF; UPDATE donornew SET yrgoal = v_newgoal WHERE idno = v_idno; END; / SET VERIFY ON

  25. PL/SQL - compound AND / OR v_state=MA N Y v_datefst > 01-JAN-98 N Y v_yrgoal < 100 N Y No change to goal Increase goal by 10% Increase goal by 10% v_datefst > 01-JAN-98 N Y No change to goal Increase goal by 10% IF v_state = 'MA' AND v_yrgoal < 100 OR v_datefst > '01-JAN-98' THEN v_newgoal := v_yrgoal * 1.1; ELSE v_newgoal := v_yrgoal; END IF;

  26. PL/SQL - compound AND / OR v_datefst > 01-JAN-98 N Y Increase goal by 10% v_state=MA N Y No change to goal v_yrgoal < 100 N Y Increase goal by 10% No change to goal IF v_datefst > '01-JAN-98’ OR v_state = 'MA' AND v_yrgoal < 100 THEN v_newgoal := v_yrgoal * 1.1; ELSE v_newgoal := v_yrgoal; END IF;

  27. PL/SQL - compound AND / OR SQL> SELECT * FROM donornew; IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT ----- --------------- --------------- ---------- -- ----- --------- --------- ------------ 11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 845 John Smith 12121Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 605 Susan Jones 22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones 23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 190.08 Amy Costa 33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 60.5 John Adams 34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 63.53 Amy Costa SQL> @ donor3c Enter value for input_idno: 12121 PL/SQL procedure successfully completed. SQL> @ donor3c Enter value for input_idno: 33333 PL/SQL procedure successfully completed. SQL> SELECT * FROM donornew; IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT ----- --------------- --------------- ---------- -- ----- --------- --------- ------------ 11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 845 John Smith 12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 605 Susan Jones 22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones 23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 190.08 Amy Costa 33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 66.55 John Adams 34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 63.53 Amy Costa

  28. PL/SQL - null SQL> edit donor3d SET VERIFY OFF DECLARE v_idno VARCHAR2(5) :=&input_idno; v_yrgoal donornew.yrgoal%TYPE; v_newgoal donornew.yrgoal%TYPE; v_state donornew.state%TYPE; BEGIN SELECT yrgoal, state INTO v_yrgoal, v_state FROM donornew WHERE idno = v_idno; IF v_state = 'RI' AND (v_yrgoal < 100 OR v_yrgoal IS NULL) THEN v_newgoal := 100; ELSE v_newgoal := v_yrgoal; END IF; UPDATE donornew SET yrgoal = v_newgoal WHERE idno = v_idno; END; / SET VERIFY ON

  29. PL/SQL - null SQL> SELECT * FROM donornew; IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT ----- --------------- --------------- ---------- -- ----- --------- --------- ------------ 11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 845 John Smith 12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 605 Susan Jones 22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones 23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 190.08 Amy Costa 33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 66.55 John Adams 34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 63.53 Amy Costa Record 22222 is for RI and the yrgoal is null so it meets the criteria and the change is made. SQL> @ donor3d Enter value for input_idno: 22222 PL/SQL procedure successfully completed. SQL> SELECT * FROM donornew; IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT ----- --------------- --------------- ---------- -- ----- --------- --------- ----------- 11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 845 John Smith 12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 605 Susan Jones 22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 100 Susan Jones 23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 190.08 Amy Costa 33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 66.55 John Adams 34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 63.53 Amy Costa

  30. PL/SQL - compound SQL> edit firstpay8 SET VERIFY OFF DECLARE v_pay_id first_pay_new.pay_id%TYPE :=&input_pay_id; v_new_sal first_pay_new.salary%TYPE; v_jobcode first_pay_new.jobcode%TYPE; v_bonus first_pay_new.bonus%TYPE; BEGIN SELECT salary, jobcode, bonus INTO v_new_sal, v_jobcode, v_bonus FROM first_pay_new WHERE pay_id = v_pay_id; IF v_jobcode = 'CI' AND v_bonus > 1500 OR v_jobcode = 'IN' AND v_bonus > 1000 THEN v_new_sal := v_new_sal * 1.05; END IF; UPDATE first_pay_new SET salary = v_new_sal WHERE pay_id = v_pay_id; END; / SET VERIFY ON

  31. PL/SQL - compound v_jobcode=CI N Y v_jobcode=IN v_bonus > 1500 N Y N Y v_bonus > 1000 N Y Increase salary by 5% Increase salary by 5% IF v_jobcode = 'CI' AND v_bonus > 1500 OR v_jobcode = 'IN' AND v_bonus > 1000 THEN v_new_sal := v_new_sal * 1.05; END IF;

  32. SQL> SELECT * FROM first_pay_new; PAY_ NAME JO STARTDATE SALARY BONUS ---- -------------------- -- --------- --------- --------- 1111 Linda Costa CI 15-JAN-97 50604.75 1000 2222 John Davidson IN 25-SEP-92 41616 1500 3333 Susan Ash AP 05-FEB-00 25755 600 4444 Stephen York CM 03-JUL-97 42420 2000 5555 Richard Jones CI 30-OCT-92 55125 2500 6666 Joanne Brown IN 18-AUG-94 51912 2000 7777 Donald Brown CI 05-NOV-99 45000 8888 Paula Adams IN 12-DEC-98 46350 2000 PL/SQL - compound SQL> @ firstpay8 Enter value for input_pay_id: 1111 PL/SQL procedure successfully completed. SQL> @ firstpay8 Enter value for input_pay_id: 5555 PL/SQL procedure successfully completed. SQL> @ firstpay8 Enter value for input_pay_id: 2222 PL/SQL procedure successfully completed. SQL> @ firstpay8 Enter value for input_pay_id: 8888 PL/SQL procedure successfully completed. SQL> SELECT * FROM first_pay_new; PAY_ NAME JO STARTDATE SALARY BONUS ---- -------------------- -- --------- --------- --------- 1111 Linda Costa CI 15-JAN-97 50604.75 1000 2222 John Davidson IN 25-SEP-92 43696.8 1500 3333 Susan Ash AP 05-FEB-00 25755 600 4444 Stephen York CM 03-JUL-97 42420 2000 5555 Richard Jones CI 30-OCT-92 57881.25 2500 6666 Joanne Brown IN 18-AUG-94 51912 2000 7777 Donald Brown CI 05-NOV-99 45000 8888 Paula Adams IN 12-DEC-98 48667.5 2000

More Related