Oracle学习笔记（三）

作者: dev_winner | 来源:发表于2019-09-19 14:36 被阅读0次

基本查询语句

-- 查询语句加上DISTINCT关键字表示不显示重复的记录
SELECT [DISTINCT] column_name1, ... | * FROM table_name [WHERE conditions];

在SQL*PLUS中设置查询显示格式

-- 更改查询结果显示的一个字段名，并不会对原表进行修改，其中COLUMN可以简写成COL
COLUMN column_name HEADING new_name;
-- 例如：创建users新表，并对其查询结果中username字段进行更改
CREATE table users
(
  id varchar2(10) primary key,
  username varchar2(20),
  salary number(7, 2)
);
INSERT INTO users VALUES('1', 'AAA', 800);
INSERT INTO users VALUES('2', 'bbb', 1800.5);
INSERT INTO users VALUES('3', 'Ccc', 5000.5);
COL username HEADING 用户名;
SELECT * FROM users;
-- 设置输出格式，若是字符类型，则只能设置显示其的长度。
COLUMN column_name FORMAT dataformat;
-- 例如：设置username显示的字符长度为10
SQL> COL username format a10;
SQL> SELECT * FROM users;
ID         用户名         SALARY
---------- ---------- ----------
1          AAA               800
2          bbb            1800.5
3          Ccc            5000.5

-- 数值类型用'9'代表一位数字占位符
SQL> COL salary FORMAT 9999.9;
SQL> SELECT * FROM users;
ID         用户名      SALARY
---------- ---------- -------
1          AAA          800.0
2          bbb         1800.5
3          Ccc         5000.5

--  若设置为三位数字显示，则会用'#'代替，类似excel
SQL> COL salary FORMAT 999.9;
SQL> SELECT * FROM users;
ID         用户名     SALARY
---------- ---------- ------
1          AAA         800.0
2          bbb        ######
3          Ccc        ######

-- 添加美元符号'$'
SQL> COL salary FORMAT $9999.9;
SQL> SELECT * FROM users;
ID         用户名       SALARY
---------- ---------- --------
1          AAA          $800.0
2          bbb         $1800.5
3          Ccc         $5000.5

-- 清除设置查询结果的显示格式
COLUMN column_name CLEAR;

SQL> COL username clear;
SQL> COL salary clear;
SQL> SELECT * FROM users;
ID         USERNAME                 SALARY
---------- -------------------- ----------
1          AAA                         800
2          bbb                      1800.5
3          Ccc                      5000.5

给字段设置别名，并不更改表中的字段名，其中关键字AS可以省略，用空格隔开原来的字段名和新字段名即可

SELECT column_name AS new_name, ... FROM table_name;
-- 例如：设置user表中各个字段查询结果的别名
SQL> SELECT id AS 编号, username as 用户名, salary 工资 FROM users;
编号       用户名                     工资
---------- -------------------- ----------
1          AAA                         800
2          bbb                      1800.5
3          Ccc                      5000.5

表达式 = 操作数 + 运算符
Oracle中操作数可以是变量、常量和字段
- 算术运算符（+,-,*,/）
- 比较运算符（>, >=, <, <=, =, <>）
- 逻辑运算符（and, or, not）
优先级：比较运算符>逻辑运算符（not>and>or ）
模糊查询：关键字为LIKE
- 通配符的使用（_, %）：一个_只能代表一个字符，一个%可以代表0到任意多个字符。

-- 例如：查询用户名以A开头的用户信息
SQL> SELECT * FROM users WHERE username LIKE 'A%';
ID         USERNAME                 SALARY
---------- -------------------- ----------
1          AAA                         800

-- 查询用户名中含有A的用户信息
SQL> SELECT * FROM users WHERE username LIKE '%A%';
ID         USERNAME                 SALARY
---------- -------------------- ----------
1          AAA                         800

范围查询：[NOT] BETWEEN...AND（左闭右闭）、IN/NOT IN

SQL> SELECT * FROM users WHERE salary BETWEEN 800 AND 2200;
ID         USERNAME                 SALARY
---------- -------------------- ----------
1          AAA                         800
2          bbb                      1800.5

SQL> SELECT * FROM users WHERE username IN('AAA', 'bbb');
ID         USERNAME                 SALARY
---------- -------------------- ----------
1          AAA                         800
2          bbb                      1800.5

对查询结果进行（升序/降序）排序

SELECT ... FROM ... [WHERE ...] ORDER BY column_name1 DESC/ASC , ...;

SQL> SELECT * FROM users order by id desc;
ID         USERNAME                 SALARY
---------- -------------------- ----------
3          Ccc                      5000.5
2          bbb                      1800.5
1          AAA                         800

INSERT INTO users VALUES(5, 'aaa', 2000);
-- 先对username第一关键字进行降序排序，若第一关键字有两个值相等，则对第二关键字进行升序排序
SQL> SELECT * FROM users order by username desc, salary ASC;
ID         USERNAME                 SALARY
---------- -------------------- ----------
2          bbb                      1800.5
4          aaa                        1000
5          aaa                        2000
3          Ccc                      5000.5
1          AAA                         800

case...when语句的作用：相当于多条if-else语句设置查询结果的不同值

-- 语法1：不太灵活
CASE column_name WHEN value1 THEN result1, ... [ELSE result] END;

-- 例如：设置查询显示结果
SQL> SELECT username, CASE username when 'aaa' THEN '计算机部门' when 'bbb' then '市场部门' ELSE '其他部门' END as 部门 FROM users;
USERNAME             部门
-------------------- ----------
AAA                  其他部门
bbb                  市场部门
Ccc                  其他部门
aaa                  计算机部门
aaa                  计算机部门

-- 语法2：灵活易用
CASE WHEN column_name=value1 THEN result1, ... [ELSE result] END; 

SQL> SELECT username, CASE when username='aaa' THEN '计算机部门' when username='bbb' then '市场部门' ELSE '其他部门' END as 部门 FROM users;
USERNAME             部门
-------------------- ----------
AAA                  其他部门
bbb                  市场部门
Ccc                  其他部门
aaa                  计算机部门
aaa                  计算机部门

SQL> SELECT username, CASE when salary <= 800 then '工资低' when salary > 5000 then '工资高' END as 工资水平 FROM users;
USERNAME             工资水
-------------------- ------
AAA                  工资低
bbb
Ccc                  工资高
aaa
aaa

decode()函数的使用：相当于case...when语句或者多条if-else语句。

DECODE(column_name, value1, result1, value2, result2, ..., defaultvalue);

SQL> SELECT username, decode(username, 'AAA', '计算机部门', 'bbb', '市场部门', '其他') as 部门 FROM users;
USERNAME             部门
-------------------- ----------
AAA                  计算机部门
bbb                  市场部门
Ccc                  其他
aaa                  其他
aaa                  其他

PL/SQL（Procedure Language/SQL），是Oracle对sql语言的过程化扩展，指在SQL命令语言中增加了过程处理语句（如分支、循环等），使得SQL语言具有过程处理能力。PL/SQL是面向过程的语言。
不同数据库的SQL 扩展

Oracle：PL/SQL
DB2：SQL/PL
SQL Server：Transac-SQL（T-SQL）

PL/SQL的程序结构

declare
  -- 说明部分（变量说明、光标申明、例外说明）
begin
  -- 语句序列（DML语句）
exception
  -- 例外处理语句
end;
/
-- 例如：打印hello world
set SERVEROUTPUT on
declare
begin
    DBMS_OUTPUT.PUT_LINE('Hello World!');
end;
/
-- 例如：使用基本变量
declare
    pnumber number(7, 2);
    pname VARCHAR2(20);
    pdate date;
begin
    pnumber := 1;
    DBMS_OUTPUT.put_line(pnumber);
    pname := 'Tom';
    DBMS_OUTPUT.put_line(pname);
    pdate := sysdate;
    DBMS_OUTPUT.put_line(pdate);
    DBMS_OUTPUT.PUT_LINE(pdate + 1); 
end;
/

说明部分：
- 引用型变量，例如：my_name emp.ename%type;，表示变量my_name的类型为emp.ename的类型
- 记录型变量，例如：emp_rec emp%rowtype;，其中emp_rec变量相当于一个数组，将一行记录的所有类型赋予一个数组。记录型变量分量的引用：emp_rec.ename := 'ADAMS';

set SERVEROUTPUT on
-- 引用型变量的使用
declare 
    -- 定义引用型变量：查询并打印7839的姓名和薪水
    pename emp.ename%type;
    psal emp.sal%type;
begin
    -- into 或者 := 为赋值操作
    select ename, sal into pename, psal from emp where empno = 7839;
    dbms_output.put_line(pename || '的薪水是' || psal);
end;
/

-- 记录型变量的使用
set SERVEROUTPUT on
declare
    emp_rec emp%rowtype;
begin
    -- 得到 7839 一行的信息
    select * into emp_rec from emp where empno = 7839;
    DBMS_OUTPUT.put_line(emp_rec.ename || '的薪水是' || emp_rec.sal);
end;
/

-- 条件判断：if then elsif then ... else ... end if; 的使用，注意：这里没有进行异常处理，只能输入纯数字
set SERVEROUTPUT on
-- 接收键盘输入一个数字
-- num: 地址值，表示在该地址上保存了输入的值
ACCEPT num PROMPT '请输入一个数字：';
DECLARE
    pnum number := &num; -- 取值
BEGIN
    IF pnum = 0 then dbms_output.put_line('您输入的数字是0');
    elsif pnum = 1 then dbms_output.put_line('您输入的数字是1');
    else dbms_output.put_line('其他数字');
    end if;
end;
/

-- 循环语句1：while 循环条件 LOOP 循环体 END LOOP; 的使用
set SERVEROUTPUT on
declare 
    -- 定义循环变量，循环打印1~10
    pnum number := 1;
begin
    while pnum <= 10 LOOP
        DBMS_OUTPUT.PUT_LINE (pnum);
        pnum := pnum + 1;
    end LOOP;
end;
/

-- 循环语句2：LOOP EXIT [WHEN 退出条件]; ... END LOOP; 的使用
set SERVEROUTPUT on
declare 
    -- 定义循环变量
    pnum number := 1;
begin
    LOOP
    exit when pnum > 10;
    DBMS_OUTPUT.put_line(pnum);
    pnum := pnum + 1;
    end LOOP;
end;
/

-- 循环语句3： FOR i IN 1..n LOOP 语句序列; END LOOP;
-- 打开屏幕输出
set SERVEROUTPUT on
DECLARE
    pnum number := 1;
begin
    -- 必须是连续的区间，并且只需写出左区间端点和右区间端点，中间用2个点隔开
    for pnum in 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE(pnum);
    END LOOP;
end;
/

光标就是一个结果集（Result Set）

CURSOR 光标名 [(参数名 数据类型[, 参数名 数据类型] ...)] IS SELECT 语句;

-- 查询并打印员工的姓名和薪水
SET SERVEROUTPUT ON
DECLARE
    -- 定义一个光标
    CURSOR cemp IS SELECT ename, sal from emp;
    -- 为光标定义对应的变量
    pename emp.ename%type;
    psal emp.sal%TYPE;
BEGIN
    -- 打开光标执行查询
    OPEN cemp;
    LOOP
    -- 通过光标取一条记录
    FETCH cemp INTO pename, psal;
    EXIT WHEN cemp%notfound;
    DBMS_OUTPUT.put_line(pename || '的薪水是' ||psal);
    END LOOP;
    -- 关闭光标释放资源
    CLOSE cemp;
END;
/

fetch关键字的作用：把当前指针指向的记录返回，并将指针指向下一条记录。
光标的属性：%found（若fetch语句取到一条记录，则返回true，反之返回false）、%notfound、%isopen（判断光标是否打开）、%rowcount（影响的行数）
光标数的限制：默认情况下，Oracle数据库只允许在同一个会话中打开300个光标！
修改光标数的限制：ALTER system set open_cursors=400 scope=both;。其中scope的取值有3个：both（同时使用memory和spfile各有的特性）、memory（只更改当前实例，不更改参数文件，写内存立即生效，但重启后将不再生效）、spfile （只更改参数文件，不更改当前实例，且需重启数据库才能生效）

-- 给员工涨工资，总裁1000，经理800，其他400
SET SERVEROUTPUT ON;
DECLARE
    -- 定义光标代表给哪些员工涨工资
    -- alter table "SCOTT"."EMP" rename column "EMPJOB" to empjob;
    CURSOR cemp IS SELECT empno, empjob FROM emp;
    pempno emp.empno%type;
    pjob emp.empjob%type;
BEGIN
    -- 回滚上一步更新无效的操作
    -- ROLLBACK;
    -- 打开光标
    OPEN cemp;
    LOOP
    FETCH cemp into pempno, pjob;
    EXIT WHEN cemp%notfound;
    -- 判断员工的职位
    if pjob = 'PRESIDENT' THEN UPDATE emp SET sal = sal + 1000 WHERE empno = pempno;
    elsif pjob = 'MANAGER' THEN UPDATE emp SET sal = sal + 800 WHERE empno = pempno;
    else UPDATE emp SET sal = sal + 400 WHERE empno = pempno;
    END IF;
    END LOOP;
    
    -- 关闭光标
    CLOSE cemp;
    
    -- 对于Oracle，默认的事务隔离级别是 read committed（读提交）
    -- 遵循事务的ACID特性
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE('完成涨工资！');
END;
/

-- 光标参数的测试
SET SERVEROUTPUT ON;
DECLARE
    CURSOR cemp IS SELECT empno, empjob FROM emp;
    pempno emp.empno%type;
    pjob emp.empjob%type;
BEGIN   
    -- 打开光标 
    OPEN cemp;
    -- 判断光标的状态
    if cemp%isopen THEN dbms_output.put_line('光标已经打开！');
    else dbms_output.put_line('光标没有打开！');
    END IF;
    -- 执行循环体
    LOOP
    -- 每次取出一条记录
    FETCH cemp into pempno, pjob;
    EXIT WHEN cemp%notfound;
    -- 打印当前光标所在的行数
    dbms_output.put_line('rowcount:' || cemp%rowcount);
    END LOOP;
    
    -- 关闭光标
    CLOSE cemp;
END;
/

-- 切换到管理员sys或者system，使用show parameter 模糊查询光标cursor的所有参数，相当于在指定参数左右两边各添加一个%
SQL> SHOW PARAMETER abcd;
SQL> SHOW PARAMETER cursor;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
cursor_space_for_time                boolean     FALSE
open_cursors                         integer     300
session_cached_cursors               integer     50

-- 查询某个部门中员工的姓名
SET SERVEROUTPUT ON;
DECLARE 
    -- 定义带参数的光标
    CURSOR cemp(dno number) IS SELECT ename FROM emp WHERE deptno=dno;
    pename emp.ename%type;
BEGIN
    -- 打开光标（要传参）
    OPEN cemp(20);
    LOOP
    -- 取出每个员工的姓名
    FETCH cemp INTO pename;
    EXIT WHEN cemp%notfound;
    DBMS_OUTPUT.PUT_LINE(pename);
    
    END LOOP;
    -- 关闭光标
    CLOSE cemp;
END;
/

Oracle学习笔记（三）

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读