Functions

• Definition: Functions are collections of SQL statements that complete a given task and return a single value.
• Purpose: Calculations, data transformations, and other operations that require a return result are all supported.
• Syntax:
  CREATE [OR REPLACE] FUNCTION function_name 
  [(parameter_name  type [, …])]
  // this statement  is must for functions 
  RETURN return_datatype  
  {IS | AS}
  BEGIN 
  // program code
  [EXCEPTION 
  exception_section;
  END [function_name];
• Example:
  create function MultiplyNumbers(@int1 as int,@int2 as int)
  As
  BEGIN
  Return (@int1 * @int2)
  end

Procedure

• Definition: Procedures (also known as stored procedures) are a collection of SQL statements that execute a sequence of operations but do not always return a value.
• Purpose: Used for data manipulation (INSERT, UPDATE, DELETE), complicated business logic, and batch processing.
• Syntax:
  CREATE or REPLACE PROCEDURE name(parameters)
  IS
  variables;
  BEGIN
  //statements;
  END;
• Example:
  CREATE or REPLACE PROCEDURE INC_SAL(eno IN NUMBER, up_sal OUT NUMBER)
  IS
  BEGIN
  UPDATE emp_table SET salary = salary+1000 WHERE emp_no = eno;
  COMMIT;
  SELECT sal INTO up_sal FROM emp_table WHERE emp_no = eno;
  END; 

Declaring and Revoking SQL Functions and Procedures

Declaring

• Functions:
  CREATE FUNCTION function_name (parameters)
  RETURNS return_datatype
  AS $$
  BEGIN
    -- Function body
    RETURN value;
  END;
  $$ LANGUAGE plpgsql;
• Procedures:
  CREATE PROCEDURE procedure_name (parameters)
  LANGUAGE plpgsql
  AS $$
  BEGIN
    -- Procedure body
  END;
  $$;

Revoking

• Functions:
  DROP FUNCTION IF EXISTS function_name(parameters);
• Procedures:
  DROP PROCEDURE IF EXISTS procedure_name(parameters);

Language Constraints for Procedures and Functions

• SQL/PLSQL: Procedures and functions are typically written in SQL or procedural languages such as PostgreSQL PL/pgSQL, SQL Server T-SQL, or Oracle PL/SQL.
• Constraints:
  • Functions can only return one value and cannot perform transactions (such as COMMIT or ROLLBACK).
  • Procedures can perform transactions without returning a value.

Triggers

• Definition: Triggers are stored procedures that execute or "fire" when specific database events occur (e.g., INSERT, UPDATE, DELETE).
• Purpose: Used to enforce business rules, audit modifications, and ensure complicated referential integrity.
• Syntax:
  CREATE TRIGGER trigger_name
  {BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE}
  ON table_name
  FOR EACH ROW
  EXECUTE FUNCTION function_name();

Indexes

• Definition: Indexes are database objects that speed up data retrieval operations on a table but need more writing and storage space.
• Purpose: Used to accelerate search queries, ensure uniqueness, and improve database performance.
• Types:
  • B-Tree Index: The default type is chosen in the majority of circumstances.
  • Hash Index: Used to compare equality.
  • Gin / Gist Index: Used for full-text searches and other specialised procedures.
• Syntax:
  CREATE INDEX index_name
  ON table_name (column_name);