Using Relational Databases and SQL

1
Using Relational Databases and SQL
Lecture 10 Advanced Topics

• Steven Emory
• Department of Computer Science
• California State University, Los Angeles

2
Topics for Today

• Stored Functions (SQL-Invoked Routines)
• Stored Procedures (SQL-Invoked Routines)
• Final Review
• This Thursday
• MySQL Connectors
• Database-Enabled Applications
• More Final Review

3
Stored Functions

• Simple way to write your own functions
• Functions must return a single value
• Stored functions are defined in database schema
• Not global, must be associated with a database
• Can use SQL
• Drawbacks
• Cant define your own aggregates
• For custom aggregates, use C/C to make
  UDFs(user-defined functions).

4
Stored Functions

• Single Statement Syntax
• CREATE FUNCTIONname(varname type, varname type,
  varname type, ...)RETURNS data_typeRETURN
  single_query single_command
• Compound Statement Syntax
• CREATE FUNCTIONname(varname type, varname type,
  varname type, ...)RETURNS data_typeBEGINcompoun
  d_statementsEND

5
Stored Functions

• Drop Function Syntax
• DROP FUNCTION IF EXISTS name

6
Stored Functions

• Using compound table syntax, you can
• declare variables
• initialize variables
• use conditional statements
• use loop statements
• use subqueries
• Statements must be terminated by a semicolon
• Statements must occur between BEGIN and END

7
Stored Functions

• To declare a variable
• DECLARE name data_type DEFAULT expression
• To initialize a variable
• SET name expression subquery
• To return a value
• RETURN expression subquery

8
Stored Functions

• To define an IF statement
• IF expression THEN statement_listELSEIF
  expression THEN statement_listELSE
  statement_listENDIF

9
Stored Functions

• To define a LOOP statement
• begin_label LOOPstatement_list END LOOP
  end_label
• LOOP is similar to for() in C and Java
• ITERATE is similar to continue in C and Java
• LEAVE is similar to break in C and Java

10
Stored Functions

• Example 1 Write a function to solve the
  quadratic equation. Functions parameters should
  be a, b, and c for the constants and s 1, 2
  for the first or second solution.
• CREATE FUNCTIONQUADRATIC_V1(a REAL, b REAL, c
  REAL, r INT)RETURNS REALRETURN IF(r 1, (-b
  SQRT(bb - 4ac))/(2a), (-b - SQRT(bb -
  4ac))/(2a))

11
Stored Functions

• Example 2 Rewrite the previous example using
  the compound-statement syntax.
• CREATE FUNCTIONQUADRATIC_V2(a REAL, b REAL, c
  REAL, r INT)RETURNS REAL BEGINDECLARE denom,
  discr REALSET discr SQRT(bb - 4ac)SET
  denom 2aRETURN IF(r 1, (-b
  discr)/(denom), (-b - discr)/(denom))END

12
Stored Functions

• Example 3 Write a stored function that computes
  the number of occurrences of a certain character
  within a string.
• CREATE FUNCTIONOCCURRENCE(s VARCHAR(256), c
  CHAR(1))RETURNS INTBEGINDECLARE retval INT
  DEFAULT 0-- A BUNCH OF STUFF GOES HERERETURN
  retvalEND

13
Stored Functions

• Solution
• CREATE FUNCTIONOCCURRENCE(s VARCHAR(256), c
  CHAR(1))RETURNS INT BEGINDECLARE retval INT
  DEFAULT 0DECLARE i INT DEFAULT 1loop_label
  LOOP IF i gt CHAR_LENGTH(s) THEN LEAVE
  loop_label END IF IF SUBSTRING(s, i, 1) c
  THEN SET retval retval 1 END IF SET i i
  1END LOOPRETURN retval END

14
Stored Procedures

• A stored procedure is a stored function that does
  not return a value
• Stored procedures are defined in database schema
• Not global, must be associated with a database
• Can use SQL
• Provides an extra layer of security
• If only stored procedures are allowed (no
  selection queries or commands), what you can do
  to the database is limited by the procedures the
  database supports

15
Stored Procedures

• Syntax is the same as stored functions, with two
  exceptions
• No RETURNS and RETURN statements
• You cannot use a stored procedure in a selection
  query, you must use the CALL keyword
• CALL Syntax
• CALL stored_procedure_name(param1, param2, ...)

16
Stored Procedures

• Example Write a stored procedure for the toy
  store database that inserts a new genre into the
  database.
• CREATE PROCEDUREADD_GENRE(g VARCHAR(32))BEGIN
  INSERT INTO genre VALUES(0, g)END