oracle 过程和函数经典练习题

知识点：存储过程和函数 
练习：
存储过程和函数：
1.	创建包含以下列的salary_details表：
EMPCODE		VARCHAR2(10),
WORKINGDAYS	NUMBER,
SALARY		NUMBER
编写一个过程，根据EMPCODE计算雇员在扣除税款(税率为5%)后的净收入，并将净收入显示出来.

create table salary_details(
    EMPCODE		VARCHAR2(10),--员工编码
    WORKINGDAYS	NUMBER,--工作日
    SALARY		NUMBER  --工资
)

create or replace 
procedure mypro6

is
e_code salary_details.EMPCODE%type;
v_salary number;
begin 
  select SALARY into v_salary from salary_details 
  where EMPCODE=e_code;
  v_salary:=v_salary*0.95;
  dbms_output.put_line(v_salary);
end;

2.	利用在问题1种创建的salary_details表，创建work_days函数，接受EMPCODE雇员编号，并检查所有雇员的工作天数。标准工作天数为22天，雇员每少工作一天从其工资中扣除50元。函数范围雇员最后的工资，工作天数多于标准天数不加工资，工资扣完为止，不可为负值。

CREATE OR REPLACE FUNCTION work_days(ecode NUMBER)
   RETURN NUMBER
   AS
   days NUMBER;
   sal  NUMBER;
BEGIN
   SELECT workdays,salary INTO days,sal FROM salary_detail
          WHERE empcode=ecode;
   days := 22-days;       --少工作的天数
   IF days>=1 THEN
      sal := sal-50*days; --实际薪水
      IF sal<0 THEN
         sal := 0;
      END IF;
   END IF;
   RETURN sal;   
END;

SELECT * FROM salary_detail;
SELECT work_days(1004) FROM dual;


3.	有表student(name varchar2(20),age number(3,0),address varchar2(30))
       在插入部分记录之后，请建立存储过程getInfo,并可通过参数学生姓名来得到学生信息。
       
--建立表
create table student(name varchar2(20),age number(3,0),address varchar2(30))
--插入信息
insert into student values('aaa',56,'abcdefg');
insert into student values('bbb',23,'abcdefg');
insert into student values('ccc',35,'abcdefg');
insert into student values('ddd',56,'abcdefg');
insert into student values('eee',23,'abcdefg');
insert into student values('fff',35,'abcdefg');
insert into student values('ggg',56,'abcdefg');
insert into student values('hhh',23,'abcdefg');
insert into student values('iii',35,'abcdefg');
--查询表
select * from student
--建立过程
create or replace procedure pro_getinfo(sname varchar2)
as
s_address varchar2(20);
begin

select address into s_address from student
where name = sname;
dbms_output.put_line(s_address);
end;
declare
sname varchar2(20) := 'aaa';
begin
pro_getinfo( sname);
end;

4.	有表
studentinfo(stuid varchar2(5),stuname varchar2(20),stuphone varchar2(12),stuaddress varchar2(50));
        stumark(stuid varchar2(5),clsid varchar2(10),mark number);
        插入部分数据后
        创建包规范
	      定义函数来获得某个学生，某门课程的成绩
        定义函数来获得某个学生所有课程的平均成绩
      	定义函数来获得某个学生最差的科目
        创建包体来实现上述函数。
        编写PL/SQL块测试上述函数
create table studentinfo(stuid varchar2(5),stuname varchar2(20),stuphone varchar2(12),stuaddress varchar2(50));
create table stumark(stuid varchar2(5),clsid varchar2(10),mark number);

insert into studentinfo values('1','yang','17865277691','威海市');
insert into stumark values('1','yong',89);
insert into stumark values('2','chen',90);

create or replace package pack_test
as
function fun_getinfo(ids varchar2) return stumark%rowtype;
end pack_test;

create or replace package body pack_test as
function fun_getinfo(ids varchar2) return stumark%rowtype is info stumark%rowtype;
begin
select * into info from stumark where stuid = ids;
dbms_output.put_line(info.stuid||info.clsid);
return info;
end;

end pack_test;

declare
info varchar2(30);
begin
select pack_test.fun_getinfo('1') into info from dual ;
end;

declare
info stumark%rowtype;
begin
info := pack_test.fun_getinfo('1');
end;
----------------------根据一个人名获取他的各科成绩------------------------------------
--建立一个包
select * from view_stu
create or replace package pack_test
is
cursor cur_test(sname varchar2) return view_stu%rowtype;
procedure proc_test(sname varchar2);
end pack_test;
create or replace package body pack_test
is
cursor cur_test(sname varchar2) return view_stu%rowtype is select * from view_stu where stuname = sname;
procedure proc_test(sname varchar2) is
begin
dbms_output.put_line('姓名是'||sname);
dbms_output.put_line('--------------------------------------');
for ee in cur_test(sname)
loop
dbms_output.put_line('科目是'||ee.clsid||'分数是'||ee.mark);
end loop;
end;
end;
begin
pack_test.proc_test('aaa');
end;
--所有学生的信息加上该生vb的考试成绩.
select a.*,b.mark from studentinfo a,stumark b
where a.stuid = b.stuid and b.clsid = 'vb';