Procedure,function,bulk collect,forall,start with ...connect by ...prior ...哦哈哈

目录

存储过程常用语句

procedure结构类型

function结构类型

存储过程和函数的区别和联系

bulk collect 用法

FORALL用法

FORALL 与  bulk collect 的综合运用

start with ...connect by ...prior...  针对B树结构类型的数据 的递归查询

---

存储过程常用语句

procedure proc_name is v_count_num number := 1000;                        --定义多少次循环commit一次in_region_id  varchar2 := '8392819432';v_count := 0;v_lan_id varchar2(100);v_sql  varchar2(100); v_port_id  varchar2(24);v_port_no  varchar2(100);v_row  user_procedure%rowtype；cursor v_proc_list is select * from user_procedure where ....;        --各种变量及游标声明部分
beginfor v_row in v_proc_list loop                         --for循环beginv_sql := 'exec pkg_dbdata.'||v_row.procedure_name;execute immediate v_sql;select lan_id into v_lan_id                               -- start with ...connect by ...prior... 下面详解from spc_region where grade_id = '2000004'start with region_id = in_region_idconnect by prior super_region_id = in_region_id ;v_lan_id  := func_get_lan_id(v_row.region_id);     --调用外函数，放在一个包pkg内无妨 update table2 set coloum1 = v_lan_id  where order_id = v_row.order_id;--充分利用dualselect '00012532'||lpad(seq_RME_PORT.nextval,16,'0') into v_port_id from dual; v_port_no := eqp_no||'PORT'||v_port_num_1;insert into rme_port (PORT_ID,Port_No) values (v_port_id,v_port_no);--灵活运用内置函数select count(1) into v_xxx from table3 where coloum2 = uppper(v_row.table_name);--可利用declare临时声明declare cursor c_mmm is select ... from  table3 where ....;exception when others then null;end;if v_row.flag > 0 then begin.....(如上花样)exception when others then dbms_output.put_line('error,获取表'||uppper(v_row.table_name)||'字段');end;end if;v_count := v_count+1;if v_count = v_count_num thencommit;v_count := 0;end if;end loop;commit;
end;

关于循环，有三种，上面用的是for循环，个人认为较为简便。令两种是 fetch 和 while

我不喜欢while,不说了哈哈哈

提一提fetch

目前而言，我觉得，当我们需要 游标结果集中的记录数时，fetch比较合适(不知道有没有  游标.count  这个用法，有的话我就快要放弃fetch直接全用for了哈哈哈)

fetch 实例：

procedure proc_update_pip_marker is v_limit_num := 1000;v_count number := 0;v_lan_id varchar2(100);cursor c_pip_marker isselect * from pip_marker;type t_pip_marker is table of pip_marker%rowtype;           --定义 表 类型v_pip_marker t_pip_marker;                                  --结果集变成了表结构型 
beginopen c_pip_marker;loop fetch c_pip_marker bulk collcet                         --bulk collect  into v_pip_marker limitv_limit_num; for i in 1 .. v_pip_marker.count loopv_lan_id := func_get_lan_id(v_pip_marker(i).region_id);beginupdate pip_marker set lan_id = v_lan_id where ...;.....(如上例花样)exception when others then null;end;end loop;exit when c_pip_marcker%notfound;           --与  fetch...  对应end loop;close c_pip_marker;commit;
end;

procedure结构类型

学习于https://www.cnblogs.com/zlbx/p/4818007.html后总结如下 

无参无返：上例就是

--执行存储过程方式1
set serveroutput on;
begin
  p1();
end;
--执行存储过程方式2
set serveroutput on;
execute p1();

有参有返：

create or replace procedure p2 ( name in varchar2 , age int ,msg out varchar2)  
--参数列表中，声明变量类型时切记不能写大小写，只写类型名即可
--关键字  in  , out  , in out ,  不写代表默认为in
is 
beginmsg := '姓名'||name||',年龄'||age;
end;  

执行存储过程法一begin,end

set serveroutput on;
declare
  msg varchar2(100);
begin
  p2('张三',23,msg);
  dbms_output.put_line(msg);
end;

以下括号部分未验证，属个人理解

（--执行存储过程法二
别想了，没有法二，不可以用execute,因为他带有参数输出，而程序默认还是使用的无输出参数的begin，declare来运行的。

那么，为什么使用的还是无输出参数的begin，declare呢？

因为你   is  后面没有定义的参数

所以，如果使用execute，可以将存储过程的参数定义在存储过程中，而不是参数列表中）

存储过程中定义参数

create or replace procedure p3
as--存储过程中定义的参数列表name varchar(50);
beginname := 'hello world';dbms_output.put_line(name);
end;

---执行存储过程
set serveroutput on;
execute p3();

参数列表有 in out 参数

create or replace procedure p4
(msg in out varchar2)
--当既想携带值进来，又想携带值出去，可以用in out
as
begindbms_output.put_line(msg); --输出的为携带进来的值msg:='我是从存储过程中携带出来的值';
end;--执行存储过程
set serveroutput on;
declaremsg varchar2(100):='我是从携带进去的值';
beginp4(msg);dbms_output.put_line(msg);
end;

function结构类型

function 结构类型其实和procedure 相似，但是function 多个return 

function必须要用return来返回值，有三种，一种标量值函数(返回的是个标量)，另一种是表值函数，第二种又分为两类，一类是内嵌表值函数(返回数即表内内容，不返回表结构)或多语句函数（不仅返回数据还返回表结构）

create or replace function f1
return varchar--必须有返回值，且声明返回值类型时不需要加大小
asmsg varchar(50);
beginmsg := 'hello world';return msg;
end;

存储过程和函数的区别和联系

最重要一点  ——  存储过程中不能用return返回值，但函数中可以，而且函数中必须有return子句。

还有点就是，执行方式略有不同，存储过程的执行方式有两种（1.使用execute2.使用begin和end），函数除了存储过程的两种方式外，还可以当做表达式使用，例如放在select中（select f1() form dual;）

variable a varchar2(24);
execute :a:=create_otn('bjdkbsbidsb,'026735672178183929');

 

bulk collect 用法

学习于https://blog.csdn.net/leeboy_wang/article/details/7991021后总结如下

bulk collect ——批查询，可以将结果集一次性加载到collections中(cursor是用来一条一条处理)，所以 bulk collect into 的所有对象都必须是collection，增强SQL引擎到PL/SQL引擎的交互。

可以在select into,fetch into,returning into语句使用bulk collect。

SELECT sal BULK COLLECT INTO sals FROM emp SAMPLE(10); --sample(10) 表示随机选取10%的记录行，sample后续...FETCH c_tmp_cursor BULK COLLECT INTO dept_recs;RETURNING employee_id, last_name BULK COLLECT INTO enums, names;

Bulk Collect批查询是将所需数据(一次性将结果集绑定到一个集合变量中)读入内存，然后再统计分析(从SQL引擎发送到PL/SQL引擎分析)，所以是从SQL引擎到PL/SQL引擎查询效率的提高（增强SQL引擎到PL/SQL引擎的交换）。

但是，如果Oracle数据库的内存较小，Shared Pool Size不足以保存Bulk Collect批查询结果，那么该方法需要将Bulk Collect的集合结果保存在磁盘上，在这种情况下，Bulk Collect方法的效率不高

有bulk  collect提高SQL引擎到PL/SQL引擎的交换，应该也有增强PL/SQL到SQL引擎交换的，那就是FORALL

FORALL用法

学习于https://www.cnblogs.com/hellokitty1/p/4584333.html后总结如下

FORALL 将多个DML(增删改execute)批量发送给SQL引擎来执行，增强PL/SQL引擎到SQL引擎的交互。

CREATE TABLE tmp_tab(id     NUMBER(5),NAME   VARCHAR2(50)
);--批量插入      相当于  create table tmp_table as select */... from tb_table ... ; 
DECLAREindex_poniter index_poniter_type;TYPE tb_table_type is table of tmp_tab%rowtype index by binary_integer;tb_table  tb_table_type;
BEGINfor i in 1 ..100 LOOPtb_table(i).id := 1;.....(同最上那些花样)end loop;--forall 用法一：forall i  in lower_bound .. uppper_boundforall i in 1..tb_table.countinsert into tmp_tab values tb_table(i);       --批量插入update tmp_tab t SET row = tb_table(i) where t.id =tb_table(i).id;      --批量修改dekete from tmp_tab where id =tb_table(i).id;     --批量删除execute immediate ....;                         --动态（EXECUTE IMMEDIATE）DML语句--forall 用法二：forall i  in INDICES OF collection_name [ BETWEEN lower_bound AND upper_bound ]           (indices--这个表里所有有用的行的下标集合)forall i in indices of tb_tableinsert into tmp_tab values tb_table(i);.....(dml_statement)--forall 用法三：FORALL i IN VALUES OF index_poniter    (指定下表集合)index_pointer := index_pointer_type(1,3,7,10,16,21);forall i in values of index_pointerinsert into tmp_tb values tb_table(i);END;

forall 格式

FORALL index_name IN 
     { lower_bound .. upper_bound                                                                                      --易知       1 ..100之类的
     | INDICES OF collection_name [ BETWEEN lower_bound AND upper_bound ]         --collection中去除  没有赋值的元素，例如被 DELETE 的元素，NULL 也算值  后，剩下的下标来放入indices中
     | VALUES OF index_collection         --指定 集合中的下标，该集合中的值只能是PLS_INTEGER/BINARY_INTEGER。
     }
  [ SAVE EXCEPTIONS ]      --可选关键字，表示即使一些DML语句失败，直到FORALL LOOP执行完毕才抛出异常。可以使用SQL%BULK_EXCEPTIONS 查看异常信息。

dml_statement;  --静态语句，例如：UPDATE或者DELETE；或者动态（EXECUTE IMMEDIATE）DML语句。

FORALL 与  bulk collect 的综合运用

-- 创建表tb_emp
CREATE TABLE tb_emp AS  SELECT empno, ename, hiredate  FROM   emp  WHERE  1 = 0;  DECLARE-- 声明游标CURSOR emp_cur ISSELECT empno, ename, hiredate FROM emp;-- 基于游标的嵌套表类型TYPE nested_emp_type IS TABLE OF emp_cur%ROWTYPE;-- 声明变量emp_tab   nested_emp_type; 
BEGIN  SELECT empno, ename, hiredate  BULK   COLLECT INTO emp_tabFROM   emp  WHERE  sal > 1000;-- 使用FORALL语句将变量中的数据插入到表tb_emp  FORALL i IN 1 .. emp_tab.COUNT                     INSERT INTO (SELECT empno, ename, hiredate FROM tb_emp)  VALUES emp_tab( i );  COMMIT;  DBMS_OUTPUT.put_line('总共向 tb_emp 表中插入记录数： ' || emp_tab.COUNT);  
END;

start with ...connect by ...prior...  针对B树结构类型的数据 的递归查询

学习于https://www.cnblogs.com/benbenduo/p/4588612.html后总结如下

针对B树结构类型的数据，给出B树结构类型中的任意一个结点，遍历其最终父结点或者子结点。

start with 子句：遍历起始条件，有个小技巧，如果要查父结点，这里可以用子结点的列，反之亦然。

connect by 子句：连接条件。关键词prior，prior跟父节点列parentid放在一起，就是往父结点方向遍历；prior跟子结点列subid放在一起，则往叶子结点方向遍历，

                         parentid、subid两列谁放在“=”前都无所谓，关键是prior跟谁在一起。