Oracle 数据库系统笔记(二)

简述

SQL: 由 IBM 公司 San Jose 研究所的两位研究员于 1974 年提出的结构化数据(查询)语言,并在 System R 上实现, 1986 年通过 ANSI, 1987 年通过 ISO, 不同厂商的 DBMS 支持的 SQL 一般在标准 SQL 基础上对 SQL 功能进行了扩展.例如 Oracle 的 PL/SQL, Microsoft SQL Server 的 Transact-SQL(T-SQL).
SQL 的主要功能:

SQL 功能	命令
DDL(数据定义)	CREATE, DROP, ALTER
DML(数据操纵)	SELECT, INSERT, UPDATE, DELETE,TRUNCATE
DCL(数据控制)	GRANT, REVOKE

多表查询

通过 WHERE 字句定义连接

select emp.* , dept.*
from emp, dept
where emp.deptno = dept.deptno;

在 FROM 字句中定义连接

select t1.c1, t2.c2
from t1 <conn type> t2
on (conn condition)
where <query condition> 

内连接([INNER] JOIN)

• 等值连接
• 非等值连接

select emp2.ename, emp2.sal
from emp emp1, emp emp2
WHERE emp1.sal < emp2.sal and emp1.ename = 'SMITH';

• 自然连接

SELECT emp.empno, emp.ename, dept.dname
FROM emp JOIN dept
ON (emp.deptno = dept.deptno);

外连接

select emp.empno, emp.ename, dept.dname
from emp left|right|full outer join dept dept
on (emp.deptno = dept.deptno);

交叉连接

不能使用 where 子句, 返回笛卡尔积

select emp.empno, emp.ename, dept.dname
from emp cross join dept dept

EXISTS 谓词子查询

select deptno, dname, loc
from dept
where exists(
            select *
            from emp
            where emp.deptno = dept.deptno and ename = 'SMITH'
            );

SQL 常用函数

Function_name	描述
avg()	求平均值
count()	返回指定列的个数(NULL 值除外)
first()	返回指定列的第一个值, 类似:last()
max()	返回最大值, 类似 min()
sum()	返回总值
group by	指定列分组
having	类似where 子句,但可用于聚合函数
upper() & lower()	大小写转换

SQL 语句优化

0.尽量不用 '*' 查询

• SQL 语句执行过程
  • SQL 语句从客户端进程传递至服务器端进程
  • 查找共享池中是否有此 SQL 语句
  • 判断语句语法正确性
  • 查找数据字典验证表和列定义
  • 检查当前用户操作权限
  • 确定语句最佳执行计划
  • 将语句和执行计划保存到共享的SQL区
• set timing on : 显示语句执行时间

1. 多用 'commit'

• 建议在 end 前使用

2.尽量用 WHERE 代替 HAVING

3.用 truncate 代替 delete

4.用表连接代替表的多次查询

5.exists 代替 in, distinct

SQL*Plus

SQL*Plus 是用户与 Oracle 数据库进行交互的客户端工具,支持 SQL 语句, PL*SQL 语句, 各种 SQL*Plus 命令,以及格式化输出结果.

SQL*Plus 命令类型

命令种类	命令说明
SQL 命令	DDL, DML, DCL 等
PL/SQL	通过 PL/SQL 语句编写的各种过程, 函数, 触发器和包等
SQL*Plus	格式化结果,保存,编辑,查看,执行 前两种程序,设置环境变量等

常用语句

• prompt "Please input: ": 向屏幕发送交互信息.
• accept variable [datatype] [format] [prompt text] [hide] : 提示用户输入数据, 例如: accept tempno number prompt 'Please input a number: ' hide
• & 与 && , 提示输入变量, && 只提示一次输入.
• SHO[W] option; :显示当前 SQL*PLUS环境的系统变量 , 错误信息, 初始化参数, 当前用户等.
• @file_name.sql : 执行脚本文件.与 start [file_name] 类似.
• spool [file_name]: 把输出结果复制到指定文件, 结束前需 off 该指令.
• set linesize|pagesize 160|20 : 设置行|页显示大小.
• set pause on|off: 启动|关闭分页显示.
• ttitle|btitle [left|center|bold|format] [text] [on|off]: 设置页眉页脚显示内容及格式

PL/SQL

PL/SQL 语言是 Ada 编程语言的结构化编程语言,由 oracle 从 6.0 开始提供的专用于 oracle 产品的数据库编程语言.

命名块分类:

• 子程序(subprogram) : 包括函数,储存过程等
• 触发器
• 程序包

基本结构

SET SERVEROUTPUT ON; --打开数据输出函数
DECLARE <option>
-- 声明变量,常量, 用户定义数据类型, 游标等
BEGIN
-- 主程序体,编写 PL/SQL 语句, 函数和存储过程
EXCEPTION
--异常处理程序
END;

数据类型

PL/SQL 中既可以使用 SQL 数据类型,也可以使用 %type 和 %rowtype 数据类型,指定为对应列或对应行的数据类型

• 绑定变量

-- 声明
var b_result number;
-- 赋值
exec :b_result := 1234; 
-- 打印绑定变量
print b_result;

bind.png

PL/SQL 匿名块 DML 示例

DECLARE
    v_deptno NUMBER(2,0) := 60;
    v_dname dept.dname%TYPE;
    v_loc dept.loc%type := '北京';
BEGIN 
    --插入数据
    dbms_output.put_line('-----Operation Start------');
    INSERT INTO dept VALUES(v_deptno, '财政部', v_loc);
    COMMIT;
    dbms_output.put_line('-----插入成功------');
    --更新数据
    UPDATE dept SET loc = '广州' WHERE deptno = 60;
    COMMIT;
    dbms_output.put_line('-----更新成功------');
    --删除数据
    DELETE FROM dept WHERE deptno= 60;
    COMMIT;
    dbms_output.put_line('-----删除成功------');
end;

函数与存储过程

区别:

• 函数不能进行 DML 操作, 有返回值,
• 存储过程进行 DML 操作, 无返回值.

--函数定义
CREATE [OR REPLACE] FUNCTION function_name
 (arg1 [ { IN | OUT | IN OUT }] type1 [DEFAULT value1],
 [arg2 [ { IN | OUT | IN OUT }] type2 [DEFAULT value1]],
 ......
 [argn [ { IN | OUT | IN OUT }] typen [DEFAULT valuen]])
 [ AUTHID DEFINER | CURRENT_USER ]
RETURN return_type
 IS | AS
    <类型.变量的声明部分>
BEGIN
    执行部分
    RETURN expression
EXCEPTION
    异常处理部分
END function_name; 

参数传递:

• 传址法
  使用 IN 或默认时将实参的地址传递给形参
• 传值法
  使用 OUT 或 IN OUT 时将实参的实际数据传递给形参

-- 存储过程创建
CREATE [OR REPLACE] PROCEDURE procedure_name
([arg1 [ IN | OUT | IN OUT ]] type1 [DEFAULT value1],
 [arg2 [ IN | OUT | IN OUT ]] type2 [DEFAULT value1]],
 ......
 [argn [ IN | OUT | IN OUT ]] typen [DEFAULT valuen])
    [ AUTHID DEFINER | CURRENT_USER ]
{ IS | AS }
  <声明部分>
BEGIN
  <执行部分>
EXCEPTION
  <可选的异常错误处理程序>
END procedure_name;

游标(cursor)

-- 使用 游标 处理多条数据
create or replace procedure p_printemp(i_depid number)
as
v_name employees.first_name%type;
v_sal employees.salary%type;
v_hdate employees.hire_date%type;
cursor cur_emp is
    select first_name, salary, hire_date
         from employees where employees.department_id = i_depid;
begin
    open cur_emp;
    fetch cur_emp into v_name, v_sal, v_hdate;
    while cur_emp%FOUND loop
        dbms_output.put_line(v_name || ': ' || v_sal || ', ' || v_hdate);
        fetch cur_emp into v_name, v_sal, v_hdate;
    end loop;
    close cur_emp;
exception 
    when no_data_found then 
        dbms_output.put_line('您输入的编号不存在');
end;
/
show error;
exec p_printemp(100);

流程控制语句

DECLARE
    v_empno  employees.employee_id%TYPE :=&empno;
    V_salary employees.salary%TYPE;
    V_comment VARCHAR2(35);
BEGIN
   SELECT salary INTO v_salary FROM employees 
   WHERE employee_id = v_empno;
   IF v_salary < 1500 THEN
       V_comment:= '太少了,加点吧~!';
   ELSIF v_salary <3000 THEN
      V_comment:= '多了点,少点吧~!';
   ELSE
      V_comment:= '没有薪水~!';
   END IF;
   DBMS_OUTPUT.PUT_LINE(V_comment);
   exception
     when no_data_found then
        DBMS_OUTPUT.PUT_LINE('没有数据~!');
     when others then
        DBMS_OUTPUT.PUT_LINE(SQLCODE || '---' || sqlerrm);        
END;