存储过程学习.doc

1、 定义一个过程，创建一个记录，把emp表的员工号，姓名，工资，工作都查询出来，并打印：实现：declare type emp_record_type is record( r_eno emp.empno%type, r_name emp.ename%type, r_sal emp.sal%type, r_job emp.job%type ); emp_record emp_record_type;begin select empno,ename,sal,job into emp_record from scott.emp where empno=7788; dbms_output.put_line(employee name:|emp_record.r_name|employee sal:|emp_record.r_sal); end;2、 先创建一个变量，再定义一个过程，通过这个过程，把员工号为7788的人的名字赋值给这个事先定义好的变量中。然后打印出查询结果实现：SQL var name varchar2(20);SQL begin select ename into :name from emp where empno=7788; end;SQL print name;NAME-SCOTT也可以这样：SQL begin select ename into :name from emp where empno=7788; dbms_output.put_line(:name); end;/SCOTTPL/SQL 过程已成功完成。3、 写一个过程，当我们输入员工号后，打印出该员工号的名字，工作，还有薪水：实现：declare v_name emp.ename%type;v_sal emp.sal%type;v_job emp.job%type;v_id emp.empno%type;beginv_id:=&inputid;select ename,sal,job into v_name,v_sal,v_job from emp where empno=v_id;dbms_output.put_line(name:|v_name); dbms_output.put_line(sal:|v_sal); dbms_output.put_line(job:|v_job);exception when no_data_found then dbms_output.put_line(NOT FOUND RECORD!);end;4、 写一个过程，输入一个员工号和新的工资，实现显示旧的工资，新的工资；实现：declare v_oldsal emp.sal%type; v_newsal emp.sal%type; v_id emp.empno%type;begin v_newsal:=&newsal; v_id:=&inputid; select sal into v_oldsal from emp where empno=v_id; update emp set sal=v_newsal where empno=v_id; dbms_output.put_line(sal:|v_oldsal); dbms_output.put_line(newsal:|v_newsal);EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(NOT FOUND RECORD!);end;5、 写一个过程，当输入一个员工号，判断这个员工的工作是不是clerk，如果是，薪水增加10%，如果不是，薪水增加8%，并且显示现在和原来的薪水。实现：declare v_oldsal emp.sal%type; v_newsal emp.sal%type; v_id emp.empno%type; v_job emp.job%type;begin v_id:=&inputid; select job,sal into v_job,v_oldsal from emp where empno=v_id; if v_job=CLERK then v_newsal:=v_oldsal*1.1 ; else v_newsal:=v_oldsal*1.08; end if; update emp set sal = v_newsal where empno=v_id; dbms_output.put_line(oldsal:|v_oldsal); dbms_output.put_line(newsal:|v_newsal);exception when no_data_found then dbms_output.put_line(no data found); end;6、 创建一个过程，输入员工编号,查询其工资,如果其职位是CLERK,则工资增加10%,如果是SALESMAN工资增加8%,其它的加5%,显示修改前后的工资数。实现：declare v_oldsal emp.sal%type; v_newsal emp.sal%type; v_id emp.empno%type; v_job emp.job%type;begin v_id:=&inputid; select job,sal into v_job,v_oldsal from emp where empno=v_id; if v_job=CLERK then v_newsal:=v_oldsal*1.1 ; elsif v_job=SALESMAN then v_newsal:=v_oldsal*1.08; else v_newsal:=v_oldsal*1.05; end if; update emp set sal = v_newsal where empno=v_id; dbms_output.put_line(oldsal:|v_oldsal); dbms_output.put_line(newsal:|v_newsal);exception when no_data_found then dbms_output.put_line(no data found); end;继续用条件分支语句完成：declare v_oldsal emp.sal%type; v_newsal emp.sal%type; v_id emp.empno%type; v_job emp.job%type;begin v_id:=&inputid; select job,sal into v_job,v_oldsal from emp where empno=v_id; case v_job when CLERK THEN v_newsal:=v_oldsal*1.1 ; when SALESMAN then v_newsal:=v_oldsal*1.08; else v_newsal:=v_oldsal*1.05; end case; update emp set sal = v_newsal where empno=v_id; dbms_output.put_line(oldsal:|v_oldsal); dbms_output.put_line(newsal:|v_newsal);exception when no_data_found then dbms_output.put_line(no data found); end;7、 定义一个过程，以JOB为参数，查询该JOB的最高工资、最低工资、平均工资。实现：create or replace procedure xs_sal(v_job emp.job%type) as v_maxsal emp.sal%type; v_avgsal emp.sal%type; v_minsal emp.sal%type;begin select max(sal) into v_maxsal from emp where job=v_job; select avg(sal),min(sal) into v_avgsal,v_minsal from emp where job=v_job; dbms_output.put_line(dept|v_job|avg sal|v_avgsal); dbms_output.put_line(dept|v_job|maxsal|v_maxsal); dbms_output.put_line(dept|v_job|min sal|v_minsal); exception when no_data_found then dbms_output.put_line(not found record);end xs_sal;8、 定义一个存储过程，插入一条记录，如果已经存在相同的ID，就报错，不存在的话，就插入，最后显示你插入的值:实现：create or replace procedure in_record(i_no in emp.empno%type,i_name in emp.ename%type,i_dept in emp.deptno%type default 20)as d_no emp.empno%type; d_name emp.ename%type; d_dept emp.deptno%type;begin insert into emp (empno,ename,deptno) values (i_no,i_name,i_dept); select empno,ename,deptno into d_no,d_name,d_dept from emp where empno=