我想完成以下题目:
(1)使用PL/SQL 建立一个帐户表(帐号,帐户名,余额)
并插入数据:(1,‘李四’,13000)
(2,‘王五’,100)
我写的PL/SQL代码如下:
DECLARE
BEGIN
EXECUTE IMMEDIATE 'create table account_user(
userno char(10),
username char(12),
balance number
)'
insert into account_user values('1','存款',13000)
insert into account_user values('2','支票',100)
commit;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
-----------------
问题:
1 执行通不过。我改试了好多回都通不过。
2 请大家帮忙指出我的错误之处,并将你的代码粘贴出来。
3 你的代码请务必是 执行通过 可以实现以上功能的代码。谢谢!
4 请使用PL/SQL写,不要把简单的SQL语句粘贴出来,因为通过简单的SQL语句其实很容易实现。
谢谢!
------解决方案--------------------
- SQL code
DECLARE BEGIN create table account_user( userno char(10), username char(12), balance number ); insert into account_user values('1','存款',13000) insert into account_user values('2','支票',100) commit; EXCEPTION WHEN OTHERS THEN ROLLBACK; END;
------解决方案--------------------
--试试
- SQL code
DECLARE BEGIN EXECUTE IMMEDIATE 'create table account_user( userno char(10), username char(12), balance number )' ; EXECUTE IMMEDIATE 'insert into account_user values(''1'',''存款'',13000)'; EXECUTE IMMEDIATE 'insert into account_user values(''2'',''支票'',100)'; commit; EXCEPTION WHEN OTHERS THEN ROLLBACK; END;
------解决方案--------------------
it seems that the create table statement and insert statement cannot co-exist.
because
DECLARE
BEGIN
EXECUTE IMMEDIATE 'create table account_user(
userno char(10),
username char(12),
balance number)';
commit;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
can return successful,
and
DECLARE
BEGIN
insert into account_user values('1','deposit',13000) ;
insert into account_user values('2','cheque',100) ;
commit;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
can return succefully too.
but
DECLARE
BEGIN
EXECUTE IMMEDIATE 'create table account_user(
userno char(10),
username char(12),
balance number)';
insert into account_user values('1','deposit',13000) ;
insert into account_user values('2','cheque',100) ;
commit;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
cannot return successfully.
------解决方案--------------------
DECLARE
BEGIN
EXECUTE IMMEDIATE 'create table account_user(
userno char(10),
username char(12),
balance number
)';
EXECUTE IMMEDIATE 'insert into account_user values(''1'',''存款'',13000)';
EXECUTE IMMEDIATE 'insert into account_user values(''2'',''支票'',100)';
commit;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
这个可以编译成功的
------解决方案--------------------
原因在于 insert into account_user values 这条语句。
sql在执行时要先分析sql语句,
当分析到这条时,因为table account_user还没有创建,
因此DB无法解析这一条语句,因而报错。
可以将这两条insert语句用execute immediate 这种方式执行,
可以绕过db对语句的检查。