182 lines
2.3 KiB
Markdown
Executable File
182 lines
2.3 KiB
Markdown
Executable File
# SQL Server 存储过程基本语法
|
|
|
|
> T-SQL 存储过程常用语法
|
|
|
|
---
|
|
|
|
## 一、变量定义
|
|
|
|
### 简单赋值
|
|
|
|
```sql
|
|
DECLARE @a INT
|
|
SET @a = 5
|
|
PRINT @a
|
|
|
|
-- 使用 SELECT 赋值
|
|
DECLARE @user1 NVARCHAR(50)
|
|
SELECT @user1 = '张三'
|
|
PRINT @user1
|
|
```
|
|
|
|
---
|
|
|
|
## 二、临时表
|
|
|
|
### 创建临时表
|
|
|
|
```sql
|
|
CREATE TABLE #TempTable
|
|
(
|
|
[ID] INT NOT NULL,
|
|
[Login] NVARCHAR(50) NOT NULL,
|
|
[Name] NVARCHAR(50) NOT NULL
|
|
)
|
|
|
|
-- 插入数据
|
|
INSERT INTO #TempTable (ID, Login, Name)
|
|
VALUES (100, 'LS', '临时')
|
|
|
|
-- 从查询创建
|
|
SELECT * INTO #TempTable2 FROM ST_User WHERE ID < 8
|
|
|
|
-- 删除
|
|
DROP TABLE #TempTable
|
|
```
|
|
|
|
### 表变量
|
|
|
|
```sql
|
|
DECLARE @t TABLE
|
|
(
|
|
id INT NOT NULL,
|
|
msg NVARCHAR(50) NULL
|
|
)
|
|
|
|
INSERT INTO @t VALUES (1, 'msg1')
|
|
INSERT INTO @t VALUES (2, 'msg2')
|
|
|
|
SELECT * FROM @t
|
|
```
|
|
|
|
---
|
|
|
|
## 三、IF/ELSE 条件
|
|
|
|
```sql
|
|
IF 条件
|
|
BEGIN
|
|
-- 语句
|
|
END
|
|
ELSE IF 条件
|
|
BEGIN
|
|
-- 语句
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
-- 语句
|
|
END
|
|
```
|
|
|
|
---
|
|
|
|
## 四、WHILE 循环
|
|
|
|
```sql
|
|
DECLARE @i INT = 1
|
|
|
|
WHILE @i <= 10
|
|
BEGIN
|
|
PRINT @i
|
|
SET @i = @i + 1
|
|
|
|
-- 退出循环
|
|
IF @i = 5
|
|
BREAK
|
|
END
|
|
```
|
|
|
|
---
|
|
|
|
## 五、CASE 语句
|
|
|
|
```sql
|
|
SELECT
|
|
Name,
|
|
CASE
|
|
WHEN Score >= 90 THEN '优秀'
|
|
WHEN Score >= 60 THEN '及格'
|
|
ELSE '不及格'
|
|
END AS Grade
|
|
FROM Student
|
|
```
|
|
|
|
---
|
|
|
|
## 六、游标
|
|
|
|
```sql
|
|
DECLARE @id INT
|
|
DECLARE @name NVARCHAR(50)
|
|
|
|
DECLARE my_cursor CURSOR FOR
|
|
SELECT ID, Name FROM ST_User
|
|
|
|
OPEN my_cursor
|
|
FETCH NEXT FROM my_cursor INTO @id, @name
|
|
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
PRINT CAST(@id AS NVARCHAR) + ' - ' + @name
|
|
FETCH NEXT FROM my_cursor INTO @id, @name
|
|
END
|
|
|
|
CLOSE my_cursor
|
|
DEALLOCATE my_cursor
|
|
```
|
|
|
|
---
|
|
|
|
## 七、存储过程
|
|
|
|
### 创建存储过程
|
|
|
|
```sql
|
|
CREATE PROCEDURE sp_GetUser
|
|
@UserID INT
|
|
AS
|
|
BEGIN
|
|
SELECT * FROM ST_User WHERE ID = @UserID
|
|
END
|
|
```
|
|
|
|
### 执行存储过程
|
|
|
|
```sql
|
|
EXEC sp_GetUser @UserID = 1
|
|
```
|
|
|
|
### 带输出参数
|
|
|
|
```sql
|
|
CREATE PROCEDURE sp_CountUser
|
|
@Count INT OUTPUT
|
|
AS
|
|
BEGIN
|
|
SELECT @Count = COUNT(*) FROM ST_User
|
|
END
|
|
```
|
|
|
|
---
|
|
|
|
## 八、常用函数
|
|
|
|
| 函数 | 说明 |
|
|
|------|------|
|
|
| `GETDATE()` | 当前日期时间 |
|
|
| `DATEADD()` | 日期加减 |
|
|
| `DATEDIFF()` | 日期间隔 |
|
|
| `ISNULL()` | 空值替换 |
|
|
| `CAST()` | 类型转换 |
|
|
| `LEN()` | 字符串长度 |
|