Application of cursor and recursion in Oracle Functions

Application of cursor and recursion in Oracle Functions

Most programmers may be familiar with recursion in code, but it is rare to write a recursive estimate in a stored procedure or function.

Today, we have encountered an example of using recursion in Oracle functions, which is recorded for future reference.

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 );
-- Queries the cursor of the Directory table data based on the parent node ID of the Directory table
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
-- Record the old parent node ID
V_oldCatalogueId: = v_pcatalogue;
-- Record the new parent node 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
-- Generate a new id and copy a job
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 (Region, c2.cname, c2.ename, c2.cdescription, c2.edescription, c2.authtype, region, c2.orderb, c2.createtime, c2.userid, c2.creater, c2.updatetime, region, c2.updator, c2.state );
Commit;
-- The variable value is not used, and the compilation cannot be completed.
V_value: = F_Func (v_oldCatalogueId, v_newCatalogueId, v_authTypeId );
End loop;
-- This return is very important.
Return 1;
End if;

END F_Func;
/

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian