PL/SQL programming

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 28 29 30 31 32 33 34 35 36 38 39 40 41 42 43 44 45 46 48 49 50 51

/* By lby indicates the employee's long salary. Starting from the minimum wage, the length of each person is 10%, but the total salary cannot exceed 50 thousand yuan. Please calculate the number of people with long salary and the total salary after long salary, and output the number of long salary and total salary. SQL used: select empno, sal from emp order by sal; select sum (sal) from emp; update emp set sal = sal + sal * 0.1 where empno = ?; */Set serveroutput on declare cursor cemp is select empno, sal from emp order by sal; -- the corresponding variable pempno emp. empno % TYPE; psal emp. sal % TYPE; -- save total wages ptotalSal NUMBER; -- calculator Save the number of people who raise wages pcount NUMBER; begin -- initialize total wages select sum (sal) into ptotalSal from emp; pcount: = 0; open cemp; loop exit when ptotalSal> 60000; fetch cemp into pempno, psal; exit when cemp % notfound; ptotalSal: = ptotalSal + psal * 0.1; if ptotalSal> 60000 then ptotalSal: = ptotalSal-psal * 0.1; exit; else update emp set sal = sal * 1.1 where empno = pempno; pcount: = pcount + 1; end if; end loop; close cemp; commit; dbms_output.put_line ('total salary: '| ptotalSal); dbms_output.put_line ('number of people who raise salaries' | pcount); end ;/