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

?

?

?

?

?

?

?

?

?

?

?

?

?