Oracle学习过程_orcale学习过程-CSDN博客

本文链接：https://blog.csdn.net/weixin_45932157/article/details/103771545
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