之前建表的语句找不到了,用这个存储过程代替下~ CREATE OR REPLACE PROCEDURE findStu_info(stu_id IN INT ) AS stuName VARCHAR2(20); stuSubject VARCHAR2(20); stuScore INT; BEGIN EXECUTE IMMEDIATE 'SELECT theName, subject, theScore FROM name_subject_score WHERE stuid = :1' INTO stuName, stuSubject, stuScore USING stu_id; dbms_output.put_line( stuName ||'''s '|| stuSubject || '''s score is ' || stuScore); EXCEPTION WHEN OTHERS THEN dbms_output.put_line('stu_id is '|| stu_id); END;
with test as ( select 'a' as thename,'english' as subject,'90' as thescore,'1' as stuid from dual union all select 'b' as thename,'english' as subject,'90' as thescore,'2' as stuid from dual union all select 'c' as thename,'english' as subject,'90' as thescore,'3' as stuid from dual union all select 'a' as thename,'english' as subject,'90' as thescore,'4' as stuid from dual union all select 'b' as thename,'english' as subject,'90' as thescore,'5' as stuid from dual union all select 'c' as thename,'english' as subject,'90' as thescore,'6' as stuid from dual union all select 'a' as thename,'english' as subject,'90' as thescore,'7' as stuid from dual union all select 'b' as thename,'english' as subject,'90' as thescore,'8' as stuid from dual ) select thename, subject, thescore, min(stuid) keep(DENSE_RANK first ORDER BY stuid) over(partition by thename) as stuid from test
CREATE OR REPLACE PROCEDURE findStu_info(stu_id IN INT )
AS
stuName VARCHAR2(20);
stuSubject VARCHAR2(20);
stuScore INT;
BEGIN
EXECUTE IMMEDIATE
'SELECT theName, subject, theScore FROM name_subject_score WHERE stuid = :1'
INTO stuName, stuSubject, stuScore
USING stu_id;
dbms_output.put_line( stuName ||'''s '|| stuSubject || '''s score is ' || stuScore);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('stu_id is '|| stu_id);
END;
select 'a' as thename,'english' as subject,'90' as thescore,'1' as stuid from dual
union all
select 'b' as thename,'english' as subject,'90' as thescore,'2' as stuid from dual
union all
select 'c' as thename,'english' as subject,'90' as thescore,'3' as stuid from dual
union all
select 'a' as thename,'english' as subject,'90' as thescore,'4' as stuid from dual
union all
select 'b' as thename,'english' as subject,'90' as thescore,'5' as stuid from dual
union all
select 'c' as thename,'english' as subject,'90' as thescore,'6' as stuid from dual
union all
select 'a' as thename,'english' as subject,'90' as thescore,'7' as stuid from dual
union all
select 'b' as thename,'english' as subject,'90' as thescore,'8' as stuid from dual
)
select thename,
subject,
thescore,
min(stuid) keep(DENSE_RANK first ORDER BY stuid) over(partition by thename) as stuid
from test