---
title: "Stored Procedures in PL/SQL"
layout: default
parent: "Stored Procedures"
has_children: true
nav_order: 1
---
# Stored Procedures in PL/SQL
Oxibase implements a dedicated native procedural language clone, inspired heavily by PostgreSQL's `PL/pgSQL`. This allows you to write procedures using standard database control flows, without needing to learn a new external scripting language.
Because this interpreter is built natively within Oxibase, it provides deep integration and will serve as the foundation for our upcoming debugger (DAP) capabilities.
## Basic Usage
You can define a PL/SQL procedure using `LANGUAGE sql` or `LANGUAGE plsql`. The procedure body generally requires a `BEGIN ... END;` block.
```sql
CREATE PROCEDURE check_val(val INT, OUT is_positive BOOLEAN)
LANGUAGE plsql
AS '
BEGIN
IF val > 0 THEN
is_positive := TRUE;
ELSE
is_positive := FALSE;
END IF;
END;
';
```
Call the procedure:
```sql
CALL check_val(5, false);
```
**Result:**
| is_positive |
## Variable Assignment
In PL/SQL, variables are assigned using the `:=` operator. Note that currently, variables act dynamically during assignment in the MVP implementation.
```sql
CREATE PROCEDURE calculate_fee(base_price INT, INOUT total INT)
LANGUAGE plsql
AS '
BEGIN
IF base_price > 100 THEN
total := base_price + 20;
ELSE
total := base_price + 5;
END IF;
END;
';
```
## Executing SQL Commands
One of the most powerful features of native PL/SQL is the ability to run standard database queries directly inside the procedural block. The Oxibase PL/SQL interpreter natively bridges these commands to the core SQL execution engine.
Any parameters or local variables defined in your PL/SQL environment can be seamlessly used within your SQL statements.
```sql
CREATE TABLE audit_logs (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
message TEXT
);
CREATE PROCEDURE log_event(msg TEXT)
LANGUAGE plsql
AS '
BEGIN
-- Execute an INSERT using the PL/SQL variable `msg`
INSERT INTO audit_logs (message) VALUES (msg);
END;
';
```
Call the procedure:
```sql
CALL log_event('User signed in successfully');
```
If you query the `audit_logs` table, you will see the record has been inserted natively within the procedure's execution context.
```sql
SELECT * FROM audit_logs;
```
## Supported Syntax Overview
The PL/SQL native dialect currently supports:
- **Blocks**: `BEGIN ... END;`
- **Conditionals**: `IF condition THEN ... ELSE ... END IF;`
- **Assignments**: `variable := expression;`
- **Return**: `RETURN;` (to exit the block early)
- **SQL Execution**: Standard DML statements (`INSERT`, `UPDATE`, `DELETE`, etc.) natively bridge to the database.
_Note: The PL/SQL dialect is continuously evolving. Features like looping (`WHILE`, `FOR`), explicit `DECLARE` blocks, and native transaction control (`COMMIT`, `ROLLBACK`) are slated for upcoming releases._