PL_SQL

1. PL_SQL Explanation of DISPCUST.SQL

2. 1 of 3 DECLARE cnum builder.customer.customer_id%type; cname builder.customer.customer_name%type; caddr builder.customer.customer_address%type; status boolean; procedure get_cust_details (cust_no in builder.customer.customer_id%type,cust_name out builder.customer.customer_name%type,cust_addr out builder.customer.customer_address%type,status out boolean) is

3. Part 1 – declarations Formal parameters: • The first three parameters take their type from the data definitions in the builder.customer table (i.e. the customer table in the builder schema). • Cust_no • This allocates a slot for the customer id to be passed into the procedure. • Cust_name • This allocates a slot for the customer name to be returned from the procedure, having been read from the customer table. • Cust_addr • This allocates a slot for the customer address to be returned from the procedure , having been read from the customer table. • Status • This returns a value to show how the procedure finished.

4. part 2 of 3 begin status := true; select builder.customer.customer_name, builder.customer.customer_address into cust_name, cust_addr from builder.customer where builder.customer.customer_id = cust_no Exception when no_data_found then dbms_output.put_line('ERROR'); status := false; end;

5. Part 2 – procedure content. • Initially, we assume the procedure will work. • Using the cust_no passed across, we select the customer’s name and address from the customer table. • If this works, the status is left as at ‘true’. • If it fails, it triggers an exception. The exception ‘ no_data_found’ then sets the status to ‘false’.

6. Part 3 of 3 begincnum:=1;get_cust_details(cnum,cname, caddr, status);dbms_output.enable;if (status) then dbms_output.put_line('status = true'); dbms_output.put('customer number ='); dbms_output.put_line (cnum || ' ' || cname || ' ' || caddr);else dbms_output.put_line('status = false'); dbms_output.put_line ('Customer ' || cnum || ' not found'); end if; end;

7. Part 3 – main program • We initialise the value of CNUM to 1 • We call cust_details, replacing the formal parameters as follows: • Cnum fills the slot cust_no • Cname takes its value from cust_name • Caddr takes its value from cust_addr • Status takes its value from status. • The procedure outputs the customer details in a concatenated string.

8. Exceptions • There can be several different types of exceptions. • The ‘catch-all’ exception handler is • When others then… • This neatly exits from any error condition.

9. Commit and Rollback • When updating, inserting or deleting, the user has the chance to commit data at certain checkpoints. • This allows the user to conduct quite a complex transaction, without fully saving data to the database. • If the transaction completes successfully, then the data can be COMMITted. • If the transaction fails, the user can initiate a ROLLBACK, which cancels everything since the last commit.

10. Builder schema tables

11. Update procedures • Write a procedure to increase the cost and retail price of all stock from a given supplier by x%. • Specification: • We need to know: • Who the supplier is • What the percentage is. • We need to change: • The unitprice and the unitcostprice for all stock supplied by that supplier. • We need to confirm: • That the procedure has completed successfully and COMMITed the data.