oracle学习——PL/SQL

--绝对值，取余，判断数值正负函数
select abs(100),abs(-100),abs('100') from dual;
select mod(100,10),mod(100,0) ,mod(34,7) from dual;
select sign(-9),sign(10) from dual;

--四舍五入截取函数
select round(98.36,1) from dual;
select trunc(23562.3,-4) ,trunc(23532.34634,4) from dual;

insert into productinfo(productid,productname,productprice,quantity) values('024004001','鞋子',22.3,10);

set serveroutput on;

--记录类型
declare

  type product_rec is record (
    v_productid productinfo.productid%type,
    v_productname varchar2(20),
    v_productprice number(8,2)
  );

  v_product product_rec;

  v_product2 productinfo%rowtype;
begin

  select productid,productname,productprice into v_product from productinfo where productid='024004001';
  select * into v_product2 from productinfo where productid='024004001';
  dbms_output.put_line(v_product2);
  exception
    when no_data_found then dbms_output.put_line('未找到数据');
    when too_many_rows then dbms_output.put_line('不只一行');
    when others then dbms_output.put_line('其他异常导致的');
end;

--索引表类型
declare 
  type t1 is table of productinfo%rowtype index by binary_integer;
  type t2 is table of productinfo.productname%type index by varchar(10);
  v_t1 t1;
  v_t2 t2;
begin
  v_t2(0) := '正确';
  v_t2(1) := '错误';
  v_t2('hello'):= 'hello';
  select * into v_t1(1) from productinfo where productid='024004001';
  dbms_output.put_line('v_t1(1):'||v_t1(1).productname);
  dbms_output.put_line('v_t2(1):'||v_t2(1));
  dbms_output.put_line('v_t2(0):'||v_t2(0));
  dbms_output.put_line('v_t2(hello):'|| v_t2('hello'));
end;
/

--varray变长数组
declare
  type varr is varray(100) of varchar(10) not null;
  v_array varr := varr('a','b','c','hello');
begin
  v_array(2):='sdfsdf';
  dbms_output.put_line(v_array(2));
  dbms_output.put_line(v_array(1));
end;
/

declare
 v_price number(8,2):=1.4;
begin
  if v_price > 12 then dbms_output.put_line('big than 12');
  elsif v_price <10 then  dbms_output.put_line('less than 12');
  end if;
  case v_price
  when 2 then dbms_output.put_line('2');
  when 1.4 then dbms_output.put_line('1.4');
 --when v_price<10 then  dbms_output.put_line('s')
  else dbms_output.put_line('no');
  end case;
end;
/

--loop while-loop
declare
 v_price number(8,2):=1.4;
begin

  <<basic_loop>>
  loop
    v_price := v_price+1;
    dbms_output.put_line('basic'||v_price);
   /** if v_price > 210 then 
      exit basic_loop;
    end if;**/
    exit basic_loop when v_price > 210;
  end loop;
  /****/
  <<while_loop>>
  while v_price<410
  loop
    v_price:=v_price+1;
    dbms_output.put_line('while'||v_price);
  end loop;

  <<for_loop>>
  for i in 1..200
    loop
      dbms_output.put_line('for'||i);
    end loop;
end;
/

--以上是基础，下面是PL/SQL使用DML和DDL
declare
  v_category categoryinfo.categoryid%type;
  v_categoryinfo categoryinfo%rowtype;
begin
   select categoryid into v_category from categoryinfo where categoryinfo.categoryid='f';
   exception
    when no_data_found then 
      dbms_output.put_line('没有对应的产品类型编号，将添加编号');
      insert into categoryinfo(categoryid,categoryname) values('f','食物');
      commit;
       select * into v_categoryinfo from categoryinfo where categoryinfo.categoryid='f';
      dbms_output.put_line(v_categoryinfo.categoryid||','||v_categoryinfo.categoryname);
    when too_many_rows then 
      dbms_output.put_line('不止一条记录被发现');
end;
/

declare
  v_ddl varchar(200);
begin
  v_ddl:='
    create table testtable(
      id varchar2(100) primary key not null
    )';
  execute immediate v_ddl;
end;
/

--查询预定义异常
select * from dba_source where name='STANDARD' and text like '%EXCEPTION_INIT%';

--自定义异常
declare
  v_char number(8,0):=9.9;
  myException Exception;
  pragma exception_init(myException,-11111);
begin
  /** **/
  if v_char=10 then raise myException;
   end if;
  exception
    when myException then dbms_output.put_line('myException raise');
end;
drop function testfun;
create function testfun
return number
as 
  v_count number(8,0);
begin
  select count(*) into v_count from categoryinfo;
  return v_count;
end;

select testfun() from dual;