存储过程常见语法

存储过程常见语法

一、存储过程的概念:

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;

效果: