EXP8_CSE2004Lab_20BRS1064

1.  WRITE a PROCEDURE that prints “Hello World !!!”
2.  WRITE a PROCEDURE that computes the square OF VALUE OF a passed VALUE.
3.  WRITE a PROCEDURE program finds the maximum OF three VALUE OF passed VALUE.
4.  WRITE a PL/SQL PROCEDURE TO accept name OF tester AS input parameter AND display the details.
5.  WRITE a PL/SQL PROCEDURE FOR inserting/deleting VALUES IN tester TABLE.
6.  WRITE a PL/SQL PROCEDURE TO CHECK IF an tester EXISTS IN database AND throw suitable.EXCEPTION message IF NOT present.

Pl/SQL Functions

7.  WRITE a FUNCTION prints “Hello World !!!” BY passing it AS argument.
8.  WRITE a FUNCTION TO CHECK whether the given NUMBER IS prime OR NOT.
9.  WRITE a PL/SQL FUNCTION TO accept a tester id AND display the name OF the tester.
10. WRITE a PL/SQL FUNCTION TO UPDATE the phone NUMBER OF the a developer D205.
11. WRITE a PL/SQL FUNCTION TO DELETE the RECORD WITH Bugid B104 which IS mistakenly escalated BY the testing team.
12. WRITE a PL/SQL TO FUNCTION TO RETURN the NUMBER OF OPEN bugs.




--1
CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN
    DBMS_OUTPUT.put_line ('Hello World!');
END;
/
EXECUTE greetings;

--2

DECLARE
    a INTEGER;
    b INTEGER;
PROCEDURE sqr(x IN NUMBER,y OUT NUMBER) IS
BEGIN
    y := x*x;
END;

BEGIN
    a := &a;
    sqr(a,b);
    DBMS_OUTPUT.put_line('Value of a^2: ' || b);
END;
/

--3
DECLARE
    a INTEGER;
    b INTEGER;
    c INTEGER;

PROCEDURE find_max(x IN NUMBER,y IN NUMBER, z IN NUMBER) IS
BEGIN
    IF x>=y THEN
        IF x>=z THEN
            DBMS_OUTPUT.put_line('Largest value is ' || x);
        ELSE
            DBMS_OUTPUT.put_line('Largest value is ' || z);
        END IF;
    ELSE
        IF y>=z THEN
            DBMS_OUTPUT.put_line('Largest value is ' || y);
        ELSE
            DBMS_OUTPUT.put_line('Largest value is ' || z);
        END IF;
    END IF;
END;

BEGIN
    a := &a;
    b := &b;
    c := &c;
    find_max(a,b,c);
END;
/

--4
CREATE TABLE tester_20BRS1064(
testerid VARCHAR2(4) CHECK (testerid LIKE 'T%') PRIMARY KEY,
testername VARCHAR2(20) NOT NULL,
jobband VARCHAR2(1) CHECK (jobband IN ('C','B'))
);

INSERT INTO tester_20BRS1064 VALUES('T101','Arron','B');
INSERT INTO tester_20BRS1064 VALUES('T102','Jacob','C');
INSERT INTO tester_20BRS1064 VALUES('T103','Jerome','B');
INSERT INTO tester_20BRS1064 VALUES('T104','Simonds','B');
INSERT INTO tester_20BRS1064 VALUES('T105','Paul','B');
INSERT INTO tester_20BRS1064 VALUES('T106','Antony','C');
INSERT INTO tester_20BRS1064 VALUES('T107','Nancy','C');
INSERT INTO tester_20BRS1064 VALUES('T108','Gonzalez','B');

CREATE OR REPLACE PROCEDURE findna(&a IN VARCHAR2)
AS
BEGIN
    SELECT * FROM employee_20BRS1064 WHERE testername = a;
END;
/
EXECUTE findna('Paul');

--5
DECLARE
    a INTEGER;
    b VARCHAR2;
    c VARCHAR2;
    d VARCHAR2;

PROCEDURE add_rec(a IN VARCHAR2,b IN VARCHAR2,c IN VARCHAR2) IS
BEGIN
    INSERT INTO tester_20BRS1064 VALUES(a,b,c);
END;
/

PROCEDURE del_rec(a IN VARCHAR2) IS
BEGIN
    DELETE FROM tester_20BRS1064 WHERE testerid := a;
END;
/
add_rec('T109','Lee','B');
del_rec('T109');


--6
PROCEDURE chck(a IN VARCHAR2) IS
BEGIN
    SELECT * FROM tester_20BRS1064 WHERE testerid := a;
END;
/
chck('T102');

--7
CREATE OR REPLACE FUNCTION greet(x IN VARCHAR2)
IS
BEGIN
    DBMS_OUTPUT.put_line(x);
END;
/
greet("Hello World!);



CREATE OR REPLACE PROCEDURE hellomf
IS
BEGIN

  DBMS_OUTPUT.PUT_LINE('Hello mfker!');

END;
/
exec hellomf;

--8

CREATE OR REPLACE function prime_chk(x in number)
return null is
DECLARE
a number;
temp number := 1;
BEGIN
for a in 2 .. x loop
    if mod(x,a) THEN
        temp := 0;
    end if;
    end loop;
    if temp = 1 then
        DBMS_OUTPUT.put_line ('True');
    else
        DBMS_OUTPUT.put_line ('False!');
    end if;
end;
/
prime_chk(17);

--9
CREATE OR REPLACE function disp(x in varchar2)
return null is
BEGIN
select * from tester_20BRS1064 where testerid := x;
end;
/
disp('T102');

--10
CREATE TABLE developer_20BRS1064(
developerid varchar2(4) PRIMARY KEY CHECK (developerid LIKE 'D%'),
developername varchar2(20) NOT NULL,
developerexperience varchar2(15),
phonenumber NUMBER
);
INSERT INTO developer_20BRS1064 VALUES('D201','Anson','EXPERT',9785554423);
INSERT INTO developer_20BRS1064 VALUES('D202','Dsouza','COMPETENT',9256743210);
INSERT INTO developer_20BRS1064 VALUES('D203','Sharon','COMPETENT',9886556889);
INSERT INTO developer_20BRS1064 VALUES('D204','Peter','COMPETENT',9298277662);
INSERT INTO developer_20BRS1064 VALUES('D205','Susan','NOVICE',9789175600);
INSERT INTO developer_20BRS1064 VALUES('D206','Sophia','NOVICE',9008134134);

CREATE OR REPLACE function upd(x in NUMBER)
return null is
BEGIN
    update developer_20BRS1064 set phonenumber := x
    where developerid='D205';
end;
/
upd(9911223344);

--11
CREATE TABLE bugreport_20BRS1064(
bugid varchar2(4) PRIMARY KEY CHECK (bugid LIKE 'B%'),
testerid varchar2(4),
bugdescription varchar2(50) NOT NULL,
severity varchar2(1) CHECK (severity IN ('C','M','L')),
reporteddate DATE,
CONSTRAINT FK_testerid FOREIGN KEY (testerid) REFERENCES tester_20BRS1064(testerid)
);

INSERT INTO bugreport_20BRS1064 VALUES('B101','T106','File Not Saved','M',to_date('11-08-2021','DD-MM-YYYY'));
INSERT INTO bugreport_20BRS1064 VALUES('B102','T102','Crash on opening','C',to_date('12-08-2021','DD-MM-YYYY'));
INSERT INTO bugreport_20BRS1064 VALUES('B103','T102','Memory Leak','C',to_date('13-08-2021','DD-MM-YYYY'));
INSERT INTO bugreport_20BRS1064 VALUES('B104','T102','ID Field Editable','L',to_date('14-08-2021','DD-MM-YYYY'));
INSERT INTO bugreport_20BRS1064 VALUES('B105','T101','File corrupted when received','M',to_date('15-08-2021','DD-MM-YYYY'));


create or REPLACE function del
return null is
BEGIN
    delete from bugreport_20BRS1064 where bugid='B104';
end;
/
del();

--12
CREATE TABLE bugallocation_20BRS1064(
allocationid varchar2(4) PRIMARY KEY CHECK(allocationid LIKE 'A%'),
bugid varchar2(4),
developerid varchar2(4),
STATUS varchar2(1) CHECK (STATUS IN ('O','C')),
escalationdate DATE,
resolutiondate DATE,
CONSTRAINT FK_bugid FOREIGN KEY (bugid) REFERENCES bugreport_20BRS1064(bugid),
CONSTRAINT FK_developerid FOREIGN KEY (developerid) REFERENCES developer_20BRS1064(developerid)
);

INSERT INTO bugallocation_20BRS1064 VALUES('A101','B101','D201','C',to_date('12-08-2021','DD-MM-YYYY'),to_date('21-08-2021','DD-MM-YYYY'));
INSERT INTO bugallocation_20BRS1064 VALUES('A102','B103','D203','C',to_date('13-08-2021','DD-MM-YYYY'),to_date('19-08-2021','DD-MM-YYYY'));
INSERT INTO bugallocation_20BRS1064 VALUES('A103','B102','D204','O',to_date('14-08-2021','DD-MM-YYYY'),to_date('24-08-2021','DD-MM-YYYY'));
INSERT INTO bugallocation_20BRS1064 VALUES('A104','B104','D201','O',to_date('15-08-2021','DD-MM-YYYY'),to_date('22-08-2021','DD-MM-YYYY'));
INSERT INTO bugallocation_20BRS1064 VALUES('A105','B105','D202','O',to_date('16-08-2021','DD-MM-YYYY'),to_date('29-08-2021','DD-MM-YYYY'));

create or REPLACE function open_bugs
return number is
    z number;
BEGIN
    select count(*) into z
    from bugallocation_20BRS1064 where STATUS='O';
    return z;
end;
DECLARE
a number;
BEGIN
a:=open_bugs();
DBMS_OUTPUT.put_line('Count: '||a);
end;
/

DROP TABLE bugallocation_20BRS1064;
DROP TABLE bugreport_20BRS1064;
DROP TABLE developer_20BRS1064;
DROP TABLE tester_20BRS1064;