CREATE OR REPLACE PROCEDURE demo(salary IN NUMBER) AS
cursor_name INTEGER;
rows_processed INTEGER;
BEGIN
cursor_name := dbms_sql.open_cursor;
DBMS_SQL.PARSE(cursor_name, ’DELETE FROM emp WHERE sal > :x’,dbms_sql.native);
DBMS_SQL.BIND_VARIABLE(cursor_name, ’:x’, salary);
rows_processed := dbms_sql.execute(cursor_name);
DBMS_SQL.close_cursor(cursor_name); EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;
cursor_name INTEGER;
rows_processed INTEGER;
BEGIN
cursor_name := dbms_sql.open_cursor;
DBMS_SQL.PARSE(cursor_name, ’DELETE FROM emp WHERE sal > :x’,dbms_sql.native);
DBMS_SQL.BIND_VARIABLE(cursor_name, ’:x’, salary);
rows_processed := dbms_sql.execute(cursor_name);
DBMS_SQL.close_cursor(cursor_name); EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;
create or replace function UP_TTTT return number IS
cur_name NUMBER;
ret NUMBER;
ls_sql VARCHAR2(1000);
ls_number varchar2(20);
BEGIN
ls_sql:='begin :v:=UK_EMIS.UF_CLEARDATA(''100''); end;';
Cur_name := DBMS_SQL.Open_Cursor;
DBMS_SQL.PARSE(cur_name,ls_sql,DBMS_SQL.Native);
dbms_sql.bind_variable(cur_name,':v',ls_number);
ret := DBMS_SQL.Execute(cur_name);
DBMS_sql.variable_value(cur_name,':v',ls_number);
DBMS_SQL.Close_Cursor(cur_name); return ret;
end UP_TTTT;
...
begin
...
p_out:=ret;
..
end;然后定义一个变量传进来,就会得到一个返回值了.
是在执行ret := DBMS_SQL.Execute(cur_name);这句时候
可以调用动态指定的函数,但是在函数返回的时候出错。
create or replace procedure UP_TTTT
IS
cur_name NUMBER;
ret NUMBER;
ls_sql VARCHAR2(1000);
ls_number varchar2(20);
BEGIN
ls_sql:='select UK_EMIS.UF_CLEARDATA(''100'') from dual';
execute immediate ls_sql into ls_number;
end UP_TTTT;
END;
/