Οδηγός με παραδείγματα Stored Procedures στην PostgreSQL

Εισαγωγή

Οι Stored Procedures στην PostgreSQL παίζουν σημαντικό ρόλο και προσφέρουν αρκετά οφέλη στη διαχείριση βάσεων δεδομένων. Σε αυτόν τον οδηγό θα δούμε πώς να χρησιμοποιείς stored procedures στην PostgreSQL για να χειρίζεσαι τις λειτουργίες SQL πιο αποδοτικά και με μεγαλύτερη ασφάλεια. Το άρθρο απευθύνεται σε software engineers και διαχειριστές βάσεων δεδομένων που θέλουν να βελτιώσουν τις δεξιότητές τους αξιοποιώντας πιο προχωρημένες δυνατότητες της PostgreSQL.

Τι είναι μια Stored Procedure;

Πριν ξεκινήσουμε με τα παραδείγματα, είναι σημαντικό να καταλάβεις την έννοια της Stored Procedure. Στην PostgreSQL, μια Stored Procedure είναι ένα προμεταγλωττισμένο εκτελέσιμο αντικείμενο που περιέχει μια σειρά από εντολές SQL. Η βασική της δουλειά είναι να εκτελεί λειτουργίες στη βάση δεδομένων, όπως insert, update, delete και create. Είναι ιδιαίτερα χρήσιμες για λειτουργίες με πολλά βήματα ή σύνθετη λογική, επιτρέποντας καλύτερη διαχείριση των transactions και λιγότερα τρωτά σημεία στη βάση δεδομένων.

Σημείωση: Τα παραδείγματα και οι εντολές αυτού του οδηγού είναι συμβατά με την PostgreSQL 11 και νεότερες εκδόσεις.

Προαπαιτούμενα

Για αυτόν τον οδηγό θα πρέπει να έχεις εγκαταστήσει και ρυθμίσει τα εξής:

1. Εγκατάσταση PostgreSQL στον υπολογιστή σου ή σε server.
2. Εργαλείο γραμμής εντολών ή GUI εργαλείο όπως το Beekeeper Studio για να εκτελείς τις εντολές SQL.

Δημιουργία Stored Procedure στην PostgreSQL

Ιστορικά, η PostgreSQL χρησιμοποιούσε τη λέξη-κλειδί FUNCTION για να ορίζει ρουτίνες που μπορούν να επιστρέφουν τιμή. Οι functions στην PostgreSQL μπορούν να εκτελούν λειτουργίες και πρέπει να επιστρέφουν μια τιμή ή ένα σύνολο, που μπορεί να είναι μια βαθμωτή τιμή ή ένας πίνακας. Μπορούν επίσης να χρησιμοποιηθούν μέσα σε SQL queries.

Η PROCEDURE, που εισήχθη στην PostgreSQL 11, χρησιμοποιείται για να ορίζει ρουτίνες που δεν επιστρέφουν τιμή. Οι procedures μπορούν να εκτελούν ενέργειες όπως η αλλαγή της κατάστασης της βάσης δεδομένων χωρίς να επιστρέφουν απευθείας δεδομένα. Καλούνται με την εντολή CALL, σε αντίθεση με τις functions που συνήθως καλούνται ως μέρος μιας εντολής SELECT.

Χρήση PROCEDURE για ενημέρωση πίνακα

Ας ξεκινήσουμε δημιουργώντας μια απλή Stored Procedure στην PostgreSQL. Θα φτιάξουμε μια procedure που ενημερώνει τον μισθό ενός υπαλλήλου.

CREATE OR REPLACE PROCEDURE update_employee_salary(p_id INT, p_salary NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
   UPDATE employees SET salary = p_salary WHERE employee_id = p_id;
END;
$$;

Στον παραπάνω κώδικα, update_employee_salary είναι το όνομα της procedure, η οποία δέχεται δύο παραμέτρους: το ID του υπαλλήλου (p_id) και τον νέο μισθό (p_salary).

Κλήση Stored Procedure στην PostgreSQL

Για να καλέσουμε τη νέα μας procedure, χρησιμοποιούμε την εντολή CALL όπως φαίνεται παρακάτω:

CALL update_employee_salary(1, 50000);

Η παραπάνω εντολή θα ενημερώσει τον μισθό του υπαλλήλου με ID 1 σε 50000.

Προχωρημένη χρήση: Procedures με λογική συνθηκών

Οι procedures μπορούν επίσης να χρησιμοποιούν πιο σύνθετη λογική, όπως εντολές συνθηκών και χειρισμό σφαλμάτων, για να εκτελούν πιο εξελιγμένες λειτουργίες.

Αυτή η procedure ενημερώνει το τμήμα ενός υπαλλήλου με βάση τις συνθήκες που δίνονται, δείχνοντας τη χρήση δομών ελέγχου μέσα σε procedures.

CREATE OR REPLACE PROCEDURE update_employee_department(employee_id INT, new_department_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
    IF new_department_id IS NOT NULL THEN
        UPDATE employees
        SET department_id = new_department_id
        WHERE id = employee_id;
    ELSE
        RAISE EXCEPTION 'Department ID cannot be null.';
    END IF;
END;
$$;

Αναμενόμενο αποτέλεσμα:

CREATE PROCEDURE

Χρήση FUNCTION για ανάκτηση δεδομένων

Σε αντίθεση με την PROCEDURE, μια FUNCTION στην PostgreSQL ταιριάζει καλύτερα σε λειτουργίες όπου αναμένεται τιμή επιστροφής. Αυτό είναι ιδιαίτερα χρήσιμο για ανάκτηση δεδομένων και υπολογισμούς που πρέπει να αποτελούν μέρος SQL queries. Εδώ θα φτιάξουμε ένα παράδειγμα function που ανακτά πληροφορίες με βάση συγκεκριμένες συνθήκες.

Αυτή η function θα φέρνει τα στοιχεία ενός υπαλλήλου με βάση το ID του. Δείχνει πώς οι functions μπορούν να επιστρέφουν δεδομένα που μπορείς στη συνέχεια να χρησιμοποιήσεις απευθείας σε SQL queries.

CREATE OR REPLACE FUNCTION get_employee_details(employee_id INT)
RETURNS TABLE(first_name VARCHAR, last_name VARCHAR, department_id INT) AS $$
BEGIN
    RETURN QUERY 
    SELECT first_name, last_name, department_id
    FROM employees
    WHERE id = employee_id;
END;
$$ LANGUAGE plpgsql;

Αναμενόμενο αποτέλεσμα:

CREATE FUNCTION

Κλήση της function

Για να ανακτήσεις δεδομένα με αυτή τη FUNCTION, χρησιμοποιείς την εντολή SELECT, όπως ακριβώς όταν κάνεις query σε έναν πίνακα.

SELECT * FROM get_employee_details(1);

Αναμενόμενο αποτέλεσμα:

Εδώ, το αποτέλεσμα θα είναι τα στοιχεία του υπαλλήλου με ID 1. Αν ο υπάλληλος υπάρχει, θα εμφανιστούν το όνομα, το επώνυμο και το ID του τμήματός του. Αν δεν υπάρχει τέτοιος υπάλληλος, το αποτέλεσμα θα είναι κενό.

 first_name | last_name | department_id 
------------+-----------+---------------
 John       | Doe       | 1
(1 row)

Συμπέρασμα

Σε αυτόν τον οδηγό μάθαμε πώς να γράφουμε Stored Procedures στην PostgreSQL με PROCEDURE και FUNCTION και πώς να τις καλούμε. Η κατανόηση της χρήσης των Stored Procedures βοηθά σε καλύτερη οργάνωση κώδικα, ασφάλεια και ταχύτητα εκτέλεσης. Τα παραδείγματα είναι απλά, αλλά οι έννοιες παραμένουν ίδιες και για πιο σύνθετες λειτουργίες.

Οι stored procedures στην PostgreSQL προσφέρουν έναν αξιόπιστο τρόπο να ενθυλακώνεις λογική, εξασφαλίζοντας ότι οι λειτουργίες εκτελούνται αποδοτικά και με ασφάλεια. Ενσωματώνοντας stored procedures στον σχεδιασμό της βάσης δεδομένων σου, μπορείς να φτιάξεις εφαρμογές βάσεων δεδομένων πιο εύκολες στη συντήρηση και πιο αξιόπιστες. Χρησιμοποίησε αυτόν τον οδηγό ως αφετηρία για να εξερευνήσεις τις πολλές δυνατότητες που προσφέρουν οι διαδικαστικές λειτουργίες της PostgreSQL.