Written Assignment-Printing a Record Using an If Statement Set echo on SET SERVE
ID: 3590518 • Letter: W
Question
Written Assignment-Printing a Record Using an If Statement
Set echo on
SET SERVEROUT ON
Set up a spool file to receive your output for submission. I would suggest c:CSwa5spool.txt .
DECLARE a record variable (Emp_rec) using %ROWTYPE
In the BEGIN block add a select statement to read a record into the declared variable from HR.EMPLOYEES
Add If Statement to print record
Add DBMS_OUTPUT lines to print EMPLOYEE_ID, FIRST_NAME, LAST_NAME, and SALARY for the selected record
Use TO_CHAR to format the salary as $999,999
Add a EXCEPTION block to report when no data is found
Compile and run the procedure.
Close the spool file
Explanation / Answer
Answer and Explanation :
The process of writing a query to file is called spooling.
So in this case i have assumed that in if condition if employee_id is equals to 123 then procedure will print records.
Below is procedure that as requested:
--------------------------------------------------------------------------------------------------------------------------
SET echo ON
SET serveroutput ON
SET verify OFF
Create or replace procedure data_in_spool is
Emp_rec HR.Employees%rowtype;
BEGIN
spool c:CSwa5spool.txt
select EMPLOYEE_ID, FIRST_NAME, LAST_NAME,to_char( SALARY, '$999,999' ) as sal into emp_rec from HR.employess;
if emp_rec.employee_id=123 then
dbms_output.put_line('EMPLOYEE_ID'||emp_rec.employee_id||', FIRST_NAME'||emp_rec.first_name||', LAST_NAME'||emp_rec.last_name||'and SALARY'||emp_rec.sal);
end if;
exception
when no_data_found then
dbms_output.put_line('NO data is there');
spool off
END;
end data_in_spool;
------------------------------------------ Procedure created---------------------
Once we run this above code in sql developer or sql plus or toad then proedure will be created.
Once the procedure created we can use below commands to run the procedure:
-------------------
begin
data_in_spool;
end;
/
-------------------
after you run this the output will be displayed in that spool file;)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.