Archive for the ‘Oracle’ Category

PL/SQL Cursor 6 – Update values based on different conditions

Select employees from the table, Emp, who
1. Have a salary of Rs.10,000/- and give them an increment of 10% in salary and hence update the commission also.
2. Have a salary between Rs.5000/- and Rs.10,000/- and give them an increment of 20% in salary and hence update the commission also.
3. If the salary is less than Rs.5000/-, then give them an increment of 30% in salary and hence update the commission also.

Answer

declare
    emp_row empp_a5518%rowtype;
    cursor cur1 is select * from empp_a5518;
    total number(9,2);
begin
    open cur1;
    loop
        fetch cur1 into emp_row;
        exit when cur1%notfound;
        
        select emp_row.salary into total from empp_a5518 where empno=emp_row.empno;
                                  
        if(total>=10000) then
            update empp_a5518 set salary=salary*1.10, comm=comm*1.10 where empno=emp_row.empno;
        elsif(total>=5000) then

Read more »

PL/SQL Cursor 5 – Create Global Temporary Table

Create a PL/SQL table containing instructorid, firstname, lastname of instructor table and insert values into the table.

Answer

declare
    cursor cur1 is select * from empp_a5518;
    emp_row cur1%rowtype;
    ch varchar2(20):=1;
begin
    
    select upper(tname) into ch from tab where tname like upper('instructor_a5518');

    if(ch=upper('instructor_a5518')) then
        dbms_output.put_line('instructor_a5518 table already created !!');
    else
        execute immediate 'CREATE GLOBAL TEMPORARY TABLE instructor_a5518 ( id  NUMBER,fname VARCHAR2(20), lname  VARCHAR2(20) ) ON COMMIT PRESERVE ROWS';
    end if;

    

Read more »

PL/SQL Cursor 4 – Fetch cursor contents

Create a PL/SQL block to retrieve name of each department from dept table and print each department name to screen, incorporating a PL/SQL table.

A) Declare PL/SQL table my_dept_table to temporarily store name of departments.   

B) Using a loop retrieves names of all departments currently in dept table and store them in PL/SQL table. Each department number is a multiple of 10.                          

C) Using another loop, retrieves department names from PL/SQL table and print them to screen, using dbms_output.put_line.

Answer

declare

            cursor cur1 is select * from ddept_a5518;

            cursor cur2 is select * from my_dept_table;

            dept_row cur1%rowtype;

            total number(4);

            dept_row_1 cur2%rowtype;

            total number(4);

begin

            –execute immediate 'CREATE GLOBAL TEMPORARY TABLE my_dept_table ( dno  NUMBER,    dname  VARCHAR2(20) ) ON COMMIT PRESERVE ROWS';

            delete from my_dept_table;
 

Read more »