Advertisement
RashedBQ

good luck in PL/SQL

Oct 5th, 2021
244
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2. /*
  3.  
  4. 1- DECLARE the cursor in the declaration section.
  5. * CURSOR cursor_name IS select_statement;
  6.  
  7. 2- OPEN the cursor in the Execution Section.
  8. * OPEN cursor_name;
  9.  
  10. 3- FETCH the data from cursor into PL/SQL variables or records in the Execution
  11. Section.
  12. * FETCH cursor_name INTO VARIABLES|RECORD;
  13.  
  14. 4- CLOSE the cursor in the Execution Section before you end the PL/SQL Block.
  15. * CLOSE cursor_name;
  16.  
  17.  
  18.  
  19.  
  20.  
  21.  
  22.  
  23.  
  24.  
  25. */
  26.  
  27.  
  28.  
  29.  
  30.  
  31.  
  32.  
  33. --             show the first row
  34. --################################################
  35. DECLARE
  36. CURSOR emp_cur IS
  37. SELECT employee_id,last_name,salary
  38. FROM employees;
  39.  i employees.employee_id%TYPE;
  40.  n employees.last_name%TYPE;
  41.  s employees.salary%TYPE;
  42. BEGIN
  43. OPEN emp_cur;
  44. FETCH emp_cur INTO i,n,s;
  45. CLOSE emp_cur;
  46. DBMS_OUTPUT.put_line('ID: '||i);
  47. DBMS_OUTPUT.put_line('NAME: '||n);
  48. DBMS_OUTPUT.put_line('SALARY: '||s);
  49. END;
  50. --------------------------------------------------
  51.  
  52.  
  53. --                  while loop
  54. --################################################
  55. DECLARE
  56. CURSOR emp_all IS
  57.         SELECT employee_id,first_name,salary,job_id
  58.         FROM employees
  59.         ORDER BY salary ASC;
  60.        
  61.         emp_info emp_all%ROWTYPE;
  62.  
  63. BEGIN
  64. OPEN emp_all;
  65.  
  66. FETCH emp_all INTO emp_info;
  67. WHILE emp_all%found
  68. LOOP
  69.  
  70. DBMS_OUTPUT.put_line('EMP ID: '||emp_info.employee_id);
  71. DBMS_OUTPUT.put_line('First Name: '||emp_info.first_name);
  72. DBMS_OUTPUT.put_line('Salary : '||emp_info.salary);
  73. DBMS_OUTPUT.put_line('Name job: '||emp_info.job_id);
  74. DBMS_OUTPUT.put_line('===============================');
  75. FETCH emp_all INTO emp_info;
  76. END LOOP;
  77. CLOSE emp_all;
  78.  
  79. END;
  80. --------------------------------------------------
  81.  
  82. --      while loop with count of employees
  83. --################################################
  84. DECLARE
  85. CURSOR emp_all IS
  86.         SELECT employee_id,first_name,salary,job_id
  87.         FROM employees
  88.         ORDER BY salary ASC;
  89.        
  90.         emp_info emp_all%ROWTYPE;
  91.  
  92.         num NUMBER := 1
  93. BEGIN
  94. OPEN emp_all;
  95.  
  96. FETCH emp_all INTO emp_info;
  97. WHILE emp_all%found
  98. LOOP
  99. DBMS_OUTPUT.put_line(num);
  100. DBMS_OUTPUT.put_line('EMP ID: '||emp_info.employee_id);
  101. DBMS_OUTPUT.put_line('First Name: '||emp_info.first_name);
  102. DBMS_OUTPUT.put_line('Salary : '||emp_info.salary);
  103. DBMS_OUTPUT.put_line('Name job: '||emp_info.job_id);
  104. DBMS_OUTPUT.put_line('===============================');
  105. num+1
  106. FETCH emp_all INTO emp_info;
  107. END LOOP;
  108.  
  109. DBMS_OUTPUT.put_line('Employees Number is '||emp_all%rowcount);
  110.  
  111. CLOSE emp_all;
  112.  
  113. END;
  114. --------------------------------------------------
  115.  
  116.  
  117.  
  118.  
  119. --                 simple loop
  120. --################################################
  121. DECLARE
  122. CURSOR emp_all IS
  123.         SELECT *
  124.         FROM employees
  125.         ORDER BY salary ASC;
  126.        
  127.         emp_info emp_all%ROWTYPE;
  128.         num NUMBER:= 1;
  129.        
  130.  
  131. BEGIN
  132. OPEN emp_all;
  133.  
  134.  
  135. LOOP
  136. FETCH emp_all INTO emp_info;
  137. DBMS_OUTPUT.put_line(num);
  138. DBMS_OUTPUT.put_line('EMP ID: '||emp_info.employee_id);
  139. DBMS_OUTPUT.put_line('First Name: '||emp_info.first_name);
  140. DBMS_OUTPUT.put_line('Salary : '||emp_info.salary);
  141. DBMS_OUTPUT.put_line('Name job: '||emp_info.job_id);
  142. DBMS_OUTPUT.put_line('===============================');
  143. num:= num +1;
  144. EXIT WHEN emp_all%notfound;
  145. END LOOP;
  146.  
  147. DBMS_OUTPUT.put_line('Employees Number is '||emp_all%rowcount);
  148. CLOSE emp_all;
  149.  
  150. END;
  151. -----------------------------------------------
  152.  
  153.  
  154. --             cursor with for loop
  155. --#############################################
  156.  
  157. DECLARE
  158. CURSOR emp_all IS
  159. SELECT *
  160. FROM employees
  161. ORDER BY salary ASC;       
  162. num NUMBER:= 1;
  163.        
  164.  
  165. BEGIN
  166.  
  167.  
  168. FOR emp_info IN emp_all
  169. LOOP
  170. DBMS_OUTPUT.put_line(num);
  171. DBMS_OUTPUT.put_line('EMP ID: '||emp_info.employee_id);
  172. DBMS_OUTPUT.put_line('First Name: '||emp_info.first_name);
  173. DBMS_OUTPUT.put_line('Salary : '||emp_info.salary);
  174. DBMS_OUTPUT.put_line('Name job: '||emp_info.job_id);
  175. DBMS_OUTPUT.put_line('===============================');
  176. num:= num +1;
  177. END LOOP;
  178.  
  179.  
  180.  
  181. END;
  182. ----------------------------------------------
  183.  
  184.  
  185.  
  186. --               just for loop
  187. --############################################
  188.  
  189. BEGIN
  190. FOR emp_rec IN (SELECT employee_id,last_name,salary -- or  use mark * for everything
  191. FROM employees)
  192. LOOP
  193. DBMS_OUTPUT.put_line('ID: '||emp_rec.employee_id);
  194. DBMS_OUTPUT.put_line('NAME: '||emp_rec.last_name);
  195. DBMS_OUTPUT.put_line('SALARY: '||emp_rec.salary);
  196. DBMS_OUTPUT.put_line('========');
  197. END LOOP;
  198. END;
Advertisement
RAW Paste Data Copied
Advertisement