Oracle的存储过程

【replace】【游标】【循环】【fetch】

在ORACLE 中， 若干个有联系的过程可以组合在一起构成程序包。

https://www.2cto.com/database/201610/559389.html

游标定义： cursor [游标名] is [sql语句]

在PL SQL里打开查询窗口，在解释器里输入过程名，按住ctrl点击过程名，就能调出源码。

***存储过程语句

CREATE [OR REPLACE] PROCEDURE 存储过程名[(参数[IN|OUT|IN OUT] 数据类型...)]

{AS|IS}

[说明部分]

BEGIN

可执行部分

[EXCEPTION

错误处理部分]

END [过程名];

***删除存储过程 语法：

DROP PROCEDURE 存储过程名;

 

---将一个字段中有多个数据，有逗号隔开的，分别输出。

SELECT regexp_substr('1101,Yokohama,Japan,1.5.105', '[^,]+', 1, LEVEL) AS output ,'内容' FROM dual 

CONNECT BY LEVEL <= length('1101,Yokohama,Japan,1.5.105') -length(REPLACE('1101,Yokohama,Japan,1.5.105', ',')) + 1;

结果：
     1101
     Yokohama
     Japan
     1.5.105

　　

在PL SQL中循环过程中可以写多个游标定义多个循环，学习for循环，类似于

 cursor [游标名] is [sql语句]

 for  [循环名称] IN [游标名] loop---遍历游标，在打印出来

 DBMS_OUTPUT.put_line( );
 END LOOP;

***存储过程中SQL语句执行会加 【execute immediate】：

简单来说 就是你一个存储过程当中 创建了一个表 table_a 然后要用insert into将其他的数据插入到这个table_a当中，但是因为你在创建过程的时候 table_a还不存在，过程就会显示有编译错误，因为table_a不存在必然导致过程无法执行，所以无法编译成功，而把insert into语句加如到 execute immediate之后 则oracle不会再去理会这个对象是否存在，因此可以成功编译和执行。

----存储过程中参数

 ---存储过程的参数
        ---IN  定义一个输入参数变量，用于传递参数给存储过程   
        --OUT 定义一个输出参数变量，用于从存储过程获取数据   
        ---IN OUT  定义一个输入、输出参数变量，兼有以上两者的功能  
        --这三种参数只能说明类型，不需要说明具体长度 比如 varchar2(12)，defaul 可以不写，但是作为一个程序员最好还是写上。
        例：---创建有参数的存储过程
     

 create or replace procedure test_param(p_id1 in VARCHAR2 default '0')
 as v_name varchar2(32);
 begin
 select t.name into v_name from TESTTABLE t where t.id1=p_id1;
 DBMS_OUTPUT.put_line('name：'||v_name);
 end;

　　

      ----执行存储过程
      在PL SQL中在左侧找到“procedure”,选中已经编译过的过程，鼠标右击，选中“edit”进行编译确定无错之后，在鼠标右击选择“test”,执行，可查看“DBMS OUTPUT”输出的结果。

create or replace procedure test_text
  as
  v_num number;
  tel  varchar2(100);
  text varchar2(1200);
  output varchar2(100);
  -- 设置游标
  cursor select_tel is   select RECV_MOBILES,CONTENT from sqm_fnt.sm_sms_info_tbl t 
  -- where sm_sms_info_id=0001
  ;
  begin
  -- 循环打印
  for v_pos in select_tel loop
   tel  := v_pos.RECV_MOBILES;
   text := v_pos.CONTENT;
   v_num := length(tel) - length(REPLACE(tel, ','))+1; -- 6
   while (v_num>=1)
    loop
           SELECT regexp_substr(tel, '[^,]+', 1, v_num) into output  FROM dual;
           v_num := v_num -1; -- 6,5,4,3,2,1,0
            dbms_output.put_line(v_num);
          --  tel := SUBSTR(tel, INSTR(tel, ',', 1, 1) + 1);
          
    dbms_output.put_line('电话号码：'||output||','||'短信内容:'||text);

    end loop;
  end loop;
  end;