Tạo, thay đổi, thả và thực thi các thủ tục được lưu trữ trên máy chủ SQL – ms sql server tạo thủ tục

Trong mẹo này, chúng tôi xem xét một số ví dụ về cách bắt đầu với việc tạo, sửa đổi và xóa các thủ tục được lưu trữ trên SQL Server.

Bạn đang xem : Thủ tục tạo máy chủ sql ms

Bởi: | Cập nhật: 2019-08-12 | Nhận xét (2) | Liên quan: Thêm & gt; Thủ tục được lưu trữ

Vấn đề

Là một nhà phát triển SQL Server \ DBA mới bắt đầu, tôi có kỹ năng thiết kế và chạy T-SQL theo cách thủ công
các tập lệnh. Tuy nhiên, tôi chưa hiểu rõ về cách đóng gói các tập lệnh T-SQL của mình để dễ dàng
tái sử dụng bởi tôi và những người khác. Vui lòng cung cấp các ví dụ minh họa những điều cơ bản
tạo, thay đổi và chạy các thủ tục được lưu trữ để tạo điều kiện sử dụng lại T-SQL
mã số. Ngoài ra, hãy mô tả ngắn gọn việc sử dụng các tham số đầu vào và đầu ra
như các giá trị mã trả về được liên kết với các thủ tục được lưu trữ. Chứng minh một lưu trữ
thủ tục trả về nhiều hơn một tập kết quả dựa trên giá trị của một tham số đầu vào.

Giải pháp

Mẹo này cung cấp cho bạn giới thiệu nhanh về những điều cơ bản về tạo, thả và
thay đổi các thủ tục được lưu trữ. Bạn cũng sẽ học cách chạy một thủ tục được lưu trữ để
tạo tập hợp kết quả để xem.

Nhiều khối mã T-SQL có thể được chạy từ một thủ tục được lưu trữ. Nó là phổ biến
để kiểm tra phiên bản mã ban đầu bên trong tệp kịch bản T-SQL và sau đó sao chép
mã vào phần thân của một trình bao thủ tục được lưu trữ.

Sau khi tạo hoặc sửa đổi một thủ tục được lưu trữ có chứa một hoặc nhiều SELECT
, bạn có thể gọi thủ tục được lưu trữ bằng câu lệnh EXEC. Do đó,
bạn có thể nghĩ về một thủ tục được lưu trữ như một vùng chứa tạo điều kiện cho việc tái sử dụng
Mã T-SQL bên trong nó.

Tổng quan về các thủ tục được lưu trữ trên máy chủ SQL

Một thủ tục được lưu trữ là một khối mã T-SQL đã lưu, chẳng hạn như một truy vấn để liệt kê
các hàng trong một bảng. Một khối mã T-SQL có thể được lưu trong tệp kịch bản T-SQL.
Bạn cũng có thể lưu trữ mã từ một tệp kịch bản trong một quy trình được lưu trữ.

Có một số lợi ích từ việc lưu mã trong một quy trình được lưu trữ
chứ không phải là một tệp kịch bản. Đây là một số ví dụ.

  • Bạn không cần để lộ mã trong một quy trình được lưu trữ để chạy
    mã T-SQL của nó. Ngược lại, người dùng cần mở một tệp script với
    mã để chạy mã.
  • Các thủ tục được lưu trữ cũng cung cấp một phương tiện hạn chế quyền truy cập vào
    bảng cho một truy vấn. Bằng cách cấp quyền truy cập để chạy các thủ tục được lưu trữ mà không cần
    quyền đọc hoặc ghi vào các bảng bên dưới, bạn có thể bảo mật dữ liệu nhưng
    vẫn cho phép hiển thị dữ liệu trong các bảng bên dưới thông qua một thủ tục được lưu trữ.
  • Bạn có thể sử dụng các tham số đầu vào với các thủ tục được lưu trữ để thay đổi hoạt động
    của mã bên trong một thủ tục được lưu trữ. Mặc dù các tệp kịch bản cho phép
    sử dụng các biến cục bộ để sửa đổi tập hợp trả về từ các truy vấn, tệp tập lệnh
    phải hiển thị mã của họ để cho phép bạn sửa đổi các biến cục bộ tại thời điểm chạy.
  • Bằng cách đạt được sự thành thạo trong việc phân đoạn giải pháp lập trình thành các phần dựa trên
    trên các thủ tục được lưu trữ, bạn có thể dễ dàng thay đổi mã hơn theo thời gian. Qua
    thêm mã trong các tập lệnh mô-đun ngắn, mỗi tập lệnh có thể dễ đọc hơn và
    duy trì và thậm chí sử dụng lại trong các ứng dụng khác. Các giải pháp dựa trên SQL
    các tệp có tập lệnh cho các truy vấn có thể ngày càng dài, khó đọc,
    và duy trì khi các thay đổi liên tiếp tiếp tục được thực hiện đối với một giải pháp.

Các thủ tục được lưu trữ giới thiệu mức độ trừu tượng giữa mã cho một giải pháp
và sử dụng mã không xuất hiện khi bạn duy trì mã của mình trong tệp tập lệnh.
Do đó, nếu bạn có một giải pháp đơn giản được sử dụng bởi một người dùng cần phải có
truy cập vào các nguồn dữ liệu cơ bản cho một truy vấn (hoặc một tập hợp các truy vấn), sau đó là một tập lệnh
tệp có thể tốt hơn vì nó đơn giản hóa giải pháp.

Tạo thủ tục lưu trữ trên máy chủ SQL mới

Nhiều DBA đã quen thuộc với việc tạo bảng thông qua câu lệnh CREATE TABLE.
Tương tự, các DBA của nhà phát triển cũng có thể tạo một thủ tục được lưu trữ bằng CREATE PROC
hoặc câu lệnh CREATE PROCEDURE. Cũng giống như câu lệnh tạo bảng, thêm một
bảng vào cơ sở dữ liệu để câu lệnh CREATE PROC thêm một thủ tục được lưu trữ vào
Một cơ sở dữ liệu. Vì vậy, bạn cần bắt đầu với cơ sở dữ liệu mới hoặc cơ sở dữ liệu hiện có khi bạn
muốn tạo một thủ tục được lưu trữ, vì thủ tục được lưu trữ thực sự được lưu trữ
trong cơ sở dữ liệu.

Nếu bạn có quyền thích hợp, bạn có thể sử dụng câu lệnh TẠO CƠ SỞ DỮ LIỆU
để tạo một cơ sở dữ liệu mới để chứa các bảng và các loại đối tượng khác, chẳng hạn như được lưu trữ
các thủ tục. Câu lệnh tạo cơ sở dữ liệu thường bị hạn chế đối với một số lần đăng nhập
tài khoản trên phiên bản SQL Server.

Tập lệnh sau tạo cơ sở dữ liệu có tên CodeModuleTypes. Đầu tiên của nó
câu lệnh chỉ định cơ sở dữ liệu chính làm cơ sở dữ liệu mặc định. Thứ hai của nó
câu lệnh tạo cơ sở dữ liệu. Có thể có nhiều công phu hơn
phiên bản của

TẠO CƠ SỞ DỮ LIỆU
tùy thuộc vào nhu cầu của bạn. Trừ khi bạn chỉ định khác, một câu lệnh tạo cơ sở dữ liệu đơn giản như dưới đây
sẽ sử dụng cài đặt mặc định từ cơ sở dữ liệu mô hình, đây là một trong những tiêu chuẩn
cơ sở dữ liệu cài đặt với SQL Server.

sử dụng chính chủ;
ĐI
 
tạo cơ sở dữ liệu CodeModuleTypes;

Sau khi bạn có một cơ sở dữ liệu, chẳng hạn như CodeModuleTypes, bạn có thể gọi một CREATE
PROC
câu lệnh trong cơ sở dữ liệu đó. Tất cả các thủ tục được lưu trữ được tạo trong mẹo này
được lưu trong cơ sở dữ liệu CodeModuleTypes.

Tập lệnh sau trình bày cú pháp mà bạn có thể sử dụng để tạo
thủ tục được lưu trữ. Quy trình được lưu trữ trong đoạn mã dưới đây sẽ hiển thị một kết quả
đặt với tất cả các cột cho mỗi hàng từ bảng Nhân viên trong Nguồn nhân lực
giản đồ của cơ sở dữ liệu AdventureWorks2014.

Xem Thêm  Cách căn giữa một hình ảnh trong HTML & CSS - cách căn giữa html hình ảnh

Bạn có thể coi giản đồ như một cách để nhóm các đối tượng cơ sở dữ liệu một cách hợp lý, chẳng hạn như
dưới dạng các bảng và các thủ tục được lưu trữ. Các nhóm hợp lý này tránh xung đột tên
giữa các đối tượng có cùng tên trong các lược đồ khác nhau. Bất kỳ một cơ sở dữ liệu nào cũng có thể
có nhiều lược đồ. Trong mẹo này, tất cả các thủ tục được lưu trữ được chỉ định là
thuộc về lược đồ dbo của cơ sở dữ liệu CodeModuleTypes.

Câu lệnh CREATE PROC dưới đây có ba phần.

  • Câu lệnh CREATE PROC đặt tên cho thủ tục được lưu trữ (và lược đồ của nó nếu
    bạn đang chỉ định nó một cách rõ ràng).
  • Từ khoá as hoạt động như một điểm đánh dấu để biểu thị rằng mã xác định cho
    thủ tục được lưu trữ sắp bắt đầu.
  • Mã T-SQL xác định hoạt động của thủ tục được lưu trữ. Trong
    ví dụ này, mã xác định là câu lệnh SELECT cho bảng Employee
    trong lược đồ HumanResources của cơ sở dữ liệu AdventureWorks2014. Đây

    Mẹo MSSQLTips.com trước đây
    mô tả cách tải xuống bản sao của cơ sở dữ liệu AdventureWorks2014.
sử dụng CodeModuleTypes;
đi
 
tạo proc dbo.uspMyFirstStoredProcedure
như
lựa chọn *
từ AdventureWorks2014.HumanResources.Eprisee;

Sau khi bạn tạo một thủ tục được lưu trữ, bạn có thể chạy nó với một câu lệnh EXEC như
cái bên dưới. Đó là câu lệnh này trả về tập kết quả với tất cả các cột
cho mỗi hàng từ bảng Nhân viên.

executive dbo.uspMyFirstStoredProcedure

Đây là đoạn trích từ kết quả được tạo bởi tập lệnh trước.

  • Ngăn Kết quả hiển thị mười một cột đầu tiên từ mười bảy hàng đầu tiên
    trong số 290 nhân viên tại công ty AdventureWorks.
  • Nếu bạn muốn xử lý các hàng được hiển thị bằng câu lệnh SELECT trong
    một thủ tục được lưu trữ, sau đó bạn sẽ cần lưu trữ các hàng tập hợp kết quả trong một số
    bảng hoặc đối tượng SQL Server khác. Tiếp theo, xử lý kết quả trong đối tượng đó.

Thả hoặc xóa một thủ tục được lưu trữ trên máy chủ SQL

Tập lệnh trước để tạo thủ tục được lưu trữ sẽ không thành công nếu uspMyFirstStoredProcedure
thủ tục được lưu trữ trong lược đồ dbo đã tồn tại. Một phản hồi cho vấn đề này
là bỏ phiên bản trước của thủ tục được lưu trữ và sau đó chạy lại tập lệnh
để tạo phiên bản mới của thủ tục được lưu trữ. Bạn có thể xóa cái trước
phiên bản của thủ tục lưu trữ uspMyFirstStoredProcedure với DROP PROC hoặc
Tuyên bố DROP PROCEDURE.
Dòng sau của T-SQL minh họa việc sử dụng câu lệnh drop proc cho
uspMyFirstStoredProcedure thủ tục được lưu trữ.

thả proc dbo.uspMyFirstStoredProcedure

Thay vì cho phép câu lệnh CREATE PROC bị lỗi khi có phiên bản trước
của một proc được lưu trữ, thông thường là kiểm tra xem thủ tục được lưu trữ có tồn tại hay không
đã và xóa nó để tránh lỗi trước khi chạy câu lệnh CREATE PROC
cho một phiên bản mới của thủ tục được lưu trữ. Tập lệnh sau có thể được sử dụng
để thả phiên bản trước của thủ tục được lưu trữ uspMyFirstStoredProcedure nếu nó
đã tồn tại. Bạn có thể chạy một tập lệnh như thế này trước khi gọi một CREATE
PROC
bản tường trình. Tùy thuộc vào yêu cầu của bạn và mã xác định
thủ tục, có thể có lợi nếu đặt lại tên cho thủ tục được lưu trữ hiện tại
thay vì bỏ nó.

- thả một cách có điều kiện một proc đã lưu trữ
nếu object_id ('dbo.uspMyFirstStoredProcedure') không null
     thả proc dbo.uspMyFirstStoredProcedure
đi

Thay đổi hoặc sửa đổi thủ tục lưu trữ trên máy chủ SQL hiện có

Khối mã tiếp theo minh họa câu lệnh ALTER PROC. ALTER
PROC
câu lệnh khác với câu lệnh CREATE PROC ở chỗ câu lệnh ALTER PROC
chỉ có thể hoạt động trên một quy trình được lưu trữ hiện có.

  • Tập lệnh này được thiết kế để chạy ngay sau tập lệnh trước đó
    xóa uspMyFirstStoredProcedure nếu nó đã tồn tại.
  • Hai câu lệnh đầu tiên trong khối mã sau là CREATE PROC và
    Các câu lệnh EXEC có thể tạo một bản sao mới của thủ tục được lưu trữ và chạy uspMyFirstStoredProcedure.
    Bởi vì tất cả mã xuất hiện trong một khối duy nhất, ba từ khóa GO là bắt buộc
    điều đó sẽ không cần thiết nếu toàn bộ khối mã được phân đoạn thành bốn
    các khối mã riêng biệt.

    • Câu lệnh CREATE PROC ban đầu cần được theo sau bởi một từ khóa GO
      để câu lệnh CREATE PROC hoàn thành trước câu lệnh EXEC đầu tiên.
    • Sau đó, câu lệnh EXEC ban đầu cần được theo sau bởi một từ khóa GO
      để câu lệnh ALTER PROC là câu lệnh đầu tiên trong lô của nó.
    • Cuối cùng, câu lệnh ALTER PROC cần phải được theo sau bởi một từ khóa GO
      để câu lệnh ALTER PROC hoàn thành trước câu lệnh EXEC cuối cùng.
  • Cũng giống như câu lệnh CREATE PROC, câu lệnh ALTER PROC có ba
    các bộ phận.

    • Tên đối tượng sau ALTER PROC phải khớp với tên của đối tượng hiện có
      thủ tục đã lưu trữ mà bạn muốn thay đổi.
    • Từ khoá as hoạt động như một dấu phân cách phân tách các khai báo ALTER PROC
      từ mã T-SQL mới xác định phiên bản sửa đổi của uspMyFirstStoredProcedure.
    • Mã mới bên trong câu lệnh ALTER PROC tham gia vào bảng Person
      từ giản đồ Người sang bảng Nhân viên từ lược đồ Nguồn nhân lực
      trong cơ sở dữ liệu AdventureWorks2014.
- tạo một proc mới được lưu trữ
tạo proc dbo.uspMyFirstStoredProcedure
như
lựa chọn *
từ AdventureWorks2014.HumanResources.Eprisee
đi
 
- chạy chương trình đã lưu trữ
executive dbo.uspMyFirstStoredProcedure
đi
 
- thay đổi proc được lưu trữ
thay đổi proc dbo.uspMyFirstStoredProcedure
như
lựa chọn
 Employee.BusinessEntityID
, Person.FirstName
, Person.LastName
, Employee.JobTitle
từ AdventureWorks2014.HumanResources.Eprisee
tham gia bên trong AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityID
đi
 
- chạy proc được lưu trữ đã thay đổi
executive dbo.uspMyFirstStoredProcedure

Đây là kết quả từ câu lệnh EXEC trong tập lệnh trước.

  • Các cột BusinessEntityID và JobTitle là từ bảng Nhân viên.
  • Cột FirstName và LastName lấy từ bảng Person.

Ngoài sự khác biệt rõ ràng của việc tạo một quy trình mới được lưu trữ và sửa đổi
một thủ tục được lưu trữ hiện có, câu lệnh ALTER PROC khác với câu lệnh
TẠO câu lệnh PROC theo những cách quan trọng khác. Ví dụ, câu lệnh ALTER PROC
giữ nguyên tất cả các cài đặt bảo mật được liên kết với một proc được lưu trữ hiện có, nhưng
Câu lệnh CREATE PROC không bảo toàn các cài đặt này. Bằng cách này,
Câu lệnh ALTER PROC tốt hơn câu lệnh CREATE PROC khi tất cả những gì bạn muốn làm
là thay đổi mã trong một quy trình được lưu trữ hiện có.

Bắt đầu với SQL Server 2016 SP1, Microsoft đã giới thiệu một tạo mới hoặc thay đổi
câu lệnh cho các mô-đun mã, chẳng hạn như các thủ tục được lưu trữ, các khung nhìn và các chức năng do người dùng xác định.
Bạn có thể nhận được giới thiệu về chức năng của tuyên bố mới này từ

mẹo MSSQLTips.com trước
.

Tham số đầu vào thủ tục được lưu trữ trên máy chủ SQL

Tham số đầu vào cho phép nhà phát triển thay đổi cách hoạt động của một quy trình được lưu trữ
trong thời gian chạy. Thông thường sử dụng một tham số đầu vào trong mệnh đề where
của một câu lệnh SELECT để kiểm soát các hàng hiển thị khi một thủ tục được lưu trữ chạy.
Xem cái này

Trang hướng dẫn MSSQLTips.com

để trình bày về cách sử dụng các tham số đầu vào trong mệnh đề where.

Xem Thêm  Vòng lặp trong Java - cho tôi trong java

Đây là một ví dụ đơn giản dựa trên sự thay đổi đối với uspMyFirstStoredProcedure
điều đó chứng tỏ việc sử dụng một tham số đầu vào.

  • Tham số đầu vào có tên @jobtitle được đặt tên ngay trước từ khóa as.
  • Tham số đầu vào được tham chiếu trong mệnh đề where của câu lệnh SELECT.
  • Câu lệnh EXEC theo sau câu lệnh ALTER PROC chỉ định một giá trị
    đến tham số đầu vào @jobtitle tại thời điểm chạy. Giá trị tham số là một chuỗi nvarchar (Giám sát sản xuất).
- thay đổi quy trình được lưu trữ - thay đổi này có một câu lệnh chọn với mệnh đề where
- và một tiêu chí được đặt bởi một tham số đầu vào
- và một tham số đầu vào
thay đổi proc dbo.uspMyFirstStoredProcedure
@jobtitle nvarchar (50)
như
 
lựa chọn
 Employee.BusinessEntityID
, Person.FirstName
, Person.LastName
, Employee.JobTitle
từ AdventureWorks2014.HumanResources.Eprisee
tham gia bên trong AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityID
nơi Employee.JobTitle như @jobtitle + '%'
đi
 
- chạy proc được lưu trữ đã thay đổi với
- Giá trị tham số @jobtitle được truyền mà không cần đặt tên cho tham số
thực thi uspMyFirstStoredProcedure N'Production Supervisor '

Đây là tab Kết quả hiển thị kết quả từ câu lệnh SELECT bên trong
uspMyFirstStoredProcedure. Tab này hiển thị 21 nhân viên có công việc
chức danh bắt đầu bằng Giám sát sản xuất. Bạn có thể thay đổi nội dung của
Tab kết quả bằng cách sử dụng giá trị chuỗi ký tự khác trong câu lệnh EXEC.

Tham số đầu ra thủ tục được lưu trữ của SQL Server

Một tham số đầu ra chuyển một giá trị vô hướng từ một thủ tục được lưu trữ tới EXEC
tuyên bố gọi nó. Một số phạm vi MSSQLTips.com trước đó về chủ đề này xuất hiện
trong này

trang hướng dẫn
. Ngoài ra,
một mẹo tiếp theo trong loạt bài này về các thủ tục được lưu trữ sẽ trình bày rất nhiều chi tiết
ví dụ minh họa cách lập trình tham số đầu vào, tham số đầu ra và trả về
giá trị mã với các thủ tục được lưu trữ.

Khi bạn chỉ muốn chuyển một giá trị duy nhất, chẳng hạn như tổng hoặc số, từ một
thủ tục, bạn có thể làm điều đó với một tham số đầu ra. ALTER sau
PROC
tuyên bố minh họa một cách thực hiện loại nhiệm vụ này.

  • Câu lệnh ALTER PROC lại thay đổi uspMyFirstStoredProcedure.
  • Tham số đầu vào (@jobtitle) từ phiên bản trước của quy trình được lưu trữ
    được giữ lại.
  • Ngoài ra, đặc tả tham số đầu ra được thêm vào trước từ khóa as.
    • Tên thông số đầu ra là @jobtitlecount.
    • Kiểu dữ liệu cho tham số là int vì nó được dùng để lưu trữ
      một giá trị đếm, nhưng bạn cũng có thể sử dụng bigint làm kiểu dữ liệu nếu nó được yêu cầu.
    • Từ khoá out kết thúc đặc tả tham số để chỉ ra rằng điều này
      tham số trả về một giá trị sau khi thủ tục được lưu trữ chạy.
  • Câu lệnh SELECT bao gồm một câu lệnh select bên trong lồng nhau trong một bên ngoài
    câu lệnh chọn.

    • Câu lệnh select bên trong trả về một hàng cho mỗi nhân viên có JobTitle
      bắt đầu bằng giá trị của tham số đầu vào.
    • Câu lệnh chọn bên ngoài đếm số hàng được trả về bởi
      câu lệnh chọn bên trong và gán số lượng cho tham số đầu ra (@jobtitlecount).
- thay đổi một proc được lưu trữ
- thay đổi này tính giá trị hàm tổng hợp
- dựa trên một phần tham số đầu vào (@jobtitle)
- và lưu giá trị đã tính trong một tham số đầu ra (@jobtitlecount)
thay đổi proc dbo.uspMyFirstStoredProcedure
@jobtitle nvarchar (50), @jobtitlecount int out
như
 
chọn @jobtitlecount = count (*)
từ AdventureWorks2014.HumanResources.Eprisee
tham gia bên trong AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityID
nơi Employee.JobTitle như @jobtitle + '%'

Sau khi câu lệnh ALTER PROC chạy, bạn có thể gọi phiên bản mới được sửa đổi
của uspMyFirstStoredProcedure và hiển thị giá trị của tham số đầu ra.
Đoạn mã sau đây chỉ ra cách đạt được điều này.

  • Trước khi gọi câu lệnh EXEC để chạy uspMyFirstStoredProcedure, hãy khai báo
    một biến cục bộ (@jobtitlecount) để nhận giá trị tham số đầu ra từ
    thủ tục được lưu trữ.

    • Giá trị tham số đầu ra xuất hiện trong câu lệnh EXEC với dấu
      từ khóa đầu ra (OUTPUT). Từ khóa này cho biết giá trị tham số
      được chuyển từ thủ tục đã lưu trữ sang câu lệnh EXEC.
    • Một toán tử gán (=) chuyển giá trị tham số đầu ra cho
      @jobtitlecount biến cục bộ.
  • Câu lệnh SELECT sau câu lệnh EXEC hiển thị giá trị của @jobtitlecount
    biến cục bộ đã nhận giá trị tham số đầu ra. Cho bạn dễ dàng
    tham chiếu, giá trị tham số đầu ra được hiển thị bởi tập lệnh trước là 21.
- chạy một proc được lưu trữ đã thay đổi với - giá trị tham số đầu vào @jobtitle và
- lưu tham số đầu ra trả về trong một biến cục bộ

khai báo @jobtitlecount int

executive uspMyFirstStoredProcedure N'Production Supervisor ', @ jobtitlecount = @jobtitlecount OUTPUT

chọn @jobtitlecount [Đếm chức danh]

Giá trị mã trả về thủ tục được lưu trữ trên SQL Server

Các thủ tục đã lưu trữ có thể có các giá trị mã trả về. Trả lại các giá trị mã luôn
có kiểu dữ liệu int.

Đây là tập lệnh để đặt mã trả về bằng 0 hoặc một mã từ bên trong
thủ tục. Nếu tồn tại tiêu chí chuỗi tìm kiếm dựa trên tham số đầu vào
trong một cột, thì giá trị trả về được đặt thành một. Nếu không, giá trị trả về
được đặt thành 0.

  • Tham số đầu vào có tên @jobtitle.
  • Tiêu chí mệnh đề where trong câu lệnh SELECT là: Employee.JobTitle
    như ‘%’ + @jobtitle
    + ‘%’
  • Khi câu lệnh SELECT với mệnh đề where trả về ít nhất một hàng,
    thì giá trị trả về được đặt thành một. Nếu không, giá trị trả về được đặt
    về 0.
  • Điều kiện EXISTS xác định xem có ít nhất một hàng được trả về hay không từ
    câu lệnh SELECT.
  • Một mệnh đề trả về chuyển lại một giá trị mã trả về và thoát khỏi thủ tục đã lưu trữ.
- thay đổi một proc được lưu trữ
- thay đổi này xác minh xem một giá trị chuỗi tìm kiếm
- nằm trong một tập hợp các giá trị cột
- Tham số đầu vào @jobtitle chứa giá trị chuỗi tìm kiếm
- JobTitle là cột giá trị được tìm kiếm
thay đổi proc dbo.uspMyFirstStoredProcedure
@jobtitle nvarchar (50)
như
 
- có ít nhất một JobTitle chứa @jobtitle không?
nếu tồn tại (
   chọn 1 Nhân viên hàng đầu.JobTitle
   từ AdventureWorks2014.HumanResources.Eprisee
   tham gia bên trong AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityID
   nơi Employee.JobTitle như '%' + @jobtitle + '%'
)
bắt đầu
   trở lại (1)
chấm dứt
khác
bắt đầu
   trở lại (0)
chấm dứt

Tập lệnh sau trình bày cú pháp để thu thập giá trị mã trả lại
từ một thủ tục được lưu trữ và hiển thị một số mã mẫu để xử lý mã trả lại
giá trị. Tập lệnh gọi uspMyFirstStoredProcedure cho hai giá trị khác nhau
của @jobtitle – sals hoặc bán hàng. Không có giá trị cột JobTitle nào chứa
sals, nhưng ít nhất một JobTitle chứa doanh số bán hàng.

  • Đầu tiên, mã khai báo hai biến cục bộ có tên @jobtitle và @exists.
    • Biến cục bộ @jobtitle được sử dụng trong một câu lệnh EXEC để chuyển một
      chuỗi tìm kiếm tới uspMyFirstStoredProcedure.
    • Biến cục bộ @exists được sử dụng để thu thập giá trị mã trả về
      từ uspMyFirstStoredProcedure. Một câu lệnh gán bên trong
      câu lệnh EXEC điền biến cục bộ @exists với giá trị trả về
      giá trị mã.
    • Câu lệnh luồng điều khiển if … else sau khi câu lệnh EXEC xử lý
      giá trị trả về từ thủ tục được lưu trữ.

      • Nếu @exists bằng 0, câu lệnh SELECT báo cáo rằng có
        không có JobTitle với giá trị chuỗi tìm kiếm trong tham số đầu vào.
      • Nếu @exists bằng một, một câu lệnh SELECT báo cáo rằng có
        ít nhất một giá trị JobTitle với tham số đầu vào.
  • Dưới đây, thủ tục được lưu trữ được thực thi hai lần. Quá trình thực thi ban đầu dành cho
    giá trị chuỗi tìm kiếm của sals. Lần thực thi thứ hai dành cho một chuỗi tìm kiếm
    giá trị bán hàng.
- chạy một chương trình đã được lưu trữ đã thay đổi với
- @jobtitle là một tham số đầu vào
- @exists bằng 1 cho ít nhất 1 JobTitle chứa @jobTitle
- @exists bằng 0 nếu không có JobTitle nào chứa @jobtitle
khai báo @jobtitle nvarchar (50), @exists int
 
đặt @jobtitle = 'sals'
executive @exists = uspMyFirstStoredProcedure @jobtitle
nếu @exists = 0
bắt đầu
   chọn 'Không có giá trị JobTitle với' + @jobtitle [kết quả tìm kiếm]
chấm dứt
khác
bắt đầu
   chọn 'Ít nhất một giá trị JobTitle với' + @jobtitle [kết quả tìm kiếm]
chấm dứt
 
set @jobtitle = 'sales'
executive @exists = uspMyFirstStoredProcedure @jobtitle
nếu @exists = 0
bắt đầu
   chọn 'Không có giá trị JobTitle với' + @jobtitle [kết quả tìm kiếm]
chấm dứt
khác
bắt đầu
   chọn 'Ít nhất một giá trị JobTitle với' + @jobtitle [kết quả tìm kiếm]
chấm dứt

Đây là kết quả từ tập lệnh trước. Bạn có thể sử dụng nó để xác nhận
hoạt động của mã để đánh giá xem có ít nhất một giá trị trong một cột chứa
một chuỗi tìm kiếm.

Xem Thêm  Menu điều hướng trên thiết bị di động với HTML và CSS - menu điều hướng di động css

Nhiều bộ kết quả từ một thủ tục được lưu trữ trên máy chủ SQL

Tập lệnh sau trình bày lại cách sử dụng tham số đầu vào trong
Câu lệnh ALTER PROC. Tên tham số đầu vào trước từ khóa as là @jobtitle.
Trình diễn này đặc biệt ở chỗ nó bao gồm hai câu lệnh SELECT riêng biệt.
Câu lệnh SELECT đầu tiên trả về một tập hợp kết quả bao gồm tất cả các hàng có JobTitle
bắt đầu bằng giá trị trong tham số đầu vào. Câu lệnh SELECT thứ hai
trả về một giá trị vô hướng là số lượng nhân viên trong Nhân viên
bảng có JobTitle bắt đầu bằng giá trị tham số đầu vào.

Câu lệnh EXEC sau câu lệnh ALTER PROC gọi uspMyFirstStoredProcedure.
Giá trị chuỗi ký tự nvarchar (Giám sát sản xuất) sau quy trình được lưu trữ
tên là giá trị tham số đầu vào.

- thay đổi một proc được lưu trữ - thay đổi này có hai câu lệnh chọn
- và một tham số đầu vào
thay đổi proc dbo.uspMyFirstStoredProcedure
@jobtitle nvarchar (50)
như
 
- Câu lệnh lựa chọn đầu tiên trả về một tập hợp các giá trị hàng
lựa chọn
   Employee.BusinessEntityID
  , Person.FirstName
  , Person.LastName
  , Employee.JobTitle
từ AdventureWorks2014.HumanResources.Eprisee
tham gia bên trong AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityID
nơi Employee.JobTitle như @jobtitle + '%'
 
- Câu lệnh select thứ 2 trả về một giá trị vô hướng
chọn số lượng (*) làm JobTitleCount
từ AdventureWorks2014.HumanResources.Eprisee
tham gia bên trong AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityID
nơi Employee.JobTitle như @jobtitle + '%'

Đây là một đoạn script ngắn để gọi phần trước
thủ tục được lưu trữ.

- chạy proc được lưu trữ đã thay đổi
- Giá trị tham số @jobtitle được truyền mà không cần đặt tên cho tham số
 
executive dbo.uspMyFirstStoredProcedure N'Production Supervisor '

Đây là tab Kết quả hiển thị kết quả từ hai câu lệnh SELECT
bên trong thủ tục được lưu trữ có tên uspMyFirstStoredProcedure. Bạn có thể thay đổi
nội dung của tab Kết quả bằng cách sử dụng một giá trị chuỗi ký tự khác trong
Tuyên bố EXEC.

  • Khung trên cùng hiển thị 21 nhân viên có chức danh bắt đầu bằng
    Giám sát sản xuất.
  • Khung dưới cùng hiển thị một giá trị vô hướng với số lượng nhân viên có công việc
    các chức danh bắt đầu bằng Giám sát sản xuất.
Các bước tiếp theo
  • Bạn có thể chạy mã cho mẹo này trên máy tính có AdventureWorks2014
    cơ sở dữ liệu mặc dù các phiên bản khác của cơ sở dữ liệu có khả năng mang lại kết quả tương tự
    kết quả (nếu bạn cập nhật tham chiếu mệnh đề from từ AdventureWorks2014 thành
    bất kỳ phiên bản nào khác của cơ sở dữ liệu AdventureWorks mà bạn sử dụng để kiểm tra điều này
    kịch bản của mẹo).
  • Tiếp theo, sao chép tập lệnh mà bạn muốn kiểm tra từ mẹo này để xác nhận rằng
    bạn sẽ nhận được cùng một kết quả được báo cáo trong mẹo.
  • Cuối cùng, sửa đổi tập lệnh để hoạt động trong một cơ sở dữ liệu khác mà bạn chọn để
    bắt đầu tạo và chạy các thủ tục được lưu trữ với các tập lệnh trong cơ sở dữ liệu của bạn.

Các bài viết liên quan

Các bài báo phổ biến

Giới thiệu về tác giả

Xem tất cả các mẹo của tôi
Rick Dobson là Chuyên gia kỹ thuật được chứng nhận của Microsoft, đồng thời là tác giả SQL Server và Access hoàn thành tốt.
< br />
Bài viết cập nhật lần cuối: 2019-08-12


Xem thêm những thông tin liên quan đến chủ đề ms sql server tạo thủ tục

Bài 5: Giới thiệu về thủ tục lưu trữ (Stored Procedure) trong SQL Server

  • Tác giả: Anh Nguyen Ngoc
  • Ngày đăng: 2020-06-05
  • Đánh giá: 4 ⭐ ( 9231 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Quản trị Cơ sở dữ liệu SQL Server playlist: https://www.youtube.com/playlist?list=PLn9lhDYvf_3Gdiz6uY9HAztCKapCvzTPe
    Nhóm thảo luận: https://www.facebook.com/groups/115838366528104

PROCEDURE (Thủ tục) trong SQL Server

  • Tác giả: nq.com.vn
  • Đánh giá: 3 ⭐ ( 6815 lượt đánh giá )
  • Khớp với kết quả tìm kiếm:

SQL Server 2000

  • Tác giả: monhoc.vn
  • Đánh giá: 5 ⭐ ( 3485 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Thủ tục nội tại thật sự là một tập hợp chứa các dòng lệnh, các biến và các cấu trúc điều khiển bên trong ngôn ngữ Transaction-SQL dùng để thực hiện một hành độn

Hướng dẫn tạo sử dụng Stored procedure trong SQL Server

  • Tác giả: shareprogramming.net
  • Đánh giá: 4 ⭐ ( 8587 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Stored Procedure là một tập hợp các câu lệnh SQL dùng để thực thi một nhiệm vụ nhất định. Nó có thể xem như một hàm trong các ngôn ngữ lập trình.

Hệ quản trị SQL Server

  • Tác giả: www.hiepsiit.com
  • Đánh giá: 5 ⭐ ( 7415 lượt đánh giá )
  • Khớp với kết quả tìm kiếm:

PROCEDURE (Thủ tục) trong SQL Server

  • Tác giả: mocnoi.com
  • Đánh giá: 4 ⭐ ( 1920 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: PROCEDURE (Thủ tục) trong SQL Server. Khoa học và cuộc sống

Bài 12: Thủ tục lưu trú, hàm và trigger

  • Tác giả: www.zun.vn
  • Đánh giá: 3 ⭐ ( 3500 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Mục tiêu: – Kỹ băng: – Viết thủ tục lưu trú để giải quyết một vấn đề được đăt ra về CSDL. Bao gồm cả việc kiểm chứng dữ liệu khi cần thiết. – Viết các hàm

Xem thêm các bài viết khác thuộc chuyên mục: Kiến thức lập trình