Create and test a simple procedure named check_available_credit that accepts a c

Question

Create and test a simple procedure named check_available_credit that accepts a customer id and a purchase amount as input. This procedure will then compare the purchase amount to the credit limit of the specified customer in the demo_customer table. If the credit limit is high enough for the customer to make the purchase, the procedure will display the message “Credit approved for customer# nnnnn.” Otherwise display “Credit denied for customer# nnnnn.” Practice calling the procedure for various customers and purchase amounts. Include proof of your tests in your submitted lab document

Explanation / Answer

Query to create table

create table demo_customer(id number(10) primary key,name varchar2(20),cbal number(10,2));

Query to insert into data into table

insert into demo_customer(id,name,cbal)values(111,'sham',50000);
insert into demo_customer(id,name,cbal)values(222,'tony',25000);
insert into demo_customer(id,name,cbal)values(333,'greg',10000);

Procedure

CREATE OR REPLACE CHECK_AVAILABLE_CREDIT
(
CUSTID IN NUMBER
, PURAMT IN NUMBER
)
AS
CREDITBAL NUMBER;
SET SERVEROUT ON;
BEGIN
SELECT CBAL
INTO CREDITBAL
FROM DEMO_CUSTOMER
WHERE ID=CUSTID;

BEGIN
IF(CREDITBAL>PURAMT)
THEN
DBMS_OUTPUT.PUTLINE('Credit approved for customer');
ELSE
DBMS_OUTPUT.PUTLINE('Credit denied for customer');
END IF;

END CHECK_AVAILABLE_CREDIT;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.