oracle 匿名块测试

      set serveroutput on;
        declare
                         v_array varchar2(100) :='/cCC/ccC/1122858051(OPS)';
                         V_BRACKETS varchar2(1) :='(';
                         v_str varchar2(100);
                       begin      
                       select     substr(v_array, 10, case when INSTR(v_array,V_BRACKETS) > 0 then INSTR(v_array,V_BRACKETS)-10  else  length(v_array) end) into v_str from dual;
                           DBMS_OUTPUT.PUT_LINE('abc ' || v_str);
                       end;
                      

2.命名块：

<<outer>> -- used for go to

declare

v_deptno number(2);

v_dname varchar2(10);

begin

<<inner>>

begin

select deptno into v_deptno from emp where lower(ename)=lower('&name');

end;--<<inner>>

select dname into v_dname from dept where deptno=v_deptno;

dbms_output.put_line('部门名:'||v_dname);

end;--<<outer>>

<<outer>>外层块    <<inner>>内层块

3.子程序：包括过程、函数、包。

3.1过程：

create procedure update_sal(name1 varchar2,newsal number)

is

begin

update emp set sal=newsal where lower(ename)=lower(name1);

end;

 

exec update_sal('scott',3700);

3.2函数：

create function annual_income(name1 varchar2)

return number is

annual_salary number(7,2);

begin

select sal*12+nvl(comm,0) into annual_salary from emp where lower(ename)=lower(name1);

return annual_salary;

end;

 

var income number

call annual_income('scott') into :income;

 

3.3包

create package emp_pkg is

procedure update_sal(name1 varchar2,newsal number);

function annual_income(name1 varhcar2) return number;

end;

create

3.3.1包体：用于实现包规范中的过程和函数

create package body emp_pkg1 is

procedure update_sal(name1 varchar2,newsal number)

is

begin

update emp set sal=newsal where lower(ename)=lower(name1);

end;

function annual_income(name1 varchar2) return number

is

annual_salary number(7,2);

begin

select sal*12+nvl(comm,0) into annual_salary from emp where lower(ename)=lower(name1);

return annual_salary;

end;

end;

 

exec emp_pkg1.update_sal('scott',1500);

 

var income number

exec emp_pkg1.annual_income('scott') into :income;

 

4.触发器：

create trigger update_cascade

after update of deptno on dept for each row

begin

update emp set deptno=:new.deptno where deptno=:old.deptno;

end;