mysql存储过程insert 和 select into一起写_Oracle存储过程入门（一）

概念

将不同的业务处理过程存储起来进行复用，这些被存起来重复利用的sql程序就是存储过程；类似于编程语言中的封装之后再调用的逻辑。

作用

oracle官方认为，在数据库能够完成的操作全部留在数据库中，因为在外部程序中运行这些操作容易产生错误；开发程序时，有些业务逻辑的开发，会向数据库进行多次连接，这样连接和关闭很耗费资源，性能比较低；如果将常用的业务逻辑放到数据库中，在应用程序的时候仅仅调用其就可以做到连接一次数据库，直接实现业务逻辑，可以大大提高效率；

语法

基本结构

CREATE OR REPLACE PROCEDURE 存储过程名字

 ( 

    参数1 IN NUMBER,     

    参数2 IN OUT NUMBER

) AS 

变量1 INTEGER :=0; 

变量2 DATE; 

BEGIN 

NULL;

END 存储过程名字;

IN, OUT, IN OUT用来修饰参数；

IN 变量必须传入到PROCEDURE进行处理；

 OUT 表示PRCEDURE 通过这个变量将值传出；

 IN OUT 是这两种的组合。

begin与end之间为程序体；BEGIN关键词表明PL/SQL体的开始，END关键词表明PL/SQL体的结束。

null不能删去，pl/sql体中至少要有一句；

根据参数的类型，可以分为无参数、有输入参数in、有输入输出(返回值)in out的；

PLSQL中通过可视化界面创建存储过程的选项如下所示：

语句创建： 

create or replace procedure 存储过程名(param1 in type，param2 out type)

as 

变量1 类型(值范围); --vs_msg   VARCHAR2(4000);

变量2 类型(值范围);

Begin

Select count(*) into 变量1 from 表A where列名=param1;

    If (判断条件) then

       Select 列名 into 变量2 from 表A where列名=param1;

       Dbms_output.Put_line(‘打印信息’);

    Elsif (判断条件) then

       Dbms_output.Put_line(‘打印信息’);

    Else

       Raise 异常名(NO_DATA_FOUND);

    End if;

Exception

    When others then

       Rollback;

End;

(param1 in type，param2 out type) 为参数，in/out分别为输入和输出参数；

普通常量的赋值：

V_TEST := "常量";

通过SELECT INTO STATEMENT对变量进行赋值：  将select查询的结果存入到变量中，存入一个变量：

Select count(*) into 变量1 from 表A where 列名=param1;

同时将多个列存储到多个变量中，查询结果必须是一条记录(多于一条无法写入)，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)  BEGIN  SELECT col1,col2 into 变量1,变量2 FROM 表A where 条件;  EXCEPTION  WHEN NO_DATA_FOUND THEN      xxxx;  END;

变量

基础的标量类型 (有%TYPE)

    标量类型，常用的有NUMBER、CHAR 、VARCHAR2 、VARCHAR、NCHAR 、NVARCHAR2 、LONG 、DATE 、TIMESTAMP；

(1)引用性变量

    另外还有一个最常用的是 %TYPE ，它利用已经存在的数据或者变量的数据类型来定义新数据的数据类型。例如，当定义多个变量或常量时，只要前面使用过的数据类型，后边的变量就可以利用%TYPE 引用，最常见的就是把表中的字段类型作为变量或常量的数据类型。

v_sal emptab.salary%TYPE表示:一个变量v_nb，类型是与表emptab中的字段salary的类型一致。

比如：

create or replace procedure 存储过程名(v_sal emptab.salary%TYPE，param2 out type)

as 

 --vs_msg   VARCHAR2(4000);

param2 类型(值范围);

Begin

Select count(*) into param2 from 表A where列名=param1;

dbms_output.put_line(param2);

End;

 (2)记录类型 (有%ROWTYPE) 

如果需要选取的字段过多，比如该表十五个字段需要全部取用，用引用名称.成员名称和into 的赋值方式定义变量会过于麻烦，所以有记录类型的变量；

通过%ROWTYPE的方式获取变量，是提取行记录时常用的存储数据的方式。

可以直接引用表中的某行作为变量类型；

DECLARE

v_obj ly_ds%rowtype; --ROWTYPE不区分大小写

BEGIN

 --into赋值给v_obj，会按照定义的type顺序赋值

select * into v_obj from emp where id='2'; 

dbms_output.put_line('第一个变量：'||v_obj.ly_nl);

END;

select * into ，只能为 * ，如果要写为具体的列，那就要写全，因为%rowtype是一条记录的变量类型；

流程控制

1.条件语句 

--只有一个if

if ....then

end if;

只有两种 

--两种选择

if .....then

else

...

end if;

注意的一点就是elsif的写法！！ 

--多种选择

if .... then

elsif....then

else....

end if;

也可以在判断之后进行某些操作

 IF V_TEST=1 THEN

    BEGIN

       do something

    END;

  END IF;

2.循环

• 简单循环或者无限循环 

LOOP关键字开始，

    END LOOP语句结束

     循环体内的EXIT、EXIT WHEN 或者 RETURN退出循环(或者异常抛出)

  【EXIT WHEN后面是判断退出条件的布尔表达式，没有退出条件会变成死循环】

begin

  --打开游标变量c_emps

  open c_emps;

       --循环开始

       loop

         --需要显式声明游标，显式打开、关闭游标

         fetch c_emps into v_emp;

         --退出循环的条件(%notfound是游标的sql自带属性)

         exit when c_emps%notfound;

              if v_emp.ENAME = '张三' then

                 dbms_output.put_line('张三' || ' - ' || v_emp.JOB);

              elsif v_emp.ENAME = '李四' then

                 dbms_output.put_line('李四' || ' - ' || v_emp.JOB);

              else

                 dbms_output.put_line(v_emp.ENAME || ' - ' || v_emp.JOB);

              end if;

       --循环结束

       end loop;

  --关闭游标变量c_emps

  close c_emps;

end PRO_LOOP_EMP;

• WHILE循环：

  WHILE循环和LOOP循环非常相似，

  关键的区别在于WHILE循环会在每次循环之前检查是否满足终止条件；

  【事先无法确定会循环多少次；明确终止循环的条件】

  --循环输出1-10的数字

create or replace procedure while_test as

  n_count number := 0;

begin

--循环条件是改变量<10时执行循环体

  while n_count < 10 loop

    dbms_output.put_line(n_count);

    n_count := n_count + 1;

  end loop;

end;--end后面不加过程名，则默认该过程的结束；

begin

  while_test();

end;

• FOR循环：

使用数值型FOR循环，需要指定循环开始的整数值和结束的整数值，程序会迭代每一个中间取值，最后结束循环；

create or replace procedure FOR_TEST is

       --让游标变量c_emps指向一个动态select查询的结果集

       cursor c_emps is select * from emp where rownum <= 10;

begin

       --循环开始

       for i in c_emps loop

           if i.ENAME = '张三' then

                dbms_output.put_line('张三' || ' - ' || e.JOB);

           elsif i.ENAME = '李四' then

                dbms_output.put_line('李四' || ' - ' || e.JOB);

           else

                dbms_output.put_line(e.ENAME || ' - ' || e.JOB);

           end if;

      --循环结束

      end loop;

end FOR_TEST;

注：如果需要取出游标的每条记录并进行依次处理，可以使用游标的FOR循环；

游标型的FOR循环使用相同的基本结构，但需要提供一个明确的游标或者SELECT语句；

游标

概念

游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。

游标的简单理解：

用于临时存储一个查询结果返回的多行数据(多条记录)；

通过遍历游标(该查询结果)，可以逐行的处理该结果数据；

游标有两种，显示游标和隐式游标：

对于SELECT…INTO的查询和DML操作，系统都会使用一个隐式游标(隐式游标默认打开游标)。
DML操作和单行SELECT语句会使用隐式游标：

• 插入操作：INSERT。

• 更新操作：UPDATE。

• 删除操作：DELETE。

• 单行查询操作：SELECT … INTO …。

显式游标

游标的使用，必须严格的遵循：声明游标declare---->打开游标open---->读取游标fetch---->关闭游标close

DECLARE

         v_ename VARCHAR2(10); 、

         --声明游标

         CURSOR emp_cursor IS    

         SELECT ename,job FROM emp WHERE empno=1;   

BEGIN  

    --打开游标

     OPEN emp_cursor;   

     --获取游标

    FETCH emp_cursor INTO v_ename,v_job;   

        DBMS_OUTPUT.PUT_LINE(v_ename||','||v_job);   

    --关闭游标

    CLOSE emp_cursor;   

END;  

显式游标的属性如下所示。

游标的属性   返回值类型   意    义   

%ROWCOUNT   整型  获得FETCH语句返回的数据行数   

%FOUND  布尔型 最近的FETCH语句返回一行数据则为真，否则为假

%NOTFOUND   布尔型 与%FOUND属性返回值相反   

%ISOPEN 布尔型 游标已经打开时值为真，否则为假  

通过游标名%属性的方式判断游标的状态：

比如，通过游标名%NOTFOUND判断游标获取的查询结果中是否还有数据，可以作为循环的终止条件；

游标也可以带参数：

DECLARE

         v_ename VARCHAR2(10); 、

         --声明游标,该游标的参数不是变量而是常量则在打开游标的时候需要传入参数值

         CURSOR emp_cursor(p_deptno NUMBER,p_job VARCHAR2) IS    

         SELECT ename,job FROM emp WHERE empno=1;   

BEGIN  

    --打开游标

     OPEN emp_cursor(10, 'job_type');   

     --获取游标

    FETCH emp_cursor INTO v_ename,v_job;   

        DBMS_OUTPUT.PUT_LINE(v_ename);   

    --关闭游标

    CLOSE emp_cursor;   

END;  

一个基于业务需求的可以执行的存储过程实例

-- Created on 2020/12/16 by XIAOB

declare

  -- Local variables here

  local_cf_num  NUMBER; --本地实际上报惩罚数目

  local_xk_num  NUMBER; --本地实际上报许可数目

  upload_cf_num NUMBER; --实际上报省平台惩罚数目

  upload_xk_num NUMBER; --实际上报省平台许可数目

  --定义游标

  CURSOR c_cfid IS

    select b.id

      from (select id

              from t_sgssb_punish_new_mid t

             where trunc(t.sbsj) >= trunc(sysdate) - 2) a

      full join (select id

                   from t_sgssb_id_cf t

                  where trunc(to_date(t.sbsj, 'yyyy-mm-dd hh24:mi:ss')) >=

                        trunc(sysdate) - 2) b

        on a.id = b.id

     where b.id is null

    union

    select b.id

      from (select id

              from t_sgssb_punish_new_mid t

             where trunc(t.sbsj) >= trunc(sysdate) - 2) a

      full join (select id

                   from t_sgssb_id_cf t

                  where trunc(to_date(t.sbsj, 'yyyy-mm-dd hh24:mi:ss')) >=

                        trunc(sysdate) - 2) b

        on a.id = b.id

     where a.id is null;

  CURSOR c_xkid IS

    select b.id

      from (select id

              from t_sgssb_licening_new_mid t

             where trunc(t.sbsj) >= trunc(sysdate) - 2) a

      full join (select id

                   from t_sgssb_id_xk t

                  where trunc(to_date(t.sbsj, 'yyyy-mm-dd hh24:mi:ss')) >=

                        trunc(sysdate) - 2) b

        on a.id = b.id

     where b.id is null

    union

    select b.id

      from (select id

              from t_sgssb_licening_new_mid t

             where trunc(t.sbsj) >= trunc(sysdate) - 2) a

      full join (select id

                   from t_sgssb_id_xk t

                  where trunc(to_date(t.sbsj, 'yyyy-mm-dd hh24:mi:ss')) >=

                        trunc(sysdate) - 2) b

        on a.id = b.id

     where a.id is null;

  --声明变量记录游标的值；

  cf_id_list t_sgssb_punish_new_mid.id%type; --惩罚id

  xk_id_list t_sgssb_licening_new_mid.id%type; --许可id

BEGIN

begin

  -- Test statements here

  --给单个变量赋值

  SELECT count(t.id)

    INTO local_cf_num

    from t_sgssb_punish_new_mid t

   where trunc(t.sbsj) >= trunc(sysdate) - 2

  --and IS_UPLOAD = '0'

  ;

  SELECT count(t.id)

    INTO upload_cf_num

    from t_sgssb_id_cf t

   where trunc(to_date(t.sbsj, 'yyyy-mm-dd hh24:mi:ss')) >=

         trunc(sysdate) - 2

  --and IS_UPLOAD = '0'

  ;

  SELECT count(t.id)

    INTO local_xk_num

    from t_sgssb_licening_new_mid t

   where trunc(t.sbsj) >= trunc(sysdate) - 2

  -- and IS_UPLOAD = '0'

  ;

  SELECT count(t.id)

    INTO upload_xk_num

    from t_sgssb_id_xk t

   where trunc(to_date(t.sbsj, 'yyyy-mm-dd hh24:mi:ss')) >=

         trunc(sysdate) - 2

  --and IS_UPLOAD = '0'

  ;

  DBMS_OUTPUT.PUT_LINE('实际上报惩罚条数：' || local_cf_num || chr(10)||';省平台惩罚条数：' ||

                       upload_cf_num || chr(10)||';实际上报许可条数：' || local_xk_num ||chr(10)||

                       ';省平台许可条数：' || upload_xk_num);

end;

if local_cf_num = upload_cf_num and local_xk_num = upload_xk_num then

    DBMS_OUTPUT.PUT_LINE('省平台上报和上报数据一致'||chr(10)); 

  elsif local_cf_num <> upload_cf_num or local_xk_num <> upload_xk_num then

    DBMS_OUTPUT.PUT_LINE('双公示上报不一致'||chr(10)); 

    begin

  --打开游标

  open c_cfid;

  loop

    --获取游标

    fetch c_cfid

      into cf_id_list;

    --循环结束条件

    exit when c_cfid%notfound;

    DBMS_OUTPUT.PUT_LINE('惩罚id：' || cf_id_list);

    --退出循环

  end loop;

  close c_cfid;

end;

begin

  --打开游标

  open c_xkid;

  loop

    --获取游标

    fetch c_xkid

      into xk_id_list;

    --循环结束条件

    exit when c_xkid%notfound;

    DBMS_OUTPUT.PUT_LINE('有问题的许可id：' || xk_id_list);

    --退出循环

  end loop;

  close c_xkid;

end;

    else

         DBMS_OUTPUT.PUT_LINE('程序错误');

      -- message:=('对不起'||nvl(objname,'输入姓名')||'不存在！');

  end if;  

end;

结果输出如下(,位置未作调整)：

NEXT TASK：存储过程

.✴END✴

Now is no time to think what you have. Think what you can do with what there is...

—— The Old Man and The Sea

     未完待续