Oracle 11G 进阶语法

最新推荐文章于 2022-10-04 21:07:20 发布

才华有限青年_

最新推荐文章于 2022-10-04 21:07:20 发布

阅读量594

点赞数

文章标签：数据库

本文链接：https://blog.csdn.net/yx183/article/details/106563633

版权

--查找数据库名字
select name from v$database ;

--根据关键字查找对应的存储过程
select distinct name from USER_SOURCE where type = 'PROCEDURE' and text like '%要查找的内容%';

SELECT * FROM USER_SOURCE T WHERE UPPER(T.TEXT) LIKE UPPER('%订单需求数%')

--此语句是查看数据库的死锁
select 'alter system kill session '''||sess.sid||','||sess.serial#||''';'
from v$locked_object lo,dba_objects ao,v$session sess
where ao.object_id = lo.object_id
and lo.session_id = sess.sid;

--杀死进程
alter system kill session '28,32460';

--查找表空间使用情况
select D.TABLESPACE_NAME,
       ROUND(D.SPACE/(1024*1024*1024),2) 
       SPACE,ROUND(D.MaxSpace/(1024*1024*1024),2),
       ROUND(F.Free_SPACE/(1024*1024*1024),2) FREE_SPACE,
       ROUND((D.SPACE - nvl(F.Free_SPACE,0))/decode(D.MaxSpace,0,null,D.MaxSpace) * 100,2) Use_Rate 
from
(SELECT TABLESPACE_NAME,SUM(BYTES) SPACE,SUM(BLOCKS) BLOCKS,SUM(MAXBYTES) as MaxSpace 
FROM DBA_DATA_FILES t
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,sum(BYTES) as Free_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F 
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);

--查找每个表对接的数据量
select table_name,num_rows from user_tables order by num_rows desc;

--存储过程调试
declare 
r_test varchar2(200); --输出参数

begin
 	SPSOUDF_PURCHASE_PRICE('ZPRK19091900015','1','M00507','cn',r_test);
 	dbms_output.put_line(r_test); 
end;


--Server SQL 2008 游标通用语法
declare @a varchar(2000),@b varchar(2000),@c int 
  declare Cursor_name cursor 
	for  
	select a,b,c from table 	
    open Cursor_name
          fetch next from Cursor_name into @a,@b,@c     
	  while @@FETCH_STATUS =0 
            begin
                               
               fetch next from Cursor_name into @a,@b,@c               
            end            
	close Cursor_name
 deallocate Cursor_name


--Oracle 11G 游标通用语法
declare 
r_a    varchar2(50);
r_b    varchar2(50);
r_c    varchar2(50);

declare cursor cursor_name is
       select a,b,c from table; 
begin
  open cursor_name;
  loop
    fetch cursor_name into r_a,r_b,r_c;
    exit when cursor_name%notfound;
    
    	begin

    	end;
  end loop;
  close order_cur;
end;

--函数wm_concat(列名),该函数可以把列值以","号分隔起来,并显示成一行

select t.orderno 订单号,WM_CONCAT(t.orderlineno) 行号, 
       WM_CONCAT(t.location||'('||t.qty||')') "分配库位(数量)"
from act_allocation_details t where t.orderno='ZPCK20060403044' group by t.orderno

--listagg() WITHIN GROUP () 该函数可以实现按条件实现列转行

select listagg(t.sku||' '||bs.sku_group2||' '||bs.sku_group3||' '||t.qty||' '||t.location,';') WITHIN GROUP (order by t.sku)
from act_allocation_details t left join bas_sku bs on t.sku=bs.sku
where t.orderno='ZPCK20060403044'

--oracle 用一张表的更新另外一张表某个字段

select * from A123 for update 

delete from A123

MERGE INTO bas_sku b
   USING A123 a
    ON ( a.sku = b.sku)                    
WHEN MATCHED
THEN
  UPDATE SET b.Reservedfield07 = a.jkm

--根据某个分隔符取对应的值

CREATE OR REPLACE FUNCTION Get_UDFA_Parameter
(
IN_List    in varchar2,--要分隔的字符串
IN_SplitOn in varchar2,--分隔符
IN_num1    in int
)
return varchar2 is  Result varchar2(2000 char);
r_i int;
r_j int;
r_p int;
BEGIN
  r_p := lengthb(IN_SplitOn);
  if IN_num1 - 1 > 0 then
     r_i := instrb(IN_List, IN_SplitOn, 1, IN_num1 - 1);
  else
     r_i := 0;
  end if;

  r_j := instrb(IN_List, IN_SplitOn, 1, IN_num1);
  if r_j = 0 then
    r_j := lengthb(IN_List) + r_p;
  end if;
  result := substrb(IN_List, r_i + r_p, r_j- r_i - r_p);
  return(Result);
end;

--例子
select Get_UDFA_Parameter('A#B#C','#',1) from dual

oracle 物化视图

ON DEMAND指物化视图在用户需要的时候进行刷新，可以手工通过DBMS_MVIEW.REFRESH等方法来进行刷新，也可以通过JOB定时进行刷新,即更新物化视图，以保证和基表数据的一致性;
而ON COMMIT是说，一旦基表有了COMMIT，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。


CREATE MATERIALIZED VIEW SEC_SYS_USER
REFRESH FAST ON COMMIT
AS
SELECT "SYS_USER"."USER_ID" "USER_ID","SYS_USER"."USER_NAME" "USER_NAME","SYS_USER"."PASSWORD" "PASSWORD",
"SYS_USER"."ACTIVE_FLAG" "ACTIVE_FLAG","SYS_USER"."SALT" "SALT","SYS_USER"."ROLE_ID" "ROLE_ID",
"SYS_USER"."ORG_ID" "ORG_ID","SYS_USER"."PRICEACCESS" "PRICEACCESS",
"SYS_USER"."USER_LEVEL" "USER_LEVEL","SYS_USER"."USER_TITLE" "USER_TITLE","SYS_USER"."USER_TEL" "USER_TEL",
"SYS_USER"."USER_ZIP" "USER_ZIP","SYS_USER"."USER_ADDRESS1" "USER_ADDRESS1","SYS_USER"."USER_ADDRESS2" "USER_ADDRESS2",
"SYS_USER"."USER_ADDRESS3" "USER_ADDRESS3","SYS_USER"."USER_ADDRESS4" "USER_ADDRESS4",
"SYS_USER"."USER_EMAIL" "USER_EMAIL","SYS_USER"."ADDTIME" "ADDTIME","SYS_USER"."ADDWHO" "ADDWHO",
"SYS_USER"."EDITTIME" "EDITTIME","SYS_USER"."EDITWHO" "EDITWHO","SYS_USER"."GLOBALDATEFORMAT" "GLOBALDATEFORMAT",
"SYS_USER"."CARDNO" "CARDNO" FROM "DBSEC"."SYS_USER" "SYS_USER";