1 Create a stored procedure named insertglaccount that lets

1. Create a stored procedure named insert_glaccount that lets a user add a new row to the General_Ledger_Accounts table in the AP schema. This procedure should have two parameters, one for each of the two columns in this table. Then, code a CALL statement that tests the procedure. (Note that this table doesn’t allow duplicate account descriptions.)

2. Code a script that calls the procedure that you created in exercise 1 and passes the parameters by name. This procedure should provide exception handling that displays this message if the INSERT statement fails because the account number or account description is already in the table (a DUP_VAL_ON_INDEX error):

A DUP_VAL_ON_INDEX error occurred.

It should provide this message if any other type of error occurs:

An unknown exception occurred.

3. Code a function named test_glaccounts_description that accepts one parameter that tests whether an account description is already in the General_Ledger_Accounts table. This function should return a value of 1 if the account description is in the table or zero if it isn’t. (Note: If a SELECT statement doesn’t return any data, it throws a NO_DATA_FOUND exception that your function can handle.)

4. Code a script that calls the function that you created in exercise 1. This script should display this message if the account description is in the table:

Account description is already in use.

It should provide this message if any other type of error occurs:

Account description is available.

5. Modify the stored procedure that you created in exercise 1 and save it as insert_glaccount_with_test. This procedure should use the function that you created in step 3 to test whether the account description is going to be okay before it issues the INSERT statement. If the account description is a duplicate, this procedure should raise an application error with -20002 as the error number and an error message of

Duplicate account description

6. Modify the script that you created in step 2 so it uses the procedure of exercise 5. This script should use the SQLERRM function to display the error number and message for the application error if the account description is already in use.

Solution

1. Create a stored procedure named insert_glaccount that lets a user add a new row to the General_Ledger_Accounts table in the AP schema. This procedure should have two parameters, one for each of the two columns in this table. Then, code a CALL statement that tests the procedure.

Answer :-

Step 1 : first we can create General_Ledger_Accounts table column details

CREATE TABLE General_Ledger_Accounts (
id INTEGER NOT NULL PRIMARY KEY,
first_name VARCHAR(10),
last_name VARCHAR(10),
APschema VARCHAR(10),
)

Step 2 : we can create insert procedute .
create PROCEDURE insert_glaccount
(
@id INTEGER,
@first_name VARCHAR(10),
@last_name VARCHAR(10),
@APschema VARCHAR(10)
)

AS
BEGIN
IF @StatementType = \'Insert\'
BEGIN
insert into employee (id,first_name,last_name,APschema )values( @id, @first_name, @last_name, @APschema)   
END