淺談sql server資料庫的預存程序

1、什麼是預存程序？

預存程序（Stored Procedure）是在大型資料庫系統中，一組為了完成特定功能的SQL 陳述式集，經編譯後儲存在資料庫中，使用者通過指定預存程序的名字並給出參數（如果該預存程序帶有參數）來執行它。

2、預存程序的優缺點？

優點：

預存程序的能力大大增強了SQL語言的功能和靈活性。預存程序可以用流量控制語句編寫，有很強的靈活性，可以完成複雜的判斷和較複雜的 運算。

可保證資料的安全性和完整性。

通過預存程序可以使沒有許可權的使用者在控制之下間接地存取資料庫，從而保證資料的安全。

通過預存程序可以使相關的動作在一起發生，從而可以維護資料庫的完整性。 缺點：調試麻煩，但是用 PL/SQL Developer 調試很方便！彌補這個缺點。 移植問題，資料庫端代碼當然是與資料庫相關的。但是如果是做工程型項目，基本不存在移植問題。 重新編譯問題，因為後端代碼是運行前編譯的，如果帶有參考關聯性的對象發生改變時，受影響的預存程序、包將需要重新編譯（不過也可以設定成運行時刻自動編譯）。  如果在一個程式系統中大量的使用預存程序，到程式交付使用的時候隨著使用者需求的增加會導致資料結構的變化，接著就是系統的相關問題了，最後如果使用者想維護該系統可以說是很難很難、而且代價是空前的，維護起來更麻煩。

 

3、預存程序的簡單格式

CREATE PROCEDURE [擁有者.]預存程序名[;程式編號] sql中的預存程序及相關介紹[(參數#1,…參數#1024)][WITH{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}][FOR REPLICATION]AS 程式行其中預存程序名不能超過128個字。每個預存程序中最多設定1024個參數(SQL Server 7.0以上版本),參數的使用方法如下：@參數名資料類型[VARYING] [=內定值] [OUTPUT]每個參數名前要有一個“@”符號,每一個預存程序的參數僅為該程式內部使用,參數的類型除了IMAGE外，其他SQL Server所支援的資料類型都可使用。[=內定值]相當於我們在建立資料庫時設定一個欄位的預設值，這裡是為這個參數設定預設值。[OUTPUT]是用來指定該參數是既有輸入又有輸出值的，也就是在調用了這個預存程序時，如果所指定的參數值是我們需要輸入的參數，同時也需要在結果中輸出的，則該項必須為OUTPUT，而如果只是做輸出參數用，可以用CURSOR，同時在使用該參數時，必須指定VARYING和OUTPUT這兩個語句。

 

----- 使用者自訂預存程序

   1、 建立文法

create proc | procedure pro_name
    [{@參數資料類型} [=預設值] [output],
     {@參數資料類型} [=預設值] [output],
     ....
    ]
as
    SQL_statements

 

   2、 建立不帶參數預存程序

--建立預存程序
if (exists (select * from sys.objects where name = 'proc_get_student'))
    drop proc proc_get_student
go
create proc proc_get_student
as
    select * from student;

--調用、執行預存程序
exec proc_get_student;

   3、 修改預存程序

--修改預存程序
alter proc proc_get_student
as
select * from student;

   4、 帶參預存程序

--帶參預存程序
if (object_id('proc_find_stu', 'P') is not null)
    drop proc proc_find_stu
go
create proc proc_find_stu(@startId int, @endId int)
as
    select * from student where id between @startId and @endId
go

exec proc_find_stu 2, 4;

   5、 帶萬用字元參數預存程序

--帶萬用字元參數預存程序
if (object_id('proc_findStudentByName', 'P') is not null)
    drop proc proc_findStudentByName
go
create proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')
as
    select * from student where name like @name and name like @nextName;
go

exec proc_findStudentByName;
exec proc_findStudentByName '%o%', 't%';

   6、 帶輸出參數預存程序

if (object_id('proc_getStudentRecord', 'P') is not null)
    drop proc proc_getStudentRecord
go
create proc proc_getStudentRecord(
    @id int, --預設輸入參數
    @name varchar(20) out, --輸出參數
    @age varchar(20) output--輸入輸出參數
)
as
    select @name = name, @age = age  from student where id = @id and sex = @age;
go

--
declare @id int,
        @name varchar(20),
        @temp varchar(20);
set @id = 7;
set @temp = 1;
exec proc_getStudentRecord @id, @name out, @temp output;
select @name, @temp;
print @name + '#' + @temp;

 

 

   7、 不緩衝預存程序

--WITH RECOMPILE 不緩衝
if (object_id('proc_temp', 'P') is not null)
    drop proc proc_temp
go
create proc proc_temp
with recompile
as
    select * from student;
go

exec proc_temp;

   8、 加密預存程序

--加密WITH ENCRYPTION
if (object_id('proc_temp_encryption', 'P') is not null)
    drop proc proc_temp_encryption
go
create proc proc_temp_encryption
with encryption
as
    select * from student;
go

exec proc_temp_encryption;
exec sp_helptext 'proc_temp';
exec sp_helptext 'proc_temp_encryption';

   9、 帶遊標參數預存程序

if (object_id('proc_cursor', 'P') is not null)
    drop proc proc_cursor
go
create proc proc_cursor
    @cur cursor varying output
as
    set @cur = cursor forward_only static for
    select id, name, age from student;
    open @cur;
go
--調用
declare @exec_cur cursor;
declare @id int,
        @name varchar(20),
        @age int;
exec proc_cursor @cur = @exec_cur output;--調用預存程序
fetch next from @exec_cur into @id, @name, @age;
while (@@fetch_status = 0)
begin
    fetch next from @exec_cur into @id, @name, @age;
    print 'id: ' + convert(varchar, @id) + ', name: ' + @name + ', age: ' + convert(char, @age);
end
close @exec_cur;
deallocate @exec_cur;--刪除遊標

 

 

   10、 分頁預存程序

---預存程序、row_number完成分頁
if (object_id('pro_page', 'P') is not null)
    drop proc proc_cursor
go
create proc pro_page
    @startIndex int,
    @endIndex int
as
    select count(*) from product
;   
    select * from (
        select row_number() over(order by pid) as rowId, * from product
    ) temp
    where temp.rowId between @startIndex and @endIndex
go
--drop proc pro_page
exec pro_page 1, 4
--
--分頁預存程序
if (object_id('pro_page', 'P') is not null)
    drop proc pro_stu
go
create procedure pro_stu(
    @pageIndex int,
    @pageSize int
)
as
    declare @startRow int, @endRow int
    set @startRow = (@pageIndex - 1) * @pageSize +1
    set @endRow = @startRow + @pageSize -1
    select * from (
        select *, row_number() over (order by id asc) as number from student
    ) t
    where t.number between @startRow and @endRow;

exec pro_stu 2, 2;

 

 

----- Raiserror

Raiserror返回使用者定義的錯誤資訊，可以指定嚴重層級，設定系統變數記錄所發生的錯誤。

   文法如下：

Raiserror({msg_id | msg_str | @local_variable}
  {, severity, state}
  [,argument[,…n]]
  [with option[,…n]]
)

   # msg_id:在sysmessages系統資料表中指定的使用者定義錯誤資訊

   # msg_str:使用者定義的資訊，資訊最大長度在2047個字元。

   # severity：使用者定義與該訊息關聯的嚴重層級。當使用msg_id引發使用sp_addmessage建立的使用者定義訊息時，raiserror上指定嚴重性將覆蓋sp_addmessage中定義的嚴重性。

    任何使用者可以指定0-18直接的嚴重層級。只有sysadmin固定伺服器角色常用或具有alter trace許可權的使用者才能指定19-25直接的嚴重層級。19-25之間的安全層級需要使用with log選項。

   # state：介於1至127直接的任何整數。State預設值是1。

raiserror('is error', 16, 1);
select * from sys.messages;
--使用sysmessages中定義的訊息
raiserror(33003, 16, 1);
raiserror(33006, 16, 1);