19.更新数据的方法
数据更新的方法
20. 动态SQL
在PL/SQL程序开发中,可以使用DML语句和事务控制语句,但是还有很多语句(比如DDL语句如 create table , truncate insert into 这种)不能直接在PL/SQL中执行。这些语句可以使用动态SQL来实现。
PL/SQL块先编译然后再执行,动态SQL语句在编译时不能确定,只有在程序执行时把SQL语句作为字符串的形式由动态SQL命令来执行。在编译阶段SQL语句作为字符串存在,程序不会对字符串中的内容进行编译,在运行阶段再对字符串中的SQL语句进行编译和执行,动态SQL的语法是:
语法格式:动态SQL
EXECUTE IMMEDIATE 动态语句字符串
[INTO 变量列表]
[USING 参数列表]
语法解析:
如果动态语句是SELECT语句,可以把查询的结果保存到INTO后面的变量中。如果动态语句中存在参数,USING为语句中的参数传值。
动态SQL中的参数格式是:[:参数名],参数在运行时需要使用USING传值。
【例】在过程中复制EMP表
BEGIN
EXECUTE IMMEDIATE ‘CREATE TABLE YYY AS SELECT * FROM EMP’;
END;
如果拼接太长,可以用这种方式来看到到底拼接的是什么。动态传参
-------------------动态sql完整语法
EXECUTE IMMEDIATE 动态语句字符串
[INTO 变量列表]
[USING 参数列表]
-----用动态sql实现
-----用动态sql实现
DECLARE
v_ename VARCHAR2(20);
v_sql VARCHAR2(100);
v_empno number;
BEGIN
v_empno:=7566;
v_sql:='SELECT ename FROM emp WHERE empno=:id';--动态sql中的参数,格式 :参数名
EXECUTE IMMEDIATE v_sql INTO v_ename USING v_empno; --向动态sql中传参
dbms_output.put_line(v_ename);
END;
------每次检测的语句都是:
SELECT ename FROM emp WHERE empno=:id
-----这种方法的好处
/*
查询的执行原理:
1.提交查询语句
2.检索缓存区,缓存区会把之前的执行计划暂时保留下来。
若语句和缓存区中存在的语句一模一样(多一个空格都不行),
则会减少很多解析时间,若缓存区没有,那么执行3
3.检查语法
4.检查表、字段是否存在,检查查询权限
5.设计执行计划
6.执行计划
*/
—目的是让数据库觉得每次执行的都是同一个语句,无论参数如何变化,
—实现减少解析时间的目的,叫做软解析
—如果是一个全新的语句,在解析的时候叫做硬解析
21.把存储过程函数等放进包里–打包
-----把存储过程函数等放进包里–打包
----1、先创建一个包,也叫作包定义,或者叫包头
CREATE OR REPLACE PACKAGE pkg_test ---包的名字
AS
----没有begin
---放入存储过程或者函数的定义
END;
—2、创建一个包体,放入存储过程的实现过程
CREATE OR REPLACE PACKAGE BODY pkg_test---对应包头的名称
AS
---没有begin
---放入存储过程或者函数的内容
END;
----3、向包中加入存储过程或者函数
—把之前创建好的存储过程或者函数,加入到包中
—首先打开包头,把要放入包中的存储过程或者函数的定义部分填在包头里。
CREATE OR REPLACE PACKAGE pkg_test ---包的名字
AS
----没有begin
---放入存储过程或者函数的定义
v_grade varchar2(20); ---全局变量
procedure sp_test004(p_deptno number);
procedure s_emp5 ;
END;
—然后打开包体,把实现部分填在包体里!!!
是从procedure,function 开始到end;整段都叫做包体
create or replace package body pkg_test004 ---包体部分
as
-----------------------
procedure sp_test004(p_deptno number) is
v_cnt number;
cursor c is
select e.*, sysdate etl_date
from emp e
where deptno = p_deptno;
begin
select count(*)
into v_cnt
from emp e where e.deptno = p_deptno;
if v_cnt >0 then
delete from emp_t where deptno = p_deptno;
for i in c loop
insert into emp_t
(empno, ename, job, mgr, hiredate, sal, comm, deptno, etl_date)
values
(i.empno,
i.ename,
i.job,
i.mgr,
i.hiredate,
i.sal,
i.comm,
i.deptno,
i.etl_date);
end loop;
dbms_output.put_line('部门编号'||p_deptno||'数据插入完成');
else
dbms_output.put_line('部门编号'||p_deptno||'无数据');
end if ;
end;
-----------------------------
procedure s_emp5 is
cursor c_emp is
select deptno,ename,sal,
case when deptno =10 then sal*1.3
when deptno =20 then sal*1.4
when deptno =30 then sal*1.5
else sal*1.2 end as sal_plus
from emp e
group by deptno ,ename,sal;
begin
for i in c_emp loop
dbms_output.put_line( i.deptno||','||i.ename||','||i.sal||','||i.sal_plus);
end loop;
end;
-------------------------------------------
end;
-----4、执行包里的存储过程或者函数,要指定好包名,也就是使用的是哪个包里的存储过程或者函数
BEGIN
pkg_test.sp_test0016(7566);
END;
执行方法
select ename,pkg_test.f_test004(7788) from emp
21.索引