oracle常用操作
--创建函数并且调用函数
CREATE OR REPLACE FUNCTION GET_VSL_NAMES_BY_VSLTYPE (V_TYPE VARCHAR2) RETURN VARCHAR2 IS
BEGINDECLARE
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;
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;