Pl/sql imp program 2

 Full course of c++ programming language

Click here > C++ programming language

=========================================

1]Write a PL/SQL block for insertion into table EMPDEPT table with thefollowing.

Calculations:

HRA= 50% of BASICDA= 20% of BASIC PF= 7% of BASIC

NETPAY= BASIC + HRA + DA + PF

EMPDEPT (ENO, ENAME, DEPTNO, BASIC, HRA, DA, PF)

SQL> create table Empdept

  2 (Eno number primary key,

  3 Ename varchar(20),

  4 Deptno number,

  5 Basic number,

  6 HRA number,

  7 DA number,

  8 PF number,

  9 Netpay number);

Table created.

set severoutput on

declare

 Eno number:=&Eno;

 Ename varchar2(20):=&Ename;

 Deptno number:=&Deptno;

 Basic number:=&Basic;

 HRA number:=Basic*0.5;

 DA number:=Basic*0.2;

 PF number:=Basic*0.07;

 Netpay number:=Basic+HRA+DA-PF;

begin

 insert into Empdept(Eno,Ename,Deptno,Basic,HRA,DA,PF,Netpay)values(Eno,Ename,Deptno,Basic,HRA,DA,PF,Netpay);

end;

/

SQL> @q1

Enter value for eno: 4

old 2: Eno number:=&Eno;

new 2: Eno number:=4;

Enter value for ename: 'Mohit'

old 3: Ename varchar2(20):=&Ename;

new 3: Ename varchar2(20):='Mohit';

Enter value for deptno: 103

old 4: Deptno number:=&Deptno;

new 4: Deptno number:=103;

Enter value for basic: 1200

old 5: Basic number:=&Basic;

new 5: Basic number:=1200;

PL/SQL procedure successfully completed.

========================================

2]Write PL/SQL block that will accept employee number from user and deduct an amount of Rs.200 from the

inputted employee, if he has a salary less than 1000 after Salary is deducted, it display message‘s Salary is

less than 1000’. The process is to be fired on table employee (emp_no, name, salary).

SQL> create table employee

  2 (emp_no number primary key,

  3 name varchar(20),

  4 salary number);

Table created.

set serveroutput on

declare 

 id number:=&id;

 sal number;

begin

 update employee set salary=salary-200 where emp_no=id;

 select salary into sal from employee where emp_no=id;

 if sal<1000 then

  dbms_output.put_line('salary is less than 1000');

 end if;

end;

/

SQL> @q2

Enter value for id: 1

old 2: id number:=&id;

new 2: id number:=1;

salary is less than 1000

PL/SQL procedure successfully completed.

==========================================

3]Consider the following table and Do as Directed

Product (Prod_No, Name, Price_per_Unit, Quantity_Sold, Total_Sale)

Note: Consider ProdNo, Name, PricePerUnit and QuantitySold columns have values.

TotalSale column has null values.

Write a PL/SQL block that accepts prodNo from user to calculate total sale for that product and update it in

product table.

SQL> create table Product

  2 (prod_no number primary key,

  3 name varchar(20),

  4 price_per_unit number,

  5 quantity_sold number,

  6 total_sale number)

  7 /

Table created.

set serveroutput on

declare

 p_no number:=&prod_no;

 t_sale number;

begin

 select price_per_unit*quantity_sold into t_sale from Product where Prod_no=p_no;

 update Product set total_sale=t_sale where prod_no=p_no;

 dbms_output.put_line('Total sale for product '||p_no||' is '||t_sale);

end;

/

SQL> select * from Product;

   PROD_NO NAME PRICE_PER_UNIT QUANTITY_SOLD TOTAL_SALE

---------- -------------------- -------------- ------------- ----------

         1 Laptop 30000 3      

SQL> @q3

Enter value for prod_no: 1

old 2: p_no number:=&prod_no;

new 2: p_no number:=1;

Total sale for product 1 is 90000

PL/SQL procedure successfully completed.

==========================================

4]Create following tables.

Doctormaster (doct_id, name, address, specialization)

Patientmaster (pat_id, pat_name, DOB, address, phoneno, doct_id)

Write a PL/SQL block to take input of patient id and display patient information if available. If patient

information is not available then print appropriate message.

SQL> create table Doctormaster

  2 (doct_id number primary key,

  3 name varchar(20),

  4 address varchar(20),

  5 specialization varchar(20));

Table created.

  1 create table Patientmaster

  2 (pat_id number primary key,

  3 pat_name varchar(20),

  4 dob date,

  5 address varchar(20),

  6 phoneno number(10),

  7 doct_id number,

  8* constraint d foreign key(doct_id) references Doctormaster(doct_id))

SQL> /

Table created.

DECLARE

  v_pat_id number:= &Enter_patient_id;

  v_pat_name varchar(20);

  v_dob date;

  v_address varchar(20);

  v_phoneno number(10);

  v_doct_id number;

  v_doct_name varchar(20);

BEGIN

 

  SELECT pat_name, dob, address, phoneno, doct_id INTO v_pat_name, v_dob, v_address, v_phoneno, v_doct_id 

  FROM Patientmaster WHERE pat_id = v_pat_id;

  SELECT name INTO v_doct_name FROM Doctormaster WHERE doct_id = v_doct_id;

  DBMS_OUTPUT.PUT_LINE('Patient ID: ' || v_pat_id);

  DBMS_OUTPUT.PUT_LINE('Patient Name: ' || v_pat_name);

  DBMS_OUTPUT.PUT_LINE('Date of Birth: ' || v_dob);

  DBMS_OUTPUT.PUT_LINE('Address: ' || v_address);

  DBMS_OUTPUT.PUT_LINE('Phone Number: ' || v_phoneno);

  DBMS_OUTPUT.PUT_LINE('Doctor Name: ' || v_doct_name);

EXCEPTION

    WHEN NO_DATA_FOUND THEN

    DBMS_OUTPUT.PUT_LINE('Patient information not found for ID ' || v_pat_id);

END;

/

SQL> @q4

Enter value for enter_patient_id: 102

old 2: v_pat_id number:= &Enter_patient_id;

new 2: v_pat_id number:= 102;

Patient ID: 102

Patient Name: Parth

Date of Birth: 28-APR-00

Address: mumbai

Phone Number: 4324685786

Doctor Name: dr shah

PL/SQL procedure successfully completed.

==========================================