PL/pgSQL - SQL Procedural Language

PL/pgSQL là một loadable procedural language cho PostgreSQL database system. Những điểm sáng của PL/pgSQL khi được tạo ra một loadable procedural language là:

• có thể sử dụng để tạo ra các function và trigger
• Thêm vào các cấu trúc điểu khiển trong SQL language
• Có thể thể hiện được những tính toán phức tạp
• Có thể kế thừa từ mọi kiểu mà người dùng định nghĩa, các hàm, các phép toán.
• Có thể định nghĩa trusted bởi serve
• Dễ dàng để sử dụng

Những function được tạo ra bằng PL/pgSQL có thể sử dụng ở mọi nơi. ví dụ, Nó có thể thể hiện các tính toán phức tạp kèm theo điều kiện tính toán và sau đó, sử dụng chúng để định nghĩa các phép toán khác hoặc sử dụng chúng trong các phép tính index.

Trong PostgreSQL 9.0 và các phiên bản gần đây, PL/pgSQL đưuọc cài đặt như là mặc định , tuy nhiên, nó vẫn là một loadable module , nên vì lý do đặc biệt về bảo mật nào đó, thì admin có thể lựa chọn xóa bỏ chúng.

Lợi thế của việc sử dụng PL/pgSQL

• SQL trong PostgreSQL cũng giống như đa phần các cơ sở dữ liệu quan hệ sử dụng truy vấn khác. Đó là di động và dễ dàng để tìm hiểu. Tuy nhiên, mỗi câu lệnh SQL phải được thực hiện riêng rẽ bởi các server.Điều đó có nghĩa là client của bạn phải gửi cho mỗi truy vấn đến máy chủ cơ sở dữ liệu, chờ cho nó để được xử lý, tiếp nhận và xử lý kết quả, làm một số tính toán, sau đó gửi các truy vấn thêm để các máy chủ. Tất cả điều này phải chịu chi phí mạng nếu khách hàng của bạn là trên một máy tính khác nhau hơn so với các máy chủ cơ sở dữ liệu.

Với PL / pgSQL bạn có thể nhóm một khối lượng tính toán và một loạt các truy vấn bên trong các máy chủ cơ sở dữ liệu, do đó có sức mạnh của một ngôn ngữ thủ tục và dễ sử dụng của SQL, với chi phí tiết kiệm của mô hình client-server

• Các thông tin vòng, lặp giữa client-server được loại bỏ.
• Các kết quả trung gian mà khách hàng không cần không cần phải đưa vào hoặc chuyển giữa máy chủ và máy khách
• Tránh được nhiều vòng phên.

Những điều trên có thể dẫn đến một sự gia tăng hiệu suất đáng kể so với một ứng dụng không sử dụng được lưu trữ functions.Vì vậy, với PL / pgSQL bạn có thể sử dụng tất cả các loại dữ liệu, vận hành và chức năng của SQL.

Hỗ trợ các tham số và Kiểu dữ liệu trả về

• Các hàm được viết trong PL/pgSQL có thể cheo phép các tham số, hoặc bất kỳ một mảng dữ liệu nào được hỗ trợ bởi server, và chúng cũng có thể trả về một kết qủa có kiểu của chúng. CHúng đồng ý kết qủa trả về có thể là bất kỳ kiểu trả về nào được định nghĩa theo tên. Diều này có thể khải báo một PL/pgSQL function và kết qủa trả về là một record, điều đó cũng có nghĩa kết qủa trả về là một row type với những cột của nó được gọi bởi câu query.
• PL/pgSQL functions có thể khai báo các biết là số hoặc các tham số kiểu VARIADIC marker. Nó làm việc chính xác giống như SQL functions
• PL/pgSQL fnction có thể khai báo kiểu trả về là bất cứ kiểu nào khả dụng được hỗ trợ bở server. Các kiểu tự nhiên(int, boolean...) được gọi từ một hàm đa hình, có thể được gọi như gọi một hàm thông thường.
• PL/pgSQL có thể trả về một set hoặc trigger, table, query .v.v là những đối tượng của databases.
• Cuối cùng, một PL/pgSQL function có thể khai báo kiểu trả về là void và không có dữ liệu trả về.

Cấu trúc của PL/pgSQL

• PL/pgSQL là một ngôn ngữ có cấu trúc. Dể hoàn thành một định nghĩa một function, nó phải trong một khối lện. Một block (khối lệnh) được định nghĩa như sau:

    [ <<label>> ]
    [ DECLARE
        declarations ]
    BEGIN
        statements
    END [ label ];

Với mỗi khai báo và mỗi câ lệnh bên trong khối lệnh, phải được kết thúc bởi dấu chấm phẩy (. Một khối được phân biệt với một khối khác bởi dấu chấm phẩy sau và trước end/ Tuy nhiên, kết thúcEND sẽ bao gồm thân của function không yêu cầu dấu chấm phẩy (

Tip: Một lỗi phổ biến là viết một dấu chấm phẩy ở giữa BEGIN. Đềi này là không đúng và kết qủa trả về là lỗi syntax.

Một label chỉ thực sự cần thiết nếu bạn muốn định danh một khối lệnh cho câu lệnh EXIT, hoặc nâng cao chất lượng việc sử dụng tên cho các biến được khai bó trong block. Nếu một label được đưa ra sau END, nó phải được gaứn với một block begin

Mọi key word là case-insentive (không nhạy cảm không phân biệt chữ hoa chữ thường). Các định danh sẽ được convert sang lower case (chuyển chữ thường) trừ các từ trong dấu ngoặc kép (""), nó chỉ là các câu lệnh SQL.

Các comment hoạc động giống như trong SQL. Một dấu double dash (--) sẽ bắt đầu một comment và kết thúc của dòng đó.Dấu /* */ sẽ bắt đầu và kết thúc một comment trên nhiều dòng. Khối comment có thể lồng nhau.

Bất kỳ một câu lệnh nào đều trong một statement secsion của một block, (cũng có thể là một subblock). Subblock có thể được sử dụng để nhóm các logic và tính toán theo biến cục bộ , thành các nhóm nhỏ của những câu lệnh. Các biến được khai bao trong một subblock nó không khả dụng ở ngoài subblock.

Các Khai báo

• Mọi biến được sử dụng trong một block phải được khai báo trong declaration section của block. Chỉ có một ngoại lệ là các biến trong vòng lặp FOR trong môt mảng các số integer.
• Các biến PL/pgSQL có thể là bất cứ kiểu dữ liệu của SQL nào, gioongs như integer, varchar, char..v.v.
• Đây là một ví dụ của việc khai báo biến

    user_id integer;
    quantity numeric(5);
    url varchar;
    myrow tablename%ROWTYPE;
    myfield tablename.columnname%TYPE;
    arow RECORD;

Cấu trúc thông thường để khai báo các biến là :

    name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := | = } expression ];

Mệnh đề DEFAULT, nếu được, xác định giá trị ban đầu gán cho biến khi khối được nhập vào. Nếu mệnh đề DEFAULT không được đưa ra sau đó biến được khởi tạo với giá trị SQL null. Các tùy chọn CONSTANT ngăn ngừa biến từ được phân công sau khi khởi tạo, vì vậy mà giá trị của nó sẽ không thay đổi trong suốt thời gian của khối. Các tùy chọn đối chiếu xác định một collation để sử dụng cho biến (xem phần 40.3.6). Nếu NOT NULL được chỉ định, chuyển nhượng một kết quả giá trị null trong một lỗi thời gian chạy. Tất cả các biến khai báo là NOT NULL phải có một giá trị mặc định nonnull quy định. Bằng (=) có thể được sử dụng thay cho PL / SQL-compliant:. = Giá trị mặc định của một biến được đánh giá và gán cho biến mỗi lần khối được nhập (không chỉ một lần cho mỗi cuộc gọi chức năng). Vì vậy, ví dụ, giao now () cho một biến của loại dấu thời gian gây ra các biến để có thời gian của cuộc gọi chức năng hiện tại, không phải là thời gian khi các chức năng đã được biên dịch sẵn.

Ví dụ

    quantity integer DEFAULT 32;
    url varchar := 'http://mysite.com';
    user_id CONSTANT integer := 10;

Khai baó Tham số của hàm

Các thông số thông qua chức năng được đặt tên với định danh $ 1, $ 2, vv Tùy chọn, bí danh có thể được khai báo với $ n tên tham số cho tăng khả năng đọc. Hoặc là bí danh hoặc nhận dạng số sau đó có thể được sử dụng để tham khảo các thông số value.There hai cách để tạo ra một bí danh. Cách ưa thích là để đặt tên cho các tham số trong CREATE FUNCTION lệnh, ví dụ:

    CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
    BEGIN
        RETURN subtotal * 0.06;
    END;
    $$ LANGUAGE plpgsql;

Một cách khác là để tuyên bố một cách rõ ràng một bí danh, bằng cách sử dụng cú pháp khai báo

    name ALIAS FOR $n;

Các ví dụ tương tự trong phong cách này hình như:

    CREATE FUNCTION sales_tax(real) RETURNS real AS $$
    DECLARE
        subtotal ALIAS FOR $1;
    BEGIN
        RETURN subtotal * 0.06;
    END;
    $$ LANGUAGE plpgsql;

Chú ý: Hai ví dụ trên là không hoàn toàn tương đương. Trong trường hợp đầu tiên, tổng số phụ có thể được tham chiếu như sales_tax.subtotal, nhưng trong trường hợp thứ hai nó không thể. (Chúng ta đã gắn nhãn cho các khối bên trong, tổng số phụ có thể đủ điều kiện với nhãn đó, thay thế.)

Một số ví dụ khác:

    CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$
    DECLARE
        v_string ALIAS FOR $1;
        index ALIAS FOR $2;
    BEGIN
        -- some computations using v_string and index here
    END;
    $$ LANGUAGE plpgsql;

    CREATE FUNCTION concat_selected_fields(in_t sometablename) RETURNS text AS $$
    BEGIN
        RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
    END;
    $$ LANGUAGE plpgsql;

Khi một hàm PL / pgSQL được khai báo với các thông số đầu ra, các thông số đầu ra được cho $ n tên và bí danh tùy chọn chỉ trong cùng một cách như các thông số đầu vào bình thường. Một tham số đầu ra là có hiệu quả một biến mà bắt đầu ra NULL; nó nên được giao cho trong quá trình thực hiện chức năng. Giá trị cuối cùng của tham số là những gì được trả lại. Ví dụ, ví dụ bán hàng thuế cũng có thể được thực hiện theo cách này:

    CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
    BEGIN
        tax := subtotal * 0.06;
    END;
    $$ LANGUAGE plpgsql;

Chú ý rằng chúng ta bỏ qua RETURNS thực - chúng ta có thể đã bao gồm nó, nhưng nó sẽ được redundant.Output thông số rất hữu ích khi quay trở lại nhiều giá trị. Một ví dụ nhỏ là:

    CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
    BEGIN
        sum := x + y;
        prod := x * y;
    END;
    $$ LANGUAGE plpgsql;

này có hiệu quả tạo ra một loại kỷ lục vô danh cho kết quả của hàm. Nếu một điều khoản RETURNS được đưa ra, nó phải nói RETURNS cách record.Một cách khác để khai báo một hàm PL / pgSQL là với RETURNS TABLE, ví dụ:

    CREATE FUNCTION extended_sales(p_itemno int)
    RETURNS TABLE(quantity int, total numeric) AS $$
    BEGIN
        RETURN QUERY SELECT s.quantity, s.quantity * s.price FROM sales AS s
                     WHERE s.itemno = p_itemno;
    END;
    $$ LANGUAGE plpgsql;

Điều này tương đương với tuyên bố một hoặc nhiều OUT thông số và chỉ rõ RETURNS một kiểu nào đó.

Nguồn : plpgsql