Keep Learning, Keep Growing

Functions A function is compiled and executed every time whenever it is called. A function must return a value and cannot modify the data received as parameters Stored Procedures Stored Procedures are pre-compiled objects which are compiled for the first time and its compiled format is saved, which executes (compiled code) whenever it is called. For more about a stored procedure, please refer to the article  Different types of Stored Procedure .

--generate checksum for all records in transaction history --remember to change checksumKey DECLARE checksumKey varchar2 ( 100 ) : = 'Tcbs@20022020' ; rawData varchar2 ( 1000 ) : = '' ; newchecksum varchar2 ( 1000 ); BEGIN DBMS_OUTPUT . PUT_LINE ( 'INFO: Workflow initiated' ); FOR txh IN ( SELECT * FROM IXU_TRANSACTION_HISTORY WHERE CAMPAIGN_ID = 2006 ORDER BY ID DESC ) LOOP rawData : = concat ( ':' , txh . HISTORY_KEY ); IF txh . EXPIRED_DATE IS NULL THEN rawData : = concat ( '' , rawData ); rawData : = concat ( ':' , rawData ); ELSE rawData : = concat ( TO_CHAR ( txh . EXPIRED_DATE , 'YYYY-MM-DD' ), rawData ); rawData : = concat ( ':' , rawData ); END IF ; rawData : = concat ( to_char ( txh . OUTSTANDING , 'FM99999999999999990.00' ), rawData ); rawData : = concat ( ':' , rawData ); rawData : = concat ( to_char ( ...

CREATE INDEX temp_tcbsid_IXU_TRANSACTION_HISTORY ON IXU_TRANSACTION_HISTORY ( TCBSID ); CREATE INDEX temp_tcbsid_IXU_GENERAL_LEDGER ON IXU_GENERAL_LEDGER ( TCBSID ); create procedure updateCurrentBalanceExactly ( tcbsid_in IN varchar2 , redeemablePoint_in IN number , rankingPoint_in IN number ) is begin update IXU_GENERAL_LEDGER set RANKING_POINT = rankingPoint_in , REDEEMABLE_POINT = redeemablePoint_in where TCBSID = tcbsid_in ; -- caculate checksum here -- commit; end ; create procedure updateHistoryByID ( id_in IN number , outstanding_in IN number ) is begin update IXU_TRANSACTION_HISTORY set OUTSTANDING = outstanding_in where ID = id_in ; -- caculate checksum here -- commit; end ; create procedure arrangeHistoryByIssueDateAndGlIDExactly ( tcbsid_in IN varchar2 , award_type_in IN varchar2 ) is initBalance number ; begin initBalance = 0 ; for txHistory in...

Declare procedure verifyPointOutStandingByTcbsId ( tcbsid_in IN varchar2 ) is sumOfCreditPointRedem number ; sumOfDebitPointRedem number ; resultRedem number ; currentBalanceRedem number ; sumOfCreditPointRank number ; sumOfDebitPointRank number ; resultRank number ; currentBalanceRank number ; begin -- dbms_output.PUT_LINE('verifying this tcbsid: ' || tcbsid_in); select sum ( POINT ) into sumOfDebitPointRedem from IXU_TRANSACTION_HISTORY where ACTION = 'Debit' and AWARD_TYPE = 'Redeemable' and TCBSID = tcbsid_in ; select sum ( POINT ) into sumOfDebitPointRank from IXU_TRANSACTION_HISTORY where ACTION = 'Debit' and AWARD_TYPE = 'Ranking' and TCBSID = tcbsid_in ; select sum ( POINT ) into sumOfCreditPointRedem from IXU_TRANSACTION_HISTORY where ACTION = 'Credit' and AWARD_TYPE = 'Redeem...

1. Create store proceduce ----- STORED PROCEDURE QUERY #1 ----- DELIMITER $ CREATE PROCEDURE findAllEmployees ()     BEGIN         SELECT * FROM employee;     END $ DELIMITER ; ----- STORED PROCEDURE QUERY #2 ----- DELIMITER $ CREATE PROCEDURE findEmployeeByDepartment (IN emp_department VARCHAR(200))     BEGIN         SELECT * FROM employee emp WHERE emp.edept = emp_department;     END $ DELIMITER ; ----- STORED PROCEDURE QUERY #3 ----- DELIMITER $ CREATE PROCEDURE findEmployeeCountByDesignation (IN emp_designation VARCHAR(200), OUT designation_count INT(50))     BEGIN         SELECT COUNT(*) INTO designation_count FROM employee emp WHERE emp.edesig = emp_designation;     END $ DELIMITER ; 2. Create Entity @Entity @Table(name= "employee") public class Employee {     @Id     @GeneratedValue(strategy= GenerationType.IDENTITY)     pr...