常见实用的SQL语句、存储过程、游标、函数、视图（不断整理中）

1、批量取代

update TableName set [RowName] = REPLACE([RowName],'要替换前的字符','要替换后的字符')

2、游标和存储过程（逐条记录操作）

CREATE PROCEDURE [dbo].[P_T_Course_Delete]
@CourseID INT  --有多个参数一定用,且（）包起来，若为输出参数还得后面加output
AS
declare @SurveyID INT  --有多个变量要不用;要不省去 sql语句不用加结束符号;
declare cur_test cursor for select SurveyID from dbo.T_Survey where CourseID = @CourseID
open cur_test
fetch cur_test into @SurveyID  --正规写法应该是fetch next from cur_test into @SurveyID
while(@@fetch_status = 0)
  begin
    delete from T_SurveyDetail where SurveyID = @SurveyID
    fetch next from cur_test into @SurveyID 
  end
close cur_test
deallocate cur_test

-- exec [dbo].[P_T_Course_Delete] 1

3、触发器（inserted表，deleted表）

CREATE TRIGGER [TR_InfoSupply] ON mois.information_supply FOR UPDATE,INSERT
AS 
BEGIN
--定义变量
DECLARE @area_id_list VARCHAR(50)
declare @area_text_list VARCHAR(50)
declare @area_text_city_list VARCHAR(50)
DECLARE @txt VARCHAR(100)
DECLARE @txt_area_id VARCHAR(100)
DECLARE @info_id INT
DECLARE @sql VARCHAR(500)
DECLARE @area VARCHAR(100)
SELECT @area_id_list=area,@info_id=info_id FROM inserted i
IF @area=',' --添加的面向地区是“全国”
    BEGIN
    UPDATE mosi.[information_supply] SET province= ',' WHERE info_id=@info_id
    UPDATE mosi.[information_supply] SET city= ',' WHERE info_id=@info_id
    END
ELSE
    BEGIN
	--由,1,2,3,格式拆分成1,2,3格式
	SET @area_id_list=SUBSTRING(@area_id_list,2,LEN(@area_id_list)-2)
	--构造查询地区id的sql语句
	SET @sql='SELECT parent_id,area_id FROM [mosi].area WHERE area_id IN ('+@area_id_list+')'
	--定义临时表
	CREATE TABLE #area(parent_id VARCHAR(100),area_id VARCHAR(100))
	--将查询结果存放在临时表
	INSERT INTO #area(parent_id,area_id) EXEC(@sql)
	--Cursor开始
	DECLARE my_cursor CURSOR FOR SELECT parent_id,area_id FROM #area
	OPEN my_cursor 
	FETCH NEXT FROM my_cursor INTO @txt,@txt_area_id
	WHILE @@FETCH_STATUS = 0
    BEGIN
		IF  @txt=@txt_area_id
			BEGIN
				SET @area_text_list=@area_text_list+@txt+','
			END
		ELSE IF @txt<32
			BEGIN
				SET @area_text_list=@area_text_list+@txt+','
				SET @area_text_city_list=@area_text_city_list+@txt_area_id+','
			END
		ELSE
			BEGIN
				SET @area_text_city_list=@area_text_city_list+@txt+','
				SET @txt=(SELECT parent_id FROM [mosi].area WHERE area_id =@txt)
				SET @area_text_list=@area_text_list+@txt+','
			END
        FETCH NEXT FROM my_cursor INTO @txt,@txt_area_id
	END
	CLOSE my_cursor
	DEALLOCATE my_cursor
    END
	--cursor结束
--修改数据库
UPDATE mosi.[information_supply] SET province= @area_text_list WHERE info_id=@info_id
UPDATE mosi.[information_supply] SET city= @area_text_city_list WHERE info_id=@info_id
drop table #area
END