目录
动态SQL
plsql里面只能执行 select insert update delete ，想执行create怎么办？其他复杂功能？
使用动态sql，不能加分号，单引号实现转义功能''''
在Oracle数据库开发PL/SQL块中我们使用的SQL分为：静态SQL语句和动态SQL语句。所谓静态SQL指在PL/SQL块中使用的SQL语句在编译时是明确的，执行的是确定对象。而动态SQL是指在PL/SQL块编译时SQL语句是不确定的，如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进行处理，只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。Oracle中动态SQL可以通过本地动态SQL来执行，也可以通过DBMS_SQL包来执行。本地动态SQL是使用EXECUTE IMMEDIATE语句来实现的。
execute immediate语句
execute immediate sql语句(字符串，也可以是字符串变量) [into(只有在执行查询时使用)变量,...] [using 参数,...];
--不能执行语句，原有能执行的语句也可以放在里面
declare
v_sql varchar2(1000);
begin
v_sql :=
'create table stest(sno varchar(20),sdate date)';
dbms_output.put_line(v_sql);
execute immediate v_sql;
end;
--增删改查也可以使用动态sql，查询需要使用into
declare
v_sql varchar2(200);
v emp%rowtype;
begin
v_sql := 'select * from emp where empno=7369';
execute immediate v_sql into v;
dbms_output.put_line(v.empno||v.ename);
end;
--带输入且判断的复杂动态sql
declare
v_sql varchar2(200);
v emp%rowtype;
eno varchar(4):='&请输入编号';
begin
v_sql := 'select * from emp ';
if eno is not null then
v_sql:=v_sql||' where empno='||eno;
else
v_sql:=v_sql||' where rownum=1';
end if;
execute immediate v_sql into v;
dbms_output.put_line(v.empno||v.ename);
end;
--输入课程编号,如果不输入则显示所有数据第一条，如果输入显示当前课程的第一条
select * from sc where cno='c002' and rownum=1
select * from sc where rownum=1
declare
dsql varchar2(1000);
sc1 sc%rowtype;
cnoin varchar2(10):='&请输入课程编号';
begin
dsql:='select * from sc where ';
if cnoin is null then
dsql:=dsql||'rownum=1';
else
dsql:=dsql||'cno='''||cnoin||''' and rownum=1';
end if;
dbms_output.put_line(dsql);
execute immediate dsql into sc1;
dbms_output.put_line(sc1.sno||' '||sc1.cno||' '||sc1.score);
end;
带参数使用
declare
-- 声名一个变量保存sql语句
v_sql varchar2(255);
-- 声名一个rowtype类型变量保存一条员工信息
v emp%rowtype;
-- 声名一个变量接收从键盘输入的员工编号
v_empno emp.empno%type:='&empno';
begin
v_sql:='select * from emp where empno=:1';
dbms_output.put_line(v_sql);
execute immediate v_sql into v using v_empno;
-- execute immediate v_sql into v using v_empno;
-- 打印变量v的值
dbms_output.put_line(v.empno||v.ename);
execute immediate v_sql into v using 7839;
-- execute immediate v_sql into v using v_empno;
-- 打印变量v的值
dbms_output.put_line(v.empno||v.ename);
end;
作业
/*1、使用动态sql实现输入员工编号查询员工信息，如果不输入则显示所有员工的第一
条，显示员工姓名和工资*/
declare
dsql varchar2(1000):='select ename,sal from emp where empno=';
dsqldefault varchar2(1000):='select ename,sal from emp where rownum=1';
v_empno varchar2(10):='&输入员工编号';
type names is record(
ename emp.ename%type,
sal emp.sal%type
);
v names;
begin
if v_empno is not null then
execute immediate concat(dsql,v_empno) into v;
else
execute immediate dsqldefault into v;
end if;
dbms_output.put_line(v.ename||' '||v.sal);
end;
/*2、搜索一个员工信息，输入工资和工作岗位，如果输入为空则显示不带条件的第一条；
如果不为空则显示带条件的第一条，显示员工姓名和工资
提示：
1000为测试的输入工资，SALESMAN为测试的输入工作岗位
select * from emp where sal>1000 and job = 'SALESMAN' and rownum=1
select * from emp where sal>1000 and rownum=1
select * from emp where job = 'SALESMAN' and rownum=1
select * from emp where rownum=1*/
declare
dsql varchar2(2000);
type names is record(
ename emp.ename%type,
sal emp.sal%type
);
v names;
v_sal varchar2(10):='&输入工资';
v_job varchar2(20):='&输入工作岗位';
begin
if v_sal is not null and v_job is not null then
dsql:='select ename,sal from emp where sal=''||v_sal||''and job='||v_job;
elsif v_sal is not null and v_job is null then
dsql:='select ename,sal from emp where sal='||v_sal;
elsif v_sal is null and v_job is not null then
dsql:='select ename,sal from emp where job='||v_job;
else dsql:='select ename,sal from emp where rownum=1';
end if;
execute immediate dsql into v;
dbms_output.put_line(v.ename||' '||v.sal);
end;
动态游标
在程序运行过程中，把一个游标变量在不同时刻关联不同的查询语句，也就是使用一个游标变量可以获取多个不同的结果集。通过把动态游标作为参数传递给另一个过程或函数使用，通过这种方式达到共享结果集的目的，把一个游标的内容（结果集）赋值给另外一个游标变量。
动态游标声明步骤：
●声明动态游标类型
●声明动态游标名称
动态游标类型声明
弱类型动态游标系统自带了一个直接使用即可: sys_refcursor
强类型动态游标只能配合静态sql语句使用，无法和动态sql配合使用。
弱类型动态游标能够执行动态sql 以及配合绑定变量。
示例：
refcur_emp sys_refcursor;
动态游标使用
使用 open 打开动态游标并给其绑定结果集
open 动态游标名 for sql语句;
动态游标无法使用for循环打开。
--没有返回类型的弱类型，一般用sys_refcursor,可以省略步骤，为了返回类型铺垫
declare
cursor cur1 is select * from emp where empno=7499;
emp1 emp%rowtype;
emp2 emp%rowtype;
n1 number(4):=5;
type newcur is ref cursor;--弱类型游标类型
cur2 newcur;--游标变量
begin
open cur2 for select * from emp where empno=7499;
fetch cur2 into emp2;
dbms_output.put_line(emp2.ename);
close cur2;
dbms_output.put_line(n1);
n1:=6;
dbms_output.put_line(n1);
open cur1;
fetch cur1 into emp1;
dbms_output.put_line(emp1.ename);
close cur1;
end;
--弱类型动态游标
declare
emp2 emp%rowtype;
cur2 sys_refcursor;--游标变量
begin
open cur2 for select * from emp where empno=7499;
fetch cur2 into emp2;
dbms_output.put_line(emp2.ename);
close cur2;
end;
--动态游标可以随时换查询结果集
declare
emp2 emp%rowtype;
cur2 sys_refcursor;--游标变量
dept2 dept%rowtype;
begin
open cur2 for select * from emp where empno=7499;
fetch cur2 into emp2;
dbms_output.put_line(emp2.ename);
close cur2;
open cur2 for select * from dept where deptno=20;
fetch cur2 into dept2;
dbms_output.put_line(dept2.dname);
close cur2;
end;
--while循环
declare
emp2 emp%rowtype;
cur2 sys_refcursor;--游标变量
begin
open cur2 for select * from emp;
fetch cur2 into emp2;
while cur2%found
loop
dbms_output.put_line(emp2.ename);
fetch cur2 into emp2;
end loop;
close cur2;
end;
--动态sql弱类型动态游标,输入部门编号，不输入代表所有
declare
dsql varchar2(1000);
dno varchar2(10):='&请输入部门编号';
ename1 varchar2(10);
job1 varchar2(10);
cur1 sys_refcursor;
begin
dsql:='select ename,job from emp ';
if dno is not null then
dsql:=dsql||'where deptno='||dno;
end if;
dbms_output.put_line(dsql);
open cur1 for dsql;
fetch cur1 into ename1,job1;
while cur1%found
loop
dbms_output.put_line(ename1||job1);
fetch cur1 into ename1,job1;
end loop;
close cur1;
end;
--二重循环动态游标显示
declare
cur1 sys_refcursor;
cur2 sys_refcursor;
dept1 dept%rowtype;
emp1 emp%rowtype;
begin
open cur1 for select * from dept;
fetch cur1 into dept1;
while cur1%found
loop
dbms_output.put_line(dept1.deptno||dept1.dname);
open cur2 for select * from emp where deptno=dept1.deptno;
fetch cur2 into emp1;
while cur2%found
loop
dbms_output.put_line(' '||emp1.deptno||emp1.ename);
fetch cur2 into emp1;
end loop;
close cur2;
fetch cur1 into dept1;
end loop;
close cur1;
end;
强类型游标
declare
type names is record(
ename varchar2(10),
dname varchar2(10)
);
zs names;
type curtype is ref cursor return names;
cur1 curtype;
begin
open cur1 for select dname,ename from emp inner join dept on emp.deptno=dept.deptno;
fetch cur1 into zs;
while cur1%found
loop
dbms_output.put_line(zs.name1||zs.name2);
fetch cur1 into zs;
end loop;
close cur1;
end;
作业2
--1、弱类型动态游标，显示所有员工名称，工作
declare
rlx sys_refcursor;
type names is record(
ename emp.ename%type,
job emp.job%type
);
ej names;
begin
open rlx for select ename,job from emp;
fetch rlx into ej;
while rlx%found
loop
dbms_output.put_line(ej.ename||' '||ej.job);
fetch rlx into ej;
end loop;
close rlx;
end;
/*2、根据输入的部门编号显示员工编号，工作，若没有输入部门编号则显示所有，使用动
态sql+弱类型动态游标实现*/
declare
dsql varchar2(2000);
rlx sys_refcursor;
v_deptno varchar2(3):='&输入部门编号显示员工编号和工作';
type names is record(
empno emp.empno%type,
job emp.job%type
);
v_ej names;
begin
if v_deptno is null then
dsql:='select empno,job from emp';
open rlx for dsql;
fetch rlx into v_ej;
while rlx%found
loop
dbms_output.put_line(v_ej.empno||' '||v_ej.job);
fetch rlx into v_ej;
end loop;
close rlx;
else
dsql:='select empno,job from emp where deptno='||v_deptno;
open rlx for dsql;
fetch rlx into v_ej;
while rlx%found
loop
dbms_output.put_line(v_ej.empno||' '||v_ej.job);
fetch rlx into v_ej;
end loop;
close rlx;
end if;
end;
/*3、根据输入显示学员姓名、课程编号及课程成绩，输入学号不输入课程号直接显示学员
姓名和所有课程编号和成绩，输入学号和课程号显示对应课程成绩，使用动态sql+弱类
型动态游标实现*/
declare
type names is record(
cno sc.cno%type,
score sc.score%type
);
sc2 names;
v_name student.sname%type;
v_score sc.score%type;
dsql varchar2(2000);
rlx sys_refcursor;
v_sno sc.sno%type:='&输入学号';
v_cno sc.cno%type:='&输入课程号';
begin
if v_sno is not null and v_cno is null then
select sname into v_name from student where sno=v_sno;
dsql:='select cno,score from sc where sno='''||v_sno||'''';
dbms_output.put_line(v_name);
open rlx for dsql;
fetch rlx into sc2;
while rlx%found
loop
dbms_output.put_line(sc2.cno||' '||sc2.score);
fetch rlx into sc2;
end loop;
close rlx;
elsif v_sno is not null and v_cno is not null then
select score into v_score from sc where sno=v_sno and cno=v_cno;
dbms_output.put_line(v_score);
end if;
end;
--4、通过teacher表显示各个课程信息，二重循环弱类型游标,同上机练习7.2的效果图
declare
rlx sys_refcursor;
rlx2 sys_refcursor;
v_teacher teacher%rowtype;
v_course course%rowtype;
begin
open rlx for select * from teacher;
fetch rlx into v_teacher;
while rlx%found
loop
dbms_output.put_line(v_teacher.tno||'---'||v_teacher.tname);
fetch rlx into v_teacher;
open rlx2 for select * from course where tno=v_teacher.tno;
fetch rlx2 into v_course;
while rlx2%found
loop
dbms_output.put_line(' '||v_course.cno||'-----'||v_course.cname||'-----'||v_course.tno);
fetch rlx2 into v_course;
end loop;
close rlx2;
end loop;
close rlx;
end;
--5、使用强类型游标显示教师姓名及所教课程名称
declare
type names is record(
tname teacher.tname%type,
cname course.cname%type
);
t names;
type cur is ref cursor return names;
A cur;
begin
open A for select tname,cname
from teacher left join course on teacher.tno=course.tno;
fetch A into t;
while A%found
loop
dbms_output.put_line(t.tname||'-----'||t.cname);
fetch A into t;
end loop;
close A;
end;遗忘点复习
number类型不能为空。