四个简单的Oracle语句,高分急求~

发布网友发布时间：2022-04-07 19:18

共4个回答

懂视网时间：2022-04-07 23:40

SELECT ‘alter table ‘|| t.table_name||‘ disable constraint ‘||t.constraint_name||‘;‘ 
FROM user_constraints t WHERE t.constraint_type = ‘R‘;

SELECT ‘ALTER TABLE ‘|| table_name || ‘ ENABLE CONSTRAINTS ‘ || t.constraint_name ||‘;‘
FROM user_constraints t
WHERE t.constraint_type = ‘R‘ ;

ALTER TABLE TBL_NAME DISABLE CONSTRAINTS FK_TBL_ID;

ALTER TABLE TBL_NAME ENABLE CONSTRAINTS FK_TBL_ID;

ALTER TABLE TBL_NAME ENABLE ALL TRIGGERS;

ALTER TABLE TBL_NAME DISABLE ALL TRIGGERS;

INSERT INTO tbl_import SELECT * FROM tbl_export;

GRANT SELECT ON tbl_export TO schema_out;

几个有用的oracle语句

标签：

热心网友时间：2022-04-07 20:48

A、编写SQL语句查询男同学的XML平均成绩，要求列出姓名和成绩两列；
是要列出单指姓名和成绩：
select a.sname,b.Score from tbstudent a,Tbscore b where a.sno = b.sno and a.ssex = '男' and b.subject = 'xml'
还是姓名，后面都是平均成绩：
select a.sname,b.avg from tbstudent a,(select avg(b.Score) avg from tbstudent a,Tbscore b where a.sno = b.sno
and a.ssex = '男'
and b.subject = 'xml' ) b
查平均成绩：
select avg(b.Score) from tbstudent a,Tbscore b where a.sno = b.sno and a.ssex = '男' and b.subject = 'xml'

B、编写SQL语句查询出参加考试的各科成绩都及格的学员学号、平均成绩；

select a. sno a,avg(b.Score) cheng_ji from tbstudent a,Tbscore b where a.sno = b.sno
and b.Score >=60 group by a.sno

C、用一条SQL语句，查询出科目、该科目成绩60分以上的学生个数；
select b.Subject, count(a.sno) geshu from tbstudent a,Tbscore b where a.sno = b.sno
and b.Score >=60 group by b.Subject

D、

CREATE OR REPLACE PROCEDURE plus
(
i_subject IN VARCHAR2, --课程名

)
AS
v_count number;
v_failcount number;
BEGIN
udpdate Tbscore set Score = Score + 3 where Subject = i_subject and Score < 98;
commit;

select count(*) into v_count from Tbscore where Subject = i_subject
select count(*) into v_failcount from Tbscore where Subject = i_subject and Score < 60

if v_failcount/v_count < 0.7 then
plus(i_subject);
end if;

END plus;

都是txt下写的，存储过程有什么问题，就贴出来，没oracle环境，只能尽量这样写了

热心网友时间：2022-04-07 22:06

A.
select a.sname,b.sore from tbstudent as a,tbscore as b where a.sno=b.sno and b.subject='XML'

热心网友时间：2022-04-07 23:40

A
select Sname,Score from tbstudent,tbscore
where tbstudent.Sno=tbscore.Sno and Ssex='男' and Subject='XML'

B
select Sno,avg(Score) from tbscore
where Score>=60
group by Sno

C
select Subject,count(Sno) from tbscore
where Score>=60
group by Subject

D
CREATE OR REPLACE PROCEDURE plus(
plusSubject IN varchar2
)
as
num1 number;
num2 number;
begin
num1:=10;
num2:=1;

while num1>=num2*10/7 loop

select count(Sno) into num1 from tbscore
where Subject=plusSubject;

select count(Sno) into num2 from tbscore
where Score>=60 and Subject=plusSubject;

update tbscore set Score:=Score+3
where Score<98 and Subject=plusSubject;

end loop;
commit;
end;