ORACLE³£ÓÃFAQ

 µÚÒ»²¿·Ö¡¢SQL&PL/SQL
[Q]ÔõôÑù²éѯÌØÊâ×Ö·û£¬ÈçͨÅä·û%Óë_
[A]select * from table where name like 'A_%' escape ''
[Q]ÈçºÎ²åÈëµ¥ÒýºÅµ½Êý¾Ý¿â±íÖÐ
[A]¿ÉÒÔÓÃASCIIÂë´¦Àí£¬ÆäËüÌØÊâ×Ö·ûÈç&Ò²Ò»Ñù£¬Èç
insert into t values('i'||chr(39)||'m'); -- chr(39)´ú±í×Ö·û'
»òÕßÓÃÁ½¸öµ¥ÒýºÅ±íʾһ¸ö
or insert into t values('I''m'); --Á½¸ö''¿ÉÒÔ±íʾһ¸ö'
[Q]ÔõÑùÉèÖÃÊÂÎñÒ»ÖÂÐÔ
[A]set transaction [isolation level] read committed;ĬÈÏÓï¾ä¼¶Ò»ÖÂÐÔ
set transaction [isolation level] serializable;
read only;ÊÂÎñ¼¶Ò»ÖÂÐÔ
[Q]ÔõôÑùÀûÓÃÓαê¸üÐÂÊý¾Ý
[A]cursor c1 is
select * from tablename
where name is null for update [of column]
……
update tablename set column = ……
where current of c1;
[Q]ÔõÑù×Ô¶¨ÒåÒì³£
[A] pragma_exception_init(exception_name,error_number);
Èç¹ûÁ¢¼´Å׳öÒì³£
raise_application_error(error_number,error_msg,true|false);
ÆäÖÐnumber´Ó-20000µ½-20999£¬´íÎóÐÅÏ¢×î´ó2048B
Òì³£±äÁ¿
SQLCODE´íÎó´úÂë
SQLERRM´íÎóÐÅÏ¢
[Q]Ê®½øÖÆÓëÊ®Áù½øÖƵÄת»»
[A]8iÒÔÉÏ°æ±¾£º
to_char(100,'XX')
to_number('4D','XX')
8iÒÔϵĽøÖÆÖ®¼äµÄת»»²Î¿¼ÈçϽű¾
create or replace function to_base( p_dec in number, p_base in number )
return varchar2
is
l_str varchar2(255) default NULL;
l_num number default p_dec;
l_hex varchar2(16) default '0123456789ABCDEF';
begin
if ( p_dec is null or p_base is null ) then
return null;
end if;
if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then
raise PROGRAM_ERROR;
end if;
loop
l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
l_num := trunc( l_num/p_base );
exit when ( l_num = 0 );
end loop;
return l_str;
end to_base;
/
create or replace function to_dec
( p_str in varchar2,
p_from_base in number default 16 ) return number
is
l_num number default 0;
l_hex varchar2(16) default '0123456789ABCDEF';
begin
if ( p_str is null or p_from_base is null ) then
return null;
end if;
for i in 1 .. length(p_str) loop
l_num := l_num * p_from_base + instr(l_hex,upper(substr(p_str,i,1)))-1;
end lo