oracle常用操作

发布日期:2018-07-11 10:20:09

--创建函数并且调用函数

CREATE OR REPLACE FUNCTION GET_VSL_NAMES_BY_VSLTYPE (V_TYPE VARCHAR2) RETURN VARCHAR2 IS

BEGIN
  DECLARE
    V_RESULT  VARCHAR2(2000);
    CURSOR C_VSL(L_TYPE VARCHAR2) IS 
      SELECT V.VSL_CNAME
      FROM bas_vessels V
      WHERE V.vsl_vtype = L_TYPE;
    C_VSL_REF C_VSL%ROWTYPE;
  BEGIN

    V_RESULT:= '';


    IF V_TYPE IS NULL THEN
       RAISE_APPLICATION_ERROR(-80291, '船类型不能为空!');          
    END IF;

    OPEN C_VSL(V_TYPE);
    FETCH C_VSL
      INTO C_VSL_REF;
     
    WHILE C_VSL%FOUND LOOP
      IF C_VSL_REF.VSL_CNAME IS NOT NULL THEN
         V_RESULT:= V_RESULT || C_VSL_REF.VSL_CNAME || '|';
      END IF;
      FETCH C_VSL
        INTO C_VSL_REF;
    END LOOP;
    
    RETURN V_RESULT;
    CLOSE C_VSL;
  END;
END GET_VSL_NAMES_BY_VSLTYPE;

select GET_VSL_NAMES_BY_VSLTYPE('CAR') as names  from dual;


返回最新插入值的主键

SELECT SEQ_NAME.NEXTVAL  INTO v_cnt_id FROM DUAL;
insert into tableName (...) values  (...);
RETURN v_cnt_cst_id;

字符串转日期

V_BIN_CREDIT_STTM := to_date(V_BIN_CREDIT_STTM_S,'YYYY-MM-DD HH24:MI:SS');


--if判断的用法

declare 
a number := 200;
b number := 200;
begin
  if a > b then
     dbms_output.put_line('a>b');
  elsif a = b then
     dbms_output.put_line('a=b');        
  else
     dbms_output.put_line('a<b');
  end if;
end;

--case的用法
declare 
score varchar2(1) := 'A';
result varchar2(4);
begin
      result :=
      case score
        when 'A' then '优'
        when 'B' then '良'
        when 'C' then '中'
        when 'D' then '差'
        else '不详'
      end;
      dbms_output.put_line('结果等级为' || result || '!');
end;

--loop的用法
declare 
total number := 0;
salary number := 1200;
begin
  loop
         total := total + salary;
         dbms_output.put_line(total);  
         exit when total > 5000;
  end loop;
end;

--while的用法
declare 
total number := 0;
salary number := 1200;
begin
  while total <= 5000 loop
         total := total + salary;
         dbms_output.put_line(total);  
  end loop;
end;

--for循环的用法
declare 
i number;
begin
  for i in 1..3 loop
    dbms_output.put_line(i);  
  end loop;
end;

--goto的用法
declare 
n number := 2;
begin
  if n>1 then
     goto big_lable;
  end if;
  <<big_lable>>
  dbms_output.put_line('n is large then 1');
end;

--if的用法
declare 
a number := null;
b number := null;
begin
  if a=b then
     dbms_output.put_line('a=b');
  elsif a!=b then
     dbms_output.put_line('a!=b');
  else
     dbms_output.put_line('they are not equal');
  end if;
end;

--type类型的用法
declare 
type Person is record(
     name varchar2(50),
     age number
);
p Person;
begin
  select 'julian.zhu',31 into p.name,p.age from dual;
  dbms_output.put_line(p.name || ',' || p.age);
end;

declare 
  type area_table_type is table of number
       index by varchar2(10);
  area_table area_table_type;
begin
  area_table('北京') :=1;
  area_table('上海') :=2;
  area_table('广州') :=3;
  dbms_output.put_line(area_table.first);
  dbms_output.put_line(area_table.last);
  dbms_output.put_line(area_table.next('北京'));
end;

--创建存储过程,查询没有找到数据
create or replace procedure query_employee
(eno number,name out varchar2,salary out number)
is
begin
select name,sal into name,salary from emp where empno=eno;
exception
when NO_DATA_FOUND then
RAISE_APPLICATION_ERROR(-2000,'该雇员不存在');
end;