- 基本查询语句
-- 查询语句加上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 := # -- 取值
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;
/
网友评论