PL_SQL

1
PL_SQL

• Explanation of DISPCUST.SQL

2
1 of 3

• DECLARE
• cnum builder.customer.customer_idtype
• cname builder.customer.customer_nametype
• caddr builder.customer.customer_addresstype
• status boolean
• procedure get_cust_details (cust_no in
  builder.customer.customer_idtype,cust_name out
  builder.customer.customer_nametype,cust_addr out
  builder.customer.customer_addresstype,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.custom
  er_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
  customers 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

• begincnum1get_cust_details(cnum,cname,
  caddr, status)dbms_output.enableif (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.