Week9 - Introduction to PL/SQL, Control Statements / Design

The questions can come in any order, so make sure you are selecting right option for all questions.

1. SB - Date Operations

Write a PL/SQL anonymous block which will perform date and time operations. Declare a variable ‘d’ of type DATETIME and initialize with the value '25-Jan-2020'. And then perform below date and time operations and print the result:
1. Print the date after adding 4 months to the date d
2. Print only the year part present in the date d
3. Print the last day of the month based on the date d
4. Assume that the current date is ‘2020-10-25’. Display the number of month between the current date and the date d

Note:
Do not change the value of the date d.
Display the result in the same order. Do not change the order of operations.

SET SERVEROUTPUT ON;
declare
d date := '25-Jan-2020';
cd date := '25-Oct-2020'; 
a date;
b int;
m int;
begin
a := add_months(d,4);
dbms_output.put_line(a);
b := extract(year from d);
dbms_output.put_line(b);
dbms_output.put_line(last_day(d));
m := months_between(cd,d);
dbms_output.put_line(m);
end;
/

2. SB - Display even numbers

Write a PL/SQL anonymous block to display the even numbers between 1 and 100 each in a newline.

Sample Output:
2
4
6
8
.
.
.
100

SET SERVEROUTPUT ON;
declare
a int := 2;
BEGIN
--Write your code here
loop
dbms_output.put_line(a);
a := a+2;
if a>100 then
exit;
end if;
end loop;
END;
/

3. SB - Display custom message

Write a PL/SQL simple procedure named display_custommessage with 2 parameters. The first input parameter is name of type varchar. The second output parameter is custmsg of type varchar. This procedure will set the output parameter message with ‘Hi <name>, You’re learning PL/SQL’.


Use the below skeleton:
Procedure name: display_custommessage
Input parameter: name of type varchar
Output parameter: custmsg of type varchar

Note:
Do not change the procedure name
Do not change the argument count and order
Do not change the output text.

Instructions:

1. Create the procedure successfully
2. Once the procedure is created, check the functionality of the procedure using different anonymous block call.
3. DO NOT submit the anonymous block. Submit only the CREATE PROCEDURE query.

CREATE OR REPLACE PROCEDURE display_custommessage(
    name IN VARCHAR2,
    custmsg OUT VARCHAR2
)
IS
BEGIN
    custmsg := 'Hi ' || name || ', You''re learning PL/SQL';
END;
/

SET SERVEROUTPUT ON;
DECLARE
    output_msg VARCHAR2(100);
BEGIN
    display_custommessage('Alice', output_msg);
    DBMS_OUTPUT.PUT_LINE(output_msg);
END;
/

4. SB - Display dates

Write a PL/SQL simple procedure named display_dates with 2 input parameter. The first input parameter is d1 of type date. The second input parameter is d2 of type date. This procedure will display the dates between d1 and d2(exclusive), each in a newline.

Use the below skeleton:
Procedure name: display_dates
Input parameter: d1 of type date, d2 of type date

Note:
Do not change the procedure name
Do not change the argument count and order
Do not change the output text.


Instructions:

1. Create the procedure successfully
2. Once the procedure is created, check the functionality of the procedure using different anonymous block call.
3. DO NOT submit the anonymous block. Submit only the CREATE PROCEDURE query.

CREATE OR REPLACE PROCEDURE display_dates(
    d1 IN DATE,
    d2 IN DATE
)
IS
    curr_date DATE := d1 + 1;
BEGIN
    WHILE curr_date < d2 LOOP
        DBMS_OUTPUT.PUT_LINE(curr_date);
        curr_date := curr_date + 1; 
    END LOOP;
END;
/

SET SERVEROUTPUT ON;
DECLARE
    start_date DATE := TO_DATE('2020-01-01', 'YYYY-MM-DD');
    end_date DATE := TO_DATE('2020-01-31', 'YYYY-MM-DD');
BEGIN
    display_dates(start_date, end_date);
END;
/

5. SB - Display numbers in reverse order

Write a PL/SQL simple procedure named display_reverse with 1 input parameter. The input parameter is n of type number. This procedure will display the numbers n to 1, each in a newline.

Use the below skeleton:
Procedure name: display_reverse
Input parameter: n of type number

Note:
Do not change the procedure name
Do not change the argument count and order
Do not change the output text.


Instructions:

1. Create the procedure successfully
2. Once the procedure is created, check the functionality of the procedure using different anonymous block call.
3. DO NOT submit the anonymous block. Submit only the CREATE PROCEDURE query.

CREATE OR REPLACE PROCEDURE display_reverse(
    n IN NUMBER
)
IS
BEGIN
    FOR i IN REVERSE 1..n LOOP
        DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
END;
/


SET SERVEROUTPUT ON;
DECLARE
    num_to_display NUMBER :=25 ; 
BEGIN
    display_reverse(num_to_display);
END;
/