oracle procedure 小实例

fifiaaqwld

　　//根据提供员工编号查询员工信息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;beginselect 1 into v_signfrom dualwhere exists(select count(*) from emp where empno=empno_in);if v_sign=1 thenselect empno,ename,deptno into v_empno,v_ename,v_deptnofrom 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;exceptionwhen others thendbms_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)asv_sign number;//标记变量begin//检查员工是否存在于emp表中select 1 into v_sign from dualwhere exists(select count(*) from emp where empno=empno_in);//如果emp表中有此员工，则进行更新操作if v_sign=1 thenupdate empset sal=sal_inwhere empno=empno_in;commit;end if;//若不存在此员工，或者非法输入，则抛出异常exceptionwhen others thendbms_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)asv_sign number;v_ann_sal number;//年薪变量beginselect 1 into v_signfrom dualwhere 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_salfrom empwhere empno=empno_in;dbms_output.put_line('the annual salary of employee '||empno_in||' are:'||v_ann_sal);end if;//若不存在此员工，或者非法输入，则抛出异常exceptionwhen others thendbms_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=37200SQL> 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)asv_sign number;begin//检查员工的存在性select 1 into v_signfrom dualwhere exists(select count(*) from emp where empno=empno_in);if v_sign=1 thendelete from emp where empno=empno_in;commit;end if;exceptionwhen others thendbms_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