Scribd Logo
Upload

Welcome to Scribd!

  • 274 views

    Rdbms Lab - q5

    Uploaded by

    Jothi Kumar
    The code block accepts an account number from the user, looks up the account details from the Accounts table, debits Rs. 2000 from the account if the remaining balance is above Rs. 500, and displays the account details before and after the transaction. It uses PL/SQL control structures like IF-THEN-ELSE to check the balance condition before updating the database.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Rdbms Lab - q5

    Uploaded by

    Jothi Kumar
    274 views5 pages
    The code block accepts an account number from the user, looks up the account details from the Accounts table, debits Rs. 2000 from the account if the remaining balance is above Rs. 500, and displays the account details before and after the transaction. It uses PL/SQL control structures like IF-THEN-ELSE to check the balance condition before updating the database.

    Original Title

    Rdbms Lab- q5

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The code block accepts an account number from the user, looks up the account details from the Accounts table, debits Rs. 2000 from the account if the remaining balance is above Rs. 500, and displays the account details before and after the transaction. It uses PL/SQL control structures like IF-THEN-ELSE to check the balance condition before updating the database.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    274 views5 pages

    Rdbms Lab - q5

    Uploaded by

    Jothi Kumar
    The code block accepts an account number from the user, looks up the account details from the Accounts table, debits Rs. 2000 from the account if the remaining balance is above Rs. 500, and displays the account details before and after the transaction. It uses PL/SQL control structures like IF-THEN-ELSE to check the balance condition before updating the database.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 5

    Q.

    1 Write a PL/SQL code block that will accept a number from the user and debit an account
    of Rs.2000 from the account has min balance of 500 after the amount is debited. The process
    is to find account table.

    SQL> create table Accounts3(Account_id varchar(5), Name varchar(20), Bal


    number(7,2));
    Table created.

    SQL>insert into Accounts3 values ('AC001','Anuj',5000);


    insert into Accounts3 values ('AC002','Robert',10000);
    insert into Accounts3 values ('AC003','Mita',5000);
    insert into Accounts3 values ('AC004','Sunita',15000);
    insert into Accounts3 values ('AC005','Melba',10000);
    1 row deleted.
    1 row deleted.
    1 row deleted.
    1 row deleted.
    1 row deleted.

    SQL> SELECT * FROM accounts3;


    ACCOUNT_ID NAME BAL
    ------------ -------------------- ----------
    AC001 Anuj 5000
    AC002 Robert 10000
    AC003 Mita 3000
    AC004 Sunita 15000
    AC005 Melba 10000

    SQL> set serveroutput on;


    SQL> declare
    2 acct_balance number(7,2);
    3 acct_no varchar2(6);
    4 debit_amt number(7,2):=2000.00;
    5 min_bal constant number(7,2):=500.00;
    6 Begin
    7 acct_no:=&acct_no;
    8 select bal into acct_balance
    9 from accounts3
    10 where account_id=acct_no;
    11 acct_balance:=acct_balance-debit_amt;
    12 if acct_balance>=min_bal then
    13 update accounts3 set bal=bal-debit_amt
    14 where account_id=acct_no;
    15 end if;
    16 end;
    17 /

    Enter value for acct_no: 'AC003'


    old 7: acct_no:=&acct_no;
    new 7: acct_no:='AC003';

    PL/SQL procedure successfully completed.

    SQL> SELECT * FROM accounts3;

    ACCOUNT_ID NAME BAL


    ------------ -------------------- ----------
    AC001 Anuj 5000
    AC002 Robert 10000
    AC003 Mita 3000
    AC004 Sunita 15000
    AC005 Melba 10000

    Q.2 Write a PL/SQL code block to calculate the area of circle for a value of radius from 3 to
    7 store the radius and the corresponding values of calculated.

    SQL>CREATE TABLE areas(Radius number(5), area number(14,2));


    Table created.

    SQL> set serveroutput on;


    SQL> declare
    2 pi constant number(4,2):=3.14;
    3 radius number(5);
    4 area number(14,2);
    5 begin
    6 radius :=3;
    7 while radius <=10
    8 loop
    9 area := pi*power(radius,2);
    10 insert into areas
    11 values(radius,area);
    12 radius := radius+1;
    13 end loop;
    14 end;
    15 /

    PL/SQL procedure successfully completed.

    SQL> select * from areas;

    RADIUS AREA
    ---------- ----------
    3 28.26
    4 50.24
    5 78.5
    6 113.04
    7 153.86
    8 200.96
    9 254.34
    10 314

    8 rows selected.

    Q.3 Write a PL/SQL code block for inverting a number ‘102345’ and string ‘AMITY’.

    // Reverse number 102345.

    SQL> set serveroutput on;

    SQL> declare
    2 num varchar(6):='102345';
    3 len number(2);
    4 rev varchar(6);
    5 begin
    6 len:=length(num);
    7 for cntr in reverse 1..len
    8 loop
    9 rev:=rev||substr(num,cntr,1);
    10 end loop;
    11 dbms_output.put_line('The Given Number is'||num);
    12 dbms_output.put_line('The Inverted Number is'||rev);
    13 end;
    14 /

    The Given Number is102345


    The Inverted Number is543201
    PL/SQL procedure successfully completed.

    // program to invert string ‘AMITY’.

    SQL> set serveroutput on;

    SQL> declare
    2 given_str varchar(5):='AMITY';
    3 str_length number(2);
    4 inv_str varchar(5);
    5 begin
    6 str_length := length(given_str);
    7 for cntr IN REVERSE 1..str_length
    8 loop
    9 inv_str := inv_str || substr(given_str,cntr,1);
    10 end loop;
    11 dbms_output.put_line('The given String is' || given_str);
    12 dbms_output.put_line('The inverted String is ' || inv_str);
    13 end;
    14 /

    The given String is AMITY


    The inverted String is YTIMA

    PL/SQL procedure successfully completed.


    Syntax:-IF < Condition > THEN< Action >ELSIF < Condition > THEN< Action >ELSE<
    Action >END IF;

    Example 1:Write a PL/SQL code block that will accept an account number from the user
    and debit an amount of Rs. 2000 from the account if the account has a minimum balance
    of 500 after the amount is debited. The process is to be fired on the Accounts Table.

    SQL> SELECT * FROM Accounts;

    ACCOUNT_IDNAME BALANCE
    ---------------------- ------------- ----------------
    AC001 Anuj 5000
    AC002 Robe 10000
    AC003 Mita 5000
    AC004 Sunita 15000
    AC005 Melba 10000
    5 rows selected.
    SQL> SET SERVEROUTPUT ON;
    SQL> DECLARE2 /* Declaration of Memory Variables and Constants to be used in the3
    Executable section. */4 5 Acct_Balance number(11,2);6 Acct_No varchar2(6);7
    Acct_Name varchar2(20);8 Debit_Amt number(5) := 2000;9 Min_Bal CONSTANT
    number(5,2) := 500.00;10 11 BEGINPAGE PAGE 2323 OF OF 72

    SQL & PL/SQL Commands12 13 /* Accept an Account No from the User */14 15 Acct_No
    := &Acct_No;16 17 /* Retrieving the Balance from the Accounts Table where Account18
    No in the Table is Equal to the Account No entered by the User. */19 20 SELECT Name,
    Balance INTO Acct_Name, Acct_Balance21 FROM Accounts22 WHERE Account_Id =
    Acct_No;23 24 DBMS_OUTPUT.PUT_LINE('Existing Record');25
    DBMS_OUTPUT.PUT_LINE('Account Number: ' || Acct_No);26
    DBMS_OUTPUT.PUT_LINE('Account Holder Name: ' || Acct_Name);27
    DBMS_OUTPUT.PUT_LINE('Balance IN Account: ' || Acct_Balance);28 29 /* Subtract an
    Amount of Rs. 2000 from the Balance retrieved from 30 the table */31 32 Acct_Balance
    := Acct_Balance - Debit_Amt;33 34 /* Checking if the Resultant Balance is greater than
    or equal to the35 minimum balance of Rs. 500. If the condition is satisfied an amount 36
    of Rs. 2000 is Subtracted from the Balance of the corresponding37 Account No. */38 39
    IF Acct_Balance >= Min_Bal THEN40 UPDATE Accounts SET Balance = Balance -
    Debit_Amt41 WHERE Account_Id = Acct_No;42 END IF;43 44
    DBMS_OUTPUT.PUT_LINE('After Updating Record');45
    DBMS_OUTPUT.PUT_LINE('Account Number: ' || Acct_No);46
    DBMS_OUTPUT.PUT_LINE('Account Holder Name: ' || Acct_Name);47
    DBMS_OUTPUT.PUT_LINE('Balance IN Account: ' || Acct_Balance);48 49 END;

    You might also like