谁能帮我做一下这个机试试题?用Oracle不用全做对,70分就好!
来源:学生作业帮助网 编辑:作业帮 时间:2024/09/09 08:03:08
![谁能帮我做一下这个机试试题?用Oracle不用全做对,70分就好!](/uploads/image/z/12975054-6-4.jpg?t=%E8%B0%81%E8%83%BD%E5%B8%AE%E6%88%91%E5%81%9A%E4%B8%80%E4%B8%8B%E8%BF%99%E4%B8%AA%E6%9C%BA%E8%AF%95%E8%AF%95%E9%A2%98%3F%E7%94%A8Oracle%E4%B8%8D%E7%94%A8%E5%85%A8%E5%81%9A%E5%AF%B9%2C70%E5%88%86%E5%B0%B1%E5%A5%BD%21)
谁能帮我做一下这个机试试题?用Oracle不用全做对,70分就好!
谁能帮我做一下这个机试试题?用Oracle
不用全做对,70分就好!
谁能帮我做一下这个机试试题?用Oracle不用全做对,70分就好!
create table student(
stuID NUMBER(10) primary key,
stuName VARCHAR2(20) not null,
password VARCHAR2(20),
major VARCHAR2(50),
email VARCHAR2(30) unique,
gender char(1) default '0',
birthday date
);
create table book(
bid number(10) primary key,
title varchar2(20),
author varchar2(20),
price number(6,2));
create table borrow(
borrowid number(10) primary key,
stuid number(10),
id number(10),
t_time date default sysdate,
b_time date);
alter table student add constraint ch_passwd check (password>6);
alter table student add constraint ch_gender check (gender in ('0','1'));
alter table borrow add constraint fk_borrow_student foreign key (stuid) references student(stuID);
alter table borrow add constraint fk_borrow_book foreign key (id) references book(bid);
alter table borrow add constraint ch_borrow check (b_time < t_time);
1.insert into student values(1,'jack','1234567','computer','yyyy3322@163.com','0',to_date('19950505','yyyymmdd'));
insert into book values(1,'oracle','thomas kyte',200);
2.select t.stuid,t.stuname,(case when t.gender = 0 then '鐢?#39; else '濂?#39; end) from student t,borrow t1
where t.stuid = t1.stuid
and to_char(t1.t_time,'yyyy-mm-dd') between '2012-12-15' and '2013-1-8';
3.select max(count(t.stuid)) from borrow t group by t.stuid;
4.select t.stuname,t2.title,t1.t_time,t1.b_time from student t,borrow t1,book t2 where t.stuid = t1.stuid and t1.id =
t2.bid and t2.author = '瀹夋剰濡?#39;;
5.select t.* from student t where t.stuid not in (select t1.stuid from borrow t1 group by t1.stuid);
6.create or replace package login
is PROCEDURE login_student
(student_id in NUMBER,
passwd in varchar2,
return_flag out varchar2);
end;
create or replace package body login is
PROCEDURE login_student(student_id in NUMBER,
passwd in varchar2,
return_flag out varchar2) as
student_num1 number(2) := 0;
student_num2 number(2) := 0;
begin
select count(1)
into student_num1
from student t
where t.stuid = student_id;
if student_num1 = 1 then
select count(1)
into student_num2
from student t
where t.stuid = student_id
and t.password = passwd;
if student_num2 = 1 then
select t.stuname
into return_flag
from student t
where t.stuid = student_id
and t.password = passwd;
else
return_flag := '0';
end if;
else
return_flag := '-1';
end if;
end;
end login;
begin
login.login_student(student_id => :student_id,
passwd => :passwd,
return_flag => :return_flag);
end;
杩欓噷鏄?祴璇曡?:student_id :passwd 綘瑕佽緭鍏ョ殑鍊稽/p>
鍩虹?鏁版嵁琛?/p>
7.
CREATE OR REPLACE FUNCTION del_book (BOOKID NUMBER)
RETURN NUMBER
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
delete from borrow t where t.id = BOOKID;
COMMIT;
delete from book t1 where t1.bid = BOOKID;
commit;
END;