mysql mysql  地区全名  存储过程
场景： 查询地区所有上级名称 组成地区全名
知识点

场景： 查询地区所有上级名称 组成地区全名
知识点

SQL SELECT INTO 语句.
Mysql存储过程查询结果赋值到变量的方法.

// 
CREATE DEFINER=`root`@`%` PROCEDURE `FULL_NAME_PROC`(in areaId varchar(32) , out fullName varchar(64))
BEGIN
-- 根据地区id得到地区全名
declare `@n1` VARCHAR(32);
declare `@n2` VARCHAR(32);
declare `@n3` VARCHAR(32);
declare `@n4` VARCHAR(32);
declare `@n5` VARCHAR(32);
declare `@p1` VARCHAR(32);
declare `@p2` VARCHAR(32);
declare `@p3` VARCHAR(32);
declare `@p4` VARCHAR(32);
declare `@p5` VARCHAR(32);


select  a5.name,a5.parent_id into @n5 , @p5 from sys_area as a5  where  a5.id = areaId;

if exists(select a5.parent_id from sys_area as a5  where a5.id = areaId limit 1) then

select a4.name  , a4.parent_id into @n4 , @p4 from sys_area as a4  where a4.id = @p5;

if exists(select a4.parent_id from sys_area as a4  where a4.id = @p5 limit 1) then

select a3.name  , a3.parent_id into @n3 , @p3 from sys_area as a3  where a3.id = @p4;

if exists(select a3.parent_id from sys_area as a3  where a3.id = @p4 limit 1) then

select a2.name  , a2.parent_id into @n2 , @p2  from sys_area as a2  where a2.id = @p3;

if exists(select a2.parent_id from sys_area as a2  where a2.id = @p3 limit 1) then

set @n1 =  (select a1.name  from sys_area as a1  where a1.id = @p2);

set  fullName =  CONCAT(IFNULL(@n1,""),IFNULL(@n2,""),IFNULL(@n3,""),IFNULL(@n4,""),IFNULL(@n5,""));
-- SELECT @n1,@n2,@n3,@n4,@n5, @p6,fullName ;
ELSE
set  fullName =  CONCAT(IFNULL(@n2,""),IFNULL(@n3,""),IFNULL(@n4,""),IFNULL(@n5,""));
END IF;
ELSE
set  fullName =  CONCAT(IFNULL(@n3,""),IFNULL(@n4,""),IFNULL(@n5,""));
END IF;
ELSE
set  fullName =  CONCAT(IFNULL(@n4,""),IFNULL(@n5,""));
END IF;
ELSE
set  fullName =  IFNULL(@n5,"");
END IF;

-- set @n1 =NULL;
-- set @n2 =NULL;
-- set @n3 =NULL;
-- set @n4 =NULL;
-- set @n5 =NULL;
-- set @p1 =NULL;
-- set @p2 =NULL;
-- set @p3 =NULL;
-- set @p4 =NULL;
-- set @p5 =NULL;
-- 

END

--drop proc insertpostcode
create proc insertpostcode (
@districtname nvarchar(50),--地区名称
@provincename   nvarchar(50),--省名
@districtpostcode varchar(20)--邮编号码
)
as
declare @districtcode varchar(20)--地区编号
--给地区编号赋值
set @districtcode=(select distinct districtcode  from district where  citypostcodeid in(
select citypostcodeid from city where provincepostcode in(
select provincepostcode from province where provincename=@provincename)
)
and districtname=@districtname )
--城市编号
declare @citypostcodeid varchar(20)
--给城市编号赋值
set @citypostcodeid=(select distinct citypostcodeid  from district where  citypostcodeid in(
select citypostcodeid from city where provincepostcode in(
select provincepostcode from province where provincename=@provincename)
)
and districtname=@districtname) 
--地区编号不为空。说明存在此地区
if( @districtcode!='')
BEGIN
 --记录总数
 DECLARE @count int
 SET @count=(SELECT count(*) FROM district WHERE district.districtpostcode=@districtpostcode)
 --PRINT @count
 --说明此邮编在数据中不存在
 IF(@count=0)
 begin
           --插入到district 表中
           insert into district values(@districtpostcode,@districtcode,@districtname,@citypostcodeid,0,0,null,null,null,null,null)
           select * from district where districtpostcode =@districtpostcode
           --areassign 下插入新的记录
              DECLARE  @areacount int
      SET @areacount=(SELECT  count(*) FROM areaassign WHERE districtpostcode IN(
             SELECT district.districtpostcode
                FROM district
                 WHERE district.districtname=@districtname  ))
                 --PRINT @areacount
      IF(@areacount!=0)--已经分配
      begin
         declare     @provinceid     varchar(20)   --省邮编
           DECLARE     @areaid         varchar(10)           --区域编号
           declare     @sellerid        varchar(10)             --所属销售人员
           declare     @rankid          varchar(10)            --级别
           declare     @groupid          varchar(10)          -- 组编号
           declare   kk   cursor   for     
           SELECT  TOP 1 areaassign.provincepostcode,areaassign.areaid,areaassign.sellerid,rankid,groupid
              FROM areaassign 
              WHERE districtpostcode
              IN
              (
               SELECT district.districtpostcode
               FROM district
               WHERE district.districtname=@districtname
              )  
            open   kk   
            fetch     next   from     kk   into   @provinceid,@areaid ,@sellerid,@rankid,@groupid
            while   @@fetch_status=0   
            begin   
            fetch     next   from     kk   into    @provinceid,@areaid ,@sellerid,@rankid,@groupid
            --PRINT  @provinceid
            -- PRINT @areaid 
            -- PRINT  @sellerid
            -- PRINT  @rankid
            -- PRINT  @groupid
            INSERT INTO areaassign
            (
             --assignareaid,
             provincepostcode,
             citypostcodeid,
             districtpostcode,
             areaid,
             sellerid,
             technicianid,
             remark,
             isdeleted,
             createtime,
             createby,
             updatetime,
             updateby,
             rankid,
             groupid
            )
            VALUES
            (
             @provinceid,
             @citypostcodeid,
             @districtpostcode,
             @areaid,
             @sellerid,
             NULL,
             NULL,
             0,
             getdate(),
             NULL,
             getdate(),
             NULL,
             @rankid,
             @groupid
            )
            end   
            close   kk   
            deallocate     kk
      end
        else --还没有分配
         BEGIN
           PRINT @districtname +'未分配'
                 
          
          end 
          --插入到areassign 表中结束
          --插入到areadetails 表中开始
         declare @areadetailscount int--总数
         set @areadetailscount =(select count(*) from areadetails where citypostcodeid=@citypostcodeid)
         print @areadetailscount
         if(@areadetailscount!=0)
          begin
          declare     @provin     varchar(10)   
            DECLARE @aareaid varchar(10)
            DECLARE @agroupid varchar(10)
           declare   areadetailscur   cursor   for     
           select top 1  provincepostcode,areaid,groupid from dbo.areadetails where citypostcodeid=@citypostcodeid
           open   areadetailscur   
           fetch     next   from     areadetailscur   into  @provin,@aareaid,@agroupid
           while   @@fetch_status=0   
           begin   
           fetch     next   from     areadetailscur   into   @provin,@aareaid,@agroupid
          INSERT INTO areadetails
           (
            --areadetailsid,
            provincepostcode,
            citypostcodeid,
            districtpostcode,
            areaid,
            remark,
            isdeleted,
            createtime,
            createby,
            updatetime,
            updateby,
            groupid
           )
           VALUES
           (
            @provin,
            @citypostcodeid,
            @districtpostcode,
            @aareaid,
            null,
            0,
            getdate(),
            null,
            getdate(),
            null,
            @agroupid
           )
           end   
           close   areadetailscur   
           deallocate     areadetailscur
          end
          else
           begin
             PRINT @districtname +'未分配'
           end            
   
 END
  ELSE 
   BEGIN
   PRINT @districtpostcode+'邮编已经存在'
   END 
end 
else
begin
print  @districtname+ '区域不存在'
end 
--exec insertpostcode '滨湖区','江苏省','214066'

--delete from district where districtpostcode='214066'
--DELETE FROM areaassign WHERE  districtpostcode='214066'
--DELETE FROM areadetails WHERE districtpostcode='214066'
--select * from district where districtname='江苏省'
--SELECT * FROM areaassign WHERE districtpostcode='410083'
--SELECT * FROM areaassign WHERE citypostcodeid='410083'
--SELECT * FROM areadetails WHERE districtpostcode='410083'
--SELECT * FROM areadetails WHERE  citypostcodeid='214000'
--SELECT * FROM district WHERE district.districtpostcode='410083'
--SELECT * FROM district WHERE district.districtname='岳麓区'
 
 
 

--csdn.net邹建  --*/
/*--调用示例
 exec p_show '地区资料'
 exec p_show 'select top 100 percent * from 地区资料 order by 地区名称',5,3,'地区编号,地区名称,助记码'
--*/
/*创建 p_show*/

CREATE Proc p_show
@QueryStr nvarchar(4000), --表名、视图名、查询语句
@PageSize int=20,   --每页的大小(行数)
@PageCurrent int=1,   --要显示的页
@FdShow nvarchar (4000)='', --要显示的字段列表,如果查询结果不需要标识字段,需要指定此值,且不包含标识字段
@FdOrder nvarchar (1000)='' --排序字段列表
as
set nocount on
declare @FdName nvarchar(250) --表中的主键或表、临时表中的标识列名
 ,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
 ,@Obj_ID int    --对象ID
--表中有复合主键的处理
declare @strfd nvarchar(2000) --复合主键列表
 ,@strjoin nvarchar(4000) --连接字段
 ,@strwhere nvarchar(2000) --查询条件

select @Obj_ID=object_id(@QueryStr)
 ,@FdShow=case isnull(@FdShow,'') when '' then ' *' else ' '+@FdShow end
 ,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end
 ,@QueryStr=case when @Obj_ID is not null then ' '+@QueryStr else ' ('+@QueryStr+') a' end
--如果显示第一页，可以直接用top来完成
if @PageCurrent=1 
begin
 select @Id1=cast(@PageSize as varchar(20))
 exec('select top '+@Id1+@FdShow+' from '+@QueryStr+@FdOrder)
 return
end
--如果是表,则检查表中是否有标识更或主键
if @Obj_ID is not null and objectproperty(@Obj_ID,'IsTable')=1
begin
 select @Id1=cast(@PageSize as varchar(20))
  ,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20))
 select @FdName=name from syscolumns where id=@Obj_ID and status=0x80
 if @@rowcount=0   --如果表中无标识列,则检查表中是否有主键
 begin
  if not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype='PK')
   goto lbusetemp  --如果表中无主键,则用临时表处理
  select @FdName=name from syscolumns where id=@Obj_ID and colid in(
   select colid from sysindexkeys where @Obj_ID=id and indid in(
    select indid from sysindexes where @Obj_ID=id and name in(
     select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
   )))
  if @@rowcount>1  --检查表中的主键是否为复合主键
  begin
   select @strfd='',@strjoin='',@strwhere=''
   select @strfd=@strfd+',['+name+']'
    ,@strjoin=@strjoin+' and a.['+name+']=b.['+name+']'
    ,@strwhere=@strwhere+' and b.['+name+'] is null'
    from syscolumns where id=@Obj_ID and colid in(
    select colid from sysindexkeys where @Obj_ID=id and indid in(
     select indid from sysindexes where @Obj_ID=id and name in(
      select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
    )))
   select @strfd=substring(@strfd,2,2000)
    ,@strjoin=substring(@strjoin,5,4000)
    ,@strwhere=substring(@strwhere,5,4000)
   goto lbusepk
  end
 end
end
else
 goto lbusetemp
/*--使用标识列或主键为单一字段的处理方法--*/
lbuseidentity: 
 exec('select top '+@Id1+@FdShow+' from '+@QueryStr
  +' where '+@FdName+' not in(select top '
  +@Id2+' '+@FdName+' from '+@QueryStr+@FdOrder
  +')'+@FdOrder
  )
 return
/*--表中有复合主键的处理方法--*/
lbusepk:  
 exec('select '+@FdShow+' from(select top '+@Id1+' a.* from
  (select top 100 percent * from '+@QueryStr+@FdOrder+') a
  left join (select top '+@Id2+' '+@strfd+' 
  from '+@QueryStr+@FdOrder+') b on '+@strjoin+'
  where '+@strwhere+') a'
  )
 return
/*--用临时表处理的方法--*/
lbusetemp:  
select @FdName='[ID_'+cast(newid() as varchar(40))+']'
 ,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))
 ,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))
exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'
  into #tb from'+@QueryStr+@FdOrder+'
 select '+@FdShow+' from #tb where '+@FdName+' between '
 +@Id1+' and '+@Id2
 )
GO 

--新建存储过程，向新闻表插入一条数据，并返回自动生成的最大编号

create proc pro_GerMaxId

@maxid INT OUT 

AS

INSERT INTO T_News

(NewsTitle,NewsContent,NewsCreator,CreateTime,ClassId)

VALUES

('本周全国大部分地区降温','本周全国大部分地区降温，最高降温达°','003',GETDATE(),'39ec3206-4467-44b4-8fa1-711443eb4ce5');

set @maxid=@@IDENTITY;

go

declare @maxid INT;

exec Pro_GerMaxId @maxid OUT;

PRINT @maxid;  

select * from T_News
 
 
--接受用户输入的参数，插入数据，返回最大编号

create proc  Pro_GetMaxId1

@newstitle VARCHAR(64),

@newscontent VARCHAR(max),

@newscreator VARCHAR(8),

@createtime DATETIME,

@classname VARCHAR(8),

@maxid INT OUT

AS

DECLARE @classid uniqueidentifier;

select @classid=T1.ClassId from T_NewsClass

T1 where T1.ClassName=@classname;

INSERT INTO T_News(NewsTitle,NewsContent,NewsCreator,CreateTime,ClassId)

VALUES

(@newstitle,@newscontent,@newscreator,GETDATE(),@classid);

SET @maxid=@@IDENTITY;

GO