sql server在使用存储过程中常见问题总结一：

谁践踏了优雅 2021-11-29 05:24 340阅读 0赞

### 阅读本篇博客你可以get到以下内容： ###

*   *  一、SQL存储过程中 SET ANSI\_NULLS ON 和 SET QUOTED\_IDENTIFIER ON的作用和详解
     *  二、关于SQL的四种连接-左外连接、右外连接、内连接、全连接
     *  三、在实际应用存储过程时对于isnull()和sum()的使用。
     *  四、需要对数据进行分类排序，row\_number() over的基本用法
     *  五、截取字符串指定长度：left()函数和right()函数，对应oracle中的substr()函数
     *  六、如何创建临时表
     *  七、case when then的用法
     *  八、视图的运用
     *  九、sql对XML格式进行解析

## 一、SQL存储过程中 SET ANSI\_NULLS ON 和 SET QUOTED\_IDENTIFIER ON的作用和详解 ##

1）SET ANSI\_NULLS ON:表示对空值(null)对等于(=)或不等于(<>)进行判断时，遵从 SQL-92 规则。（当where后的字段值为空时，select出的结果为零行/空。）

2）SET ANSI\_NULLS OFF:表示在对空值(null)进行等于(=)或不等于(<>)比较时，不再遵从SQL-92的规则。（当where后的字段值为空时，select出的结果为对应数据行。）

3）SET QUOTED\_IDENTIFIER ON:表示使用 引用标识符，标识符可以用双引号分隔，但是，文字必须用单引号分隔。

4）SET QUOTED\_IDENTIFIER OFF:表示标识符不能用双引号分隔，否则标识符会被当做字符串值来返回，不再是字符来返回。而且，文字部分必须用单引号或双引号分隔。

详情请移步：[https://blog.csdn.net/qq112212qq/article/details/84578263][https_blog.csdn.net_qq112212qq_article_details_84578263]

## 二、关于SQL的四种连接-左外连接、右外连接、内连接、全连接 ##

**1、内联接**（典型的联接运算，使用像 = 或 <> 之类的比较运算符）。包括相等联接和自然联接。  
内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 students和courses表中学生标识号相同的所有行。

**2、外联接** 外联接可以是左向外联接、右向外联接或完整外部联接。  
在 FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：  
1）LEFT JOIN或LEFT OUTER JOIN  
左向外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。

2）RIGHT JOIN 或 RIGHT OUTER JOIN  
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。

3）FULL JOIN 或 FULL OUTER JOIN  
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

**3、交叉联接**  
交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。  
FROM 子句中的表或视图可通过内联接或完整外部联接按任意顺序指定；但是，用左或右向外联接指定表或视图时，表或视图的顺序很重要。有关使用左或右向外联接排列表的更多信息，请参见使用外联接。

详情请参阅：[https://www.cnblogs.com/yyjie/p/7788413.html][https_www.cnblogs.com_yyjie_p_7788413.html]

连接查询是SQL查询的核心，连接查询的连接类型选择依据实际需求。
    如果选择不当，非但不能提高查询效率，反而会带来一些逻辑错误或者性能低下。
    下面总结一下两表连接查询选择方式的依据：
    
    1、 查两表关联列相等的数据用内连接。
    2、 Col_L是Col_R的子集时用右外连接。（Col_R是主表，Col_L是关联着的明细表）
    3、 Col_R是Col_L的子集时用左外连接。（Col_L是主表，Col_R是关联着的明细表）
    4、 Col_R和Col_L彼此有交集但彼此互不为子集时候用全外。
    5、 求差操作的时候用联合查询。
    多个表查询的时候，这些不同的连接类型可以写到一块。例如：
    SELECT T1.C1,T2.CX,T3.CY
    FROM TAB1 T1
           INNER JOIN TAB2 T2 ON (T1.C1=T2.C2)
           INNER JOIN TAB3 T3 ON (T1.C1=T2.C3)
           LEFT OUTER JOIN TAB4 ON(T2.C2=T3.C3);
    WHERE T1.X >T3.Y;

## 三、在实际应用存储过程时对于isnull()和sum()的使用。 ##

SELECT ISNULL(SUM(ISNULL(ZJGXFKD_JE,0)),0) AS JE 
    FROM ZJGXFKD LEFT JOIN ZJGXFKMX ON ZJGXFKMX_NM = ZJGXFKD_NM  
    WHERE ZJGXFKD_DJZT <> '1' AND ZJGXFKMX_HTID = @HTBH

在一次金额查询时，由于测试数据不完整，查询出的订单金额有的是null，在前端取值时经过运算结果总是null，开始以为是类型转换的问题，后来也没解决了。最后在存储过程中添加了isnull()操作。  
在对金额求和时用到了sum()函数，但是这个函数有个特点，就是当查询出来的值有null时，求和结果只会是null，因此也需要isnull()来辅助求和。

ISNULL():使用指定的替换值替换 NULL。
    语法 :  isnull(value1,value2)
            1、value1与value2的数据类型必须一致。
            2、如果value1的值不为null，结果返回value1。
            3、如果value1为null，结果返回vaule2的值。vaule2是你设定的值。
     同时要注意，在sql server中字段为空的写法，
     select je from ZJGXFKMX where je is null\is not null 
     而不是name=null、name=' '

## 四、需要对数据进行分类排序，row\_number() over的基本用法 ##

用法：
    简单的说row_number()从1开始，为每一条分组记录返回一个数字，
    例1）ROW_NUMBER() OVER (ORDER BY xlh DESC)  AS 测试
    这里是先把xlh列降序，再为降序以后的没条xlh记录返回一个序号。 
    
    例2）ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)  AS 测试
    表示根据COL1分组，在分组内部根据 COL2排序，
    而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)

## 五、截取字符串指定长度：left()函数和right()函数，对应oracle中的substr()函数 ##

语法：LEFT(ARG,LENGTH)、RIGHT(ARG,LENGTH)   
    LEFT、RIGHT函数返回ARG最左边、右边的LENGTH个字符串，
    ARG可以是CHAR或BINARY STRING。  
    
    例如：RIGHT(HTGY_DDBH,len(HTGY_DDBH)-1) HTGY_DDBH
    获取订单编号从右数length-1个长度的编号数据（当时这么写是为了去除订单编号前的字母，
    如TK0000123,需要传的数据是K0000123）

Oracle数据库中是没有left() 和right() 函数的，若想按照DB2中对应的函数去使用，自己新建两个function即可，方法如下

LEFT:
    CREATE OR REPLACE FUNCTION "LEFT" (str in varchar2,sublen in integer) return varchar2 is
      strlen integer;
    begin
    
      strlen := length(str);
    
      if sublen<=0 then
          return '';
      elsif strlen<=sublen then
           return str;
       else
         return SUBSTR(str,0,sublen);
      end if;
     return '';
    end LEFT;

RIGHT:
    CREATE OR REPLACE FUNCTION "RIGHT" (str in varchar2,sublen in integer) return varchar2 is
      strlen integer;
    begin
    
      strlen := length(str);
    
      if sublen<=0 then
          return '';
      elsif strlen<=sublen then
           return str;
       else
         return SUBSTR(str,strlen-sublen+1,sublen);
      end if;
     return '';
    end RIGHT;

## 六、如何创建临时表 ##

一个简单的例子：关键是在创建前要判断数据库中是否已经有了这个临时表，有的话就删除
    if object_id('tempdb..#GT_header') is not null 
    	Begin
    		   DROP TABLE  #GT_header
    	End
    	create table #GT_header
    	(
    	ZHTH nvarchar(35) default '',
    	ZHTHXM int default 0,
    	KSCHL nvarchar(4) default '',
    	ZLSBS nvarchar(1) default '',
    	ZHTZT nvarchar(1) default '',
    	KTWRTX DECIMAL(15,2) default 0.00,
    	KTWRT  DECIMAL(15,2) default 0.00
    	)
    	insert into #GT_header
    	(ZHTH,ZHTHXM,KSCHL,ZLSBS,ZHTZT,KTWRTX,KTWRT)
    	select
         RIGHT(HTGY_DWBH,len(HTGY_DWBH)-1) HTGY_DWBH,isnull(HTGY.HTDJ28,'') HTDJ28,
         isnull(HTBD.HTDJ16,0)HTDJ16,	isnull(HTBD.HTDJ17,0.00)HTDJ17, HTGY_C15
    	from HTGY left join HTBD on HTGY.HTGY_NM = HTBD.HTBD_HTNM（HTGY为主表，HTBD为子表，所以用左连接）
    	where  HTGY.HTGY_NM = @BillNo
    
    注意不要在实际项目中不要轻易用  select * into 新表 from 旧表，
    因为有时候旧表（很多时候是产品部已经建好的表，数据格式不能改变）的数据属性
    和需要传的数据属性不一致，当出现数据截断时会报数据溢出或者转换异常的错误。
    有时候数据字段值产品部也不会添加进去经常出现null的字段值，在运算时很麻烦，还要去排查。
    在建表时指定好数据属性，并附上默认值能比较好的避免这种问题，
    出现数据转换异常时还要配合cast()和convert()函数，进行强制类型转换。

类型转换示例：[https://www.cnblogs.com/davidgu/archive/2011/02/15/1955335.html][https_www.cnblogs.com_davidgu_archive_2011_02_15_1955335.html]

## 七、case when then的用法 ##

--简单case函数
    case sex
            when '1' then '男'
            when '2' then '女’
            else '其他' end
    
    --case搜索函数
    case when sex = '1' then '男'
         when sex = '2' then '女'
         else '其他' end
         
    还有一个需要注重的问题，case函数只返回第一个符合条件的值，
    剩下的case部分将会被自动忽略。
    --比如说，下面这段sql，你永远无法得到“第二类”这个结果
    
    case when col_1 in ('a','b') then '第一类'
         when col_1 in ('a') then '第二类'
         else '其他' end

相关问题参见：[https://www.cnblogs.com/weihuang6620/p/6904722.html][https_www.cnblogs.com_weihuang6620_p_6904722.html]

## 八、视图的运用 ##

1、什么是视图？  
视图是一个虚拟的表，是一个表中的数据经过某种筛选后的显示方式，视图由一个预定义的查询select语句组成。

2、视图的特点。  
视图中的数据并不属于视图本身，而是属于基本的表，对视图可以像表一样进行insert,update,delete操作。  
视图不能被修改，表修改或者删除后应该删除视图再重建。  
视图的数量没有限制，但是命名不能和视图以及表重复，具有唯一性。  
视图可以被嵌套，一个视图中可以嵌套另一个视图。  
视图不能索引，不能有相关联的触发器和默认值，sql server不能在视图后使用order by排序。

3、视图的功能  
1.简化用户操作  
2.能以不同的角度观察同一个数据库  
3.对重构数据库提供了逻辑独立性：  
利用视图将需要的数据合并或者筛选，但是不影响原表的数据和结构  
***（这点很重要，在实际应用中会很多）***  
4.对机密数据提供安全保护：  
可以建立不同的视图对用不同的用户，以达到安全的目的。

建立视图的语法：
    Create view 视图名称[(字段1) (字段2) (字段3)…]
    AS
    Select 查询语句
    [with  check  option]
    参数：[with check  option]可选项，防止用户对数据插入、删除、更新是操作了视图范围外的基本表的数据。
    删除视图的语法：
    Drop view 视图名称

说一个实例，有一次在表单开发中，表单上需要展示关联子表的信息，
    我用平台自带的load()方法后没办法查出子表的信息，只能查到主表的信息，
    尝试调用存储过程又总是失败，最后开发经理提醒我说可以用视图筛选出需要的数据，
    然后创建数据模型，在调用load()方法查询。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXlpbjY1NTM_size_16_color_FFFFFF_t_70][]  
还有就是要注意主键和唯一值的关系，最后筛选出来的数据一定要有不同内码，如果查出相同数据内码的话，在调用JS自带的getchecked属性时会失效，具体表现为即使勾选了多个数据，在数组中也只选择了一组数据，因为它们的内码相同。

## 九、sql对XML格式进行解析 ##

USE [cwbase0001]
    GO
    /****** Object:  StoredProcedure [LC00019999].[SP_FSSC_BaseDate_SAPCGHTDDInfor]    Script Date: 2019/6/4 10:09:20 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		Qiang Liu
    -- Create date: 2019-5-31
    -- Description:	解析XML文件，将字段保存到表中
    -- =============================================
    ALTER PROCEDURE [lcerp9999].[SP_FSSC_BaseDate_SAPCGHTDDInfor](
    	@strXML text, /*<?xml version="1.0" encoding="gb2312"?><'xmldata><item><detailsItem>.</detailsItem>.<detailsItem>.</detailsItem></item>
    																	<item><detailsItem>.</detailsItem></item>.</xmldata>*/
    	@DataType VARCHAR(20)
    )
    AS 
    
    BEGIN
    DECLARE @hdoc int
    ----------------------------采购订单合同'EKPO'----------------------------
    if @DataType='EKPO'--采购订单合同
    begin
    if object_id('tempdb..#CGGetSAPDDInfo') is not null 
    Begin
           DROP TABLE  #CGGetSAPDDInfo
    End
    --drop table #CGGetSAPDDInfo
    create table #CGGetSAPDDInfo(
    --日期类型，数字类型数据用nvarchaer表示
    TBID nvarchar(40) not null,--自建数据id列
    EBELN nvarchar(10) default '',--采购凭证号
    EBELP int default 0,--采购凭证的项目编号
    RETPO nvarchar(10) default '',--退货项目
    IDNLF nvarchar(35) default '',--供应商使用的物料编号
    ZHTHXM int default 0,--合同行项目号
    AEDAT nvarchar(20) default '',--采购凭证项目更改日期
    ERNAM nvarchar(20) default '',--创建对象的人员名称
    MATNR nvarchar(20) default '',--物料号
    TXZ01 nvarchar(40) default '',--短文本
    MATKL nvarchar(20) default '',--物料组
    MENGE decimal(13,3) default 0.000,--采购订单数量   
    NETPR nvarchar(20) default '',--采购凭证中的净价(以凭证货币计)
    PEINH nvarchar(20) default '',--价格单位
    NETWR nvarchar(20) default '',--采购订单货币的订单净值
    WAERS nvarchar(10) default '',--货币代码
    PSTYP nvarchar(10) default '',--采购凭证中的项目类别
    );
    --将SAP文件解析，并将数据插入到临时表中
    
    EXEC sp_xml_preparedocument @hdoc OUTPUT, @strXML
    print(@hdoc)
    INSERT INTO #CGGetSAPDDInfo
    SELECT newid(),EBELN ,EBELP ,RETPO ,IDNLF ,
    ZHTHXM ,AEDAT ,ERNAM ,MATNR ,TXZ01 ,
    MATKL ,MENGE ,NETPR ,PEINH ,NETWR,WAERS,PSTYP 
    FROM OPENXML(@hdoc,'xmldata/item/detailsItem',1) --句柄解析的位置
    WITH(**
    
    EBELN nvarchar(10)  'EBELN',
    EBELP int  'EBELP', 
    RETPO nvarchar(10)  'RETPO', 
    IDNLF nvarchar(35)  'IDNLF', 
    ZHTHXM int  'ZHTHXM', 
    AEDAT nvarchar(20)  'AEDAT', 
    ERNAM nvarchar(20)  'ERNAM', 
    MATNR nvarchar(20)  'MATNR', 
    TXZ01 nvarchar(40)  'TXZ01', 
    MATKL nvarchar(20)  'MATKL', 
    MENGE decimal(13,3)  'MENGE', 
    NETPR nvarchar(20)  'NETPR', 
    PEINH nvarchar(20)  'PEINH', 
    NETWR nvarchar(20)  'NETWR', 
    WAERS nvarchar(10)  'WAERS', 
    PSTYP nvarchar(10)  'PSTYP' 
    );
    
    --将临时表数据插入到正式存储表
    --1、更新编号重复的订单
    update CGGetSAPDDInfo  set 
    CGGetSAPDDInfo.EBELN=#CGGetSAPDDInfo.EBELN ,
    EBELP=#CGGetSAPDDInfo.EBELP ,
    RETPO=#CGGetSAPDDInfo.RETPO ,
    IDNLF=#CGGetSAPDDInfo.IDNLF ,
    ZHTHXM=#CGGetSAPDDInfo.ZHTHXM ,
    AEDAT=#CGGetSAPDDInfo.AEDAT,
    ERNAM=#CGGetSAPDDInfo.ERNAM ,
    MATNR=#CGGetSAPDDInfo.MATNR ,
    TXZ01=#CGGetSAPDDInfo.TXZ01 ,
    MATKL=#CGGetSAPDDInfo.MATKL ,
    MENGE=#CGGetSAPDDInfo.MENGE ,
    NETPR=#CGGetSAPDDInfo.NETPR ,
    PEINH=#CGGetSAPDDInfo.PEINH ,
    NETWR=#CGGetSAPDDInfo.NETWR, 
    WAERS=#CGGetSAPDDInfo.WAERS, 
    PSTYP=#CGGetSAPDDInfo.PSTYP
    
    from #CGGetSAPDDInfo 
    WHERE CGGetSAPDDInfo.EBELN=#CGGetSAPDDInfo.EBELN
    
    
    --2、插入不存在编号的订单
    insert into CGGetSAPDDInfo 
    (TBID ,EBELN ,EBELP ,RETPO ,IDNLF ,
    ZHTHXM ,AEDAT ,ERNAM ,MATNR ,TXZ01 ,
    MATKL ,MENGE ,NETPR ,PEINH ,NETWR,WAERS,PSTYP)
    
    select 
    newid(),EBELN ,EBELP ,RETPO ,IDNLF ,
    ZHTHXM ,AEDAT ,ERNAM ,MATNR ,TXZ01 ,
    MATKL ,MENGE ,NETPR ,PEINH ,NETWR,WAERS,PSTYP 
    from #CGGetSAPDDInfo
    where not exists (select 1 from CGGetSAPDDInfo where CGGetSAPDDInfo.EBELN = #CGGetSAPDDInfo.EBELN) 
    end

[https_blog.csdn.net_qq112212qq_article_details_84578263]: https://blog.csdn.net/qq112212qq/article/details/84578263
[https_www.cnblogs.com_yyjie_p_7788413.html]: https://www.cnblogs.com/yyjie/p/7788413.html
[https_www.cnblogs.com_davidgu_archive_2011_02_15_1955335.html]: https://www.cnblogs.com/davidgu/archive/2011/02/15/1955335.html
[https_www.cnblogs.com_weihuang6620_p_6904722.html]: https://www.cnblogs.com/weihuang6620/p/6904722.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXlpbjY1NTM_size_16_color_FFFFFF_t_70]: /images/20211129/1bb4c74a94014f8fa879786491438817.png