SHARE
TWEET

Untitled

a guest Jun 27th, 2017 136 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DECLARE
  2. pnum p.pno%type;
  3. p_name p.pname%type;
  4. snum s.sno%type;
  5. s_name s.sname%type := ' ';
  6. s_tempName s.sname%type := ' ';
  7. s_lastKnownValue s.sname%type := ' ';
  8. outputname varchar2(100):= ' ';
  9. cursor getP IS SELECT pno,pname from p;
  10. cursor getsno (pnum p.pno%type ) IS SELECT sno from sp where pno = pnum;
  11. cursor getsupp (snum s.sno%type) IS SELECT sname from s WHERE sno = snum;
  12.  
  13. BEGIN
  14.  
  15. open getP;
  16.  
  17. loop
  18.  
  19. outputname := ' ';
  20. fetch getP into pnum,p_name;
  21. exit when getP%NOTFOUND;
  22. dbms_output.put_line (pnum ||' is a ' || p_name);
  23. dbms_output.put_line ('----------------------------------');
  24.  
  25. open getsno(pnum);
  26. open getsupp(snum);
  27.  
  28. loop
  29.  
  30. fetch getsno into snum;
  31. fetch getsupp into s_tempName;
  32.  
  33. exit when getsupp%NOTFOUND;
  34.  
  35. if (s_name <> ' ') then
  36. outputname := outputname || ' , '|| s_name;
  37. dbms_output.put_line('s_name is in the first if statement ' || s_name);
  38. end if;
  39.  
  40. fetch getsupp into s_name;
  41.  
  42. if getsupp%NOTFOUND then s_lastKnownValue := s_tempName;
  43. exit;
  44. else
  45. outputname := outputname || ' , '|| s_tempName;
  46. dbms_output.put_line('s_tempn=Name is in the else statement ' || s_tempName);
  47.  
  48. s_lastKnownValue := s_name;
  49.  
  50. end if;
  51.  
  52.  
  53. end loop;
  54. close getsno;
  55. close getsupp;
  56.  
  57. outputname := outputname || ' AND '||s_lastKnownValue;
  58. dbms_output.put_line(outputname);
  59.  
  60. end loop;
  61.  
  62. close getP;
  63.  
  64. END;
  65. /
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top