--存储过程 procedure
语法 --相当于没有return的函数
create or replace procedure 存过名(参数1 [in]|out 数据类型)
is|as
begin
[exception]
end; --制作一个存过,把emp表的员工信息插入到emp1之后
--更新emp1的员工姓名为小写
--删除1980年入职的员工信息
create or replace procedure pro_1
is
begin
insert into emp1 select * from emp;
update emp1 set ename=lower(ename);
delete from emp1 where to_char(hiredate,'yyyy')=1980;
end;--存过相当于一次性执行多条语句,可以命名,可以保存
--调用存过
call pro_1();select *
from emp1 --相对于sql,存储过程有什么好处?
1.存储过程只在创建时进行编译,以后每次执行都不需要编译,而一般的sql语句
每次执行一次,都有编译一次,所以使用存储过程可以提高数据库的执行速度.
2.当数据库进行复杂的操作时(比如多表连接查询,修改等操作),可以将此复杂的
事务处理结合在一起使用这些操作,如果用sql需要多次连接数据库,如果用存过
只需要连接连接一次数据库.减少数据库的连接次数.
3.存储过程可以重复使用,减少数据库开发人员的工作量. --有in无out
--输入一个员工姓名,打印他的工资
create or replace procedure pro_2(v_ename varchar2)
is
v_sal number;
begin
select sal into v_sal
from emp
where ename=v_ename;
dbms_output.put_line(v_sal);
end; call pro_2('KING')
--编写一个存过,输入一个名字,
--如果工资比MARTIN高,打印高,低,打印低,一样,打印巧了.
create or replace procedure pro_3(v_ename varchar2)
is
v_sal number;
v_sal_m number;
begin
select sal into v_sal
from emp
where ename=v_ename;
select sal into v_sal_m
from emp
where ename='MARTIN';
if v_sal>v_sal_m then dbms_output.put_Line('高');
elsif v_sal<v_sal_m then dbms_output.put_line('低');
else dbms_output.put_line('巧了');
end if;
end;call pro_3('KING')
--编写一个存过,输入一个数字
--打印这个数字层数的等腰三角形
create or replace procedure pro_4(v1 number)
is
begin
for i in 1..v1 loop
for j in 1..v1-i loop
dbms_output.put(' ');
end loop;
for k in 1..i loop
dbms_output.put('* ');
end loop;
dbms_output.put_line('');
end loop;
end; call pro_4(10)
--编写一个存过,输入一个数字
--打印这个数字层数的等腰梯形
create or replace procedure pro_5(v1 number)
is
begin
for i in v1/3..v1 loop
for j in 1..v1-i loop
dbms_output.put(' ');
end loop;
for k in 1..i loop
dbms_output.put('* ');
end loop;
dbms_output.put_line('');
end loop;
end; call pro_5(12)
--编写一个存过,输入一个数字
--打印这个数字层数的长方形
create or replace procedure pro_6(v1 number)
is
begin
for i in 1..v1 loop
for j in 1..v1*2/3 loop
dbms_output.put(' ');
end loop;
for i in 1..v1/3 loop
dbms_output.put('* ');
end loop;
dbms_output.put_line('');
end loop;
end;call pro_6(10);
--组合成圣诞树
create or replace procedure pro_sd(v1 number)
is
begin
for i in 1..v1 loop
for j in 1..v1-i loop
dbms_output.put(' ');
end loop;
for k in 1..i loop
dbms_output.put('* ');
end loop;
dbms_output.put_line('');
end loop;
for i in v1/3..v1 loop
for j in 1..v1-i loop
dbms_output.put(' ');
end loop;
for k in 1..i loop
dbms_output.put('* ');
end loop;
dbms_output.put_line('');
end loop;
for i in 1..v1 loop
for j in 1..v1*2/3 loop
dbms_output.put(' ');
end loop;
for i in 1..v1/3 loop
dbms_output.put('* ');
end loop;
dbms_output.put_line('');
end loop;
end;call pro_sd(10);
--存过调用存过
create or replace procedure pro_sds(v1 number)
is
begin
pro_4(v1);
pro_5(v1);
pro_6(v1);
end;call pro_sds(15)
编写一个存过,输入一个数字和一个部门编号,
要求数字必须是0-9的整数(如果不是抛出异常,报错'请输入0-9的整数')
如果输入的编号emp1表不存在,抛出异常,报错'部门错误'
当部门人数小于该数字,则将部门的员工信息插入到emp_kk，
并打印插入了多少行
当部门人数大于该数字,则将部门的员工姓名、员工编号删除,
并打印删除了几个人
当部门人数等于该数字,则更新该部门的员工工资/100之后再3次方,
并打印更新了几个人的工资--emp1表数据同emp
create table emp1 as select * from emp;
--emp_kk表格式同emp,但是没有数据
create table emp_kk as select * from emp where 1=2;create or replace procedure pro_8(v_num int,v_deptno number) is
err1 exception;
err2 exception;
v_count number;
begin
if v_num not in (0,1,2,3,4,5,6,7,8,9)then
raise err1;
end if;
select count(1)into v_count from emp where deptno=v_deptno;--部门人数
if v_count=0 then
raise err2;
end if;
case when v_count<v_num then
insert into emp_kk select * from emp1 where deptno=v_deptno;
dbms_output.put_line('插入了'||sql%rowcount||'行');
when v_count>v_num then
update emp1 set ename=null,empno=null where deptno=v_deptno;
dbms_output.put_line('删除了'||sql%rowcount||'人');
when v_count=v_num then
update emp1 set sal=power(sal/100,3) where deptno=v_deptno;
dbms_output.put_line('更新了'||sql%rowcount||'人的工资');
end case;
exception
when err1 then
raise_application_error(-20000,'请输入0-9的整数');
when err2 then
raise_application_error(-20000,'部门编号不正确');
end;
--调用
call pro_8(3,10);select * from emp1;
select * from emp_kk; --带游标的
--输入一个字母,打印名字包含这个字母的员工信息和
--部门名称包含这个字母的部门信息create or replace procedure pro_7(v1 varchar2)
is
cur_1 sys_refcursor;
v_emp emp%rowtype;
v_dept dept%rowtype;
begin
open cur_1 for select *
from emp
where ename like '%'||v1||'%';
loop
fetch cur_1 into v_emp;
exit when cur_1%notfound;
dbms_output.put_line(v_emp.empno||' '||v_emp.ename||' '||v_emp.job||' '||v_emp.mgr||' '||v_emp.hiredate||' '||v_emp.sal||' '||v_emp.comm||' '||v_emp.deptno);
end loop;
close cur_1;
open cur_1 for select *
from dept
where dname like '%'||v1||'%';
loop
fetch cur_1 into v_dept;
exit when cur_1%notfound;
dbms_output.put_line(v_dept.deptno||' '||v_dept.dname||' '||v_dept.loc);
end loop;
close cur_1;
end; --有out
--输入一个员工编号,输出部门编号和工资
create or replace procedure pro_8(v_empno number,v_deptno out number,v_sal out number)
is
begin
select deptno,sal into v_deptno,v_sal
from emp
where empno=v_empno;
dbms_output.put_line(v_deptno||' '||v_sal);
end; --用plsql调用
declare
v1 number;
v2 number;
begin
pro_8(7788,v1,v2);
dbms_output.put_line(v1||' '||v2);
end; --输入一个员工姓名,输出他的员工编号,工作和部门名称
create or replace procedure pro_9(v_ename varchar2,v_empno out number,v_job out varchar2,v_dname out varchar2)
is
begin
select empno,job,dname into v_empno,v_job,v_dname
from emp e
join dept d
on e.deptno=d.deptno
where e.ename=v_ename;
end; --调用
declare
v1 number;
v2 varchar2(20);
v3 varchar2(20);
begin
pro_9('SCOTT',v1,v2,v3);
dbms_output.put_line(v1||' '||v2||' '||v3);
end; --创建emp_tt数据同emp
create table emp_tt as select * from emp;--编写一个存过 输入两个名字 更改他们两人的名字为两人的首字母中间加上+
--输出两个人更新后的名字
--比如 SMITH KING
-- S+K K+S
create or replace procedure pro_10(v1 varchar2,v2 varchar2,v3 out varchar2,v4 out varchar2)
is
begin
update emp_tt set ename=substr(v1,1,1)||'+'||substr(v2,1,1) where ename=v1
returning ename into v3;
update emp_tt set ename=substr(v2,1,1)||'+'||substr(v1,1,1) where ename=v2
returning ename into v4;
end;--调用
declare
v1 varchar2(20);
v2 varchar2(20);
begin
pro_10('KING','SMITH',v1,v2);
dbms_output.put_line(v1||' '||v2);
end; 存过和函数的区别
1.函数有return,存过没有
2.存过可以调函数,函数不能调存过
3.存过是用来实现某些操作或业务,函数用来实现某种功能
4.DML一般用存过
5.有out一般用存过
6.调用方式不同
┌──────────┬───────────────────────────────┬──────────────────────┐
│ │存储过程 │自定义函数 │
├──────────┼───────────────────────────────┼──────────────────────┤
│无参数 │sql窗口 CALL pro_name(); │sql调 │
│ │命令窗口EXEC pro_name; │ │
├──────────┼───────────────────────────────┼──────────────────────┤
│有in无out │sql窗口 CALL pro_name(参); │sql调 │
│ │命令窗口EXEC pro_name(参); │ │
├──────────┼───────────────────────────────┼──────────────────────┤
│有out │plsql调 │plsql调 │
│ │pro_name(参) │返回值:=fun_name(参..)│
└──────────┴───────────────────────────────┴──────────────────────┘本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
