存储过程常见语法

存储过程常见语法存储过程常见语法一、存储过程的概念: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)
blank

相关推荐

  • servu搭建ftp服务器_简单ftp server怎么用

    servu搭建ftp服务器_简单ftp server怎么用使用Serv-U搭建FTP服务器  操作一:Serv-U的安装1、双击安装包,点击“下一步” 2、选择安装位置,点击“下一步” 3、进入安装过程,如下图 4、安装完成 操作二  Serv-U的配置每个Serv-U引擎都能用来运行多个虚拟的FTP服务器,而虚拟的FTP服务器就称之为“域”。对于每个Serv-U  FTP服务器来讲,应该至少创建一个域和一个用

  • FFM模型在点击率预估中的应用实践

    FFM模型在点击率预估中的应用实践这篇文章,将主要讲述FFM模型在CTR预估中的应用。

  • pytorch 学习 | 全局平均池化 global average pooling

    版权声明:本文为CSDN博主「qyhyzard」的原创文章,遵循CC4.0BY-SA版权协议,转载请附上原文出处链接及本声明。原文链接:https://blog.csdn.net/CVSvsvsvsvs/article/details/90495254利用现有的poolingAPI实现全局平均池化的效果。首先我们简单理解全局平均池化操作。如果有一批特征图,其尺寸为[B,C,H,W],我们经过全局平均池化之后,尺寸变为[B,C,1,1]。也就是说,全局平均池化…

  • 高德地图获取shp文件_手机高德地图坐标拾取

    高德地图获取shp文件_手机高德地图坐标拾取转载自:http://blog.csdn.net/yukimineryuu/article/details/50933582用Eclipse获取sha1值比较简单。最近自己换了androidstudio开发,申请key的时候,要两个版本的sha1值。一个是开发版(debug),一个是发布版(release)。debug版本的sha1比较好获取,网上有介绍,这里

  • Spring Boot 2 学习笔记(1 / 2)[通俗易懂]

    Spring Boot 2 学习笔记(1 / 2)[通俗易懂]01、基础入门-SpringBoot2课程介绍SpringBoot2核心技术SpringBoot2响应式编程学习要求-熟悉Spring基础-熟悉Maven使用环境要求Java8及以上Maven3.3及以上学习资料SpringBoot官网SpringBoot官方文档本课程文档地址视频地址1、视频地址2源码地址02、基础入门-Spring生态圈Spring官网Spring能做什么Spring的能力Spring的生态覆盖了:w

  • 常用的字符串截取方法

    常用的字符串截取方法1取字符串的前i个字符str=str.substring(0,i);str=str.remove(i,str.Length-i);2去掉字符串的前i个字符:str=str.remove(0,i);str=str.substring(i);3从右边开始取i个字符:str=str.substring(str.Length-i);str=str.remove(0,str.Lengt…

发表回复

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

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