PLSQL 存储过程

--存储过程           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(参..)│
└──────────┴───────────────────────────────┴──────────────────────┘