declare
v_SQLStringInit Varchar2(8000);--动态SQL语句原型
v_SQLString varchar2(8000);--动态SQL语句
v_TempTableName Varchar2(50);--动态表名称
v_LoopVar NUMBER;--循环递增变量
BEGIN
--动态SQL语句原型初始化
v_SQLStringInit := '
create table {TableName} (
USERID NUMBER(10) not null,
USERNAME VARCHAR2(20) not null,
PASSWORD VARCHAR2(20) not null,
constraint PK_{TableName} primary key (USERID)
);
';
--循环变量初始化
v_LoopVar := 1;
WHILE v_LoopVar<=2 LOOP
--生成动态表名称
v_TempTableName := concat('UserInfo_',lpad(to_char(v_LoopVar),3,'0'));
--生成动态SQL实例
v_SQLString := REPLACE(v_SQLStringInit,'{TableName}',v_TempTableName);
--DBMS_OUTPUT.PUT_LINE(SUBSTR(v_SQLString,0,255));
EXECUTE IMMEDIATE v_SQLString;
--循环递增
v_LoopVar:=v_LoopVar+1;
END LOOP;
END;-----------------------------------
错误提示:
The following error has occurred:ORA-06550: line 30, column 0:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ; <an identifier> <a double-quoted delimited-identifier>
The symbol ";" was substituted for "end-of-file" to continue.Details:
ORA-06550: line 30, column 0:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ; <an identifier> <a double-quoted delimited-identifier>
The symbol ";" was substituted for "end-of-file" to continue.
v_SQLStringInit Varchar2(8000);--动态SQL语句原型
v_SQLString varchar2(8000);--动态SQL语句
v_TempTableName Varchar2(50);--动态表名称
v_LoopVar NUMBER;--循环递增变量
BEGIN
--动态SQL语句原型初始化
v_SQLStringInit := '
create table {TableName} (
USERID NUMBER(10) not null,
USERNAME VARCHAR2(20) not null,
PASSWORD VARCHAR2(20) not null,
constraint PK_{TableName} primary key (USERID)
);
';
--循环变量初始化
v_LoopVar := 1;
WHILE v_LoopVar<=2 LOOP
--生成动态表名称
v_TempTableName := concat('UserInfo_',lpad(to_char(v_LoopVar),3,'0'));
--生成动态SQL实例
v_SQLString := REPLACE(v_SQLStringInit,'{TableName}',v_TempTableName);
--DBMS_OUTPUT.PUT_LINE(SUBSTR(v_SQLString,0,255));
EXECUTE IMMEDIATE v_SQLString;
--循环递增
v_LoopVar:=v_LoopVar+1;
END LOOP;
END;-----------------------------------
错误提示:
The following error has occurred:ORA-06550: line 30, column 0:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ; <an identifier> <a double-quoted delimited-identifier>
The symbol ";" was substituted for "end-of-file" to continue.Details:
ORA-06550: line 30, column 0:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ; <an identifier> <a double-quoted delimited-identifier>
The symbol ";" was substituted for "end-of-file" to continue.
--EXECUTE IMMEDIATE v_SQLString;
--显示动态执行的sql语句,看看是否有错误
DBMS_OUTPUT.PUT_LINE(SUBSTR(v_SQLString,0,255));
--EXECUTE IMMEDIATE v_SQLString;
是可以运行的。
v_SQLStringInit Varchar2(8000);--动态SQL语句原型
v_SQLString varchar2(8000);--动态SQL语句
v_TempTableName Varchar2(50);--动态表名称
v_LoopVar NUMBER;--循环递增变量
BEGIN
--动态SQL语句原型初始化
v_SQLStringInit := '
create table {TableName} (
USERID NUMBER(10) not null,
USERNAME VARCHAR2(20) not null,
PASSWORD VARCHAR2(20) not null,
constraint PK_{TableName} primary key (USERID)
); --;去掉
';
--循环变量初始化
v_LoopVar := 1;
WHILE v_LoopVar<=2 LOOP
--生成动态表名称
v_TempTableName := concat('UserInfo_',lpad(to_char(v_LoopVar),3,'0'));
--生成动态SQL实例
v_SQLString := REPLACE(v_SQLStringInit,'{TableName}',v_TempTableName);--DBMS_OUTPUT.PUT_LINE(SUBSTR(v_SQLString,0,255));
EXECUTE IMMEDIATE v_SQLString;
--循环递增
v_LoopVar:=v_LoopVar+1;
END LOOP;
END;
v_SQLStringInit Varchar2(8000);--动态SQL语句原型
v_SQLString varchar2(8000);--动态SQL语句
v_TempTableName Varchar2(50);--动态表名称
v_LoopVar NUMBER;--循环递增变量
BEGIN
--动态SQL语句原型初始化
v_SQLStringInit := '
create table {TableName} (
USERID NUMBER(10) not null,
USERNAME VARCHAR2(20) not null,
PASSWORD VARCHAR2(20) not null,
constraint PK_{TableName} primary key (USERID)
)
';
--循环变量初始化
v_LoopVar := 1;
WHILE v_LoopVar<=2 LOOP
--生成动态表名称
v_TempTableName := concat('UserInfo_',lpad(to_char(v_LoopVar),3,'0'));
--生成动态SQL实例
v_SQLString := REPLACE(v_SQLStringInit,'{TableName}',v_TempTableName);DBMS_OUTPUT.PUT_LINE(SUBSTR(v_SQLString,0,255));
EXECUTE IMMEDIATE v_SQLString;
--循环递增
v_LoopVar:=v_LoopVar+1;
END LOOP;
END;
USERID NUMBER(10) not null,
USERNAME VARCHAR2(20) not null,
PASSWORD VARCHAR2(20) not null,
constraint PK_{TableName} primary key (USERID)
);
';--测试通过
--注意
)后面的;去掉