我想把任意运算:如3*24*60*60算出来的结果,取前4个的文本字符,这样的语句该怎么写呢?我试着这样写:select to_char(a,0,4) from dual where a=3*24*60*60 结果说未知的a 吧 或者substr函数么? 关键是我这样写有问题,那应该怎样把变量a临时用上呢?
create or replace function getfourstr(instr number) return varchar2 is a varchar2(50); b varchar2(50); begin select instr into a from dual; select substr(a,0,4) into b from dual; return b; end;select getfourstr(3*24*60*60) from dual;
SQL> create or replace function test(p_str in varchar2) return number 2 is 3 v_str number; 4 begin 5 execute immediate 'select '||p_str||' from dual' into v_str; 6 return v_str; 7 end; 8 /函数已创建。SQL> select substr(test('3*24*60*60'),1,4) from dual;SUBSTR(TEST('3*2 ---------------- 2592SQL>
创建的函数带有编译错误,我是在oracle sql plus 里运行的
怎么我执行这一段,老是说name is already used by an existing object ,而且我换了p_str、v_str 和 varchar2 的名称后,还是这样说
把test换个名字,你可能定义过叫test的对象
SQL> create or replace function tes(p_str in varchar2) return number; 2 is 3 v_str number; 4 begin 5 execute immediate 'select '||p_str||' from dual' into v_str; 6 return v_str; 7 end; 8 /警告: 创建的函数带有编译错误。
你多了个分号 create or replace function tes(p_str in varchar2) return number is v_str number; begin execute immediate 'select '||p_str||' from dual' into v_str; return v_str; end; /
难道是版本问题么?我的是oracle 9.2 for windows
试试这个还报错不 create or replace function tes(p_str in varchar2) return number is v_str number; mycursor INTEGER; ignore INTEGER; begin mycursor := dbms_sql.open_cursor; dbms_sql.parse(mycursor,'select '||p_str||' from dual',1); DBMS_SQL.DEFINE_COLUMN(mycursor,1,str,22); ignore := dbms_sql.execute(mycursor); if DBMS_SQL.FETCH_ROWS(mycursor)<> 0 then DBMS_SQL.COLUMN_VALUE(mycursor,1,str); end if; dbms_sql.close_cursor(mycursor); return str; end; /
is
a varchar2(50);
b varchar2(50);
begin
select instr into a from dual;
select substr(a,0,4) into b from dual;
return b;
end;select getfourstr(3*24*60*60) from dual;
2 is
3 v_str number;
4 begin
5 execute immediate 'select '||p_str||' from dual' into v_str;
6 return v_str;
7 end;
8 /函数已创建。SQL> select substr(test('3*24*60*60'),1,4) from dual;SUBSTR(TEST('3*2
----------------
2592SQL>
SQL> create or replace function tes(p_str in varchar2) return number;
2 is
3 v_str number;
4 begin
5 execute immediate 'select '||p_str||' from dual' into v_str;
6 return v_str;
7 end;
8 /警告: 创建的函数带有编译错误。
create or replace function tes(p_str in varchar2) return number
is
v_str number;
begin
execute immediate 'select '||p_str||' from dual' into v_str;
return v_str;
end;
/
试试这个还报错不
create or replace function tes(p_str in varchar2) return number
is
v_str number;
mycursor INTEGER;
ignore INTEGER;
begin
mycursor := dbms_sql.open_cursor;
dbms_sql.parse(mycursor,'select '||p_str||' from dual',1);
DBMS_SQL.DEFINE_COLUMN(mycursor,1,str,22);
ignore := dbms_sql.execute(mycursor);
if DBMS_SQL.FETCH_ROWS(mycursor)<> 0 then
DBMS_SQL.COLUMN_VALUE(mycursor,1,str);
end if;
dbms_sql.close_cursor(mycursor);
return str;
end;
/