PL/SQL 开发基础

隆多9
679 阅读6分钟

5. 管理数据表

5.1 创建表

数据定义语言（DDL）：主要用来操纵数据库的结构,如定义、修改和删除在oracle中存在的每种类型的对象

PL/SQL 不能直接使用DDL语句，可以通过动态SQL语句的形式来执行SQL命令

  • DDL语言： 包含一个隐式提交的事务语句，即执行COMMIT提交

    1. CREATE：创建数据库对象

      • 创建数据库、数据表、索引或子程序、视图、触发器等

      • 创建对象类型不同，参数也会不同

        -- 创建数据库对象
CREATE TABLE;     -- 数据表
CREATE INDEX;     -- 索引
CREATE PROCEDURE; -- 过程
CREATE FUNCTION;  -- 函数
CREATE VIEW;      -- 视图
CREATE TRIGGER;   -- 触发器

    2. ALTER：修改数据库对象

      -- 添加表列
ALTER TABLE COMPANY_EMP ADD DESCRIPTION VARCHAR2(200) NULL;
-- 删除表列
ALTER TABLE COMPANY_EMP DROP COLUMN DESCRIPTION;

    3. DROP：移除数据库对象

      DROP TABLE COMPANY_EMP;

  • 创建表副本

    -- 创建表副本语句
CREATE TABLE <NEWTABLE> AS SELECT {*|COLUMN(S)} FROM <OLDTABLE> [WHERE <CONDITION>]
-- 创建emp_back 与emp 相同结构及数据
create table emp_back as SELECT * FROM emp
-- 创建emp_back 与emp 相同结构 但不包含数据
create table emp_back as SELECT * FROM emp where 1=2

5.2 创建约束

  1. 为什么要创建约束？

    • 为了保证数据的完整性而实现的一套机制，它是数据库服务器强制用户必须遵从的业务逻辑，限制用户可能输入指定范围外的值；

  2. 有哪些约束？

    1. 非空约束：NOT NULL(该值不能为空)

    2. 唯一约束：UNIQUE(整个表相同列中是唯一的)

    3. 检查约束：CHECK (对字段执行检查条件)

    4. 主键约束：PRIMARY KEY（唯一约束+非空约束）

    5. 外键约束：REFERENCES(表间关联的约束，实现数据完整性)

      CREATE TABLE TABLE_A
(
VENDOR_ID NUMBER,
INVOICE_TOTAL NUMBER(9,2) CHECK (INVOICE_TOTAL>0 AND 
INVOICE_TOTAL<=5000),                           -- 检查约束
VENDOR_NAME VARCHAR2(50) NOT NULL,              -- 非空约束
CONSTRAINT VENDORS_PK PRIMARY KEY (VENDOR_ID)   -- 主键
CONSTRAINT VENDOR_NAME_UQ UNIQUE (VENDOR_NAME)  -- 唯一约束
)

  3. 如何查看约束？

    -- constraints 
SELECT * FROM user_constraints t where t.constraint_name = 'PRIMART_ENPNO';

5.3 修改表

  1. ALTER TABLE 修改表列的4件事

    1. 向表中添加的列
    2. 修改已经存在的列的类型或数据范围
    3. 删除已经存在的列
    4. 重命名表列
    SELECT * FROM emp; 
-- 增加列
alter table emp add yang varchar2(20);
-- 删除列
alter table emp drop column yang;
-- 重命名列名
alter table emp rename column yang to yang_new;
-- 修改列类型和检查约束（慎用）
alter table emp modify yang_new varchar(40) check(length(yang_new)<30);

  2. ALTER TABLE 修改约束的3件事

    1. 向表中添加一个新的约束
    2. 移除表中现有的约束
    3. 启用或禁用约束
    -- 删除约束 DROP
ALTER TABLE EMP DROP CONSTRAINT PRIMART_ENPNO;
-- 添加约束
ALTER TABLE EMP ADD CONSTRAINT UQ_CONSTR UNIQUE (ENAME);
ALTER TABLE EMP ADD CONSTRAINT PRIMART_ENPNO PRIMARY KEY(EMPNO);

  3. 移除数据表

    -- 删除表，同时删除数据、表结构及约束
DROP TABLE EMP;
-- 当表有外键约束时，应先移除外键表，在移除该表

5.4 索引

索引是建立在数据库中的一列或多列用来加速访问表中数据的辅助对象；

  • 索引优点

    1. 加快检索数据的速度
    2. 保证数据唯一性
    3. 加快表与表之间的连接
    4. 减少查询分组和排序时间

5.5 视图

6. 查询数据表

  • 查询相关关键字：

    1. DISTINCT ：该列去重

    2. AS ： 改列别名 （当使用字符串为别名时，必须使用双引号标注字符串）

    3. NULL : (IS NULL 或 IS NOT NULL)

    4. || : 字符串连接符

    5. 范围操作符

      1. BETWEEN-AND : 比较的值是否在两个值之间

      2. IN ：比较的值是否在任意的值列表中间

      3. LIKE：通配符类匹配一个字符模板

        • %：表示0个或多个字符
        • _：表示一个字符

    6. 逻辑组合

      1. AND：都为真返回True，否则返回False
      2. OR：一个为真返回True，否则返回False
      3. NOT：为假返回True，否则返回False

    7. 排序

      1. ORDER BY ：必须为最后一个字句；默认为ASC升序，倒序为DESC

    8. 统计函数

      1. SUM函数：总和
      2. AVG函数：平均值
      3. MIN函数：最小值
      4. MAX函数：最大值
      5. COUNT函数：数目

    9. GROUP BY ：分组查询

    10. HAVING 字句：只再GROUP BY 字句的后面，是对分组进一步的过滤

    11. DUAL：是一个真实的表，用来计算并返回结果

    12. ROWNUM伪列：动态的，在结果集后加上去的一个列，可用于分页

      -- 可用于类似的分页
SELECT * FROM (
SELECT t.*,rownum rn FROM emp t) a where a.rn>1 and a.rn<4

    13. ROWID伪列：记录物理位置的一个ID，常用于 更新数据、删除完全重复的两条记录

      -- 删除重复行
DELETE FROM EMP_BACK WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM EMP_BACK GROUP BY EMPNO)
-- 更新
SELECT T.*,ROWID FROM EMP;
​

    14. 多表连接查询

      -- 内连接
SELECT * FROM test6 t1,test_61 t2 where t1.id = t2.id;
-- 左连接
SELECT * FROM test6 t1,test_61 t2 where t1.id = t2.id(+);
SELECT * FROM TEST6 T1 LEFT JOIN TEST_61 T2 ON T1.ID = T2.ID; 
-- 右连接
SELECT * FROM test6 t1,test_61 t2 where t1.id(+) = t2.id;
SELECT * FROM TEST6 T1 RIGHT JOIN TEST_61 T2 ON T1.ID = T2.ID;
-- 全连接
SELECT * FROM TEST6 T1 FULL JOIN TEST_61 T2 ON T1.ID = T2.ID;

    15. 子查询

      1. 相关子查询

        • 内查询依赖于外部查询的数据，外查询执行一次，内查询就执行一次

      2. 非相关子查询

        • 内查询只执行一次，并将执行结果返回给外查询

        • 单行单列：

          • 比较运算符：IN、>、<、=、!=、>=、<=
          SELECT *
  FROM test6
 where id = (SELECT t.id FROM test_61 t where t.e_name = 'yang');

        • 多行单列

          • ANY、ALL
           -- <ANY  小于最大值
 -- >ANY  大于最小值
 -- =ANY  等同于 IN
 -- <ALL  小于最小值
 -- >ALL  大于最大值
 SELECT *
   FROM test6
  where id  >all
  (SELECT t.id FROM test_61 t where t.e_name in ('de','yang'));

    16. 表集合操作

      1. 联合运算

        • 从两个查询的结果去掉重复值后合并后的结果（UNION）

          -- UNION 过滤重复列
SELECT *
  FROM TEST6 T1
 WHERE T1.ID = 1
UNION 
SELECT *
  FROM TEST6_BACK T2
 WHERE T2.ID IN(5,2,1);

      2. 全联合运算

        • 返回两个查询结果的并集，但是包括所有重复值（UNION ALL）

          -- UNION ALL  保留重复列
SELECT *
  FROM TEST6 T1
 WHERE T1.ID = 1
UNION ALL
SELECT *
  FROM TEST6_BACK T2
 WHERE T2.ID IN(5,2,1);

      3. 相交运算

        • 返回多个查询中的相同的行（INTERSECT）

          
SELECT *
  FROM TEST6 T1
 WHERE T1.ID = 1
INTERSECT
SELECT *
  FROM TEST6_BACK T2
 WHERE T2.ID IN(1,2,5);

      4. 相减运算

        • 返回查询在第1个表中但不在第2个表中的行

          
SELECT *
  FROM TEST6 T1
 WHERE T1.ID IN(1,2,5)
MINUS
SELECT *
  FROM TEST6_BACK T2
 WHERE T2.ID =1;

7. 操作数据表

> \

DML数据操纵语言可以对数据表进行操作，且必须手动提交与回滚操作

  • DML数据操纵语言

    1. INSERT：向表添加行
    2. UPDATE：更新表中数据
    3. DELETE：删除现有的行
    4. MERGE：插入所有的行到另一个具有现存记录的表，如果要插入的行的键匹配已存在行，则更新已经存在的行而不是插入一个新行

  1. 插入记录

    1. 插入单行

      -- 语句中的列顺序可以不按表列顺序，单VALUES 里面的顺序必须跟语句中的列顺序一致
INSERT INTO TEST6(ENAME,ID,NAME,AGE)VALUES('laowang',6,'老王',78);
-- 可以这么用，但它并不方便
INSERT INTO TEST6_BACK SELECT 11,'老八',19,0,'laoba',sysdate FROM DUAL;

    2. 插入多行

      -- 跟插入单行一样，只是插入多行的结果集多一点而已，通常更新更新整表，部也是如此
INSERT INTO TEST6_BACK SELECT * FROM TEST6 T WHERE T.ID > 4;

    3. 插入多表

      --------------插入多表语法---------------------
-- 参数FIRST 当WHEN条件成功将执行该THEN且跳过后面WHEN字句
-- 参数ALL 执行每一个WHEN，不管前面是否成功，都将执行后面的WHEN
-- 参数ELSE 当条件都不满足时，执行ELSE中的插入子句
-- SUBQUERY 要进行多表插入的子查询
INSERT {FIRST|ALL} 
   [WHEN CONDITION THEN] INTO TABLE [VALUES(...)]
   [WHEN CONDITION THEN] INTO TABLE [VALUES(...)]
   ...
   ELSE INTO TABLE [VALUES(...)]
   SUBQUERY;
------------------实例------------------------
INSERT FIRST          -- 参数FIRST 当WHEN条件成功将执行该THEN且跳过后面WHEN字句
   WHEN MOD(ID,2)=0   -- 偶数将插入TEST_01表
     THEN
       INTO TEST_01
   WHEN MOD(ID,2)<>0  -- 奇数将插入TEST_02表
     THEN
       INTO TEST_02
   ELSE
     INTO TEST_03     -- 前面都不满足的情况下将插入TEST_03表， 该例此子句无意义
SELECT * FROM TEST6;

  2. 更新记录

    1. 更新行

      -- 两种方式
UPDATE TEST6 SET AGE=17 WHERE ID = 2;  -- 先确定更新表、再过滤列
update (SELECT * FROM TEST6 WHERE ID = 2) set age =77; -- 通过子查询先过滤列，再将子查询当作表更新  

    2. MERGE合并表行

      • 对比两个表，针对条件判断执行两种选择

        -- 该例 为存在该ID 则更新其他列，不存在该ID 则插入到目标表
MERGE INTO TEST_01 C         -- 目标表
USING TEST6 E                -- 原表
ON (C.ID = E.ID)             -- 条件
WHEN MATCHED THEN            -- 满足条件将执行下面代码
  UPDATE
  SET C.NAME = E.NAME,C.AGE=E.AGE,C.SEX=E.SEX,
  C.ENAME=E.ENAME,C.ADDTIME=E.ADDTIME
WHEN NOT MATCHED THEN        -- 不满足条件将执行下面代码
  INSERT VALUES(
  E.ID,E.NAME,E.AGE,E.SEX,
  E.ENAME,E.ADDTIME)

  3. 删除记录

    1. 删除单行记录

      1. DELETE语句一次只能删除整行记录，不能删除某个字段

      2. 使用DELETE语句，需要显示提交到数据库

      3. DELETE语句只能删除记录，不会删除表， 如果要删除整个表，使用 DROP TABLE 命令

      4. 如果一次性清除表中数据且不需要撤销，使用TRUNCATE语句

        -- DELETE FROM RESULT_COLUMN(结果集所有行)   将删除结果集所有行
-- 删除单行记录 DELETE FROM 
delete from test6 where id = 8;  -- 先确定删除表，再确定行
delete from (SELECT * FROM test6 t where t.id =8); -- 通过子查询先过滤行，再将子查询当作删除行

    2. 删除多行记录

      -- 通过相关子查询删除多行记录
DELETE FROM TEST6_BACK X WHERE EXISTS(SELECT 1 FROM TEST6 T2 WHERE T2.ID = X.ID )

    3. TRUNCATE清除表数据

      • TRUNCATE数据DDL数据定义语言，跟CREATE TABEL等语句一样，不具有撤销功能
      • 速度比DELETE效率高
      • 不会激活表的删除触发器
      • 有主外键关系的主表，使用TRUNCATE前禁用该约束
      • 因属于DDL语言，因此不能被PL/SQL语句块调用，必须使用动态语句调用方式 : EXECUTE IMMEDIATE V_SQL;
      TRUNCATE TABLE TEST6_BACK;

  4. 提交和回滚记录

    提交和回滚数据ORACLE事务处理内容，主要用来确保数据一致性

    1. 提交COMMIT：当执行DML语言时，隐式事务激活，被DML（insert、update、delete）操作的数据被放在数据段中，此时必须显示提交事务COMMIT，物理数据库才会更改
    2. 回滚ROLLBACK：当执行DML语言时，旧数据保存到回退表空间（UNDO TABLESPACE）中，执行ROLLBACK后，Oracle 会将回退表空间中的数据写到数据段中

  5. 序列

    主要用途是创建一个主键的值，它独立于表存储和生成

    -- 序列创建
CREATE SEQUENCE SEQUENCE_NAME  
[INCREMENT BY N]              -- 序列步长，默认为1
[START WITH N]                -- 序列初始值， 默认为1
[{MAXVALUE N | NOMAXVALUE}]   -- 最大值， 默认没有最大值定义
[{MINVALUE N | NOMINVALUE}]   -- 最小值， 默认最小值为1
[{CYCLE | NOCYCLE}]           -- 是否循环
[{CACHE N | NOCACHE}];        -- 定义缓存数，默认为20
    • NEXTVAL : 返回下一个可用序列值（SEQUENCE_NAME.NEXTVAL）
    • CURRVAL：获得当前的序列值（SEQUENCE_NAME.CURRVAL）

    8. 记录与集合

  • 复合类型

    1. 记录
    2. 集合

  1. 记录类型

    DECLARE
-- 记录类型定义
TYPE TYPE_NAME IS RECORD
(
DEPT_ROW DEPT%ROWTYPE,
EMPNO NUMBER,
ENAME VARCHAR(20),
JOB VARCHAR(20),
SAL NUMBER(7,2)
)
-- 声明记录类型的变量
emp_info emp_rec
BEGIN
  NULL;
  END;

  2. 集合类型

    1. 索引表
    
DECLARE
TYPE INX_TABLE IS TABLE OF VARCHAR2(10)    -- 定义INX_TABLE 为索引表名
INDEX BY pls_integer;     -- pls_integer 为使用整数为索引
V_Y INX_TABLE;            -- 定义索引表变量
BEGIN
  FOR I IN 1.. 10 LOOP
    V_Y(I):='HHD00'||I;
    END LOOP;
  FOR Y IN 1.. 10 LOOP
    DBMS_OUTPUT.put_line(V_Y(Y));
    END LOOP;
  END;
  
-- 实例，字符串索引 
DECLARE
A1 VARCHAR2(10);
A2 VARCHAR2(10);
TYPE INX_TABLE IS TABLE OF VARCHAR2(20)
INDEX BY varchar2(20);
V_Y INX_TABLE;
BEGIN
  FOR I IN 1.. 10 LOOP
    A1 := '单据00'||I;
    V_Y(A1) :='DH00'||I;
    --DBMS_OUTPUT.put_line(V_Y(A1));
    END LOOP;
    FOR Y IN 1.. 10 LOOP
    A2 := '单据00'||Y;
    DBMS_OUTPUT.put_line(V_Y(A2));
    END LOOP;
  END;

\

avatar
文章
阅读
粉丝