Oracle实用相关备忘

àì夳堔傛蜴生んèń 2024-04-01 18:35 72阅读 0赞

#### 文章目录 ####

*  1. Oracle中输出语句\_一般调试用
 *  2. Oracle存储过程 case when then的使用
 *  3. Oracle锁表查询和解锁方法
 *   *  3.1 查看表被锁的信息
     *  3.2 杀掉锁表进程
     *  3.3 查看数据库引起锁表的SQL语句
 *  4. Oracle中循环(Goto、For、While、Loop)
 *   *  4.1 Oracle中的GOTO用法
     *  4.2 Oracle中的For循环用法
     *  4.3 Oracle中的While循环用法
     *  4.4 Oracle中的LOOP循环用法
 *  5. Oracle中常用或常见的使用
 *   *  5.1 Oracle触发器中ORA-04091报错--自治事务
     *  5.2 Oracle中%TYPE和%ROWTYPE的使用
     *   *  5.2.1 %TYPE
         *  5.2.2 %RowType
     *  5.3 Oracle闪回
     *  5.4 Oracle查看当前日期是第几个星期
     *  5.5 Oracle和MySQL批量插入数据；利用update实现将一个表的字段值赋给另一张表的字段
     *  5.6 Oracle取得分组后最大值的整行记录
     *  5.7 Oracle函数，按分隔符截取字符串
     *  5.8 Oracle 把字符串中 多个空格 转为 一个空格
 *  6. Oracle创建表空间自增长和创建用户
 *  7. Oracle的导出和导入
 *   *  7.1 导出
     *   *  7.1.1 exp导出数据命令
         *  7.1.2 expdp导出数据 -- 服务器上操作
         *  7.1.3 Oracle11g导出时会发现少表，空表没有导出
     *  7.1 导入 -- 使用imp、impdp方式导入数据
     *   *  7. 1.1 使用imp导入数据
         *  7.1.2 使用impdp导入数据 -- 服务器上操作
 *  8. Oracle中存储过程
 *  9. Oracle中触发器
 *  10. Oracle中的包和包体

## 1. Oracle中输出语句\_一般调试用 ##

begin 
    	sqlStr = '测试输出内容';
    	dbms_output.put_line('-----' || sqlStr);
    end;

## 2. Oracle存储过程 case when then的使用 ##

# 实例
    declare 
      i integer;
      imonth int;
      updatestr  varchar2(50);
      daystr     varchar2(5);
    begin
      imonth := 5;
       case imonth
           when 1 then  daystr:=31; when 2 then daystr:=28;
           when 5 then daystr:=31; else daystr:=30;
       end case;
       dbms_output.put_line(daystr);
    end;

## 3. Oracle锁表查询和解锁方法 ##

--以下几个为相关表
    SELECT * FROMv$lock;
    SELECT * FROMv$sqlarea;
    SELECT * FROMv$session;
    SELECT * FROMv$process;
    SELECT * FROMv$locked_object;
    SELECT * FROMall_objects;
    SELECT * FROMv$session_wait;

### 3.1 查看表被锁的信息 ###

Select sess.sid,	-- Sid
     		sess.serial#, 	-- Serial
     		lo.oracle_username,	-- 登录的账号 
     		lo.os_user_name, 	-- 登录的电脑
     		ao.object_name,		-- 被锁的表名
     		lo.locked_mode  	--  锁住级别
     from 	v$locked_object lo,dba_objects ao,v$session sess 
     where 	ao.object_id=lo.object_id and 
     		lo.session_id=sess.sid;

### 3.2 杀掉锁表进程 ###

alter system kill session '68,51';  --分别为SID和SERIAL#号

### 3.3 查看数据库引起锁表的SQL语句 ###

SELECT A.USERNAME,
           A.MACHINE,
           A.PROGRAM,
           A.SID,
           A.SERIAL#,
           A.STATUS,
           C.PIECE,
           C.SQL_TEXT
      FROM V$SESSION A, V$SQLTEXT C
     WHERE A.SID IN (SELECT DISTINCT T2.SID
                       FROM V$LOCKED_OBJECT T1, V$SESSION T2
                      WHERE T1.SESSION_ID = T2.SID)
       AND A.SQL_ADDRESS = C.ADDRESS(+)
     ORDER BY C.PIECE;

## 4. Oracle中循环(Goto、For、While、Loop) ##

### 4.1 Oracle中的GOTO用法 ###

DECLARE
       x number;
    BEGIN
       x := 9;
       <<repeat_loop>> -- 设置循环点
       x := x - 1;
       DBMS_OUTPUT.PUT_LINE(X);
       IF X > 0 THEN
         GOTO repeat_loop;  --当x的值小于9时, 就goto到repeat_loop
       END IF;
    END;

### 4.2 Oracle中的For循环用法 ###

-- 循环输出数值   递增循环
    begin
      for i in 1 .. 100 loop
        sys.dbms_output.put_line(i);
      end loop;
    end;
    
    -- 倒序循环
    DECLARE
        X number; -- 变量
      BEGIN
        x := 1; -- 初始值
        FOR X IN REVERSE 1 .. 10 LOOP
          -- reverse由大到小
          DBMS_OUTPUT.PUT_LINE('内:x=' || x);
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('end loop:x=' || X); -- x=1
    END;
    
    -- 实例0 
    -- 直接循环Select的结果集   cur_row为游标子项  (更多的时候,表是动态表,   所以这句声明也可以省略)
    begin
      for cur_row in (select user_id, username, created from all_users) loop
        sys.dbms_output.put_line(cur_row.username);
      end loop;
    end;
    
    
    -- 实例1 
    declare
    	cursor myCur is select * from hr.jobs;
    	-- 游标子项 这句声明也可以省略
    	oneRow hr.jobs%rowtype;
    begin
    	for oneRow in myCur loop
    		dbms_output.put_line(oneRow.job_id ||'    ' ||onerow.job_title);
    	end loop;
    end;
    
    -- 实例2 ： 双重For循环实例，结合游标遍历，供使用参考：
    create or replace procedure test_procedure is
      --a表游标定义
      cursor a_cursor is
      select substr(mc,0,2) as str ,mc as mcs from t_bz_zd_xzqh_jc;
      --b表游标定义
      cursor b_cursor(str1 string) is
        SELECT bm FROM t_bz_zd_xzqh where mc like  '%' || str1 || '%'; -- instr(mc, str1) > 0;
    begin
      for a_cur in a_cursor loop
        for b_cur in b_cursor(a_cur.str) loop
          --这里是你要执行的操作，比如insert到c
          --insert into c values (b_cur.id);
          update t_bz_zd_xzqh_jc set bh= b_cur.bm where mc = a_cur.mcs ;
          commit;
        end loop;
      end loop;
    end;

### 4.3 Oracle中的While循环用法 ###

DECLARE
        x number;
      BEGIN
        x := 0;
        WHILE x < 9 LOOP
          x := x + 1;
          DBMS_OUTPUT.PUT_LINE('内:x=' || x);
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('外:x=' || x);
     END;
    
    -- 实例
    declare
    	cursor myCur is select * from hr.jobs;
    	oneRow hr.jobs%rowtype;
    begin
    	open myCur;
    	fetch myCur into oneRow;
    	while (myCur%found)
    	loop
    		dbms_output.put_line(oneRow.job_id ||'    ' ||onerow.job_title);
    		fetch myCur into oneRow;
    	end loop;
    	close myCur;
    end;

### 4.4 Oracle中的LOOP循环用法 ###

DECLARE
        x number;
      BEGIN
        x := 0;
        LOOP
          x := x + 1;
          EXIT WHEN x > 9;  -- 满足条件时退出
          DBMS_OUTPUT.PUT_LINE('内:x=' || x);
       END LOOP;
       DBMS_OUTPUT.PUT_LINE('外:x=' || x);
    END;
    
    -- 实例：
    declare
    	cursor myCur is select * from hr.jobs;
    	oneRow hr.jobs%rowtype;
    begin
    	open myCur;
    	loop
    		fetch myCur into oneRow;
    		dbms_output.put_line(oneRow.job_id ||'    ' ||onerow.job_title);
    		exit when myCur%notFound;
    	end loop;
    	close myCur;
    end;

## 5. Oracle中常用或常见的使用 ##

### 5.1 Oracle触发器中ORA-04091报错–自治事务 ###

CREATE OR REPLACE Trigger Pdm_wfproc_gjy
      After Insert Or Update On Cpcwfproc
      For Each Row
      when (new.procid =1 and new.stat = 7)  --分开事务了最好加上条件，确保前置数据拥有	
    declare
      Pragma autonomous_transaction; ----这段可以确保触发器表与下面查询语句表分开事务
      x_wftempid Integer;
    Begin
     ---raise_application_error(-20000,'wfid='||:new.wfid);
     select w.wftempid into x_wftempid from cpcwf w where w.wfid = :new.wfid;--不加条件在当前场景就报错了
      if x_wftempid in (135, 134, 133, 27) then
        update yfps_cache_trigger t
           set (t.aendtime, t.endtime, t.period, t.aendtime2, t.sylb) =
               (Select C2.Aendtime as aendtime,
                       C2.Aendtime Endtime,
                       To_Number(Round(C2.Aperiod / 8, 2)) Period,
                       C2.Aendtime Aendtime2,
                       Decode(w.Wftempid,
                              27,
                              '量产',
                              135,
                              '单独送样',
                              134,
                              '二次送样流程',
                              133,
                              '首次送样流程',
                              w.Wftempid) Sylb
                  From User_Yryskfpfd a, Cpcshtins s, Cpcwfproc c2, Cpcwf w
                 Where s.Shtinsid = a.Shtinsid
                   And s.Visible = 2
                   And C2.Wfid = s.Wfid
                   And C2.Stat = 7
                   And C2.Procid = 18
                   And a.Yfy <> 'admin'
                   and s.wfid = :new.wfid)  
         where exists (select 1 from Cpcshtins s where s.wfid = :new.wfid and s.shtinsid = t.shtinsid);
    
    commit; -- ***这句是必须了，不然又有新的报错了***

### 5.2 Oracle中%TYPE和%ROWTYPE的使用 ###

#### 5.2.1 %TYPE ####

DECLARE   
      V_ORG_NAME SF_ORG.ORG_NAME%TYPE; 		--与ORG_NAME类型相同  
      V_PARENT_ID SF_ORG.PARENT_ID%TYPE;	--与PARENT_ID类型相同  
    BEGIN  
      SELECT ORG_NAME,PARENT_ID 
      INTO V_ORG_NAME,V_PARENT_ID  
      FROM SF_ORG SO  
      WHERE SO.ORG_ID=&ORG_ID;  
      DBMS_OUTPUT.PUT_LINE('部门名称：' || V_ORG_NAME);  
      DBMS_OUTPUT.PUT_LINE('上级部门编码：' || TO_CHAR(V_PARENT_ID));  
    END;

#### 5.2.2 %RowType ####

DECLARE   
      V_SF_ORG_REC SF_ORG%ROWTYPE; --与SF_ORG表中的各个列相同  
    BEGIN  
      -- 获取整行的数据，具体字段值，通过 V_SF_ORG_REC.字段名 即可获取
      SELECT * 
      INTO V_SF_ORG_REC  
      FROM SF_ORG SO  
      WHERE SO.ORG_ID=&ORG_ID;  
      DBMS_OUTPUT.PUT_LINE('部门ID：' || TO_CHAR(V_SF_ORG_REC.ORG_ID));  
      DBMS_OUTPUT.PUT_LINE('部门名称：' || V_SF_ORG_REC.ORG_NAME);  
      DBMS_OUTPUT.PUT_LINE('上级部门编码：' || TO_CHAR(V_SF_ORG_REC.PARENT_ID));  
    END;

### 5.3 Oracle闪回 ###

-- 参考
    Select * from scott.dept 
    as of timestamp 
    to_timestamp('2017-12-14 16:20:00','yyyy-mm-dd hh24:mi:ss') 
    Where XXX ;

### 5.4 Oracle查看当前日期是第几个星期 ###

-- 相关Sql，自己查询下看看便清楚了
    select to_char(sysdate,'ww') ,to_char(sysdate,'iw') from dual;
    select to_char(sysdate,'ddd') from dual;
    select TRUNC(SYSDATE,'MM') from dual;1）
    -- 查今天是 "本月" 的第几周　　
    SELECT TO_CHAR(SYSDATE,'WW') - TO_CHAR(TRUNC(SYSDATE,'MM'),'WW') + 1 AS "weekOfMon" from dual;　　
    或　　
    SELECT TO_CHAR(SYSDATE,'W') AS "weekOfMon" from dual;　　
    -- 查今天是 "今年" 的第几周　　
    select to_char(sysdate,'ww') from dual;　　
    或　　
    select to_char(sysdate,'iw') from dual;

### 5.5 Oracle和MySQL批量插入数据；利用update实现将一个表的字段值赋给另一张表的字段 ###

update TableA 
    set TableA.no= (select TableB.no from TableB 
    where TableA.id=TableB.id)
    Where TableA.XXXXXXXX

### 5.6 Oracle取得分组后最大值的整行记录 ###

-- 方法一
    select t1.a,t1.b,t1.c   
    from test t1   
    inner join   
    (select a,max(b) as b from test group by a) t2   
    on t1.a=t2.a and t1.b=t2.b  
    
    -- 方法二
    select * from 
    (select t.*, row_number() over(partition by 分组字段 order by 排序字段 desc ) rn
    from tablename t )
    where rn=1

### 5.7 Oracle函数，按分隔符截取字符串 ###

-- 可以正则表达式直接实现 ，返回 'ccccc'
    select regexp_substr('aaa,bb,ccccc,ddd,vvv','[^,]+',1,3)
    from dual;
    
    -- 按分隔符截取指定位置的字符串，希望有需要的朋友能用到
    -- 实例： substrbysep('aaa,bb,ccccc,ddd,vvv',',',3)返回结果 'ccccc'
    -- 函数代码：
    create or replace function substrbysep(sourceString    varchar2,
                                           destString      varchar2,
                                           appearPosition  number)
      return varchar2 is
      substring varchar2(255);
      --功能：按分隔符截取字符串
      --参数：1、原始字符串；2、分隔符；3、截取第几段
    begin
      substring := substr(destString || sourceString || destString,
                          instr(destString || sourceString || destString,
                                destString,
                                1,
                                appearPosition) + 1,
                          instr(destString || sourceString || destString,
                                destString,
                                1,
                                appearPosition + 1) -
                          instr(destString || sourceString || destString,
                                destString,
                                1,
                                appearPosition) - 1);
      return(substring);
    end substrbysep;

### 5.8 Oracle 把字符串中 多个空格 转为 一个空格 ###

-- 使用正则表达式直接实现
    SELECT REGEXP_REPLACE(str,' {1,}',' ') FROM DUAL;
    或者
    SELECT REGEXP_REPLACE(str,' +',' ') FROM DUAL;

## 6. Oracle创建表空间自增长和创建用户 ##

/*
      步骤：
      1、创建表空间
      2、创建用户
      3、用户授权
      */
      
      /* 1. 创建表空间*/
      create tablespace test
      /*表空间物理文件名称*/
      datafile 'test.dbf' 
      -- 这种方式指定表空间物理文件位置
      -- datafile 'F:\APP\QIUCHANGJIN\ORADATA\ORCL\QCJ_TABLESPACE.dbf' 
      -- 大小 500M，每次 5M 自动增大，最大不限制
      size 500M autoextend on next 5M maxsize unlimited; 
      
       /* 2. 创建用户*/
      create user qiuchangjin 
      IDENTIFIED BY root --用户密码
      default tablespace test	-- 表空间是上面创建的
      temporary tablespace TEMP -- 临时表空间默认 TEMP
      profile DEFAULT;
      
      --password expire;
       /*密码过期需要重设密码,意思是当你用这个新建立的密码过期用户首次登录后，系统会提示你重新输入新密码，不然会拒绝你登陆，重设新密码后就可以登录，该用户的密码就是你新设的密码，相当于首次登录修改密码这样的选项。*/
        
       /* 3. 用户授权_1*/
      grant connect,resource,dba to qiuchangjin;
       /* 3. 用户授权_2*/
      grant connect to qiuchangjin with admin option;
      grant dba to qiuchangjin with admin option;
      grant resource to qiuchangjin with admin option;
      
      -- 一下为表空间的一些相关Sql，备忘 
      /*查询所有表空间物理位置*/
      select name from v$datafile;
      /*查询当前用户的表空间*/
      select username,default_tablespace from user_users;
      /*修改用户的默认表空间*/
      alter user 用户名 default tablespace 新表空间; 
      /*查询所有的表空间*/
      select * from user_tablespaces; 
      
      /* 删除表空间*/
      alter tablespace test offline;
      drop tablespace test including contents and datafiles;

## 7. Oracle的导出和导入 ##

### 7.1 导出 ###

#### 7.1.1 exp导出数据命令 ####

--  Db连接参考： sqlplus system/orcl@localhost:1521/orcl
    -- Cmd下执行如下命令：  
    exp test/123456@192.168.13.211:1521/oanet 
    file=D:\export\test.dmp 
    log=D:\export\test.log 
    full=y

#### 7.1.2 expdp导出数据 – 服务器上操作 ####

-- (1)cmd窗口连接数据库，进入sqlplus页面，如：
    sqlplus sys/sys@192.168.13.211:1521/oanet as sysdba
    -- (2)创建目录对象(使用管理员账号登录创建)
    SQL> create or replace directory dump_dir as 'D:\fzb';
    -- 创建好后，退出sqlplus
    
    -- (3)在操作系统上创建相应的目录，如在D盘目录下建立文件夹fzb
    
    -- (4)连接数据库执行导出命令
    -- 实例1： 把test1库和test2库全部导入
    expdp system/123456@192.168.13.211:1521/oanet directory=dump_dir dumpfile=XX.dmp schemas=test1,test2;
    -- 实例2： 把该实例下所有数据库导出
    expdp system/123456@192.168.13.211:1521/oanet directory=dump_dir dumpfile=XX.dmp Full=y;

#### 7.1.3 Oracle11g导出时会发现少表，空表没有导出 ####

-- 1.查询当前用户下的所有空表，一个用户最好对应一个默认的表空间，命令如下：
     　　　　>SQL:  select table_name from user_tables where num_rows='0'
    
    -- 2.根据上述查询的语句，可以构建针对空表分配空间的命令语句，具体如下;
           　  >SQL:select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or num_rows is null  -- （注意：很多教程没有这里，这里是有可能为空的）
    -- 3. 上述代码可产生批量的修改表extent的SQL语句(有多少张空表就产生多少条)，我们只需要将其生成的所有sql代码全部执行，就可以给每一张已经存在的表来分配  segment，就OK了。
    -- 4. 再用exp命令进行导出就可以了

### 7.1 导入 – 使用imp、impdp方式导入数据 ###

#### 7. 1.1 使用imp导入数据 ####

-- 打开cmd窗口，然后直接敲入一下命令即可，需要注意的是，要事先把dmp文件放到正确的路径中去
    imp test/123456@192.168.13.211:1521/oanet 
    file = E:\yxb\teste.dmp 
    log = E:\yxb\teste.log 
    full = y

#### 7.1.2 使用impdp导入数据 – 服务器上操作 ####

-- 1. 进入数据库服务器或本机打开cmd或shell命令界面，执行如下命令
    Sqlplus system/123456@192.168.13.211:1521/oanet as sysdba
    -- 2. 创建目录对象，如：
    Sql>  create or replace directory dump_dir as 'D:\fzb';  --（以管理员账号登录创建）
    -- 3. 创建好目录后退出，输入exit，然后回车退出Sql输入模式
    -- 4. 在操作系统上创建相应的目录， 如 在D盘目录下建立文件夹  fzb
    -- 5. 将dump文件放入对应文件夹，然后执行如下命令
    impdp system/123456@192.168.13.211:1521/oanet directory=dump_dir dumpfile=test.DMP schemas=test
    导入多个库：  如下实例：
    impdp system/123456@192.168.13.211:1521/oanet directory=dump_dir dumpfile=test.dmp schemas=HIS6,easyhis;

## 8. Oracle中存储过程 ##

[参考链接：][Link 1]

## 9. Oracle中触发器 ##

[参考链接：][Link 2]

## 10. Oracle中的包和包体 ##

[包和包体详解参考][Link 3]  
[包和包体例子参考][Link 4]

[Link 1]: https://www.cnblogs.com/yucoder/p/11089564.html
[Link 2]: http://blog.csdn.net/q547550831/article/details/50074721
[Link 3]: https://blog.csdn.net/u013249984/article/details/77651549
[Link 4]: https://blog.csdn.net/liaomin416100569/article/details/48422239