RashedBQ

Implicit cursor and exception

Dec 7th, 2021
890
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. INSERT ALL
  2. INTO STUDENT VALUES (1,'AHMED',3.5)
  3. INTO STUDENT VALUES (2,'ALI',4.5)
  4. INTO STUDENT VALUES (3,'SALEH',4.0)
  5. SELECT * FROM DUAL;
  6. /*
  7.  
  8. )INSERT, UPDATE OR DELETE ( DML جملة تنفيذ عند تلقائيا انشاءها يتم: Implicit cursor
  9.  
  10. بعد انشاء جدول للطالب واضافة بيانات 3 طالب، سيتم التعديل على معدالت الطالب الذين معدالتهم اكبر من او تساوي
  11. 4 ،وباستخدام خصائص الكورسر الضمني )attributes cursor implicit )سيتم التحقق من ان هناك طالب تم تغيير
  12. معدالتهم ومن ثم طباعة عددهم او طباعه رسالة انه ال يوجد أي تعديل تم
  13.  
  14. */
  15.  
  16.  
  17. DECLARE
  18. STD_NO NUMBER;
  19. BEGIN
  20. UPDATE STUDENT
  21. SET GRADE = GRADE+0.1
  22. WHERE GRADE >=4;
  23. IF SQL%FOUND THEN
  24. STD_NO := SQL%ROWCOUNT;
  25. DBMS_OUTPUT.PUT_LINE('THERE ARE '||STD_NO||' STUDENT WHOES GARDES HAVE
  26. CHANGED' );
  27. ELSIF SQL%NOTFOUND THEN
  28. DBMS_OUTPUT.PUT_LINE('THERE IS NO CHANGE HAPPEN ');
  29. END IF;
  30. END;
  31. --############################
  32.  
  33.  
  34. /*
  35. help in Exception
  36. */
  37.  
  38. DECLARE
  39.  Declaration section
  40. BEGIN
  41.  EXCEPTION section
  42. EXCEPTION
  43. WHEN NO_DATA_FOUND THEN
  44.  -Error handling statements
  45. WHEN DUP_VAL_ON_INDEX THEN
  46.  -Error handling statements
  47.  WHEN TOO_MANY_ROWS THEN
  48.  -Error handling statements
  49. WHEN OTHERS THEN
  50.  -Error handling statements
  51. END;
  52.  
  53. --ex
  54.  
  55. DECLARE
  56. N EMPLOYEES.LAST_NAME%TYPE;
  57. S EMPLOYEES.SALARY%TYPE;
  58. I EMPLOYEES.EMPLOYEE_ID%TYPE:=:EMPLOYEE_ID;
  59. BEGIN
  60. SELECT LAST_NAME, SALARY INTO N,S
  61. FROM EMPLOYEES
  62. WHERE EMPLOYEE_ID = I;
  63. DBMS_OUTPUT.PUT_LINE('NAME: '||N);
  64. DBMS_OUTPUT.PUT_LINE('SALARY: '||S);
  65. EXCEPTION
  66. WHEN NO_DATA_FOUND THEN
  67. DBMS_OUTPUT.PUT_LINE(' EMPLOYEE ID IS NOT CORRECT');
  68. END;
  69.  
  70.  
  71. --############################
  72.  
  73. --unamed excption
  74.  
  75.  
  76. DECLARE
  77. parent_key_not_found EXCEPTION;
  78. PRAGMA EXCEPTION_INIT (parent_key_not_found,-02291);
  79. BEGIN
  80. INSERT INTO std VALUES (112,'ahmed',2);
  81. EXCEPTION
  82. WHEN parent_key_not_found THEN
  83. DBMS_OUTPUT.put_line('department not found');
  84. WHEN DUP_VAL_ON_INDEX THEN
  85. DBMS_OUTPUT.put_line('student id is already used');
  86. WHEN OTHERS THEN
  87. DBMS_OUTPUT.put_line('ERROR...');
  88.  
  89. END;
  90.  
  91. --############################
  92. -- User defined exception
  93.  
  94.  
  95. DECLARE
  96. M STDD.MARK%TYPE:=:MARK;
  97. MARK_CHECK EXCEPTION;
  98. BEGIN
  99. IF M NOT BETWEEN 0 AND 100 THEN
  100. RAISE MARK_CHECK;
  101. END IF;
  102. UPDATE STDD SET MARK = M
  103. WHERE SID =111;
  104. EXCEPTION
  105. WHEN MARK_CHECK THEN
  106. DBMS_OUTPUT.put_line('MARK MUST BE BETWEEN 0 AND 100');
  107. END;
  108.  
  109.  
  110. --################################
RAW Paste Data