| Written Assignment-Printing a Record Using an If Statement [message #672404] |
Sun, 14 October 2018 04:32  |
cwilliam912
Messages: 30
Registered: February 2009
|
Member |
|
|
My assignment is to do but he want use to the if statement to do You can use it to determine the Department they work for, like we did in the past with a CASE. You can use it to print a message if they make over a certain amount of money.
Maybe they are due a raise if they were hired before a certain date.
I am having problem with my coding because I am getting all these error please help
1. Set echo on
2. SET SERVEROUT ON
3. Set up a spool file to receive your output for submission. I would suggest c:\CS4210\wa5spool.txt .
4. DECLARE a record variable (Emp_rec) using %ROWTYPE
5. In the BEGIN block add a select statement to read a record into the declared variable from HR.EMPLOYEES
6. Add If Statement to print record
7. Add DBMS_OUTPUT lines to print EMPLOYEE_ID, FIRST_NAME, LAST_NAME, and SALARY for the selected record
8. Use TO_CHAR to format the salary as $999,999
9. Add a EXCEPTION block to report when no data is found
10. Compile and run the procedure.
11. Close the spool file
(SELECT *
*
ERROR at line 7:
ORA-06550: line 7, column 1:
PLS-00103: Encountered the symbol "(" when expecting one of the following:
in
The symbol "in" was substituted for "(" to continue.
ORA-06550: line 12, column 1:
PLS-00103: Encountered the symbol "IF" when expecting one of the following:
( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
continue avg count current exists max min prior sql
ORA-06550: line 18, column 5:
PLS-00103: Encountered the symbol "LOOP" when expecting one of the following:
if
SQL> SET SERVEROUT ON FORMAT TRUNCATED
SQL> DECLARE
2
3 EMP_REC HR.EMPLOYEES%ROWTYPE;
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE ('---------------------------------------');
6 FOR EMP_REC
7 (SELECT *
8 FROM HR.EMPLOYEES
9 WHERE EMPLOYEE_ID = 5)
10 LOOP
11 IF
12 IF (EMP_REC.EMPLOYEE_ID = 5) THEN
13 DBMS_OUTPUT.PUT_LINE ('EMPLOYEE_ID: ' || EMP_REC.EMPLOYEE_ID) ;
14 DBMS_OUTPUT.PUT_LINE ('FIRST_NAME: ' || EMP_REC.FIRST_NAME) ;
15 DBMS_OUTPUT.PUT_LINE ('LAST_NAME: ' || EMP_REC.LAST_NAME) ;
16 DBMS_OUTPUT.PUT_LINE ('SALARY: ' || TO_CHAR (EMP_REC.SALARY, '$999,999'));
17 DBMS_OUTPUT.PUT_LINE ('---------------------------------------');
18 END LOOP;
19 END IF;
20 EXCEPTION
21 WHEN NO_DATA_FOUND THEN
22 DBMS_OUTPUT.PUT_LINE ('NO DATA FOUND.');
23
24 END;
25 /
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Written Assignment-Printing a Record Using an If Statement [message #672413 is a reply to message #672412] |
Sun, 14 October 2018 09:11  |
cwilliam912
Messages: 30
Registered: February 2009
|
Member |
|
|
This is what I got
SQL> SET SERVEROUT ON
SQL> DECLARE
2 EMP_REC HR.EMPLOYEES%ROWTYPE;
3
4 BEGIN
5 SELECT *
6 INTO EMP_REC
7 FROM HR.EMPLOYEES
8 WHERE EMPLOYEE_ID = 100;
9 IF EMP_REC.EMPLOYEE_ID = 100 THEN
10 DBMS_OUTPUT.PUT_LINE ('DEPARTMENT_ID: ' || EMP_REC.DEPARTMENT_ID) ;
11 DBMS_OUTPUT.PUT_LINE ('EMPLOYEE_ID:' || EMP_REC.EMPLOYEE_ID);
12 DBMS_OUTPUT.PUT_LINE ('FIRST_NAME: ' || EMP_REC.FIRST_NAME) ;
13 DBMS_OUTPUT.PUT_LINE ('LAST_NAME: ' || EMP_REC.LAST_NAME) ;
14 DBMS_OUTPUT.PUT_LINE ('SALARY: ' || TO_CHAR (EMP_REC.SALARY, '$999,999'));
15 END IF;
16 EXCEPTION
17 WHEN NO_DATA_FOUND THEN
18 DBMB_OUTPUT.PUT_LINE ('NO DATA FOUND.');
19
20 END;
21 /
DBMB_OUTPUT.PUT_LINE ('NO DATA FOUND.');
*
ERROR at line 18:
ORA-06550: line 18, column 1:
PLS-00201: identifier 'DBMB_OUTPUT.PUT_LINE' must be declared
ORA-06550: line 18, column 1:
PL/SQL: Statement ignored
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Written Assignment-Printing a Record Using an If Statement [message #672421 is a reply to message #672419] |
Mon, 15 October 2018 03:35 |
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
That IF statement is pointless - the select specifies employeee_id must be 100, so if it finds anything the IF must be true.
If it doesn't find anything then you'll get a no_data_found error and code execution will go from the select straight to the exception handler, meaning the IF will not be executed in that case.
Your instructor really ought to come up with better examples for using IF.
|
|
|
|
| Re: Written Assignment-Printing a Record Using an If Statement [message #672429 is a reply to message #672421] |
Mon, 15 October 2018 06:03 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
Don't forget the other requirements from your assignments, the spool
SET SERVEROUT ON
spool c:\CS4210\wa5spool.txt
DECLARE
Emp_rec Hr.Employees%ROWTYPE;
BEGIN
SELECT *
INTO Emp_rec
FROM Hr.Employees
WHERE Employee_id = 100;
IF Emp_rec.Employee_id = 100 THEN
DBMS_OUTPUT.Put_line('DEPARTMENT_ID: ' || Emp_rec.Department_id);
DBMS_OUTPUT.Put_line('EMPLOYEE_ID:' || Emp_rec.Employee_id);
DBMS_OUTPUT.Put_line('FIRST_NAME: ' || Emp_rec.First_name);
DBMS_OUTPUT.Put_line('LAST_NAME: ' || Emp_rec.Last_name);
DBMS_OUTPUT.Put_line(
'SALARY: ' || TO_CHAR(Emp_rec.Salary, '$999,999'));
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.Put_line('NO DATA FOUND.');
END;
/
spool off |
|
|
|
|
|
| Re: Written Assignment-Printing a Record Using an If Statement [message #672431 is a reply to message #672430] |
Mon, 15 October 2018 06:18 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
He is using the IF statement because the professor wanted him to include it. See requirements from first entry. It is completely unnecessary but required for his homework.
1. Set echo on
2. SET SERVEROUT ON
3. Set up a spool file to receive your output for submission. I would suggest c:\CS4210\wa5spool.txt .
4. DECLARE a record variable (Emp_rec) using %ROWTYPE
5. In the BEGIN block add a select statement to read a record into the declared variable from HR.EMPLOYEES
6. Add If Statement to print record
7. Add DBMS_OUTPUT lines to print EMPLOYEE_ID, FIRST_NAME, LAST_NAME, and SALARY for the selected record
8. Use TO_CHAR to format the salary as $999,999
9. Add a EXCEPTION block to report when no data is found
10. Compile and run the procedure.
11. Close the spool file
|
|
|
|
| Re: Written Assignment-Printing a Record Using an If Statement [message #672449 is a reply to message #672421] |
Mon, 15 October 2018 15:12 |
cwilliam912
Messages: 30
Registered: February 2009
|
Member |
|
|
cookiemonster wrote on Mon, 15 October 2018 03:35That IF statement is pointless - the select specifies employeee_id must be 100, so if it finds anything the IF must be true.
If it doesn't find anything then you'll get a no_data_found error and code execution will go from the select straight to the exception handler, meaning the IF will not be executed in that case.
Your instructor really ought to come up with better examples for using IF.
Do you have a better if example use instead of employee_id and it doesn't have to 100 that was something that I can up with? This is what he ask for You can use it to determine the Department they work for, like we did in the past with a CASE.
You can use it to print a message if they make over a certain amount of money.
Maybe they are due a raise if they were hired before a certain date.
|
|
|
|
|
|
|
|
|
|
| Re: Written Assignment-Printing a Record Using an If Statement [message #672514 is a reply to message #672513] |
Wed, 17 October 2018 03:57 |
cwilliam912
Messages: 30
Registered: February 2009
|
Member |
|
|
I was asking how can I put something like if it finds anything the IF must be true.
If it doesn't find anything then you'll get a no_data_found error and code execution will go from the select straight to the exception handler, meaning the IF will not be executed in that case in sql so that is want give me an error when I run the script
|
|
|
|
|
|
| Re: Written Assignment-Printing a Record Using an If Statement [message #672549 is a reply to message #672519] |
Wed, 17 October 2018 14:05 |
cwilliam912
Messages: 30
Registered: February 2009
|
Member |
|
|
Can you tell me what I did wrong please
my line 3: CRNT_ROOM DDI.LEDGER_VIEW.ROOMNUM%TYPE: =0;
my column 39: DBMS_OUTPUT.PUT_LINE (CRNT_ROOM ||' '||TO_CHAR(AVG_GUESTS(CRNT_ROOM),'$99999
.99')) ||' '||NIGHTS_BOOK);
CRNT_ROOM DDI.LEDGER_VIEW.ROOMNUM%TYPE: =0;
*
ERROR at line 3:
ORA-06550: line 3, column 39:
PLS-00103: Encountered the symbol ":" when expecting one of the following:
:= ( ; not null range default character
|
|
|
|
| Re: Written Assignment-Printing a Record Using an If Statement [message #672552 is a reply to message #672549] |
Wed, 17 October 2018 15:45 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
cwilliam912 wrote on Wed, 17 October 2018 15:05Can you tell me what I did wrong please
my line 3: CRNT_ROOM DDI.LEDGER_VIEW.ROOMNUM%TYPE: =0;
my column 39: DBMS_OUTPUT.PUT_LINE (CRNT_ROOM ||' '||TO_CHAR(AVG_GUESTS(CRNT_ROOM),'$99999
.99')) ||' '||NIGHTS_BOOK);
CRNT_ROOM DDI.LEDGER_VIEW.ROOMNUM%TYPE: =0;
*
ERROR at line 3:
ORA-06550: line 3, column 39:
PLS-00103: Encountered the symbol ":" when expecting one of the following:
:= ( ; not null range default character
It's telling you exactly what the problem is
There must be a space before the colon and no space between the colon and the equal sign.
CRNT_ROOM DDI.LEDGER_VIEW.ROOMNUM%TYPE :=0;
This says make a variable called crnt_room with the same structure as the column ROOMNUM in the table LEDGER_VIEW in the schema DDI and then assign an initial value of 0.
|
|
|
|
| Re: Written Assignment-Printing a Record Using an If Statement [message #672562 is a reply to message #672549] |
Thu, 18 October 2018 04:02 |
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
cwilliam912 wrote on Wed, 17 October 2018 20:05Can you tell me what I did wrong please
my line 3: CRNT_ROOM DDI.LEDGER_VIEW.ROOMNUM%TYPE: =0;
my column 39: DBMS_OUTPUT.PUT_LINE (CRNT_ROOM ||' '||TO_CHAR(AVG_GUESTS(CRNT_ROOM),'$99999
.99')) ||' '||NIGHTS_BOOK);
No, column != line. Column is counting across.
line 3:
CRNT_ROOM DDI.LEDGER_VIEW.ROOMNUM%TYPE: =0;
^
|
column 39 is here
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
