Oracle：简单SQL程序、存储过程、触发器
/* 以下代码是对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; --声明二个普通变量 ame%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; pno%type; ame%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; ame%type; psal emp.sal%type; pno%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; / set serveroutput on /* * 输入部门号，显示对应部门号中总员工数， 和每个员工的姓名和工资信息。 */ declare -- 光标 cursor c_emp(dno number) is select ename, sal from emp where deptno=dno; ame%type; vsal emp.sal%type; vcount number; vdeptno dept.deptno%type; -- 用于保存接收到的部门编号信息 begin -- 得到查询的条件 vdeptno := &input_deptno; -- 显示部门中的总员工数 select count(*) into vcount from emp where deptno=vdeptno; dbms_output.put_line( '总人数为： ' || vcount ); -- 显示每一个员工的姓名与工资信息 open c_emp(vdeptno); loop fetch c_emp into vname, vsal; exit when c_emp%notfound; dbms_output.put_line( ' ' || vname || ' 的工资为： ' || vsal ); end loop; close c_emp; 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; ame%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; ame%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 ame%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; / -- 是否是工作时间 -- to_char(sysdate, 'hh24') -- to_number( to_char(sysdate, 'hh24') ) -- not (to_number( to_char(sysdate, 'hh24') ) between 9 and 17) -- 阻止继续执行 -- raise_application_error(-20000, '现在是非工作时间，不能插入数据！') create or replace trigger mytrigger before insert on emp2 declare -- 变量 begin if not (to_number( to_char(sysdate, 'hh24') ) between 9 and 17) then raise_application_error(-20000, '现在是非工作时间，不能插入数据！'); 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]
转载于:.html
