CS-GY 6083: Principles of Database Systems
1.0.0
1.0.0
  • Introduction
  • DBMS Basics
    • Introduction to DBMS
    • Why use a DBMS instead of a File System?
    • Levels of Abstraction
    • Instances and Schemas
  • Data Models
    • Introduction to Data Models
    • Database Languages
    • Database Design
  • DBMS Internals
    • Introduction to DBMS Internals
    • Storage Manager
    • Query Processor
    • Transaction Management
    • Database Users
    • Database Architecture
  • DBMS History
  • Some Popular Database Systems
  • OLTP, OLAP, and Data Mining
  • Databases vs. Information Retrieval
  • The Entity-Relationship Model - Details
    • Introduction
    • Cardinality Constraints
    • ER Diagram Components
    • ER Diagram to Relational Schema
    • Design Issues
  • The Relational Model - Details
    • Relations
    • Keys
    • Relational Query Languages
      • Relational Algebra
      • Relational Calculus
      • Relative Expressive Power
    • Relational Operators
  • SQL
    • Introduction to SQL
    • Domain Types in SQL
    • DDL Commands
      • Creating a Table
      • Alter and Drop
    • DML Commands
      • Basic Query Structure
      • Select
      • From
      • Where
      • Joins
      • Rename
      • String Operations
      • Ordering
      • Set Operations
      • Group By and Having
      • Nested Subqueries
      • Test for Empty Relations
      • Test for Duplicate Tuples
      • Derived Relations
      • With
      • Database Modification
    • Intermediate SQL
      • Joins Revisited
      • Views
      • Transactions
      • Integrity Constraints
      • More SQL Data Types and Schemas
        • Other Features
      • Authorization
    • Advanced SQL
      • Accessing SQL From a Programming Language
        • ODBC and JDBC
        • Embedded SQL
        • PHP
        • Some Security Issues
      • Accessing Metadata
      • Text Operations
        • Like
        • Contains
      • Cursors
      • Functions and Procedures
        • Procedural Constructs
        • External Language Routines
      • Triggers
      • Ranking
      • Windowing
      • OLAP
Powered by GitBook
On this page

Was this helpful?

  1. SQL
  2. Advanced SQL

Functions and Procedures

We can store procedures in the database and execute them using a call statement.

This allows external applications to operate on the database without knowing about the internal details.

Functions/procedures can be written in SQL itself, or in an external programming language.

SQL also supports a rich set of imperative constructs, including loops, if-then-else, assignment etc.

For example: Define a function that, given the name of a department, returns the count of the number of instructors in that department:

create function dept_count (dept_name varchar(20))
returns integer
begin
    declare d_count integer;
    select count (* ) into d_count
    from instructor
    where instructor.dept_name = dept_name
    return d_count;
end

Now, we can use the above function to find the department name and budget of all departments with more that 12 instructors:

select dept_name, budget
from department
where dept_count (dept_name ) > 12

The dept_count function could instead be written as procedure:

create procedure dept_count_proc (in dept_name varchar(20), out d_count integer)
begin
    select count(*) into d_count
    from instructor
    where instructor.dept_name = dept_count_proc.dept_name
end

Procedures can be invoked either from an SQL procedure or from embedded SQL, using the call statement:

declare d_count integer;
call dept_count_proc( 'Physics', d_count);

We can have more than one function/procedure with the same name (overloading), as long as the number/types of arguments is different.

Table Functions

These functions return a relation as a result.

For example: Return all instructors from a given department

create function instructors_of (dept_name char(20)
returns table ( ID varchar(5), name varchar(20), dept_name varchar(20), salary numeric(8,2))
return table
(select ID, name, dept_name, salary
 from instructor
 where instructor.dept_name = instructors_of.dept_name)

Usage: select * from table (instructors_of (‘Music’))

PreviousCursorsNextProcedural Constructs

Last updated 5 years ago

Was this helpful?