CS计算机代考程序代写 SQL c/c++ database Java Outline

Outline

2

● Functions and Procedural Constructs

● Triggers

● Recursive Queries

● Advanced Aggregation Features

What are they?

• Database objects which contain a

set of SQL statements to complete a

task.

• Can be written in: SQL itself, or in an

external programming language (e.g.,

C, Java).

Stored Procedures and Functions

Stored Procedures and Functions

Why do we need them?

To remove the repetition

To make the code easier

to maintain and facilitate

data abstraction

To access general

purpose programming

languages

Stored Procedures and Functions

Why do we need them?

○ Functions written in an external languages are particularly

useful with specialized data types such as images and

geometric objects.

■ Example: functions to check if polygons overlap, or

to compare images for similarity.

Stored Procedures and Functions

Why do we need them?

● To leverage SQL’s rich set of imperative constructs,

including loops, if-then-else, assignment

● Some database systems support table-valued
functions, which can return a table as a result.

SQL Functions

7

● Compound statement: begin … end
○ May contain multiple SQL statements between begin and end.

● returns –indicates the variable-type that is returned (e.g., integer)

● return –specifies the values that are to be returned as result of
invoking the function

● SQL functions are in fact parameterized views that generalize the
regular notion of views by allowing parameters.

PostgreSQL Functions

https://www.postgresql.org/docs/9.4/sql-createfunction.html

https://www.postgresql.org/docs/9.4/sql-createfunction.html

Function Example

8

—Create functions

CREATE FUNCTION add_em (IN x int, IN y int) RETURNS integer
AS ‘SELECT $1 + $2’

LANGUAGE SQL;

—Using a function

SELECT add_em(3,7);

Trivial examples to help you get started.

https://www.dbrnd.com/2016/09/postgresql-what-are-the-differences-
between-sql-and-plpgsql-language-in-function/

PostgreSQL: What are the Differences between SQL and PL/pgSQL language in Function

Create SQL Functions

9

Define a function that, given the name of a department, returns the

count of the number of instructors in that department.

In PostgreSQL:

CREATE OR REPLACE FUNCTION dept_count (dept_name
varchar(20)) RETURNS integer AS
$func$

DECLARE d_count integer;
BEGIN

SELECT COUNT (*) INTO d_count
FROM instructor
WHERE instructor.dept_name = $1;
RETURN d_count;

END
$func$

LANGUAGE plpgsql;

Use SQL Functions

1
0

● Find out the department names and budget of all departments with

more that 2 instructors using dept_count() function.

SELECT dept_name, budget
FROM department
WHERE dept_count (dept_name) > 2;

9

Create Table Function
SQL:2003 added functions that return a relation as a result

In PostgreSQL: Example: Return all instructors in a given department
CREATE FUNCTION instructor_of (department_name char(20))

RETURNS TABLE (
id varchar(5),
name varchar(20),
dept_name varchar(20),
salary numeric(8,2)) AS

$$
BEGIN

RETURN QUERY
SELECT i.id, i.name, i.dept_name, i.salary
FROM instructor i
WHERE i.dept_name = instructor_of.department_name;

END
$$

LANGUAGE plpgsql;

SELECT instructor_of (‘Physics’);

Use Table Function

Classwork-1: Functions

Create a function course_count to display # of courses taken by

a student

Use the function to find the students who have taken more than

2 courses

PGSQL difference between stored procedures and functions

In PostgreSQL, both stored procedures and user-
defined functions are created with CREATE FUNCTION
statement.

There are differences between the notion of stored
procedures and functions in database systems. So in
most cases, the purpose of a stored procedure is to:

•Perform actions without returning any result (INSERT,
UPDATE operations i.e.)
•Return one or more scalar values as OUT parameters
•Return one or more result sets

PGSQL difference between stored procedures and functions

Stored
Procedure

Function

Use in an
expression

No Yes

Return a value No Yes

Return values as
OUT parameters

Yes No

Return a single
result set

Yes
Yes (as a table

function)

Return multiple
result sets

Yes No

SQL Procedures

16

CREATE TABLE tbl(va integer);

CREATE PROCEDURE insert_data(a

integer, b integer)

LANGUAGE SQL

AS

$$

INSERT INTO tbl VALUES (a);

INSERT INTO tbl VALUES (b);

$$;

CALL insert_data(1, 2);

SELECT * FROM tbl

Trivial examples to help you get started.

SQL Procedures

17

The dept_count function could instead be written as procedure:

CREATE OR REPLACE PROCEDURE dept_count_proc (dept_name

VARCHAR(20))

LANGUAGE ‘plpgsql’

AS $$

DECLARE countd INT;

BEGIN

SELECT count(*) INTO countd

FROM instructor

WHERE instructor.dept_name = dept_count_proc.dept_name;

UPDATE dcount SET d_count = countd;

COMMIT;

END;

$$;

SQL Procedures (Cont.)

18

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

CALL dept_count_proc (‘Physics’);

SELECT * FROM d_count

Procedures and functions can be invoked also from dynamic SQL

● SQL:1999 allows more than one function/procedure of the same
name (called name overloading), as long as the number of
arguments differ, or at least the types of the arguments differ

Language Constructs for Procedures & Functions

19

● SQL supports constructs that gives it almost all the power of a

general-purpose programming language.

○ Warning: most database systems implement their own variant

of the standard syntax below.

● Compound statement: begin … end,
○ May contain multiple SQL statements between begin and end.
○ Local variables can be declared within a compound statements

Language Constructs for Procedures & Functions (Cont.)

20

● While and repeat statements:
○ while boolean expression do

sequence of statements ;
end while

○ repeat
sequence of statements ;

until boolean expression
end repeat

Language Constructs (Cont.)

21

● For loop
○ Permits iteration over all results of a query

● Example: Find the budget of all departments

declare n integer default 0;
for r as

select budget from department
do

set n = n + r.budget
end for

Language Constructs (Cont.)

22

● Conditional statements (if-then-else)
SQL:1999 also supports a CASE statement similar to C/C++ case
statement

● Example procedure: registers student after ensuring classroom

capacity is not exceeded

○ Returns 0 on success and -1 if capacity is exceeded

○ See book (page 177) for details

Triggers

Triggers

24

● Statement that is executed automatically by the system as

a side effect of a modification to the database.

● To design a trigger mechanism, we must:

○ Specify the conditions under which the trigger is to be executed.

○ Specify the actions to be taken when the trigger executes.

● Triggers introduced to SQL standard in SQL:1999, but supported

even earlier using non-standard syntax by most databases.
○ Syntax illustrated here may not work exactly on your database

system; check the system manuals

Triggering Events and Actions in SQL

25

● Triggering event can be insert, delete or update

● Triggers on update can be restricted to specific attributes

○ For example, after update of takes on grade

● Values of attributes before and after an update can be referenced
○ referencing old row as: for deletes and updates
○ referencing new row as: for inserts and updates

Trigger: PostgreSQL

• To create a new trigger in PostgreSQL, you need to follow
these steps:

•First, create a trigger function using CREATE
FUNCTION statement.

•Second, bind the trigger function to a table by using CREATE
TRIGGER statement.

http://www.postgresqltutorial.com/postgresql-create-function/

Classwork: Triggering Events and Actions in SQL (Cont.)

27

● Triggers can be activated before an event, which can serve as extra

constraints. For example, create a trigger to raise an error message

when grade is entered as empty.

create trigger raiseerror_trigger before update of

takes referencing new row as nrow

for each row

when (nrow.grade = ‘ ‘)

Raise an error ‘Empty

String is not

allowed…’

Classwork Answer

CREATE OR REPLACE FUNCTION raiseerror_trigger ()

RETURNS trigger AS

$body$

BEGIN

IF NEW.grade = ‘ ‘

THEN

RAISE EXCEPTION ‘Emptry string is not allowed…’;

END IF;

RETURN NEW;

END;

$body$

LANGUAGE plpgsql;

CREATE TRIGGER raiseerror_trigger

BEFORE UPDATE ON takes

FOR EACH ROW EXECUTE PROCEDURE raiseerror_trigger();

UPDATE takes

SET grade = ‘ ‘

where ID = ‘98988’ and course_id = ‘BIO-301’;

Statement Level Triggers

30

● Instead of executing a separate action for each affected row, a

single action can be executed for all rows affected by a transaction

○ Use

○ Use

for each statement
referencing old table

instead of for each row
or referencing new table to refer

to temporary tables (called transition tables) containing the
affected rows

○ Can be more efficient when dealing with SQL statements that

update a large number of rows

When Not To Use Triggers

31

● Triggers were used earlier for tasks such as

○ Maintaining summary data (e.g., total salary of each

department)

○ Replicating databases by recording changes to special relations
(called change or delta relations) and having a separate process
that applies the changes over to a replica

● There are better ways of doing these now:

○ Databases today provide built in materialized view facilities to

maintain summary data

○ Databases provide built-in support for replication

When Not To Use Triggers (Cont.)

32

● Encapsulation facilities can be used instead of triggers in many

cases

○ Define methods to update fields

○ Carry out actions as part of the update methods instead of

through a trigger

When Not To Use Triggers (Cont.)

33

● Risk of unintended execution of triggers, for example, when

○ Loading data from a backup copy

○ Replicating updates at a remote site

○ Trigger execution can be disabled before such actions.

● Other risks with triggers:

○ Error leading to failure of critical transactions that set off the

trigger

○ Cascading execution

Recursive Queries

Recursion in SQL

SQL:1999 permits recursive view definition

Example: find which courses are a prerequisite, whether directly or indirectly, for a

specific course. This example view, rec_prereq, is called the transitive closure of

the prereq relation

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;

The Power of Recursion

• Recursive views make it possible to write queries, such as transitive closure

queries, that cannot be written without recursion or iteration.

• Without recursion, a non-recursive non-iterative program can perform only a

fixed number of joins of prereq with itself

Advanced Aggregation
Features

Ranking

38

● Ranking is done in conjunction with an order by specification.

● Suppose we are given a relation

student_grades (ID, GPA)

giving the grade-point average of each student

● Find the rank of each student:

● An extra order by clause is needed to get them in sorted order:

SELECT ID, RANK() OVER (ORDER BY GPA DESC) AS s_rank
FROM student_grades;

SELECT ID, RANK() OVER (ORDER BY GPA DESC) AS s_rank
FROM student_grades
ORDER BY s_rank;

Ranking (cont.)

39

● Ranking may leave gaps: e.g. if 2 students have the same top GPA,

both have rank 1, and the next rank is 3
○ dense_rank does not leave gaps, so next dense rank would be 2

● Ranking can be done using basic SQL aggregation, but resultant

query is very inefficient.

Example:

SELECT ID, (1 + (SELECT COUNT(*)
FROM student_grades B
WHERE B.GPA > A.GPA)) AS s_rank

FROM student_grades A
ORDER BY s_rank;

Ranking (cont.)

40

● For a given constant n, the ranking the function ntile(n) takes the
tuples in each partition in the specified order, and divides them into

n buckets with equal numbers of tuples.

● Example:

SELECT ID, NTILE(4) OVER (ORDER BY GPA DESC) AS quartile
FROM student_grades;

Ranking (cont.)

41

● Ranking can be done within partition of the data.

Example: “Find the rank of students within each department.”

● Multiple rank clauses can occur in a single select clause.

● Ranking is done after applying group by clause/aggregation

● Can be used to find top-n results
○ More general than the limit n clause supported by many

databases, since it allows top-n within each partition

SELECT ID, dept_name,
RANK() OVER (PARTITION BY dept_name ORDER BY GPA DESC)

AS dept_rank
FROM dept_grades
ORDER BY dept_name, dept_rank;

Ranking (cont.)

42

● Other ranking functions:
○ percent_rank (within partition, if partitioning is done)
○ cume_dist (cumulative distribution)

■ fraction of tuples with preceding values
○ row_number (non-deterministic in presence of duplicates)

● SQL:1999 permits the user to specify nulls first or nulls last

SELECT ID, RANK() OVER (ORDER BY GPA DESC NULLS LAST) AS s_rank
FROM student_grades;

Window Functions

43

Basic window function syntax (in PostgreSQL):

● function_name can be any built-in or user-defined aggregate
function

● PARTITION_BY groups the rows into groups, if not used the entire
result set is a single partition

● ORDER_BY sort rows within apartition

SELECT function_name(arg1, arg2, …) OVER
(PARTITION BY expression ORDER BY expression)

FROM relation;

https://www.postgresql.org/docs/9.5/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS
https://www.postgresql.org/docs/9.6/functions-window.html

Window Functions (cont.)

44

● Used to smooth out random variations.

● E.g., moving average: “Given sales values for each date, calculate
for each date the average of the sales on that day, the previous day,

and the next day”

● Window specification in SQL:
○ Given the relation: sales (date, value)

SELECT date, AVG(value) OVER
(ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

FROM sales;

35

Window Functions (cont.)

● Can do windowing within partitions

● E.g., Given a relation, transaction (account_number, date_time,
value), where value is positive for a deposit and negative for a
withdrawal,

○ “Calculate the total balance of each account after each transaction

on the account”

SELECT account_number, date_time,
SUM(value) OVER

(PARTITION BY account_number
ORDER BY date_time
ROWS UNBOUNDED PRECEDING)

AS balance
FROM transaction

ORDER BY account_number, date_time;