Notes

Example 1:

DECLARE

v_string varchar2(256):='hello,world!';

begin

dbms_output.put_line(v_string);

END;

/









Example 2

DECLARE

var_salary number(6);

var_emp_id number(6) := 7698;

BEGIN

SELECT sal

INTO var_salary

FROM emp

WHERE empno = var_emp_id;

dbms_output.put_line(var_salary);

dbms_output.put_line('The Employee: '||var_emp_id||' has salary '||var_salary);

END;

/









Example 3:

DECLARE

var_num1 number;

var_num2 number;

BEGIN

var_num1 := 100;

var_num2 := 200;

DECLARE

var_mult number;

BEGIN

var_mult := var_num1*var_num2;

dbms_output.put_line(var_mult);

END;

END;

/







Example 4:

DECLARE

v_name varchar2(256);

BEGIN

SELECT ENAME

into v_name

FROM emp

WHERE empno =7698;

dbms_output.put_line('v_name: '||v_name);

end;

/













TASK 1

DECLARE

v_enr number;

v_name varchar2(10);

v_course varchar2(20);

v_age number;

v_email varchar2(20);

BEGIN

v_enr := 01234;

v_name := 'DANISH';

v_course := 'DBMS';

v_age := 19;

v_email := 'danishyasin33';

dbms_output.put_line('v_enr:' || v_enr);

dbms_output.put_line('v_name:' || v_name);

dbms_output.put_line('v_course:' || v_course);

dbms_output.put_line('v_age:' || v_age);

dbms_output.put_line('v_email:' || v_email);

end;

/









Task 2

DECLARE

v_grade1 number := 30;

v_grade2 number := 25;

v_grade3 number := 44;

v_grade4 number := 50;

v_grade5 number := 39;

v_average number;

BEGIN

v_average := (v_grade1+v_grade2+v_grade3+v_grade4+v_grade5)/5;

dbms_output.put_line('average: '|| v_average);

end;

/







Task 3

DECLARE

v_tempc number := 50;

v_tempf number;

BEGIN

v_tempf := (v_tempc * (9/5)) + 32;

dbms_output.put_line('Celcius: ' || v_tempc);

dbms_output.put_line('Farehiet: ' || v_tempf);

end;

/











Task 4

CREATE TABLE testtable(

H_ID INT NOT NULL

,H_NAME VARCHAR(10) NOT NULL

,H_LOC VARCHAR(10) NOT NULL

,BRANCHES INT NOT NULL

,PATIENTS INT NOT NULL

,DOCTORS INT NOT NULL);

INSERT INTO testtable(H_ID,H_NAME,H_LOC,BRANCHES,PATIENTS,DOCTORS) VALUES (H_Id,'H_Name','H_Location',Number_of_Branches,Number_of_Patients,Number of Doctors);

INSERT INTO testtable(H_ID,H_NAME,H_LOC,BRANCHES,PATIENTS,DOCTORS) VALUES (1,'----','Islamabad',4,2000,200);

INSERT INTO testtable(H_ID,H_NAME,H_LOC,BRANCHES,PATIENTS,DOCTORS) VALUES (2,'-----','Karachi',5,3000,500);

INSERT INTO testtable(H_ID,H_NAME,H_LOC,BRANCHES,PATIENTS,DOCTORS) VALUES (3,'-----','Lahore',3,5000,700);

INSERT INTO testtable(H_ID,H_NAME,H_LOC,BRANCHES,PATIENTS,DOCTORS) VALUES (4,'-----','Multan',4,1000,400);

INSERT INTO testtable(H_ID,H_NAME,H_LOC,BRANCHES,PATIENTS,DOCTORS) VALUES (5,'-----','Faislabad',6,6000,700);

/



Part 1

DECLARE

v_name varchar2(20);

BEGIN

SELECT H_NAME

INTO v_name

FROM testtable

where BRANCHES > 5;

dbms_output.put_line('Hospital Name: ' || v_name);

end;

/







Part 2

DECLARE

v_name varchar2(20);

BEGIN

SELECT H_LOC

INTO v_name

FROM testtable

where BRANCHES > 5 and DOCTORS > 400;

dbms_output.put_line('Hospital Location: ' || v_name);

end;

/







Part 3

DECLARE

v_hosid1 number;

v_pat1 number;

v_hosid2 number;

v_pat2 number;

BEGIN

SELECT H_ID, PATIENTS

INTO v_hosid1, v_pat1

FROM testtable

where H_LOC = 'Faislabad';

SELECT H_ID, PATIENTS

INTO v_hosid2, v_pat2

FROM testtable

where H_LOC = 'Lahore';

dbms_output.put_line('Hospital ID: ' || v_hosid1 || ' Patients: '||v_pat1);

dbms_output.put_line('Hospital ID: ' || v_hosid2 || ' Patients: '||v_pat2);

end;

/





Part 4

DECLARE

v_hos varchar2(20);

BEGIN

SELECT H_NAME

INTO v_hos

FROM testtable

where DOCTORS > 400 and DOCTORS < 700;

dbms_output.put_line('Hospital Name: ' || v_hos);

end;

/





Part 5



Part 6