💻 Database Programming: Similar Technologies

1. PL/pgSQL (PostgreSQL)

• Procedural language for PostgreSQL (an open-source RDBMS).
• Very similar to Oracle’s PL/SQL.
• Supports control structures, loops, variables, exception handling.
• Used for writing stored procedures, functions, and triggers.

Example:

CREATE FUNCTION increase_salary(emp_id INT, increment NUMERIC) RETURNS VOID AS $$
BEGIN
  UPDATE employees SET salary = salary + increment WHERE employee_id = emp_id;
END;
$$ LANGUAGE plpgsql;

2. SQL/PSM (Persistent Stored Modules)

• An ISO/ANSI SQL standard procedural extension.
• Defines syntax and semantics for stored procedures and triggers.
• Supported (to varying degrees) by some DBMS like MySQL, IBM DB2, and others.

3. Java Stored Procedures

• Supported by many DBMS (Oracle, SQL Server, DB2).
• Allows writing stored procedures in Java.
• Useful when business logic requires advanced programming features.

4. Embedded SQL

• SQL statements embedded in a host programming language like C, C++, Java, or Python.

• Uses host variables to pass data between SQL and the host program.

• Examples:

  • Pro*C (Embedded SQL in C for Oracle)
  • JDBC (Java Database Connectivity)
  • ODBC (Open Database Connectivity)

5. PL/SQL-like Languages in NoSQL Databases

Some NoSQL and NewSQL databases provide scripting or procedural logic:

• MongoDB uses JavaScript for stored procedures or server-side logic.
• Cassandra supports CQL (Cassandra Query Language) with limited procedural features.
• Redis supports Lua scripting for atomic operations.

6. User-Defined Functions (UDFs)

• Available in many DBMS for extending SQL capabilities.
• Can be written in various languages (SQL, procedural languages, or external languages like C or Python).
• Example: Writing a Python UDF in Amazon Redshift or SQL Server.

7. Summary Table