存儲過程遞歸

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