i have had a same questionbut no person can answer this questionzhenyukeji's answer can not be run 1 declare 2 table_name varchar2(10); 3 begin 4 table_name := 'tabf_sex'; 5 select * from :table_name; 6* end; SQL> / SP2-0552: 未说明结合变量"TABLE_NAME"
zhenyukeji 你所说的方法好像不行啊!
用动态SQL,例如: CREATE OR REPLACE PROCEDURE query_invoice( month VARCHAR2, year VARCHAR2) IS TYPE cur_typ IS REF CURSOR; c cur_typ; query_str VARCHAR2(200); inv_num NUMBER; inv_cust VARCHAR2(20); inv_amt NUMBER; BEGIN query_str := 'SELECT num, cust, amt FROM inv_' || month ||'_'|| year || ' WHERE invnum = :id'; OPEN c FOR query_str USING inv_num; LOOP FETCH c INTO inv_num, inv_cust, inv_amt; EXIT WHEN c%NOTFOUND; -- process row here END LOOP; CLOSE c; END; /
OPEN c FOR query_str USING inv_num;中USING inv_num是什么意思? WHERE invnum = :id 中 :id 这样表示是什么意思?
1 declare
2 table_name varchar2(10);
3 begin
4 table_name := 'tabf_sex';
5 select * from :table_name;
6* end;
SQL> /
SP2-0552: 未说明结合变量"TABLE_NAME"
CREATE OR REPLACE PROCEDURE query_invoice(
month VARCHAR2,
year VARCHAR2) IS
TYPE cur_typ IS REF CURSOR;
c cur_typ;
query_str VARCHAR2(200);
inv_num NUMBER;
inv_cust VARCHAR2(20);
inv_amt NUMBER;
BEGIN
query_str := 'SELECT num, cust, amt FROM inv_' || month ||'_'|| year
|| ' WHERE invnum = :id';
OPEN c FOR query_str USING inv_num;
LOOP
FETCH c INTO inv_num, inv_cust, inv_amt;
EXIT WHEN c%NOTFOUND;
-- process row here
END LOOP;
CLOSE c;
END;
/
WHERE invnum = :id 中 :id 这样表示是什么意思?