SQL Database Programming Written Assignment 8: Write a program using a return st
ID: 3594302 • Letter: S
Question
SQL Database Programming
Written Assignment 8: Write a program using a return statement
SQL> DECLARE
2 f_sal varchar2(100);
3
4 i NUMBER := 0;
5 BEGIN
6 FOR rec IN (SELECT * FROM EMPLOYEES)
7 LOOP
8 i := i + 1;
9 f_sal := to_char(rec.salary, '$999,999');
10
11
12 IF rec.salary < 3000 THEN
13 DBMS_OUTPUT.PUT_LINE (' Employee ID: '||rec.EMPLOYEE_ID);
14
15 DBMS_OUTPUT.PUT_LINE (rec.FIRST_NAME||' '||rec.LAST_NAME|| ' Needs a raise.');
16
17 DBMS_OUTPUT.PUT_LINE (' Salary: '||f_sal);
18 DBMS_OUTPUT.PUT_LINE ('----------------------------------');
19
20
21
22 ELSE
23 IF rec.salary < 7000 AND rec.salary >= 3000 THEN
24 DBMS_OUTPUT.PUT_LINE (' Employee ID: '||rec.EMPLOYEE_ID);
25 DBMS_OUTPUT.PUT_LINE (rec.FIRST_NAME||' '||rec.LAST_NAME|| ' Gets paid enough.');
26
27 DBMS_OUTPUT.PUT_LINE (' Salary: '||f_sal);
28 DBMS_OUTPUT.PUT_LINE ('----------------------------------');
29
30 ELSE
31 DBMS_OUTPUT.PUT_LINE (' Employee ID: '||rec.EMPLOYEE_ID);
32 DBMS_OUTPUT.PUT_LINE (rec.FIRST_NAME||' '||rec.LAST_NAME|| ' Gets paid too much.');
33
34 DBMS_OUTPUT.PUT_LINE (' Salary: '||f_sal);
35 DBMS_OUTPUT.PUT_LINE ('----------------------------------');
36
37 END IF;
38 END IF;
39
40 END LOOP;
41
42 EXCEPTION
43 WHEN no_data_found THEN
44 dbms_output.put_line('No customer exists with that number.');
45 WHEN others THEN
46 dbms_output.put_line('ERROR');
47
48 END;
49 /
Explanation / Answer
Return statement in SQL is a statement that immediately and unconditionally passes the flow of control to from where the function was called. The return statement should always return an integer value. A program can have multiple return statements but only after the declaration block.
Following is an example of using a return statement in an SQL function. Do ask if you have any doubt :
CREATE PROCEDURE return_test (IN table1 hours)
LANGUAGE SQL
SPECIFIC return_test
BEGIN
IF hours>10 THEN
RETURN 1;
ELSE
RETURN -1;
END IF;
END rt
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.