KEMBAR78
Chapter4 Scripts | PDF | Pl/Sql | Areas Of Computer Science
Scribd
Upload
100 views20 pages

Chapter4 Scripts

The document contains multiple examples of PL/SQL code blocks that demonstrate the use of conditional logic and control flow statements like IF-THEN-ELSE. The code blocks check conditions, perform actions based on conditional results, and output messages. They include examples of converting between Celsius and Fahrenheit temperatures, checking for even/odd numbers, determining letter grades from scores, and more. Overall the document shows how to use PL/SQL to add conditional logic and control program flow.

Uploaded by

wilutb79
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
100 views20 pages

Chapter4 Scripts

The document contains multiple examples of PL/SQL code blocks that demonstrate the use of conditional logic and control flow statements like IF-THEN-ELSE. The code blocks check conditions, perform actions based on conditional results, and output messages. They include examples of converting between Celsius and Fahrenheit temperatures, checking for even/odd numbers, determining letter grades from scores, and more. Overall the document shows how to use PL/SQL to add conditional logic and control program flow.

Uploaded by

wilutb79
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 20

DECLARE

v_num1 NUMBER := 5;
v_num2 NUMBER := 3;
v_temp NUMBER;
BEGIN
-- if v_num1 is greater than v_num2 rearrange their values
IF v_num1 > v_num2 THEN
v_temp := v_num1;
v_num1 := v_num2;
v_num2 := v_temp;
END IF;

-- display the values of v_num1 and v_num2


DBMS_OUTPUT.PUT_LINE ('v_num1 = '||v_num1);
DBMS_OUTPUT.PUT_LINE ('v_num2 = '||v_num2);
END;
v_num1 = 3
v_num2 = 5

PL/SQL procedure successfully completed.

DECLARE
v_num NUMBER := &sv_user_num;
BEGIN
-- test if the number provided by the user is even
IF MOD(v_num,2) = 0

THEN

DBMS_OUTPUT.PUT_LINE (v_num||' is even number');

ELSE
DBMS_OUTPUT.PUT_LINE (v_num||' is odd number');
END IF;
DBMS_OUTPUT.PUT_LINE ('Done');
END;

Enter value for v_user_num: 24


old

2:

v_num

NUMBER := &v_user_num;

new

2:

v_num

NUMBER := 24;

24 is even number
Done

PL/SQL procedure successfully completed.

DECLARE
v_num1 NUMBER := 0;
v_num2 NUMBER;
BEGIN
IF v_num1 = v_num2 THEN
DBMS_OUTPUT.PUT_LINE ('v_num1 = v_num2');
ELSE
DBMS_OUTPUT.PUT_LINE ('v_num1 != v_num2');
END IF;
END;

v_num1 != v_num2

PL/SQL procedure successfully completed.

-- ch04_1a.sql, version 1.0


SET SERVEROUTPUT ON
DECLARE
v_date DATE := TO_DATE('&sv_user_date', 'DD-MON-YYYY');
v_day

VARCHAR2(15);

BEGIN
v_day := RTRIM(TO_CHAR(v_date, 'DAY'));

IF v_day IN ('SATURDAY', 'SUNDAY') THEN


DBMS_OUTPUT.PUT_LINE (v_date||' falls on weekend');
END IF;

-- control resumes here


DBMS_OUTPUT.PUT_LINE ('Done');
END;

Enter value for sv_user_date: 09-JAN-2007


old

2:

v_date DATE := TO_DATE('&sv_user_date', 'DD-MON-YYYY');

new

2:

v_date DATE := TO_DATE('09-JAN-2007', 'DD-MON-YYYY');

Done...

PL/SQL procedure successfully completed.


Enter value for sv_user_date: 13-JAN-2007
old

2:

v_date DATE := TO_DATE('&sv_user_date', 'DD-MON-YYYY');

new

2:

v_date DATE := TO_DATE('13-JAN-2007', 'DD-MON-YYYY');

13-JAN-07 falls on weekend

Done...

PL/SQL procedure successfully completed.

-- ch04_1b.sql, version 2.0


SET SERVEROUTPUT ON
DECLARE
v_date DATE := TO_DATE('&sv_user_date', 'DD-MON-YYYY');
v_day

VARCHAR2(15);

BEGIN
v_day := TO_CHAR(v_date, 'DAY');

IF v_day IN ('SATURDAY', 'SUNDAY') THEN


DBMS_OUTPUT.PUT_LINE (v_date||' falls on weekend');
END IF;

--- control resumes here


DBMS_OUTPUT.PUT_LINE ('Done');
END;

Enter value for sv_user_date: 14-JAN-2007


old

2:

v_date DATE := TO_DATE('&sv_user_date', 'DD-MON-YYYY');

new

2:

v_date DATE := TO_DATE('14-JAN-2007', 'DD-MON-YYYY');

Done...

PL/SQL procedure successfully completed.

-- ch04_1c.sql, version 3.0

SET SERVEROUTPUT ON
DECLARE
v_date DATE := TO_DATE('&sv_user_date', 'DD-MON-YYYY');
v_day

VARCHAR2(15);

BEGIN
v_day := RTRIM(TO_CHAR(v_date, 'DAY'));

IF v_day LIKE 'S%' THEN


DBMS_OUTPUT.PUT_LINE (v_date||' falls on weekend');
END IF;

-- control resumes here


DBMS_OUTPUT.PUT_LINE ('Done');
END;

-- ch04_1d.sql, version 4.0


SET SERVEROUTPUT ON
DECLARE
v_date DATE := TO_DATE('&sv_user_date', 'DD-MON-YYYY');
v_day

VARCHAR2(15);

BEGIN
v_day := RTRIM(TO_CHAR(v_date, 'DAY'));

IF v_day IN ('SATURDAY', 'SUNDAY') THEN


DBMS_OUTPUT.PUT_LINE (v_date||' falls on weekend');
ELSE
DBMS_OUTPUT.PUT_LINE (v_date||' does not fall on the weekend');
END IF;

-- control resumes here


DBMS_OUTPUT.PUT_LINE('Done');
END;

-- ch04_2a.sql, version 1.0


SET SERVEROUTPUT ON
DECLARE
v_total NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_total
FROM enrollment e
JOIN section s USING (section_id)
WHERE s.course_no = 25
AND s.section_no = 1;

-- check if section 1 of course 25 is full


IF v_total >= 15 THEN
DBMS_OUTPUT.PUT_LINE ('Section 1 of course 25 is full');
ELSE
DBMS_OUTPUT.PUT_LINE ('Section 1 of course 25 is not full');
END IF;
-- control resumes here
END;
-- ch04_2b.sql, version 2.0
SET SERVEROUTPUT ON

DECLARE
v_total

NUMBER;

v_course_no

CHAR(6) := '&sv_course_no';

v_section_no NUMBER

:= &sv_section_no;

BEGIN
SELECT COUNT(*)
INTO v_total
FROM enrollment e
JOIN section s USING (section_id)
WHERE s.course_no = v_course_no
AND s.section_no = v_section_no;

-- check if a specific section of a course is full


IF v_total >= 15 THEN
DBMS_OUTPUT.PUT_LINE ('Section 1 of course 25 is full');
ELSE
DBMS_OUTPUT.PUT_LINE ('Section 1 of course 25 is not full');
END IF;
-- control resumes here
END;

-- ch04_2c.sql, version 3.0


SET SERVEROUTPUT ON
DECLARE
v_total NUMBER;
v_students NUMBER;
BEGIN
SELECT COUNT(*)

INTO v_total
FROM enrollment e
JOIN section s USING (section_id)
WHERE s.course_no = 25
AND s.section_no = 1;

-- check if section 1 of course 25 is full


IF v_total >= 15 THEN
DBMS_OUTPUT.PUT_LINE ('Section 1 of course 25 is full');
ELSE
v_students := 15 - v_total;
DBMS_OUTPUT.PUT_LINE (v_students||' students can still enroll'||
' into section 1 of course 25');
END IF;
-- control resumes here
END;

DECLARE
v_num NUMBER := &sv_num;
BEGIN
IF v_num < 0 THEN
DBMS_OUTPUT.PUT_LINE (v_num||' is a negative number');
ELSIF v_num = 0 THEN
DBMS_OUTPUT.PUT_LINE (v_num||' is equal to zero');
ELSE
DBMS_OUTPUT.PUT_LINE (v_num||' is a positive number');
END IF;

END;

Enter value for sv_num: 5


old

2:

v_num

NUMBER := &sv_num;

new

2:

v_num

NUMBER := 5;

5 is a positive number

PL/SQL procedure successfully completed.

DECLARE
v_num NUMBER := &sv_num;
BEGIN
IF v_num < 0 THEN
DBMS_OUTPUT.PUT_LINE (v_num||' is a negative number');
ELSIF v_num > 0 THEN
DBMS_OUTPUT.PUT_LINE (v_num||' is a positive number');
END IF;
DBMS_OUTPUT.PUT_LINE ('Done');
END;

Enter value for sv_num: 0


old

2:

v_num

NUMBER := &sv_num;

new

2:

v_num

NUMBER := 0;

Done

PL/SQL procedure successfully completed.

-- ch04_3a.sql, version 1.0


SET SERVEROUTPUT ON
DECLARE
v_student_id

NUMBER := 102;

v_section_id

NUMBER := 89;

v_final_grade

NUMBER;

v_letter_grade CHAR(1);
BEGIN
SELECT final_grade
INTO v_final_grade
FROM enrollment
WHERE student_id = v_student_id
AND section_id = v_section_id;

IF v_final_grade BETWEEN 90 AND 100 THEN


v_letter_grade := 'A';
ELSIF v_final_grade BETWEEN 80 AND 89 THEN
v_letter_grade := 'B';
ELSIF v_final_grade BETWEEN 70 AND 79 THEN
v_letter_grade := 'C';
ELSIF v_final_grade BETWEEN 60 AND 69 THEN
v_letter_grade := 'D';
ELSE
v_letter_grade := 'F';
END IF;

-- control resumes here

DBMS_OUTPUT.PUT_LINE ('Letter grade is: '||


v_letter_grade);
END;

-- ch04_3b.sql, version 2.0


SET SERVEROUTPUT ON
DECLARE
v_student_id

NUMBER := 102;

v_section_id

NUMBER := 89;

v_final_grade

NUMBER;

v_letter_grade CHAR(1);
BEGIN
SELECT final_grade
INTO v_final_grade
FROM enrollment
WHERE student_id = v_student_id
AND section_id = v_section_id;

IF v_final_grade IS NULL THEN


DBMS_OUTPUT.PUT_LINE('v_final_grade is null');
ELSIF v_final_grade BETWEEN 90 AND 100 THEN
v_letter_grade := 'A';
ELSIF v_final_grade BETWEEN 80 AND 89 THEN
v_letter_grade := 'B';
ELSIF v_final_grade BETWEEN 70 AND 79 THEN
v_letter_grade := 'C';
ELSIF v_final_grade BETWEEN 60 AND 69 THEN
v_letter_grade := 'D';
ELSE

v_letter_grade := 'F';
END IF;

-- control resumes here


-DBMS_OUTPUT.PUT_LINE ('Letter grade is: '||
v_letter_grade);
END;

-- ch04_3c.sql, version 3.0


SET SERVEROUTPUT ON
DECLARE
v_student_id

NUMBER := &sv_student_id;

v_section_id

NUMBER := &sv_section_id;

v_final_grade

NUMBER;

v_letter_grade CHAR(1);
BEGIN
SELECT final_grade
INTO v_final_grade
FROM enrollment
WHERE student_id = v_student_id
AND section_id = v_section_id;

IF v_final_grade BETWEEN 90 AND 100 THEN


v_letter_grade := 'A';
ELSIF v_final_grade BETWEEN 80 AND 89 THEN
v_letter_grade := 'B';
ELSIF v_final_grade BETWEEN 70 AND 79 THEN
v_letter_grade := 'C';
ELSIF v_final_grade BETWEEN 60 AND 69 THEN

v_letter_grade := 'D';
ELSE
v_letter_grade := 'F';
END IF;

-- control resumes here


DBMS_OUTPUT.PUT_LINE ('Letter grade is: '||
v_letter_grade);
END;

-- ch04_3d.sql, version 4.0


SET SERVEROUTPUT ON
DECLARE
v_student_id

NUMBER := 102;

v_section_id

NUMBER := 89;

v_final_grade

NUMBER;

v_letter_grade CHAR(1);
BEGIN
SELECT final_grade
INTO v_final_grade
FROM enrollment
WHERE student_id = v_student_id
AND section_id = v_section_id;

IF v_final_grade >= 90 THEN


v_letter_grade := 'A';
ELSIF v_final_grade >= 80 THEN
v_letter_grade := 'B';
ELSIF v_final_grade >= 70 THEN

v_letter_grade := 'C';
ELSIF v_final_grade >= 60 THEN
v_letter_grade := 'D';
ELSE
v_letter_grade := 'F';
END IF;

--- control resumes here


-DBMS_OUTPUT.PUT_LINE ('Letter grade is: '||
v_letter_grade);
END;

DECLARE
v_num1 NUMBER := &sv_num1;
v_num2 NUMBER := &sv_num2;
v_total NUMBER;
BEGIN
IF v_num1 > v_num2 THEN
DBMS_OUTPUT.PUT_LINE ('IF part of the outer IF');
v_total := v_num1 - v_num2;
ELSE
DBMS_OUTPUT.PUT_LINE ('ELSE part of the outer IF');
v_total := v_num1 + v_num2;

IF v_total < 0 THEN


DBMS_OUTPUT.PUT_LINE ('Inner IF');
v_total := v_total * (-1);
END IF;

END IF;
DBMS_OUTPUT.PUT_LINE ('v_total = '||v_total);
END;

Enter value for sv_num1: -4


old

2:

v_num1

NUMBER := &sv_num1;

new

2:

v_num1

NUMBER := -4;

Enter value for sv_num2: 3


old

3:

v_num2

NUMBER := &sv_num2;

new

3:

v_num2

NUMBER := 3;

ELSE part of the outer IF


Inner IF
v_total = 1

PL/SQL procedure successfully completed.

DECLARE
v_letter CHAR(1) := '&sv_letter';
BEGIN
IF (v_letter >= 'A' AND v_letter <= 'Z') OR
(v_letter >= 'a' AND v_letter <= 'z')
THEN
DBMS_OUTPUT.PUT_LINE ('This is a letter');
ELSE
DBMS_OUTPUT.PUT_LINE ('This is not a letter');

IF v_letter BETWEEN '0' and '9' THEN


DBMS_OUTPUT.PUT_LINE ('This is a number');

ELSE
DBMS_OUTPUT.PUT_LINE ('This is not a number');
END IF;

END IF;
END;

Enter value for sv_letter: ?


old

2:

v_letter CHAR(1) := '&sv_letter';

new

2:

v_letter CHAR(1) := '?';

This is not a letter


This is not a number

PL/SQL procedure successfully completed.

-- ch04_4a.sql, version 1.0


SET SERVEROUTPUT ON
DECLARE
v_temp_in

NUMBER := &sv_temp_in;

v_scale_in

CHAR

v_temp_out

NUMBER;

:= '&sv_scale_in';

v_scale_out CHAR;
BEGIN
IF v_scale_in != 'C' AND v_scale_in != 'F' THEN
DBMS_OUTPUT.PUT_LINE ('This is not a valid scale');
ELSE
IF v_scale_in = 'C' THEN

v_temp_out

:= ( (9 * v_temp_in) / 5 ) + 32;

v_scale_out := 'F';
ELSE
v_temp_out

:= ( (v_temp_in 32) * 5 ) / 9;

v_scale_out := 'C';
END IF;
DBMS_OUTPUT.PUT_LINE ('New scale is: '||v_scale_out);
DBMS_OUTPUT.PUT_LINE ('New temperature is: '||v_temp_out);
END IF;
END;

Enter value for sv_temp_in: 100


old

2:

v_temp_in

NUMBER := &sv_temp_in;

new

2:

v_temp_in

NUMBER := 100;

Enter value for sv_scale_in: C


old

3:

v_scale_in

CHAR := '&sv_scale_in';

new

3:

v_scale_in

CHAR := 'C';

New scale is: F


New temperature is: 212

PL/SQL procedure successfully completed.

Enter value for sv_temp_in:


old

2:

v_temp_in

NUMBER := &sv_temp_in;

new

2:

v_temp_in

NUMBER := ;

Enter value for sv_scale_in: C


old

3:

v_scale_in

CHAR := '&sv_scale_in';

new

3:

v_scale_in

CHAR := 'C';

v_temp_in

NUMBER := ;
*

ERROR at line 2:
ORA-06550: line 2, column 27:
-PLS-00103: Encountered the symbol ";" when expecting one of the
following:
( - + mod not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
cast <a string literal with character set specification>
<a number> <a single-quoted SQL string>
The symbol "null" was substituted for ";" to continue.

Enter value for sv_temp_in: 45


old

2:

v_temp_in

NUMBER := &sv_temp_in;

new

2:

v_temp_in

NUMBER := 45;

Enter value for sv_scale_in: V


old

3:

v_scale_in

CHAR := '&sv_scale_in';

new

3:

v_scale_in

CHAR := 'V';

This is not a valid scale

PL/SQL procedure successfully completed.

-- ch04_4b.sql, version 2.0


DECLARE
v_temp_in

NUMBER := &sv_temp_in;

v_scale_in

CHAR

v_temp_out

NUMBER;

v_scale_out CHAR;

:= '&sv_scale_in';

BEGIN
IF v_scale_in != 'C' AND v_scale_in != 'F' THEN
DBMS_OUTPUT.PUT_LINE ('This is not a valid scale');
v_temp_out

:= 0;

v_scale_out := 'C';
ELSE
IF v_scale_in = 'C' THEN
v_temp_out

:= ( (9 * v_temp_in) / 5 ) + 32;

v_scale_out := 'F';
ELSE
v_temp_out

:= ( (v_temp_in - 32) * 5 ) / 9;

v_scale_out := 'C';
END IF;
END IF;
DBMS_OUTPUT.PUT_LINE ('New scale is: '||v_scale_out);
DBMS_OUTPUT.PUT_LINE ('New temperature is: '||v_temp_out);
END;

Enter value for sv_temp_in: 100


old

2:

v_temp_in

NUMBER := &sv_temp_in;

new

2:

v_temp_in

NUMBER := 100;

Enter value for sv_scale_in: V


old

3:

v_scale_in

CHAR := '&sv_scale_in';

new

3:

v_scale_in

CHAR := 'V';

This is not a valid scale.


New scale is: C
New temperature is: 0

PL/SQL procedure successfully completed.

You might also like