分类:
Oracle
```csharp
Oracle 基础知识了解
Oracle 最早员工主管 Kinq
默认账户
Sys 123456 as sysdba
System 123456 dba(数据库管理员)
Scott tiger（养猫的名字）
（Oracle 公司的第一个员工）
Oracle 公司的三个认证
Oca:oracle 初级认证
Ocp:Oracle企业级认证，终极认证
Ocm : oracle 大师级认证（Oracle 总部）
Oracle 授权命令
grant 权限/角色 to 用户名
Oracle 撤销命令
revoke 权限 from 用户名
授予权限Create session 和角色DBA 给数据库管理员
grant create session ,dba to teacher;
teacher -- 是数据库管理员
Oracle 常用命令
查询当前登录的用户
Show user
登录命令/切换账户的命令
Conn 用户名/密码
锁定账户命令
alter user 用户名 account lock
解锁账户命令
alert user 用户名 account unlock
修改密码
alert user 用户名 identified 密码
回退事务
Rollback；
授权命令
Grant 权限 to 用户名
scott 查询权限
给Scott查询test 表的权限
grant select on test to scott
给Scott添加test 表的权限
grant insert on test to scott
给Scott删除test 表的权限
grant delete on test to scott
取消Scott查询test 表的权限
revoke select on test to scott
总结：查询用grant ，取消用revoke。
Oracle权限传递
权限传递前提：
1.自己具有该权限。
2.权限配置的权限
with admin option(系统权限)
with grant option(对象权限)
权限的回收
如果对象权限会取消，如果是系统权限不会连带取消
查询角色拥有的各种权限
-- 查询所有的角色
select * from dba.roles;
-- 查询某个角色的系统权限
select * from dba_sys_privs where grantee='DBA';
查询某个角色的对象权限
select * from dba_tab_privs where grantee='DBA';
查询某个角色的角色权限
select * from dba_role_privs where grantee='DBA';
select * from dba_role_pr
给Scott查询test 表的权限
grant select on test to scott
grant select on test to scott
开启控制器输出
set serveroutput on;
序列的应用
创建序列
create sequence seq
start with 2 --从2开始
increment by 2 -- 自增 2
查询序列
select seq.nextval from dual;
修改序列的最大值
alter sequence seq
maxvalue 50 -- 最大值为50
让序列循环起来
alter sequence seq
maxvalue 50 -- 最大值为50
cycle -- 循环
让序列无限增长
alter sequence seq
increment by 4 -- 自增 2
nomaxvalue -- 最大值为50
nocycle -- 循环
Oracle 的if判断
declare i int:=1;
begin
if i=1 then
dbms_output.put_line('小明') ;
end if;
end;
Oracle的 loop循环
declare i int:=1;
begin
loop
dbms_output.put_line(i) ;
i:=i+1;
exit when i>=20;
end loop;
end;
Oracle的 while循环
declare i int:=1;
begin
while i<20
loop
dbms_output.put_line(i) ;
i:=i+1;
end loop;
end;
Oracle的 for循环
declare i int:=1;
begin
for i in 1..20
loop
dbms_output.put_line(i) ;
end loop;
end;
Oracle 的 switch 判断
declare i int:=1;
begin
case i
when 10 then
dbms_output.put_line(10) ;
when 20 then
dbms_output.put_line(20) ;
when 30 then
dbms_output.put_line(30) ;
else
dbms_output.put_line('数字不存在') ;
end case;
end;
建立一个不带参数的时间存储过程
create or replace procedure time_out
is
begin
dbms_output.put_line(systimestamp);
end;
建立一个带有输出参数的存储过程
create or replace procedure add_employee
(EMPNO NUMBER(4),ENAME VARCHAR2(10) ,JOB VARCHAR2(9),MGR NUMBER(4),
,HIREDATE DATE , SAL NUMBER(7,2) ,COMM NUMBER(7,2),DEPTNO NUMBER(2))
is
begin
insert into scott.emp values (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO )
end ;
如何创建带out参数的存储过程
create or replace procedure testOut(values1 number ,values2 out number)
is
begin
values2:=values1+50;
end;
调用存储过程
declare
result number;
begin
testOut(10,result);
DBMS_OUTPUT.put_line(result);
end;
查看表结构
desc scott.emp;
实例：
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO NUMBER(4)
ENAME VARCHAR2(10) Y
JOB VARCHAR2(9) Y
MGR NUMBER(4) Y
HIREDATE DATE Y
SAL NUMBER(7,2) Y
COMM NUMBER(7,2) Y
DEPTNO NUMBER(2) Y
创建函数
create or replace function get_user
return varchar2
is
v_user varchar2(100);
begin
select ename into v_user from scott.emp where empno='7369';
return v_user;
end;
使用变量接收函数返回值。
declare
username varchar2(50);
begin
username:=get_user;
dbms_output.put_line(username);
end;
调用方法：
1. SQL 语句中直接调用函数。
SQL> select get_user from dual;
GET_USER
--------------------------------------------------------------------------------
SMITH
SQL>
创建索引
create unique index indeEMPNO on scott.emp(EMPNO);
创建位图索引
create bitmap index indeJob on scott.emp(job);
修改索引
1.重建索引。
alter index indeJob rebuild;
rebuild -- 重建
2. 合并索引
alter index indeJob coalesce;
coalesce -- 合并
删除视图
drop index indeJob ;
创建视图
create view view_employee as
select * from scott.emp;
删除视图
drop view view_employee;
创建私有同义词
create synonym myemp for scott.emp;
创建公有同义词
create public synonym public_myemp for scott.emp;
删除同义词
drop synonym myemp;
函数的使用
1.ABS（n）.该函数用于返回数字n的绝对值。
select 'ABS:'|| ABS(-12.3) from dual;
结果：
'ABS:'||ABS(-12.3)
------------------
ABS:12.3
2.CEIL（n）。返回大于数字n的最小整数。
select 'CEIL:'|| CEIL(-12.3) from dual;
select 'CEIL:'|| CEIL(12.3) from dual;
select 'CEIL:'|| CEIL(12) from dual;
返回结果：
'CEIL:'||CEIL(-12.3)
--------------------
CEIL:-12
'CEIL:'||CEIL(12.3)
-------------------
CEIL:13
'CEIL:'||CEIL(12)
-----------------
CEIL:12
3.FLOOR（n） .返回小于等于数字n 的最大整数。
select 'FLOOR:'|| FLOOR(-12.3) from dual;
select 'FLOOR:'|| FLOOR(12.3) from dual;
select 'FLOOR:'|| FLOOR(12) from dual;
返回结果：
'FLOOR:'||FLOOR(-12.3)
----------------------
FLOOR:-13
'FLOOR:'||FLOOR(12.3)
---------------------
FLOOR:12
'FLOOR:'||FLOOR(12)
-------------------
FLOOR:12
4.ROUND(n,[m]).四舍五入。如果省略吗，则四舍五入至整数，如果m是负数，则四舍五入到小数点前m位；如果m是正数，则四舍五入至小数点m位。
select 'ROUND:'|| ROUND(-12.32344，2) from dual;
返回结果：
'ROUND:'||ROUND(-12.32344，2)
----------------------------
ROUND:-12.32
5.TRUNC（n,[m]）.截取数字。如果省略吗，则截取至整数，如果m是负数，则截取到小数点前m位；如果m是正数，则截取至小数点m位。
select 'TRUNC:'|| TRUNC(-12.32344，2) from dual;
结果：
'TRUNC:'||TRUNC(-12.32344，2)
----------------------------
TRUNC:-12.32
字符函数
LOWER(char) 。将字符串转化为小写格式。
UPPER（char）。 将字符串转化为大写格式。
LENGTH（char）。返回字符串的长度。
LTRIM（char[,set])。去掉字符串char左端包含的set中的任何字符。set默认为空格。
使用LTRIM（）函数。
select 'LTRIM:'|| LTRIM('this is') from dual;
select 'LTRIM:'|| LTRIM('this is','th') from dual;
结果：
'LTRIM:'||LTRIM('THISIS')
-------------------------
LTRIM:this is
'LTRIM:'||LTRIM('THISIS','TH')
------------------------------
LTRIM:is is
使用RTRIM（）函数。
select 'RTRIM:'|| RTRIM('this is') from dual;
select 'RTRIM:'|| RTRIM('this is','th') from dual;
结果：
'RTRIM:'||RTRIM('THISIS')
-------------------------
RTRIM:this is
'RTRIM:'||RTRIM('THISIS','TH')
------------------------------
RTRIM:this is
使用REPLACE（）函数。
select 'REPLACE:'|| REPLACE('this a apple','this','that') from dual;
返回结果：
'REPLACE:'||REPLACE('THISAAPPL
------------------------------
REPLACE:that a apple
转化函数
1.使用TO_NUMBER()函数。
select 'TO_NUMBER:'|| TO_NUMBER('100.33','99999D99') from dual;
返回结果：
'TO_NUMBER:'||TO_NUMBER('100.3
--------------------------------------------------
TO_NUMBER:100.33
2.使用TO_CHAR（）函数。
select 'TO_CHAR:'|| TO_CHAR(sysdate,'yyyy-MM-dd') from dual;
返回结果：
'TO_CHAR:'||TO_CHAR(SYSDATE,'Y
------------------------------
TO_CHAR:2019-12-27
3.使用TO_DATE（）函数
select 'TO_DATE:'|| TO_DATE('05-03-10','mm-dd-yy') from dual;
返回结果：
'TO_DATE:'||TO_DATE('05-03-10'
------------------------------
TO_DATE:03-5月 -10
4.使用NVL（）函数。NVL(expr1,expr2),如果expr1是NULL，则返回expr2;否则返回expr1。两者的数据类型必须要匹配。
select 'NVL:'|| NVL(comm,0) from scott.emp where empno=7369;
返回结果：
'NVI:'||NVL(COMM,0)
--------------------------------------------
NVI:0
4.使用NVL2（）函数。NVL2(expr1,expr2，expr3),如果expr1是NULL，则返回expr3;否则返回expr2。两者的数据类型可以不匹配。
select 'NVL2:'|| NVL2(comm,0,1) from scott.emp where empno=7369;
返回结果：
'NVL2:'||NVL2(COMM,0,1)
---------------------------------------------
NVL2:1
5.使用replace 函数
select 'replace'|| replace('kb0932o9312il93111','o','0') from dual
select replace((select replace('kb0932o9312il93111','o','0') from dual),'i','1') from dual
返回结果：
'REPLACE'||REPLACE('KB0932O931
------------------------------
replacekb093209312il93111
REPLACE((SELECTREPLACE('KB0932
------------------------------
kb0932093121l93111
多表查询
SQL的集合操作符。
UNION ：返回查询检索的所有不重复的行。
UNION ALL ：返回查询检索的所有行，包括重复行。
INTERSECT：返回两个查询都检索到的行
MINUS： 返回第一个检索到的行减去第二个查询检索的行所剩余的行
连接查询
使用内连接
select * from scott.dept,scott.emp where dept.deptno=emp.deptno;
select * from scott.dept a inner join scott.emp b on a.deptno=b.deptno and a.deptno=20;
使用自连接
select manager.ename from scott.emp manager,scott.emp worker where manager.empno=worker.mgr and worker.ename='smith';
使用左外连接。
select * from scott.dept a left join scott.emp b on a.deptno=b.deptno and a.deptno=20;
使用右外连接。
select * from scott.dept a right join scott.emp b on a.deptno=b.deptno and a.deptno=20;
说明一个完整的PL/SQL块
declare
v_ename varchar2(20);
begin
select ename into v_ename from scott.emp where empno=&empno;
DBMS_OUTPUT.put_line('员工姓名:' || v_ename);
exception
when no_data_found then
dbms_output.put_line('请输入正确的员工号！');
end;
输出结果：
员工姓名:SMITH
PL/SQL procedure successfully completed
计算员工的工资所得税
declare
v_ename varchar2(20);
v_sal number(6,2);
c_tax_rate constant number(3,2):=0.03;
v_tax_sal number(6,2);
begin
select ename,sal into v_ename,v_sal from
scott.emp where empno=&empno;
v_tax_sal :=v_sal * c_tax_rate;
dbms_output.put_line('员工姓名：'||v_ename);
dbms_output.put_line('员工工资：'||v_sal);
dbms_output.put_line('所得税：'||v_tax_sal);
end;
输出结果：
SQL> /
员工姓名：SMITH
员工工资：800
所得税：24
PL/SQL procedure successfully completed
使用%type属性计算员工的工资所得税
declare
v_ename scott.emp.ename%type;
v_sal number(6,2);
c_tax_rate constant number(3,2):=0.03;
v_tax_sal number(6,2);
begin
select ename,sal into v_ename,v_sal from scott.emp where empno = &empno;
v_tax_sal :=v_sal * c_tax_rate;
dbms_output.put_line('员工姓名：'||v_ename);
dbms_output.put_line('员工工资：'||v_sal);
dbms_output.put_line('所得税：'||v_tax_sal);
end;
输出结果：
员工姓名：SMITH
员工工资：800
所得税：24
PL/SQL procedure successfully completed
条件分支语句
declare
v_dep number(6,2);
v_sal number(6,2);
begin
select deptno ,sal into v_dep,v_sal from scott.emp where ename=trim('JAMES');
if v_dep=10 then
update scott.emp set sal=v_sal+200 where deptno=10;
elsif v_dep=20 then
update scott.emp set sal=v_sal+100 where deptno=20;
elsif v_dep=30 then
update scott.emp set sal=v_sal+50 where deptno=30;
else
update scott.emp set sal=v_sal+20 where deptno!=10 and deptno!=20;
end if;
end;
游标
1.显示游标
declare
cursor cursor_name
is
select 123 from dual;
begin
open cursor_name;
dbms_output.put_line('你好，很热！');
close cursor_name;
end;
输出结果：
SQL> ed
SQL> /
你好，很热！
PL/SQL procedure successfully completed
2.使用游标提取部门20名员工的姓名与工资
declare
cursor emp_cursor
is
select ename,sal from scott.emp where deptno=20;
v_ename scott.emp.ename%type;
v_sal scott.emp.sal%type;
begin
open emp_cursor;
loop
fetch emp_cursor into v_ename ,v_sal;
exit when emp_cursor%Notfound;
dbms_output.put_line(v_ename||':'||v_sal);
end loop;
close emp_cursor;
end;
运行结果如下：
SQL> set serveroutput on;
SQL> /
FORD3:3000
SMITH:800
JONES:2975
SCOTT:3000
ADAMS:1100
FORD:3000
PL/SQL procedure successfully completed
参数游标
declare
cursor emp_cursor(cno number)
is
select ename ,sal from scott.emp where deptno=cno;
v_ename scott.emp.ename%type;
v_sal scott.emp.sal%type;
begin
if not emp_cursor %isopen then
open emp_cursor(10);
end if;
loop
fetch emp_cursor into v_ename,v_sal;
exit when emp_cursor%notfound;
dbms_output.put_line(v_ename||':'||v_sal);
end loop;
close emp_cursor;
end;
输出结果：
CLARK:2450
KING:5000
MILLER:1300
PL/SQL procedure successfully completed
使用游标删除或更新数据
将工资低于2500的员工增加150元工资
declare
cursor emp_cursor
is
select ename,sal from scott.emp for update of sal;
v_ename scott.emp.ename%type;
v_oldsal scott.emp.sal%type;
begin
open emp_cursor;
loop
fetch emp_cursor into v_ename,v_oldsal;
exit when emp_cursor%notfound;
if v_oldsal<2500 then
update scott.emp set sal=sal+150 where current of emp_cursor;
end if;
end loop;
close emp_cursor;
end;
使用游标for循环显示部门编号为20的所有员工姓名
declare
cursor emp_cursor
is
select ename from scott.emp where deptno=20;
begin
for emp_record in emp_cursor loop
dbms_output.put_line('第'||emp_cursor%rowcount||'个员工'||emp_record.ename);
end loop;
end;
运行结果：
第1个员工FORD3
第2个员工SMITH
第3个员工JONES
第4个员工SCOTT
第5个员工ADAMS
第6个员工FORD
PL/SQL procedure successfully completed
在游标for循环中使用子循环显示部门编号20的所有员工姓名
begin
for emp_record in
(select ename from scott.emp where deptno=20)
loop
dbms_output.put_line(emp_record.ename);
end loop;
end;
输出结果：
SQL> ed
SQL> /
FORD3
SMITH
JONES
SCOTT
ADAMS
FORD
PL/SQL procedure successfully completed
建立before语句触发器，保证员工信息的修改只能在工作时间。
create or replace trigger tr_sec_emp
before insert or update or delete on scott.emp
declare
begin
if to_char(susdate,'dy','bls_date_language=AMERICAN') IN ('sat','sum')
then
raise_application_error(-20001,'今天是休息时间，不能改变员工信息');
end if;
end tr_sec_emp;
使用dbms_output 包输出99乘法表
begin
dbms_output.put_line('打印久久乘法表');
for i in 1..9 loop
for j in 1..i loop
dbms_ouput.put_line(i||'*'j||'='||i*j);
dbms_ouput.put_line(' ');
end loop;
dbms_output.new_line;
end loop;
end;
输出结果：
SQL> set serverout on;
SQL> /
打印久久乘法表
1*1=1
2*1=2 2*2=4
3*1=3 3*2=6 3*3=9
4*1=4 4*2=8 4*3=12 4*4=16
5*1=5 5*2=10 5*3=15 5*4=20 5*5=25
6*1=6 6*2=12 6*3=18 6*4=24 6*5=30 6*6=36
7*1=7 7*2=14 7*3=21 7*4=28 7*5=35 7*6=42 7*7=49
8*1=8 8*2=16 8*3=24 8*4=32 8*5=40 8*6=48 8*7=56 8*8=64
9*1=9 9*2=18 9*3=27 9*4=36 9*5=45 9*6=54 9*7=63 9*8=72 9*9=81
PL/SQL procedure successfully completed
```评价
