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

最近在做一个ORACLE存储过程的需求，之前完全没有做过，以下是最近整理的学习笔记。供参考。还有肯定还有其他操作但是没有涉及，也可以一起讨论。

ORACLE存储过程

PLSQL编程

• PLSQL（Procedure LanguageSQL）
• 是Oracle对sql语言的过程扩展化（类似于basic）
• 在SQL命令语言中，增加了过程处理语句（如分支，循环等），使SQL语言具有处理过程。

HelloWorld

-- Created on 2021/5/8 by ZJP 
declare
  --类似于java中的声明
  -- Local variables here
  i integer;
begin
  -- Test statements here
  -- 执行语句，异常处理。
  DBMS_output.put_line('Hello World');
end;

• 在Sqlplus中，存储过程后添加/表示执行该存储过程。
• set serveroutput on 开启输出窗口，才能输出语句。

变量

• 普通数据类型（char，varchar2，date，number，boolean，long）
• 特殊变量类型

引用型变量:取决于表字段类型
记录型变量:接收的不是一个字段的值而是一整条记录的值
声明变量方式

-- 变量名 变量类型（变量长度）
v_name varchar(20);

• 变量赋值

1. 直接赋值语句:= 比如 ,v_name:=‘张三’
2. 语句赋值，使用select ... into ...赋值，比如(语法，select 值 into 变量)
   示例打印人员信息

-- 打印人员信息
declare
  -- Local variables here
  v_name VARCHAR2(20) :='张三';
  v_sal NUMBER;
  v_addr VARCHAR(200);
begin

    -- 直接赋值
    v_sal :=15800;
    
    --语句赋值
    SELECT '山西省太原市' INTO v_addr FROM dual;
    
    --打印输出
    
    DBSM_OUTPUT.PUT_LINE('姓名：'||v_name||',薪水：'||v_sal||'，地址：'||v_addr);

end;

引用型变量

• 变量取决于表中字段类型和长度

-- 通过员工号，打印员工信息
DECLARE
  -- 姓名
  V_NAME emp.ename%TYPE;
  -- 薪水
  V_SAL NUMBER;

BEGIN

    SELECT ENAME,SAL INTO V_NAME,V_SAL FROM EMP WHERE EMPNO ='7839';
    
    --打印输出
    
    DBSM_OUTPUT.PUT_LINE('姓名：'||V_NAME||',薪水：'||V_SAL);

END;

-- EMP 表名
-- ENAME，SAL 列名

推荐使用引用型定义变量。更加能够适应对数据库结构的更新。

记录型变量

• 接收表中的一整行记录，相当于java的一个对象
• 语法 ：v_emp emp%rowtype;

-- 通过7839员工号，打印员工信息
DECLARE
  -- 记录型变量
  v_emp emp%ROWTYPE;

BEGIN

    SELECT * INTO V_EMP FROM EMP WHERE EMPNO ='7839';
    
    --打印输出
    
    DBSM_OUTPUT.PUT_LINE('姓名：'||v_emp.ename||',薪水：'||v_emp.sql);

END;

-- EMP 表名
-- ENAME，SAL 列名

慎重使用，浪费效率。

流程控制

• 条件分支
  语法：

BEGIN

    IF 条件1 THEN 执行1
        ELSIF 条件2 THEN 执行2
        ELSE 执行3
    END IF;

END;

注意关键字ELSIF

-- 判断EMP表中，记录是否超过20条，10-20之间，或者10条以下
DECLARE
  -- 声明变量接收emp中的数量
  v_count NUMBER;

BEGIN

    SELECT count(1) INTO V_COUNT FROM EMP ;
    
    IF v_count>20 THEN 
        dbms_output.put_line('emp表中的记录数超过了20条为：'||v_count);
    ELSIF v_count>=10 THEN
        dbms_output.put_line('emp表中的记录数在10~20条为：'||v_count);
    ELSE
        dbms_output.put_line('emp表中的记录数在10条以下：'||v_count);
    END IF;
    
END;

• 循环

语法：

BEGIN
    LOOP
        EXIT WHEN 退出循环条件
    END LOOP;
END;

示例

  -- 声明循环变量 
  V_NUM NUMBER :=1;

BEGIN
    LOOP
    EXIT WHEN V_NUM>10;
    DBMS_OUTPUT.OUT_LINE(V_NAME);
    -- 循环变量的自增
    V_NUM :=V_NUM+1;
    END LOOP;
    
END;

游标【集合类型的变量】

• 游标是什么？

用于临时存储一个查询返回的多行数据（结果集）
使用方式：声明–》打开–》读取–》关闭

• 语法

游标声明：CURSOR 游标名（参数列表） IS 查询语句;
打开游标： OPEN 游标名;
游标的取值：FETCH 游标名INTO 变量列表;
关闭游标：CLOSE 游标名;

游标的属性

• 其中，%NOTFOUND是在游标中找不到元素的时候返回TRUE，通常用来判断退出循环

创建和使用

-- 使用游标查询emp表中，所有员工的姓名和工资，并将其一次打印出来。
DECLARE
  --声明游标，现在游标中放的是emp表中的所有ename和sal
  CURSOR c_emp IS select ename,sal FROM emp;
  
  --声明变量接收游标中的数据
  v_ename emp.ename%TYPE;
  v_sal emp.sal%TYPE;

BEGIN
    -- 打开游标
    OPEN c_emp;
    --遍历游标
    LOOP
    --获取游标中的数据
    FETCH c_emp INTO v_ename,v_sal;
    --退出循环条件
    EXIT WHEN c_emp%NOTFOUND;
    --关闭游标
    
    DBMS_OUTPUT.PUT_line(v_ename||'  -  '||v_sal);
    
    CLOSE c_emp;
END;

• 注意游标属性的顺序

带参的游标

使用游标查询并打印某个部门的员工姓名和薪水

-- 使用游标查询emp表中，所有员工的姓名和工资，并将其一次打印出来。
DECLARE
  --声明游标，现在游标中放的是emp表中的所有ename和sal
  CURSOR c_emp(v_deptno emp.deptno%TYPE) IS select ename,sal FROM emp WHERE DEPTNO=v_deptno;
  
  --声明变量接收游标中的数据
  v_ename emp.ename%TYPE;
  v_sal emp.sal%TYPE;

BEGIN
    -- 打开游标,传参
    OPEN c_emp(10);
    --遍历游标
    LOOP
    --获取游标中的数据
    FETCH c_emp INTO v_ename,v_sal;
    --退出循环条件
    EXIT WHEN c_emp%NOTFOUND;
    --关闭游标
    
    DBMS_OUTPUT.PUT_line(v_ename||'  -  '||v_sal);
    
    CLOSE c_emp;
END;

• 注意
• FETCH语句放在判断条件上面，要考虑FETCH的位置。

存储过程

概念作用

之前编写的PLSQL程序可以进行表的操作，判断，循环处理逻辑，但无法重复调用。
PLSQL将PLSQL的业务处理过程存储起来进行复用，被存储起来的PLSQL程序称之为存储过程。

• 作用

1. 把业务放到PLSQL，应用只需要调用PLSQL连接关闭一次，大大提高效率。
2. 能够让数据库做的就不放在程序中

• 语法

CREATE OR REPLACE PROCEDURE 过程名称[(参数列表)] IS
BEGIN

END[过程名称];

• 不带参数的
• 带输入参数的
• 带输入输出参数（返回值）的。

无参存储过程。

create or replace procedure P_hello is
-- is和as是互用的
--直接声明变量
begin
  dbms_output.put_line('hello world');
end P_hello;

plsql调用存储过程

--plsql调用存储过程
 p_hello;

sqlplus 调用
exec p_hello;

• p_hello是一个存储过程。

###输入参数的存储过程
示例

create or replace procedure p_querynameandsal(i_empno IN  emp.empno%TYPE) as
--直接声明变量
v_name emp.ename%TYPE;
v_sal emp.sal%TYPE;
begin
  --查询emp表中某个员工的姓名和薪水并赋值给变量
  SELECT ename,sal INTO v_name,v_sal FROM emp WHERE empno=i_empno;
  dbms_output.put_line(v_name||'_'||v_sal);
end p_querynameandsal;

带输出参数的存储过程

示例：。

--通过员工号查询某个员工信息，要求将薪水作为返回值输出，给调用的程序使用
create or replace procedure p_querysal_out(i_empno IN  emp.empno%TYPE,o_sal OUT emp.sal%TYPE) as

begin

  SELECT sal INTO o_sal FROM emp WHERE empno=i_empno;

end ;

接收存储过程的输出参数

declar 
    --声明变量接收存储过程中的输出参数
    v_sal emp.sal%TYPE;
BEGIN
--plsql调用存储过程
 p_querysal_out(7839,v_sal);
 
 dbsm_output.put_line(vsal);
END;

JAVA程序调用存储过程

• Connection CallableStatement

调用存储过程

常用命令

• 插入数据

insert into table(param1,param2) values(‘param1’,‘param2’);

语法格式：

• 创建存储过程

CREATE [OR REPLACE] PROCEDURE 过程名[(参数1 [IN|OUT|IN OUT] 数据类型,
									 参数2 [IN|OUT|IN OUT] 数据类型……)]
IS|AS
PL/SQL过程体;

• 调用存储过程,执行过程中的命令

BEGIN
  过程名[(参数)];
END;

设置存储过程定时作业（DBMS_JOB）

该过程用于建立一个新的作业,当建立作业的时候,需要通过设置相应的参数来告诉Oracle要执行的内容,
要执行的时间,要执行任务的间隔。如下格式：
– 包中的存储过程DBMS_JOB.SUBMIT,参数如下

DBMS_JOB.SUBMIT(
   JOB OUT BINARY_INTERGER,                          -- 用于指定作业编号
   WHAT IN VARCHAR2,                                 -- 用于指定作业要执行的操作
   NEXT_DATE IN DATE DEFAULT SYSDATE,                -- 用于指定该操作的下一次运行的日期
   INTERVAL IN VARCHAR2 DEFAULT 'NULL',              -- 用于指定该操作的时间间隔
   NO_PARSE IN BOOLEAN DEFAULT FALSE,                -- 用于指定是否需要解析与作业相关的过程
   INSTANCE IN BINARY_INTEGER DEFAULT ANY_INSTANCE,  -- 用于指定哪个例程可以运行作业？
   FORCE IN BOOLEAN DEFAULT FALSE                    -- 用于指定是否强制运行与作业相关的例程
   )

执行和停止定时任务的DEMO

DECLARE
  V_JOBNO NUMBER;
BEGIN
--提交,操作的时间间隔设置为5秒（default参数可以不用传入）
  DBMS_JOB.SUBMIT(JOB=>V_JOBNO,    --序列号
				  WHAT=>      ,    --调用存储过程(分号不能少"xxxxxxx;")
				  NEXT_DATE=>SYSDATE, 
				  INTERVAL=>'SYSDATE+1/(24*60*12)');  
--打印序列号
  DBMS_OUTPUT.PUT_LINE('JOBNO='||V_JOBNO);
--运行
  DBMS_JOB.RUN(V_JOBNO);
END;
  
-- 删除
BEGIN
DBMS_JOB.REMOVE(JOBNO);  -- JOBNO为具体数字
END;