sql procedure语句
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
sql procedure语句1. 创建存储过程
```sql
CREATE PROCEDURE sp_create_table
AS
BEGIN
CREATE TABLE dbo.Employee
(
ID INT PRIMARY KEY,
Name NVARCHAR(50),
Age INT,
Department NVARCHAR(50)
)
END
```
2. 查询存储过程
```sql
CREATE PROCEDURE sp_get_employee
AS
BEGIN
SELECT * FROM dbo.Employee
END
```
3. 插入数据的存储过程
```sql
CREATE PROCEDURE sp_insert_employee
@ID INT,
@Name NVARCHAR(50),
@Age INT,
@Department NVARCHAR(50)
AS
BEGIN
INSERT INTO dbo.Employee (ID, Name, Age, Department) VALUES (@ID, @Name, @Age, @Department)
END
```
4. 更新数据的存储过程
```sql
CREATE PROCEDURE sp_update_employee
@ID INT,
@Age INT
AS
BEGIN
UPDATE dbo.Employee
SET Age = @Age
WHERE ID = @ID
END
```
5. 删除数据的存储过程
```sql
CREATE PROCEDURE sp_delete_employee @ID INT
AS
BEGIN
DELETE FROM dbo.Employee
WHERE ID = @ID
END
```
6. 带条件查询的存储过程
```sql
CREATE PROCEDURE sp_search_employee @Department NVARCHAR(50)
AS
BEGIN
SELECT * FROM dbo.Employee
WHERE Department = @Department
END
```
7. 带输出参数的存储过程
```sql
CREATE PROCEDURE sp_count_employee
@Count INT OUTPUT
AS
BEGIN
SELECT @Count = COUNT(*) FROM dbo.Employee END
```
8. 带事务的存储过程
```sql
CREATE PROCEDURE sp_update_employee_age
@ID INT,
@Age INT
AS
BEGIN
BEGIN TRANSACTION
BEGIN TRY
UPDATE dbo.Employee
SET Age = @Age
WHERE ID = @ID
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
END
```
9. 带循环的存储过程
```sql
CREATE PROCEDURE sp_increase_age
AS
BEGIN
DECLARE @ID INT
DECLARE @Age INT
DECLARE cur CURSOR FOR
SELECT ID, Age FROM dbo.Employee
OPEN cur
FETCH NEXT FROM cur INTO @ID, @Age WHILE @@FETCH_STATUS = 0
SET @Age = @Age + 1
UPDATE dbo.Employee
SET Age = @Age
WHERE ID = @ID
FETCH NEXT FROM cur INTO @ID, @Age END
CLOSE cur
DEALLOCATE cur
END
```
10. 带条件判断的存储过程
```sql
CREATE PROCEDURE sp_check_age
@Age INT
AS
BEGIN
IF @Age >= 18
BEGIN
PRINT '成年人'
ELSE
BEGIN
PRINT '未成年人' END
END