今天学了pl/sql基础，做了几道基础的编程题，做个笔记，方便复习。如果有做错的地方或者有待改进的地方，欢迎指正，不胜感激。
练习参考的数据库表为oracle中自带的employees表。
1.求1~100之间素数
declare
m_flag boolean := true;
begin
for i in 1 .. 100 loop
for j in 2 .. i - 1 loop
if mod(i, j) = 0 then
m_flag := false;
end if;
end loop;
if m_flag then
dbms_output.put_line(i);
end if;
m_flag := true;
end loop;
end;
2.对所有JOB_ID为IT_PROG 薪水增加一倍.
declare
cursor cur_raise_it is select * from employees e where e.job_id='IT_PROG' for update;
begin
for i in cur_raise_it loop
update employees e set e.salary=e.salary*2 where current of cur_raise_it;
end loop;
end;
3.编写一个函数以检查所指定雇员的薪水是否有效范围内。不同职位的薪水范围为：
JOB_ID Range
IT_PROG 5500-8500
ST_CLERK 2501-3500
SA_REP 7000-8500
Others No operation.
如果薪水在此范围内，则显示消息"Salary is OK"，否则，更新薪水为该范围内的最小值。
create or replace function check_sal(emp_no employees.employee_id%type)
return varchar2 as
v_job employees.job_id%type;
v_sal employees.salary%type;
v_msg varchar2(100) := 'Salary Is Ok';
begin
select e.job_id, e.salary
into v_job, v_sal
from employees e
where e.employee_id = emp_no;
if v_job = 'IT_PROG' then
if v_sal not between 5500 and 8500 then
update employees e set e.salary = 8500;
v_msg := 'salary changes from ' || v_sal || ' to' || ' 5500';
end if;
elsif v_job = 'ST_CLERK' then
if v_sal not between 2501 and 3500 then
update employees e set e.salary = 3500;
v_msg := 'salary changes from ' || v_sal || ' to' || ' 2501';
end if;
elsif v_job = 'SA_REP' then
if v_sal not between 7000 and 8500 then
update employees e set e.salary = 8500;
v_msg := 'salary changes from ' || v_sal || ' to' || ' 7000';
end if;
else
null;
end if;
return v_msg;
end;--调用示例
declare v_msg varchar2(100);
begin
v_msg:=check_sal(emp_no=>102);
dbms_output.put_line(v_msg);
end;
4.对名字以"A"或"H"开始的所有雇员按他们的基本薪水的10%加薪。
declare
cursor cur_sal_raise is select * from employees e where substr(e.last_name,1,1) in('A','H');
begin
for emp_record in cur_sal_raise loop
update employees e set e.salary = e.salary * 1.1 WHERE CURRENT OF emp_record;
end loop;
end;
5.编写一PL/SQL，以提升两个资格最老的"职员"为"高级职员"。（按工作时间算）
declare
cursor cur_raise_old is
select employee_id,
first_name,
last_name,
email,
job_id,
hire_date,
salary
from (select e.employee_id,
e.job_id,
e.first_name,
e.last_name,
e.email,
e.hire_date,
e.salary,
months_between(sysdate, e.hire_date) months
from employees e
order by months desc) t
where rownum <= 2 ;
begin
for i in cur_raise_old loop
update employees e set e.job_id ='高职' where i.employee_id =e.employee_id;
end loop;
end;
6.编写存储过程检查如果已经雇佣该雇员超过60个月，则给他额外加薪3000.
create or replace procedure pro_raise_old(emp_no in number) is
v_hiredate employees.hire_date%type;
v_sal employees.salary%type;
begin
select e.hire_date,e.salary into v_hiredate,v_sal from employees e where e.employee_id=emp_no;
if months_between(sysdate,v_hiredate)>60 then v_sal:=v_sal+3000;
end if;
update employees e set e.salary=v_sal where e.employee_id=emp_no;
end;--调用示例
begin
pro_raise_old(emp_no=>103);
end;
7.编程实现对所有雇员按他们基本薪水的10%加薪，如果所增加的薪水大于1000，则取消加薪.
declare
cursor cur_raise_two is
select * from employees for update;
begin
for i in cur_raise_two loop
if i.salary * 0.1 > 1000 then
null;
else
update employees e
set e.salary = e.salary*1.1
where current of cur_raise_two;
end if;
end loop;
end;
8.A单价49，B单价39，C单价17，现有2000元，要购买A,B,C，请给出花费最接近2000元（<=2000元）的购买计划，打印出来。（今天上课时的课堂练习）
DECLARE
a NUMBER := 49;
b NUMBER := 39;
c NUMBER := 17;
total_amount NUMBER := 2000;
a_qty NUMBER;
b_qty NUMBER;
c_qty NUMBER;
a_amount number;
b_amount number;
c_amount number;
l_max_amount NUMBER := 0;
BEGIN
FOR i IN 0 .. trunc(total_amount / a) LOOP
FOR j IN 0 .. trunc(total_amount / b) LOOP
FOR k IN 0 .. trunc(total_amount / c) LOOP
IF a * i + b * j + c * k >= l_max_amount AND
a * i + b * j + c * k <= total_amount THEN
l_max_amount := a * i + b * j + c * k;
a_amount := i;
b_amount := j;
c_amount := k;
END IF;
END LOOP;
END LOOP;
END LOOP;
dbms_output.put_line('a:' || a_amount);
dbms_output.put_line('b:' || b_amount);
dbms_output.put_line('c:' || c_amount);
dbms_output.put_line('最接近的数字为：' || l_max_amount);
END;