data_sub在Oracle,Oracle系列之一-Datatype And Subprogram

Oracle系列之一----Datatype And Subprogram

Oracle系列之一----Datatype And Subprogram：http://overshit.iteye.com/admin/blogs/932585；

Oracle系列之二----Exception And Expression：http://overshit.iteye.com/admin/blogs/932605；

Oracle系列之三----Implicit Cursor：http://overshit.iteye.com/admin/blogs/932609；

Oracle系列之四----Dynamic Cursor：http://overshit.iteye.com/admin/blogs/932610；

Oracle系列之五----Cursor And Variable：http://overshit.iteye.com/admin/blogs/932612；

Oracle系列之六----Procedure--Package--Purity：http://overshit.iteye.com/admin/blogs/932615；

Oracle系列之七----Trigger：http://overshit.iteye.com/admin/blogs/932616；

请先查看Oracle系列之零：Oracle体系结构

请先在Oracle 10g下创建用户：housesale，密码为housesale，赋予DBA Permission

----1:---------------------------------------------

declare

v_date date;

begin

v_date := sysdate + 1;

dbms_output.put_line(v_date);

end;

select sysdate from dual;

----2:---------------------------------------------

----declare

declare

v_custname varchar2(50);

begin

----execute

select cust.custname into v_custname

from customer cust

where cust.custid = 96;

dbms_output.put_line('custname:' || v_custname);

----deal with exception

Exception

when no_data_found then

dbms_output.put_line('This customer does not exists!');

end;

----3:---------------------------------------------

subprogram(procedure,function,trigger,package)

----3.1:procedure----

create or replace procedure update_age(p_custname varchar2,p_newAge number)

is

begin

update customer set custage = p_newAge

where custname = p_custname;

commit;

end;

----execute this sql in sql command

execute update_age('彭海燕',20);

----execute this sql in sql edit

call update_age('彭海燕',18);

----3.2:function----

create or replace function func_add(param number)

return number

as

begin

return param + 10;

end;

select func_add(10) from dual;

select func_add(custage) from customer where custid = 96;

----3.3:trigger----

create or replace trigger tri_cust_update

before update or delete on customer

for each row

declare

begin

if(:old.custage > :new.custage) then

raise_application_error('-20001','age can not reduce,add it!');

end if;

end;

----test expression

update customer set custage = '80' where custid = 96;

select * from customer where custid = 96;

----3.4:package----

----package head such as interface in Java

create or replace package pack_add

is

function func_add(param number)

return number;

end;

----and package body implements interface

create or replace package body pack_add

is

function func_add(param number)

return number

as

begin

return param + 10;

end;

end;

select pack_add.func_add(50) from dual;

select pack_add.func_add(custage) from customer where custid = 96;

----4:query oracle character-----------------------

login as system administrator

select name,value$ from props$ where name like '%NLS%'

----5:datatype and ‎variable------------------------

----5.1:collection type----

declare

Type customer_table_type is table of customer.custage%type index by binary_integer;

custtype customer_table_type;

begin

----operator index ‎directly

select custage into custtype(-1) from customer where custname = '彭海燕' and custid = 96;

dbms_output.put_line('custage is: ' || custtype(-1));

----let query result insert into collection directly

select custage bulk collect into custtype from customer;

dbms_output.put_line('custage is: ' || custtype(1));

dbms_output.put_line('count is: ' || custtype.count);

end;

declare

Type customer_table_type is table of customer.custage%type index by binary_integer;

custtype customer_table_type;

begin

----operator index ‎directly

select custage into custtype(-1) from customer where custname = '彭海燕' and custid = 96;

dbms_output.put_line('custage is: ' || custtype(-1));

----let query result insert into collection directly

select custage bulk collect into custtype from customer;

for i in custtype.first..custtype.last

loop

dbms_output.put_line('The ' || i || ' position is ' || custtype(i));

end loop;

end;

----5.2 LOB datatype-------------------------------

drop table bfile_tab;

drop table utl_lob_test;

grant create any directory to housesale;

grant create any library to housesale;

create or replace directory meilin_dir as 'D:\\ora';

create table bfile_tab(bfile_column bfile);

create table utl_lob_test(blob_column blob);

----execute expression as follows will insert picture into utl_lob_test blob_column column as d:\ora catalog

declare

a_blob blob;

----MEILIN_DIR must be uppercase,and you should put meilin.jpg as path:'D:/ora/meilin.jpg'

a_bfile bfile := bfilename('MEILIN_DIR','meilin.jpg');

begin

insert into bfile_tab values(a_bfile)

returning bfile_column into a_bfile;

insert into utl_lob_test values (empty_blob())

returning blob_column into a_blob;

dbms_lob.fileopen(a_bfile);

dbms_lob.loadfromfile(a_blob,a_bfile,dbms_lob.getlength(a_bfile));

dbms_lob.fileclose(a_bfile);

commit;

end;

select * from utl_lob_test;

注：Oracle对LOB字段类型的的处理：这里做个入门，对Java操作LOB类型字段请查看：

JDBC operator oracle LOB column：http://overshit.iteye.com/admin/blogs/932619

----6:---------------------------------------------

create or replace procedure house_count

as

v_houCount number(10);

begin

select count(*) into v_houCount from house;

dbms_output.put_line('house;' || v_houCount);

end;

execute house_count;

----2:call procedure at hr account

grant execute on house_countto hr;

----login hr

begin

housesale.house_count;

end;

create or replace procedure house_list

as

cursor house_cursor is

select housenumber,housetype,edificeid from house where housenumber <105 and housetype > 0 and edificeid < 3;

begin

for house_record in house_cursor loop

dbms_output.put_line('housenumber = [' || house_record.housenumber || '] ---- housetype = [' || house_record.housetype || ']');

end loop;

house_count;

end;

select * from house

----7:nine nine multiplication table---------------

begin

for i in 1..9 loop

for j in 1..i loop

dbms_output.put('' || j || '*' || i || '=' || j*i || ' ');

end loop;

dbms_output.put_line('');

end loop;

end;

Oracle系列SQL及数据库下载：http://download.csdn.net/source/3046868