oracle 存储过程 ,触发器练习

2017-09-30 21:54:36来源:CSDN作者:dhdhxgx人点击

分享

/*
以下代码是对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,ename,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,pename,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,job,sal into pename,pjob,psal from emp where empno=pempno;
end;
/

declare
pename emp.ename%type;
pjob emp.job%type;
psal emp.sal%type;
begin
findEmpNameAndSalAndJob(7902,pename,pjob,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,ename,job,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:/app/Administrator/product/11.2.0/dbhome_1/jdbc/lib/ojdbc5.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 

//--------------------------------------------------------------------------------------------------

微信扫一扫

第七城市微信公众平台