Xem Nhiều 2/2023 #️ Sql:function Và Store Procedure (Hàm/Thủ Tục) Trong Sql # Top 2 Trend | Sansangdethanhcong.com

Xem Nhiều 2/2023 # Sql:function Và Store Procedure (Hàm/Thủ Tục) Trong Sql # Top 2 Trend

Cập nhật thông tin chi tiết về Sql:function Và Store Procedure (Hàm/Thủ Tục) Trong Sql mới nhất trên website Sansangdethanhcong.com. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất.

2. Function và Store Procedure (hàm/thủ tục)

2.1. Function và Store Procedure là gì

Là đoạn chương trình kịch bản (programming scripts) với các câu lệnh SQL nhúng (embedded SQL) được lưu dưới dạng đã được biên dịch và thi hành thực tiếp bởi MySQL server.

SP cho phép lưu trữ các logic ứng dụng trên CSDL.

2.2. Cú pháp

Tạo hàm/thủ tục

CREATE FUNCTION name ([parameterlist]) RETURNS datatype [options] sqlcode

CREATE PROCEDURE name ([parameterlist]) [options] sqlcode

Ví dụ:

DELIMITER $$

CREATE PROCEDURE count_people_with_age(IN age_in INT, OUT quantity INT)

BEGIN

SELECT *

FROM people

SELECT FOUND_ROWS() INTO quantity;

END

DELIMITER $$: Giải phóng để cho phép sử dụng ‘;’ trong thủ tục

Gán giá trị cho biến

Sử dụng SET hoặc SELECT INTO.

Gọi thủ tục:

Call count_people_with_age (18,@people_count);

Select @ people _count;

Hiện thị thông tin

SHOW FUNCTION STATUS;

SHOW PROCEDURE STATUS;

SHOW FUNCTION STATUS LIKE ‘repeat%’;

SHOW PROCEDURE STATUS LIKE ‘film%’;

SHOW CREATE PROCEDURE ‘name‘;

Một số cấu trúc điều khiển dùng trong Hàm, thủ tục

[ ELSE commands;]

REPEAT commands;

UNTIL condition

END REPEAT [ loopname];

CASE expression

WHEN value1 THEN commands;

[WHEN value2 THEN commands;]

[ELSE commands;]

END CASE;

[loopname:]

WHILE condition DO commands;

END WHILE [loopname];

2.3. Con trỏ (cursor) trong hàm/thủ tục

MySQL hỗ trợ con trỏ trong hàm/thủ tục, trigger

Con trỏ được sử dụng để duyệt các dòng kết quả trả lại bởi truy vấn, và xử lý từng dòng riêng lẻ

Cú pháp Con trỏ:

DECLARE cursor_name CURSOR FOR SELECT_statement;

OPEN cursor_name;

Lấy ra từng dòng và chuyển tới dòng kết quả tiếp theo sử dụng lệnh FETCH

FETCH cursor_name INTO variable list;

CLOSE cursor_name;

2.4. Xử lý lỗi thông qua Handlers

Luôn có khả năng hàm/thủ tục gặp lỗi trong khi thi hành các lệnh SQL. MySQL cung cấp kỹ thuật xử lý lỗi thông qua handler

Một handler cần định nghĩa sau khai báo các biến, con trỏ và điều kiện, nhưng trước các lệnh SQL

Cú pháp Handler: DECLARE type HANDLER FORcondition1, condition2, condition3, … statement;

condition(s): Các điều kiện mà handler sẽ được gọi : Not found, SqlException,..

statement: Câu lệnh sẽ thi hành khi có điều kiện xảy ra

2.5. Ưu nhược điểm

Ưu điểm

Giảm dư thừa mã chương trình: Các đoạn mã tương tự trong các ứng dụng như thêm, cập nhật có thể lưu ở phía CSDL

Bảo trì: Nếu có sự thay đổi trong CSDL, mã lệnh cần thay đổi có thể xác định trong các SP

An ninh CSDL tốt hơn: Trong các ứng dụng an ninh cao, với hàm/thủ tục có thể kiểm soát truy cập dữ liệu và đưa ra các qui định an ninh tập trung

Nhược điểm

Thiếu tính khả chuyển (Lack of Portability)

SP khó chuyển từ một DBMS sang một DBMS khác. Yêu cầu lập trình và kiểm thử lại đáng kể

Sử dụng hàm/thủ tục nhiều có thể gây quá tải MySQL server

Lập trình SP không phong phú như các nền tảng phát triển khác như Java hay PHP

Lưu ý: Các phiên bản tương lai của MySQL có thể cung cấp các giao diện cho phép tạo các hàm/thủ tục sử dụng các ngôn ngữ bên ngoài, ví dụ Java

Procedure, Function Và Package Trong Oracle Pl/Sql

Thủ tục (Procedure) ▴

Một nhóm các lệnh thực hiện chức năng nào đó có thể được gom lại trong một thủ tục (procedure) nhằm làm tăng khả năng xử lý,khả năng sử dụng chung,tăng tính bảo mật và an toàn dữ liệu,tiện ích trong phát triển.

Thủ tục có thể được lưu giữ ngay trong database như một đối tượng của database, sẵn sàng cho việc tái sử dụng. Thủ tục lúc này được gọi là Store procedure. Với các Store procedure, ngay khi lưu giữ Store procedure, chúng đã được biên dịch thành dạng p-code vì thế có thể nâng cao khả năng thực hiện.

Thủ tục không trả về giá trị trực tiếp như hàm.

Các bước thực hiện một thủ tục:

Ví dụ tạo một thủ tục:

Đây là một ví dụ tạo một thủ tục đầu tiên trên PL/SQL Developer. ví dụ này sẽ là khuôn mẫu giúp bạn vừa lập trình vừa kiểm tra lỗi nếu có vấn đề xẩy ra.

Tạo một thủ tục (Procedure)

Biên dịch thủ tục này

Chạy thủ tục

Debug thủ tục bằng PL/SQL Developer để xem chương trình chạy thế nào.

Tạo mới một thủ tục (Procedure):

Nhập vào tên của thủ tục, các tham số sẽ được viết sau:

Thủ tục đã được PL/SQL Developer tạo ra. Tuy nhiên bạn cần sửa lại danh sách tham số, và viết code cho thủ tục này.

Sửa thủ tục của bạn như sau:

Nhấn vào biểu tượng Execute

hoặc F8 để biên dịch thủ tục. Trong trường hợp có lỗi code, PL/SQL Developer sẽ thông báo cho bạn.

Test thủ tục trên PL/SQL Developer ▴

Nhấn phải chuột vào thủ tục Get_Employee_Infos chọn Test:

Nhập tham số đầu vào, ví dụ:

Kết quả thực thi thủ tục:

Xem trên màn hình Console:

Test trường hợp khác với:

Debug thủ tục trên PL/SQL Developer ▴

Debug trên PL/SQL Developer cho phép bạn xem một thủ tục, hàm đã được chạy thế nào, theo từng lệnh. Giúp bạn dễ dàng tìm ra các vị trí phát sinh lỗi. Bạn có thể xem hướng dẫn tại:

Hàm (Function) ▴

Tương tự như thủ tục, hàm (function) cũng là nhóm các lệnh PL/SQL thực hiện chức năng nào đó. Khác với thủ tục, các hàm sẽ trả về một giá trị ngay tại lời gọi của nó. Hàm cũng có thể được lưu giữ ngay trên database dưới dạng Store procedure.

Với việc sử dụng hàm, trong một số trường hợp bạn có thể thấy được các lợi điểm như sau:

Cho phép thực hiện các thao tác phức tạp(các phép tìm kiếm, so sánh phứctạp) ngay trong mệnh đề của câu lệnh SQL mà nếu không sử dụng hàm bạn sẽ không thể nào thực hiện được

Tăng tính độc lập của dữ liệu do việc phân tích và xử lý dữ liệu được thực hiện ngay trên Server thay vì trả về dữ liệu trực tiếp cho ứng dụng dưới Client để chúng tiếp tục xử lý.

Tăng tính hiệu quả của câu lệnh truy vấn bằng việc gọi các hàm ngay trong câu lệnh SQL

Bạn có thể sử dụng hàm để thao tác trên các kiểu dữ liệu tự tạo. Cho phép thực hiện đồng thời các câu lệnh truy vấn

Một số hạn chế khi sử dụng hàm trong câu lệnh SQL

Chỉ các hàm do người dùng định nghĩa được lưu trên database mới có thể sử dụng được cho câu lệnh SQL.

Các hàm do người dùng định nghĩa chỉ được áp dụng cho điều kiện thực hiện trên các dòng dữ liệu (mệnh đề WHERE), không thể áp dụng cho các điều kiện thực hiện trên nhóm (mệnh đề GROUP).

Tham số sử dụng trong hàm chỉ có thể là loại IN, không chấp nhận giá trị OUT hay giá trị IN OUT.

Kiểu dữ liệu trả về của các hàm phải là kiểu dữ liệu DATE, NUMBER, NUMBER. Không cho phép hàm trả về kiểu dữ liệu như BOOLEAN, RECORD, TABLE. Kiểu dữ liệu trả về này phải tương thích với các kiểu dữ liệu bên trong Oracle Server .

Cú pháp tạo Hàm.

Ví dụ tạo một hàm.

Các hàm không có tham số OUT, có thể tham gia vào câu lệnh SQL, ví dụ:

Kết quả chạy câu lệnh SQL trên:

Package ▴

Package là một tập hợp các kiểu dữ liệu, biến lưu giữ giá trị và các thủ tục,hàm có cùng một mối liên hệ với nhau, được gộp chung lại. Đặc điểm nổi bật nhất của package là khi một phần tử trong package được gọi thì toàn bộ nội dung của package sẽ được nạp vào trong hệ thống. Do đó, việc gọi tới các phần tử khác trong package sau này sẽ không phải mất thời gian nạp vào hệ thống nữa. Từ đó, nâng cao tốc độ thực hiện lệnh của toàn bộ hàm, thủ tục có trong package.

Một package được cấu trúc làm hai phần. Phần mô tả (specification) định nghĩa các giao tiếp có thể có của package với bên ngoài. Phần thân (body) là các cài đặt cho các giao tiếp có trong phần mô tả ở trên.

Trong cấu trúc của package bao gồm 05 thành phần:

Public variable (biến công cộng): là biến mà các ứng dụng bên ngoài có thể tham chiếu tới được.

Public procedure (thủ tục công cộng): bao gồm các hàm, thủ tục của package có thể triệugọi từ các ứngdụngbên ngoài.

Private procedure (thủ tục riêng phần): là các hàm, thủ tục có trong package và chỉ có thể được triệu gọi bởi các hàm hay thủ tục khác trong package mà thôi.

Global variable (biến tổng thể): là biến được khai báo dùng trong toàn bộ package, ứng dụng bên ngoài tham chiếu được tới biến này .

Private variable (biến riêng phần): là biến được khai báo trong một hàm, thủ tục thuộc package.Nó chỉ có thể được tham chiếu đến trong bản thân hàm hay thủ tục đó.

Tạo package trên PL/SQL Developer ▴

PL/SQL Developer giúp bạn tạo nhanh package spec & package body.

Package đã được PL/SQL Developer tạo ra, với các gợi ý viết package được tự động sinh ra. Bạn có thể xóa hết chúng đi.

Bạn có thể xóa hết các code tự tạo ra tự động bởi PL/SQL Developer để có một package rỗng:

Create Or Replace Package Pkg_Emp Is

Function Get_First_Name(p_Emp_Id Employee.Emp_Id%Type)

Return Employee.First_Name%Type;

Function Get_Dept_Name(p_Emp_Id Employee.Emp_Id%Type)

Return Department.Name%Type;

Postgresql Và Mysql, Một So Sánh

Trong bài đăng này, tôi muốn so sánh cấp cao giữa PostgreSQL và MySQL.

Một trong những điểm bán hàng chính của PostgreSQL là giấy phép. PostgreSQL là Mã nguồn mở, có giấy phép GPL và nó thuộc sở hữu của cộng đồng Nhóm phát triển toàn cầu PostgreSQL.

MySQL ngày nay thuộc sở hữu của Oracle và có 2 giấy phép khác nhau. Một là mã nguồn mở, một là thương mại. Điều này khôngkhông phảicó nghĩa là nếu bạn muốn sử dụng MySQL cho một dự án thương mại, bạn cần phải trả phí. Giấy phép thương mại cung cấp hỗ trợ trả phí và phần mềm bổ sung được xây dựng trên MySQL, từ Oracle.

Có một nhánh mã nguồn mở hoàn toàn của MySQL được gọi làMariaDB, ít phổ biến hơn nhiều về cách sử dụng và thương hiệu, bởi vì nó không có cùng lịch sử với MySQL, nhưng nó là sự thay thế tối ưu nếu bạn thích MySQL hơn nhưng lại lo lắng về việc cấp phép.

Hãy nói về các tính năng.

Ít nhiều thì bộ tính năng của cả MySQL và PostgreSQL đều tương đương nhau. Một người có thể làm điều gì đó mà người kia không làm, nhưng chúng ta đang nói về các tính năng rất nâng cao mà tôi không đề cập ở đây.

Cả hai đều là hệ quản trị cơ sở dữ liệu rất tiên tiến có lịch sử lâu đời.

Cả hai đều hỗ trợ tiêu chuẩn SQL (không hoàn toàn, nhưng một phần lớn) và thêm các tính năng trên đó. Cả hai đều cung cấp tuân thủ ACID (Tính nguyên tử, Tính nhất quán, Tính cách ly, Độ bền).

Cả hai đều cho phép dễ dàng tạo bản sao, cả hai đều rất an toàn, cả hai đều có một cộng đồng khổng lồ xung quanh họ cho bất kỳ vấn đề nào bạn có thể gặp phải.

Cả hai đều có một bộ công cụ lớn được xây dựng xung quanh chúng.

BẠn nên chọn cái nào? Khó mà nói ra được.

Trừ khi bạn cần một tính năng cụ thể được triển khai trong một tính năng này và không được triển khai trong tính năng kia, tôi sẽ chọn một tính năng mà bạn đã quen thuộc.

Ví dụ: nếu bạn đến từ WordPress và đã tương tác với MySQL trước đây, bạn có thể chỉ cần sử dụng nó (hoặc MariaDB).

Nó cũng có thể phụ thuộc vào nền tảng bạn muốn triển khai phần mềm của mình. Ví dụ: Heroku cung cấp cơ sở dữ liệu đám mây PostgreSQL được quản lý tích hợp dưới dạng dịch vụ, có thể rất thuận tiện thay vì quản lý máy chủ cơ sở dữ liệu của riêng bạn.

Các hướng dẫn khác về cơ sở dữ liệu:

Phân Biệt Điểm Khác Nhau Giữa Sql Và Nosql

ISBN

:

9780992461225

,

title

:

“JavaScript: Novice to Ninja”

,

author

:

“Darren Jones”

,

format

:

“ebook”

,

price

:

29.00

}

Các tài liệu giống nhau có thể được lưu trữ trong một collection, tương tự như bảng SQL. Tuy nhiên, bạn có thể lưu trữ bất kỳ dữ liệu nào bạn thích trong bất kỳ tài liệu nào; cơ sở dữ liệu NoSQL sẽ không phàn nàn. Ví dụ:

{

ISBN

:

9780992461225

,

title

:

"JavaScript: Novice to Ninja"

,

author

:

"Darren Jones"

,

year

:

2014

,

format

:

"ebook"

,

price

:

29.00

,

description

:

"Learn JavaScript from scratch!"

,

rating

:

"5/5"

,

review

:

[

{

name

:

"A Reader"

,

text

:

"The best JavaScript book I've ever read."

}

,

{

name

:

"JS Expert"

,

text

:

"Recommended to novice and expert developers alike."

}

]

}

Table trong SQL tạo ra data template rất nghiêm ngặt, vì vậy rất khó để ta mắc lỗi. NoSQL thì linh hoạt và dễ sử dụng hơn, nhưng việc có thể lưu trữ bất kỳ dữ liệu nào ở bất kỳ đâu có thể dẫn đến các vấn đề về tính nhất quán.

SQL Schema vs NoSQL Schemaless

Trong cơ sở dữ liệu SQL, không thể thêm dữ liệu cho đến khi bạn xác định bảng và field type trong schema (lược đồ). Schema theo tùy chọn còn có thể chứa các thông tin khác, chẳng hạn như –

primary keys

 — 

unique identifier như ISBN, áp dụng cho một bản ghi (record)

indexes

 —

các trường thường được truy vấn, được lập thành chỉ mục (index) để hỗ trợ tìm kiếm nhanh

relationships

 — liên kết logic giữa các trường dữ liệu

Các chức năng như 

triggers

 và 

stored procedures

.

Lược đồ dữ liệu của bạn phải được thiết kế và triển khai trước khi phát triển bất kỳ business logic nào để thao tác dữ liệu. Bạn hoàn toàn có thể có thể cập nhật sau (nhưng những thay đổi lớn có thể sẽ phức tạp).

Trong cơ sở dữ liệu NoSQL, dữ liệu có thể được thêm vào mọi lúc, mọi nơi. Không cần phải chỉ định trước thiết kế tài liệu (document design) hoặc thậm chí là bộ sưu tập (collection). Ví dụ: trong MongoDB, câu lệnh sau sẽ tạo một tài liệu mới trong một bộ sưu tập book mới nếu nó chưa được tạo trước đó:

db

.

book

.

insert

(

ISBN

:

9780994182654

,

title

:

"Jump Start Git"

,

author

:

"Shaumik Daityari"

,

format

:

"ebook"

,

price

:

29.00

)

;

(MongoDB sẽ tự động thêm một giá trị _id độc nhất vào mỗi tài liệu trong một bộ sưu tập. Bạn có thể vẫn muốn xác định các chỉ mục, nhưng điều đó có thể được thực hiện sau nếu cần.)

Cơ sở dữ liệu NoSQL sẽ phù hợp hơn với các dự án mà các yêu cầu dữ liệu ban đầu khó xác định được. Tuy vậy, đừng dùng sự khó khăn làm lý do cho việc lười biếng: việc lơ là trong việc thiết kế một kho lưu trữ dữ liệu tốt khi bắt đầu dự án sẽ dẫn đến các vấn đề sau này.

SQL Normalization vs NoSQL Denormalization

Giả sử chúng ta muốn thêm thông tin nhà xuất bản vào cơ sở dữ liệu cửa hàng sách. Một nhà xuất bản có thể cung cấp nhiều hơn một tên sách, do đó, trong cơ sở dữ liệu SQL, chúng ta sẽ tạo một bảng publisher mới:

id

name

country

email

SP001

SitePoint

Australia

feedback@sitepoint.com

Sau đó, chúng ta có thể thêm trường publisher_id vào table book của mình, table này sẽ tham chiếu đến các bản ghi theo publisher.id:

ISBN

title

author

format

price

publisher_id

9780992461225

JavaScript: Novice to Ninja

Darren Jones

ebook

29.00

SP001

9780994182654

Jump Start Git

Shaumik Daityari

ebook

29.00

SP001

Để giảm thiểu sự dư thừa dữ liệu; chúng ta không cần lặp lại thông tin nhà xuất bản cho mọi cuốn sách – chỉ cần tham chiếu thôi. Kỹ thuật này được gọi là chuẩn hóa (normalization) và mang đến những lợi ích thiết thực. Chúng ta có thể cập nhật một nhà xuất bản duy nhất mà không cần thay đổi dữ liệu của book.

Chúng ta cũng có thể sử dụng các kỹ thuật chuẩn hóa trong NoSQL. Tài liệu trong collection book :

{

ISBN

:

9780992461225

,

title

:

"JavaScript: Novice to Ninja"

,

author

:

"Darren Jones"

,

format

:

"ebook"

,

price

:

29.00

,

publisher_id

:

"SP001"

}

— tham chiếu một document trong collection publisher :

{

id

:

"SP001"

name

:

"SitePoint"

,

country

:

"Australia"

,

email

:

"feedback@sitepoint.com"

ISBN

:

9780992461225

,

title

:

"JavaScript: Novice to Ninja"

,

author

:

"Darren Jones"

,

format

:

"ebook"

,

price

:

29.00

,

publisher

:

{

name

:

"SitePoint"

,

country

:

"Australia"

,

email

:

"feedback@sitepoint.com"

}

FROM

book

`

ISBN

`

,

`

title

`

,

`

author

`

)

VALUES

(

'9780992461256'

,

'Full Stack JavaScript'

,

'Colin Ihrig & Adam Bretz'

)

;

db

.

book

.

insert

(

{

ISBN

:

"9780992461256"

,

title

:

"Full Stack JavaScript"

,

author

:

"Colin Ihrig & Adam Bretz"

}

)

;

cập nhật đầu mục sách

UPDATE

book

SET

price

=

19.99

WHERE

ISBN

=

'9780992461256'

db

.

book

.

update

(

{

ISBN

:

'9780992461256'

}

,

{

$

set

:

{

price

:

19.99

}

}

)

;

trả kết quả của tất cả đầu sách có giá trên $10

SELECT

title

FROM

book

{

price

:

{

&

gt

;

:

10

}

}

,

{

_id

:

0

,

title

:

1

}

WHERE

publisher_id

=

'SP001'

;

db

.

book

.

count

(

{

"publisher.name"

:

"SitePoint"

}

)

;

Giả định ta đang sử dụng tài liệu không chuẩn hóa.

Trả số kiểu định dạng sách

SELECT

format

,

COUNT

(

1

)

AS

`

total

`

FROM

book

GROUP

BY

format

;

db

.

book

.

aggregate

(

[

{

$group

:

{

_id

:

"$format"

,

total

:

{

$sum

:

1

}

}

}

]

)

;

Đây gọi là aggregation: một bộ document mới được tính toán từ bộ gốc.

Xóa tất cả sách SitePoint

DELETE

FROM

book

WHERE

publisher_id

=

'SP001'

;

db

.

book

.

remove

(

{

"publisher.name"

:

"SitePoint"

Bạn đang xem bài viết Sql:function Và Store Procedure (Hàm/Thủ Tục) Trong Sql trên website Sansangdethanhcong.com. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!