{"title":"递归查询","inframe":false,"hidden":false,"roles":{"dev":1},"pageLink":"","children":[{"configs":{"description":"本例以SQL Server数据库为例演示基于SQL的递归查询，其他数据库请参考各自手册。\n请在数据库浏览器中分别创建下述两个数据库函数：\n--查询指定部门id及其所有子部门记录函数:\ncreate function getDept(@dept_id numeric(3))\nreturns table\nas\nreturn (with dept_view as(\nselect DEPT_ID, DEPT_NAME, PARENT_DEPT from WB_EMP_DEPT WHERE DEPT_ID=@dept_id\nunion all\nselect a.DEPT_ID, a.DEPT_NAME, a.PARENT_DEPT FROM WB_EMP_DEPT as a, dept_view as b WHERE a.PARENT_DEPT=b.DEPT_ID\n)\nselect * from dept_view\n);\n--查询部门路径函数：\ncreate function getDeptPath(@dept_id numeric(3))\nreturns nvarchar(1000)\nas\nbegin\ndeclare @dept_name as nvarchar(1000)\nselect @dept_name = DEPT_NAME from WB_EMP_DEPT where DEPT_ID = @dept_id\nwhile exists (select 1 from WB_EMP_DEPT where DEPT_ID = @dept_id and PARENT_DEPT <> -1)\nbegin\nselect @dept_id = b.DEPT_ID, @dept_name = b.DEPT_NAME + '->' + @dept_name from WB_EMP_DEPT a, WB_EMP_DEPT b\nwhere a.DEPT_ID = @dept_id and a.PARENT_DEPT = b.DEPT_ID\nend\nreturn @dept_name\nend;","serverScript":"/*\n//请先创建module.description中的函数再取消注释\n//查询部门编号为15及其所有子部门的记录\nvar sb = new StringBuilder(),\n  addComma = false,\n  rs = app.run('select * from getDept(15)');\nwhile (rs.next()) {\n  if (addComma)\n    sb.append(',');\n  else\n    addComma = true;\n  sb.append(rs.getString('DEPT_NAME'));\n}\n//查询部门编号为10的路径\nrs = app.run('select dbo.getDeptPath(10)');\nrs.next();\nsb.append('\\n路径为：' + rs.getString(1));\napp.send(sb);\n*/\napp.send('请参考module.description和module.serverScript说明');","itemId":"module"},"children":[],"type":"module","expanded":true}],"iconCls":""}