create or replace function makdt(tablename varchar2) return number is -- Result number; strsql varchar2(10000); -- s_rec refcursor; num number; begin execute immediate 'select count(*) from ' ||tablename into num ; dbms_output.put_line(num); return(num); end makdt;
--procedure实现 CREATE OR REPLACE PROCEDURE MAKDT(tablename VARCHAR2) AS v_sql VARCHAR2(10000); cur_type SYS_REFCURSOR; ename VARCHAR2(20); BEGIN v_sql := 'SELECT ENAME FROM '||tablename; OPEN cur_type FOR v_sql; LOOP FETCH cur_type INTO ename; EXIT WHEN cur_type%NOTFOUND; DBMS_OUTPUT.PUT_LINE(ename); END LOOP; END MAKDT; /--function实现 CREATE OR REPLACE FUNCTION MAKDT(tablename VARCHAR2) RETURN NUMBER AS v_sql VARCHAR2(10000); cur_type SYS_REFCURSOR; ename VARCHAR2(20); BEGIN v_sql := 'SELECT ENAME FROM '||tablename; OPEN cur_type FOR v_sql; LOOP FETCH cur_type INTO ename; EXIT WHEN cur_type%NOTFOUND; DBMS_OUTPUT.PUT_LINE(ename); END LOOP; RETURN 0;--你想要反回的数据 END MAKDT; /
for是默认要打开cursor的,所以for不能用于已经打开的cursor。 打开的cursor使用 loop fetch 游标名 into 变量列表; exit when 游标名%notfound; do something.... end loop;
-- Result number;
strsql varchar2(10000);
-- s_rec refcursor;
num number;
begin
execute immediate 'select count(*) from ' ||tablename into num ;
dbms_output.put_line(num);
return(num);
end makdt;
CREATE OR REPLACE PROCEDURE MAKDT(tablename VARCHAR2)
AS
v_sql VARCHAR2(10000);
cur_type SYS_REFCURSOR;
ename VARCHAR2(20);
BEGIN
v_sql := 'SELECT ENAME FROM '||tablename;
OPEN cur_type FOR v_sql;
LOOP
FETCH cur_type INTO ename;
EXIT WHEN cur_type%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(ename);
END LOOP;
END MAKDT;
/--function实现
CREATE OR REPLACE FUNCTION MAKDT(tablename VARCHAR2)
RETURN NUMBER
AS
v_sql VARCHAR2(10000);
cur_type SYS_REFCURSOR;
ename VARCHAR2(20);
BEGIN
v_sql := 'SELECT ENAME FROM '||tablename;
OPEN cur_type FOR v_sql;
LOOP
FETCH cur_type INTO ename;
EXIT WHEN cur_type%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(ename);
END LOOP;
RETURN 0;--你想要反回的数据
END MAKDT;
/
打开的cursor使用
loop
fetch 游标名 into 变量列表;
exit when 游标名%notfound;
do something....
end loop;