在插入新数据的时候,取出该字符串对应的字母用户定义函数A
create function f_ch2py(@chn nchar(1))
returns char(1)
as
begin
declare @n int
declare @c char(1)
set @n = 63select @n = @n +1,
@c = case chn when @chn then char(@n) else @c end
from(
select top 27 * from (
select chn =
'吖' union all select
'八' union all select
'嚓' union all select
'咑' union all select
'妸' union all select
'发' union all select
'旮' union all select
'铪' union all select
'丌' union all select --because have no 'i'
'丌' union all select
'咔' union all select
'垃' union all select
'嘸' union all select
'拏' union all select
'噢' union all select
'妑' union all select
'七' union all select
'呥' union all select
'仨' union all select
'他' union all select
'屲' union all select --no 'u'
'屲' union all select --no 'v'
'屲' union all select
'夕' union all select
'丫' union all select
'帀' union all select @chn) as a
order by chn COLLATE Chinese_PRC_CI_AS
) as b
return(@c)
end用户定义函数BCREATE FUNCTION F_GetHelpCode (
@cName VARCHAR(50) )
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @i SMALLINT, @L SMALLINT , @cHelpCode VARCHAR(20), @e VARCHAR(20), @iAscii SMALLINT
SELECT @i=1, @L=0 , @cHelpCode=''
while @L<=20 AND @i<=LEN(@cName) BEGIN
SELECT @e=LOWER(SUBSTRING(@cname,@i,1))
SELECT @iAscii=ASCII(@e)
IF @iAscii>=48 AND @iAscii <=57 OR @iAscii>=97 AND @iAscii <=122 or @iAscii=95
SELECT @cHelpCode=@cHelpCode +@e
ELSE
IF @iAscii>=176 AND @iAscii <=247
SELECT @cHelpCode=@cHelpCode + dbo.f_ch2py(@e)
ELSE SELECT @L=@L-1
SELECT @i=@i+1, @L=@L+1 END
RETURN @cHelpCode
END
调用方法
CREATE TRIGGER aa ON dbo.表A
FOR INSERT, UPDATE, DELETE
AS
update 表A set pinyin=dbo.F_GetHelpCode(b.item_name)
from 表A a,inserted b
where a.item_code=b.item_code
create function f_ch2py(@chn nchar(1))
returns char(1)
as
begin
declare @n int
declare @c char(1)
set @n = 63select @n = @n +1,
@c = case chn when @chn then char(@n) else @c end
from(
select top 27 * from (
select chn =
'吖' union all select
'八' union all select
'嚓' union all select
'咑' union all select
'妸' union all select
'发' union all select
'旮' union all select
'铪' union all select
'丌' union all select --because have no 'i'
'丌' union all select
'咔' union all select
'垃' union all select
'嘸' union all select
'拏' union all select
'噢' union all select
'妑' union all select
'七' union all select
'呥' union all select
'仨' union all select
'他' union all select
'屲' union all select --no 'u'
'屲' union all select --no 'v'
'屲' union all select
'夕' union all select
'丫' union all select
'帀' union all select @chn) as a
order by chn COLLATE Chinese_PRC_CI_AS
) as b
return(@c)
end用户定义函数BCREATE FUNCTION F_GetHelpCode (
@cName VARCHAR(50) )
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @i SMALLINT, @L SMALLINT , @cHelpCode VARCHAR(20), @e VARCHAR(20), @iAscii SMALLINT
SELECT @i=1, @L=0 , @cHelpCode=''
while @L<=20 AND @i<=LEN(@cName) BEGIN
SELECT @e=LOWER(SUBSTRING(@cname,@i,1))
SELECT @iAscii=ASCII(@e)
IF @iAscii>=48 AND @iAscii <=57 OR @iAscii>=97 AND @iAscii <=122 or @iAscii=95
SELECT @cHelpCode=@cHelpCode +@e
ELSE
IF @iAscii>=176 AND @iAscii <=247
SELECT @cHelpCode=@cHelpCode + dbo.f_ch2py(@e)
ELSE SELECT @L=@L-1
SELECT @i=@i+1, @L=@L+1 END
RETURN @cHelpCode
END
调用方法
CREATE TRIGGER aa ON dbo.表A
FOR INSERT, UPDATE, DELETE
AS
update 表A set pinyin=dbo.F_GetHelpCode(b.item_name)
from 表A a,inserted b
where a.item_code=b.item_code
解决方案 »
- oracle 数据同步
- oracle安装过程中出现的问题
- 时间长了select慢怎么办
- 查询正在运行的ORACLE进程
- 求助一SQL语句
- 请教:在before insert触发器中,如果发生指定事件,则中止这次插入操作,请问如何写。谢谢大家
- 简单问题,请指教
- 回滚段太小,怎么解决啊
- 用SQL语句建一个表,如果这个表已经存在,则不建了,语句怎么写!!!!
- Enterprise Manager ,system/manager ,SYSDBA 为何登陆不了,Normal 就可以
- 请教一个递归sql的编写,已经有了但是效率不高,要花8秒多的时间啊
- 请问大家,怎么样能把数据库中所有值为负最大值的字段及表找出来呢?先谢谢了。
CREATE OR REPLACE FUNCTION "MDM"."F_CH2PY" (chn in varchar2)
return varchar2 is
n float :=63;
c varchar2;
begin
select n = n +1, c = case chn when chn then to_char(n) else c end
from ( select * from ( select chn ='吖' union all select'八' union all select'嚓' union all select'咑' union all select'妸' union all select'发' union all select'旮' union all select'铪' union all select'丌' union all select
'丌' union all select
'咔' union all select
'垃' union all select
'嘸' union all select
'拏' union all select
'噢' union all select
'妑' union all select
'七' union all select
'呥' union all select
'仨' union all select
'他' union all select
'屲' union all select
'屲' union all select
'屲' union all select
'夕' union all select
'丫' union all select
'帀' union all select chn) as a where rownum < 28
order by chn COLLATE Chinese_PRC_CI_AS
) as b
return c;
end;
行号=6 列号=10 错误文本=pl/sql: ora-00923: from keyword not found where expect
行号=6 列号=1 错误文本 = pl/sql: sql statement ignored
return varchar2
as
p_n int;
p_c char(1);
begin
p_n := 63;
select p_n+1,(case chn when p_chn then to_char(p_n) else p_c end) into p_n, p_c
from(
select * from (
select '吖' chn from dual union all
select '八' chn from dual union all
select '嚓' chn from dual union all
select '咑' chn from dual union all
select '妸' chn from dual union all
select '发' chn from dual union all
select '旮' chn from dual union all
select '铪' chn from dual union all
select '丌' chn from dual union all --because have no 'i'
select '丌' chn from dual union all
select '咔' chn from dual union all
select '垃' chn from dual union all
select '嘸' chn from dual union all
select '拏' chn from dual union all
select '噢' chn from dual union all
select '妑' chn from dual union all
select '七' chn from dual union all
select '呥' chn from dual union all
select '仨' chn from dual union all
select '他' chn from dual union all
select '屲' chn from dual union all --no 'u'
select '屲' chn from dual union all --no 'v'
select '屲' chn from dual union all
select '夕' chn from dual union all
select '丫' chn from dual union all
select '帀' chn from dual union all
select p_chn chn from dual) a
order by chn
) b where rownum <= 27
;
return(p_c);
end;
/
(p_cName VARCHAR2)
RETURN VARCHAR2
AS
p_i SMALLINT;
p_L SMALLINT;
p_cHelpCode VARCHAR2(20);
p_e VARCHAR2(20);
p_iAscii SMALLINT;
BEGIN
p_i := 1;
p_L := 0;
p_cHelpCode := '';
while (p_L <=20 AND p_i <=length(p_cName)) loop
p_e := lower(substr(p_cname, p_i, 1));
p_iAscii := ASCII(p_e); IF (p_iAscii >=48 AND p_iAscii <=57) OR (p_iAscii>=97 AND p_iAscii <=122) or (p_iAscii=95) then
p_cHelpCode := p_cHelpCode + p_e;
ELSE
IF p_iAscii>=176 AND p_iAscii <=247 then
p_cHelpCode := p_cHelpCode + f_ch2py(p_e);
ELSE
p_L := p_L -1;
END IF;
p_i := p_i+1;
p_L := p_L+1;
END IF;
end loop;
RETURN p_cHelpCode;
END;
/
第一个函数执行成功,第二个还存在编译错误行号=18 列号=28 错误文本 = pLS-00103: encounterd the symbol "=" when expecting one of the following: (-+all case mod new null < an identifier> < a double-quoted delimited-identifier> <a bin variable> any avg cout current max min prior some sql stddev sum variance execute forall merge time timestamp interval date <a string literal with character set specification > < a number> <a single-quoted sql string > pipe
第21,26,28行业出现错误
21 encounterd the symbol "then" when expecting one of the following : * & - + : / at mod...
26 encouted the symbol "p_i"
encouted the symbol ":" when expecting one of the following :).....
28 encountered the symbol "if" when expecting one of the following : ; <an identifier>
(p_cName VARCHAR2)
RETURN VARCHAR2
AS
p_i SMALLINT;
p_L SMALLINT;
p_cHelpCode VARCHAR2(20);
p_e VARCHAR2(20);
p_iAscii SMALLINT;
BEGIN
p_i := 1;
p_L := 0;
p_cHelpCode := '';
while (p_L <=20 AND p_i <=length(p_cName)) loop
p_e := lower(substr(p_cname, p_i, 1));
p_iAscii := ASCII(p_e); IF (p_iAscii >=48 AND p_iAscii <=57) OR (p_iAscii>=97 AND p_iAscii <=122) or (p_iAscii=95) then
p_cHelpCode := p_cHelpCode + p_e;
ELSE
IF p_iAscii>=176 AND p_iAscii <=247 then
p_cHelpCode := p_cHelpCode + f_ch2py(p_e);
ELSE
p_L := p_L -1;
END IF;
p_i := p_i+1;
p_L := p_L+1;
END IF; end loop;
RETURN p_cHelpCode;
END;
/
现在第二个函数也编译成功了,
现在测试了下,没有反应,没有转换成功,
是不是oracle字符集的问题啊