Home  >  Article  >  Database  >  Oracle 函数中游标及递归的应用

Oracle 函数中游标及递归的应用

WBOY
WBOYOriginal
2016-06-07 16:49:571228browse

在代码中使用递归可能大部分程序员都不陌生,但是在存储过程或者函数中写个递归估计就不多见了。今天遇到了一个在Oracle函数中使

在代码中使用递归可能大部分程序员都不陌生,但是在存储过程或者函数中写个递归估计就不多见了。

今天遇到了一个在Oracle函数中使用递归的例子,特记录下来,方便以后查阅

CREATE OR REPLACE FUNCTION F_Func(v_pcatalogue IN NUMBER, i_newPcatalogue IN NUMBER, v_authTypeId in number)
  RETURN NUMBER
  as
  v_newCatalogueId number(10);
  v_oldCatalogueId number(10);
  v_newPcatalogue number(10);
  v_count number(10);
  v_value number(10);
  --根据目录表父节点ID查询目录表数据的游标
  Cursor cusor_1(v_pcatalogue number) is
        select id, cname, ename, cdescription, edescription, authtype, pcatalogue, orderb, createtime, userid, creater, updatetime, updateuserid, updator, state from t_catalogue
        where authtype=v_authTypeId and pcatalogue=v_pcatalogue;

BEGIN
  --记录下旧的父节点ID
  v_oldCatalogueId := v_pcatalogue;
  --记录下新的父节点ID
  v_newPcatalogue := i_newPcatalogue;
  select count(1) into v_count from t_catalogue where authtype=v_authTypeId and pcatalogue=v_pcatalogue;
  if v_count = 0 then
    return 1;
  else
    for c2 in cusor_1(v_oldCatalogueId) loop
 --生成新的id及拷贝工作
      v_oldCatalogueId := c2.id;
      select HIBERNATE_SEQUENCE.NEXTVAL into v_newCatalogueId from dual;
      if v_newPcatalogue is null then
        select HIBERNATE_SEQUENCE.NEXTVAL into v_newPcatalogue from dual;
      end if;
      insert into t_catalogue_20140619(id, cname, ename, cdescription, edescription, authtype, pcatalogue, orderb, createtime, userid, creater, updatetime, updateuserid, updator, state)
            values(v_newCatalogueId, c2.cname, c2.ename, c2.cdescription, c2.edescription, c2.authtype, v_newPcatalogue, c2.orderb, c2.createtime, c2.userid, c2.creater, c2.updatetime, c2.updateuserid, c2.updator, c2.state);
      commit;
      --不用变量接值,编译过不了
      v_value := F_Func(v_oldCatalogueId, v_newCatalogueId, v_authTypeId);
    end loop;
    --这个返回很重要
    return 1;
  end if;

END F_Func;
/

Linux-6-64下安装Oracle 12C笔记

在CentOS 6.4下安装Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虚拟机中安装步骤

Debian 下 安装 Oracle 11g XE R2

本文永久更新链接地址:

linux

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn