存储过程递归

1. 如何实现sql语句的递归查询

1．创建测试表，createtabletest＿connect（idnumber，p＿idnumber）；

2. sql存储过程用递归

表结构：
sale_area销售分区表
PK#area_id
class_id
gonghao
prod_class产品分类表
PK#class_id
class_name
问题sql语句：
select
distinct
pa.class_id
from
prod_class
As
pa,prod_class
As
pb,sale_area
where
pb.class_id
in(select
sale_area.class_id
from
sale_area
where
sale_area.gonghao=<param>)
and
pa.class_id
like
pb.class_id||'%'
问题定义：
根据传入的参数gonghao来确定对应的class_id集，然后遍历这个class_id集，并“扩充”这个集合
要理解“扩充”必须知道class_id是这样设等级：
0为最高级
|_01
|
|_011
|
|_012
|_02
|_011
|_012
那么拥有等级0的话，所有的等级都应该可以访问，拥有等级01的话，011、012也可以访问。
这句sql的目的就是得到当前员工能访问的所有产品分类及它的子集。
通过自连接的做法在Oracle里是对的，在DB2里就错，原因在于DB2中谓词like两边至少有一个是字符串，也就是说DB2不认为pb.class_id||'%'是字符串。
求解：
要完成上面的任务有什么可替代的办法或改进的地方，注意环境是DB2
UDB
7.2。
---------------------------------------------------------------
就是树的问题。
使用公共表达式可以完成。
你在信息中心里面按recusion去搜索，可以找到递归sql的写法。
---------------------------------------------------------------
问题写的很清楚，我喜欢！
如果要使用DB2的公共表达式建议你把
prod_class产品分类表结构改一下
PK#class_id
class_name
Parent_Class_id
sql语句如下：
with
temp_class_id(
class_id,class_name)
(
select
pc.class_id,pc.class_name
from
prod_class
pc
where
pc.class_id
in
(select
sale_area.class_id
from
sale_area
where
sale_area.gonghao=<param>)
union
select
pc.class_id,pc.class_name
from
prod_class
pc,
temp_class_id
tc
where
pc.parent_id
=
tc.class_id)
select
*
from
temp_class_id;

3. sql 知道父节点，查询所有的子节点，运用游标，递归，存储过程

呃，因为我不清楚你的表里，这个BPROD
char(15)
老爸
里面记录的值是不是和BMWHS
对应的，所以只能说个大概。
select
sys_connect_by_path(username,'>')
"Path"
from
tmbm
start
with
id=1
connect
by
prior
id=parentid;
其中，id是你要遍历的起始点，比如你想从
厂号=XX
的这个父节点开始寻找他所有的子节点，这里就换成
start
with
BMWHS=XX
然后，connect
by
prior
id=parentid，这里id=parentid，简单解释就是寻找其他记录里，parentid和我的id相同的记录，也就是找子节点。
应该是换成你的BMWHS=BPROD（我不知道你的BPROD
BCHLD
和哪个属性是对应的，是BMWHS吗？）
如果是的话就是下面这样（username是你要返回的值，假设你还是要返回
BSEQ
序号）
select
sys_connect_by_path(BSEQ,'>')
"Path"
from
tmbm
start
with
BMWHS=XX
connect
by
prior
BMWHS=BPROD;

4. 创建学生-课程数据库，编写存储过程，完成以下功能：

主要问题是存储过程主体缺少begin 和 end 语句，使得后面的测试语句也被编译成了存储过程的主体，从而造成了递归（存储过程调用自己）。
alter procere 学生信息
@snum varchar(4),
@AvgScore int OUTPUT,
@CourseNum int OUTPUT,
@NotpassNum int OUTPUT
AS
BEGIN
Select @AvgScore = Avg(Score) From SC Where SC.snum = @snum
Select @CourseNum = Count(*) From SC Where SC.snum = @snum
Select @NotpassNum = Count(*) From SC Where SC.snum = @snum and score<60
END

另外，分号是SQL语句的结束符，不明白为什么有存储过程名后面会紧跟着一个分号。

5. oracle 存储过程 递归实现 依赖分析

下面是用oracle数据库解决不用startwith来查询子父数据查询方法，里面主要用到了substr和instr函数（这两个函数，其他数据库也有相对应的函数），游标（其他数据库也有游标）。

-- 1 前提：创建表以及插入数据
CREATE TABLE TMP_TEST(MAIN_COLUMN VARCHAR2(10),PARENT_COLUMN VARCHAR2(10));
INSERT INTO TMP_TEST(MAIN_COLUMN,PARENT_COLUMN) VALUES('A',NULL);
INSERT INTO TMP_TEST(MAIN_COLUMN,PARENT_COLUMN) VALUES('B','A');
INSERT INTO TMP_TEST(MAIN_COLUMN,PARENT_COLUMN) VALUES('C','A');
INSERT INTO TMP_TEST(MAIN_COLUMN,PARENT_COLUMN) VALUES('D','A');
INSERT INTO TMP_TEST(MAIN_COLUMN,PARENT_COLUMN) VALUES('E','B');
INSERT INTO TMP_TEST(MAIN_COLUMN,PARENT_COLUMN) VALUES('F','C');
INSERT INTO TMP_TEST(MAIN_COLUMN,PARENT_COLUMN) VALUES('G','E');

-- 2 创建存储过程
CREATE OR REPLACE PROCEDURE GET_TREE(IS_PARENT IN NUMBER /** 子父查询 **/,
SEARCH_ID IN VARCHAR2 /** 查询条件节点 **/,
TREE_RESOUT OUT VARCHAR2 /** 输出结果集合 **/)

AS

V_TEMP VARCHAR2(4000);
V_SEARCH VARCHAR2(4000);
V_INDEX INTEGER;

BEGIN

V_TEMP :=SEARCH_ID||'-';
TREE_RESOUT := '';

WHILE length(V_TEMP) > 0 LOOP
V_INDEX := instr(V_TEMP,'-');
V_SEARCH := substr(V_TEMP,0,V_INDEX-1);
V_TEMP := substr(V_TEMP,V_INDEX+1);
/*DBMS_OUTPUT.put_line('V_INDEX:'|| V_INDEX ||'V_TEMP:' ||V_TEMP||'V_SEARCH:'|| V_SEARCH);*/
/** 查询子节点 **/
if(IS_PARENT = 1) THEN
FOR C1 IN (SELECT * FROM TMP_TEST T1 WHERE T1.PARENT_COLUMN = V_SEARCH) LOOP
TREE_RESOUT := TREE_RESOUT || C1.MAIN_COLUMN;
V_TEMP := V_TEMP || C1.MAIN_COLUMN || '-';
END LOOP;
ELSE
/** 查询父节点 **/
FOR C1 IN (SELECT * FROM TMP_TEST T1 WHERE T1.MAIN_COLUMN = V_SEARCH) LOOP
TREE_RESOUT := TREE_RESOUT || C1.PARENT_COLUMN;
V_TEMP := V_TEMP || C1.PARENT_COLUMN || '-';
END LOOP;
END IF;
END LOOP;
/*DBMS_OUTPUT.put_line('TREE_RESOUT:'||TREE_RESOUT);*/
END;

-- 3 调用存储过程
declare
TREE_RESULT VARCHAR2(4000);
SEARCH_ID VARCHAR2(4000);
begin
get_tree(1,'A',TREE_RESULT);
dbms_output.put_line('查询子节点:' || TREE_RESULT);
get_tree(0,'G',TREE_RESULT);
dbms_output.put_line('查询父节点:' || TREE_RESULT);
end;

6. 递归的应用举例

触发器有两种不同的递归方式，直接递归和间接递归。

1．直接递归

当触发器被激活并执行一个操作时，该操作又使用同一个触发器再次执行操作，被称为直接递归。

例利用触发器的直接递归，当在表中删除一条记录时，通过触发器删除表中所有编号相同的记录。

2．间接递归

更改表1中的数据时，触发器被激活并执行一个操作，而该操作又使另一个表2中的某个触发器被激活。表2中的这个触发器使表1得到更新，从而再次激活表1中的触发器，被称为间接递归。

例如，一应用程序更新了表TA，并引发触发器Trigger_A。Trigger_A更新表TB，从而使触发器Trigger_B被引发。Trigger_B转而更新表TA，从而使Trigger_A再次被引发间接递归。

可以利用系统存储过程sp_dboption关闭直接递归，但这又会开启间接递归。若要把这两种类型的递归都禁止，需要使用系统存储过程sp_configure。
参考资料：明日系列编程词典--SQL Server 编程词典体验版

7. 你给我写的那个存储过程递归CTE，最后的option (MAXRECURSION 4000)是什么意思是不是说递归4000次

最大允许递归的次数。
默认最大CTE递归只有100次，而你要求插入10年的数据，需要递归3000多次，所以要使用option (MAXRECURSION 4000)

8. sql存储过程用递归

表结构：
sale_area销售分区表
PK#area_id
class_id
gonghao

prod_class产品分类表
PK#class_id
class_name

问题sql语句：
select distinct pa.class_id from prod_class As pa,prod_class As pb,sale_area where pb.class_id in(select sale_area.class_id from sale_area where sale_area.gonghao=<param>) and pa.class_id like pb.class_id||'%'

问题定义：
根据传入的参数gonghao来确定对应的class_id集，然后遍历这个class_id集，并“扩充”这个集合
要理解“扩充”必须知道class_id是这样设等级：
0为最高级
|_01
| |_011
| |_012
|_02
|_011
|_012
那么拥有等级0的话，所有的等级都应该可以访问，拥有等级01的话，011、012也可以访问。
这句sql的目的就是得到当前员工能访问的所有产品分类及它的子集。

通过自连接的做法在Oracle里是对的，在DB2里就错，原因在于DB2中谓词like两边至少有一个是字符串，也就是说DB2不认为pb.class_id||'%'是字符串。

求解：
要完成上面的任务有什么可替代的办法或改进的地方，注意环境是DB2 UDB 7.2。
---------------------------------------------------------------

就是树的问题。
使用公共表达式可以完成。
你在信息中心里面按recusion去搜索，可以找到递归sql的写法。
---------------------------------------------------------------

问题写的很清楚，我喜欢！
如果要使用DB2的公共表达式建议你把
prod_class产品分类表结构改一下
PK#class_id
class_name
Parent_Class_id

sql语句如下：

with temp_class_id( class_id,class_name)
(
select pc.class_id,pc.class_name from prod_class pc
where pc.class_id in (select sale_area.class_id from sale_area
where sale_area.gonghao=<param>)
union
select pc.class_id,pc.class_name from prod_class pc,
temp_class_id tc
where pc.parent_id = tc.class_id)
select * from temp_class_id;

9. java调用存储过程，打开结果集报递归SQL级别1出现错误且超出打开游标的最大数 这是怎么回事

在命令窗口执行show parameter open_cursors;
看看你给数据库配置的游标数是多大，估计是150
需要调整的话，网络搜一下游标设置。

10. oracle递归存储过程，求指教

1楼，你从哪扒的查询语句，字段和字段内容也不给人家改一下
select parentid from 表名 start with id =5 connect by prior parentid =id;

这个是查所有的父亲节点，但是基于你要查本身，所以可以改成
select id from 表名 start with id =5 connect by prior parentid =id;一定要父亲=儿子，如果儿子=父亲了那就是查所有子节点了，start with id =5就是要查的那个id