declare v_sqlstring number; begin select sum(your_field1) into v_sqlstring from table; end;
有人说用DBMS_SQL 写不知怎写
CREATE OR REPLACE PROCEDURE "PRO_YEARCHANGE" (sqlstring in varchar2) ... select sqlstring into v_jcje from table; ...
前面回答错误 是要用DBMS_SQL
How to write ??? Do you kown ^OO^
CREATE OR REPLACE PROCEDURE "PRO_YEARCHANGE" (v_sqlstring out varchar2) as begin select sum(your_field1) into v_sqlstring from table; end; /楼主对问题描述不太清楚
是否是这样的: create or replace procedure test(sqlstring IN varchar2) is v_sql_statement VARCHAR2(100):='select '||sqlstring||' from s_dept'; v_cursor INTEGER; v_return INTEGER; v_id s_dept.id%TYPE; v_row integer; v_count integer; begin v_cursor :=DBMS_SQL.open_cursor; DBMS_SQL.parse(v_cursor,v_sql_statement,DBMS_SQL.NATIVE); DBMS_SQL.define_column(v_cursor,1,v_id); v_return :=DBMS_SQL.execute(v_cursor); while dbms_sql.fetch_rows(v_cursor)<>0 LOOP dbms_SQL.column_value(v_cursor,1,v_id); DBMS_OUTPUT.put_line(to_CHAR(v_id)); END LOOP; DBMS_SQL.CLOSE_CURSOR(v_cursor); end test; / SQL> execute test('id'); 10 31 32 33 34 35 41 42 43 44 45 50PL/SQL 过程已成功完成。
题意理解错:) 应是这样: create or replace procedure test(sqlstring IN varchar2) is v_sql_statement VARCHAR2(100):='select '||sqlstring||' from t1'; v_cursor INTEGER; v_return INTEGER; v_sum integer; begin v_cursor :=DBMS_SQL.open_cursor; DBMS_SQL.parse(v_cursor,v_sql_statement,DBMS_SQL.NATIVE); DBMS_SQL.define_column(v_cursor,1,v_sum); v_return :=DBMS_SQL.execute(v_cursor); v_return :=dbms_sql.fetch_rows(v_cursor); dbms_SQL.column_value(v_cursor,1,v_sum); DBMS_OUTPUT.put_line(to_CHAR(v_sum)); end test; / SQL> execute test('sum(c1)'); 4568PL/SQL 过程已成功完成。
SQL> create or replace procedure test_sql(sqlstring IN varchar2) is 2 sql_stmt VARCHAR2(200); 3 sum_num number(10); 4 begin 5 sql_stmt:='select '||sqlstring||' from t1'; 6 execute immediate sql_stmt into sum_num; 7 DBMS_OUTPUT.put_line(to_CHAR(sum_num)); 8 end; 9 /过程已创建。SQL> execute test_sql('sum(id)'); 1600PL/SQL 过程已成功完成。SQL>
v_sqlstring number;
begin
select sum(your_field1) into v_sqlstring from table;
end;
写不知怎写
...
select sqlstring into v_jcje from table;
...
是要用DBMS_SQL
as
begin
select sum(your_field1) into v_sqlstring from table;
end;
/楼主对问题描述不太清楚
create or replace procedure test(sqlstring IN varchar2) is
v_sql_statement VARCHAR2(100):='select '||sqlstring||' from s_dept';
v_cursor INTEGER;
v_return INTEGER;
v_id s_dept.id%TYPE;
v_row integer;
v_count integer;
begin
v_cursor :=DBMS_SQL.open_cursor;
DBMS_SQL.parse(v_cursor,v_sql_statement,DBMS_SQL.NATIVE);
DBMS_SQL.define_column(v_cursor,1,v_id);
v_return :=DBMS_SQL.execute(v_cursor);
while dbms_sql.fetch_rows(v_cursor)<>0 LOOP
dbms_SQL.column_value(v_cursor,1,v_id);
DBMS_OUTPUT.put_line(to_CHAR(v_id));
END LOOP;
DBMS_SQL.CLOSE_CURSOR(v_cursor);
end test;
/
SQL> execute test('id');
10
31
32
33
34
35
41
42
43
44
45
50PL/SQL 过程已成功完成。
应是这样:
create or replace procedure test(sqlstring IN varchar2) is
v_sql_statement VARCHAR2(100):='select '||sqlstring||' from t1';
v_cursor INTEGER;
v_return INTEGER;
v_sum integer;
begin
v_cursor :=DBMS_SQL.open_cursor;
DBMS_SQL.parse(v_cursor,v_sql_statement,DBMS_SQL.NATIVE);
DBMS_SQL.define_column(v_cursor,1,v_sum);
v_return :=DBMS_SQL.execute(v_cursor);
v_return :=dbms_sql.fetch_rows(v_cursor);
dbms_SQL.column_value(v_cursor,1,v_sum);
DBMS_OUTPUT.put_line(to_CHAR(v_sum));
end test;
/
SQL> execute test('sum(c1)');
4568PL/SQL 过程已成功完成。
2 sql_stmt VARCHAR2(200);
3 sum_num number(10);
4 begin
5 sql_stmt:='select '||sqlstring||' from t1';
6 execute immediate sql_stmt into sum_num;
7 DBMS_OUTPUT.put_line(to_CHAR(sum_num));
8 end;
9 /过程已创建。SQL> execute test_sql('sum(id)');
1600PL/SQL 过程已成功完成。SQL>