Stored Procedure, Function, and Trigger

1
Chapter 7,8,9

• Stored Procedure, Function, and Trigger

2
Operations of Query

• A query is sent to the server
• The server compiles the SQL code
• The optimizer generates an execution plan for the
  query
• The database engine executes the query
• The results are sent back to the client

3
Procedure, Function, and Trigger

• Procedures, functions, and triggers can be
  compiled and then stored as database objects by
  the DBMS.
• A procedure is a named SQL block that performs a
  specific action
• Created once and executed many times
• Omitting compilation and optimization processes
• Centralized maintenance

4
1st Example

• Write a procedure to find out the suppliers
  coming from London but not supplying P1.

5
The Structure of a Procedure

• CREATE PROCEDURE ltproc-namegt
• (parameter-list)
• AS
• ltexecutable-sectiongt
• exception ltexception-sectiongt
• DROP PROCPROCEDURE ltproc-namegt

6

• CREATE PROCEDURE S_London_not_P1
• AS
• SELECT s.
• FROM s
• WHERE city'London'
• AND s.s NOT IN
• (SELECT s.s
• FROM s, sp
• WHERE s.ssp.s
• AND p 'P1')

7
How to Run the Procedure

• Execute the procedure
• exec S_London_not_P1

8
2nd Example

• Write a procedure to find out the suppliers
  coming from a given city but not supplying a
  given part.

9

• CREATE PROCEDURE S_city_not_Part
• _at_city varchar(20), _at_p char(2)
• AS
• SELECT s.
• FROM s
• WHERE city_at_city
• AND s.s NOT IN
• (SELECT s.s
• FROM s, sp
• WHERE s.ssp.s
• AND p _at_p)

10
How to Run the Procedure

• Execute the procedure
• exec S_city_not_Part Paris, P1

11
3rd Example

• Write a procedure to insert any given values of
  P, Pname, and color into P table.

12
CREATE PROCEDURE PartAdd _at_p char(2), _at_pname
varchar(30), _at_color varchar(30) AS Insert Into
p (p, pname, color) Values (_at_p, _at_pname,
_at_color) Execute the procedure exec PartAdd
(P10,Screw,Blue)
13
4th Example

• Write a procedure to find out the suppliers
  coming from a given city but not supplying a
  given part. Check those input values to see
  whether they are in the table.

14

• CREATE PROCEDURE S_city_not_Part
• _at_city varchar(20), _at_p char(2)
• AS
• DECLARE _at_flag NUMERIC
• Begin
• SET _at_flag (SELECT count()FROM s WHERE
  city_at_city)
• IF _at_flag lt 1
• Begin
• RAISERROR (' The name of the city was
  wrong!',16,1)
• Rollback Tran
• End
• SET _at_flag (SELECT count()FROM p WHERE p
  _at_p)
• IF _at_flag lt 1
• Begin
• RAISERROR (The part number was wrong!, 16,2)
• Rollback Tran
• End
• SELECT s.
• FROM s

15
5th Example

• Supplier A takes over the shipments of another
  supplier B.
• For each part supplied by B, if A did not supply
  it, generate a new record otherwise, sum the two
  quantities supplied by both suppliers up to A.
  The records for B should then be deleted.

16

• CREATE PROCEDURE take_over_shipment1
• _at_from_s VARCHAR(2),
• _at_to_s VARCHAR(2)
• AS
• DECLARE _at_flag NUMERIC, _at_p CHAR(2), _at_qty
  NUMERIC
• BEGIN
• -- Check whether from_supplier exists. If it
  doesn't, raise error message.
• SET _at_flag (SELECT count()FROM s WHERE s
  _at_from_s)
• IF _at_flag lt 1
• RAISERROR ('From supplier does not
  exist!',-20001,1)
• -- Check whether to_supplier exists. If it
  doesn't, raise error message.
• SET _at_flag (SELECT count()FROM s WHERE
  s _at_to_s)
• IF _at_flag lt 1
• RAISERROR ('To supplier does not exist!',
  -20002,2)
• -- Take over every shipment of from_supplier.

17

• DECLARE c1 CURSOR FOR select p, qty from sp
  where s_at_from_s
• OPEN c1
• FETCH c1 INTO _at_p, _at_qty
• -- Check whether to_supplier supplies the
  part
• WHILE _at__at_fetch_status0
• BEGIN
• SET _at_flag (SELECT count() FROM sp WHERE
  s _at_to_s AND p _at_p)
• -- If it doesn't, insert a new record,
  otherwise, update the qty of to_supplier.
• IF _at_flag lt 1
• INSERT INTO sp VALUES (_at_to_s,_at_p, _at_qty)
• ELSE
• UPDATE sp SET qtyqty_at_qty
• WHERE s_at_to_s AND p_at_p
• FETCH c1 INTO _at_p, _at_qty
• END
• CLOSE c1
• DEALLOCATE C1
• -- Delete the records of from_supplier
• DELETE sp WHERE s_at_from_s

18
Procedure, Function, and Trigger

• A function is a named block that computes a
  value.
• Being called anywhere,
• Built-in functions can be used.

19
1st Example

• Find out the shipment, shipping quantity,,
  average quantity, and quantity difference in the
  shipment table
• We can run
• Select s, p, (select avg(qty) from sp) as
  average,
• qty - (select avg(qty) from sp) as diff
• From sp

20
The Structure of a Function

• CREATE FUNCTION ltproc-namegt
• lt_at_parameter namegt ltscalar data typegt
• RETURNS ltscalar typegt
• AS
• BEGIN
• ltexecutable-sectiongt
• exception ltexception-sectiongt
• RETURN lttype as defined in RETURNS
  clausegtRETURN (ltSELECT statementgt)
• END

21

• Creating functions is another approach to
  calculate average price and price difference.
• Create function averageqty()
• Returns integer
• As
• Begin
• return (select avg(qty) from sp)
• End
• Create function qtydifference(_at_qty integer)
• Returns integer
• As
• Begin
• return _at_qty-dbo.averageqty()
• End
• This function calls the function above.

22
How to call the functions

• Select s, p, dbo.averageqty() as average,
  dbo.qtydifference(qty) as difference
• From sp

23
2nd Example

• Write a function to compute the total shipment
  quantity of a supplier.

24

• CREATE FUNCTION supplier_totqty (_at_supplier
  varchar(20))
• RETURNS NUMERIC
• AS
• BEGIN
• DECLARE _at_flag INT, _at_totqty NUMERIC
• -- Check whether the supplier exists. If it
  doesn't, raise error message.
• SET _at_flag (SELECT count() FROM s WHERE s
  _at_supplier)
• IF _at_flag lt 1
• SET _at_TOTQTY-1
• -- Check whether the supply anything. If it
  doesn't, totqty0. Otherwise, get the total
  quantity of the supplier
• SET _at_flag (SELECT count()FROM sp WHERE
  s _at_supplier)
• IF _at_flag lt 1
• SELECT _at_totqty 0
• ELSE
• SET _at_totqty (SELECT sum(qty)FROM sp WHERE s
  _at_supplier)
• -- Return the total quantity of the supplier
• return _at_totqty
• END

25
How to call the Function

• You can modify this example to call any functions
  you have created in the database.
• DECLARE _at_totqty NUMERIC
• set _at_totqtydbo.supplier_totqty('S1')
• print _at_totqty

26
Procedure, Function, and Trigger

• A trigger is a named block that will be
  automatically invoked (triggered) by the DBMS
  upon some specified condition to perform a
  specific action.
• A trigger is a special kind of stored procedure
  that responds to specific events.

27
A Trigger Example

• No suppliers with status less than 20 can supply
  any part in a quantity greater than 500.

28
A Trigger Example

• Recall we couldn't enforce this constraint when
  we create tables.
• Write a trigger to check this constraint when a
  new shipment is entered into the SP table.
• Inserted table (in the following example) has the
  same structure as SP table. All the input data
  are kept in inserted table before saved in SP
  permanently.

29

• CREATE TRIGGER insert_sp
• ON sp
• FOR INSERT, UPDATE
• AS
• DECLARE _at_status int, _at_qty numeric
• BEGIN
• -- Check if status lt 20 and qtygt500, then error.
• SET _at_status (SELECT status FROM s s,
  inserted i
• WHERE s.s i.s)
• SET _at_qty (SELECT qty FROM inserted i)
• IF _at_statuslt20 AND _at_qtygt500
• BEGIN
• RAISERROR('Constraint Violated!', 16,1)
• ROLLBACK TRAN
• END
• END

30
The Structure of a Trigger

• CREATEALTER TRIGGER lttrigger-namegt
• ON lttable-namegt
• FOR AFTERDELETE INSERT UPDATE
• AS
• BEGIN
• ltexecutable-sectiongt
• exception ltexception-sectiongt
• END
• DROP TRIGGER lttrigger-namegt

31
Invoke a Procedure, Function, or Trigger

• Procedure
• can be explicitly invoked (executed).
• EXEC take_over_shipment('S1', 'S3')
• Function
• can be used in a SQL statement anywhere SQL
  built-in functions can be used.
• SELECT s, supplier_totqty(s) totqty FROM s
• Trigger
• automatically invoked (triggered) by the DBMS
  upon some specified condition.
• INSERT INTO SP VALUES ('S5', 'P6', 600)