please need help with this anyone know! Notice: before working on these problems
ID: 3815687 • Letter: P
Question
please need help with this anyone know!
Notice: before working on these problems, you need to
execute the command
set serveroutput on;
re-run the script files companyDB.sql to create the COMPANY database.
run the following command to make the COMPANY database permanently stored in database commit; purse recyclebin; // remove all tables whose names start with BIN$.
before you start working on each problem, run the command “savepoint pt1;”, after you finish testing the solution to the problem, run the command “rollback to savepoint pt1;”. By doing so, you will use the same database (state) when working on each problem.
Use cursor to solve the following questions although some may be solved without it. Since you are required to use cursor for the following questions, you need to write an anonymous block that contains a cursor. Be sure to save all your codes in a file. 1. For each employee who is working on more than 1 project, print his/her name and project names.
2. Use cursor for update to increase the salaries by 10% for the employees who work for department 5
3. Use parameterized cursor to list, for a given project number, the names of employees working on it. Then test the parameterized cursor for the project with pnumber=10;
Explanation / Answer
1.
declare
ename employee.ename%type;
pname employee.pname%type;
CURSOR c_company is
select ename,pname from employee,project where DNO=DNUM having count(*)>1;
BEGIN
OPEN c_company;
Loop
FETCH c_company into ename,pname;
EXIT when c_company %notfound;
dbms_output.put_line(ename||' '||pname);
END Loop
close c_company;
END;
/
In this first we are variables where we need to retireve, then we are declaring the vursor with CURSOR keyword folllowed by cursor name followed by select statement.
As we already know cursors have multiple phases which are opening, fetch and close. After the cursor we need to begin the main section. In this we are opening the cursor then we are fetching the cursor and we are sending the result to the declared variables,durig this fetch time we need to loop the fetch so that the multiple records are get selected.
If records not found then cursor will exit and then we are printing the variables then closing the cursor.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.