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中存储过程

参考链接：

9. Oracle中触发器

参考链接：

10. Oracle中的包和包体

包和包体详解参考
包和包体例子参考