SQL基础语法主体知识2

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.索引