我自己写了个匿名块,想使用嵌套表。但是在sqlplus中报错,我不知道是不是在执行部分不能包含ddl语句,平时看到的都是dml语句。
请高人点拨一二。
代码如下:
declare
type emp_type is object(
name varchar2(10),salary number(6,2),
hiredate date);
type emp_array is table of emp_type;
begin
create table department(
deptno number(2),dname varchar2(10),
employee emp_array)
nested table employee store as employee;
end;
/
请高人点拨一二。
代码如下:
declare
type emp_type is object(
name varchar2(10),salary number(6,2),
hiredate date);
type emp_array is table of emp_type;
begin
create table department(
deptno number(2),dname varchar2(10),
employee emp_array)
nested table employee store as employee;
end;
/
type emp_type is object(
name varchar2(10),salary number(6,2),
hiredate date);
type emp_array is table of emp_type;
begin
execute immediate 'create table department(
deptno number(2),dname varchar2(10),
employee emp_array)
nested table employee store as employee';
end;
/
SQL>
SQL> declare
2
3 begin
4 execute immediate 'create table department(deptno number(2),dname varchar2(10),name varchar2(10),salary number(6,2),hiredate date) ';
5 end;
6 /PL/SQL procedure successfully completedSQL>
--上面是可以的,但嵌套表还没有试出来。
--LZ:必须create后才能引用:SQL>
SQL> declare
2
3 begin
4
5 execute immediate 'create type emp_type2 as object(name varchar2(10),salary number(6,2),hiredate date)';
6 execute immediate 'create type emp_array2 as table of emp_type2';
7 execute immediate 'create table department2(deptno number(2),dname varchar2(10),employee emp_array2) nested table employee store as employee2';
8 end;
9 /PL/SQL procedure successfully completedSQL> select * from department2;DEPTNO DNAME EMPLOYEE
------ ---------- --------SQL>
报错:ora-06550
--可以的:declarebeginexecute immediate 'create type emp_type as object(name varchar2(10),salary number(6,2),hiredate date)';
execute immediate 'create type emp_array as table of emp_type';
execute immediate 'create table department(deptno number(2),dname varchar2(10),employee emp_array) nested table employee store as employee';
end;PL/SQL procedure successfully completedSQL> select * from department;DEPTNO DNAME NAME SALARY HIREDATE
------ ---------- ---------- -------- -----------SQL>
顺便问一下为什么非要create创建一下,我在声明中声明之后不能直接使用吗?
比如:
declare
type rec is record(name varchar2(10),salary number(6,2),
hiredate date);
type emp_array is table of rec index by binary_integer;
emp emp_array;
begin
select ename into emp(1).name from emp
where empno=7788;
dbms_output.put_line(emp(1).name);
end;
/
create type emp_type as object(name varchar2(10),salary number(6,2),hiredate date);
/
create type array_emp is table of emp_type;
/
create table department(
deptno number(2),
dname varchar2(10),
employee array_emp)
nested table employee store as employee;
create type emp_type as object(name varchar2(10),salary number(6,2),hiredate date);
/
create type array_emp is table of emp_type;
/
create table department(
deptno number(2),
dname varchar2(10),
employee array_emp)
nested table employee store as employee;
你的这段是可以执行,但是ddl不能在pl/sql中直接执行的,一定得用execute immediate之类的动态执行。动态执行ddl要考虑事务问题。 ddl语句在执行时,会把当前的事务先提交了再执行的