Oracle就业课第四课之子程序
【摘要】
4、子程序
回顾
练习：
declare
v_name emp.ename%type:='张三';
v_job emp.job%type:='游客';
begin
for n ...
4、子程序
回顾
练习：
declare
v_name emp.ename%type:='张三';
v_job emp.job%type:='游客';
begin
for n in 1..3 loop
insert into emp(empno,ename,job,mgr,hiredate,sal,deptno) values(n*10,v_name||n,v_job,8000,to_date('2020-9-16','yyyy-MM-dd'),16000,10);
end loop;
commit; --提交事务
exception
when others then
rollback; --回滚事务
end;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
学习目标
1、子程序
2、存储过程
3、函数
4、包
学习内容
1、子程序
plsql:过程化的sql语言；变量、常量、匿名块、分支语句、循环语句
子程序：命名的程序块，存储过程 和 函数。
2、存储过程
分析：完成一些指定的业务，单一的命令无法搞定；比如：转账，分页操作
存储过程：类似于java中的方法，通过多条sql命令，完成一个特定的任务。
优点：
1、执行效率高：预编译的，存储在服务器上，第一次创建好，系统会进行预编译；
2、减少网络流量：代码封装到过程中，只需要调用过程名就行。
3、安全性：A:小明 B：小红 C：小青
4、重用性：代码角度考虑。
存储过程中能写的命令：
1、insert,update,delete,select …into 变量
2、过程控制命令(条件、循环)
3、不能写DDL
使用存储过程
1、定义过程
-- 创建过程 procedure:过程
create or replace procedure sp_test1(
-- 参数
)
as
-- 声明部分 （临时变量：过程中使用的变量）
begin
exception
end;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
2、调用过程
1 call 过程名(参数);
2、
begin
过程名(参数);
end;
3、sqlplus:
execute 过程名();
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
栗子：
1、转账：无参数
create or replace procedure sp_zhuanzhang
as
v_money bank.balance%type:=1000; --要转账的钱数
begin
update bank set balance=balance-v_money where name='李四';
update bank set balance=balance+v_money where name='张三';
commit;
exception
when others then
rollback;
end;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
调用:
call sp_zhuanzhang();
begin
sp_zhuanzhang;
end;
- 1
- 2
- 3
- 4
- 5
- 6
2、转账：带参数
--flag :1 ：张三---》李四 2：李四 --->张三 钱数
create or replace procedure sp_zhuanzhang2
(
--输入参数
--参数名 数据类型（变量声明）
flag in number,
money in number
)
as
begin
if flag=1 then
update bank set balance=balance-money where name='张三';
update bank set balance=balance+money where name='李四';
else
update bank set balance=balance-money where name='李四';
update bank set balance=balance+money where name='张三';
end if;
commit;
exception
when others then
rollback;
end;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
调用：
declare
flag number(1):=2;
money number(5):=500;
begin
sp_zhuanzhang2(flag,money);
end;
- 1
- 2
- 3
- 4
- 5
- 6
3、转账：带输入和输出参数
create or replace procedure sp_zhuanzhang3
(
--输入参数
--参数名 数据类型（变量声明）
flag in number,
money in number,
--输出参数：调用过程后，可以在外部获取到值的数据：类似于返回值，可以有多个
balance1 out bank.balance%type,
balance2 out bank.balance%type
)
as
begin
if flag=1 then
update bank set balance=balance-money where name='张三';
update bank set balance=balance+money where name='李四';
else
update bank set balance=balance-money where name='李四';
update bank set balance=balance+money where name='张三';
end if;
commit;
select balance into balance1 from bank where name='张三';
select balance into balance2 from bank where name='李四';
exception
when others then
rollback;
end;
- 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
- 27
调用:
declare
--声明变量：用来接受输出参数的值
b1 bank.balance%type;
b2 bank.balance%type;
begin
sp_zhuanzhang3(2,1000,balance2=>b1,balance1=>b2);
dbms_output.put_line('张三:'||b2||'李四:'||b1);
end;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
过程调用中的参数传递
1、按照位置传递
2、按照名字传值：顺序无关
参数名=>变量 参数名=>变量 参数名=>变量
- 1
3、组合传递
位置传递+名字传递；位置传递（靠前）
3、函数
本质上跟过程一样：必须有返回值
定义函数：
create or replace function 名字(
-- 参数
--名字 类型
) return 数据类型
as
begin
return 结果;
end;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
例子：
1、根据部门编号查询部门名称
create or replace function f_getDname(
-- 传入参数 ：部门编号
dno dept.deptno%type
) return varchar2 --返回字符串
as
v_name dept.dname%type;
begin
select dname into v_name from dept where deptno=dno;
return v_name; --返回结果
end;
--调用
select empno,ename,deptno,f_getdname(deptno) 部门 from emp
where f_getdname(deptno)='外交部';
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
2、根据部门编号统计部门人数
create or replace function f_getdeptcount(
--参数
dno number
)return number
as
v_count number(5);
begin
select count(*) into v_count from emp where deptno=dno;
return v_count;
end;
--调用
select deptno,dname,f_getdeptcount(deptno) 人数 from dept;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
3、生成随机数字的函数
create or replace function getrandomvalue return number
as
n number(20);
begin
n:=dbms_random.value(1,10);
return n;
end;
--调用
select getrandomvalue() from dual ;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
调用：
declare
n number(5);
begin
n:=getrandomvalue();
dbms_output.put_line(n);
end;
- 1
- 2
- 3
- 4
- 5
- 6
4、包
包：package
一系列的过程和函数
可以根据业务的不同创建不同的包：声明变量+过程+函数；
包：
1、包规范：类似于java中的接口
2、包体：类似于实现类
创建包规范：
create or replace package 名字
is
--声明 变量 过程 函数
end 名字;
- 1
- 2
- 3
- 4
创建包体：
create or replace package body 名字
is
--实现包规范定义好的过程和函数
end;
- 1
- 2
- 3
- 4
栗子：
create or replace package myfirstpackage
is
--声明 全局的变量
dno dept.deptno%type;
--过程：向员工表中录入数据，不能录入重名的
procedure add_emp(vno in number,vname in varchar2);
--过程：根据部门编号，删除部门信息，只能删除没有员工的部门
procedure del_dept(vno in number);
end package;
--包体
create or replace package body myfirstpackage
is
--过程实现
procedure add_emp(vno in number,vname in varchar2)
as
v_count number(2);-- 满足条件的员工的数量
begin
select count(*) into v_count from emp where ename=vname;
if v_count>0 then
--提示错误消息
--dbms_output.put_line('');
--raise_appliction_error:抛出一个自定义的错误消息，错误号：-20000-
raise_application_error(-20001,'名字重复');
else
insert into emp(empno,ename) values(vno,vname);
commit;
end if;
end;
--过程实现:根据部门编号，删除部门信息，只能删除没有员工的部门
procedure del_dept(vno in number)
as
v_count number(3);
begin
--根据部门编号查询该部门对应的人数
select count(*) into v_count from emp where deptno=vno;
if v_count=0 then
delete from dept where deptno=vno;
commit;
else
raise_application_error(-20002,'该部门下有员工,无法删除');
end if;
end;
end myfirstpackage;
- 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
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
总结
1、存储过程
2、函数
相同点：
1、预编译、执行效率高、安全性、减少网络流量
2、输入参数、输出参数
不同点：
1、函数必须指定return（返回值）
3、包：包规范和包体（实现）；
文章来源: aaaedu.blog.csdn.net，作者：tea_year，版权归原作者所有，如需转载，请联系作者。
原文链接：aaaedu.blog.csdn.net/article/details/108914496
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）