oracle学习——PL/SQL

7d2v0p6ht6

--绝对值，取余，判断数值正负函数
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;