-- 请用scott用户执行
-- 查询入职时间在1-1月-1982后的员工名字和入职日期
select ename, hiredate from emp where hiredate > '1-1月-1982';
-- 查询工资大于500或者工作是MANAGER并且名字首字母为"J"的员工名字和工资
select ename, sal, job from emp where (sal > 500 or job = 'MANAGER') and ename like 'J%';
-- 查询员工所有信息并按部门编号升序,入职日期降序
select * from emp order by deptno, hiredate desc;
-- 查询员工姓名和年薪,并按年薪升序(年薪包含月工资和奖金)
select ename, sal * 12 + nvl(comm, 0) * 12 "年工资" from emp order by "年工资";
-- 查询最高工资和最低工资
select max(sal), min(sal) from emp;
-- 查询工资最高的员工姓名和工资
-- select ename, sal from emp where sal = max(sal);-- 错误
select ename, sal from emp where sal = (select max(sal) from emp);-- 使用子查询
-- 查询大于平均工资的员工所有信息
select * from emp where sal > (select avg(sal) from emp);
-- 显示每个部门的平均工资和最高工资
select avg(sal), max(sal), deptno from emp group by deptno;
-- 显示每个部门的每种岗位的最低工资、平均工资和最高工资
select min(sal), avg(sal), max(sal), deptno, job from emp group by deptno, job;
-- 显示平均工资低于2000的部门号和它的平均工资
select deptno, avg(sal) from emp group by deptno having avg(sal) < 2000;
-- ############################### 连接查询 ###################################
-- 显示雇员名,雇员工资及所在部门的名字【笛卡尔集】
select emp.ename, emp.sal, dept.dname from emp, dept where emp.deptno = dept.deptno;
-- 显示部门号为10的部门名、员工名和工资
select dept.dname, emp.ename, emp.sal from emp, dept where emp.deptno = dept.deptno and emp.deptno = 10;
-- 显示各个员工的姓名,工资,及其工资的级别
select emp.ename, emp.sal, salgrade.grade from emp, salgrade where emp.sal between salgrade.losal and salgrade.hisal;
-- 显示雇员名,雇员工资及所在部门的名字,并按部门编号排序
select emp.ename, emp.sal, dept.dname from emp, dept where emp.deptno = dept.deptno order by dept.loc;
-- 显示员工(worker)"FORD”的上级领导(boss)的姓名
select worker.ename "员工", boss.ename "领导" from emp worker, emp boss where worker.mgr = boss.empno and worker.ename = 'FORD';
-- ############################## 子查询 ###############################
-- 显示与SMITH同一部门的所有员工
select * from emp where deptno = (select deptno from emp where ename = 'SMITH');
-- 查询和部门10的工作相同的雇员的名字、岗位、工资、部门号
select ename, job, sal, deptno from emp where job in (select distinct job from emp where deptno = 10);
-- 显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
select ename, sal, deptno from emp where sal > all (select sal from emp where deptno = 30);
select ename, sal, deptno from emp where sal > (select max(sal) from emp where deptno = 30);-- 推荐
-- 显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号
select ename, sal, deptno from emp where sal > any (select sal from emp where deptno = 30);
select ename, sal, deptno from emp where sal > (select min(sal) from emp where deptno = 30);-- 推荐
-- 查询与smith的部门和岗位完全相同的所有雇员
select * from emp where (deptno, job) = (select deptno, job from emp where ename = 'SMITH');
-- 显示高于自己部门平均工资的员工信息
-- 1.查询各部门平均工资
select deptno, avg(sal) from emp group by deptno;
-- 2.将上面查询结果当做子表使用
select emp.ename, emp.sal, emp.deptno, avgsal from emp, (select deptno, avg(sal) avgsal from emp group by deptno) t1 where emp.deptno = t1.deptno and emp.sal > avgsal;
-- ################################### 分页查询 #####################################
-- 增加行号
select t1.*, rownum "行号" from (select * from emp) t1;
-- 查询前n行数据
select t1.*, rownum "行号" from (select * from emp) t1 where rownum <= 5;
-- 查询n~m行数据
select * from (select t1.*, rownum rn from (select * from emp) t1 where rownum <= 5) where rn >= 3;
-- 查询n~m行数据并指定字段
select * from (select t1.*, rownum rn from (select ename from emp) t1 where rownum <= 5) where rn >= 3;
-- 查询n~m行数据并排序
select * from (select t1.*, rownum rn from (select ename, sal from emp order by sal desc) t1 where rownum <= 10) where rn >= 3;
-- ################################### 复制表 #####################################
create table myemp1
as
select empno id, ename name, sal sal
from emp;
select * from myemp1;
drop table myemp1;
-- #################################### 复杂查询 #########################################
-- 联合查询, 取并集
select ename,sal,job from emp where sal>2500
union
select ename,sal,job from emp where job='MANAGER';
-- 取交集
select ename,sal,job from emp where sal>2500
intersect
select ename,sal,job from emp where job='MANAGER';
-- ################################### 插入日期格式 #####################################
-- 使用to_date函数插入
insert into emp values(2, '董小天2号', '滑手', '0', to_date('2018-10-3', 'yyyy-mm-dd'), null, null, null);
-- ################################### 批量操作数据 #####################################
desc emp;
create table test1(
id number,
name varchar2(20)
);
-- 复制emp表中10号部门的员工的编号和名字到test1表
insert into test1 (id, name) select emp.empno, emp.ename from emp where emp.deptno = 10;
select * from test1;
-- 希望员工董小天1号的岗位、工资、补助与smith员工一样
select * from emp;
update emp set (job, sal, comm) = (select job, sal, comm from emp where ename = 'SMITH') where ename = '董小天1号';
-- ################################### 字符函数 #####################################
-- 将所有员工的名字按小写的方式显示
select lower(ename), sal from emp;
--将所有员工的名字按大写的方式显示
select upper(ename), sal from emp;
--显示正好为4个字符的员工的姓名。
select ename from emp where length(ename) = 4;
--显示所有员工姓名的前三个字符。
select substr(ename, 1 , 3) from emp;
--以首字母大写的方式显示所有员工的姓名
select upper(substr(ename, 1, 1)) || lower(substr(ename, 2, length(ename) - 1)) from emp;
--以首字母小写的方式显示所有员工的姓名。
select lower(substr(ename, 1, 1)) || upper(substr(ename, 2, length(ename) - 1)) from emp;
-- 拼接字符串
select ename || 'eeeee' from emp;
-- 将员工姓名中的A替换为"啊"
select replace(ename, 'A', '啊') from emp;
-- 返回子串在主串中出现的位置
select instr('abcde', 'bc') from dual; -- 2
-- ################################### 数学函数 #####################################
--显示在一个月为30天的情况所有员工的日薪金,并忽略余数
select ename, trunc(sal / 30) from emp;
-- ################################### 日期函数 #####################################
--查找已经入职400个月多的员工
select * from emp where add_months(hiredate, 400) < sysdate;
--显示满10年服务年限的员工的姓名和受雇日期.
select * from emp where add_months(hiredate, 10 * 12) <= sysdate;
--对于每个员工,显示其加入公司的天数。
select ename, hiredate, trunc(sysdate - hiredate) from emp;
--找出各月倒数第3天受雇的所有员工。
select ename, hiredate from emp where (last_day(hiredate) - hiredate) = 2;
-- 查询指定日期的月的最后一天日期
select last_day(sysdate) from dual;
-- 格式化日期:年月日时分秒
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;
-- 格式化薪水
select ename, to_char(sal, 'L99,999.99') from emp;
-- 格式化数字
select to_char(123.56, 'L99,999.99') from dual;
--显示1980年入职的所有员工
select ename, hiredate from emp where to_char(hiredate, 'yyyy') = 1980;
--显示所有12月份入职的员工
select * from emp where to_char(hiredate, 'mm') = 12;
-- ################################### 备份数据库 #####################################
-- doc下进入Oracle的bin目录下使用以下命令
-- 导出表
exp userid=scott/tiger@orcl tables=(emp, dept) file=c:\test\导出表.dmp
-- 导出其他用户表
exp userid=system/manager@orcl tables=(scott.emp) file=c:\test\system导出表.dmp
-- 导出表结构
exp userid=scott/tiger@orcl tables=(emp) file=c:\test\导出表结构.dmp rows=n
-- 直接方式导出表结构(效率高,需要保证Oracle字符集和客户端字符集一致)
exp userid=scott/tiger@orcl tables=(emp) file=c:\test\直接方式导出表结构.dmp direct=y
-- 导出方案
exp scott/tiger@orcl owner=scott file=c:\test\导出方案.dmp
-- 导出其他用户方案
exp system/manager@orcl owner=(system,scott) file=c:\test\导出其他用户方案.dmp
-- 导出数据库
exp system/manager@orcl full=y inctype=complete file=c:\test\导出数据库.dmp
-- 导入表
imp scott/tiger@orcl tables=(emp) file=c:\test\导出表.dmp ignore=y
-- 给其他用户导入表??
imp system/manager@orcl tables=(scott.emp) file=c:\test\system导出表.dmp touser=scott
-- 导入表结构
imp scott/tiger@orcl tables=(emp) file=c:\test\导出表.dmp rows=n
-- 导入方案
imp scott/tiger file=c:\test\导出方案.dmp
-- 给其他用户导入方案
imp system/manager file=c:\test\导出方案.dmp fromuser=scott touser=scott
-- 导入数据库
imp system/manager full=y file=c:\test\导出数据库.dmp
-- ################################### 其他命令 #####################################
-- 显示当前用户的表
select table_name from user_tables;
-- 显示当前用户可访问的表
select table_name from all_tables;
-- 显示数据库中所有表
select table_name from dba_tables;
-- 查询当前数据库全称
select * from global_name;
-- 使用自定义表空间创建表
create table mytable(
id number,
name varchar2(20)
) tablespace mytablespace;
select * from mytable;
drop table mytable;
insert into mytable values(1, '董小天一号');
insert into mytable values(2, '董小天二号');
-- 查询指定表空间下所有表
select * from all_tables where tablespace_name='MYTABLESPACE';
-- 查询指定表的表空间
select tablespace_name, table_name from user_tables where table_name='MYTABLE';
-- 装满表空间
insert into mytable select * from mytable;
-- ############################### 索引 ###############################
-- 创建单列索引
create index id_idx on test1(id);
-- 创建复合索引
create index test_idx on test1(id, name);
create index test_idx2 on test1(name, id);
-- 查询指定表上的所有索引
select index_name, index_type from user_indexes where table_name='TEST1';
-- 查询指定索引对应字段
select table_name, column_name from user_ind_columns where index_name='TEST_IDX';
-- ################################### 约束 #####################################
-- 查询指定表的约束信息
select constraint_name, constraint_type, status, validated from user_constraints where table_name='EMP';
-- 查询指定约束所对应的列
select column_name, position from user_cons_columns where constraint_name='PK_EMP';
-- 使用system创建的同义词进行查询
select * from scott_emp_synonym;
-- 创建同义词
create public synonym emp_synonym for emp;
-- ###################### pl/sql编程(SQL*Plus中无法执行时在末尾加"\") ######################
-- 打开输出选项
set serveroutput on;
-- 输出Hello Word!
begin
dbms_output.put_line('Hello Word!');
end;
-- pl/sql编程的基本示例:
-- 定义变量
declare
v_ename varchar2(20);
v_sal number;
-- 执行部分
begin
-- 执行语句给变量赋值
select ename, sal into v_ename, v_sal from emp where empno=&no;
-- 在控制台打印变量
dbms_output.put_line('雇员名:' || v_ename || '薪水:' || v_sal);
-- 例外处理
exception
when no_data_found then
dnms_output.put_line('未找该员工');
end;
-- ############# 存储过程 ###############
-- 创建存储过程(or replace: 存在时替换)
create or replace procedure insert_pro1 is
begin
insert into test1 values(1, '董小天');
end;
-- 调用存储过程
exec insert_pro1(参数);
select * from test1;
show error;
-- 创建Java测试用的存储过程,实现存入test1表指定数据
create procedure insert_test1_pro(v_id number, v_name varchar2) is
begin
insert into test1 values(v_id, v_name);
end;
select * from test1;
-- ############# 函数 ###############
-- 创建函数(根据名字返回年薪)
create function return_yearSal_by_name_fun(name varchar)
return number is
yearSal number(7, 2);
begin
select sal*12 into yearSal from emp where ename = name;
return yearSal;
end;
-- SQL*Plus中调用函数
var income number;
call return_yearSal_by_name_fun('SCOTT') into:income;
print income;
-- ############# 包 ###############
-- 创建包
create package my_package is
procedure insert_test1_pro(v_id number, v_name varchar2);-- 存储过程声明
function return_yearSal_by_name_fun(name varchar) return number;-- 函数声明
end;
-- 创建包体
create or replace package body my_package is
-- 储存过程实现
procedure insert_test1_pro(v_id number, v_name varchar2) is
begin
insert into test1 values(v_id, v_name);
end;
-- 函数实现
function return_yearSal_by_name_fun(name varchar)
return number is
yearSal number(7, 2);
begin
select sal*12 into yearSal from emp where ename = name;
return yearSal;
end;
end;
-- 调用包
call my_package.insert_test1_pro(3, '使用包插入');
select my_package.return_yearsal_by_name_fun('SCOTT') from dual;
select * from test1;
-- 根据编号计算员工应教税额
declare
c_tax_rate number(3, 2) := 0.03;-- 税率0.03
v_ename emp.ename%type;-- 员工名(%type: 和该字段类型相同)
v_sal emp.sal%type;-- 薪水
v_tax_sal number(7, 2);-- 税额
begin
select ename, sal into v_ename, v_sal from emp where empno = 7654;
v_tax_sal := v_sal * c_tax_rate;-- 计算税额
dbms_output.put_line('姓名:' || v_ename || ', 薪水:' || v_sal || ', 应交税额:' || v_tax_sal);
end;
-- 使用pl/sql记录类型(类似结构体)
declare
-- type 记录变量 is record (记录成员)
type emp_record_type is record(name emp.ename%type, salary emp.sal%type, title emp.job%type);
emp_record emp_record_type;
begin
select ename, sal, job into emp_record from emp where empno = 7654;
-- 记录变量.记录成员
dbms_output.put_line('姓名:' || emp_record.name || ', 薪水:' || emp_record.salary || ', 工作:' || emp_record.title);
end;
-- 使用pl/sql表类型(类似数组)
declare
-- type 表类型_type is table of 字段类型 index by binary_integer;
type sp_table_type is table of emp.ename%type index by binary_integer;
sp_table sp_table_type;
begin
select ename into sp_table(-1) from emp where empno = 7654;
-- 记录变量.记录成员
dbms_output.put_line('姓名:' || sp_table(-1));
end;
-- 请使用p1/sg1编写一个块,可以输入部门号,并显示该部门所有员工姓名和工资
declare
-- 定义游标
type sp_emp_cursor is ref cursor;
test_cursor sp_emp_cursor;
-- 定义接受结果的变量
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
-- 使游标指向指定结果集
open test_cursor for select ename, sal from emp;
-- 循环取出结果
loop
fetch test_cursor into v_ename, v_sal;
-- 判断游标是否为空
exit when test_cursor%notfound;
dbms_output.put_line('姓名:' || v_ename ||', 薪水: ' || v_sal);
end loop;
-- 关闭游标
close test_cursor;
end;
-- 编写一个存储过程,可以输入一个雇员名,如果该雇员的工资低于2000,就给该雇员工资增加10%
create or replace procedure addSal_pro(spName varchar2) is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where ename=spName;
if v_sal < 2000 then
update emp set sal = sal * 1.1 where ename = spName;
end if;
end;
exec addSal_pro('MARTIN');
select * from emp;
-- 编写一个过程,可以输入一个雇员名,如果该雇员的补助不是0就在原来的基础上增加100;如果补助为0就把补助设为200;
create or replace procedure addComm_pro(spName varchar2) is
v_comm emp.comm%type;
begin
select comm into v_comm from emp where ename=spName;
if v_comm <> 0 then
update emp set comm = comm + 100 where ename = spName;
else
update emp set comm = 200 where ename = spName;
end if;
end;
exec addComm_pro('MARTIN');
select * from emp;
-- 编写一个过程,可以输入一个雇员编号,
--如果该雇员的职位是PRESIDENT 就给他的工资增加1000,
--如果该雇员的职位是MANAGER 就给他的工资增加500,
--其它职位的雇员工资增加200.
create or replace procedure addSal2_pro(spNo number) is
v_job emp.job%type;
begin
select job into v_job from emp where empno=spNo;
if v_job = 'PRESIDENT' then
update emp set sal = sal + 1000 where empno=spNo;
elsif v_job = 'MANAGER' then
update emp set sal = sal + 500 where empno=spNo;
else
update emp set sal = sal + 200 where empno=spNo;
end if;
end;
exec addSal2_pro(7839);
select * from emp;
-- (loop循环)编写一个过程,可输入用户名,并循环添加10个用户到test1表中,用户编号从10开始增加。
create or replace procedure saveLoop_pro(spName varchar2) is
-- 定义循环变量
v_num number := 10;
begin
loop
insert into test1 values(v_num, spName);
-- 判断是否退出
exit when v_num = 20;
-- 自增循环变量
v_num := v_num + 1;
end loop;
end;
exec saveLoop_pro('loopSave');
select * from test1;
-- (while循环)编写一个过程,可输入用户名,并循环添加10个用户到test1表中,用户编号从21开始增加。
create or replace procedure saveWhile_pro(spName varchar2) is
-- 定义循环变量
v_num number := 21;
begin
while v_num < 30 loop
insert into test1 values(v_num, spName);
-- 自增循环变量
v_num := v_num + 1;
end loop;
end;
exec saveLoop_pro('while');
select * from test1;
-- (for循环)编写一个过程,可输入用户名,并循环添加10个用户到test1表中,用户编号从31开始增加。
create or replace procedure saveFor_pro(spName varchar2) is
begin
for i in reverse 31..40 loop
insert into test1 values(i, spName);
end loop;
end;
exec saveFor_pro('for');
select * from test1;
declare
i int := 1;
begin
loop
dbms_output.put_line('输出i=' || i);
if i=10 then
goto end_loop;
end if;
i := i+1;
end loop;
dbms_output.put_line('循环结束');
<<end_loop>>
dbms_output.put_line('循环结束2');
end;
-- ########################## 分页 ############################
-- #分页基础:
create table book(
bookId number,
bookName varchar2(50),
publishHouse varchar2(50)
);
-- 保存图书
create or replace procedure saveBook_pro
(spBookId in number, spBookName in varchar2, sppublishHouse in varchar2) is
-- in: 表示该参数为输入参数
-- out: 表示该参数为输出参数
begin
insert into book values(spBookId, spBookName, sppublishHouse);
end;
-- exec saveBook_pro(); -- 在java中调用
select * from book;
-- 根据id查询图书名(java中调用)
create or replace procedure selectBook_pro
(spBookId in number, spBookName out varchar2, spPublishHouse out varchar2) is
begin
select bookName, publishHouse into spBookName, spPublishHouse from book where bookId = spBookId;
end;
-- 返回结果集的存储过程实现 根据部门号查询客户所有信息
-- 1.创建包并定义游标
create or replace package testPackage as
-- 定义游标
type test_cursor is ref cursor;
end testPackage;
-- 2.创建存储过程
create or replace procedure selectEmpByCursor_pro
(spNo in number, p_cursor out testPackage.test_cursor) is
begin
-- 使游标指向指定结果集
open p_cursor for select * from emp where deptno=spNo;
end;
select * from emp;
-- #正式编写分页
-- 创建包并定义游标
create or replace package testPackage as
-- 定义游标
type test_cursor is ref cursor;
end testPackage;
-- 输入表名、每页显示记录数、当前页。返回总记录数,总页数,和返回的结果集
create or replace procedure paging_pro(
tableName in varchar2,-- 表名
pageSize in number,-- 每页记录数
pageNow in number,-- 当前页
rows out number,-- 返回的总记录数
pageCount out number,-- 总页数
p_cursor out testPackage.test_cursor-- 返回的记录集
) is
v_sql varchar2(1000);-- 定义sql语句
v_begin number := (pageNow - 1) * pageSize + 1;
v_end number := pageNow * pageSize;
begin
v_sql := 'select * from (select t1.*, rownum rn from (select * from ' || tableName || ') t1 where rownum <= ' || v_end || ') where rn >= ' || v_begin;
-- 使游标指向指定结果集
open p_cursor for v_sql;
-- 计算rows
v_sql := 'select count(*) from ' || tableName;
execute immediate v_sql into rows;
-- 计算pageCount
if mod(rows, pageSize) = 0 then
pageCount := rows / pageSize;
else
pageCount := rows / pageSize + 1;
end if;
-- 关闭游标
-- close p_cursor;
end;
-- ##################### 例外处理 ###################
-- ####### 预定义例外 ########
-- case_not_found: 编写case语句时,在when子句中没有包含必须的条件分支
create or replace procedure sp-pro6(spno number) is
v_sal emp.sa1%type;
begin
select sal into v_sal from emp where empno=spno;
case
when v_sal<1000 then
update emp set sal=sal+100 where empno=spno;
when v_sal<2000 then
update emp set sal=sal+200 where empno=spno;
end case;
exception
when case_not_found then
dbms_output.put_line('case语句没有与' || v_sal || '相匹配的条件');
end;
-- cursor_already_open: 当重新打开已经打开的游标时,会隐含的触发例外
declare
cursor emp_cursor is select ename, sal from emp;
begin
open emp_cursor;
for emp_recordl in emp_cursor 1oop
dbms_output.put_line(emp_record1.ename);
end 1oop;
exception
when cursor_already_open then
dbms_output.put_line('游标已经打开');
end;
-- dup_val_on_index: 在唯一索引所对应的列上插入重复的值时,会隐含的触发
begin
insert into dept values(10, '公关部', '北京');
exception
when dup_val_on_index then
dbms_output.put_line('在deptno列上不能出现重复值');
end;
-- invaild_cursor: 当试图在不合法的游标上执行操作时,会触发该例外
-- 例如:试图从没有打开的游标提取数据,或是关闭没有打开的游标。则会触发该例外
declare
cursor emp_cursor is select ename, sal from emp;
emp_record emp_cursor%rowtype;
begin
-- open emp_cursor;--打开游标
fetch emp_cursor into emp_record;
dbms_output.put_line(emp_record.ename);
close emp_cursor;
exception
when invalid_cursor then
dbms_output.put_line('请检测游标是否打开');
end;
-- invalid_number: 当输入的数据有误时,会触发该例外
-- 比如:数字100写成了l00就会触发该例外
begin
update emp set sal=sal+'loo';
exception
when invalid_ number then
dbms_output.put_line('输入的数字不正确');
end;
-- no_data_found: 当执行select into没有返回行,就会触发该例外
declare
v_sal emp.sa1%type;
begin
select sal into v_sal from emp where ename='&name';
exception
when no_data_found then
dbms_output.put_line('不存在该员工');
end;
-- too_many_rows: 当执行select into 语句时,如果返回超过了一行,则会触发该例外。
declare
v_ename emp.ename%type;
begin
select ename into v_ename from emp;
exception
when too_many_rows then
dbms_output.put_line('返回了多行');
end;
-- zero_divide: 当执行2/0语句时,则会触发该例外。
-- value_error: 当在执行赋值操作时,如果变量的长度不足以容纳实际数据,则就会触发该例外
declare
v_ename varchar2(5);
begin
select ename into v_ename from emp where empno=&nol;
dbms_output.put_line(v_ename);
exception
when value_error then
dbms_output.put_line('变量尺寸不足');
end;
-- ############# 其他预定义例外 ############
①login_denidemls
当用户非法登陆时,会触发该例外
② not_logged_on
如果用户没有登陆就执行dml操作,就会触发该例外
③storage_error
如果超出了内存空间或是内存被损坏,就触发该例外
④ timeout_on_resource
如果oracle在等待资源时,出现了超时就触发该例外
-- ############# 自定义例外 ############
-- 请编写一个p1/sq1块,接收一个雇员的编号,并给该雇员工资增加1000元,如果该雇员不存在,请提示。
create or replace procedure ex_test(spNo number) is
--定义一个例外
myex exception;
begin
--更新用户sal
update emp set sal=sal+1000 where empno=spNo;
if sql%notfound then -- 如果没有update
raise myex; -- 触发myex自定义异常
end if;
exception
when myex then
doms_output.put_1ine('没有更新任何用户');
end;
Oracle命令使用演示案例
最新推荐文章于 2023-07-15 22:12:46 发布