资讯专栏INFORMATION COLUMN

PL/SQL(Procedure Language & Structured Query L

hss01248 / 3102人阅读

摘要:基本语法动态获取表中字段的类型,,记录类型返回一个记录类型流程控制选择结构。大于在到之间小于循环结构。

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;

文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。

转载请注明本文地址:https://www.ucloud.cn/yun/32388.html

相关文章

  • PL/SQL 中的存储过程与函数

    摘要:如可使用的函数来计算字符串的长度这将返回,也就是字符串的长度关键字该关键字是用来调用存储过程的。 存储过程概述 PL/SQL块分类 PL/SQL块分类:匿名块、命名块匿名块:不保存在数据库中,没有名称,不可以重复调用命名块:存在数据库中,有名称,可以重复调用命名块:存储过程、函数、触发器、包 存储过程与函数概述 存储过程与函数:都是一个子程序,完成一定的功能存储过程:没有返回值,可以调...

    sutaking 评论0 收藏0
  • PySpark SQL 相关知识介绍

    摘要:大数据除了体积和速度外,数据的多样性和准确性也是大数据的一大特点。这些也被称为大数据的特征。介绍是一个解决大数据问题的分布式可伸缩的框架。介绍计算的模型最早出现在谷歌的一篇研究论文中。相关链接介绍是一个通用的分布式编程框架。 本文作者:foochane 本文链接:https://foochane.cn/article/2019060601.html 1 大数据简介 大数据是这个时代最...

    CoderStudy 评论0 收藏0
  • 怎么用多模型数据库为复杂数据建模?--航空舰队实例

    摘要: Editor’s note: Full disclosure — the author is a developer and software architect at ArangoDB GmbH, which leads the development of the open source multi-model database ArangoDB. In recent years...

    mo0n1andin 评论0 收藏0
  • 怎么用多模型数据库为复杂数据建模?--航空舰队实例

    摘要: Editor’s note: Full disclosure — the author is a developer and software architect at ArangoDB GmbH, which leads the development of the open source multi-model database ArangoDB. In recent years...

    tianhang 评论0 收藏0
  • 怎么用多模型数据库为复杂数据建模?--航空舰队实例

    摘要: Editor’s note: Full disclosure — the author is a developer and software architect at ArangoDB GmbH, which leads the development of the open source multi-model database ArangoDB. In recent years...

    xingqiba 评论0 收藏0

发表评论

0条评论

hss01248

|高级讲师

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<