oracle实用语法信息

《PL/SQL编程》                              /*procedural language/sql*/--1、过程、函数、触发器是pl/sql编写的--2、过程、函数、触发器是在oracle中的--3、pl/sql是非常强大的数据库过程语言--4、过程、函数可以在java程序中调用--提高效率：优化sql语句或写存储过程--pl/sql移植性不好--IDE(Integration Develop Environment)集成开发环境--命令规则：--变量(variable)           v_--常量(constant)           c_--指针、游标(cursor)         _cursor--例外、异常(exception)    e_--可定义的变量和常量：  --标量类型：scalar  --复合类型：composite    --存放记录、表、嵌套表、varray  --参照类型：reference  --lob(large object)  《PL/SQL 基本语法》--例：创建存储过程create or replace procedure pro_addisbegin  insert into mytest values('韩xx','123');end;exec pro_add; --调用--查看错误信息show error;--调用过程exec 过程(c1,c2,...);call 过程(c1,c2,...);--打开/关闭输出选项set serveroutput on/off--输入&--块结构示意图declare   --定义部分，定义常量、变量、游标、例外、复杂数据类型begin     --执行部分，执行pl/sql语句和sql语句exception --例外处理部分，处理运行的各种错误end;      --结束--《实例演示》declare  v_ival number(4) :=100; --声明并初始化变量  --v_dtm date;  v_dtm syslogs.dtm%type; --取表字段类型  v_content varchar(512);begin  v_ival := v_ival * 90;  --赋值运算  insert into syslogs values(seq_syslogs.nextval,10,sysdate,'v_ival='||v_ival,user);--数据库存储  dbms_output.put_line('v_ival'||v_ival);    select count(*) into v_ival from syslogs;--使用select查询赋值--select ename,sal into v_name,v_sal from emp where empno=&aa;  insert into syslogs values (seq_syslogs.nextval,10,sysdate,'日志条数='||v_ival,user);  dbms_output.put_line('日志条数'||v_ival);     --获取日志序号==11的日志时间和日志内容  select dtm , content  into v_dtm,v_content  from syslogs  where logid=14;    insert into syslogs values (seq_syslogs.nextval,'10',sysdate,'v_dtm='||v_dtm||'v_content='||v_content,user);  dbms_output.put_line('v_dtm='||v_dtm||'v_content='||v_content);  --修改日志序号=11的日志记录人  update syslogs  set whois='PL/SQL.'||v_ival  where logid = 14;    --delete syslogs where logid=15;    --分支流程控制  if v_ival>50 then    dbms_output.put_line('日志需要清理了~');  else    dbms_output.put_line('日志空间正常！');  end if;    --Loop循环  v_ival :=0;  loop      exit when v_ival>3;           --循环体           v_ival := v_ival+1;           dbms_output.put_line('loop循环：'||v_ival);  end loop;    --While循环  v_ival := 0;  while v_ival < 4   loop     --循环体     v_ival := v_ival+1;     dbms_output.put_line('while循环：'||v_ival);  end loop;    --For循环  for v_count in reverse 0..4 loop  --reverse递减      dbms_output.put_line('for循环：'||v_count);     end loop;  commit;--提交事物end;select * from syslogs;  《PL/SQL 异常处理》--PL/SQL异常处理：oracle内置异常，oracle用户自定义异常declare   v_title logtypes.tid%type;   v_ival number(9,2);   --自定义的异常   ex_lesszero exception ;begin  --select title into v_title  --from logtypes     --;  too_many_rows  --where tid = 30 ;  --NO_DATA_FOUND 异常    v_ival := 12/-3;    if v_ival < 0 then    --直接抛出异常    --raise ex_lesszero ;    --使用系统存储过程抛出异常    raise_application_error(/*错误代码，-20000~-20999*/-20003,/*异常描述*/'参数不能小于0！');  end if;    commit;exception  --异常处理代码块   when no_data_found then    dbms_output.put_line('发生系统异常：未找到有效的数据！');  when too_many_rows then    dbms_output.put_line('发生系统异常：查询结果超出预期的一行！');  when ex_lesszero then    dbms_output.put_line('发生用户异常：数值不能为负！'||sqlcode||'异常描述：'||sqlerrm);  when others then --other例如Exception    rollback;    dbms_output.put_line('发生异常！'||sqlcode||'异常的描述：'||sqlerrm);end;  《PL/SQL 游标的使用》declare    --游标的声明    cursor myCur is            select tid,title from logtypes ;    --定义接收游标中的数据变量    v_tid   logtypes.tid%type;    v_title logtypes.title%type;    --通过记录来接受数据    v_typercd myCur%rowtype ;begin    --打开游标    open myCur ;    --取游标中的数据    loop      --遍历游标中的下一行数据      fetch myCur into v_tid,v_title ;      --检测是否已经达到最后一行      exit when myCur%notfound ;      --输出游标中的数据      dbms_output.put_line('读取tid='||v_tid||' title='||v_title);    end loop;    --关闭游标    close myCur;        --打开游标    open myCur ;    loop      fetch myCur into v_typercd ;      exit when myCur%notfound ;      dbms_output.put_line('--//读取tid='||v_typercd.tid||' title='||v_typercd.title);    end loop;    --关闭游标    close myCur ;        --for循环游标    for tmp_record in myCur loop      dbms_output.put_line('++//读取tid='||tmp_record.tid||' title='||tmp_record.title);    end loop;end;  《PL/SQL 存储过程★》--            可以声明入参in，out表示出参，但是无返回值。create or replace procedure prc_writelog(/*日志类型*/ tid in number ,                              /*日志内容*/ content in varchar2 ,                              /*错误码  */ i_ret out number ,                               /*错误描述*/ s_ret out varchar2 )isbegin      insert into syslogs values (seq_syslogs.nextval , tid ,sysdate ,content ,user);      commit;      i_ret := 1 ;      s_ret := '记录日志成功！' ;exception     when others then         rollback ;         i_ret := -1 ;         s_ret := '记录日志失败：'||sqlerrm ;  end;--测试declare  iRet number(4) ;  sRet varchar2(128) ;begin  prc_writelog(10,'测试存储过程',iRet,sRet);  dbms_output.put_line('iRet:'||iRet||'sRet'||sRet);end;select * from syslogs;  《PL/SQL 触发器》 --触发器 是一种基于数据库特定事件的 由数据库自动执行的pl/sql块--触发的事件源：database 【启动、停止、用户联机...】--              表名【insert/update/delete】--触发时机 before/after--语句级、行级（需要知道数据,对数据库运行速度有影响）create or replace trigger tri_logtypesafter insert or update or delete --在所有的表的事件发生后执行on logtypesfor each row --行级 （:new , :old）declare    iret number(4);    sret varchar2(128);begin    --不要有事物的管理    --:new 新数据 记录型     --:old 原有的数据 记录型    --prc_writelog(10,'触发器执行了！',iret,sret);    if inserting then        insert into syslogs values(seq_syslogs.nextval,10,sysdate,'触发器执行添加数据！',user);    elsif updating then        if :new.title <> :old.title then           raise_application_error(-20001,'不允许修改日志类型名称数据！');    --抛出异常        end if;        insert into syslogs values(seq_syslogs.nextval,10,sysdate,'触发器执行更新数据！',user);    elsif deleting then        raise_application_error(-20001,'不允许删除表中的数据！');        insert into syslogs values(seq_syslogs.nextval,10,sysdate,'触发器执行删除数据！',user);    end if;end ;--test!insert into logtypes values(30,'test log');delete from logtypes where tid = 30;update logtypes set title = 'test log' where tid = 30;select * from syslogs order by dtm desc;select * from logtypes ;  《案例》 --创建表create table emp2 (  name varchar2(30),  sal number(8,2));insert into emp2 values('simple',99999);insert into emp2 values(&a,&b);--存储过程案例：--修改员工工资create or replace procedure pro_input(t_name in varchar2,                           t_sal in number)isbegin  update emp2 set sal = t_sal where name=t_name; end;--Test!declarebegin  pro_input('simple',2000);end;select * from emp2;--函数案例：create or replace function fun_test(t_name varchar2) return number is yearSal number(7,2);begin  select sal*12 into yearSal from emp2 where name = t_name;  return yearSal;end;--包案例：create package pac_test is                           --创建一个包pac_test  procedure pro_input(t_name varchar2,t_sal number); --声明该包有一个过程 pro_input  function fun_test(t_name varchar2) return number;  --声明该包有一个函数 fun_testend;--包体案例:create package body pac_test is  procedure pro_input(t_name in varchar2,t_sal in number)  is  begin    update emp2 set sal = t_sal where name=t_name;   end;    function fun_test(t_name varchar2)   return number is yearSal number(7,2);  begin    select sal*12 into yearSal from emp2 where name = t_name;    return yearSal;  end;end ;--调用包中的函数或过程call pac_test.pro_input('summer',1000);call pac_test.fun_testselect pac_test.fun_test('simple') from dual;--案例：select * from emp2;--下面以输入员工工号，显示雇员姓名、工资、个人所得税--税率（0.03）。declare  c_tax_rate number(3,2):=0.03;  --常量，税率  --v_name varchar2(30);  v_name emp2.name%type;  --v_sal number(8,2);  v_sal emp2.sal%type;  v_tax_sal number(8,2);begin  --执行  select name,sal into v_name,v_sal from emp2 where name = &na;  --计算所得税  v_tax_sal:=v_sal*c_tax_rate;  --输出  dbms_output.put_line('姓名：'||v_name||' 工资'||v_sal||' 交税'||v_tax_sal);  end;--pl/sql记录实例declare  --定义一个pl/sql记录类型 emp_record_type ，类型包含2个数据，t_name,t_sal  type emp_record_type is record(t_name emp2.name%type,t_sal emp2.sal%type);  --定义一个 record_test 变量，类型是 emp_record_type  record_test emp_record_type;begin  select name,sal into record_test from emp2 where name = 'simple';  dbms_output.put_line('员工工资:'||record_test.t_sal);end;--pl/sql表实例declare  --定义了一个pl/sql表类型 emp_table_type 该类型是用于存放 emp.name%type元素类型 的数组  -- index by binary_integer 下标是整数  type emp_table_type is table of emp2.name%type index by binary_integer;  --定义一个 table_test 变量  table_test emp_table_type;begin  --table_test(0)下标为0的元素  select name into table_test(0) from emp2 where name='summer';  dbms_output.put_line('员工:'||table_test(0));end;--案例--显示该部门的所有员工和工资declare  --定义游标类型 emp_cursor  type emp_cursor is ref cursor;  --定义一个游标变量  cursor_test emp_cursor;  --定义变量  v_name emp2.name%type;  v_sal emp2.sal%type;begin  --执行  --把cursor_test 和一个select结合  open cursor_test for   select name,sal from emp2;  --循环取出   loop    --fetch取出 游标 给 v_name,v_sal    fetch cursor_test into v_name,v_sal;    --判断工资    if v_sal<1000 then      update emp2 set sal = v_sal+1000 where sal=v_sal;    end if;    --判断cursor_test是否为空    exit when cursor_test%notfound;    dbms_output.put_line('姓名：'||v_name||' 薪水：'||v_sal);  end loop; end;select * from emp2;--《分页》案例：--建表drop table book;create table book(  bookId number(5),  bookName varchar2(50),  publishHouse varchar2(50));--编写过程create or replace procedure pro_pagination( t_bookId in number,                            t_bookName in varchar2,                            t_publishHouse in varchar2)isbegin  insert into book values(t_bookId,t_bookName,t_publishHouse);end;--在java中调用--select * from book;--insert into book values(11,'流星','蝴蝶');--commit;--有输入和输出的存储过程create or replace procedure pro_pagination2( i_id in number,                                             o_name out varchar2,                                             o_publishHouse out varchar2                                             )isbegin  select bookName,publishHouse into o_name,o_publishHouse from book where bookId = i_id;end;--Test!declare  err book.bookname%type;  err2 book.publishhouse%type;begin  pro_pagination2(10,err,err2);  dbms_output.put_line(err||' '||err2);end;--返回结果集的过程--1、创建一个包create or replace package testpackage as   type cursor_test is ref cursor;end testpackage;--2、建立存储过程create or replace procedure pro_pagination3(                                            o_cursor out testpackage.cursor_test)isbegin  open o_cursor for   select * from book;end;--3、如何在java中调用--Test!declare  err testpackage.cursor;begin  pro_pagination2(10,err);  dbms_output.put_line(err);end;<Oracle的分页> select t1.*,rownum rn from (select * from emp) t1;select t1.*,rownum rn from (select * from emp) t1 where rownum<=10;--在分页的时候，可以把下面的sql语句当做一个模板使用select * from (select t1.*,rownum rn from (select * from emp) t1 where rownum<=10) where rn>=6;--开发一个包--1、创建一个包create or replace package testpackage as   type cursor_test is ref cursor;end testpackage;--开始编写分页的过程create or replace procedure fenye(tableName in varchar2,                                  pageSize in number, --每页显示记录数                                  pageNow in number,                                  myRows out number,--总记录数                                  myPageCount out number,--总页数                                  p_cursor out testpackage.cursor_test)is  --定义sql语句 字符串  v_sql varchar2(1000);  --定义2个整数  v_begin number:=(pageNow-1)*pageSize+1;  v_end number:=pageNow*pageSize;begin  v_sql:='select * from (select t1.*,rownum rn from (select * from '||tableName||' order by sal) t1 where rownum<='||v_end||') where rn>='||v_begin||'';  --把游标和sql关联  open p_cursor for v_sql;  --计算myRows和myPageCount  --组织一个sql  v_sql:='select count(*) from '||tableName||'';  --执行sql，并把返回的值，赋给myRows  execute immediate v_sql into myRows;  --计算myPageCount  if mod(myRows,pageSize)=0 then    myPageCount:=myRows/pageSize;  else    myPageCount:=myRows/pageSize+1;  end if;  --关闭游标  --close p_cursor;end;--使用java测试

我的异常网推荐解决方案：oracle存储过程,http://www.myexception.cn/oracle-develop/177537.html