Advanced SQL

:material-circle-edit-outline: 约 133 个字 :fontawesome-solid-code: 92 行代码 :material-clock-time-two-outline: 预计阅读时间 2 分钟

JDBC and ODBC省略

Procedural Constructs in SQL

SQL Functions

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

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

Table Functions

create function instructors_of (dept_name char(20) )
    returns table ( ID varchar(5),
    name varchar(20),
    dept_name varchar(20),
    salary numeric(8,2))

select *
from table (instructors_of (‘Music’)

SQL Procedures

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

declare d_count integer;
call dept_count_proc( ‘Physics’, d_count);

Procedural Constructs

While and repeat statements :

declare n integer default 0;
while n < 10 do
    set n = n + 1
end while

repeat
    set n = n – 1
until n = 0
end repeat

For loop: Permits iteration over all results of a query

declare n integer default 0;
for r as
    select budget from department
    where dept_name = ‘Music’
do
    set n = n - r.budget
end for

Conditional statements (\(if-then-else\))

if boolean expression 
    then statement or compound statement 
elseif boolean expression 
    then statement or compound statement 
else statement or compound statement 
end if

Recursive Queries

find which courses are a prerequisite, whether directly or indirectly, for a specific course

with recursive rec_prereq(course_id, prereq_id) as (
        select course_id, prereq_id
        from prereq
    union
        select rec_prereq.course_id, prereq.prereq_id, 
        from rec_prereq, prereq
        where rec_prereq.prereq_id = prereq.course_id
)
select ∗
from rec_prereq;

This example view, rec_prereq, is called the transitive closure of the prereq relation

Triggers

A trigger is a statement that is executed automatically by the system as a side effect of a modification to the database.

• Trigger - ECA rule
  • E: Event （ insert, delete ，update）
  • C: Condition
  • A: Action

account_log(account, amount, datetime)

-- event
create trigger account_trigger after update of account on balance
referencing new row as nrow -- for deletes and updates
referencing old row as orow -- for inserts and updates

-- condition
for each row
when nrow.balance - orow.balance > =200000 or 
    orow.balance -nrow.balance >=50000

-- action
begin 
    insert into account_log values (nrow.account-number, 
        nrow.balance-orow.balance , current_time() )
end

Statement Level Triggers

• Use for each statement instead of for each row
• Use referencing old table or referencing new table to refer to temporary tables (called transition tables) containing the affected rows

create trigger grade_trigger after update of takes on grade
referencing new table as new_table
for each statement
when exists( select avg(grade)
    from new_table
    group by course_id, sec_id, semester, year
    having avg(grade)< 60 )
begin 
    rollback
end