Oracle 问答题 PLSQL块

1.未命名的PL/SQL块是匿名块，已命名的PL/SQL块是什么？
--过程
---------------------------------------------------------
2.有表student(name varchar2(20),age number(3,0),address varchar2(30))
  在插入部分记录之后，请建立存储过程getInfo,并可通过参数学生姓名来得到学生信息。
--先删除表
drop table student
--建立表
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 lee.student where name = sname;
   dbms_output.put_line(s_address);
  end;
declare
  sname varchar2(20) := 'aaa';
begin
  pro_getinfo( sname);
end;
---------------------------------------------------------
3.请修改上一存储过程，将address通过参数输出？并写出块来测试此过程？

create or replace procedure pro_getinfo( sname varchar2,address out varchar2,age out number )
as

  begin
    select address ,age into address,age from lee.student where name = sname;
      dbms_output.put_line('地址是'||address||'年龄是'||age);
  end;

declare
  saddress varchar2(30) ;
  sage number(3,0);
begin
  pro_getinfo('aaa',saddress,sage);
  dbms_output.put_line(saddress||'年龄是'||sage);
end;

select * from student where name = 'aaa'

---------------------------------------------------------
5.函数与过程的主要区别是什么？

过程                      函数
作为PL/SQL语句执行        作为表达式的一部分调用
在规范中不包含return子句  必须在规范中包含return子句
不返回任何值              必须返回单个值
可以只有return是用来返回  必须包含至少一条return语句
---------------------------------------------------------
6.有表studentmark(stuid varchar2(5),clsid varchar2(19),mark number(3,0))
  在插入部分记录之后
  请写函数来获得某个学号参加考试的次数。
  并测试结果
create table studentmark(stuid varchar2(5),clsid varchar2(19),mark number(3,0))
select * from studentmark
insert into studentmark values('aa','SQL',90);
insert into studentmark values('aa','Java',89);
insert into studentmark values('aa','Oracle',75);
insert into studentmark values('aa','C#',58);
insert into studentmark values('bb','JSP',24);

create or replace function fun_numcount(sid varchar2)
return number as counts number;
begin
select  count(*) into counts from studentmark where stuid = sid;
return counts;
  dbms_output.put_line(counts);
end;

declare num1 number;
begin
num1 := fun_numcount('aa');
dbms_output.put_line('这个学号的考生考试的次数是:'||num1||'次');
end;

---------------------------------------------------------
7.函数可以有out,或out in 类型的参数吗？它的参数与返回值类型有要求吗？
--可以是 输入输出型的
--不可以的
8.请写一函数获得三个数的最大数？并测试之
create or replace function fun_getmaxnumber(num1 number,num2 number,num3 number)
return number as maxvalues number;
begin
if((num1 > num2)and(num1>num3))
then
  return num1;
elsif((num2 > num3)and(num2>num1))
then
   return num2;
else
   return num3;
end if;
end;

declare num number;
begin
num := fun_getmaxnumber(99999999999,10000,8999);
   dbms_output.put_line('最大数是:'||num);
end;
--------------------------------------------------------------------
10.要声明自主事务处理要使用哪个声明？自主事务的特点是什么？
pragma autonomous_transaction;
特点是:
a.处理结果的变化不依赖于主事物处理的状态或最终配置;
b.自主事物提交或回滚时不我待不影响主事物的结果
c.自主事物一旦提交，用户可以访问以更新的信息，而无需等待主事物处理提交
d.自主事物处理可以启动其他自主事物处理
---------------------------------------------------------------------
11.程序包是什么？它包括哪两部分？
  程序包是一种数据库对象，对相关PL/SQL类型，子程序，游标，异常，变量和常量的封装。

  包括 程序包主体 和 程序包规范
---------------------------------------------------------------------
12.有表studentinfo(stuid varchar2(5),stuname varchar2(20),stuphone varchar2(12),stuaddress varchar2(50))
   表stumark(stuid varchar2(5),clsid varchar2(10),mark number)
   插入部分数据后
   创建包规范
        定义函数来获得某个学生，某门课程的成绩
        定义函数来获得某个学生所有课程的平均成绩
        定义函数来获得某个学生最差的科目
   创建包体来实现上述函数。
   测试上述函数
   请查询出所有学生的信息加上该生vb的考试成绩.

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','aaa','139123456789','秦皇岛海港区');
insert into stumark values('1','VB',89);
insert into stumark values('1','SQL',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';
--------------------------------------------------------------------
13.在pl/sql块中可以使用包中的变量吗？哪此部分可以使用，哪些部分不可用？
  公有元素在规范中定义，私有元素在程序包主体中定义。
  私有元素不能在程序包之外引用。程序包中的任何其他元素均可以引用和使用私有元素。
  可以，公有元素可以访问，私有的不能访问。
---------------------------------------------------------------------
14.在程序中如何定义和使用游标？
见12题吧
  --------------------------------------------------------------------
15.建立包规范与包主体，定义游标curStuMark来获得某学生的各科考试成绩信息。表如题12.并定义与实现过程来显示该学生的科目与分数信息。如
   vb   80
   sql  95....
   ----------------------根据一个人名获取他的各科成绩------------------------------------
--建立一个视图
create or replace view view_stu as
select i.stuid,i.stuname,m.clsid,m.mark from stumark m, studentinfo i
  where m.stuid = i.stuid(+)
--建立一个包
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;
  --------------------------------------------------------------------
16.请问题15定义的游标在包之外可以用吗，测试之？实现同15题过程显示的相同结果。
可以用。
  --------------------------------------------------------------------
17.由15，16题我们可以看出什么呢，（是不是我们可以将我们经常要用到的一些游标也可放入包中供将来直接使用）。
是的
  --------------------------------------------------------------------
18.包信息保存在哪个数据词典中。
user_objects
---------------------------------------------------------------------------------------------
19.关于自主事务处理的问题 子事物会执行自己的commit 如下

select * from studentmark

create or replace procedure p2
as
begin
update  studentmark set mark = 66 where stuid = 'bb';
commit;
end;


create or replace procedure p1
as pragma autonomous_transaction;
begin
update  studentmark set mark = 100 where stuid = 'bb';
p2;
rollback;
end;

begin
p1;
end;