ORACLE Case Study on stored procedures, oracle stored procedures

ORACLE Case Study on stored procedures, oracle stored procedures
ORACLE detailed requirements on stored procedure cases

Using the stored procedure: Obtain the old organization number from the CDEPDP table, then obtain the new organization number based on the old organization number table CDETRADEEX_BAK, and suffix the new organization number to the old organization number.

Ideas

Obtain the old organization number string from the CDEPDP table in the form of "1K7040, 1K7090, 1K7019", and then assemble the string into an array using the regular expression. The CDETRADEEX_BAK is queried based on the old organization number, obtain the new organization number, and suffix the new organization number after the old organization number.

SPERIOD

Create or replace procedure speriod is/* CREATE cur_CDEPDP, used to extract trades data in CDEPDP */Cursor cur_CDEPDP is select trades from cdepdp; v_count NUMBER; v_sum NUMBER; loop_counter NUMBER; v_sum2 NUMBER; v_result CDEPDP. TRADES % TYPE; oldOrg_var CDEPDP. TRADES % TYPE; oldOrg tables % TYPE; invalid_old_orgno EXCEPTION; BEGIN v_count: = 0; v_sum: = 0; v_sum2: = 0; loop_counter: = 1;/* use the cursor for loop, obtain data in CDEPDP by row */FOR trades in cur_CDEPDP LOOP v_result: = ''; DBMS_OUTPUT.PUT_LINE ('oldorg Items: '| trades. trades); oldOrg_var: = trades. trades; v_count: = v_count + 1; v_sum: = REGEXP_COUNT (oldOrg_var, ',') + 1; DBMS_OUTPUT.PUT_LINE ('total Items: '| v_sum ); /* IF only one data element exists */IF v_sum <2 THEN DBMS_OUTPUT.PUT_LINE (RETRIEVEORGNO (oldOrg_var); DBMS_OUTPUT.PUT_LINE ('result: '| oldOrg_var | ', '| RETRIEVEORGNO (oldOrg_var); else for loop_counter IN 1 .. v_sum LOOP oldOrg: = LTRIM (RTRIM (REGEXP_SUBSTR (oldOrg_var, '[^,] +', 1, loop_counter ),','),''); -- merge (oldOrg); -- DBMS_OUTPUT.PUT_LINE (RETRIEVEORGNO (oldOrg); if retrieveorgno (oldOrg) is not null then/* deduplication and splicing result (New Organization number) */IF REGEXP_LIKE (v_result, RETRIEVEORGNO (oldOrg) then continue; ELSE v_result: = v_result | ',' | RETRIEVEORGNO (oldOrg); end if; /* else raise invalid_old_orgno; CONTINUE; */end if; end loop;/* splicing result (old organization number + new organization number) */v_result: = oldOrg_var | ', '| LTRIM (v_result,', '); DBMS_OUTPUT.PUT_LINE ('result:' | v_result); v_sum2: = REGEXP_COUNT (v_result, ',') + 1; DBMS_OUTPUT.PUT_LINE ('total Items After Processed: '| v_sum2); end if; end loop;/* output test information */DBMS_OUTPUT.PUT_LINE ('sum Items:' | v_count ); /* EXCEPTION capture-data information does not exist */exception when NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('there is no new organization number in the database, the old organization number should be '); WHEN invalid_old_orgno THEN DBMS_OUTPUT.PUT_LINE ('the database does not have a new organization number pair that should be the old organization number 2'); end speriod;

RETRIEVEORGNO

Create or replace function RETRIEVEORGNO (oldOrg IN records % TYPE) return response % TYPE is return_value values % TYPE; begin select newtradeno into return_value FROM CDETRADEEX_BAK where oldtradeno = oldOrg; /* DBMS_OUTPUT.PUT_LINE ('Return _ value: '| return_value); */return (return_value ); /* WHEN the old organization number does not exist, the corresponding new organization Number */exception when NO_DATA_FOUND then return null; end RETRIEVEORGNO; select * from CDETRADEEX_BAK; select * from cdepdp;

Problems encountered

Regular Expression. It indicates that the two values are equal using "=". Is the relationship between packages, stored procedures, and functions? How to call each other?