存储过程常见语法

存储过程常见语法存储过程常见语法一、存储过程的概念:1、存储过程Procedure是一组为了完成特定功能的SQL语句集合,经编译后存储在数据库中,用户通过指定存储过程的名称并给出参数来执行2、存储过程中可以包含逻辑控制语句和数据操纵语句,它可以接受参数、输出参数、返回单个或多个结果集以及返回值。3、由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中,所以存储过程运行要比单个的SQL语…

大家好,又见面了,我是你们的朋友全栈君。

存储过程常见语法

一、存储过程的概念:

1、存储过程Procedure是一组为了完成特定功能的SQL语句集合,经编译后存储在数据库中,用户通过指定存储过程的名称并给出参数来执行

2、存储过程中可以包含逻辑控制语句和数据操纵语句,它可以接受参数、输出参数、返回单个或多个结果集以及返回值

3、由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中,所以存储过程运行要比单个的SQL语句块要快。同时由于在调用时只需用提供存储过程名和必要的参数信息,所以在一定程度上也可以减少网络流量、简单网络负担。


存储过程基本知识:

一、oracle存储过程结构:

CREATE OR REPLACE PROCEDURE 存储过程名
(
输入输出参数
)
IS
变量定义位置
BEGIN
代码;

END 存储过程名;

二、基本变量类型:

1、CHAR类型: ‘定长字符串'(会用空格填充来达到其最大长度), 若不指定CHAR的长度,默认为1,最大2000字节

2、NCHAR类型: 包含UNICODE格式数据的’定长字符串’,若定义为NCHAR类型,模糊查询时如下书写:

        select * from INSERTTEST t where t.qq like ‘%daa21%’  查询的值必须是’%内容%’

       NICODE格式数据:统一码、万国码、单一码)是计算机科学领域里的一项业界标准,

       包括字符集、编码方案等(统一并且唯一的二进制编码)

3、VARCHAR类型:   —>  最好不使用

4、VARCHAR2类型: ‘变长字符串’ 最大4000字节

5、NVARCHAR2类型:同Nchar类似,包含UNICODE格式数据的’变长字符串’

6、NUMBER类型: NUMBER(P,S)是最常见的数字类型

存储过程常见语法

7、INTEGER类型:NUMBER的子类型,它等同于NUMBER(38,0),用来存储整数,

    若插入、更新的数值有小数,则会被四舍五入

8、浮点数

  (1)BINARY_FLOAT :32 位单精度浮点数字数据类型

  (2)BINARY_DOUBLE :64 位双精度浮点数字数据类型

9、FLOAT类型 :也是NUMBER的子类型,

     Float(n),数 n 指示位的精度,可以存储的值的数目。N 值的范围可以从 1 到 126

10、DATE类型  :一般占用7个字节的存储空间

11、TIMESTAMP类型  :这是一个7字节或12字节的定宽日期/时间数据类型。它与DATE数据类型不同,

      因为TIMESTAMP可以包含小数秒,带小数秒的TIMESTAMP在小数点右边最多可以保留9位   

12、LONG类型 :存储变长字符串,最多达2G的字符数据

三、存储过程 if语句:

if 逻辑表达式 then
      内容
Elsif  逻辑表达式 then
      内容
Else
      内容
End if;

四、oracle 存储过程中的 := 和=有什么区别

:= 是赋值符号,例如: a := 2, 那么变量a的值,就是2了

= 是比较符号, 例如: … WHERE 字段名 = 2,和 > < 是一样的性质

五、游标与循环

CREATE OR REPLACE PROCEDURE yzy_test()

   is
   type myCur is ref cursor;
   cur myCur;
   returnValue  VARCHAR2(3000);
   SelectSQL  VARCHAR2(3000);
begin

   SelectSQL:= 'select test from yzy_test';
   open cur for SelectSQL;
   loop
   exit when cur%notfound ; --当游标属于notfound,直接弹出
   fetch cur into returnValue;
   end loop;
   close cur;
     EXCEPTION WHEN OTHERS THEN
     --存储过程出错走这里
end yzy_test;

这里注意的是exit when cur%notfound 这句话,有的时候游标的notfound 值有可能是大写也有可能是小写,这个地方是区分大小写的,如果不加这句话,造成的后果就是一直循环,不会弹出。

六、使用临时表返回数据 SYS_REFCURSOR 作为临时表

CREATE OR REPLACE PROCEDURE SP_TEST(C_RES OUT  SYS_REFCURSOR) AS
V_SQL VARCHAR2(1000);
BEGIN

  V_SQL:='BEGIN OPEN :C_RES FOR SELECT * FROM DUAL; END;';

  EXECUTE IMMEDIATE V_SQL  USING C_RES;

END SP_TEST;  

说明: EXECUTE IMMEDIATE执行的是SQL, 

        或者PL/SQL块,所以加上BEGIN … END,

         还要把C_RES当作绑定变量传递。

七、打印执行sql

create or replace procedure test is
v_sql varchar2(2000);--要定义一个存放sql语句的变量
begin
  v_sql:='insert into test1 values (sysdate)';--给sql赋值
  dbms_output.put_line(v_sql);--打印
  execute immediate v_sql;--执行sql
  commit;
end test;

sql查询: select * from test1;

结果:

存储过程常见语法

八、自治事务–自定义事务(独立)

在存储过程begin上方添加PRAGMA AUTONOMOUS_TRANSACTION;就成为自治事务

自治事务从当前事务开始,在其自身的语境中执行。它们能独立地被提交或重新运行,而不影响正在运行的事务。正因为这样,它们成了编写错误日志表格的理想形式。在事务中检测到错误时,您可以在错误日志表格中插入一行并提交它,然后在不丢失这次插入的情况下回滚主事务。(rollback;–回滚)

create or replace procedure insertLog(LogValue in varchar2)is
v_sql varchar2(2000);--要定义一个存放sql语句的变量
PRAGMA AUTONOMOUS_TRANSACTION;
begin
  v_sql:='insert into test1(id,date1,logvalue) values (sys_guid(),sysdate,'''||LogValue||''')';
  --给sql赋值
  dbms_output.put_line(v_sql);--打印
  execute immediate v_sql;--执行sql
  commit;
 
end insertLog;

九、如何检测存储过程中的错误

begin
 EXCEPTION    WHEN OTHERS THEN
   存储过程出差后走这里
 rollback;


end 存储过程名;

十、面对大量数据进行联合查询并修改情景时使用  merge into …using() on…

create or replace procedure merge_test is
begin
  MERGE INTO user_test a USING ( select id from order_test) b ON (a.id=b.id)
   WHEN MATCHED THEN
        UPDATE SET a.sex =3
    WHEN NOT MATCHED THEN  
      insert (id,USERNUME,sex) values(sys_guid(),'匹配不上',5);
   commit;
end merge_test;

————————-

含义:匹配 user_test a,用( select id from order_test) b这个查询结果,用on建立联系,当匹配上用update,

                                       匹配不上用insert。

注意:ON里面的条件,不能作为 update里 set 的条件

结果:

存储过程常见语法

十一、exit与rollback

exit     –结束 可以使用场景:跳出循环

rollback  –回滚 出差的时候进行回滚,保证运行事务后数据不缺失

十二、查询数据赋值给某个变量

 select to_date(vgfrq1,’yyyy/mm/dd’) into vgfrq from dual;

十三、INSERT ALL 多表插入数据(带条件和游标循环)

准备:

select * from user_test;

存储过程常见语法

使用:

create or replace procedure SP_more_insert is
 S_id varchar(500);
 type myCur is ref cursor;
 cur myCur;
begin
   open cur for  SELECT a.id from user_test a  where a.address = 'YZY';
   LOOP
   FETCH cur   INTO S_id;
   EXIT WHEN cur%NOTFOUND;
   --带条件多表插入
  insert ALL 
       WHEN S_id='0' THEN
         INTO one_test(id,va,ass) 
       WHEN S_id='1' THEN
         INTO  two_test(id,va,ass)
       WHEN S_id='10' THEN
         INTO  three_test(id,va,ass)
  SELECT sys_guid(),a.usernume,a.address from user_test a  where a.address = 'YZY' and  a.id = S_id; 
   commit;
  END LOOP;
  close cur;
 EXCEPTION    WHEN OTHERS THEN
   insertLog('SP_more_insert出差!!!');
 rollback;
end SP_more_insert;

效果: 

存储过程常见语法

存储过程常见语法

存储过程常见语法

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/160794.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • object中提供的clone默认为浅克隆_接口的定义形式

    object中提供的clone默认为浅克隆_接口的定义形式cloneable接口的作用cloneable其实就是一个标记接口,只有实现这个接口后,然后在类中重写Object中的clone方法,然后通过类调用clone方法才能克隆成功,如果不实现这个接口,则会抛出CloneNotSupportedException(克隆不被支持)异常。Object中clone方法:这里有一个疑问,Object中的clone方法是一个空的方法,那么他是如何判断类…

    2022年10月15日
  • python2装饰器_python函数装饰器

    python2装饰器_python函数装饰器前言我们都知道装饰器的作用是在不改变原有的代码基础上,添加新的功能,但是这样会有一个弊端,被装饰的函数某些属性会变改变,接下来我们来看下案例importtimedefrun_time(fu

  • kubernetes使用secrets保存敏感信息

    kubernetes使用secrets保存敏感信息

  • 电容与部分电容_接地电容不能太大

    电容与部分电容_接地电容不能太大转载自:http://emakerzone.com/article/229关键字:薄膜电容,电解电容,陶瓷电容,铝电解电容,钽电容,安规电容之前的文章中,介绍了电感的一些知识。本文将谈谈电容,介绍电容的知识和如何选型。一、电容的基本原理电容,和电感、电阻一起,是电子学三大基本无源器件;电容的功能就是以电场能的形式储存电能量。以平行板电容器为例,简单介绍下电容的基本原理如…

  • LoadRunner 11 安装及激活成功教程

    LoadRunner 11 安装及激活成功教程注意事项:  安装前,把所有的杀毒软件和防火墙关闭。  若以前安装过LoadRunner,则将其卸载。  安装路径不要带中文字符。  如果系统为WIN7,旗舰版才能安装。  安装完毕,需激活成功教程

  • vue 如何关闭 eslint 检查

    vue 如何关闭 eslint 检查在实际开发过程中,eslint的作用不可估量,诸如:1.审查代码是否符合编码规范和统一的代码风格;2.审查代码是否存在语法错误;But,对于初学者来说,这个功能极其不友好,各种问题层出不穷,让很多初学者头疼不已,我们有没有办法关掉它,等适当时机在启用它呢,答案是肯定的。不同vuecli版本创建工程的时候,稍微有些差别,要仔细甄别,我的vuecliv4.5.9方案一:vue脚手架创建工程的时候,不要选择Linter/Formatter选项,(那如何选择启用,请参照方案二)

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号