CREATE OR REPLACE FUNCTION get_sql(p_table VARCHAR2) RETURN VARCHAR2 IS l_str VARCHAR2(1000); BEGIN l_str:='select ename from '||p_table; RETURN l_str; END; /CREATE OR REPLACE PROCEDURE proc_test(p_table VARCHAR2) IS l_str VARCHAR2(1000); BEGIN l_str:=get_sql(p_table); dbms_output.put_line(l_str); END; /BEGIN proc_test('scott.emp'); END; /
declare v_sql varchar2(1000); begin select 函数() into v_sql from dual; end; /
SQL> create or replace function f1 2 return nvarchar2 3 is 4 begin 5 return 'select * from a'; 6 end; 7 /Function createdSQL> SQL> create or replace procedure p1 2 as 3 strsql nvarchar2(200); 4 begin 5 select f1 into strsql from dual; 6 dbms_output.put_line(strsql); 7 end; 8 /Procedure createdSQL> set serveroutput on; SQL> exec p1;select * from aPL/SQL procedure successfully completedSQL>
CREATE OR REPLACE FUNCTION get_sql(p_table VARCHAR2) RETURN VARCHAR2 IS l_str VARCHAR2(1000); BEGIN l_str:='select ename from '||p_table; RETURN l_str; END; /CREATE OR REPLACE PROCEDURE proc_test(p_table VARCHAR2,p_out OUT Sys_Refcursor) IS l_str VARCHAR2(1000); BEGIN l_str:=get_sql(p_table); dbms_output.put_line(l_str); OPEN p_out FOR l_str; END; /DECLARE l_c SYS_REFCURSOR; l_v VARCHAR2(100); BEGIN proc_test('scott.emp',l_c); LOOP FETCH l_c INTO l_v; dbms_output.put_line(l_v); EXIT WHEN l_c%NOTFOUND; END LOOP; END; / 输出: select ename from scott.emp SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER MILLER
先自定义一个游标tcursor 再存储过程中定义变量参数加上cur out tcursor,然后open cur for SQL语句
create or replace package body EXPORT_CLASS_SCORE isProcedure getClassScore( stu_no in char, stu_term in char, return_list out outscore ) as begin sql_st:=list1(stu_no,stu_term); open return_list for sql_st; return return_list; end getclassscore;
FUNCTION list1(tmp_stu IN CHAR,tmp_term IN CHAR) RETURN VARCHAR2 IS v_sumv VARCHAR2(4000); BEGIN v_sumv := 'select student_no,class_code,' ; FOR cur IN (SELECT distinct curricula_code FROM compulsory_score WHERE student_no=tmp_stu and term = tmp_term) LOOP v_sumv := v_sumv||'sum(decode(curricula_code,'''||cur.curricula_code||''',score)) "'|| cur.curricula_code|| '",'; END LOOP; v_sumv := substr(v_sumv, 1, length(v_sumv)-1) ; v_sumv :=v_sumv||' from compulsory_score where score_kind = ''F'' and student_no ='''||tmp_stu||''' group by student_no,class_code'; RETURN v_sumv; END list1; end EXPORT_CLASS_SCORE; 错误提示: Compilation errors for PACKAGE BODY CAICHANG.EXPORT_CLASS_SCOREError: PLS-00103: 出现符号"SQL_ST"在需要下列之一时: select Line: 7 Text: open return_list for sql_st;Error: PLS-00103: 出现符号"END"在需要下列之一时: beginfunctionpackagepragma procedureformexternal Line: 26 Text: end EXPORT_CLASS_SCORE; 这是我的源代码,请帮忙分析下,谢谢
CREATE OR REPLACE FUNCTION get_sql(p_table VARCHAR2) RETURN VARCHAR2 IS
l_str VARCHAR2(1000);
BEGIN
l_str:='select ename from '||p_table;
RETURN l_str;
END;
/CREATE OR REPLACE PROCEDURE proc_test(p_table VARCHAR2) IS
l_str VARCHAR2(1000);
BEGIN
l_str:=get_sql(p_table);
dbms_output.put_line(l_str);
END;
/BEGIN
proc_test('scott.emp');
END;
/
v_sql varchar2(1000);
begin
select 函数() into v_sql from dual;
end;
/
SQL> create or replace function f1
2 return nvarchar2
3 is
4 begin
5 return 'select * from a';
6 end;
7 /Function createdSQL>
SQL> create or replace procedure p1
2 as
3 strsql nvarchar2(200);
4 begin
5 select f1 into strsql from dual;
6 dbms_output.put_line(strsql);
7 end;
8 /Procedure createdSQL> set serveroutput on;
SQL> exec p1;select * from aPL/SQL procedure successfully completedSQL>
CREATE OR REPLACE FUNCTION get_sql(p_table VARCHAR2) RETURN VARCHAR2 IS
l_str VARCHAR2(1000);
BEGIN
l_str:='select ename from '||p_table;
RETURN l_str;
END;
/CREATE OR REPLACE PROCEDURE proc_test(p_table VARCHAR2,p_out OUT Sys_Refcursor) IS
l_str VARCHAR2(1000);
BEGIN
l_str:=get_sql(p_table);
dbms_output.put_line(l_str);
OPEN p_out FOR l_str;
END;
/DECLARE
l_c SYS_REFCURSOR;
l_v VARCHAR2(100);
BEGIN
proc_test('scott.emp',l_c);
LOOP
FETCH l_c INTO l_v;
dbms_output.put_line(l_v);
EXIT WHEN l_c%NOTFOUND;
END LOOP;
END;
/
输出:
select ename from scott.emp
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
MILLER
再存储过程中定义变量参数加上cur out tcursor,然后open cur for SQL语句
as
begin
sql_st:=list1(stu_no,stu_term);
open return_list for sql_st;
return return_list;
end getclassscore;
FUNCTION list1(tmp_stu IN CHAR,tmp_term IN CHAR)
RETURN VARCHAR2
IS
v_sumv VARCHAR2(4000);
BEGIN
v_sumv := 'select student_no,class_code,' ;
FOR cur IN (SELECT distinct curricula_code FROM compulsory_score WHERE student_no=tmp_stu and term = tmp_term) LOOP
v_sumv := v_sumv||'sum(decode(curricula_code,'''||cur.curricula_code||''',score)) "'|| cur.curricula_code|| '",';
END LOOP;
v_sumv := substr(v_sumv, 1, length(v_sumv)-1) ;
v_sumv :=v_sumv||' from compulsory_score where score_kind = ''F'' and student_no ='''||tmp_stu||''' group by student_no,class_code';
RETURN v_sumv;
END list1;
end EXPORT_CLASS_SCORE;
错误提示:
Compilation errors for PACKAGE BODY CAICHANG.EXPORT_CLASS_SCOREError: PLS-00103: 出现符号"SQL_ST"在需要下列之一时:
select
Line: 7
Text: open return_list for sql_st;Error: PLS-00103: 出现符号"END"在需要下列之一时:
beginfunctionpackagepragma
procedureformexternal
Line: 26
Text: end EXPORT_CLASS_SCORE;
这是我的源代码,请帮忙分析下,谢谢