Usage and exceptions of optical mark in Oracle

Usage and exceptions of optical mark in Oracle

1. 1. Use of cursor without Parameters
2. SQL>Declare
3. 2CursorClIs Select*FromEmp;
4. 3 emp_row emp % rowtype;
5. 4Begin
6. 5OpenCl;
7. 6 loop
8. 7FetchClIntoEmp_row;
9. 8 exitWhenCl % notfound;
10. 9 dbms_output.put_line ('Employee name'| Emp_row.ename |'Employee Department'| Emp_row.deptno );
11. 10EndLoop;
12. 11CloseCl;
13. 12End;
14. 13/
16. Employee name SMITH employee's department 20
17. Employee name ALLEN employee Department 30
18. Employee name WARD employee Department 30
19. Employee name JONES employee's department 20
20. Employee name MARTIN employee Department 30
21. Employee name: employee's Department 30
22. Employee name CLARK employee's Department 10
23. Employee name SCOTT employee's department 20
24. Employee name KING employee's Department 10
25. Employee name TURNER employee Department 30
26. Employee name ADAMS employee's department 20
27. Employee name JAMES employee Department 30
28. Employee name FORD employee's department 20
29. Employee name MILLER employee Department 10
31. PL/SQLProcedureSuccessfully completed
35. SQL>Declare
36. 2CursorCl (emp_name varchar2)Is Select*FromEmpWhereEname = emp_name;
37. 3 emp_row emp % rowtype;
38. 4Begin
39. 5OpenCl ('Smith');
40. 6FetchClIntoEmp_row;
41. 7 dbms_output.put_line ('Employee name'| Emp_row.ename |'Employee's work'| Emp_row.job );
42. 8CloseCl;
43. 9End;
44. 10/
46. Employee name SMITH employee's work CLERK
50. Employees numbered 10
51. SQL>Declare
52. 2CursorCl (emp_deptno number)Is Select*FromEmpWhereDeptno = emp_deptno;
53. 3 emp_row emp % rowtype;
54. 4Begin
55. 5OpenCl (10 );
56. 6 loop
57. 7FetchClIntoEmp_row;
58. 8 exitWhenCl % notfound;
59. 9 dbms_output.put_line ('Employee name :'| Emp_row.ename );
60. 10EndLoop;
61. 11CloseCl;
62. 12End;
63. 13/
65. Employee name: CLARK
66. Employee name: KING
67. Employee name: MILLER
69. PL/SQLProcedureSuccessfully completed
74. Exception except 0
75. SQL>Declare
76. 2 pnum number: = 10;
77. 3Begin
78. 4 pnum: = pnum/0;
79. 5 exception
80. 6WhenZero_divideThen
81. 7 dbms_output.put_line ('Division cannot be 0');
82. 8End;
83. 9/
85. The divisor cannot be 0.
87. PL/SQLProcedureSuccessfully completed
90. Custom exceptions
91. SQL>Declare
92. 2CursorCl (emp_no number)Is Select*FromEmpWhereEmpno = emp_no;
93. 3 emprow emp % rowtype;
94. 4 no_found exception;
95. 5Begin
96. 6OpenCl (1000 );
97. 7FetchClIntoEmprow;
98. 8 if cl % notfoundThenRaise no_found;
99. 9EndIf;
100. 10CloseCl;
101. 11 exception
102. 12WhenNo_foundThen
103. 13 dbms_output.put_line ('Custom accidents');
104. 14End;
105. 15/
107. Accidents defined by yourself
109. PL/SQLProcedureSuccessfully completed

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12