plsql包小结

已于 2022-07-26 20:04:11 修改
阅读量880 收藏 4
点赞数 3
分类专栏： PLSQL 文章标签： java 开发语言 数据库 oracle sql
于 2022-07-24 14:51:22 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/EbowTang/article/details/125956871
版权

包就是一个把各种逻辑相关的类型、常量、变量、异常和子程序组合在一起的模式对象。包通常由两个部分组成：包说明和包体，但有时包体是不需要的。包说明(简写为spec)是应用程序接口；它声明了可用的类型、变量、常量、异常、游标和子程序，包体(Body)部分完全定义游标和子程序，并对说明中的内容加以实现。

如下例所示，我们可以认为说明部分是一个可选接口，而包体是一个"黑盒"。我们可以调试、增强或替换一个包体而不同改变接口(包说明)。

 我们可以从SQL*Plus中使用CREATE PACKAGE语句来创建一个包。语法如下：

CREATE [OR REPLACE] PACKAGE package_name

  [AUTHID {CURRENT_USER | DEFINER}]

  {IS | AS}

  [PRAGMA SERIALLY_REUSABLE;]

  [collection_type_definition ...]

  [record_type_definition ...]

  [subtype_definition ...]

  [collection_declaration ...]

  [constant_declaration ...]

  [exception_declaration ...]

  [object_declaration ...]

  [record_declaration ...]

  [variable_declaration ...]

  [cursor_spec ...]

  [function_spec ...]

  [procedure_spec ...]

  [call_spec ...]

  [PRAGMA RESTRICT_REFERENCES(assertions) ...]

END [package_name];

[CREATE [OR REPLACE] PACKAGE BODY package_name {IS | AS}

  [PRAGMA SERIALLY_REUSABLE;]

  [collection_type_definition ...]

  [record_type_definition ...]

  [subtype_definition ...]

  [collection_declaration ...]

  [constant_declaration ...]

  [exception_declaration ...]

  [object_declaration ...]

  [record_declaration ...]

  [variable_declaration ...]

  [cursor_body ...]

  [function_spec ...]

  [procedure_spec ...]

  [call_spec ...]

[BEGIN

  sequence_of_statements]

END [package_name];]

在包说明部分声明的内容都是公有的，对应用程序是可见的。我们必须在所有的其他内容(除了用于为一个特殊的函数命名的编译指示；这样的编译指示必须跟在函数说明之后)声明之后才可以声明子程序。

包体中的内容有私有的，它实现了说明部分定义的细节内容，并且对应用程序是不可见的。紧跟着包体声明部分的是一个可选的初始化部分，它用于初始化包中的变量等。

AUTHID语句决定了是否是所有的打包子程序都按定义者权限(默认)或调用者权限执行，其中涉及到的模式对象是在定义者的模式中解析还是在调用者的模式中解析。

一个调用说明能让我们在Oracle数据词典中发布一个Java方法或外部C函数。调用说明靠把程序的名称、参数类型和返回类型映射到它们的SQL副本(SQL counterpart)中来发布程序。

Package组成：
Package由包说明（package Specification)和包体(package body)两部分构成; 包说明 部分相当于C语言里面的.H文件，包体部分相当于 C语言里面针对.H实现的C文件。

Package常用SQL：
create or replace package——创建包
create or replace package body ———创建包体
drop package————删除包
drop package body————删除包体

Package好处：
1、模块化：一般把有相关性的函数和过程放到一个Package中；
2、易设计：可以把包说明和包体分别编写和编译，先编写和编译包说明部分，在编写和说明包体部分；这有利 于分工合作；
3、信息隐藏：包体中函数可以部分出现在包说明中，只有出现在包说明中的函数和过程才是该Package的公有 函数和过程，可以被其他包中的函数调用，否则对其他包中的函数是不可见的，未在包说明部分出现的函数 和过程相当于私有的。
4、加载性能提高：当Package中有一个函数或过程被调用时，整个Packege就被加载到内存中，这样当该Package中其他函数被调用时，就直接从内存读取了，可以减少磁盘IO,从而提高性能。 
这个特性也提醒 我们不要去搞巨无霸的Package, 把你用到的任何 函数都写到一个Package中，这会导致严重的内存浪费。
5、重载：一个package 中可以定义同名、不同参数的函数或过程。

Package中的向前声明特性：
在Package body中，一个函数中调用另一个函数（也在该Package中），则另 一个函数必须在前面先定义；

 

如果你非要调用在程序代码中后定义的函数，可把这个函数设置成公有函数，在包说明部分说明；

对包做个练习：

/*
--     创建日期: 2022-07-19
--        作者: ebowtang
--    功能描述: plsql包的练习，围绕着三个有关联的表编写了包，模拟进行一系列学校查询，增加，删除等功能
--        对象: 学生信息表，成绩表，课程表
-- 其他注意事项:
--  1, 本脚本文件完整的包含了所有测试代码，可以到指定版块选中执行
--  2, 本脚本编写测试过程中所用到的模块细节均得到保留，可供参考，
--     比如你会看到单独的get_namelist01过程代码，实际对应的就是包中的get_grade_namelist，我先进行验证再合入到包中的。
*/

--定义包规范，包规范将被用于应用程序的接口部分，供外部调用
CREATE OR REPLACE PACKAGE stu_pkg as

   ----声名三个游标变量指向高中各个年级的集合
   cursor cur_students_one is select * from students where grade = '高中一年级';
   cursor cur_students_two is select * from students where grade = '高中二年级';
   cursor cur_students_three is select * from students where grade = '高中三年级';

   --声名一个变量用来保存游标的一条记录
   v_stu students%rowtype;
   
   --限制显示的人数
   max_show_number integer := 100;
   
   --定义过程:学生新注册
   PROCEDURE signup(p_stu_id number ,p_stu_name char,p_gender varchar2,p_age number,
                 p_grade varchar2,p_class varchar2,p_email varchar2,p_sdate DATE);
                 
   --定义过程:开除一个学生                  
   PROCEDURE expel_student( p_stu_id NUMBER );
   
   --实现过程:获取指定年级、指定学科的成绩前100排名（所有学期的）
   procedure get_grade_scores(p_grade varchar2 , p_course_name char );
   
   --实现过程:获取指定学期的考试成绩统计（平均分，总分，最高分，最低分）,并按照总分排名
   procedure get_statistics_scores(p_grade varchar2,p_batch char );
   
   --查询学生成绩的考评级别（区分ABCDE，不公布数值给学生）
   procedure get_evaluate(p_stu_id number,p_batch varchar2);
   
   --实现函数：修改指定学生的分数
   function change_score ( p_score int, p_stu_id number,
		p_course varchar2, p_batch varchar2 ) return int;
		
   --实现过程：获得年级名单
   procedure get_grade_namelist(p_grade varchar2);
   
   --实现函数：指定学生的其中考试总分
   FUNCTION get_totalscore(p_stuid scores.student_id%type) RETURN scores.score_value%type;

END stu_pkg;

--实现包体
CREATE OR REPLACE PACKAGE BODY stu_pkg AS
   --实现雇佣员工的具体实现
   PROCEDURE signup(p_stu_id number ,p_stu_name char,p_gender varchar2,p_age number,
                 p_grade varchar2,p_class varchar2,p_email varchar2,p_sdate DATE) IS
   begin
   --实现过程:新入学的学生注册
      INSERT INTO students VALUES(p_stu_id ,p_stu_name,p_gender,p_age,
      			p_grade,p_class,p_email,p_sdate); 
   END;       
   
   --实现过程:开除一个学生                  
   PROCEDURE expel_student(p_stu_id NUMBER ) IS
   BEGIN
   --从emp表中删除员工信息
      DELETE FROM scores WHERE student_id=p_stu_id;
      DELETE FROM students WHERE student_id=p_stu_id;
   END;    
   
   --实现过程:获取指定年级、指定学科的成绩前100排名（所有学期的）
   procedure get_grade_scores(p_grade varchar2 , p_course_name char ) is
   begin
		select student_name,grade,course_name,score_value,scores.batch
		from  students,scores,courses
		where scores.student_id = students.student_id 
			and courses.course_name = p_course_name 
			and courses.course_id = scores.course_id
			and students.grade = p_grade
		order by score_value desc
		limit max_show_number;
   END; 
   
   --实现过程:获取指定学期的考试成绩统计（平均分，总分，最高分，最低分）,并按照总分排名
   procedure get_statistics_scores(p_grade varchar2,p_batch char ) is
   BEGIN
		select *
		from students,(
			select student_id as 学生ID,round(avg(score_value),1) as  平均分,sum(score_value) as 总分,max(score_value) as 最高分,min(score_value) as 最低分  
			from scores  
			group  by student_id,batch
			having  batch=p_batch
		)
		where grade = p_grade and students.student_id = 学生ID
		order by 总分 desc ;
   END;
   
   --实现过程：查询学生成绩的考评级别（区分ABCDE，不公布数值给学生）（固定获取数学的）
   procedure get_evaluate(p_stu_id number,p_batch varchar2) as 
		l_course_id varchar2;
		l_score number;
		l_grade varchar2;
		tempscore number := 0;
		l_course_name varchar2 := '数学';
   begin
		select course_id  into l_course_id
		from courses 
		where course_name= '数学' and category = '文科';
		
		select score_value into l_score 
		from scores
		where student_id =p_stu_id and course_id = l_course_id and batch=p_batch;
			
		if l_course_name = '英语' or l_course_name = '数学' or l_course_name = '语文' then
			tempscore =l_score/150;
		else 
		    tempscore =l_score/100;
		end if;
		--不同的分数段进行不同的评级
		CASE TRUE
	    WHEN tempscore < 1.0 and tempscore > 0.9  
	    	THEN l_grade ='A';
	    WHEN tempscore < 0.9 and tempscore > 0.8  
	    	THEN l_grade ='B';
	    WHEN tempscore < 0.8 and tempscore > 0.7  
	    	THEN l_grade ='C';
	    WHEN tempscore < 0.7 and tempscore > 0.6 
	    	THEN l_grade ='D';
	    ELSE l_grade ='E';
	  	END CASE;
	  	RAISE NOTICE 'your Score grade is:% %',l_score, l_grade;
	 END;
	 
	 --实现函数：修改学生分数
	 function change_score ( p_score int, p_stu_id number,
		p_course varchar2, p_batch varchar2 ) return int as 
	 begin
		if p_score > 150 or p_score < 0 then 
			return 0;
		end if;
		update scores set score_value=p_score 
		where student_id =p_stu_id and course_id =p_course and batch=p_batch;
		return 1;
	 end;
	 
	 --实现过程：获取年级名单
	 procedure get_grade_namelist(p_grade varchar2) as 
	 begin
	  --打开游标
	   if p_grade = '高中一年级' then
		   open cur_students_one;
		   --执行一次fetch into 让游标指向第一条数据
		   fetch cur_students_one into v_stu;
		   --while循环，条件是循环条件
		   while cur_students_one%found loop
			  --打印学生信息
			  --dbms_output.put_line(v_stu.student_id ||','|| v_stu.student_name);
			  RAISE NOTICE 'student id is:% ,student_name is %',v_stu.student_id,v_stu.student_name;
			  --fetch into将游标指向下一条数据
			  fetch cur_students_one into v_stu;
		   end loop;
		   --dbms_output.put_line(cur_students_one%rowcount||'');
		   RAISE NOTICE 'Number of students is:%',cur_students_one%rowcount;
		   --关闭游标
			   close cur_students_one;
			   
		  elsif p_grade = '高中二年级' then 
		  open cur_students_two;
		   --执行一次fetch into 让游标指向第一条数据
		   fetch cur_students_two into v_stu;
		   --while循环，条件是循环条件
		   while cur_students_two%found loop
			  --打印学生信息
			  --dbms_output.put_line(v_stu.student_id ||','|| v_stu.student_name);
			  RAISE NOTICE 'student id is:% ,student_name is %',v_stu.student_id,v_stu.student_name;
			  --fetch into将游标指向下一条数据
			  fetch cur_students_two into v_stu;
		   end loop;
		   --dbms_output.put_line(cur_students_two%rowcount||'');
		   RAISE NOTICE 'Number of students is:%',cur_students_two%rowcount;
		   --关闭游标
			   close cur_students_two;
			   
		  elsif p_grade = '高中三年级' then
		   open cur_students_three;
		   --执行一次fetch into 让游标指向第一条数据
		   fetch cur_students_three into v_stu;
		   --while循环，条件是循环条件
		   while cur_students_three%found loop
			  --打印学生信息
			  --dbms_output.put_line(v_stu.student_id ||','|| v_stu.student_name);
			  RAISE NOTICE 'student id is:% ,student_name is %',v_stu.student_id,v_stu.student_name;
			  --fetch into将游标指向下一条数据
			  fetch cur_students_three into v_stu;
		   end loop;
		   --dbms_output.put_line(cur_students_three%rowcount||'');
		   RAISE NOTICE 'Number of students is:%',cur_students_three%rowcount;
		   --关闭游标
			   close cur_students_three;
		  else 
			   RAISE NOTICE 'your enter grade is error ';
			  end if;
		 end;
	 
	FUNCTION get_totalscore(p_stuid scores.student_id%type) RETURN scores.score_value%TYPE
	AS
		v_maxscore scores.score_value%type := 0;
	begin
		select sum(score_value) into v_maxscore
		from scores  
		group  by student_id，batch
		having  batch='期中考试' and student_id = p_stuid;
		
		RETURN v_maxscore;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
	 	RAISE NOTICE 'The student id is invalid';
	 	RETURN -1;
	END get_totalscore;
END stu_pkg;

--匿名块执行包的测试
declare 
	l_grade char;
begin 
	--开除学生（根据学号）
	stu_pkg.expel_student(2314);
	--注册一个学生(有约束)
	stu_pkg.signup(2314,'张德田','男',19,'高中一年级','第八班','13@qq.com',SYSDATE);
	--统计学生成绩（总分，最高分等）
	stu_pkg.get_statistics_scores('高中二年级','期末考试');
	--获取成绩等级（ABCDE）
	stu_pkg.get_evaluate(4324,'期末考试');
	--获取年级指定学科成绩排名
	stu_pkg.get_grade_scores('高中三年级','语文');
	--修改成绩
	select stu_pkg.change_score(141,2314,'02','期中考试') from dual;
	--验证修改学生成绩是否成功
	select score_value from scores where course_id='02' and student_id=2314;
	--测试单独写的存储过程--实现年级名单查询(非包的存储过程)
	call get_namelist01('高中一年级');
	--测试单独写的存储过程(非包的存储过程)
	stu_pkg.get_grade_namelist('高中三年级');
	--获取指定学生的总分成绩（输入错误学号会检测异常）
	select stu_pkg.get_totalscore(2614) from dual;
end



---------------------plsql包的模块单独实现测试-----------------------------


CREATE OR REPLACE FUNCTION get_totalscore(p_stuid scores.student_id%type) RETURN scores.score_value%TYPE
AS
	v_maxscore scores.score_value%type := 0;
begin
	select sum(score_value) into v_maxscore
	from scores  
	group  by student_id，batch
	having  batch='期中考试' and student_id = p_stuid;
	
	RETURN v_maxscore;
EXCEPTION
	WHEN NO_DATA_FOUND THEN
 	RAISE NOTICE 'The student id is invalid';
 	RETURN -1;
END get_totalscore;

--测试
begin 
	--select * from scores;
	select get_totalscore(4324) from dual;
end

select sum(score_value)
	from scores  
	group  by student_id，batch
	having  batch='期中考试' and student_id = 2614;

select score_value
from scores  
where  batch='期中考试' and student_id = '2614' and course_id = 01;


--嵌套表
CREATE OR REPLACE TYPE stu_arr_typ IS TABLE OF char(8);

create  or replace  procedure test01 as 
   stu_arr stu_arr_typ :=stu_arr_typ();
   CURSOR stu_cur IS SELECT student_name FROM students WHERE age > 19;
   --v_student_name students.student_name%type;
   v_student_name char(8);
   i               INTEGER := 0;
BEGIN     
   open stu_cur;
   fetch stu_cur into v_student_name;
   while stu_cur%found loop 
   	     RAISE NOTICE 'name list is % ',v_student_name;
   	     fetch stu_cur into v_student_name;
   end loop;
   close stu_cur;
END ;

--测试
begin 
	call test01();
end

open stu_cur;
   fetch stu_cur into v_student_name;
   while stu_cur%found loop 
   	     stu_arr(i) := v_student_name;
   	     RAISE NOTICE 'name list is % ',stu_arr(i);
   	     i := i+1;
   	     fetch stu_cur into v_student_name;
   end loop;
   close stu_cur;
   
declare
  --声名一个嵌套表类型，存放字符串数据
  type tabType is table of varchar2(200);
  --声名变量
  tab1 tabType;
  tab2 tabType;
  --声名一个变量保存嵌套表的下标
  ind pls_integer;
begin
  --初始化嵌套表
  tab1:=tabType();
  --打印嵌套表tab1的长度
  --dbms_output.put_line('tab1的长度'||tab1.count);
   	RAISE NOTICE 'tab2的长度 % ',tab1.count;

  --初始化嵌套表tab2
  tab2:=tabType('a','b','c','d');
   --打印嵌套表tab2的长度
  --dbms_output.put_line('tab2的长度'||tab2.count);
 	RAISE NOTICE 'tab2的长度 % ',tab2.count;
   ---遍历集合
   ind := 1;
   loop--loop循环
      --打印集合元素
      dbms_output.put_line(tab2(ind));
      --退出条件
      exit when ind=tab2.last;
      ind:=tab2.next(ind);
   end loop;
   
  for i in tab2.first..tab2.last loop--for循环
    RAISE NOTICE 'tab2 is % ',tab2(i);
  end loop;
end;



DECLARE
  TYPE Roster IS TABLE OF VARCHAR2(15);  -- nested table type
 
  -- nested table variable initialized with constructor:
 
  names Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
 
  PROCEDURE print_names (heading VARCHAR2) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(heading);
 
    FOR i IN names.FIRST .. names.LAST LOOP  -- For first to last element
      DBMS_OUTPUT.PUT_LINE(names(i));
    END LOOP;
 
    DBMS_OUTPUT.PUT_LINE('---');
  END;
  
BEGIN 
  print_names('Initial Values:');
 
  names(3) := 'P Perez';  -- Change value of one element
  print_names('Current Values:');
 
  names := Roster('A Jansen', 'B Gupta');  -- Change entire table
  print_names('Current Values:');
END;
/

--定义三个游标获取年级数据
create  or replace  procedure get_namelist01(p_grade varchar2) as 
   --三个游标
   cursor cur_students_one is select * from students where grade = '高中一年级';
   cursor cur_students_two is select * from students where grade = '高中二年级';
   cursor cur_students_three is select * from students where grade = '高中三年级';
   --声名一个变量用来保存游标的一条记录
   v_stu students%rowtype;
begin
	  --打开游标
   if p_grade = '高中一年级' then
	   open cur_students_one;
	   --执行一次fetch into 让游标指向第一条数据
	   fetch cur_students_one into v_stu;
	   --while循环，条件是循环条件
	   while cur_students_one%found loop
	      --打印学生信息
	      --dbms_output.put_line(v_stu.student_id ||','|| v_stu.student_name);
	      RAISE NOTICE 'student id is:% ,student_name is %',v_stu.student_id,v_stu.student_name;
	      --fetch into将游标指向下一条数据
	      fetch cur_students_one into v_stu;
	   end loop;
	   --dbms_output.put_line(cur_students_one%rowcount||'');
	   RAISE NOTICE 'Number of students is:%',cur_students_one%rowcount;
	   --关闭游标
		   close cur_students_one;
		   
	  elsif p_grade = '高中二年级' then 
	  open cur_students_two;
	   --执行一次fetch into 让游标指向第一条数据
	   fetch cur_students_two into v_stu;
	   --while循环，条件是循环条件
	   while cur_students_two%found loop
	      --打印学生信息
	      --dbms_output.put_line(v_stu.student_id ||','|| v_stu.student_name);
	      RAISE NOTICE 'student id is:% ,student_name is %',v_stu.student_id,v_stu.student_name;
	      --fetch into将游标指向下一条数据
	      fetch cur_students_two into v_stu;
	   end loop;
	   --dbms_output.put_line(cur_students_two%rowcount||'');
	   RAISE NOTICE 'Number of students is:%',cur_students_two%rowcount;
	   --关闭游标
		   close cur_students_two;
		   
	  elsif p_grade = '高中三年级' then
	   open cur_students_three;
	   --执行一次fetch into 让游标指向第一条数据
	   fetch cur_students_three into v_stu;
	   --while循环，条件是循环条件
	   while cur_students_three%found loop
	      --打印学生信息
	      --dbms_output.put_line(v_stu.student_id ||','|| v_stu.student_name);
	      RAISE NOTICE 'student id is:% ,student_name is %',v_stu.student_id,v_stu.student_name;
	      --fetch into将游标指向下一条数据
	      fetch cur_students_three into v_stu;
	   end loop;
	   --dbms_output.put_line(cur_students_three%rowcount||'');
	   RAISE NOTICE 'Number of students is:%',cur_students_three%rowcount;
	   --关闭游标
		   close cur_students_three;
	  else 
	  	   RAISE NOTICE 'your enter grade is error ';
		  end if;
 end



--游标的测试获取学生年级数据
declare
   --声名一个游标变量指向高中二年级的集合
   cursor cur_to_stu is select * from students where grade = '高中二年级';
   --声名一个变量用来保存游标的一条记录
   v_stu students%rowtype;
begin
   --打开游标
   open cur_to_stu;
   --执行一次fetch into 让游标指向第一条数据
   fetch cur_to_stu into v_stu;
   --while循环，条件是循环条件
   while cur_to_stu%found loop
      --打印学生信息
      --dbms_output.put_line(v_stu.student_id ||','|| v_stu.student_name);
      RAISE NOTICE 'student id is:% ,student_name is %',v_stu.student_id,v_stu.student_name;
      --fetch into将游标指向下一条数据
      fetch cur_to_stu into v_stu;
   end loop;
   --dbms_output.put_line(cur_to_stu%rowcount||'');
   RAISE NOTICE 'Number of students is:%',cur_to_stu%rowcount;
   --关闭游标
   close cur_to_stu;
end

create or replace function change_score ( p_score int, p_stu_id number,
	p_course varchar2, p_batch varchar2 ) return int as 
begin
	if p_score > 150 or p_score < 0 then 
		return 0;
	end if;
	update scores set score_value=p_score 
	where student_id =p_stu_id and course_id =p_course and batch=p_batch;
	return 1;
end;


select *  
from courses 
where course_name='数学' and category = '文科';

create or replace  procedure getaaa(p_stu_id number,p_batch varchar2) as 
	l_course_id varchar2;
	l_score number;
	l_grade varchar2;
	tempscore number := 0;
	l_course_name varchar2 := '数学';
begin
	select course_id  into l_course_id
	from courses 
	where course_name= '数学' and category = '文科';
	
	select score_value into l_score 
	from scores
	where student_id =p_stu_id and course_id = l_course_id and batch=p_batch;
		
	if l_course_name = '英语' or l_course_name = '数学' or l_course_name = '语文' then
		tempscore =l_score/150;
	else 
	    tempscore =l_score/100;
	end if;
	
	CASE TRUE
    WHEN tempscore < 1.0 and tempscore > 0.9  
    	THEN l_grade ='A';
    WHEN tempscore < 0.9 and tempscore > 0.8  
    	THEN l_grade ='B';
    WHEN tempscore < 0.8 and tempscore > 0.7  
    	THEN l_grade ='C';
    WHEN tempscore < 0.7 and tempscore > 0.6 
    	THEN l_grade ='D';
    ELSE l_grade ='E';
  	END CASE;
  	RAISE NOTICE 'your Score grade is:% %',l_score, l_grade;
  	--也可以使用系统包：DBMS_OUTPUT.PUT_LINE(l_grade);
 END;
---------------------plsql包的模块单独实现测试结束-----------------------------


------------------简单SQL测试-----------------------

--查看成绩表
select * from scores;
--查看课程表
select * from courses;
--查看实验表
select * from students;
--查看学科成绩
select student_name,grade,course_name,score_value,scores.batch
from  students,scores,courses
where scores.student_id = students.student_id 
and courses.course_name = '数学' 
and courses.course_id = scores.course_id
and grade ='高中二年级'
order by score_value desc
limit  3;

--查看指定年级所有学科成绩以及总分
select students.student_name,grade,course_name,score_value,sum(score_value) as total,scores.batch
from  students,scores,courses
where scores.student_id = students.student_id 
	and courses.course_id = scores.course_id
order by score_value desc;
--统计成绩
select student_id as 学生ID,round(avg(score_value),1) as  平均分,sum(score_value) as 总分,max(score_value) as 最高分,min(score_value) as 最低分  
from scores  
group  by student_id，batch
having  batch='期中考试';


--统计指定年级的成绩
select *
from students,(
	select student_id as 学生ID,round(avg(score_value),1) as  平均分,sum(score_value) as 总分,max(score_value) as 最高分,min(score_value) as 最低分  
	from scores  
	group  by student_id,batch
	having  batch='期中考试'
)
where grade = '高中二年级' and students.student_id = 学生ID
order by 总分 desc ;

--清理实验环境
drop table if exists scores;
drop table if exists courses;
drop table if exists students;
--测试删除一个学生
DELETE FROM scores WHERE student_id=1414;
DELETE FROM students WHERE student_id=1414;--不能只删除这个，因为有外键

------------------简单SQL测试结束-----------------------


----------------------创建实验表及其数据-----------------------------
/*
-- 实验数据及其对象
*/
--创建学生表
CREATE TABLE students (
	student_id number(8),
	student_name char(8) not null,
	gender varchar2(2),
	age number(2),  
	grade varchar2 (40) not null,
	class varchar2 (40) not null,
	email varchar2 (30),
	sdate DATE,
	constraint pk_stuid primary key (student_id),
	constraint ch_gender check ( gender in ('男','女')),
	constraint ch_grade check ( grade in ('高中一年级','高中二年级','高中三年级')),
	constraint ch_age	CHECK ( age BETWEEN 12 AND 36)
);

--创建成绩表
create table scores (  
	student_id number(8),  
	course_id char(2),  
	score_value number(3) default(-1),  
	batch varchar2(20)
);  

--创建课程表
create table courses  (  
	course_id char(2) primary key,  
	course_name char(20),  
	category char(20) check( category in ('文科','理科','文理'))  
);

--插入学生数据
INSERT INTO students VALUES(2314,'张德田','男',19,'高中一年级','第八班','13@qq.com',SYSDATE);
INSERT INTO students VALUES(4324,'吴海峰','男',18,'高中一年级','第一班','14@qq.com',SYSDATE);
INSERT INTO students VALUES(2614,'章德正','男',20,'高中二年级','第二班','15@qq.com',SYSDATE);
INSERT INTO students VALUES(2184,'宋义然','女',20,'高中三年级','第三班','16@qq.com',SYSDATE);
INSERT INTO students VALUES(9874,'张华乐','女',19,'高中二年级','第二班','17@qq.com',SYSDATE);
INSERT INTO students VALUES(1474,'黎文月','女',19,'高中三年级','第七班','18@qq.com',SYSDATE);
INSERT INTO students VALUES(6574,'吉祥','男',21,'高中二年级','第六班','19@qq.com',SYSDATE);
INSERT INTO students VALUES(8174,'向玲','女',19,'高中二年级','第四班','20@qq.com',SYSDATE);
INSERT INTO students VALUES(1414,'梅田田','女',21,'高中一年级','第五班','221@qq.com',SYSDATE);

--插入课程数据
insert into courses values('01','语文','文理');
insert into courses values('06','英语','文理');

insert into courses values('02','数学','文科');
insert into courses values('03','政治','文科');
insert into courses values('04','地理','文科');
insert into courses values('05','历史','文科');

insert into courses values('07','数学','理科');
insert into courses values('08','生物','理科');
insert into courses values('09','化学','理科');
insert into courses values('10','物理','理科');

--插入学生成绩，期末考试
insert into scores values(2314,'01',110,'期末考试');
insert into scores values(4324,'01',109,'期末考试');
insert into scores values(2614,'01',88,'期末考试');
insert into scores values(2184,'01',121,'期末考试');
insert into scores values(9874,'01',114,'期末考试');
insert into scores values(1474,'01',103,'期末考试');
insert into scores values(6574,'01',113,'期末考试');
insert into scores values(8174,'01',108,'期末考试');
insert into scores values(1414,'01',125,'期末考试');

insert into scores values(2314,'02',100,'期末考试');
insert into scores values(4324,'02',104,'期末考试');
insert into scores values(2614,'02',99,'期末考试');
insert into scores values(2184,'02',101,'期末考试');
insert into scores values(9874,'02',104,'期末考试');
insert into scores values(1474,'02',113,'期末考试');
insert into scores values(6574,'02',103,'期末考试');
insert into scores values(8174,'02',118,'期末考试');
insert into scores values(1414,'02',129,'期末考试');

insert into scores values(2314,'06',101,'期末考试');
insert into scores values(4324,'06',103,'期末考试');
insert into scores values(2614,'06',127,'期末考试');
insert into scores values(2184,'06',105,'期末考试');
insert into scores values(9874,'06',101,'期末考试');
insert into scores values(1474,'06',109,'期末考试');
insert into scores values(6574,'06',100,'期末考试');
insert into scores values(8174,'06',99,'期末考试');
insert into scores values(1414,'06',114,'期末考试');

insert into scores values(2314,'03',56,'期末考试');
insert into scores values(4324,'03',75,'期末考试');
insert into scores values(2614,'03',26,'期末考试');
insert into scores values(2184,'03',76,'期末考试');
insert into scores values(9874,'03',89,'期末考试');
insert into scores values(1474,'03',90,'期末考试');
insert into scores values(6574,'03',91,'期末考试');
insert into scores values(8174,'03',57,'期末考试');
insert into scores values(1414,'03',92,'期末考试');

insert into scores values(2314,'04',76,'期末考试');
insert into scores values(4324,'04',87,'期末考试');
insert into scores values(2614,'04',56,'期末考试');
insert into scores values(2184,'04',36,'期末考试');
insert into scores values(9874,'04',84,'期末考试');
insert into scores values(1474,'04',64,'期末考试');
insert into scores values(6574,'04',82,'期末考试');
insert into scores values(8174,'04',47,'期末考试');
insert into scores values(1414,'04',66,'期末考试');

insert into scores values(2314,'05',67,'期末考试');
insert into scores values(4324,'05',83,'期末考试');
insert into scores values(2614,'05',52,'期末考试');
insert into scores values(2184,'05',26,'期末考试');
insert into scores values(9874,'05',96,'期末考试');
insert into scores values(1474,'05',91,'期末考试');
insert into scores values(6574,'05',81,'期末考试');
insert into scores values(8174,'05',87,'期末考试');
insert into scores values(1414,'05',92,'期末考试');

--插入数据，期中考试
insert into scores values(2314,'01',110,'期中考试');
insert into scores values(4324,'01',119,'期中考试');
insert into scores values(2614,'01',95,'期中考试');
insert into scores values(2184,'01',131,'期中考试');
insert into scores values(9874,'01',145,'期中考试');
insert into scores values(1474,'01',124,'期中考试');
insert into scores values(6574,'01',124,'期中考试');
insert into scores values(8174,'01',109,'期中考试');
insert into scores values(1414,'01',121,'期中考试');

insert into scores values(2314,'02',100,'期中考试');
insert into scores values(4324,'02',104,'期中考试');
insert into scores values(2614,'02',99,'期中考试');
insert into scores values(2184,'02',101,'期中考试');
insert into scores values(9874,'02',104,'期中考试');
insert into scores values(1474,'02',113,'期中考试');
insert into scores values(6574,'02',103,'期中考试');
insert into scores values(8174,'02',118,'期中考试');
insert into scores values(1414,'02',129,'期中考试');

insert into scores values(2314,'06',105,'期中考试');
insert into scores values(4324,'06',128,'期中考试');
insert into scores values(2614,'06',126,'期中考试');
insert into scores values(2184,'06',102,'期中考试');
insert into scores values(9874,'06',107,'期中考试');
insert into scores values(1474,'06',112,'期中考试');
insert into scores values(6574,'06',102,'期中考试');
insert into scores values(8174,'06',100,'期中考试');
insert into scores values(1414,'06',102,'期中考试');

insert into scores values(2314,'03',56,'期中考试');
insert into scores values(4324,'03',77,'期中考试');
insert into scores values(2614,'03',72,'期中考试');
insert into scores values(2184,'03',73,'期中考试');
insert into scores values(9874,'03',81,'期中考试');
insert into scores values(1474,'03',82,'期中考试');
insert into scores values(6574,'03',87,'期中考试');
insert into scores values(8174,'03',67,'期中考试');
insert into scores values(1414,'03',91,'期中考试');

insert into scores values(2314,'04',78,'期中考试');
insert into scores values(4324,'04',80,'期中考试');
insert into scores values(2614,'04',85,'期中考试');
insert into scores values(2184,'04',67,'期中考试');
insert into scores values(9874,'04',85,'期中考试');
insert into scores values(1474,'04',69,'期中考试');
insert into scores values(6574,'04',80,'期中考试');
insert into scores values(8174,'04',65,'期中考试');
insert into scores values(1414,'04',69,'期中考试');

insert into scores values(2314,'05',69,'期中考试');
insert into scores values(4324,'05',81,'期中考试');
insert into scores values(2614,'05',44,'期中考试');
insert into scores values(2184,'05',56,'期中考试');
insert into scores values(9874,'05',90,'期中考试');
insert into scores values(1474,'05',84,'期中考试');
insert into scores values(6574,'05',84,'期中考试');
insert into scores values(8174,'05',76,'期中考试');
insert into scores values(1414,'05',78,'期中考试');
----------------------创建实验表及其数据结束-----------------------------

EbowTang
关注
  • 3
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
plsql汉化包
11-05
psql的汉化包，汉化之后比较方便新手学习oracle，安装完plsql之后再安装这个程序即可
PLSQL Developer美化包
12-01
PLSQL Developer中自带的美化十分的粗糙，排版不够简洁，并且美化后所有的字母都变成大写，不便于阅读。这个美化包，美化的规则是关键字大写，其他字母小写，并且排版好看。
程序包简单解释
赟文武的博客
08-02 1850
程序包简单解释
【Oracle】包
奥利文儿的博客
07-10 1308
在PL/SQL程序开发中，为了方便实现模块化程序的管理，可以将PL/SQL元素（如存储过程、函数、变量、常量、自定义数据类型、游标等）根据模块的程序结构组织在一起，存放在一个包中，称为一个完整的单元，并在编译之后存储在数据库服务器中，作为一种全局结构，供应用程序调用。在Oracle数据库中，包有两类，一类是系统内置的包，每个包实现特定的应用过程、函数、常量等的集合，如DBMS_OUTPUT.put_line()就是调用了DBMS_OUTPUT包中的put_line()函数；另一类是根据应用需要由用户创建的包
mysql和sql打包_Oracle PLSQL 打包
weixin_36448083的博客
01-28 218
Oracle PLSQL 打包 --打包--创建包--步骤：--1、声明(创建包头)--2、创建数据对象(创建包体) --1、create or replace packagOracle PLSQL 打包--打包--创建包--步骤：--1、声明(创建包头)--2、创建数据对象(创建包体)--1、create or replace package myPackasv_bookCount intege...
create or replace package body DBMS_ASSERT wrapped
congxinlan3332的博客
06-22 1226
从Windows 10.2.0.1 32bit的数据库环境使用RMAN迁移到10.2.0.1 64bit的Linux，然后再升级到10.2.0.5。软件升级后，执行catupgrd.sql升级数据字典的时候报如下错误：creat...
oracle创建package、body、函数
bladeandmaster88的博客
06-04 4761
--删除表 drop table appl_application_sbfp; drop table sys_user_info; --创建表和主键 CREATE TABLE appl_application_sbfp ( application_no varchar(32) NOT NULL , customer_id varchar(32) NOT NULL,...
PLSQL破解包
11-13
PLSQL破解需要使用的包，具体破解步骤请参照破解文档。
第九章 PL/SQL包
KNIGHTRCOM(rcom10002的专栏)
09-02 4758
/* banner */#banner { position:static; top: 0px; left: 0px; width: 100%; height: 98px; background-repeat:no-repeat; background-image: url(../images/banner.gif); background-positi
oracle新建packbodies,创建Oracle Package Body
weixin_39753584的博客
04-04 358
该代码似乎为我编译.你遇到了什么错误？sql> CREATE OR REPLACE PACKAGE synchronize_my_data2 AS3 PROCEDURE synchronize_data(p_run_date IN date);4 END synchronize_my_data;5 /Package created.sql> CREATE OR REPLAC...
plsql安装包
12-03
为方便他人，搞了个plsql工具，只需直接点击PLSQL.exe，不许安装就可以用，就是缺少导入导出的exp.exe和IMP.EXE文件
​PL/SQL 包Package
m0_46237405的博客
11-29 322
​包Package 包(PACKAGE)的全称叫程序包，是由一组相关的函数，过程，变量，游标等PL/SQL程序设计元素的组合。 包类似于java中的类,把公有的变量,常量,函数,过程等都封装到该包中. 说其像java中的类,其实更像java中的接口,因为包是包含两部分: –包接口规范:相当于java中定义了一个接口,封装公有的属性,抽象方法. –包体:相当于实现类,将抽象方法的方法体进行实体化. 包接口规范语法: CREATE [OR REPLACE] PACKAGE package_name ​ IS
oracle新建packbodies,oracle-学习笔记-5
weixin_29476767的博客
04-04 424
16.Oracle 小问题Oracle Package有哪些作用呢？简化应用设计、提高应用性能、实现信息隐藏、子程序重载。ORACLE中的function 、package、package bodies、procedure的有什么区别和相同的地方？function有返回值，有参数；procedure无返回值，有参数；package、package body是同时存在的，就像.h和.cp...
程序包+创建包规范+创建包体+删除程序包
weixin_33742618的博客
05-24 355
程序包 1作用是为了实程序模块化 2程序包里面可以包含存储过程，函数，变量，游标等PL/SQL程序 3程序包类似java的jar包，存储过程，函数，变量，游标等相当jar包提供的方法 4调用程序包里面的存储过程，函数等元素时，oracle会把程序包调入内存，下次是使用，直接从内存读取。 5程序包组成 1）包规范：列出程序包可用的存储过程，函数，游标等元...
oracle pl 删除行,oracle中使用plsql来进行平面文件卸载
weixin_34737524的博客
04-05 210
平面文件卸载要把数据从一个系统移动到另一个系统,如果没有使用EXP/IMP或EXPDP/IMPDP(用于取代EXP和IMP的新数据泵),平面卸载就很有用.尽管使用EXP(DP)/IMP(DP)可以很好地将数据从一个系统移到另一个系统,但要求两个系统都是Oracle.jy@JINGYONG> create or replace package unloader2 authid current...
存储过程常用技巧
klyuan
01-06 463
作者：袁光东 我们在进行pl/sql编程时打交道最多的就是存储过程了。存储过程的结构是非常的简单的，我们在这里除了学习存储过程的基本结构外，还会学习编写存储过程时相关的一些实用的知识。如：游标的处理，异常的处理，集合的选择等等 1.存储过程结构 1.1 第一个存储过程 [code="java"]create or replace procedure proc1( p_para1...
在PLSQL中包的查看
神采奕奕
10-28 6617
functions = S_FrameWork_FunctionProxy.executeStoredProcedure( “pkg_security.userOptionFunctions”, vPara, true);//存储过程 （1）把pkg_security.userOptionFunctions复制到pl/sql中， （2）点击ctrl，会看到所打开文件头部有个类似的(create o...
oracle 包调试 步骤
路漫漫 修远兮
10-21 5693
资源下载:http://download.csdn.net/download/sat472291519/9659808
PLSQL中关于包的一个示例
冯立彬的博客
07-27 7759
PLSQL有类似于JAVA中的接口的包，包的好处就是方便管理与理解，并且对于学习JAVA的人来说，基本没有语方法上的障碍，一看就知道是这么个东西，也好理解。下面是一个示例：需要的表：create table test(tid number,tname varchar(50));-------------------------创建一个包---------------------
PLSQL打包SQL发送
最新发布
04-11
给Oracle数据库时的流程是怎样的？ 当你想要发送一个 PL/SQL 打包的 SQL 语句给 Oracle 数据库时，首先你需要创建一个基于 _PACKAGE_ 的包，将所有相关的函数、过程和变量都包含在这个包中。然后你可以在这个包中编写你想要发送的 SQL 语句。这个包可以被编译成一个模块，然后发送给 Oracle 数据库，这个模块可以在以后的会话中被重复使用。在发送这个模块之前，你也需要为这个模块分配一个唯一的标识符。一旦这个模块被成功发送和编译，你就可以在 Oracle 数据库中调用这个模块，并执行其中包含的 SQL 语句。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值