Write a procedure that is passed a student’s identification number and returns back the student’s full name and phone number from the STUDENT table to the calling program. Also write an anonymous block with the procedure call.

What will be an ideal response?


```
SQL> CREATE OR REPLACE PROCEDURE get_student
2 (i_studentid IN NUMBER,
3 o_last OUT VARCHAR2,
4 o_first OUT VARCHAR2,
5 o_phone OUT CHAR)
6 IS
7 BEGIN
8 SELECT Last, First, Phone
9 INTO o_last, o_first, o_phone
10 FROM student
11 WHERE StudentId = i_studentid;
12 EXCEPTION
13 WHEN OTHERS THEN
14 DBMS_OUTPUT.PUT_LINE('StudentId ' ||
15 TO_CHAR(i_studentid) || ' does not exist');
16 END get_student;
17 /

Procedure created.


SQL> DECLARE
2 v_id student.StudentId%TYPE := '&Student_Id';
3 v_last student.Last%TYPE;
4 v_first student.First%TYPE;
5 v_phone student.Phone%TYPE;
6 BEGIN
7 get_student(v_id, v_last, v_first, v_phone);
8 IF v_last IS NOT NULL THEN
9 DBMS_OUTPUT.PUT_LINE('Name: '||v_first||' '||v_last);
10 DBMS_OUTPUT.PUT_LINE('Phone: ' || v_phone);
11 END IF;
12 END;
13 /
Enter value for student_id: 00101
Name: Mickey Tyler
Phone: 7185552222

PL/SQL procedure successfully completed.
```

Computer Science & Information Technology

You might also like to view...

In the array implementation of the queue, if the number of elements used drops to 25% of the capacity, why can’t we immediately just reduce the capacity to one half?

A. There may be too many elements in the queue. B. The queue may be wrapping around the array. C. It would be faster to make some element adjustments first. D. If the array is reduced to one-half its capacity, there may be memory leak for some of the elements.

Computer Science & Information Technology

In which layer are ongoing communications between a sender and a receiver set up, maintained, and then terminated, or torn down, as needed?

A. Session layer B. Physical layer C. Network layer D. Presentation layer

Computer Science & Information Technology

Device Manager is selected from the System Control Panel to update sound drivers

Indicate whether the statement is true or false

Computer Science & Information Technology

Briefly describe an entry in a routing table from a router.

What will be an ideal response?

Computer Science & Information Technology