SQL lqb 4

gistfile1.txt

LAB 4 number 1.  

CREATE OR REPLACE PROCEDURE ADDinterest11(abranchName VARCHAR,interest DECIMAL)AS
BEGIN 
UPDATE Deposit SET balance=balance+(balance*interest)
WHERE branchName=abranchName;
END;
/


number 2.


CREATE OR REPLACE PROCEDURE ToDeposit00( abranchName VARCHAR,interest DECIMAL)AS

CURSOR  c1 IS 

SELECT customerName,balance FROM Deposit 
WHERE branchName=abranchName;

acustomer   Deposit.customerName%TYPE;
abalance    Deposit.balance%TYPE;

BEGIN


UPDATE Deposit SET balance=balance+(balance*interest)
WHERE branchName=abranchName;

OPEN  c1;
LOOP

FETCH c1 INTO acustomer,abalance;
EXIT WHEN c1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(   acustomer  ||' '||abalance);
END LOOP;
CLOSE c1;
END;

/



number3.
CREATE OR REPLACE FUNCTION getamount(abranch varchar) RETURN DECIMAL IS
aAmount DECIMAL(6,2);
BEGIN
SELECT SUM(amount) INTO Aamount FROM Loan
WHERE branchname = abranch;
RETURN Aamount;
END;
/