Tạo và thực thi các hàm trong cơ sở dữ liệu máy chủ ms sql. Thủ tục lưu trữ SQL: Tạo và sử dụng

TẠO NÊN[ HOẶC THAY THẾ ] CHỨC NĂNG
Tên chức năng ([ [ phương thức đối số ][ tên_đối số ] đối số_type [,…] ])
TRẢ LẠIreturn_value_type
BẰNG" sự định nghĩa"
NGÔN NGỮ" ngôn ngữ"
[ VỚI (thuộc tính […])]

TẠO CHỨC NĂNG Tên chức năng ([[ phương thức đối số ] [tên_đối số ] đối số_type[,…] ]) - sau từ khóa CREATE FUNCTION, tên của hàm cần tạo sẽ được chỉ định, sau đó các đối số được liệt kê trong dấu ngoặc đơn, cách nhau bằng dấu phẩy. Đối với mỗi đối số, chỉ cần chỉ định loại là đủ, nhưng nếu muốn, bạn có thể chỉ định phương thức (in, out, inout; mặc định trong ) và tên. Nếu danh sách trong ngoặc đơn trống, hàm sẽ được gọi mà không có đối số (mặc dù bản thân dấu ngoặc đơn phải có cả trong định nghĩa của hàm và khi sử dụng nó). Từ khóa OR REPLACE được sử dụng để sửa đổi một hàm đã tồn tại.
TRẢ LẠI return_value_type -kiểu dữ liệu được hàm trả về.
BẰNG "sự định nghĩa" là định nghĩa chương trình của một hàm. Trong các ngôn ngữ thủ tục (chẳng hạn như PL/pgSQL), nó bao gồm mã hàm. Đối với các hàm C được biên dịch, đường dẫn hệ thống tuyệt đối đến tệp chứa mã đối tượng được chỉ định.
NGÔN NGỮ "ngôn ngữ". Tên của ngôn ngữ mà hàm được viết. Đối số có thể chuyển tên của bất kỳ ngôn ngữ thủ tục nào (chẳng hạn như plpgsqlhoặc plperl, nếu hỗ trợ tương ứng được cài đặt trong quá trình biên dịch), C hoặc SQL.
[
VỚI (thuộc tính [. ...]) ] -thuộc tính có thể nhận hai giá trị: iscachable và isstrict.
có thể truy cập được. Trình tối ưu hóa có thể sử dụng các lệnh gọi hàm trước đó để tăng tốc các lệnh gọi trong tương lai với cùng một bộ đối số. Bộ nhớ đệm thường được sử dụng khi làm việc với các hàm yêu cầu nhiều tài nguyên nhưng trả về cùng một kết quả với cùng giá trị đối số.
nghiêm ngặt. Hàm luôn trả về NULL nếu ít nhất một trong các đối số của nó là NULL. Khi truyền thuộc tính isstrict, kết quả được trả về ngay lập tức mà không thực sự thực thi hàm.

Trong PostgreSQL c Chỉ siêu người dùng mới được phép tạo các hàm trong C vì các hàm này có thể chứa các lệnh gọi hệ thống gây ra mối đe dọa tiềm tàng đối với bảo mật hệ thống. Hãy xem việc tạo các hàmsql Và plpgsql .

Tạo hàm SQL

TẠO NÊN[ HOẶC THAY THẾ ] CHỨC NĂNG
Tên chức năng ([ [ phương thức đối số ][ tên_đối số ] đối số_type [,…] ])
TRẢ LẠIreturn_value_type
BẰNG"
nhà điều hành SQL;
[nhà điều hành SQL;
… ]
"
NGÔN NGỮ sql
[ VỚI (thuộc tính […])] ;

Trong thân hàm sql có thể đứng chỉ một Các toán tử ngôn ngữ SQL, bất kỳ (Chọn, chèn, xóa, tạo, ...) ngoại trừ các toán tử kiểm soát giao dịch (cam kết, khôi phục...). Giá trị trả về là kết quả của câu lệnh SELECT, kiểu của nó phải khớp với kiểu được chỉ định sau TRẢ LẠI. Nếu có một số câu lệnh SELECT trong thân hàm, hàm sẽ trả về kết quả thực thi câu lệnh cuối cùng đó. Nếu chức năng sql không chứa các câu lệnh SELECT, loại kết quả của nó phải được chỉ định là void (trên thực tế, nó là một thủ tục).

Ví dụ về tạo và sử dụng hàm sql

Ví dụ 1: Tạo hàm trả về một cột giá trị văn bản

TẠO CHỨC NĂNGonef (số nguyên) TRẢ LẠI ký tự SEOF BẰNG "
-- Hàm trả về tên của nhà cung cấp có xếp hạng lớn hơn $1
chọn tên từ s trong đó rg>$1;
" NGÔN NGỮ sql;

Sử dụng hàm trả về một cột giá trị văn bản

LỰA CHỌN một trong những(10) BẰNG tên của ;

Kết quả

Ví dụ 2: Tạo hàm trả về một cột bản ghi

TẠO CHỨC NĂNGnhiềuf (số nguyên) TRẢ LẠI bản ghi BỘ BẰNG "
- - Hàm trả về thông tin về nhà cung cấp có rating lớn hơn $1
chọn ns,names,rg,town from s trong đó rg>$1 đặt hàng theo ns;
" NGÔN NGỮ sql;

Ghi chú. Có thể sử dụng hàm trả về một cột bản ghi trong câu lệnh SELECT nếu nó được định nghĩa là hàm SQL (hàm plpgsql có cách sử dụng khác)

Sử dụng hàm trả về một cột bản ghi

LỰA CHỌN nhiềuf(10) BẰNG kết quả ;

Kết quả

Tạo các hàm plpgSQL

TẠO NÊN[ HOẶC THAY THẾ ] CHỨC NĂNG
Tên_ chức năng ([ [ phương thức_đối số ][ Tên_ lý lẽ] kiểu_ lý lẽ[,…] ])
TRẢ LẠIreturn_value_type
BẰNG "
[ TUYÊN BỐ
Quảng cáo ]
BẮT ĐẦU
nhà điều hành;
[ nhà điều hành;…]
SAU ĐÓ

[KHI tình trạng[ HOẶCtình trạng...] SAU ĐÓ
báo cáo xử lý ngoại lệ;
... ]
KẾT THÚC;
"
NGÔN NGỮ plpgsql
[ VỚI ( thuộc tính […])] ;

Trong phần thân của hàm, các tham số được truy cập theo tên hoặc số: $1 là tham số đầu tiên, $2 là tham số thứ hai, v.v.
Trong thân hàm plpgsql Ngoài các câu lệnh SQL, có thể sử dụng các cấu trúc của ngôn ngữ PL/pgSQL, một phần mở rộng thủ tục của SQL. Đây có thể là toán tử gán, câu lệnh điều kiện, vòng lặp, v.v. Kết quả được trả về bằng lệnh TRỞ LẠI. Trong thân hàm plpgsql nhất thiết phải có ít nhất một lệnh TRỞ LẠI, ngoại trừ khi loại kết quả là void. Xem thêm chi tiết. http://www.postgresql.org/docs/8.2/interactive/extend.html
Các biến cục bộ dùng trong thân hàm được khai báo trong khối DECLARE
TUYÊN BỐ
tên biến biến_type;
[ tên biến biến_type;... ]
trong khối NGOẠI LỆ lỗi được xử lý. Tình trạng– tên lỗi (xem danh sách). http://www.sbin.org/doc/pg/doc/errcodes-appendix.html trong cột Hằng). Nếu tất cả các lỗi được xử lý theo cùng một sơ đồ (hoặc bạn quá lười suy nghĩ về những lỗi có thể xảy ra và tìm ra tên của chúng), thì khối này có thể trông như thế này
NGOẠI LỆ
KHI người khác SAU ĐÓ
TĂNG ngoại lệ " thông báo lỗi";
Chức năngNÂNG LÊNmức độngoại lệném một ngoại lệ và hiển thị thông báo lỗi (thông tin thêm về hàm NÂNG LÊNcm. http://www.sbin.org/doc/pg/doc/plpgsql-errors-and-messages.html).

Ví dụ về tạo và sử dụng hàm plpgsql

Ví dụ 3: Sáng tạo hàm trả về một giá trị nguyên

TẠO CHỨC NĂNG apf (ký tự) TRẢ LẠI số nguyên BẰNG "
TUYÊN BỐ
tôi số nguyên;
-- Hàm tính lượng cung của mặt hàng $1
BẮT ĐẦU
chọn số(*) từ spj vào i trong đó spj.np=$1;
-- trở lạikết quả
trả lại tôi;
KẾT THÚC;
" NGÔN NGỮ xin vui lòng;

Ví dụ 4: Tạo hàm trả về giá trị thực

TẠO CHỨC NĂNGtrung bình (ký tự) TRẢ LẠI thực tế BẰNG "
TUYÊN BỐ
aves thực sự;
-- Hàm tính trọng lượng giao hàng trung bình của phần $1
BẮT ĐẦU
chọn avg(spj.kol * p.ves) từ spj,p vào aves trong đó spj.np=$1 và spj.np=p.np ;
-- trở lạikết quả
trở lại aves;
KẾT THÚC;
" NGÔN NGỮ xin vui lòng;

Ví dụ 5: Sử dụng hàm trong SELECT cổ điển

LỰA CHỌNp.np, p.namep,
apf(p.np) AS kol, trung bình(p.np) là sves,
apf(p.np)* avgves(p.np) là oves TỪ P;

Kết quả

Np	tên	kol	sves	trứng
P1	Đinh ốc	1	1200	1200
P2	Chớp	2	2550	5100
P3	Đinh ốc	9	6611.11	59500.001953125
P4	Đinh ốc	2	9100	18200
P5	Cám	4	3300	13200
P6	Hoa	4	6175	24700

Ví dụ 6. Sử dụng hàm, tùy chọn không chuẩn (chỉ PostgreSql)

LỰA CHỌN apf("P5") BẰNG;

Kết quả

kol

Chức năng tùy chỉnh tương tự như thủ tục lưu trữ, nhưng, không giống như chúng, có thể được sử dụng trong các truy vấn giống như truy vấn hệ thống Chức năng tích hợp sẵn. Chức năng tùy chỉnh, trả về các bảng, có thể thay thế cho các khung nhìn. Chế độ xem được giới hạn trong một câu lệnh CHỌN duy nhất và chức năng tùy chỉnh có khả năng kết hợp các biểu thức bổ sung, cho phép tạo ra các cấu trúc phức tạp và mạnh mẽ hơn.

Hàm vô hướng

Sáng tạo và sửa đổi chức năng Loại này được thực hiện bằng lệnh:

<определение_скаляр_функции>::=(TẠO | THAY ĐỔI ) CHỨC NĂNG [chủ sở hữu.] function_name([ ( @parameter_name scalar_data_type [=default])[,...n]])RETURNS scalar_data_type ]BEGIN<тело_функции>RETURN biểu thức vô hướngEND

Chúng ta hãy xem mục đích của các tham số lệnh.

Chức năng có thể chứa một hoặc nhiều thông số đầu vào hoặc không chứa gì. Mỗi tham số phải là duy nhất trong phạm vi được tạo chức năng tên và bắt đầu bằng ký hiệu "@". Sau tên, kiểu dữ liệu của tham số được chỉ định. Ngoài ra, bạn có thể chỉ định một giá trị sẽ được tự động gán cho tham số (DEFAULT) nếu người dùng không chỉ định rõ ràng giá trị của tham số tương ứng khi gọi chức năng.

Bằng cách sử dụng cấu trúc RETURNS, scalar_data_type chỉ định kiểu dữ liệu mà kết quả trả về sẽ có. chức năng nghĩa.

Các tham số bổ sung được tạo bằng chức năng, có thể được chỉ định bằng từ khóa VỚI. Nhờ từ khóa ENCRYPTION, mã lệnh dùng để tạo chức năng, sẽ được mã hóa và không ai có thể xem được. Tính năng này cho phép bạn ẩn logic vận hành chức năng. Hơn nữa, trong cơ thể chức năng Các đối tượng cơ sở dữ liệu khác nhau có thể được truy cập và do đó việc thay đổi hoặc xóa các đối tượng tương ứng có thể dẫn đến gián đoạn chức năng. Để tránh điều này, bạn cần cấm thay đổi bằng cách chỉ định khi tạo chức năng từ khóa SCHEMABINDING.

Giữa các từ khóa BEGIN...END một tập lệnh được chỉ định, chúng sẽ là phần thân chức năng.

Khi trong quá trình thực thi mã chức năng Từ khóa RETURN xảy ra, thực thi chức năngđược hoàn thành và do kết quả tính toán của nó, giá trị được chỉ định ngay sau từ RETURN được trả về. Lưu ý rằng trong cơ thể chức năng Cho phép nhiều lệnh RETURN, có thể trả về các giá trị khác nhau. Cả hằng số thông thường và biểu thức phức tạp đều được phép làm giá trị trả về. Điều kiện duy nhất là kiểu dữ liệu của giá trị trả về phải khớp với kiểu dữ liệu được chỉ định sau từ khóa RETURNS.

Ví dụ Tạo và áp dụng hàm vô hướng để tính tổng số lượng hàng hóa nhận được trong một ngày cụ thể. Chủ sở hữu của hàm này là người dùng có tên user1.

TẠO CHỨC NĂNG user1.sale(@data DATETIME)TRẢ LẠI INTASBEGINDECLARE @c INTSET @c=(SELECT SUM(quantity) FROM Deal WHERE date=@data)RETURN (@c)END

Hàm nội tuyến

Sáng tạo và sửa đổi chức năng loại này được thực hiện bằng lệnh:

<определение_табл_функции>::=(TẠO | THAY ĐỔI ) CHỨC NĂNG [chủ sở hữu.] function_name([ ( @parameter_name scalar_data_type [=default])[,...n]])BẢNG TRẢ LẠI[ VỚI (ENCRYPTION | SCHEMABINDING) [,...n] ]RETURN [(] SELECT_statement [)]

Phần chính của các tham số được sử dụng khi tạo chức năng bảng, tương tự với các tham số hàm vô hướng. Tuy nhiên, việc tạo ra chức năng bảng có những đặc thù riêng.

Từ khóa RETURNS phải luôn được theo sau bởi từ khóa TABLE. Như vậy, chức năng loại này phải trả về một giá trị nghiêm ngặt kiểu dữ liệu BẢNG. Cấu trúc giá trị trả về gõ BẢNG không được chỉ định rõ ràng khi mô tả chính kiểu dữ liệu đó. Thay vào đó, máy chủ sẽ tự động sử dụng cấu trúc được trả về bởi truy vấn SELECT, vốn là một lệnh duy nhất, cho giá trị trả về TABLE chức năng.

Đặc thù chức năng của loại này là cấu trúc của giá trị TABLE được tạo tự động trong quá trình thực hiện truy vấn, thay vì được chỉ định rõ ràng trong định nghĩa loại sau từ khóa RETURNS.

Có thể trả lại chức năng nghĩa gõ BẢNG có thể được sử dụng trực tiếp trong một truy vấn, tức là trong phần TỪ.

Ví dụ Tạo và áp dụng hàm kiểu bảng để xác định hai mặt hàng có số dư lớn nhất.

TẠO CHỨC NĂNG user1.itog() TRẢ LẠI BẢNG TRẢ LẠI (CHỌN TOP 2 Mặt hàng.Tên TỪ Mặt hàng INNER THAM GIA Kho TRÊN Item.ItemCode=Warehouse.ItemCode ĐẶT HÀNG THEO Kho. DESC còn lại)

Sử dụng chức năngĐể có được hai mặt hàng có số dư lớn nhất, bạn có thể làm như sau:

CHỌN Tiêu đềTỪ user1.itog()

Hàm nhiều câu lệnh

Sáng tạo và sửa đổi chức năng kiểu Nhiều câu lệnhđược thực hiện bằng lệnh sau:

<определение_мульти_функции>::=(TẠO | THAY ĐỔI )CHỨC NĂNG [chủ sở hữu.] function_name([ ( @parameter_name scalar_data_type [=default])[,...n]])RETURNS @parameter_name BẢNG<определение_таблицы>]BẮT ĐẦU<тело_функции>TRỞ LẠI KẾT THÚC

Việc sử dụng hầu hết các tham số đã được thảo luận trong phần mô tả trước đó. chức năng.

Lưu ý rằng chức năng thuộc loại này cũng như dạng bảng, giá trị trả về gõ BẢNG. Tuy nhiên, không giống như chức năng bảng, trong khi tạo Hàm nhiều câu lệnh bạn phải xác định rõ ràng cấu trúc của giá trị trả về. Nó xuất hiện ngay sau từ khóa TABLE và do đó là một phần của định nghĩa kiểu dữ liệu trả về. Cú pháp xây dựng<определение_таблицы>hoàn toàn tương ứng với các cấu trúc cùng tên được sử dụng khi tạo các bảng thông thường bằng lệnh CREATE TABLE.

Tập dữ liệu trả về phải được hình thành bằng cách sử dụng các lệnh INSERT được thực thi trong phần thân chức năng. Hơn nữa, trong cơ thể chức năng Bạn có thể sử dụng các cấu trúc SQL khác nhau để kiểm soát các giá trị được đặt trong tập hàng đầu ra. Khi làm việc với lệnh INSERT, bạn phải chỉ định rõ ràng tên của đối tượng mà bạn muốn chèn hàng. Vì vậy trong chức năng kiểu Nhiều câu lệnh, Không giống dạng bảng, bạn cần gán một số tên cho đối tượng với kiểu dữ liệu BẢNG– nó được chỉ định là giá trị trả về.

Tắt chức năng xảy ra trong hai trường hợp: nếu xảy ra lỗi thời gian chạy và nếu từ khóa RETURN xuất hiện. không giống hàm kiểu vô hướng, khi sử dụng lệnh RETURN, bạn không cần chỉ định giá trị trả về. Máy chủ sẽ tự động trả về tập dữ liệu gõ BẢNG, tên và cấu trúc của nó được chỉ định sau từ khóa RETURNS. Trong cơ thể chức năng Có thể chỉ định nhiều lệnh RETURN.

Cần lưu ý rằng tác phẩm chức năng chỉ hoàn thành nếu có lệnh RETURN. Câu nói này cũng đúng khi nói đến phần cuối của cơ thể chức năng– lệnh cuối cùng phải là lệnh RETURN.

TẠO CHỨC NĂNG - tạo một chức năng

Cú pháp

Sự miêu tả

Lệnh CREATE FUNCTION xác định một hàm mới. TẠO HOẶC THAY THẾ CHỨC NĂNG tạo một hàm mới hoặc thay thế định nghĩa của hàm hiện có. Để xác định một hàm, bạn phải có quyền SỬ DỤNG đối với ngôn ngữ thích hợp.

Nếu tên lược đồ được chỉ định, hàm sẽ được tạo trong lược đồ đã chỉ định, nếu không thì - trong lược đồ hiện tại. Tên của hàm mới phải khác với tên của các hàm hiện có có cùng kiểu đối số trong lược đồ này. Tuy nhiên, các hàm có các đối số thuộc loại khác nhau có thể có cùng tên (được gọi là quá tải).

Để thay thế định nghĩa hiện tại của một hàm hiện có, hãy sử dụng lệnh TẠO HOẶC THAY THẾ CHỨC NĂNG. Nhưng lưu ý rằng nó không cho phép bạn thay đổi tên hoặc đối số của hàm (nếu bạn cố gắng làm điều này, nó thực sự sẽ tạo ra một hàm mới, độc lập). Ngoài ra, TẠO HOẶC THAY THẾ CHỨC NĂNG sẽ không cho phép bạn thay đổi loại kết quả của hàm hiện có. Để làm điều này, bạn sẽ phải xóa chức năng và tạo lại. (Điều này có nghĩa là nếu một hàm có tham số đầu ra (OUT), bạn chỉ có thể thay đổi loại tham số OUT bằng cách xóa hàm.)

Khi lệnh TẠO HOẶC THAY THẾ CHỨC NĂNG thay thế một chức năng hiện có, quyền sở hữu và quyền của chức năng đó không thay đổi. Tất cả các thuộc tính hàm khác đều nhận các giá trị được đặt rõ ràng hoặc theo mặc định bằng lệnh. Để thay thế một hàm, bạn phải là chủ sở hữu của hàm đó (hoặc thành viên của vai trò sở hữu).

Nếu bạn xóa rồi tạo lại một hàm, hàm mới sẽ trở thành một thực thể khác với hàm cũ; bạn cũng sẽ cần xóa các quy tắc, chế độ xem, trình kích hoạt, v.v. hiện có tham chiếu đến hàm cũ. Do đó, để thay đổi định nghĩa của hàm trong khi vẫn giữ nguyên các đối tượng tham chiếu đến nó, bạn nên sử dụng CREATE OR REPLACE FUNCTION . Ngoài ra, nhiều thuộc tính bổ sung của hàm hiện có có thể được thay đổi bằng cách sử dụng ALTER FUNCTION.

Người dùng đã tạo ra nó sẽ trở thành chủ sở hữu của hàm.

Để tạo một hàm, bạn phải có quyền SỬ DỤNG đối với các loại đối số và kiểu trả về của nó.

Tùy chọn

Tên

Tên của hàm cần tạo (có thể được bổ sung bằng lược đồ). chế độ đối số

Chế độ đối số: IN (đầu vào), OUT (đầu ra), INOUT (đầu vào và đầu ra) hoặc VARIADIC (biến). Mặc định là VÀO. Một đối số VARIADIC duy nhất chỉ có thể được theo sau bởi các đối số OUT. Ngoài ra, các đối số OUT và INOUT không thể được sử dụng với mệnh đề RETURNS TABLE. tên_đối số

Tên đối số. Một số ngôn ngữ (bao gồm SQL và PL/pgSQL) cho phép sử dụng tên này trong phần nội dung của hàm. Đối với các ngôn ngữ khác, tên này chỉ đóng vai trò mô tả bổ sung cho chính hàm đó; tuy nhiên, bạn có thể chỉ định tên đối số khi gọi hàm để cải thiện khả năng đọc (xem Phần 4.3). Dù sao thì tên của đối số đầu ra vẫn quan trọng vì nó chỉ định tên của cột trong loại kết quả. (Nếu bạn bỏ qua tên của đối số đầu ra, hệ thống sẽ chọn tên mặc định cho nó.) đối số_type

Kiểu dữ liệu của đối số hàm (có thể được mở rộng bằng lược đồ), nếu có bất kỳ đối số nào. Loại đối số có thể là cơ sở, tổ hợp hoặc miền hoặc có thể là tham chiếu đến một cột trong bảng.

Tùy thuộc vào ngôn ngữ triển khai, cũng có thể chỉ định "kiểu giả" ví dụ như chuỗi. Các kiểu giả chỉ ra rằng loại đối số thực tế không được xác định đầy đủ hoặc tồn tại bên ngoài tập hợp các loại SQL thông thường.

Một tham chiếu đến một loại cột được viết là tên_bảng.tên cột dọc%KIỂU. Đôi khi đặc tả này hữu ích vì nó cho phép bạn tạo một hàm độc lập với những thay đổi đối với định nghĩa bảng. biểu_thức mặc định

Biểu thức được sử dụng để tính giá trị mặc định nếu tham số không được chỉ định rõ ràng. Kết quả của một biểu thức phải quy về loại tham số tương ứng. Chỉ các tham số đầu vào (bao gồm INOUT) mới có thể có giá trị mặc định. Tất cả các tham số đầu vào theo sau một tham số có giá trị mặc định được xác định cũng phải có giá trị mặc định được xác định. loại kết quả

Kiểu dữ liệu trả về (có thể được mở rộng bằng một lược đồ). Đây có thể là loại cơ sở, hợp chất hoặc miền hoặc tham chiếu đến loại cột trong bảng. Tùy thuộc vào ngôn ngữ triển khai, điều này cũng có thể cho phép " giả danh », ví dụ như chuỗi. Nếu hàm không trả về giá trị thì loại kết quả là void.

Nếu có tham số OUT hoặc INOUT thì mệnh đề RETURNS có thể được bỏ qua. Nếu có, nó phải nhất quán với loại kết quả được suy ra từ các tham số đầu ra: kiểu trả về là RECORD nếu có nhiều tham số đầu ra hoặc loại của một tham số đầu ra.

Việc chỉ định SETOF chỉ ra rằng hàm trả về một tập hợp chứ không phải một phần tử đơn lẻ.

Tên của cột đầu ra trong mục RETURNS TABLE. Về cơ bản, đây là một cách khác để khai báo một tham số đầu ra được đặt tên (OUT), nhưng RETURNS TABLE cũng bao hàm RETURNS SETOF. cột_type

Kiểu dữ liệu của cột đầu ra trong bản ghi RETURNS TABLE. Tên ngôn ngữ

Tên của ngôn ngữ mà chức năng được thực hiện. Đây có thể là sql , c , nội bộ hoặc tên của ngôn ngữ thủ tục do người dùng xác định, chẳng hạn như plpgsql . Phong cách viết tên này bằng dấu nháy đơn được coi là lỗi thời và yêu cầu phải khớp chính xác kiểu chữ. CHUYỂN ĐỔI (DÀNH CHO LOẠI tên loại } [, ... ] }

Đặt danh sách các phép biến đổi sẽ được áp dụng khi gọi một hàm. Các phép chuyển đổi thực hiện chuyển đổi giữa các loại SQL và các loại dữ liệu dành riêng cho ngôn ngữ; xem TẠO CHUYỂN ĐỔI. Chuyển đổi loại tích hợp thường được mã hóa cứng trong triển khai ngôn ngữ thủ tục, vì vậy chúng không cần phải được chỉ định ở đây. Nếu việc triển khai ngôn ngữ thủ tục không thể xử lý một loại và không có chuyển đổi nào cho loại đó thì chuyển đổi loại mặc định sẽ được thực hiện, nhưng điều này phụ thuộc vào việc triển khai. CỬA SỔ

Dấu hiệu WINDOW cho thấy rằng nó không phải là một cái đơn giản đang được tạo ra, mà là chức năng cửa sổ. Hiện tại, điều này chỉ có ý nghĩa đối với các hàm được viết bằng C. Thuộc tính WINDOW không thể thay đổi bằng cách sửa đổi định nghĩa hàm sau đó. KHÔNG THỂ thay đổi
ỔN ĐỊNH
BAY HƠI

Các thuộc tính này thông báo cho trình tối ưu hóa truy vấn về hành vi của hàm. Bạn có thể chỉ định không quá một thuộc tính cùng một lúc. Nếu không có thuộc tính nào được chỉ định, VOLATILE được coi là mặc định.

Đặc tính IMMUTABLE (không đổi) cho thấy hàm không thể sửa đổi cơ sở dữ liệu và luôn trả về cùng một kết quả cho các giá trị đối số nhất định; nghĩa là nó không truy cập cơ sở dữ liệu hoặc sử dụng thông tin không được chuyển rõ ràng đến nó trong danh sách đối số. Nếu một hàm có đặc điểm này thì bất kỳ lệnh gọi hàm nào với các đối số không đổi đều có thể được thay thế ngay lập tức bằng giá trị của hàm.

Đặc tính STABLE chỉ ra rằng hàm không thể sửa đổi cơ sở dữ liệu và trong một lần quét bảng, nó luôn trả về cùng một kết quả cho các giá trị đối số nhất định, nhưng kết quả này có thể khác nhau trong các câu lệnh SQL khác nhau. Đây là lựa chọn thích hợp cho các hàm có kết quả phụ thuộc vào nội dung của cơ sở dữ liệu và các cài đặt có thể định cấu hình (chẳng hạn như múi giờ hiện tại). (Nhưng tùy chọn này không phù hợp với các trình kích hoạt SAU muốn đọc các hàng được sửa đổi bởi lệnh hiện tại.) Cũng lưu ý rằng họ hàm current_timestamp cũng được coi là ổn định vì kết quả của chúng không thay đổi trong một giao dịch.

Đặc tính VOLATILE (dễ bay hơi) chỉ ra rằng kết quả của một hàm có thể thay đổi ngay cả trong một lần quét bảng, do đó các lệnh gọi của nó không thể được tối ưu hóa. Tương đối ít hàm cơ sở dữ liệu có thể thay đổi được theo nghĩa này, ví dụ: Random(), Currval() và timeofday(). Nhưng lưu ý rằng bất kỳ hàm nào có tác dụng phụ đều phải được phân loại là không ổn định, ngay cả khi kết quả của nó khá dễ đoán, do đó lệnh gọi của nó không được tối ưu hóa; một ví dụ về hàm như vậy: setval() .

Để biết thêm chi tiết, xem Phần 35.6. RÒ RỈ BẰNG CHỨNG

Đặc tính LEAKPROOF (chống rò rỉ) cho biết chức năng này không có tác dụng phụ. Nó không tiết lộ thông tin về các đối số của nó ngoài việc trả về kết quả. Ví dụ: một hàm tạo ra thông báo lỗi với một số nhưng không phải tất cả các giá trị đối số hoặc xuất ra các giá trị đối số trong một thông báo lỗi, là không kín. Điều này ảnh hưởng đến cách hệ thống thực thi các truy vấn đối với các chế độ xem được tạo bằng hàng rào bảo mật (chỉ định security_barrier) hoặc đối với các bảng đã bật bảo mật hàng. Để ngăn chặn rò rỉ dữ liệu không được kiểm soát, hệ thống sẽ kiểm tra các điều kiện từ chính sách bảo mật và xem các định nghĩa có rào cản bảo mật trước bất kỳ điều kiện nào mà người dùng chỉ định trong chính yêu cầu có liên quan đến các chức năng bị rò rỉ. Các chức năng và toán tử được đánh dấu là đã niêm phong được coi là đáng tin cậy và có thể được thực thi trước các điều kiện từ chính sách bảo mật và quan điểm có rào cản bảo mật. Tuy nhiên, các hàm không có đối số hoặc không được truyền bất kỳ đối số nào từ chế độ xem hoặc bảng hàng rào bảo mật không cần phải được đánh dấu là đã được niêm phong để chúng có thể thực thi trước các điều kiện liên quan đến bảo mật. Xem TẠO XEM và Phần 38.5. Thuộc tính này chỉ có thể được đặt bởi siêu người dùng. ĐƯỢC GỌI TRÊN ĐẦU VÀO NULL
TRẢ LẠI NULL TRÊN ĐẦU VÀO NULL
NGHIÊM NGẶT

GỌI TRÊN NULL INPUT (mặc định) cho biết hàm sẽ được gọi như bình thường nếu có các giá trị NULL trong số các đối số của nó. Trong trường hợp này, người phát triển hàm có trách nhiệm kiểm tra các giá trị NULL và xử lý chúng cho phù hợp.

Việc chỉ định RETURNS NULL ON NULL INPUT hoặc STRICT chỉ ra rằng hàm luôn trả về NULL nếu nó nhận được NULL ở một trong các đối số của nó. Hàm như vậy sẽ không được gọi với các đối số NULL mà thay vào đó sẽ tự động nhận kết quả NULL. [ BÊN NGOÀI ] AN NINH INVOKER
[BÊN NGOÀI ] CÔNG CỤ ĐỊNH NGHĨA BẢO MẬT

Đặc tính SECURITY INVOKER chỉ ra rằng chức năng sẽ được thực thi với quyền của người dùng đã gọi nó. Tùy chọn này là mặc định. Tùy chọn SECURITY DEFINER chỉ định rằng hàm chạy với quyền của người dùng đã tạo ra nó.

Từ khóa EXTERNAL được phép tuân theo tiêu chuẩn SQL, nhưng là tùy chọn vì, không giống như SQL, đặc tính này áp dụng cho tất cả các hàm, không chỉ các hàm bên ngoài. SONG SONG

Câu lệnh PARALLEL UNSAFE có nghĩa là hàm không thể được thực thi song song và sự hiện diện của hàm trong câu lệnh SQL khiến kế hoạch thực hiện tuần tự được chọn. Đây là tính năng tính năng mặc định. GIỚI HẠN SONG SONG có nghĩa là chức năng có thể được thực thi song song, nhưng chỉ trong tiến trình dẫn đầu của nhóm. PARALLEL SAFE chỉ ra rằng chức năng này an toàn để chạy song song mà không bị hạn chế.

Các hàm phải được đánh dấu là không an toàn để chạy đồng thời nếu chúng sửa đổi trạng thái cơ sở dữ liệu, thực hiện các thay đổi đối với các giao dịch như sử dụng các giao dịch phụ, trình tự truy cập hoặc cố gắng lưu trữ các tham số (chẳng hạn như sử dụng setval). Song song bị hạn chế sẽ đánh dấu các chức năng truy cập các bảng tạm thời, trạng thái kết nối máy khách, con trỏ, câu lệnh đã chuẩn bị hoặc trạng thái quy trình máy chủ linh tinh mà hệ thống không thể đồng bộ hóa song song (ví dụ: setseed chỉ có thể được thực thi bởi quy trình hàng đầu của nhóm vì những thay đổi được thực hiện bởi người khác quá trình không được truyền tới máy chủ). Nói chung, nếu một hàm được đánh dấu là an toàn khi nó bị hạn chế hoặc không an toàn hoặc nếu nó được đánh dấu là an toàn có giới hạn khi nó không an toàn thì hàm đó có thể tạo ra lỗi hoặc kết quả không chính xác khi bạn cố gắng gọi hàm đó trong một truy vấn song song. Các hàm C, khi được đánh dấu không chính xác, về mặt lý thuyết có thể thể hiện hành vi hoàn toàn không xác định, vì hệ thống không có cách nào bảo vệ chống lại mã C tùy ý, nhưng thường thì tất cả chúng sẽ hoạt động không tệ hơn bất kỳ hàm nào khác. Khi có nghi ngờ, một chức năng phải được đánh dấu là không an toàn (UNSAFE), đây là mặc định. chi phí thực hiện

Một số dương chỉ định chi phí gần đúng để thực thi hàm, tính bằng đơn vị cpu_operator_cost. Nếu hàm trả về một tập hợp thì số này chỉ định chi phí cho một hàng. Nếu chi phí không được chỉ định, nó được giả định là 1 đơn vị cho C và các hàm nội bộ và 100 đơn vị cho các hàm trong tất cả các ngôn ngữ khác. Đối với các giá trị lớn hơn, bộ lập lịch sẽ cố gắng không gọi hàm này thường xuyên hơn mức cần thiết. hàng_in_kết quả

Một số dương xác định số lượng hàng gần đúng mà bộ lập lịch sẽ mong đợi dưới dạng đầu ra từ hàm này. Dấu hiệu này chỉ hợp lệ nếu hàm được khai báo trả về một tập hợp. Giá trị mặc định dự kiến là 1000 hàng. config_parameter
nghĩa

Mệnh đề SET chỉ định rằng khi một hàm được gọi, tham số cấu hình đã chỉ định phải lấy giá trị đã chỉ định và sau đó khôi phục giá trị trước đó khi hàm hoàn thành. Mệnh đề SET FROM CURRENT lưu trữ dưới dạng giá trị sẽ được áp dụng khi nhập hàm, giá trị có hiệu lực tại thời điểm CREATE FUNCTION được thực thi.

Nếu SET được thêm vào định nghĩa hàm thì tác dụng của lệnh SET LOCAL được thực thi bên trong hàm cho cùng một tham số sẽ bị giới hạn ở phần thân của hàm: giá trị trước đó của tham số cũng sẽ được khôi phục khi hàm thoát. Tuy nhiên, lệnh SET thông thường (không có LOCAL) sẽ ghi đè mệnh đề SET, giống như lệnh SET LOCAL trước đó: tác dụng của lệnh như vậy sẽ tiếp tục sau khi thoát khỏi hàm, trừ khi giao dịch được khôi phục.

Để biết chi tiết về tên và giá trị tham số, hãy xem SET và Chương 18. sự định nghĩa

Một hằng chuỗi xác định việc thực hiện hàm; ý nghĩa của nó phụ thuộc vào ngôn ngữ. Đây có thể là tên của hàm bên trong, đường dẫn đến tệp đối tượng, lệnh SQL hoặc mã cho hàm bằng ngôn ngữ thủ tục.

Việc đính kèm định nghĩa của hàm bằng đô la thường hữu ích (xem Tiểu mục 4.1.2.4) thay vì dùng dấu nháy đơn truyền thống. Nếu bạn không sử dụng đô la, tất cả dấu nháy đơn và dấu gạch chéo ngược trong định nghĩa hàm sẽ phải được loại bỏ bằng cách sao chép chúng. đối tượng_file, đối tượng_ký hiệu

Dạng mệnh đề AS này được sử dụng cho các hàm C được nạp động khi tên hàm trong mã C không giống với tên hàm trong SQL. Đường kẻ đối tượng_file chỉ định tên của tệp chứa đối tượng được tải động và đối tượng_ký hiệu là ký hiệu hàm được liên kết, nghĩa là tên của hàm trong mã nguồn C. Nếu ký hiệu đối tượng bị bỏ qua, nó được coi là khớp với tên của hàm SQL đang được xác định. Trong C, tất cả các tên hàm phải khác nhau, do đó, các hàm quá tải được triển khai trong C phải được đặt các tên khác nhau (ví dụ: bao gồm các chỉ định loại đối số trong tên C).

Nếu các lệnh gọi lặp lại để TẠO CHỨC NĂNG tham chiếu cùng một tệp đối tượng, nó chỉ được tải một lần trong phiên. Để tải lên và tải xuống lại tệp này (ví dụ: trong quá trình phát triển), hãy bắt đầu một phiên mới. thuộc tính

Về mặt lịch sử, đây là một cách để truyền thông tin bổ sung về một chức năng. Mục này có thể chứa các thuộc tính sau:

nghiêm ngặt

Tương đương với STRICT hoặc RETURNS NULL ON NULL INPUT . có thể lưu vào bộ nhớ đệm

Thuộc tính isCachable tương đương với IMMUTABLE; nó vẫn được hỗ trợ vì mục đích tương thích ngược.

Tên thuộc tính không phân biệt chữ hoa chữ thường.

Để biết thêm thông tin về phát triển tính năng, hãy xem Phần 35.3.

Quá tải

PostgreSQL cho phép quá tải chức năng; nghĩa là, nó cho phép sử dụng cùng một tên cho một số hàm khác nhau nếu chúng có các loại đối số đầu vào khác nhau. Cho dù bạn có sử dụng tính năng này hay không thì nó cũng cần thận trọng khi gọi các hàm trong cơ sở dữ liệu nơi một số người dùng không tin tưởng những người khác; xem Phần 10.3.

Hai hàm được coi là giống nhau nếu chúng có cùng tên và kiểu đầu vàođối số, tham số OUT bị bỏ qua. Vì vậy, ví dụ những tuyên bố này sẽ gây ra xung đột:

TẠO CHỨC NĂNG foo(int) ... TẠO CHỨC NĂNG foo(int, out text) ...

Các hàm có các kiểu đối số khác nhau sẽ không bị coi là xung đột tại thời điểm tạo, tuy nhiên các giá trị mặc định được cung cấp cho chúng có thể gây ra xung đột tại thời điểm sử dụng. Ví dụ, hãy xem xét các định nghĩa sau:

TẠO CHỨC NĂNG foo(int) ... TẠO CHỨC NĂNG foo(int, int mặc định 42) ...

Cuộc gọi tới foo(10) sẽ không thành công do có sự mơ hồ trong việc lựa chọn chức năng để gọi.

Ghi chú

Cú pháp khai báo kiểu SQL đầy đủ được cho phép trong khai báo các đối số hàm và giá trị trả về. Tuy nhiên, các công cụ sửa đổi kiểu trong dấu ngoặc đơn (ví dụ: trường chính xác cho kiểu số) không được lệnh CREATE FUNCTION tính đến. Vì vậy, ví dụ: CREATE FUNCTION foo (varchar(10)) ... sẽ tạo chức năng tương tự như CREATE FUNCTION foo (varchar) ... .

Khi thay thế một hàm hiện có bằng cách sử dụng TẠO HOẶC THAY THẾ CHỨC NĂNG, sẽ có những hạn chế đối với việc thay đổi tên tham số. Đặc biệt, bạn không thể thay đổi tên đã được gán cho bất kỳ tham số đầu vào nào (mặc dù bạn có thể thêm tên cho các tham số chưa được đặt tên trước đó). Ngoài ra, nếu một hàm có nhiều hơn một tham số đầu ra, bạn không thể thay đổi tên của các tham số đầu ra, vì điều này sẽ thay đổi tên của các cột thuộc loại hỗn hợp ẩn danh mô tả kết quả của hàm. Những hạn chế này giúp đảm bảo rằng các lệnh gọi hiện tại tới một chức năng sẽ không ngừng hoạt động sau khi nó được thay thế.

Nếu một hàm được khai báo STRICT với đối số VARIADIC, việc đánh giá mức độ nghiêm ngặt sẽ kiểm tra xem toàn bộ mảng biến có nói chung là có giá trị. Nếu mảng này chứa các phần tử NULL thì hàm sẽ được gọi.

Ví dụ

Dưới đây là một số ví dụ giới thiệu đơn giản. Để biết thêm thông tin và ví dụ, xem Phần 35.3.

TẠO CHỨC NĂNG add(số nguyên, số nguyên) TRẢ LẠI số nguyên NHƯ "select $1 + $2;" NGÔN NGỮ SQL KHÔNG THỂ TRẢ LẠI TRÊN NULL TRÊN ĐẦU VÀO NULL;

Hàm tăng một số nguyên lên 1 bằng cách sử dụng đối số được đặt tên, trong PL/pgSQL:

TẠO HOẶC THAY THẾ CHỨC NĂNG tăng dần(i số nguyên) TRẢ LẠI số nguyên NHƯ $$ BẮT ĐẦU TRẢ LẠI i + 1; KẾT THÚC; $$ NGÔN NGỮ plpgsql;

Hàm trả về một bản ghi có nhiều tham số đầu ra:

TẠO CHỨC NĂNG dup(trong int, out f1 int, out f2 text) NHƯ $$ SELECT $1, CAST($1 AS text) || " là văn bản" $$ NGÔN NGỮ SQL; CHỌN * TỪ dup(42);

Điều tương tự có thể được thực hiện chi tiết hơn bằng cách khai báo rõ ràng một loại hỗn hợp:

TẠO LOẠI dup_result NHƯ (f1 int, f2 văn bản); TẠO CHỨC NĂNG dup(int) TRẢ LẠI dup_result NHƯ $$ CHỌN $1, CAST($1 NHƯ văn bản) || " là văn bản" $$ NGÔN NGỮ SQL; CHỌN * TỪ dup(42);

Một cách khác để trả về nhiều cột là sử dụng hàm TABLE:

TẠO CHỨC NĂNG dup(int) BẢNG TRẢ LẠI(f1 int, f2 text) NHƯ $$ SELECT $1, CAST($1 AS text) || " là văn bản" $$ NGÔN NGỮ SQL; CHỌN * TỪ dup(42);

Tuy nhiên, ví dụ BẢNG khác với các ví dụ trước, vì trong đó hàm thực sự trả về không phải một mà là bộ dụng cụ Hồ sơ.

Phát triển các chức năng AN TOÀN DEFINER

Bởi vì hàm SECURITY DEFINER chạy theo quyền của người dùng đã tạo ra nó nên phải cẩn thận để đảm bảo rằng nó không thể được sử dụng cho các mục đích khác. Vì mục đích bảo mật, search_path phải loại trừ mọi lược đồ có thể được ghi bởi người dùng không đáng tin cậy. Điều này sẽ ngăn người dùng độc hại tạo các đối tượng của riêng họ (chẳng hạn như bảng, hàm và câu lệnh) sẽ che dấu các đối tượng được hàm đó sử dụng. Trong vấn đề này, điều đặc biệt quan trọng là loại trừ lược đồ bảng tạm thời, theo mặc định, lược đồ này được xem trước và mọi người đều có quyền ghi vào lược đồ đó theo mặc định. Có thể đạt được sự bảo vệ thích hợp bằng cách đặt sơ đồ tạm thời ở cuối danh sách tìm kiếm. Để thực hiện việc này, hãy đặt pg_temp mục nhập cuối cùng trong search_path . Chức năng sau đây thể hiện việc sử dụng an toàn:

TẠO CHỨC NĂNG check_password(uname TEXT, pass TEXT) TRẢ LẠI BOOLEAN NHƯ $$ KHAI THÁC đã vượt qua BOOLEAN; BẮT ĐẦU CHỌN (pwd = $2) VÀO được chuyển TỪ pwds WHERE tên người dùng = $1; TRẢ LẠI đã qua; KẾT THÚC; $$ LANGUAGE plpgsql SECURITY DEFINER -- Trước tiên hãy đặt đường dẫn tìm kiếm an toàn đến (các) lược đồ đáng tin cậy, sau đó là "pg_temp". BỘ search_path = quản trị viên, pg_temp;

Hàm này sẽ truy cập vào bảng admin.pwds, nhưng không có mệnh đề SET hoặc có mệnh đề SET chỉ bao gồm admin , hàm này có thể bị lừa bằng cách tạo một bảng tạm thời pwds .

Trước PostgreSQL 8.3, không có mệnh đề SET, vì vậy các hàm cũ hơn có thể chứa logic khá phức tạp để lưu, sửa đổi và khôi phục biến search_path. Việc cung cấp SET hiện tại làm cho việc này trở nên dễ dàng hơn nhiều.

Ngoài ra, hãy lưu ý rằng quyền thực thi mặc định cho các hàm đã tạo là CÔNG KHAI (xem GRANT để biết chi tiết). Tuy nhiên, thường cần hạn chế quyền truy cập vào các hàm hoạt động trong ngữ cảnh của bộ định nghĩa chỉ cho một số người dùng. Để thực hiện việc này, bạn cần thu hồi các quyền CÔNG CỘNG tiêu chuẩn và sau đó cấp quyền thực thi riêng lẻ. Để tránh tạo một cửa sổ trong đó chức năng mới hoàn toàn không khả dụng, hãy tạo nó và gán quyền trong một giao dịch. Ví dụ như thế này:

BẮT ĐẦU; TẠO CHỨC NĂNG check_password(uname TEXT, pass TEXT) ... CÔNG CỤ ĐỊNH NGHĨA BẢO MẬT; THU HỒI TẤT CẢ VỀ CHỨC NĂNG check_password(uname TEXT, pass TEXT) TỪ CÔNG KHAI; CẤP THỰC HIỆN CHỨC NĂNG check_password(uname TEXT, pass TEXT) CHO quản trị viên; LÀM;

Khả năng tương thích

Lệnh CREATE FUNCTION được định nghĩa trong SQL:1999 và các tiêu chuẩn mới hơn. Phiên bản được triển khai trong PostgreSQL gần giống với phiên bản được tiêu chuẩn hóa nhưng không hoàn toàn tuân thủ nó. Đặc biệt, các thuộc tính không thể di chuyển được, cũng như các ngôn ngữ triển khai khác nhau.

Để tương thích với các DBMS khác chế độ đối số có thể được viết sau tên_đối số hoặc trước nó, nhưng chỉ có tùy chọn đầu tiên đáp ứng tiêu chuẩn.

Để xác định giá trị mặc định cho tham số, chuẩn SQL chỉ hỗ trợ cú pháp với từ khóa DEFAULT. Cú pháp = được sử dụng trong T-SQL và Firebird.

1) Danh sách các chức năng tiêu chuẩn

Chức năng	Kết quả trả về
Bit_Length()	Số bit trong
Đóng vai)	, được chuyển đổi thành quy định
Char_Length()	Độ dài ký tự
Chuyển đổi (sử dụng)	, được chuyển đổi theo quy định
Ngay hiện tại	Ngày hiện tại
Thời điểm hiện tại()	Thời gian hiện tại từ thời điểm được chỉ định
Dấu thời gian hiện tại()	Ngày và giờ hiện tại từ ngày được chỉ định
Trích xuất (từ)	Được chỉ định (Ngày, Giờ, v.v.) kể từ ngày
Thấp hơn()	, chuyển thành chữ thường
Octet_Length()	Số byte trong
Vị trí(trong)	Vị trí từ đó đi vào
Chuỗi con(từ cho)	Phần bắt đầu tại vị trí và có quy định
Cắt(Dẫn đầu\|Theo sau\|Cả hai từ)	, trong đó những cái đứng đầu đã bị xóa \| kết thúc \| cả từ hai phía
Phía trên()	, chuyển thành chữ hoa
Người dùng	Xác định ID người dùng

2) Tổng quan về tính năng của MS SQL Server
Như vậy, SQL Server cung cấp nhiều chức năng, được chia thành các nhóm sau:
→ Chuỗi;
→ Toán học;
→ Chuyển đổi;
→ Làm việc với dữ liệu thuộc loại Văn bản và Hình ảnh;
→ Để làm việc với ngày tháng;
→ Hệ thống;
→ Niladic (null-place - không có tham số).
Cũng như một số chức năng khác.

3) Tổng quan về tính năng của Oracle
→ Xử lý lỗi;
→ Số;
→ Chuỗi;
→ Chuyển đổi;
→ Chương trình phát sóng, để làm việc với ngày tháng;
→ Mục đích khác nhau.

Khai báo một hàm được lưu trữ

TẠO CHỨC NĂNG ([ [()], …]) TRẢ LẠI [()] [[KHÔNG] XÁC ĐỊNH] [CHỨA SQL|ĐỌC DỮ LIỆU SQL|SỬA ĐỔI DỮ LIỆU SQL]
BẮT ĐẦU

TRỞ LẠI
KẾT THÚC

Từ khóa
. XÁC ĐỊNH cho biết hàm có trả về cùng giá trị với cùng giá trị đầu vào hay không. Ví dụ: hàm CURRENT_TIME KHÔNG PHẢI XÁC ĐỊNH.
. CHỨA SQL chỉ ra rằng hàm này không chứa các câu lệnh SQL đọc và sửa đổi dữ liệu. Giá trị này được đặt theo mặc định.
. ĐỌC DỮ LIỆU SQL chỉ ra rằng hàm chứa các câu lệnh SELECT hoặc FETCH.
. SỬA ĐỔI DỮ LIỆU SQL chỉ ra rằng hàm chứa các câu lệnh INSERT, UPDATE hoặc DELETE.

Hạn chế đối với các hàm không xác định

Trong Oracle:
. Không thể sử dụng các hàm không xác định khi kiểm tra các ràng buộc trong câu lệnh CHECK. Ngoài ra, các hạn chế không thể bao gồm các lệnh gọi đến các hàm do người dùng xác định.
. Các hàm không xác định không thể được sử dụng trong các chỉ mục dựa trên hàm.

Trong SQL Server, hàm do người dùng xác định được coi là xác định nếu:
. Hàm này bị ràng buộc bởi lược đồ, tức là Hàm được tạo bằng tùy chọn SCHEMABINDING, có nghĩa là các đối tượng được tham chiếu bởi hàm không thể sửa đổi hoặc xóa được.
. Mọi hàm (dù được tích hợp sẵn hay do người dùng xác định) được gọi từ phần thân của hàm đó đều mang tính xác định.
. Phần thân của hàm không chứa tham chiếu đến các đối tượng cơ sở dữ liệu (ví dụ: bảng, dạng xem và các hàm khác) nằm ngoài phạm vi.
. Hàm này không gọi các thủ tục lưu trữ mở rộng (có thể thay đổi trạng thái của cơ sở dữ liệu).

Xóa và thay đổi các chức năng được lưu trữ

Để xóa một hàm, hãy sử dụng toán tử:

Để thay đổi một hàm, hãy sử dụng toán tử:

CHỨC NĂNG THAY ĐỔI ([[{VÀO|OUT|INOUT}] [()],…])
BẮT ĐẦU

TRỞ LẠI
KẾT THÚC

Các hàm được lưu trữ trong SQL Server

TẠO CHỨC NĂNG ([@ [BẰNG] [()] [=] [CHỈ ĐỌC],…])
TRẢ LẠI [()]
[BẰNG]
BẮT ĐẦU

TRỞ LẠI
KẾT THÚC

Ví dụ:

SỬ DỤNG B1;
ĐI
TẠO CHỨC NĂNG Quý(@Dat DateTime) TRẢ LẠI int
BẮT ĐẦU
KHAI THÁC @ISQuarter int;
IF ((Tháng(@Dat)>=1) Và (Tháng(@Dat)=4) Và (Tháng(@Dat)=8) Và (Tháng(@Dat)=10) Và (Tháng(@Dat)

Yêu cầu đối với các hàm được gọi

Để một hàm do lập trình viên PL/SQL xác định được gọi từ các lệnh SQL, nó phải đáp ứng các yêu cầu sau:

Tất cả các tham số chức năng phải có chế độ sử dụng IN. Chế độ IN OUT và OUT trong các hàm được nhúng trong mã SQL không được phép.
Các kiểu dữ liệu của tham số hàm và kiểu trả về phải được máy chủ Oracle nhận dạng. PL/SQL bổ sung cho các loại Oracle cốt lõi chưa được cơ sở dữ liệu hỗ trợ. Các loại này bao gồm BOOLEAN, BINARY_INTEGER, mảng kết hợp, bản ghi PL/SQL và các kiểu con do lập trình viên xác định.
Chức năng phải được lưu trữ trong cơ sở dữ liệu. Không thể gọi hàm do phía máy khách xác định trong lệnh SQL vì SQL sẽ không thể giải quyết tham chiếu hàm.

Theo mặc định, các hàm do người dùng định nghĩa được gọi trong SQL hoạt động trên dữ liệu của một hàng chứ không phải một cột (như các hàm tổng hợp SUM, MIN và AVG). Để tạo các hàm tổng hợp được gọi trong SQL, bạn phải sử dụng giao diện ODCIAggregate, là một phần của Khung mở rộng Oracle. Để biết thông tin chi tiết về chủ đề này, vui lòng tham khảo tài liệu của Oracle.

Những hạn chế đối với các hàm do người dùng định nghĩa được gọi trong SQL

Để bảo vệ khỏi các tác dụng phụ và hành vi không thể đoán trước, Oracle không cho phép các thủ tục lưu trữ thực hiện những việc sau:

Các hàm được lưu trữ không thể sửa đổi các bảng cơ sở dữ liệu và không thể đưa ra các lệnh DDL (CREATE TABLE, DROP INDEX, v.v.), INSERT, DELETE, MERGE hoặc UPDATE. Những hạn chế này được nới lỏng nếu chức năng được xác định là một giao dịch tự trị. Trong trường hợp này, mọi thay đổi nó thực hiện đều được thực hiện độc lập với giao dịch bên ngoài mà yêu cầu được thực thi.
Các hàm được lưu trữ được gọi từ xa hoặc song song không thể đọc hoặc thay đổi giá trị của các biến gói. Oracle Server không hỗ trợ các tác dụng phụ vượt quá phiên của người dùng.
Hàm được lưu trữ chỉ có thể thay đổi giá trị của các biến gói nếu nó được gọi trong danh sách chọn hoặc trong mệnh đề VALUES hoặc SET. Nếu một hàm được lưu trữ được gọi trong mệnh đề WHERE hoặc GROUP BY, nó không thể thay đổi giá trị của các biến gói.
Trước Oracle8, các hàm do người dùng định nghĩa không thể gọi thủ tục RAISE_APPLICATION_ERROR.
Một hàm được lưu trữ không thể gọi một mô-đun khác (thủ tục hoặc hàm được lưu trữ) không đáp ứng các yêu cầu này.
Hàm được lưu trữ không thể truy cập vào chế độ xem vi phạm bất kỳ quy tắc nào trước đó. Một khung nhìn là một lệnh SELECT được lưu trữ trong đó các hàm được lưu trữ có thể được gọi.
Trước Oracle11g, chỉ có thể sử dụng ký hiệu vị trí để truyền tham số cho các hàm. Bắt đầu với Oracle11g, cho phép truyền tham số theo tên và ký hiệu hỗn hợp.

Đọc tính nhất quán và chức năng tùy chỉnh

Mô hình nhất quán đọc trong cơ sở dữ liệu Oracle rất đơn giản và dễ hiểu: sau khi thực thi, một truy vấn sẽ “nhìn thấy” dữ liệu ở trạng thái tồn tại (đã được cam kết với cơ sở dữ liệu) tại thời điểm bắt đầu truy vấn, có tính đến kết quả về những thay đổi được thực hiện bởi các lệnh DML của giao dịch hiện tại. Vì vậy, nếu truy vấn của tôi chạy lúc 9:00 sáng và tiếp tục chạy trong một giờ, ngay cả khi người dùng khác thực hiện thay đổi đối với dữ liệu trong thời gian đó, chúng sẽ không được phản ánh trong truy vấn của tôi.

Nhưng trừ khi bạn thực hiện các biện pháp phòng ngừa đặc biệt với các hàm do người dùng xác định trong truy vấn của mình, bạn có thể thấy rằng truy vấn của mình vi phạm (ít nhất là trên bề mặt) mô hình nhất quán khi đọc cơ sở dữ liệu Oracle. Để hiểu khía cạnh này, hãy xem xét hàm sau và truy vấn gọi nó:

CHỨC NĂNG tổng_doanh số (id_in IN account.account_id%TYPE) SỐ TRẢ LẠI LÀ CURSOR tot_cur LÀ CHỌN TỔNG (doanh số) tổng số TỪ các đơn đặt hàng WHERE account_id = id_in VÀ TO_CHAR (ordered_on, "YYYY") = TO_CHAR (SYSDATE, "YYYY"); tot_rec tot_cur%ROWTYPE; BẮT ĐẦU MỞ tot_cur; FETCH tot_cur VÀO tot_rec; ĐÓNG tot_cur; TRẢ LẠI tot_rec.total; KẾT THÚC; CHỌN tên, tổng_doanh số (account_id) TỪ tài khoản WHERE status = "ACTIVE";

Bảng tài khoản có 5 triệu hàng đang hoạt động và bảng đơn hàng có 20 triệu hàng. Tôi chạy yêu cầu lúc 10:00 và mất khoảng một giờ để hoàn thành. Lúc 10:45, một người có đặc quyền cần thiết bước vào, xóa tất cả các hàng khỏi bảng đơn hàng và thực hiện giao dịch. Theo các quy tắc của mô hình nhất quán đọc của Oracle, phiên trong đó truy vấn đang chạy sẽ không coi các hàng này sẽ bị xóa cho đến khi truy vấn hoàn tất. Nhưng lần tiếp theo hàm Total_sale được gọi từ truy vấn, nó sẽ không tìm thấy bất kỳ hàng nào và sẽ trả về NULL - và điều này sẽ tiếp tục cho đến khi truy vấn hoàn tất.

Khi chạy truy vấn từ các hàm được gọi trong mã SQL, bạn cần cẩn thận về tính nhất quán khi đọc. Nếu các hàm này được gọi trong các truy vấn hoặc giao dịch chạy dài, bạn có thể muốn chạy lệnh sau để đảm bảo tính nhất quán khi đọc giữa các lệnh SQL của giao dịch hiện tại: SET TRANSACTION READ ONLY

Trong trường hợp này, phải cẩn thận để đảm bảo có đủ không gian bảng hoàn tác.

Xác định các quy trình PL/SQL trong các lệnh SQL (12.1 trở lên)

Các nhà phát triển từ lâu đã có thể gọi các hàm PL/SQL của họ từ các lệnh SQL. Giả sử tôi đã tạo một hàm có tên BETWNSTR trả về một chuỗi con có vị trí bắt đầu và kết thúc nhất định:

CHỨC NĂNG betwnstr (string_in IN VARCHAR2 , start_in IN PLS_INTEGER , end_in IN PLS_INTEGER ) tiếp tục # RETURN VARCHAR2 IS BEGIN RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1)); KẾT THÚC;

Hàm này có thể được sử dụng trong các truy vấn như sau:

CHỌN betwnstr (last_name, 3, 5) TỪ nhân viên

Tính năng này cho phép bạn “mở rộng” SQL bằng chức năng dành riêng cho ứng dụng và sử dụng lại các thuật toán (thay vì sao chép chúng). Những nhược điểm của việc chạy các hàm do người dùng định nghĩa trong SQL bao gồm nhu cầu chuyển đổi ngữ cảnh giữa các công cụ thực thi SQL và PL/SQL. Bắt đầu với Cơ sở dữ liệu Oracle 12c, bạn có thể xác định các hàm và thủ tục PL/SQL trong mệnh đề WITH của truy vấn con, sau đó bạn có thể sử dụng như bất kỳ hàm dựng sẵn hoặc hàm nào do người dùng xác định. Tính năng này cho phép bạn hợp nhất một hàm và một truy vấn thành một lệnh:

VỚI CHỨC NĂNG betwnstr (string_in IN VARCHAR2, start_in IN PLS_INTEGER, end_in IN PLS_INTEGER) RETURN VARCHAR2 IS BEGIN RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1)); KẾT THÚC; CHỌN betwnstr (last_name, 3, 5) TỪ nhân viên

Ưu điểm chính của giải pháp này là tăng hiệu suất, vì với định nghĩa về hàm này, chi phí chuyển ngữ cảnh từ công cụ SQL sang công cụ PL/SQL giảm đáng kể. Mặt khác, nó phải trả giá bằng việc có thể sử dụng lại logic trong các phần khác của ứng dụng.

Tuy nhiên, có những lý do khác để xác định hàm trong mệnh đề WITH. Trong SQL, bạn có thể gọi một hàm batch, nhưng bạn không thể tham chiếu một hằng số được khai báo trong một batch (trừ khi lệnh SQL được thực thi bên trong khối PL/SQL), như trong ví dụ sau:

SQL> TẠO HOẶC THAY THẾ GÓI pkg 2 IS 3 năm_số INTEGER CONSTANT:= 2013; 4 KẾT THÚC; 5/Gói đã được tạo. SQL> SELECT pkg.year_number FROM nhân viên 2 WHERE nhân viên_id = 138 3 / SELECT pkg.year_number FROM nhân viên * LỖI ở dòng 1: ORA-06553: PLS-221: "YEAR_NUMBER" không phải là thủ tục hoặc không được xác định

Cách giải quyết cổ điển dựa trên việc xác định một hàm trong một gói và sau đó gọi nó:

SQL> TẠO HOẶC THAY THẾ GÓI pkg 2 IS 3 CHỨC NĂNG năm_số 4 RETURN INTEGER; 5 KẾT THÚC; 6/Gói đã được tạo. SQL> TẠO HOẶC THAY THẾ THÂN GÓI pkg 2 IS 3 c_year_number CONSTANT INTEGER:= 2013; 4 5 CHỨC NĂNG năm_số 6 RETURN INTEGER 7 IS 8 BEGIN 9 RETURN c_year_number; 10 KẾT THÚC; 11 KẾT THÚC; 12/ Thân gói được tạo. SQL> CHỌN pkg.year_number 2 TỪ nhân viên 3 WHERE nhân viên_id = 138 4/SỐ NĂM ------------ 2013

Việc chỉ truy cập giá trị của hằng số trong lệnh SQL sẽ đòi hỏi quá nhiều mã và công sức. Bắt đầu từ phiên bản 12.1, điều này trở nên không cần thiết - chỉ cần tạo một hàm trong phần VỚI:

VỚI CHỨC NĂNG năm_số TRẢ LẠI INTEGER LÀ BẮT ĐẦU TRẢ LẠI pkg.year_number; KẾT THÚC; CHỌN số năm TỪ nhân viên Ở ĐÂY nhân viên_id = 138

Các hàm PL/SQL được xác định trong SQL cũng hữu ích khi làm việc với cơ sở dữ liệu chỉ đọc. Mặc dù bạn không thể tạo các hàm "trợ giúp" PL/SQL trong các cơ sở dữ liệu như vậy, nhưng bạn có thể xác định chúng trực tiếp trong các truy vấn.

VỚI CHỨC NĂNG là một cải tiến cực kỳ hữu ích cho ngôn ngữ SQL. Tuy nhiên, mỗi khi bạn định sử dụng nó, một câu hỏi đáng tự hỏi là: “Liệu chức năng này có cần thiết ở nhiều nơi trong ứng dụng không?”

Nếu câu trả lời là có, bạn cần quyết định xem liệu hiệu suất thu được từ việc sử dụng WITH FUNCTION có bù đắp được những tổn thất tiềm tàng do sao chép và dán logic này qua nhiều lệnh SQL hay không.

Xin lưu ý rằng trong phiên bản 12.1, không thể thực thi lệnh chọn tĩnh với mệnh đề with trong khối PL/SQL. Tất nhiên, điều này trông rất lạ và tôi chắc chắn rằng tính năng này sẽ xuất hiện trong phiên bản 12.2, nhưng hiện tại, khi cố chạy đoạn mã sau, sẽ xuất hiện một lỗi:

SQL> BEGIN 2 VỚI CHỨC NĂNG full_name (fname_in TRONG VARCHAR2, lname_in TRONG VARCHAR2) 3 RETURN VARCHAR2 4 IS 5 BEGIN 6 RETURN fname_in || " " || lname_in; 7 KẾT THÚC; 8 9 CHỌN ĐỘ DÀI (full_name (first_name, Last_name)) 10 INTO c 11 TỪ nhân viên; 12 13 DBMS_OUTPUT.put_line("count = " || c); 14 KẾT THÚC; 15/ CÓ CHỨC NĂNG full_name (fname_in IN VARCHAR2, lname_in IN VARCHAR2) * LỖI ở dòng 2: ORA-06550: dòng 2, cột 18: PL/SQL: ORA-00905: thiếu từ khóa

Ngoài mệnh đề WITH FUNCTION, phiên bản 12.1 còn giới thiệu một lệnh UDF để cải thiện hiệu suất của các hàm PL/SQL được thực thi từ SQL.