--可以有多个用户和密码,进行判断打印输出 这个是什么意思? create or replace function fun_exp_mess_noml(mess_value in varchar2,mess_nomal in varchar2) return varchar2 as strsql varchar2(1000); begin strsql:='用户名:'||mess_value||',密码:'||mess_nomal; return strsql; end; declare strsql varchar2(1000); begin strsql:=fun_exp_mess_noml('user','123344'); dbms_output.put_line(strsql); end; / 用户名:user,密码:123344PL/SQL 过程已成功完成。
--简单写了一个,看看符合不? --1、创建函数 create or replace function fun_exp_mess_noml(mess_value in varchar2,mess_nomal in varchar2) return varchar2 as strsql varchar2(1000); mess varchar2(1000); format varchar2(1000); user_name varchar2(1000); user_pwd varchar2(1000); num0 NUMBER:=1; num1 NUMBER:=0; num2 NUMBER:=0; begin mess := mess_value; format := mess_nomal; select InStr(format,',') into num2 from dual; user_name:= SubStr(format,1,num2-2); format:=SubStr(format,num2+1); user_pwd:= replace(format,'?',''); loop select InStr(mess,',') into num0 from dual; strsql:= strsql||user_name; strsql:= strsql||SubStr(mess,1,num0-1); mess:=SubStr(mess,num0+1); select InStr(mess,',') into num1 from dual; strsql:= strsql||user_pwd; strsql:= strsql||SubStr(mess,1,num1-1)||','; mess:=SubStr(mess,num1+1); select InStr(mess,',') into num0 from dual; if num0=0 then strsql:= SubStr(strsql,1,Length(strsql)-1); strsql:= strsql||mess; end if; exit when num0 = 0 ; end loop ; return strsql; end; --2、调用 declare strsql varchar2(1000); mess_value varchar2(1000):='user,123456,admin,123456,abs,45678'; mess_nomal varchar2(1000):='用户名?,密码?'; begin dbms_output.put_line('mess_value:'||mess_value); dbms_output.put_line('mess_nomal:'||mess_nomal); strsql:=fun_exp_mess_noml(mess_value,mess_nomal); dbms_output.put_line('输入结果:'||strsql); end;--3、结果: PL/SQL block, executed in 0.016 sec. mess_value:user,123456,admin,123456,abs,45678 mess_nomal:用户名?,密码? 输入结果:用户名user密码123456,用户名admin密码123456,用户名abs密码45678 Total execution time 0.016 sec.
--写了哈,实现原理一样 --不好意思,家里没法编辑格式--1、创建函数 create or replace function fun_exp_mess_noml(mess_value in varchar2,mess_nomal in varchar2) return varchar2 as strsql varchar2(1000); mess varchar2(1000); format varchar2(1000); user_name varchar2(1000); user_pwd varchar2(1000); num0 NUMBER:=0; num1 NUMBER:=0; begin mess := mess_value; format := mess_nomal;
loop select InStr(mess,',') into num0 from dual; select InStr(format,',') into num1 from dual; user_name:= SubStr(format,1,num1-2); format:=SubStr(format,num1+1); strsql:= strsql||user_name; strsql:= strsql||SubStr(mess,1,num0-1)||','; mess:=SubStr(mess,num0+1);
select InStr(mess,',') into num0 from dual;
if num0=0 then strsql:= strsql||SubStr(format,1,Length(format)-1); strsql:= strsql||mess; end if; exit when num0 = 0 ; end loop ; return strsql; end;--2、调用 declare strsql varchar2(1000); mess_value varchar2(1000):='user,admin,abs,45678'; mess_nomal varchar2(1000):='---?,---?,---?,---?'; begin dbms_output.put_line('mess_value:'||mess_value); dbms_output.put_line('mess_nomal:'||mess_nomal); strsql:=fun_exp_mess_noml(mess_value,mess_nomal); dbms_output.put_line('result= '||strsql); end;--3、结果: mess_value:user,admin,abs,45678 mess_nomal:---?,---?,---?,---? result= ---user,---admin,---abs,---45678
create or replace function fun_exp_mess_noml(mess_value in varchar2,mess_nomal in varchar2)
return varchar2
as
strsql varchar2(1000);
begin
strsql:='用户名:'||mess_value||',密码:'||mess_nomal;
return strsql;
end; declare
strsql varchar2(1000);
begin
strsql:=fun_exp_mess_noml('user','123344');
dbms_output.put_line(strsql);
end;
/
用户名:user,密码:123344PL/SQL 过程已成功完成。
进行判断
当传人入'user,123456,admin,123456,abs,45678' 时也一样打印出多个用户名为user密码为123456
'用户名为admin,密码为123456','用户名为abs,密码为45678'
--简单写了一个,看看符合不?
--1、创建函数
create or replace function fun_exp_mess_noml(mess_value in varchar2,mess_nomal in varchar2)
return varchar2
as
strsql varchar2(1000);
mess varchar2(1000);
format varchar2(1000);
user_name varchar2(1000);
user_pwd varchar2(1000);
num0 NUMBER:=1;
num1 NUMBER:=0;
num2 NUMBER:=0;
begin
mess := mess_value;
format := mess_nomal;
select InStr(format,',') into num2 from dual;
user_name:= SubStr(format,1,num2-2);
format:=SubStr(format,num2+1);
user_pwd:= replace(format,'?','');
loop
select InStr(mess,',') into num0 from dual;
strsql:= strsql||user_name;
strsql:= strsql||SubStr(mess,1,num0-1);
mess:=SubStr(mess,num0+1);
select InStr(mess,',') into num1 from dual;
strsql:= strsql||user_pwd;
strsql:= strsql||SubStr(mess,1,num1-1)||',';
mess:=SubStr(mess,num1+1);
select InStr(mess,',') into num0 from dual;
if num0=0 then
strsql:= SubStr(strsql,1,Length(strsql)-1);
strsql:= strsql||mess;
end if;
exit when num0 = 0 ;
end loop ;
return strsql;
end;
--2、调用
declare
strsql varchar2(1000);
mess_value varchar2(1000):='user,123456,admin,123456,abs,45678';
mess_nomal varchar2(1000):='用户名?,密码?';
begin
dbms_output.put_line('mess_value:'||mess_value);
dbms_output.put_line('mess_nomal:'||mess_nomal);
strsql:=fun_exp_mess_noml(mess_value,mess_nomal);
dbms_output.put_line('输入结果:'||strsql);
end;--3、结果:
PL/SQL block, executed in 0.016 sec.
mess_value:user,123456,admin,123456,abs,45678
mess_nomal:用户名?,密码?
输入结果:用户名user密码123456,用户名admin密码123456,用户名abs密码45678
Total execution time 0.016 sec.
列如('a1,a2,a3,a4',----?,-----?,------?,----?)
输出结果为:----a1,----a2,----a3,----a4信息
输出的提示信息根据前一个参数的多少二提示
--不好意思,家里没法编辑格式--1、创建函数
create or replace function fun_exp_mess_noml(mess_value in varchar2,mess_nomal in varchar2)
return varchar2
as
strsql varchar2(1000);
mess varchar2(1000);
format varchar2(1000);
user_name varchar2(1000);
user_pwd varchar2(1000);
num0 NUMBER:=0;
num1 NUMBER:=0;
begin
mess := mess_value;
format := mess_nomal;
loop
select InStr(mess,',') into num0 from dual;
select InStr(format,',') into num1 from dual;
user_name:= SubStr(format,1,num1-2);
format:=SubStr(format,num1+1); strsql:= strsql||user_name;
strsql:= strsql||SubStr(mess,1,num0-1)||','; mess:=SubStr(mess,num0+1);
select InStr(mess,',') into num0 from dual;
if num0=0 then
strsql:= strsql||SubStr(format,1,Length(format)-1);
strsql:= strsql||mess;
end if;
exit when num0 = 0 ;
end loop ;
return strsql;
end;--2、调用
declare
strsql varchar2(1000);
mess_value varchar2(1000):='user,admin,abs,45678';
mess_nomal varchar2(1000):='---?,---?,---?,---?';
begin
dbms_output.put_line('mess_value:'||mess_value);
dbms_output.put_line('mess_nomal:'||mess_nomal);
strsql:=fun_exp_mess_noml(mess_value,mess_nomal);
dbms_output.put_line('result= '||strsql);
end;--3、结果:
mess_value:user,admin,abs,45678
mess_nomal:---?,---?,---?,---?
result= ---user,---admin,---abs,---45678