💻 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
| Technology |
Database System(s) |
Key Features |
| PL/pgSQL |
PostgreSQL |
Procedural language, similar to PL/SQL |
| SQL/PSM |
MySQL, DB2, others |
Standardized procedural SQL extension |
| Java Stored Procs |
Oracle, SQL Server, DB2 |
Java-based stored procedures |
| Embedded SQL |
Multiple (C, Java, Python) |
SQL embedded in host languages |
| JavaScript (NoSQL) |
MongoDB |
Server-side scripting in JS |
| Lua Scripting |
Redis |
Atomic operations with Lua scripts |
| UDFs |
Many DBMS |
Extend SQL with custom functions |