Question

写一个oracle 存储过程，统计输入字符串中大写字母个数

Answer 1

标准的存储过程

create or replace procedure P_STR
(v_str IN varchar2)
IS
v_len number(10);
v_dx varchar2(256);
j int;
begin
j:=0;
select length(v_str) into v_len from dual;
select lower(v_str) into v_dx from dual;
for i in 1 .. v_len
loop
if substr(v_str,i,1)<>substr(v_dx,i,1)
then
j:=j+1;
end if;
end loop;
dbms_output.put_line(j);
end;

--执行
BEGIN
p_str('afDASDF');
END;

引号里输入字符即可

Answer 2

这种一个输入一个输出的用函数。。

create or replace function uppernumb(invar in varchar2) return number
is
begin
return(length(REGEXP_REPLACE(invar,'[^[:upper:]]', '')));
end;

--测试
select uppernumb('Uisj79192GBSKL') from dual;
6

Answer 3

一条 SQL 语句足够了：

SELECT NVL( LENGTH( TRANSLATE(
'2229',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ' || '2229', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
),0) AS "Result"
FROM DUAL;

就是把需要保留的都标记下来，其他的都替换为空白

SELECT NVL( LENGTH( TRANSLATE(
'2KRW229',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ' || '2KRW229', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
),0) AS "Result"
FROM DUAL;

SELECT NVL( LENGTH( TRANSLATE(
'2229A',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ' || '2229A', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
),0) AS "Result"
FROM DUAL;

SELECT NVL( LENGTH( TRANSLATE(
'2229',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ' || '2229', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
),0) AS "Result"
FROM DUAL;