Dozens of practical pl/sql (2)

Second Stage

Q. Write a PL/SQL program block for all employees whose name begins with "A" or "S" to pay a salary increase of 10% of their basic salary.

A.

DECLARE

CURSOR C1 is

SELECT * from emp WHERE SUBSTR (ename,1,1) = ' A ' OR SUBSTR (ename,1,1) = ' S ' for UPDATE of Sal;



BEGIN

For I in C1

LOOP

UPDATE emp SET SAL=NVL (sal,0) +NVL (sal,0) *0.1 WHERE Current of C1;

End LOOP;

End;

/

Q. Write a pl/sql, and add 500 commission to all "salespeople" (salesman).

A.

DECLARE

CURSOR C1 is

SELECT * from emp WHERE job= ' salesman ' for UPDATE of Sal;

BEGIN

For I in C1

LOOP

UPDATE emp SET SAL=NVL (sal,0) +500 WHERE Current of C1;

End LOOP;

End;

/

Q. Prepare a pl/sql to upgrade the two oldest "staff" as "senior staff". (The longer the work time, the higher the priority)

A.

DECLARE

CURSOR C1 is

SELECT * from emp WHERE job= ' clerk ' ORDER by HireDate for UPDATE of job;

--in ascending order, length of seniority in front



BEGIN

For I in C1

LOOP

EXIT when c1%rowcount>2;

Dbms_output. Put_Line (I.ename);

UPDATE emp SET job= ' Highclerk ' WHERE current of C1;

End LOOP;

End;

/

Q. Prepare a pl/sql for all employees to pay a salary increase of 10% per cent of their basic salary and, if the increased salary is greater than 5000, cancel the increase.

A.

DECLARE

CURSOR C1 is SELECT * from EMP for UPDATE of Sal;



BEGIN

For I in C1

LOOP



IF (i.sal+i.sal*0.1) <=5000 THEN

UPDATE EMP SET sal=sal+sal*0.1 WHERE Current of C1;

Dbms_output. Put_Line (i.sal);

End IF;



End LOOP;

End;

/

Q. Displays the fourth record in the EMP.

A.

DECLARE

CURSOR C1 is a SELECT * from EMP;



BEGIN

For I in C1

LOOP

IF c1%rowcount=4 THEN

Dbms_output. Put_Line (I. EMPNO | | ' ' ||  I.ename | | ' ' ||  I.job | | ' ' || I.mgr | | ' ' || I.hiredate | | ' ' || I.sal | | ' ' || I.comm | | ' ' || I.DEPTNO);

EXIT;

End IF;

End LOOP;

End;

/