ORACAL9I存储过程动态变量做表名,求助!例如,我有很多表,T_1,T_2,T_3有一存储过程如下,CREATE OR REPLACE PROCEDURE Pr_test
(vlogin varchar2,vitem number)
AS
vtable varchar2(200);
BEGIN
vtable:='T'||vitem;
execute immediate insert into vtable values(vlogin,vitem);
commit;
end;
这样写,执行不了SQL语句,请问要怎么样写才能执行呢?谢谢
(vlogin varchar2,vitem number)
AS
vtable varchar2(200);
BEGIN
vtable:='T'||vitem;
execute immediate insert into vtable values(vlogin,vitem);
commit;
end;
这样写,执行不了SQL语句,请问要怎么样写才能执行呢?谢谢
你看看這個網頁~~
VTABLE VARCHAR2(200);
BEGIN
VTABLE := 'T' || VITEM;
--EXECUTE IMMEDIATE 'insert into vtable values('''||vlogin||''','''||VTABLE||''')';
insert into vtable VALUES(vlogin,VTABLE);
COMMIT;
END;
/
看错了,改正下:
CREATE OR REPLACE PROCEDURE PR_TEST(VLOGIN VARCHAR2, VITEM NUMBER) AS
VTABLE VARCHAR2(200);
BEGIN
VTABLE := 'T' || VITEM;
EXECUTE IMMEDIATE 'insert into '||vtable||' values('''||vlogin||''','||vitem||')';
COMMIT;
END;
/
CREATE OR REPLACE PROCEDURE Pr_test
(vlogin varchar2,vitem number)
AS
vtable varchar2(200);
ssql varchar2(1000);
BEGIN
vtable:='T'||vitem;
ssql := 'insert into '||vtable|' values('''|vlogin||''','||vitem||')';
execute immediate ssql;
commit;
end;
/
这样执行不了啊
SQL> create table t1(cname nvarchar2(30),idx int);Table createdSQL> select * from t1;CNAME IDX
------------------------------------------------------------ ---------------------------------------SQL>
SQL> CREATE OR REPLACE PROCEDURE Pr_test
2 (vlogin varchar2,vitem number)
3 AS
4 vtable varchar2(200);
5 ssql varchar2(1000);
6 BEGIN
7 vtable:='T'||vitem;
8 ssql := 'insert into '||vtable||' values('''||vlogin||''','||vitem||')';
9 dbms_output.put_line(ssql);
10 execute immediate ssql;
11 commit;
12 end;
13 /Procedure created
SQL> exec Pr_test('a',1);insert into T1 values('a',1)PL/SQL procedure successfully completedSQL> select * from t1;CNAME IDX
------------------------------------------------------------ ---------------------------------------
a 1SQL>
vtable varchar2(200);
ls varchar2(200);
BEGIN
vtable:='a'||ai_;
ls := 'insert into '||vtable||' values('''||as_||''')';
dbms_output.put_line(ls);
execute immediate ls;
commit;
end;
SQL> exec ap(1,'a');insert into a1 values('a')PL/SQL procedure successfully completedSQL> rollback;Rollback completeSQL>
TASKID NUMBER (38) NOT NULL,
ITEMID NUMBER (5,2),
WEBSITE VARCHAR2 (255),
STDSCORE NUMBER (4,1),
STDGRADE VARCHAR2 (20),
SPECIFIER VARCHAR2 (20))CREATE OR REPLACE PROCEDURE Pr_test1
(vlogin varchar2,vitem varchar2)
as
str_sql varchar2(2000);
vtable varchar2(200);
begin
vtable:='cet_'||vitem;
str_sql:='insert into '||vtable|| ' values'||'('||''''||vlogin||''''||','||-999||','||''''||vitem||''''||','||-999||','||'''-999'''||','||''''');';
dbms_output.put_line(str_Sql);
execute immediate str_sql;
commit;
end;
/exec Pr_test1('200','taskx'); 朋友 帮忙看看有什么问题呢 郁闷了 老报错
SQL> CREATE TABLE CET_TASKx (
2 TASKID NUMBER (38) NOT NULL,
3 ITEMID NUMBER (5,2),
4 WEBSITE VARCHAR2 (255),
5 STDSCORE NUMBER (4,1),
6 STDGRADE VARCHAR2 (20),
7 SPECIFIER VARCHAR2 (20)
8 );Table created
SQL> CREATE OR REPLACE PROCEDURE Pr_test1
2 (vlogin varchar2,vitem varchar2)
3 as
4 str_sql varchar2(2000);
5 vtable varchar2(200);
6 begin
7 vtable:='cet_'||vitem;
8 str_sql:='insert into '||vtable||' values('||vlogin||','||'-999'||','''||vitem||''','||'-999'||',''website'','||'''-999'''||')';
9 dbms_output.put_line(str_Sql);
10 execute immediate str_sql;
11 commit;
12 end;
13 /Procedure createdSQL> exec Pr_test1('200','taskx');insert into cet_taskx values(200,-999,'taskx',-999,'website','-999')PL/SQL procedure successfully completedSQL> select * from CET_TASKx; TASKID ITEMID WEBSITE STDSCORE STDGRADE SPECIFIER
--------------------------------------- ------- -------------------------------------------------------------------------------- -------- -------------------- --------------------
200 -999.00 taskx -999.0 website -999SQL>