Prerequisites – PL/SQL Introduction, PL/SQL | User Input
Write a PL/SQL code that will accept an account number from user. Check if user balance is less than minimum balance than only deduct Rs 100 from balance. The process is fired on the acct_mstr.
Example – Consider the following scenario,
acct_master (acct_no number(5) primary key, acct_name varchar2(10), balance number(10));
First, you need to create table acct_master,
# CREATING table acct_master create table acct_master(acct_no number(5) primary key, acct_name varchar2(10), balance number(10));
Insert these data into the table,
# INSERTING data in acct_mstr insert into acct_master values(1, 'aaa', 1000) insert into acct_master values(2, 'bbb', 100) insert into acct_master values(3, 'ccc', 1100) insert into acct_master values(4, 'ddd', 700) insert into acct_master values(5, 'eee', 1700)
Approach used –
- Step-1: Declare variables and set minimum balance in one of variable.
- Step-2: Take account number input from user.
- Step-3: Select the balance of that user into one variable.
- Step-4: Check if balance is less than minimum balance or not.
- Step-5: If balance is less, then update the balance in table with balance = balance -100, and display the balance after deducting 100 from that variable in which balance was stored. Then, display the output.
- Step-6: Else simply print the value.
Note that all text in green colour are comments.
Below is the required implementation:
Enter value for xacct_no: 2 old 6: xacct_no:=&xacct_no; new 6: xacct_no:=2; Rs 100 is deducted and current balance is 0 PL/SQL procedure successfully completed. SQL> / Enter value for xacct_no: 3 old 6: xacct_no:=&xacct_no; new 6: xacct_no:=3; Current balance is 1100 PL/SQL procedure successfully completed.
- SQL | TRANSACTIONS
- Sum of elements in an array with frequencies greater than or equal to that element
- How to rename columns in Pandas DataFrame
- Adding new column to existing DataFrame in Pandas
- Apply function to every row in a Pandas DataFrame
- Final keyword in PHP
- Check if the given decimal number has 0 and 1 digits only
- How to delete last element from a map in C++
- ArrayList iterator() method in Java with Examples
- How to align Placeholder Text in HTML ?
- How to create an unordered_map of pairs in C++?
- Range Sum Queries and Update with Square Root
- Puzzle | 1000 light bulbs switched on/off by 1000 persons passing by
- How to drop one or multiple columns in Pandas Dataframe
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.