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:-------------------------------------------------declaredeclare 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 exceptionException 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)isbegin update customer set custage = p_newAge where custname = p_custname; commit;end;----execute this sql in sql commandexecute update_age('彭海燕',20);----execute this sql in sql editcall update_age('彭海燕',18);----3.2:function----create or replace function func_add(param number)return numberasbegin 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 rowdeclarebegin if(:old.custage > :new.custage) then raise_application_error('-20001','age can not reduce,add it!'); end if;end;----test expressionupdate customer set custage = '80' where custid = 96;select * from customer where custid = 96;----3.4:package--------package head such as interface in Javacreate or replace package pack_addis function func_add(param number) return number;end;----and package body implements interfacecreate or replace package body pack_addis 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 administratorselect 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 catalogdeclare 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_countasv_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 accountgrant execute on house_countto hr;----login hrbegin 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
?
?
?
?
?
?
?
?
?
?
?
?
?