oracle-笔记

 

--打开执行器时间：
1、 在命令行中输入set timing on 然后执行sql语句

2、打开文本输出
  set serveroutput on;

3、清除缓冲区：
  alter system flush buffer_cache

4、清楚共享池
  alter system flush shared_pool

--创建视图
create or replace view xm_view001
 as
 select * from bureau_state
--调用视图 
select * from xm_view001

2.SELECT INTO STATEMENT
  --将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条
  记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
  例子：
  BEGIN
  SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
      xxxx;
  END;
  ...

3.IF 判断
  IF V_TEST=1 THEN
    BEGIN
       do something
    END;
  END IF;

4.while 循环
  WHILE V_TEST=1 LOOP
  BEGIN
 XXXX
  END;
  END LOOP;

5.变量赋值
  V_TEST := 123;

6.用for in 使用cursor
  ...
  IS
  CURSOR cur IS SELECT * FROM xxx;
  BEGIN
 FOR cur_result in cur LOOP
  BEGIN
   V_SUM :=cur_result.列名1+cur_result.列名2
  END;
 END LOOP;
  END;

7.带参数的cursor
  CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
  OPEN C_USER(变量值);
  LOOP
 FETCH C_USER INTO V_NAME;
 EXIT FETCH C_USER%NOTFOUND;
    do something
  END LOOP;
  CLOSE C_USER;

8.用pl/sql developer debug
  连接数据库后建立一个Test WINDOW
  在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试

9、取值范围
  between 0 and 100

10、删除重复记录最高效的方法
   delete from aaa a where rowid > (select min(rowid) from aaa b where a.name = b.name and a.id = b.id)

11、oracle数据库MD5加密
   declare
      v_out Varchar2(300);
      v_str varchar2(30);
 begin
      v_str:='111111';
      v_out:=rawtohex(utl_raw.cast_to_raw(dbms_obfuscation_toolkit.md5(input_string =>v_str)));
      dbms_output.put_line(v_out);
 end;

12、oracle分组合并
    create table test2  (col_1 char,col_2 char);

    insert into test2 values(1,2);
    insert into test2 values(1,3);
    insert into test2 values(2,4);
    insert into test2 values(2,5);

    select col_1,wmsys.wm_concat(col_2) col_2 from test2 group by col_1;


13.向数据库插入sys.xmlType数据
insert into person_extend
  (psn_code, PSN_XML)
values
  (1090019358,
   sys.xmlType.createXML('<?xml version="1.0" encoding="UTF-8" standalone="no"?><data version="1.0"><basic_info version="1"><zh_name>你没</zh_name><org_name>xxx</org_name><prof_title_name>cfasf</prof_title_name><prof_title_value>0</prof_title_value><card_type_value>1</card_type_value><gender_value></gender_value><birthday></birthday><card_type_name>dasdf</card_type_name><card_code>421302198711161623</card_code></basic_info><contact version="1"><email>724193449@qq.com</email><mobile>15013279494</mobile><tel_work>020-83302282</tel_work></contact><valid><valid_email>1</valid_email><valid_card>0</valid_card></valid></data>'))