关于PLSQL中的一些问题总结：在PLSQL中书写DDL等
关于问题前导，使用的数据表中涉及到的字段和类型：
在PLSQL中create、drop、truncate等DDL是没有办法直接执行的。
必须要使用：
Execute immediate ‘DDL语句’
但是我发现这样并不能执行！后面查阅发现，oracle中执行DDL语句需要使用变量的形式：
即 所谓的动态SQL语句
首先在declare中声明变量( 例如：sql varchar2(200):= ‘create table tmp(name varchar2(20), age number(3))’ )，再在begin和end之间 execute immediate才可以！
DML
关于DML insert、delete、update是可以直接执行的，而且通过SQL对象会返回受到影响的行数。
但是这里需要注意的是：如果你是在同一个PLSQL中创建了表，同时想在这个PLSQL中insert数据的话，就必须要小心了！！！
也要使用：executeimmediate的方式来执行：注意，最好也是通过变量的形式执行：
直接把语句通过 executeimmediate引在后面当然可以，但是这样的话，数据就必须得写死
而且有varchar和varchar2类型的时候，就很麻烦了：
引号就会冲突；
通过变量声明的方式
就更显得灵活了！
因为execute这种形式，是加载进来的时候，就会做判断的，最开始insert的话，如果检测到表不存在！就会报错！
所以为了保证和create统一加载，忽略oracle的报错（我的理解这个和java反射类似，弱引用一样），就要统一为execute immediate
DQL
但是关于select的话，不能单独执行，除非后面有into语句，才有执行意义，才不会报错！
最后需求的是查出这样的一个表结构：
以下代码纯属PLSQL、存储过程练手：
1.创建一个传入系名进行查询平均成绩的存储过程
create or replace procedure in_dname_out_avg_grade(
i_dname in dep.dname%type,
o_agrade out number
)
as
begin
select avg(grade) agrade into o_agrade
from
(
select course.cname, dep.dname, sc.grade
from course
inner join sc on course.cno = sc.cno
inner join student on sc.sno = student.sno
inner join dep on dep.dno = student.dno
where course.cname = '大学物理' and dep.dname = i_dname
);
end;2.创建一个传入系名查询不及格人数的存储过程
create or replace procedure in_dname_out_grade_low(
i_dname in dep.dname%type,
o_num out number
)
as
begin
select count(student.sno) into o_num
from course
inner join sc on course.cno = sc.cno
inner join student on sc.sno = student.sno
inner join dep on dep.dno = student.dno
where course.cname = '大学物理' and dep.dname = i_dname and sc.grade < 60;
end;
3.创建一个传入系名查询 60-85分人数的存储过程
create or replace procedure in_dname_out_grade_mid(
i_dname in dep.dname%type,
o_num out number
)
as
begin
select count(student.sno) into o_num
from course
inner join sc on course.cno = sc.cno
inner join student on sc.sno = student.sno
inner join dep on dep.dno = student.dno
where course.cname = '大学物理' and dep.dname = i_dname and sc.grade >=60 and sc.grade < 85;
end;
4.创建一个传入系名查询 85分以上人数的存储过程
create or replace procedure in_dname_out_grade_hig(
i_dname in dep.dname%type,
o_num out number
)
as
begin
select count(student.sno) into o_num
from course
inner join sc on course.cno = sc.cno
inner join student on sc.sno = student.sno
inner join dep on dep.dno = student.dno
where course.cname = '大学物理' and dep.dname = i_dname and sc.grade >=85;
end;补充注意：
存储过程不能有declare声明！！！
如果要声明变量的话直接，如下：
create or replace procedure createTable_procedure
as
-- declare 存储过程不能有声明！！
v_DDL varchar2(200) :=
'create table tmp(
t_cname varchar2(20),
t_dname varchar2(20),
grade_low number,
grade_mid number,
grade_hig number,
grade_avg number
)';
begin
execute immediate v_DDL;
end;
最后得出结果表的PLSQL，注意要用到上面的存储过程：
declare
cursor dname_cursor is
select distinct dep.dname
from student
inner join sc on student.sno = sc.sno
inner join dep on dep.dno = student.dno
where sc.cno = (
select course.cno
from course
where course.cname = '大学物理'
);
type dnameContain is table of dep.dname%type
index by binary_integer;
contain dnameContain;
m_cname course.cname%type := '大学物理';
m_dname dep.dname%type;
m_low number;
m_mid number;
m_hig number;
m_avg number(3);
i number := 0;
v_DDL varchar2(200) :=
'create table tmp(
t_cname varchar2(20),
t_dname varchar2(20),
grade_low number,
grade_mid number,
grade_hig number,
grade_avg number
)';
v_DML varchar2(100) :=
'insert into tmp values(:1, :2, :3, :4, :5, :6)';
begin
execute immediate ' drop table tmp';
exception when others then
null;
-- 为什么在一个PL/SQL中不能创建表后面就用
-- dbms_utility.exec_ddl_statement(v_DDL);
execute immediate v_DDL;
-- commit;
open dname_cursor;
loop
fetch dname_cursor into contain(i);
if i>0 then
-- dbms_output.put_line(contain(i-1));
m_dname := contain(i-1);
dbms_output.put_line(m_dname);
in_dname_out_grade_low(m_dname, m_low);
in_dname_out_grade_mid(m_dname, m_mid);
in_dname_out_grade_hig(m_dname, m_hig);
in_dname_out_avg_grade(m_dname, m_avg);
dbms_output.put_line(m_mid||', '||m_hig||', '||m_avg);
-- insert into tmp values(m_cname, m_dname, m_low, m_mid, m_hig, m_avg);
execute immediate v_DML using m_cname, m_dname, m_low, m_mid, m_hig, m_avg;
end if;
i := i+1;
exit when dname_cursor%notfound;
end loop;
close dname_cursor;
commit;
end;
注意：commit在PLSQL中可以直接写的！！！
