[大数据]Oracle、PLSQL语句

Oracle

一、常用命令

sqlplus

sys as sysdba

conn scott/tiger

alter user scott identified by 密码

show user

二、sql语句

DDL 自动提交事务

DML 不会自动提交事务

DCL 自动提交事务

TCL 终结事务

1、创建用户，只有管理员可用（DDL）

• create user 用户名 identified by 密码
• alter user 用户名 idetified by 密码

2、新建用户没有权限，要分配权限（DCL）

• grant connect,resource to 用户名

3、锁定/解锁用户，只有管理员可用（DDL）

• alter user 用户名 account lock
• alter user 用户名 account unlock

三、体系结构

四、深度理解Oracle启动和关闭

startup nomount

startup mount

startup open

shutdown normal

任何新的连接都将再不允许连接到数据库。在数据库关闭之前，Oracle将等待目前连接的所有用户都从数据库中退出后才开始关闭数据库。

shutdown

系统不等待连接到数据库的所有用户退出系统，强行回滚当前所有的活动事务，然后断开所有的连接用户。

shutdown immediate

在所有活动的事务完成后，数据库将和SHUTDOWN IMMEDIATE同样的方式关闭数据库。

shutdown abort

PLSQL

select * from emp;
select * from dept;

-- DDL  alter  create  drop  (会自动提交事务)
-- DML  insert delete update select  （不会自动提交事务）
-- DCL  grant  revoke
-- TCL  commit  rollback （终结事务）


update dept set dname = lower(dname) where deptno = 20;
commit;

select name from v$datafile;

-- 单行注释
/*
   多行注释
*/
-- 基本操作
-- 1- 查看当前用户命令：
   show user 
--2- 创建用户： 只有管理员可用
  create  user  用户名  identified  by  密码
  alter  user  用户名  identified by 新密码
  
--注：新建的用户缺少权限，需要分配使用权限才能连接
  grant  connect,resource  to  用户名  -- (连接和数据资源使用权限)
  
-- 3- 锁定/解锁用户： 只有管理员可用
  alter  user  用户名  account  lock;
  alter  user  用户名  account  unlock;   


-- 注意：语句块输出需要打开输出标记： set serveroutput on;

/*
匿名语句块
*/
declare
  -- 声明部分（定义变量、类型、游标等）
  -- 声明变量的格式： 变量名  类型(精度)[:= 初始值]
  v_var number(5) := 10;
  v_name varchar2(20) := 'abc';
begin
  -- 使用变量
  -- 注意  = 判断是否相等；    := 赋值号
  --  || 字符串连接符号
  v_var := v_var + 10;
  dbms_output.put_line(v_var || ', ' || v_name );
  
  insert into emp(empno, deptno) values(1001, 30);
  
  -- 异常处理部分
  exception 
        when others then 
          dbms_output.put_line('出现异常' );
end;


/*
 嵌套语句块
*/
declare
  -- 声明部分（定义变量、类型、游标等）
  -- 声明变量的格式： 变量名  类型(精度)[:= 初始值]
  -- 变量的声明周期：变量declare声明之后紧跟的 begin和 end 之间
  v_var number(5) := 10;
  v_name varchar2(20) := 'abc';
begin
  -- 嵌套语句块
  declare
     v_var2 number(5) := 11;
  begin
    dbms_output.put_line('inner block: ' || (v_var2 + v_var));
    
    exception 
    when others then
         dbms_output.put_line('出现异常');
  end;
  
  --dbms_output.put_line('outer block: ' || (v_var2 + v_var));
      
  dbms_output.put_line('main end ...');
  
end;

/*
IF 语句
  
   if 条件 then 
     条件满足时的语句块
     [elsif 条件  then 条件满足时的语句块]
     [else 条件不满足时的语句块]
   end if
  */
  
  declare
   v_score number := &score;
  begin
    if v_score < 60 then
      dbms_output.put_line('不及格');
    elsif v_score < 75 then
      dbms_output.put_line('良好');
    elsif v_score < 90 then
      dbms_output.put_line('优秀');
    else
      dbms_output.put_line('很秀');
    end if;
  end;

/*
  CASE 多条件语句
    
  */
  -- 不等值 case 语句
  declare
       v_score number := &score;
  begin
    case 
      when v_score < 60  then 
        dbms_output.put_line('不及格');
       when v_score < 75 then
        dbms_output.put_line('良好');
      when v_score < 90 then
           dbms_output.put_line('优秀');
      else
           dbms_output.put_line('很秀');
    end case;
  end;
   
  -- 等值 case 语句 
    declare
       v_menu number := &menu;
  begin
    case v_menu
      when 1 then 
        dbms_output.put_line('选择的是：1');
       when 2 then
        dbms_output.put_line('选择的是：2');
      when 3 then
           dbms_output.put_line('选择的是：3');
      else
           dbms_output.put_line('选择的是：其他');
    end case;
  end;

-- 循环
   -- 无条件Loop循环
   declare
      v_i number := 1;
      v_sum number := 0;
   begin
     loop
       if v_i > 100 then
         -- 跳出循环
         exit;
       end if;
       -- 注意：没有 += 、 ++ 等符号
       v_sum := v_sum + v_i;
       v_i := v_i + 1;
       
     end loop;
     
     dbms_output.put_line('sum=' || v_sum);
   end;
   
   -- 无条件Loop循环
   declare
      v_i number := 1;
      v_sum number := 0;
   begin
     loop
       -- 带条件的跳出循环
       exit when v_i > 10;
       
       -- 注意：没有 += 、 ++ 等符号
       v_sum := v_sum + v_i;
       v_i := v_i + 1;
       
     end loop;
     
     dbms_output.put_line('sum=' || v_sum);
   end;

-- 数值for循环
declare
   v_sum number := 0;
begin
  for v_i in 1..100 loop
    v_sum := v_sum + v_i;
  end loop;
  dbms_output.put_line('for sum=' || v_sum);
end;

--  in reverse 可以翻转数值的循环
begin
  for v_i in reverse 1..10 loop
    dbms_output.put_line('i =' || v_i);
  end loop;
  
end;

-- 条件循环： while
declare
   v_sum number := 0;
   v_i number:=1;
begin
  while v_i <= 100 loop
    v_sum := v_sum + v_i;
    v_i := v_i + 1;
  end loop;
  dbms_output.put_line('while sum=' || v_sum);
end;
                   
-- 九九乘法表    
declare
  v_i number(3); -- 外层循环变量
  v_j number(3); -- 内层循环变量
begin
  for v_i in 1..9 -- 开始外层循环
  loop
      for v_j in 1 .. v_i -- 开始内层循环
      loop
          dbms_output.put(v_i || '*' || v_j || '=' || v_i * v_j || ' ');
      end loop;
      dbms_output.put_line(''); -- 换行
  end loop;
end;
/*
   预设的异常：
      no_data_found ： 没找到数据
      CASE_NOT_FOUND： CASE语句中没有任何WHEN子句满足条件，并且没有编写ELSE子句。
      ...
*/

-- 异常处理
declare
   v_loc varchar2(20);
   v_dname varchar2(20);
   -- 自定义异常名
   no_parent_item exception;
   --捆绑名称到 -2291错误编码
   PRAGMA EXCEPTION_INIT(no_parent_item,-2291);
   
begin
  insert into emp(empno, deptno) values(1002, 50);
  select dname, loc into v_dname, v_loc from dept where deptno = &dno;
  dbms_output.put_line(v_dname  || '  ' || v_loc);
  exception 
    when no_data_found then 
         dbms_output.put_line('没有此部门');
    when no_parent_item then
      dbms_output.put_line('不能添加没有的部门');
    when others then
      dbms_output.put_line('其他异常');
end;

-- 自定义异常：
declare
   v_gender varchar2(20) := '&输入性别';
   -- 自定义异常名
   no_gender exception;
   --捆绑名称到 -2291错误编码
   PRAGMA EXCEPTION_INIT(no_gender,-20000);
begin
  if v_gender != '男' and v_gender != '女' then
    -- 抛出异常
    raise_application_error(-20000, '性别只能是男或者女');
  end if;
  dbms_output.put_line('性别：' || v_gender);
  
  exception when no_gender then dbms_output.put_line('性别异常');
end;

------------ sqlerrm : 获取异常的类型，编号，文本信息

declare
   v_gender varchar2(20) := '&输入性别';

begin
  if v_gender != '男' and v_gender != '女' then
    -- 抛出异常
    raise_application_error(-20000, '性别只能是男或者女');
  end if;
  dbms_output.put_line('性别：' || v_gender);
  
  exception when others then dbms_output.put_line(sqlerrm);
end;

--- 使用嵌套语句块，模仿 try catch 后还能有正常语句
declare
   v_gender varchar2(20) := '&输入性别';

begin
  --模拟 java 中 try 代码段
  begin
     if v_gender != '男' and v_gender != '女' then
       -- 抛出异常
       raise_application_error(-20000, '性别只能是男或者女');
     end if;
     dbms_output.put_line('性别：' || v_gender);
     -- 模拟catch
     exception when others then dbms_output.put_line(sqlerrm);
  end;
  -- 后续有正常语句
  dbms_output.put_line('main end ...');
end;