oracle procedure 小实例

//根据提供员工编号查询员工信息 create or replace procedure pro_show_employee(empno_in in emp.empno%type) as v_sign number; v_empno emp.empno%type; v_ename emp.ename%type; v_deptno emp.deptno%type; begin select 1 into v_sign from dual where exists(select count(*) from emp where empno=empno_in); if v_sign=1 then select empno,ename,deptno into v_empno,v_ename,v_deptno from emp where empno=empno_in; dbms_output.put_line('information of '||empno_in||' are:'); dbms_output.put_line('empno:'||v_empno||',ename:'||v_ename||',deptno:'||v_deptno); end if; exception when others then dbms_output.put_line('no data found'); end pro_show_employee; // SQL> exec pro_show_employee(7369); information of 7369 are: empno:7369,ename:SMITH,deptno:20 // SQL> exec show_employee(112); no data found //根据提供的empno和sal跟新员工薪水。 create or replace procedure pro_update_emp(empno_in in emp.empno%type,sal_in in emp.sal%type) as v_sign number;//标记变量 begin //检查员工是否存在于emp表中 select 1 into v_sign from dual where exists(select count(*) from emp where empno=empno_in); //如果emp表中有此员工，则进行更新操作 if v_sign=1 then update emp set sal=sal_in where empno=empno_in; commit; end if; //若不存在此员工，或者非法输入，则抛出异常 exception when others then dbms_output.put_line('employee not exists,check you empno.'); rollback; end pro_update_emp; // SQL> select * from emp where empno=7788; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 // SQL> exec pro_update_emp(7788,3100); PL/SQL procedure successfully completed // SQL> select * from emp where empno=7788; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7788 SCOTT ANALYST 7566 1987-4-19 3100.00 20 //根据提供员工empno，计算员工年薪 create or replace procedure pro_ann_sal(empno_in in emp.empno%type) as v_sign number; v_ann_sal number;//年薪变量 begin select 1 into v_sign from dual where exists(select count(*) from emp where empno=empno_in); if v_sign=1 then //将月薪*12加上奖金*12赋值给年薪变量 select sal*12+nvl(comm,0)*12 into v_ann_sal from emp where empno=empno_in; dbms_output.put_line('the annual salary of employee '||empno_in||' are:'||v_ann_sal); end if; //若不存在此员工，或者非法输入，则抛出异常 exception when others then dbms_output.put_line('employee not exists,check you empno.'); end pro_ann_sal; // SQL> exec pro_ann_sal(7654); the annual salary of employee 7654 are:31800 // SQL> exec pro_ann_sal(7788); the annual salary of employee 7788 are:37200 // SQL> select sal,comm from emp where empno=7788; SAL COMM --------- --------- 3100.00 3100*12=37200 SQL> select sal,comm from emp where empno=7654; SAL COMM --------- --------- 1250.00 1400.00 1250*12+1400*12=15000+16800=31800 //根据提供员工号empno删除他 create or replace procedure pro_delete_emp(empno_in in emp.empno%type) as v_sign number; begin //检查员工的存在性 select 1 into v_sign from dual where exists(select count(*) from emp where empno=empno_in); if v_sign=1 then delete from emp where empno=empno_in; commit; end if; exception when others then dbms_output.put_line('employee '||empno_in||' is not exists'); rollback; end pro_delete_emp; -- SQL> exec pro_delete_emp(6688); PL/SQL procedure successfully completed //查看6688这个员工是否存在 SQL> select count(*) from emp where empno=6688; COUNT(*) ---------- 0