This function should return a value of 1 if the account description is in the

This function should return a value of 1 if the

This preview shows page 14 - 27 out of 33 pages.

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.) Hint: Exception error you need to catch is NO_DATA_FOUND Write a Select statement which searches for the Account description.
Image of page 14
CREATE OR REPLACE FUNCTION test_glaccounts_description ( account_description_param VARCHAR2 ) RETURN NUMBER AS duplicate_description_var NUMBER; BEGIN SELECT 1 INTO duplicate_description_var FROM general_ledger_accounts WHERE account_description = account_description_param; RETURN duplicate_description_var; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 0; END; /
Image of page 15
A procedure displayed in SQL Developer
Image of page 16
A procedure in the Edit window
Image of page 17
A breakpoint that has been set in SQL Developer Click the Debug button to start debugging…
Image of page 18
The dialog box for specifying input parameters
Image of page 19
A procedure that’s being stepped through
Image of page 20
TRIGGERS
Image of page 21
The syntax of the CREATE TRIGGER statement for a table CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE|AFTER} {DELETE|INSERT|UPDATE [OF col_name1 [, col_name2]...]} [OR {DELETE|INSERT|UPDATE[OF col_name1 [, col_name2]...]}]... ON table_name [FOR EACH ROW [WHEN (trigger_condition)]] pl_sql_block
Image of page 22
A trigger that corrects mixed-case state names CREATE OR REPLACE TRIGGER vendors_before_update_state BEFORE INSERT OR UPDATE OF vendor_state ON vendors FOR EACH ROW WHEN (NEW.vendor_state != UPPER(NEW.vendor_state)) BEGIN :NEW.vendor_state := UPPER(:NEW.vendor_state); END; /
Image of page 23
An UPDATE statement that fires the trigger UPDATE vendors SET vendor_state = 'wi' WHERE vendor_id = 1; A SELECT statement that shows the new row SELECT vendor_name, vendor_state FROM vendors WHERE vendor_id = 1; The result set
Image of page 24