Database Applications Overview and SQL Interfaces

Stored procedures in a database that perform specific tasks and return values.

Procedural language extension for SQL, mainly used for Oracle databases.

Stored blocks of code that perform specific tasks in a database.

ISO standard for persistent stored modules in SQL.

Determines how an argument is used within a function (IN, OUT, INOUT).

Arguments passed into a function, not modified there.

Arguments used to return values from a function.

Arguments used for both input and output.

The identifier of an argument.

Specifies the datatype of an argument.

Stored modules are predefined blocks of code that execute within a SQL environment.

Procedural language extension for PostgreSQL databases.

A function defined within a database system, usually to perform specific tasks. Stored functions reside in the database and can be called by other parts of the system.

A procedural language used in PostgreSQL databases to write stored functions. Similar to programming languages like Python or Java, but specifically designed for database tasks.

A function written in SQL and operated by the database. Includes set operations on tables, subqueries, and operators.

A block of code, containing the instructions on how to execute the function, with parameters or without.

Values passed into a function to perform an operation; they can be read but not modified inside the function.

Values returned by a function; calculated inside function and returned, can have multiple OUT parameters.

The result or output of a function. It can be a single value or multiple values, depending on how the function is written.

Choosing the programming language in which the function is written (e.g., SQL, PL/pgSQL, PL/Python).

SQL is a set-oriented language, while PL/pgSQL is procedural allowing more complex calculations, control flow (if..then..else) and variable use.

Functions that are stored in the database and can be called like normal functions.

Instructions for the database to perform operations (like selecting data or updating records).

A procedural language available in PostgreSQL that allows creating stored procedures and functions with more complex logic than just SQL alone.

A stored function that deletes graduated students from the database and returns the count of remaining students.

The result produced by a function. Can be a simple value or a more complex object.

SQL statements that are generated at runtime, rather than being hardcoded.

A security vulnerability where malicious SQL code is inserted into an application's input parameters.

CREATE FUNCTION function_name() RETURNS data_type AS $$ SQL queries $$ LANGUAGE sql;

A function to retrieve student having maximum points in database (student table).

A SQL function that selects the student with the highest points from the 'students' table.

Indicates that the function returns a single row from the 'students' table.

Defines a function that produces a set of rows from the 'students' table.

An argument used to return values from a function.

A function that returns the highest points and the count of students with that score.

Allows the return of multiple values/columns in a function.

In a 'SELECT' statement, it restricts to only one row matching the selection criteria.

The code will give an error because 'students' is not a 'return' type.

A function defined within a database that performs specific tasks and returns values.

The result or output provided by a function after executing its code.

A stored block of code that performs specific tasks in a database, but does not return a value.

Used to execute a stored procedure in a database.

Used in stored function definitions to specify what type of value is returned.

A situation where a stored function or procedure does not provide a return value.

SQL are set-oriented and functions allows using a more focused commands in the database.

Both are stored blocks of code in a database, but functions must return a value, while procedures don't need to.

The result a function produces. It can be a single value or multiple

Procedures can execute transactions (commit or rollback) in a database. Functions can't.

Functions are called by using them in SELECT statements

Procedures are called using a CALL statement as an isolated instruction

Procedures can use 'OUT' and 'INOUT' parameters to return values

Use functions to return values. This code style is usually preferred.