示例:在某个树中函数的使用
CREATE OR REPLACE FUNCTION "getInitTreeId" (id in int, q_parentId in int, id2 in int)
RETURN int
AS
x int := id;
x1 int;
x2 int;
BEGIN
--dbms_output.put_line(x); 5 632 632
if q_parentId !=id2 then
if q_parentId != x then
while x != q_parentId loop
SELECT PARENT_ID into x2 FROM NET_SCENE_TREE WHERE ID = x;
if x2 = q_parentId then
SELECT id into x1 FROM NET_SCENE_TREE WHERE ID = x;
return x1;
end if;
SELECT PARENT_ID into x FROM NET_SCENE_TREE WHERE ID = x;
end loop;
else
return id2;
end if;
else
return 0;
end if;
END;
String sql = "select distinct \"getInitTreeId\"(parent_id,"+netST.getParent_id()+", id) as id from NET_SCENE_TREE where name like '%"+netST.getName()+"%'";
=======================
函数内部逻辑:
该函数有3个参数:1. parentId 2.用户传入parentId 3.id
首先 查看用户传入的parentId是否等于 该行数据的parentId
如果是
(用户点击查询的树上能匹配到搜索框中相应的name值,此时进入该判断)
那么返回该上数据的id
否则
判断用户传入的parentId是否等于 该行数据的parentId,如果不是,那么进入循环。
循环:
查询行的parentId是否等于用户传入的parentId,如果是那么返回该行的id
如果否,那么表示当前的树的节点不是用户点击查询的子节点,需要继续网上查找:获取到该节点的parentId将它作为新的行去循环中进行判断,知道循环结束。
如果是,那么
返回该行数据的id
=======================
1.查询是从上至下 都是一层一层往下查找的 所以该函数里的循环肯定是能返回出来,不会造成死循环。
2.用户在页面点击查询按钮 默认的parentId首先为0,然后会把第一列树的数据里的图标全部删除,然后再在查找到的树的对应id的span里添加图标。
3.用户点击树之后首先去将树加载出来,然后再去根据输入框是否有值,是否有根据这个值查找到了的id信息,如果有就在这些信息后添加图标,如果没有那么就不做操作。
示例:某个计划的立即执行调用的函数
create or replace function F_CREATE_CONCLUSION(planId in number) return number is PRAGMA AUTONOMOUS_TRANSACTION; Result number:=0; Cursor planCur is SELECT * FROM T_INSPECTION_PLAN WHERE ID = planId; planRow t_inspection_plan%Rowtype; currentDate date; --当前时间 currentQuarterDate date; --季度时间 insertFinallyDate date; --插入数据的最终时间 stationRow T_STATION_POLICE%Rowtype; locationType number; --通过计划id查询到 2.室内 1.室外,然后根据类别去将设备表中所有的类别的数据生成任务 Cursor deviceCur is SELECT * FROM t_device td where td.device_type in(SELECT tip.devicetype_id FROM t_inspection_plan tip where tip.id = planId); deviceRow t_device%rowtype; begin select to_date(to_char(sysdate,'yyyymmdd'),'yyyymmdd') into currentDate from dual; FOR planRow IN planCur LOOP -- 单次 IF planRow.PLANCYCLE=1 THEN insertFinallyDate := currentDate; -- 周 ELSIF planRow.PLANCYCLE=2 THEN insertFinallyDate := currentDate+7; -- 月 ELSIF planRow.PLANCYCLE=3 THEN insertFinallyDate := add_months(currentDate,1); -- 季度 ELSIF planRow.PLANCYCLE=4 THEN SELECT add_months(TRUNC(SYSDATE, 'Q'), +3) into currentQuarterDate FROM dual; insertFinallyDate := currentQuarterDate; END IF; locationType := planRow.DEVICETYPE_ID; --dbms_output.put_line(planRow.Id ); --dbms_output.put_line(planRow.DEVICETYPE_ID); --批量 IF planRow.DEVICE_ID is null THEN FOR deviceRow in deviceCur LOOP IF deviceRow.POLICE_ID !=null THEN select * into stationRow from T_STATION_POLICE TSP where TSP.ID = deviceRow.POLICE_ID; --关联查询到的设备都生成任务 INSERT INTO T_CONCLUSION(ID,ENDDATE,STARTDATE,STATUS,AUXILIARYDEVICEID,PLANID,USERID, ORDERUSERID, TYPE, STATIONID, REGIONID, POLICESTATIONID, company) values( SEQ_CONCLUSION_ID.NEXTVAL,insertFinallyDate,currentDate,0,deviceRow.device_id,planRow.Id,deviceRow.INSPECTIONUSERID, deviceRow.USERID, locationType, stationRow.STATION_ID, stationRow.AREGION_ID, stationRow.ID, deviceRow.company); ELSE --关联查询到的设备都生成任务 INSERT INTO T_CONCLUSION(ID,ENDDATE,STARTDATE,STATUS,AUXILIARYDEVICEID,PLANID,USERID, ORDERUSERID, TYPE, company) values( SEQ_CONCLUSION_ID.NEXTVAL,insertFinallyDate,currentDate,0,deviceRow.device_id,planRow.Id,deviceRow.INSPECTIONUSERID, deviceRow.USERID, locationType, deviceRow.company); END IF; END LOOP; --专项 ELSE select * into deviceRow from t_device td where td.DEVICE_ID = planRow.DEVICE_ID; IF deviceRow.POLICE_ID is not null THEN select * into stationRow from T_STATION_POLICE TSP where TSP.ID = deviceRow.POLICE_ID; --关联查询到的设备都生成任务 INSERT INTO T_CONCLUSION(ID,ENDDATE,STARTDATE,STATUS,AUXILIARYDEVICEID,PLANID,USERID, ORDERUSERID, TYPE, STATIONID, REGIONID, POLICESTATIONID, company) values( SEQ_CONCLUSION_ID.NEXTVAL,insertFinallyDate,currentDate,0,deviceRow.device_id,planRow.Id,deviceRow.INSPECTIONUSERID, deviceRow.USERID, locationType, stationRow.STATION_ID, stationRow.AREGION_ID, stationRow.ID, deviceRow.company); ELSE --关联查询到的设备都生成任务 INSERT INTO T_CONCLUSION(ID,ENDDATE,STARTDATE,STATUS,AUXILIARYDEVICEID,PLANID,USERID, ORDERUSERID, TYPE, company) values( SEQ_CONCLUSION_ID.NEXTVAL,insertFinallyDate,currentDate,0,deviceRow.device_id,planRow.Id,deviceRow.INSPECTIONUSERID, deviceRow.USERID, locationType, deviceRow.company); END IF; END IF; Result:=1; COMMIT; END LOOP; return(Result); end F_CREATE_CONCLUSION;
通过传入的计划id查找到相应的计划信息,拿到这些计划信息后,通过条件进行任务的生成。
示例:函数调用函数
begin dbms_output.put_line(get_annual_sal('李红')); end;
示例:函数中循环
FOR deviceRow in deviceCur LOOP dbms_output.put_line('1'); END LOOP;
示例:函数返回游标
create or replace function test111(itemNumber in varchar2) return SYS_REFCURSOR is return_cursor SYS_REFCURSOR; begin OPEN return_cursor FOR SELECT 'a' FROM dual WHERE 1 = itemNumber; RETURN return_cursor; end test111;
示例:函数用 使用函数返回游标
company varchar2(50); type ref_cursor is ref cursor; company_cur ref_cursor; company_cur := F_GET_DEVICELINK_COMPANY(planRow.DEVICE_ID) ; loop Fetch company_cur InTo company; Exit When company_cur%NotFound; dbms_output.put_line(company); End Loop;