1. 基本语法

declare
v_id employees.employee_id % type ; – 动态获取表中字段的类型
v_email employees.email % type ;
v_salary employees.salary % type ;
begin
v_id := 105;
select e.email, e.salary

into v_email , v_salary
from employees e
where e.employee_id = v_id;

dbms_output.put_line(‘id:’ || v_id || ‘，email:’ || v_email ||’，salary:’ || v_salary);
end;

2. 记录类型

declare
type emp_rec is record(

v_name      employees.last_name %type,
v_salary    employees.salary %type,
v_hire_date date );

v_emp_rec emp_rec;
v_emp_id employees.employee_id%type ;
begin
v_emp_id := 105;
select e.last_name, e.salary , e.hire_date

into v_emp_rec
from employees e
where e.employee_id = v_emp_id;

dbms_output.put_line(‘id:’ || v_emp_id || ‘, name:’ || v_emp_rec.v_name ||

                   ', salary:' || v_emp_rec.v_salary || ', hiredate:' || v_emp_rec.v_hire_date);

end;

declare
v_emp_rec employees %rowtype ; – 返回一个记录类型
v_emp_id employees.employee_id %type ;
begin
v_emp_id := 105;
select * into v_emp_rec from employees e where e.employee_id = v_emp_id;
dbms_output.put_line(‘id:’ || v_emp_id || ‘, name:’ ||

                   v_emp_rec.last_name || ', salary:' || v_emp_rec.salary);

end;

3. 流程控制

–3.1 选择结构
–3.1.1。if…then…;elsif…then…;else…;end if;
declare
v_id employees.employee_id%type ;
v_salary employees.salary%type ;
v_tmp varchar2(20);
begin
v_id := 105;
select e.salary into v_salary from employees e where e.employee_id = v_id;
if v_salary >= 10000 then v_tmp := ‘ 大于 10000’ ;
elsif v_salary >= 5000 then v_tmp := ‘ 在 5000 到 10000 之间 ’ ;
else v_tmp := ‘ 小于 5000’ ;
end if;
dbms_output.put_line(v_tmp);
end;
–3.1.2。case…when…then…else…end;
declare
v_id employees.employee_id%type ;
v_salary employees.employee_id%type ;
v_tmp varchar2(20);
begin
v_id := 105;
select e.salary into v_salary from employees e where e.employee_id = v_id;
v_tmp := case trunc (v_salary / 5000)

 when 2 then '大于 10000' when 1 then '在 5000 到 10000 之间' else '小于 5000' end ;

dbms_output.put_line(v_tmp);
end;
–3.2 循环结构
–3.2.1。loop…exit when… …end loop;
declare
v_i number(8);
begin
v_i := 1;
loop

dbms_output.put_line(v_i);

exit when v_i >= 100;

v_i := v_i + 1;

end loop;
end;
–3.2.2。while…loop… …end loop;
declare
v_i number(8);
begin
v_i := 1;
while v_i <= 100 loop

dbms_output.put_line(v_i);
v_i := v_i + 1;

end loop;
end;
–3.2.3。数字式循环
begin
for c in 1 .. 100 loop

dbms_output.put_line(c);

end loop;
end;
– 求 100 以内的素数
–while 循环实现
declare
v_i number(3); – 用于遍历 2 -100
v_j number(2); – 充当模数
v_flag number(1); – 判断是否不是素数
begin
v_i := 1;
while v_i <= 100 loop

v_j    := 2 ;
v_flag := 0 ;

while v_j <= sqrt(v_i) and v_flag = 0 loop
  if mod (v_i, v_j) = 0 then v_flag := 1 ;
  end if ;
  v_j := v_j + 1;
end loop ;
if v_flag = 0 then dbms_output.put_line (v_i);
end if ;

v_i := v_i + 1;

end loop;
end;
–for 循环实现
declare
v_flag number (1);
begin
v_flag := 0;
for i in 2 .. 100 loop

for j in 2 .. sqrt(i) loop
  if mod (i, j) = 0 then v_flag := 1 ;
    goto lable ; -- 使用 goto 提升效率
  end if ;
end loop ;
if v_flag = 0 then dbms_output.put_line (i);
end if ;
<< lable >>
v_flag := 0 ;

end loop ;
end ;

4. 游标使用

declare
v_name employees.last_name%type ;
v_sal employees.salary%type ;
Cursor v_emp_cur is

select e.last_name , e.salary from employees e where e.department_id = 80;

begin
open v_emp_cur;
fetch v_emp_cur into v_name, v_sal;
while v_emp_cur%found loop –v_sal_cur%found 用于判断游标最近一次的读记录是否成功返回

dbms_output.put_line('name:' || v_name || ', sal:' || v_sal);
fetch v_emp_cur into v_name, v_sal ;

end loop;
close v_emp_cur;

end;

declare
type emp_rec is record(

v_name      employees.last_name %type,
v_sal       employees.salary% type,
v_hire_date date );

v_emp_rec emp_rec;
Cursor v_emp_cur is select last_name, salary , hire_date from employeeswhere department_id = 80 ;
begin
open v_emp_cur;
fetch v_emp_cur into v_emp_rec;
while v_emp_cur%found loop

dbms_output.put_line('name:' || v_emp_rec.v_name || ', sal:' ||
                     v_emp_rec.v_sal || ', hire_date:' || v_emp_rec.v_hire_date);
fetch v_emp_cur into v_emp_rec;

end loop;
close v_emp_cur;

end;

declare
Cursor v_emp_cur is select last_name, salary , hire_date from employeeswhere department_id = 80 ;
begin
for c in v_emp_cur loop

dbms_output.put_line('name:' || c.last_name || ', sal:' || c.salary ||
                     ', hire_date:' || c.hire_date );

end loop;
end;
– 利用游标, 调整公司中员工的工资:
工资范围 调整基数
0 – 5000 5%
5000 – 10000 3%
10000 – 15000 2%
15000 – 1%
declare
v_tmp number(4 , 2);
Cursor v_emp_cur is select e.employee_id, e.salary from employees e ;
begin
for c in v_emp_cur loop

if c.salary >= 15000 then v_tmp := 0.01;
elsif c.salary >= 10000 then v_tmp := 0.02;
elsif c.salary >= 5000 then v_tmp := 0.03;
else v_tmp := 0.05;
end if ;

update employees set salary = c.salary * (1 + v_tmp) where employee_id = c.employee_id ;

end loop;

commit;
end;
– 带参游标
declare
Cursor v_emp_cur (dept_id number, sal number) is

select e.last_name , e.salary from employees e where e.department_id = dept_id and e.salary > sal ;

begin
for c in v_emp_cur(dept_id => 80 , sal => 6000) loop – 通过 => 符号向 dept_id 和 sal 赋值

dbms_output.put_line ('name:' || c.last_name || ', salary:' || c.salary);

end loop ;
end ;
– 隐式游标
begin
update employees set salary = 10000 where employee_id = 1111 ;
if sql %notfound then dbms_output.put_line (‘ 查无此人 ’); –sql%notfound 最近一次读记录时未成功返回
end if ;
end ;

5. 异常处理

6. 存储过程和存储函数的使用

create or replace function tst_func (i number, j number)
return date
is
v_date date;
begin
dbms_output.put_line(i + j);
v_date:= sysdate;
return v_date;
end;
执行方法①：
select tst_func (2, 3) from dual;
执行方法②：
begin
dbms_output.put_line(tst_func(2 , 3));
end;
– 获取指定部门的工资和该部门的人数
create or replace function sal_func (dept_id number, v_mbnum out number) –out 型参数实现多个返回值
return number
is
v_sum_sal number(20 , 2);
Cursor sal_cur is select salary from employees where department_id = dept_id;
begin
v_sum_sal := 0;
v_mbnum := 0;
for c in sal_cur loop

v_mbnum   := v_mbnum + 1;
v_sum_sal := v_sum_sal + c.salary;

end loop;
return v_sum_sal;
end;
declare
v_sum_sal number(20 , 2);
v_mbnum number(5);
v_dept_id number(4);
begin
v_mbnum := 0;
v_dept_id := 90;
v_sum_sal := sal_func(v_dept_id , v_mbnum);
dbms_output.put_line(‘v_mbnum:’ || v_mbnum || ‘, v_sum_sal:’ || v_sum_sal);

end;

– 使用存储过程实现上面的功能
create or replace procedure sal_proc (dept_id number, v_mbnum out number, v_sum_sal out number)
is
Cursor sal_cur is select salary from employees where department_id = dept_id;
begin
v_sum_sal := 0;
v_mbnum := 0;
for c in sal_cur loop

v_mbnum   := v_mbnum + 1;
v_sum_sal := v_sum_sal + c.salary;

end loop;

end;

declare
v_sum_sal number(20 , 2);
v_mbnum number(5);
v_dept_id number(4);
begin
v_mbnum := 0;
v_sum_sal := 0;
v_dept_id := 90;
sal_proc(v_dept_id, v_mbnum , v_sum_sal);
dbms_output.put_line(‘v_mbnum:’ || v_mbnum || ‘, v_sum_sal:’ || v_sum_sal);
end;
– 实现指定部门在 1995 年前进公司的员工加薪 5%,1998-1995 加薪 3%,1998 年之后加薪 5% 并返回加薪成本
create or replace procedure sal_proc (dept_id number, v_cost out number)
is
v_rate number(4 , 2);
Cursor sal_cur is select employee_id, salary , hire_date from employees where department_id = dept_id ;
begin
v_cost:= 0;

for c in sal_cur loop

  if to_char (c.hire_date, 'yyyy') < '1995' then v_rate:=0.05 ;
  elsif to_char (c.hire_date, 'yyyy') < '1998' then v_rate:=0.03 ;
  else v_rate :=0.01;
  end if ;
 
  update employees set salary = c.salary * (1 + v_rate) where employee_id = c.employee_id ;
  v_cost:= v_cost + c.salary * v_rate;

end loop;

end;

declare
v_dept_id number(5);
v_cost number (10 , 2);
begin
v_dept_id:= 90;
v_cost:= 0;
sal_proc(v_dept_id, v_cost);

dbms_output.put_line(v_dept_id||’ 此次提薪成本：’ ||v_cost);
end;

7. 触发器

create or replace trigger emp_tr
before delete
on employees –[after|before] [update|delete|insert] on …
for each row
when (sysdate < to_date (‘2222’, ‘yyyy’))
begin
insert into emp_bak values(: old.employee_id, :old.salary); – 删除前对数据进行备份
dbms_output.put_line(‘old sal:’ || :old.salary || ‘, new sal:’ || :new.salary); – 用:new.、:old. 获得修改前、后的值
end;

==========================================================================
mysql 存储过程
DELIMITER $
CREATE PROCEDURE mybatis.get_meb_cot(IN gd INT, OUT meb_count INT)
BEGIN

 SELECT COUNT(id) FROM members WHERE gender = gd INTO meb_count;

END
$
DROP PROCEDURE get_meb_cot;
SET @count = 0;
CALL mybatis.get_meb_cot(0, @count);
SELECT @count;