/* 以下代码是对emp表进行显示宽度设置 */ col empno for 9999; col ename for a10; col job for a10; col mgr for 9999; col hiredate for a12; col sal for 9999; col comm for 9999; col deptno for 99; col tname for a12; set pagesize 50;
//------------------------------------------------------------------------------------------------------
使用loop循环显示1-10【loop循环】 declare --声明变量 i number(2); begin i := 1; --以下代码是循环 loop exit when i>10; dbms_output.put_line(i); i := i+1; end loop; end; /
使用while循环显示10-20【while循环】 declare i number(2) := 10; begin while i<=20 loop dbms_output.put_line(i); i := i+1; end loop; end; /
使用for循环显示20-30【for循环】 declare i number; begin for i in 20..30 loop dbms_output.put_line(i); end loop; end; /
使用无参光标cursor,查询所有员工的姓名和工资【如果需要保存多行记录时,使用光标cursor】 declare --定义一个cursor,里面装多条记录 cursor cemp is select ename,sal from emp; --声明二个普通变量 pename emp.ename%type; psal emp.sal%type; begin --打开cursor open cemp; --循环 loop --将cursor下移,将用户名和工资存入二个自定义普通变量中 fetch cemp into pename,psal; --判断是否该退出循环,切记 exit when cemp%notfound; --显示 dbms_output.put_line(pename||'的工资是'||psal); end loop; --关闭cursor close cemp; end; /
使用无参光标,给员工涨工资,ANALYST涨1000,MANAGER涨800,其它涨400【编号,姓名,职位,薪水】 declare cursor cemp is select empno,ename,job,sal from emp; pempno emp.empno%type; pename emp.ename%type; pjob emp.job%type; psal emp.sal%type; begin open cemp; loop fetch cemp into pempno,pename,pjob,psal; exit when cemp%notfound; --if是PLSQL if pjob='ANALYST' then --update是SQL update emp set sal=sal+1000 where empno=pempno; elsif pjob='MANAGER' then update emp set sal=sal+800 where empno=pempno; else update emp set sal=sal+400 where empno=pempno; end if; end loop; commit; close cemp; end; /
使用带参光标,查询20号部门的员工姓名和工资,工资都加800 declare --定义一个带参cursor cursor cemp(pdeptno number) is select empno,sal from emp where deptno=pdeptno; pename emp.ename%type; psal emp.sal%type; pempno emp.empno%type; begin --打开光标,同时传入实际参数 open cemp(20); loop fetch cemp into pempno,psal; exit when cemp%notfound; update emp set sal=sal+800 where empno=pempno; end loop; --关闭光标 close cemp; end; /
oracle系统内置例外,被0除异常【zero_divide】 declare i number; s number; begin i := 10; s := i/0; exception when zero_divide then dbms_output.put_line('自已捕获系统内置例外'); end; /
用户自定义例外,没有找到员工例外【no_emp_found】 declare cursor cemp(pempno number) is select ename from emp where empno=pempno; pename emp.ename%type; --声明自定义例外 no_emp_found exception; begin open cemp(1111); loop fetch cemp into pename; --如果没有找到员工 if cemp%notfound then --抛例外 raise no_emp_found; end if; end loop; close cemp; exception when no_emp_found then dbms_output.put_line('查无此员工'); end; /
创建无参存储过程hello,无返回值
create or replace procedure hello as begin dbms_output.put_line('这就是存储过程'); end; /
删除存储过程hello drop procedure hello;
调用存储过程方式一【exec 存储过程名】 exec hello;
调用存储过程方式二【PLSQL程序】 begin --调用存储过程 hello; end; /
调用存储过程方式三【JDBC】 CallableStatement
创建有参存储过程raiseSalary(编号),为7369号员工涨10%的工资,并显示出涨前和涨后的工资【演示in的用法,默认in】 create or replace procedure raiseSalary(pempno number)
as --as看作declare,但不能出现declare,声明变量 psal emp.sal%type; begin --查询编码为7369号员工的工资 select sal into psal from emp where empno=pempno; --显示 dbms_output.put_line('7369号员工涨前工资'||psal); dbms_output.put_line('7369号员工涨后工资'||psal*1.1); end; / exec raiseSalary(7369);
创建无参存储函数myshow,有返回值 create or replace function myshow return varchar2 as begin return '哈哈'; end; /
删除存储函数myshow drop function myshow;
调用存储函数方式一【PLSQL程序】
declare value varchar2(6); begin value := myshow(); --value := myshow;可以 dbms_output.put_line(value); end; /
调用存储函数方式二【JDBC】 CallableStatement
创建有参存储函数findEmpIncome(编号),查询7369号员工的年收入【演示in的用法,默认in】
create or replace function findEmpIncome(pempno in number) return number as --年收入 income number; begin select sal*12+NVL2(comm,comm,0) into income from emp where empno=pempno; --返回年收入 return income; end; /
declare income number; begin income := findEmpIncome(7369); dbms_output.put_line('年收入是'||income); end; /
创建有参存储函数findEmpNameAndSal(编号),查询7902号员工的的姓名和月薪,【返回多个值,演示out的用法】 当返回2个或多个值,必须使用out符号 当返回1个值,就无需out符号
create or replace function findEmpNameAndSal(pempno in number,pename out varchar2) return number as psal emp.sal%type; begin select ename,sal into pename,psal from emp where empno=pempno; --返回月薪 return psal; end; /
---------------------------------------相互转值
declare psal emp.sal%type; pename emp.ename%type; begin psal := findEmpNameAndSal(7902,pename); dbms_output.put_line('7902号员工的姓名'||pename||',薪水是'||psal); end; /
创建有参存储过程findEmpNameAndSalAndJob(编号),查询7902号员工的的姓名,职位,月薪【演示out的用法】 create or replace procedure findEmpNameAndSalAndJob(pempno in number,pename out varchar2,pjob out varchar2,psal out number) as begin select ename,psal from emp where empno=pempno; end; /
declare pename emp.ename%type; pjob emp.job%type; psal emp.sal%type; begin findEmpNameAndSalAndJob(7902,psal); dbms_output.put_line('7902号员工的姓名是'||pename||':'||pjob||':'||psal); end; /
什么情况下使用存储过程?什么情况下使用存储函数? 项目中的原则: A)如果只有一个返回值:用函数 B)如果无返回值,或超过1个以上的返回值,用过程
创建语句级触发器insertEmpTrigger,当对emp表进行insert操作前,显示"hello world" create or replace trigger insertEmpTrigger before insert on emp begin dbms_output.put_line('插入记录之前执行'); end; /
删除触发器insertEmpTrigger drop trigger insertEmpTrigger;
使用insert语句操纵表,引起触发器工作 insert into emp(empno,sal) values(1122,'JACK','IT',5000);
insert into emp select * from new_emp;
创建语句级触发器deleteEmpTrigger,当对emp表进行delete操作后,显示"world hello" create or replace trigger deleteEmpTrigger after delete on emp begin dbms_output.put_line('删除记录之后执行'); end; /
周一到周五,且9-17点能向数据库插入数据,否则【raise_application_error('-20000','例外原因')】
分析: A)周六,周日 不管何时 不能插入数据 B)周一到周五 9-17之外,不包括9和17点,不能插入数据 create or replace trigger securityEmpTrigger before insert on emp declare pday varchar2(9); phour number(2); begin select to_char(sysdate,'day') into pday from dual; --隐式将varchar2转成number select to_char(sysdate,'hh24') into phour from dual; --判断 if (pday in ('星期六','星期日')) or (phour not between 9 and 17) then --抛例外,该例是系统的 raise_application_error('-20999','不是工作时间,不能操作数据库'); end if; end; /
创建行级触发器,涨后工资这一列,确保大于涨前工资【for each row/:new.sal/:old.sal】
create or replace trigger checkSalaryTrigger after update of sal on emp for each row begin --如果更新后的值<更新前的值 if :new.sal < :old.sal then --抛例外 raise_application_error('-20888','工资不能越涨越低'); end if; end; /
update emp set sal=sal+1 where ename='SMITH';
错误编号的范围:[20000-20999]
//-------------------------------------------------------------------------------------------------- *** Java 调用 Oracle 存储 过程 与 函数 CallableStatement String sql = "{call raiseSalary(?,?)}";//过程 String sql = "{?=call findEmpNameAndSal(?,?)}";//函数 cstmt.setInt(1,7369); cstmt.registerOutParameter(2,Types.VARCHAR); cstmt.execute(); String message = cstmt.getString(2);
过程:findEmpNameAndSalAndJob() 函数:findEmpNameAndSal() 找oracle11的对应的JDBC驱动:E:appAdministratorproduct11.2.0dbhome_1jdbclibojdbc5.jar
//--------------------------------------------------------------------------------------------------
Java 调用 Oracle 存储 大对象【CLOB 与 BLOB】 4G private static String driver = "oracle.jdbc.driver.OracleDriver"; //不一定是1521,但优先选用1521端口号 private static String url = "jdbc:oracle:thin:@localhost:1521:orcl"; private static String username = "scott"; private static String password = "123456"; 文本:CLOB(Character) 多媒体:BLOB(Binary)
//--------------------------------------------------------------------------------------------------
***** jquery/ajax + struts2 + javabean + hibernate + oracle 需求: 1)使用异步方式,检查用户名是否存在,参见<<综合练习.JPG>> 2)注册英雄 3)分页查询所有英雄 hibernate query.setFirstResult(0); query.setMaxResult(6) 框架自动将分页API转成oracle专有的页面语句 oracle //-------------------------------------------------------------------------------------------------- (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|