【数据库】Oracle&PL/SQL

最新推荐文章于 2024-11-02 12:29:24 发布

bfsz

最新推荐文章于 2024-11-02 12:29:24 发布

阅读量191

点赞数 1

分类专栏： Oracle 文章标签： oracle plsql

本文链接：https://blog.csdn.net/eureka52hz/article/details/104656293

版权

Oracle 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

（一）、单表查询

--=====================查询==========================
SELECT * FROM emp;

SELECT p.empno, p.ename FROM emp p;

SELECT e.deptno, e.ename, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;

--=====================NULL===========================
SELECT * FROM emp p WHERE p.comm IS NULL;

--===================拼接操作符 ||========================
SELECT p.empno || p.ename AS "编号名称" FROM emp p;
SELECT p.ename || ' is no is ' || p.empno "message" FROM emp p;

--===================去重 DISTINCT=====================
SELECT DISTINCT p.job FROM emp p;

--=======================条件限制========================
SELECT * FROM emp p WHERE p.sal > 2000;
SELECT * FROM emp p WHERE p.deptno <> 20;
SELECT * FROM emp p WHERE p.sal BETWEEN 2000 AND 3000;
SELECT * FROM emp p WHERE p.deptno IN (10, 20);
SELECT * FROM emp p WHERE p.ename LIKE '_O%';
SELECT * FROM emp p WHERE p.sal > 2000 AND p.deptno <> 20;
SELECT * FROM emp p WHERE p.sal > 2000 OR p.empno <> 20;
SELECT * FROM emp p WHERE p.deptno NOT IN(10,20);

--======================排序==============================
SELECT * FROM emp p ORDER BY sal ASC;--升
SELECT * FROM emp p ORDER BY sal DESC;--降
SELECT * FROM emp p WHERE p.deptno IN (10,20) ORDER BY p.sal;
SELECT p.sal*12 year_sal,p.ename FROM emp p ORDER BY year_sal;
SELECT * FROM emp p ORDER BY p.sal,p.empno;

--====================单行函数===============================
-- 大小写转换函数
SELECT INITCAP(p.ename) FROM emp p; --首字母大写
SELECT LOWER(p.ename) FROM emp p;--字母小写
SELECT UPPER(p.ename) FROM emp p;--字母大写

-- 字符串操作函数
SELECT CONCAT(p.empno,p.ename) FROM emp p; -- 拼接
SELECT SUBSTR('helloOracle',1,5) FROM dual;-- 截取
SELECT LENGTH('qwsdsa') FROM dual; -- 长度
SELECT INSTR('qwertyu','y') FROM dual; -- 位置角标
SELECT LPAD(p.sal,10,'*') FROM emp p;
SELECT RPAD(p.sal,10,'*') FROM emp p;
SELECT TRIM('h' FROM 'helloWorld') FROM dual;
SELECT TRIM(' hello dd ') FROM dual; --去空格

-- 数字操作函数
SELECT ROUND(45.926,2) FROM dual; -- 保留小数点2位,四舍五入
SELECT TRUNC(45.926,2) FROM dual; -- 保留小数点2位,不四舍五入
SELECT MOD(1500,200) FROM dual; --求余

--日期<->字符串
SELECT to_char(SYSDATE,'YYYY-MM-DD') FROM dual;
SELECT to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS AM') FROM dual;
SELECT to_date('1994-09-11','YYYY-MM-DD') FROM dual;

--数字<->字符串
SELECT to_char(9000,'L99,999.00') FROM dual;
SELECT to_number('123143') FROM dual;

--NVL、NVL2 
SELECT NVL(p.comm,0) comm FROM emp p;--如果expr1为空，返回expr2
SELECT NVL2(p.comm,0,1) comm FROM emp p;--如果expr1为空，返回expr3（第2个结果）否则返回expr2
SELECT NULLIF(LENGTH(p.ename),LENGTH(p.job)) FROM emp p;

--条件表达式
SELECT p.ename,
       p.deptno,
       p.sal,
       DECODE(p.deptno, '10', 1.5 * sal, '20', 1.7 * sal, '30', 2.0 * sal) r_sal
  FROM emp p;

（二）、多表查询

--=========================多表关联查询=====================
--等于链接 =
SELECT p.ename, t.deptno, t.dname
  FROM emp p, dept t
 WHERE p.deptno = t.deptno;
 
--不等链接 包括> , < , !=, between
--“ 外 链接” 语法： 包括左外连接，右外连接
-- 左
SELECT p.ename, t.deptno, t.dname
  FROM emp p, dept t
 WHERE p.deptno(+) = t.deptno;
-- 右
SELECT p.ename, t.deptno, t.dname
  FROM emp p, dept t
 WHERE p.deptno = t.deptno(+);
 
-- 自然关联
SELECT p.empno, p.ename, t.loc FROM emp p JOIN dept t USING (deptno);


--========================分组计算函数==============================
SELECT SUM(p.sal),
       AVG(p.sal),
       MAX(p.sal),
       MIN(p.sal),
       COUNT(p.sal),
       VARIANCE(p.sal),
       STDDEV(p.sal)
  FROM emp p;

/*COUNT(*) 返回满足选择条件的所有行的行数，包括值为空的行和重复的行
COUNT(expr) 返回满足选择条件的且表达式不为空行数。
COUNT(DISTINCT expr) 返回满足选择条件的且表达式不为空，且不重复的行数。*/

--=========================GROUP BY==================================
SELECT ROUND(AVG(p.sal), 2) FROM emp p GROUP BY p.deptno;

--可以按照某一个字段分组，也可以按照多个字段的组合进行分组
SELECT ROUND(AVG(p.sal), 2), p.job, p.deptno
  FROM emp p
 GROUP BY p.deptno, p.job;

-- 不能在Where 条件中使用分组计算函数表达式
SELECT ROUND(AVG(p.sal), 2) avg_sal
  FROM emp p
 GROUP BY p.deptno
HAVING ROUND(AVG(p.sal), 2) > 2500;

--=============================子查询==============================
/*单行比较必须对应单行子查询（返回单一结果值的查询）； 比如= ， >
多行比较必须对应多行子查询（返回一个数据集合的查询）；比如 IN , > ANY, > ALL 等*/
-- 薪水小于于LUNA
SELECT p.ename
  FROM emp p
 WHERE p.sal < (SELECT sal FROM emp WHERE ename = 'LUNA');
 
SELECT p.empno, p.ename, p.sal, p.deptno
  FROM emp p
 WHERE p.sal < ANY (SELECT sal FROM emp p WHERE p.deptno = '20')
   AND p.deptno <> 20;

（三）、DML语句

--=============================DML语句：数据操作语句==========================
-- 【INSERT】
-- 方式1
INSERT INTO person
  (pid, NAME, gender, birthday, address)
VALUES
  (persons.nextval, 'YUI', 0, to_date('1998-09-12', 'YYYY-MM-DD'), 'japan');
  
-- 方式2
INSERT INTO person
VALUES
  (persons.nextval, 'KOOI', 0, to_date('1999-10-12', 'YYYY-MM-DD'), 'japan');
  
-- 方式3:从另一个表中 Copy 一行
INSERT INTO person
  (pid, NAME, gender, birthday, address)
  SELECT * FROM TABLE;


-- 【UPDATE】
-- 方式1 具体的值
UPDATE person p SET p.gender = 0 WHERE p.pid = 3;

-- 方式2 子查询的结果作为更新后的值
UPDATE person p
   SET p.birthday =
       (SELECT to_date('1995-10-11', 'YYYY-MM-DD') FROM dual)
 WHERE p.pid = 3;

-- 【DELETE】
-- 方式1 删除某条记录
DELETE FROM person p WHERE p.pid = 2;

-- 方式2 删除所有记录
DELETE FROM person;
-- TRUNCATE 语句无法回滚，因此除非是单独执行，并非常确认，否则慎用

（四）、事务和锁

1、事务控制
Commit, rollback是显式的提交和回滚语句
当如下事件发生是，会隐式的执行Commit 动作：
1 、数据定义语句被执行的时候，比如新建一张表：Create Table …
2 、数据控制语句被执行的时候，比如赋权 GRANT …( 或者 DENY)
3 、正常退出 SQL*Plus 或者PLSQL DEVELOPER, 而没有显式的执行COMMIT或者 ROLLBACK 语句。

当如下事件发生时，会隐式执行Rollback 动作：

非正常退出iSQL*Plus, PLSQL DEVELOPER, 或者发生系统错误。
“读一致性”: 对于有人正在修改过程中的一批数据，在其位提交前，其他用户读到的是一致的内容。

2、锁
Oracle 中的锁的主要作用就是：防止并发事务对相同的资源（所谓资源是指表、行、共享的数据结构、数据字典行等）进行更改的时候，相互破坏。

-- 查锁
select a.*, C.type, C.LMODE
  from v$locked_object a, all_objects b, v$lock c
 where a.OBJECT_ID = b.OBJECT_ID
   and a.SESSION_ID = c.SID
   and b.OBJECT_NAME = 'TESTTAB3';

（五）、数据库对象

1、表

表的命名要求和表中列的命名要求：
1 、必须以字母开头
2 、长度不能超过30 个字符
3 、只能包含 A–Z，a–z， 0–9， _， $， and #
4 、不能与数据库中的已有对象重名
5 、不能使用Oracle 数据库的保留字

--复制表
CREATE TABLE person_c AS SELECT * FROM person;
--复制表 保留表结构，但不想要数据
CREATE TABLE person_d AS SELECT * FROM person WHERE 1=2;
--删除表
DELETE TABLE person;
--更改表名
RENAME person TO persons;
--清空表，保留结构 没有Rollback 机会
TRUNCATE TABLE person；

--添加列
ALTER TABLE person ADD (phone VARCHAR2(11));
--修改列
ALTER TABLE person MODIFY(phone VARCHAR2(20));
--修改列名
ALTER TABLE PERSON RENAME COLUMN phnoe to PHONE;
--删除列
ALTER TABLE person DROP(phone);

2、约束


NOT NULL	（非空约束）
UNIQUE	（唯一性约束）
PRIMARY KEY	（主键约束）
FOREIGN KEY	（外键约束）
CHECK	（自定义约束）

约束的创建方法：
1 、在创建表的时候同时创建约束
2 、单独创建约束

3、视图

简单视图：关联表数量1个，允许DML操作
复杂视图：关联表数量1或多个，不允许DML操作

-- 简单视图
CREATE VIEW personv
AS SELECT * FROM person;

--复杂视图
CREATE VIEW dept_sum_vu
(name, minsal, maxsal, avgsal)
AS SELECT d.dname, MIN(e.sal),
MAX(e.sal),AVG(e.sal)
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname;

--删除视图
DROP VIEW personv;

4、序列、索引、同义

序列（主键自增），序列取值： CURRVAL 取当前值， NEXTVAL

CREATE SEQUENCE person_seq
INCREMENT BY 1
START WITH 1
MAXVALUE 999999999999
NOCYCLE NOCACHE;

--更改
ALTER SEQUENCE person_seq
INCREMENT BY 20
MAXVALUE 999999
NOCACHE NOCYCLE;

--删除
DROP SEQUENCE person_seq;

索引
1、数据量大，希望数据有序，使用二分法查找，利用索引
2、创建索引

CREATE INDEX t_person_name_idx ON t_person(NAME);

什么情况下适合用索引？
查询条件中使用到这个列（或者这个列于其他列的组合），且这个列（或者与其他列的组合）上的数字范围跨度很大，而大多数情况下我们要获取的数据的量占整个表的数据总量小于4% ；
什么情况下不适合用索引？
表小；基于该表查询，多数情况下需获取的数据量超过总量4%；表频繁更新（降低数据库效率）

（六）SQL进价

集合操作

-- UNION:去除重复记录
SELECT * FROM person UNION SELECT * FROM c_person;
-- UNION ALL:保留重复记录
SELECT * FROM person UNION ALL SELECT * FROM c_person;
-- INTERSECT 取交集     
SELECT * FROM person INTERSECT SELECT * FROM c_person;
-- MINUS 取差集
SELECT * FROM c_person MINUS SELECT * FROM person;

GROUP BY字句增强
使用Rollup产生常规分组汇总行以及分组小计，Rollup 后面跟了n个字段，就将进行n+1次分组，从右到左每次减少一个字段进行分组；然后进行union

SELECT p.deptno, SUM(p.sal), p.job
  FROM emp p
 GROUP BY rollup(p.deptno, p.job);

子查询进阶

--Exists/Not Exists
SELECT *
  FROM emp p
 WHERE p.empno IN (SELECT p.mgr FROM emp p WHERE p.mgr IS NOT NULL);

SELECT *
  FROM emp e
 WHERE EXISTS (SELECT 'X' FROM emp p WHERE p.mgr = e.empno);

（七）用户权限

--================控制用户权限===================
-- Create the user 
create user LUNA
  default tablespace SYSTEM
  temporary tablespace TEMP
  profile DEFAULT
  password expire;

GRANT create session
, create table
, create procedure
, create sequence
, create trigger
, create view
, create synonym
, alter session
TO luna;

GRANT resource to luna;
--
SELECT * FROM ROLE_SYS_PRIVS;--角色对应的系统权限
SELECT * FROM ROLE_TAB_PRIVS;--角色对应的表权限
SELECT * FROM USER_ROLE_PRIVS;--用户的角色分配表 
SELECT * FROM USER_TAB_PRIVS_MADE;--用户对象上赋权者与被赋者的历史赋权情况
SELECT * FROM USER_TAB_PRIVS_RECD;--用户对象上拥有者与被赋者的历史赋权情况
SELECT * FROM USER_COL_PRIVS_MADE;--用户对象列上赋权者与被赋者的历史赋权情况
SELECT * FROM USER_COL_PRIVS_RECD;-- 用户对象列上拥有者与被赋者的历史赋权情况
SELECT * FROM USER_SYS_PRIVS;--用户的系统权限

--收回权限
REVOKE {privilege [, privilege...]|ALL}
ON object
FROM {user[, user...]|role|PUBLIC}

（八）PL/SQL

匿名块、存储过程、函数

1.1 匿名块（增删改查SQL）

DECLARE
  --变量声明
  v_hiredate  DATE;
  v_deptno    NUMBER(2) NOT NULL := 10;
  v_loc       VARCHAR2(20) := 'earth';
  v_total_sal NUMBER(9, 2) := 0;
  v_flag      BOOLEAN NOT NULL := TRUE;
  v_name      emp.ename%TYPE; --%TYPE 属性来声明与XX 类型一致的变量类型
	v_emp       emp%ROWTYPE;--%ROWTYPE 表示某张表的记录类型
BEGIN
  --把从数据库查询出内容存入变量，仅支持单行的查询结果
  SELECT SUM(sal) INTO v_total_sal FROM emp;
  --输出
  dbms_output.put_line('total sal:' || to_char(v_total_sal));
	
  --INSERT 、UPDATE 、DELETE
  INSERT INTO person
    (pid, name, gender, birthday, address)
  VALUES
    (Person_Seq.Nextval,
     'etyuddg',
     0,
     to_date('2011-11-23', 'YYYY-MM-DD'),
     'beijing');

  UPDATE person p SET p.address = v_loc WHERE p.name = 'ccyui';

  DELETE FROM person p WHERE p.pid = 3;

  --提交事务
  COMMIT;
END;

1.2 控制语句

DECLARE
  v_gender NUMBER(1);
  v_name   VARCHAR2(20) := 'YUI';
  v_x      NUMBER;

BEGIN
  SELECT p.gender INTO v_gender FROM person p WHERE p.name = v_name;

  --判断语句
  -- IF...ESLE
  IF v_gender = 1 THEN
    dbms_output.put_line('男');
  ELSIF v_gender = 0 THEN
    dbms_output.put_line('女');
  ELSE
    dbms_output.put_line('no data');
  END IF;

  -- CASE
  CASE v_gender
    WHEN 1 THEN
      dbms_output.put_line('男');
    WHEN 0 THEN
      dbms_output.put_line('女');
    ELSE
      dbms_output.put_line('no data');
  END CASE;

  --循环语句
  --LOOP
  v_x := 0;
  LOOP
    v_x := v_x + 1;
    EXIT WHEN v_x > 9;
    dbms_output.put_line('v_x=' || v_x);
  END LOOP;

  --WHILE
  v_x := 0;
  WHILE v_x < 9 LOOP
    v_x := v_x + 2;
    dbms_output.put_line('v_x=' || v_x);
  END LOOP;

  --FOR
  v_x := 1;
  FOR v_x IN REVERSE 1 .. 10 LOOP
    dbms_output.put_line('v_x=' || v_x);
  END LOOP;
END;

1.3 游标
游标是一个私有的SQL 工作区域，Oracle 数据库中有两种游标，分别是隐式游标和显式游标
显式游标的相关函数可以做到：
1 、一行一行的处理返回的数据。
2 、保持当前处理行的一个跟踪，像一个指针一样指示当前的处理的记录。
3 、允许程序员在PLSQL 块中人为的控制游标的开启、关闭、上下移动
1.4 例外（异常）处理

--预定义的例外
DECLARE
  v_name VARCHAR2(20);
BEGIN
  SELECT p.name INTO v_name FROM person p WHERE p.pid = 123;
  IF SQL%NOTFOUND THEN
    RAISE no_data_found;
  END IF;
EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line('无数据');
  WHEN too_many_rows THEN
    dbms_output.put_line('多行');
  WHEN OTHERS THEN
    -- Others 表明我们程序员未能预计到这种错误，所以全部归入到others 里面
    dbms_output.put_line('其他异常');
END;

--自定义
DECLARE
  e_nodata EXCEPTION;
  v_count NUMBER;
BEGIN
  SELECT COUNT(0) INTO v_count FROM person p WHERE p.pid = 123;
  IF v_count = 0 THEN
    RAISE e_nodata;
  END IF;
EXCEPTION
  WHEN e_nodata THEN
    dbms_output.put_line('无数据');
  WHEN OTHERS THEN
    dbms_output.put_line('其他异常');
END;

1.5 存储过程

create or replace package person_pkg is

  -- Author  : ADMINISTRATOR
  -- Created : 2019/12/5 8:31:33
  -- Purpose : 
  --================================
  /*
  * 通过UID查询
  */
  --================================
  PROCEDURE query_person(p_id NUMBER);
  --================================
  /*
  * 查询全部
  */
  --================================
  PROCEDURE query_all_person(pid NUMBER);
  --================================
  /*
  * 更新
  */
  --================================
  PROCEDURE update_person(p_id       NUMBER,
                          p_name     VARCHAR2,
                          p_gender   NUMBER,
                          p_birthday VARCHAR2,
                          p_address  VARCHAR2);
  --================================
  /*
  * 增加
  */
  --================================
  PROCEDURE insert_person(p_name     VARCHAR2,
                          p_gender   NUMBER,
                          p_birthday VARCHAR2,
                          p_address  VARCHAR2);
  --================================
  /*
  * 删除
  */
  --================================
  PROCEDURE delete_person(p_id NUMBER, p_name VARCHAR2);
end person_pkg;
/
create or replace package body person_pkg is

  --================================
  /*
  * 通过UID查询
  */
  --================================
  PROCEDURE query_person(p_id IN NUMBER) AS
    v_person   person%ROWTYPE;
    v_name     person.name%TYPE;
    v_gender   person.gender%TYPE;
    v_birthday person.birthday%TYPE;
    v_address  person.address%TYPE;
  BEGIN
    SELECT p.name, p.gender, p.birthday, p.address
      INTO v_name, v_gender, v_birthday, v_address
      FROM person p
     WHERE p.pid = p_id;
  
    dbms_output.put_line(v_name || ' ' || v_gender || ' ' ||
                         to_char(v_birthday, 'YYYY-MM-DD') || ' ' ||
                         v_address);
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('person_pkg->query_person ERROR');
  END;

  --================================
  /*
  * 查询全部
  */
  --================================
  PROCEDURE query_all_person(pid NUMBER) AS
  BEGIN
    FOR persons IN (SELECT p.name, p.gender, p.birthday, p.address
                      FROM person p) LOOP
      dbms_output.put_line(persons.name || ' ' || persons.gender || ' ' ||
                           to_char(persons.birthday, 'YYYY-MM-DD') || ' ' ||
                           persons.address);
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('person_pkg->query_all_person ERROR');
  END;
  --================================
  /*
  * 更新
  */
  --================================
  PROCEDURE update_person(p_id       IN NUMBER,
                          p_name     IN VARCHAR2,
                          p_gender   IN NUMBER,
                          p_birthday IN VARCHAR2,
                          p_address  IN VARCHAR2) AS
  BEGIN
    UPDATE person p
       SET p.name     = p_name,
           p.gender   = p_gender,
           p.birthday = to_date(p_birthday, 'YYYY-MM-DD'),
           p.address  = p_address
     WHERE p.pid = p_id;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('person_pkg->update_person ERROR');
  END;
  --================================
  /*
  * 增加
  */
  --================================
  PROCEDURE insert_person(p_name     VARCHAR2,
                          p_gender   NUMBER,
                          p_birthday VARCHAR2,
                          p_address  VARCHAR2) AS
  BEGIN
    INSERT INTO person
    VALUES
      (person_s.nextval,
       p_name,
       p_gender,
       to_date(p_birthday, 'YYYY-MM-DD'),
       p_address,
       NULL);
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('person_pkg->insert_person ERROR');
  END;
  --================================
  /*
  * 删除
  */
  --================================
  PROCEDURE delete_person(p_id NUMBER, p_name VARCHAR2) AS
  BEGIN
    DELETE FROM person p
     WHERE p.pid = p_id
        OR p.name = p_name;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('person_pkg->delete_person ERROR');
  END;
end person_pkg;
/

（九）问题

1 、ORACLE 密码忘了咋办

	1、CONNECT /AS SYSDBA;--回车*2
	2、ALTER USER SYS IDENTIFIED BY SYS;

bfsz

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录