存儲過程字元串長度

A. 關於存儲過程中執行字元串

alter
proc
[dbo].[fun_query_pm]
@ii_pm_name
varchar(20)
as
begin
declare
@where
varchar(20)
begin
set
@where
=
'missaka'
set
@where
=
@where
+
@ii_pm_name
print
@where
print
@ii_pm_name
end
end
exec
p_test
'10011'
你把char換成varchar就可以顯示了，這主要是char是固定長度，當set
@where='misaka'時，
實際上@where中的位元組段已經填滿，後面空的被空格字元填滿，所以當你再進行連接的時候就沒位置連了，
當然不能連接了。用varchar就不會了

B. oracle怎麼用函數求一個存儲過程的輸入參數（是一個以，為分隔的字元串）的長度~！ 急，不勝感激！

select substr(',' || a.exc,
instr(',' || a.exc, ',', 1, b.rn) + 1,
instr(a.exc || ',', ',', 1, b.rn) -
instr(',' || a.exc, ',', 1, b.rn)) exc
from (select 'a,b,c,d,e,f,g,h' exc from al t) A,
(SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM < 20) B
where length(a.exc) - length(replace(a.exc, ',')) + 1 >= b.rn

C. oracle 存儲過程里用 execute immedate 動態sql，更新一個表的clob欄位報字元長度

sql裡面varchar2最多4000個位元組，sql語句最多32K長度，估計是你的a_clob太長，放到sql裡面超過這個限制了，具體是哪個原因還得你自己測試。
可以使用綁定變數的方式來做。
V_SQL
:=
'UPDATE
B
'||
'
SET
B_CLOB
=
'''
||
AA.A_CLOB
||'''
WHERE
B_KEY
=
'''||
AA.A_KEY
||
'''';
EXECUTE
IMMEDIATE
V_SQL;
改成：
V_SQL
:=
'UPDATE
B
SET
B_CLOB
=:1
WHERE
B_KEY
=:2
';
EXECUTE
IMMEDIATE
V_SQL
using
AA.A_CLOB,AA.A_KEY;

D. SQL 存儲過程 長字元串分割存儲

declare @inputStr varchar(max)
set @inputStr='H,SH600,AAA,123,456,567,566,565,H,SH600,ABC,542,34,35,367,765,H,SH600,AAC,52,314,325,3867,7865'

declare @divideFlag varchar(20)
set @divideFlag='H,'

declare @bnname varchar(8)
declare @gpdm varchar(8)
declare @gpcn int
declare @ydate int
declare @yopen int
declare @yhigh int

declare @substr varchar(200)
declare @pos1 int
declare @pos2 int

declare @sql varchar(400)

--如果字元串中有分隔符標記
while CHARINDEX(@divideFlag,@inputStr,0)>0
begin
set @pos1 = CHARINDEX(@divideFlag,@inputStr,0)
print '位置1 '+cast(@pos1 as varchar)
set @pos2 = CHARINDEX(@divideFlag,@inputStr,@pos1+1)
print '位置2 ' + cast(@pos2 as varchar)

if @pos2>0
begin
set @substr = SUBSTRING(@inputStr,@pos1,@pos2-@pos1)
print '截取處理串 '+ @substr
set @inputStr = SUBSTRING(@inputStr,@pos2,len(@inputStr))
--print @inputStr
end
else
begin
set @substr = SUBSTRING(@inputStr,@pos1,len(@inputStr))
print '截取處理串 '+ @substr
set @inputStr = 'empty'
--print @inputStr
end
--去掉開頭的分割標記
set @substr=substring(@substr,CHARINDEX(@divideFlag,@substr,0)+len(@divideFlag),len(@substr))
print '去掉開頭分割標記 ' + @substr

if right(@substr,1)=','
begin
set @substr = left(@substr,len(@substr)-1)
print '去掉末尾的逗號' + @substr
end
--取得表名稱
set @bnname = substring(@substr,1,charindex(',',@substr,0)-1)
print @bnname
--取表名稱後面的字元串
set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr))
print @substr
--取得第一個欄位
set @gpdm = substring(@substr,1,charindex(',',@substr,0)-1)
print @gpdm
--取得後面的字元串
set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr))
print @substr

set @gpcn = substring(@substr,1,charindex(',',@substr,0)-1)
print @gpcn
set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr))
print @substr

set @ydate = substring(@substr,1,charindex(',',@substr,0)-1)
print @ydate
set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr))
print @substr

set @yopen = substring(@substr,1,charindex(',',@substr,0)-1)
print @yopen
set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr))
print @substr

set @yhigh = substring(@substr,1,charindex(',',@substr,0)-1)
print @yhigh
set @substr=substring(@substr,charindex(',',@substr,0)+1,len(@substr))
print @substr
-- 拼接 插入的 sql 字元串
set @sql = 'insert into '+@bnname+' (gpdm,gpcn,ydate,yopen,yhigh)
values(
'''+@gpdm+''','+cast(@gpcn as varchar)+','+cast(@ydate as varchar)+','+cast(@yopen as varchar)+','+cast(@yhigh as varchar)+'
)'

print @sql
end
-- 拷貝到查詢分析器執行，可以看到列印出的消息，拼接的字元串應該可以用