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.
解决方案 »
- 请教下有关重建datafile的问题
- 求知识,如何实现以下的效果(oracle)?
- 请问我的系统坏了,但我的oracle数据库没有倒出过,请问我从装系统和数据库后还能恢复我以前的数据库吗?
- sql plus中scott.emp如何实现对表操作时用户的参数化,即如何变成类似 name.emp的语句
- 超级难题,是高手的请进来看一下能否帮忙解决一下.
- 怎么样判断某个表中是否存在记录?
- 如何更新数据库字段
- Oracle 中建立自增序列的常用方法。
- 关于用户的操作系统验证口令与不完全恢复?
- 什么语句能把一个表中的所有字段名列出来?
- proc*中传入的字符串数组如何转化为sql查询条件?请大家帮忙,谢谢
- 一个简单的PL/SQL语句怎么就错了呢?请赐教
--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)
);
';--测试通过
--注意
)后面的;去掉