【数据库编程】PL/SQL写存储过程和存储函数

此篇主要讲的的PL/SQL一些基本数据类型和控制流程以及包的定义

其中记载了自己在写存储过程中的遇到的坑和注意事项，以供自己日后学习

---

---学生表
drop table Student;
drop table Course;
drop table Sc;

CREATE TABLE Student(
  SNO VARCHAR2(7) PRIMARY KEY,
  Sname VARCHAR2(18),
  SSex CHAR(3),
  Sage SMALLINT,
  Sdept VARCHAR2(50),
  SAvgGrade NUMBER(3,0),
  SPicture BLOB
);
---课程表
CREATE TABLE Course(
  Cno VARCHAR2(4) PRIMARY KEY,
  Cname VARCHAR2(50),
  Cpno VARCHAR2(4),--前导课
  Ccredit NUMBER(2,0),
  FOREIGN KEY (Cpno) REFERENCES Course(Cno)
);

---选课表
CREATE TABLE SC(
  Sno VARCHAR2(7),
  Cno VARCHAR2(4),
  Grade NUMBER(3,0),
  PRIMARY KEY(Sno,Cno),
  FOREIGN KEY (Sno) REFERENCES Student(Sno),
  FOREIGN KEY (Cno) REFERENCES Course(Cno)
);
--测试数据：
insert into student(sno,sname,ssex,sage,sdept) values('9512101','李勇','男',19,'计算机系');
insert into student(sno,sname,ssex,sage,sdept) values('9512102','刘晨','男',20,'计算机系');
insert into student(sno,sname,ssex,sage,sdept) values('9521101','张立','男',22,'信息系');
insert into student(sno,sname,ssex,sage,sdept) values('9521102','吴宾','女',21,'信息系');
insert into student(sno,sname,ssex,sage,sdept) values('9531101','钱小平','女',18,'数学系');
insert into student(sno,sname,ssex,sage,sdept) values('9531102','王大力','男',19,'数学系');



insert into course values('1','数据处理',null,2);
insert into course values('2','数学',null,2);

insert into course values('3','操作系统','1',3);
insert into course values('4','C 语言程序设计','1',4);
insert into course values('5','数据结构','4',4);
insert into course values('6','数据库','5',4);
insert into course values('7','信息系统','6',4);

insert into sc values('9512101','1',92);
insert into sc values('9512101','2',85);
insert into sc values('9512101','3',88);
insert into sc values('9512102','2',90);
insert into sc values('9512102','3',100);

select * from student;

select avg(sc.grade) from sc where sno = '9512101';

--存储过程---
/**
*①编写存储过程，通过输入参数接收学生学号信息，
*通过输出参数输出学生相应的平均成
*/

--ORA-06502 数值或值错误：数值精度太高
CREATE OR REPLACE PROCEDURE select_info(psno IN student.Sno%TYPE)
IS
   v_avggrade number(7,4);
BEGIN
   select avg(sc.grade) into v_avggrade from sc where sno = psno;
   dbms_output.put_line(v_avggrade);
END;
---TEST1 指定参数----
DECLARE 
   psno student.sno%TYPE := '9512101';
BEGIN
  select_info(psno);
END;
  -----TEST2 指定参数--------
  BEGIN 
       select_info(&psno);
  END;


select avg(sc.grade)  from sc where sno = '9512101';
select *  from sc where sno = '9512101';
select sum(sc.grade) from sc where sno='9512101';


drop function select_sno;
drop procedure select_sno;

-------【注意】定义数据类型时不能明确指出数据的精度-----------
CREATE OR REPLACE PROCEDURE select_sno(psno IN Student.sno%TYPE, gg OUT number)
AS
BEGIN 
    SELECT AVG(sc.grade) INTO gg FROM SC WHERE SNO = psno; 
END;

DECLARE
    psno Student.Sno%TYPE := '9512101';
    gg number(3,1);
BEGIN
    select_sno(psno, gg);
    dbms_output.put_line(gg);
END;

/**
*②.编写存储函数，通过输入参数接收学生学号信息，返回学生的平均成绩；
*/
drop function ff;
CREATE OR REPLACE FUNCTION ff(pno IN SC.cno%TYPE) 
RETURN number ---///***注意这个位置返回值一定不能要;分号，否则报pls-00103错误end-of-file
IS
--    v_grade sc.grade%TYPE;
--number(p,s);
--p是精确度，s是范围例如：decimal(3,1)是指这样一个数字：小数点前2位数字，小数点后1位数字

      v_grade number(3,2);
BEGIN
    SELECT AVG(SC.Grade) INTO v_grade from sc where sno = pno;
    RETURN(v_grade);
END;

--调用函数---由于数值精度太高回报错ORA-06502
DECLARE 
   sno Sc.Sno%TYPE := '9512101';
  -- vv_grade sc.grade%TYPE;//修改前
   vv_grade number(3,2);//改正后
   【注】//此处有个错误，我定义vv_grade变量时是按照sc表中的数据类型（3，0）定义，而在存储函数中返回值确实（3，2）故会报错，显示06502错误。
BEGIN
   vv_grade := ff(sno);
   dbms_output.put_line(vv_grade);
END;
--Test2
DECLARE 
   vv_grade number(3,2);
BEGIN 
  vv_grade := ff(&sno);
  dbms_output.put_line(vv_grade);
END;


/**
*③如果要求程序依据接收的学生学号信息，提供学生的平均成绩以及选课信息，又该如何实现
*（提示：由于选课信息是多行多列的结果集，借助游标方可实现）？
*/
select * from sc where sno='9512101';
SELECT cno,CNAME FROM COURSE where cno in (select cno from sc where sno='9512101');
-----【注意事项】多表查询时不要用笛卡尔积-->要用自然连接natural join 和 内连接 以及子查询
--使用inner join连接查询
--语法格式 select * from table1 inner join table2 on 连接rule where xxx;

--select ST.sno,ST.sname from (student) as st INNER JOIN sc on sc.sno=ST.sno; 
--select sname,cno,cname from student natural join course natural join sc where sno='9512101';

CREATE OR REPLACE PROCEDURE get_info (pno IN SC.sno%TYPE,gg OUT SC.Grade%TYPE, 
       info_cursor OUT sys_refcursor)
AS
BEGIN
       select avg(sc.grade) into gg FROM SC where sno=pno;
       open info_cursor for select cno,cname from course natural join sc where sno=pno;    
 --  open info_cursor for select cname from TB_COURSE WHERE CNO IN (SELECT CNO FROM SC WHERE SNO = PNO);
--EXCEPTION     
END;

--Test--
DECLARE 
       --声明变量
       info_cursor sys_refcursor;
   --    v_info_row sc%ROWTYPE;      --行记录类型那个
       type type_a_b is record(col_1 course.cno%TYPE,col_2 course.cname%TYPE);
       result_temp type_a_b;
       v_sno student.sno%TYPE := '9512101';
       v_grade sc.grade%TYPE;
BEGIN  
       get_info(v_sno,v_grade,info_cursor);
       dbms_output.put_line('学号'||v_sno||'平均分'||v_grade);
       LOOP
          FETCH info_cursor into result_temp;
--          IF v_info_row%NOTFOUND
  --           THEN 
  --              EXIT;
    --      ELSE
            exit when info_cursor%NOTFOUND;
                Dbms_Output.put_line('cno:'||result_temp.col_1 ||'cname:'+result_temp.col_2);
        --  END IF;
        END LOOP;
        close info_cursor;    --关闭游标                                 --
END;  


/**
*（3）、在学生－课程数据库中，针对student 表，定义包实现如下功能，并分别在PL/SQL 块以及PL/SQL
    DEVELOPER 中测试该存储子程序的代码，上机验证你的代码。
    ① 查询所有学生信息记录
    ② 查询指定学号的学生信息记录
    ③ 插入学生信息记录（包括学号、姓名、性别、年龄、系部字段）
    ④ 删除指定学号学生信息记录
    ⑤ 更新学生信息记录（包括学号、姓名、性别、年龄、系部字段）
*/
--定义包规范
create or replace package student_pack
as
       type student_cursor is ref cursor;
       procedure select_all(out_cursor OUT student_cursor);
       procedure select_all_by_sno(pno IN student.sno%TYPE, out_cursor OUT student_cursor);
       procedure insert_stu(stu IN student_cursor);
   --    procedure insert_stu(pno in student.sno%TYPE, pname in student.sname%TYPE,
    --                            psex in student.ssex%TYPE, page in student.sage%TYPE,pdepart in student.sdept%TYPE);
       procedure delete_stu(pno in student.sno%TYPE);
    --   procedure update_stu()                         

end student_pack;

--定义包体
drop package body student_pack;
create or replace package body student_pack
is
       procedure select_all(out_cursor out student_cursor)
       as 
       begin
             open out_cursor for select * from student;
       end select_all;
   --------------------------------------------------------    
       procedure select_all_by_sno(pno IN student.sno%TYPE, out_cursor OUT student_cursor)
       as 
       begin
              out_cursor is select * from student where sno=pno;
       end select_all_by_sno;
       --------------------------------------------
        procedure delete_stu(pno in student.sno%TYPE)
        as 
        begin
               delete from student where sno=pno;   
        end delete_stu;

end student_pack;


---TEST-----------
DECLARE
   v_out_cursor sys_refcursor;
   v_student_row student%ROWTYPE;
   v_sno student.sno%TYPE :='9512101';

BEGIN

    student_pack.select_all_by_sno(v_sno,v_out_cursor);
   --问题ORA-06550显示错误是v_out_cursor没有定义-----
    for item IN v_out_cursor
    LOOP
        dbms_output.put_line('sno :'||item.sno||'sname:'||item.sname);
    END LOOP;
    ----------------------------------
/*   
    LOOP
    FETCH v_out_cursor INTO v_student_row;
    EXIT WHEN v_out_cursor%NOTFOUND;
      dbms_output.put_line(v_student_row.sno || ' ' || v_student_row.sname || ' ' || v_student_row.ssex);
    END LOOP;
    close v_out_cursor;--关闭游标
*/
END;

/**【笔记】
【注意事项】
student.sno%TYPE获取数据类型
1.赋值 ：=
2.输出结果 1.
键盘输入

   select_sname_fun（&p_sno）;//函数名（参数）


   要求 输入学生一个输出参数多个
   借助系统游标实现

   sc_cursor out sys_refcursor;
   打开游标
   open sc_cursor for select * from sc where...


   测试

   Loop
     fetch sc_cursor into v_sc_row;
     exit when sc_cursor%notfound;
          dbms_output.putline();
   end loop

—-关键通过java代码接收存储函数返回的数据

二、自己总结内容

游标—一定要先声明然后才能使用

特殊的控制结构，有一个内存缓冲区（缓存SQL语句执行的结构）和一个游标指针组成。
oracle仅用游标属性反映最近一次被执行的DML,DDL语句

• %FOUND 标识DML操作是否改变了记录
• %ISOPEN 标识游标是否打开
• %NOFOUND 标识游标操作是否改变行记录
• %ROWCOUNT标识DML语句影响的行数

静态分为—
1.隐式游标 SQL%XXX 获取游标属性：在执行insert、delete、update、merge、select…into是所使用的游标，不需要执行打开、提取、关闭操作
2.显式游标 游标名%XXX 获取游标属性—>仅用于查询select处理,需要执行打开、提取、关闭操作。
REGEXP_LIKE (job_id, ‘{}’)—/声明处不懂

3.静态游标：定义是与sql语句绑定
4.**动态游标 **REF CURSOR。用游标类型，没有与sql语句绑定，打开时候才绑定。

3.存储子程序

块分为匿名块（不能被存储，只能使用一次）
和命名块（被存储，可以多次使用，–子程序）

子程序又分为过程（没有返回值）和函数
过程强调实施某种操作，函数强调返回计算值

子程序定义在包中不能要create
独立定义要用create

使用存储子程序有点：1）代码复用
2）调用清晰
3）执行速度快。在创建是已经通过语法检查和性能优化
4）规范程序设计
5）提高系统安全性，将存储子程序独立作为用户存储数据的管道

参数模式 in(缺省参数) out in-out三种

• IN：是默认模式，在子程序内部，形参就像常量一样：只读，不可修改；
• OUT：调用子程序时，实参的值被忽略，在子程序内部，形参就像未初始化的变量一样，会有一个NULL值，可读可写，过程执行完毕后，形参的值被赋给实参；
• IN OUT：IN 和OUT的复合模式；
  【注】在默认情况下，IN模式参数始终使用引用传递方式，而OUT及IN OUT模式参数使用值传递方式。
  形参定义时不能附加精度等限制

p14面在调用函数时execute :t1 := testFunc(v1=>:v1,v3=>w3)
不能再一个存储过程中删除另一个存储过程，只能调用另一个存储过程

存储函数：

参数有默认值的处理 create or replace function xxxx( 字段名 varchar2 defalust ‘11’);该字段默认参数是11

存储过程与存储函数相同点：都可以使用out返回多个值；都由声明、执行、异常处理三个部分组成；都可以接受默认值；都能够用位置表示法和名称表示发调用。
【注】当返回多个值，使用过程；返回一个值，使用函数

4. 存储过程的调试

1）检索用户子程序/触发器

5.包

包=包规范+包体（用来分类组织和管理子程序）

包规范可以定义数据类型、声明变量、常量，
异常，游标，子程序
【包规范是pl/sql 全局对象】

包体编写游标和子程序（过程，函数）

包规范语法
create or replace ace package 包名

end 包名;

2018/4/26 p15

5.用包管理存储过程和局部变量

   定义包规范
       create or replace package XXX
       as 
              type mycursor is ref cursor;
              procedure select_aall();
              procedure



       end XXXX;

       2.定义包体

       包体名字与包规范同名

       create or replace package body XX
       as 
              procedure XXX
                as
                begin

                end XXX;

                ------------------
                procedure XX
                as 
                begin 
                  end XX;  


           在代码块中引用

           declare


           begin

【说明】包第一次被调用时，被加载到服务器内存，以后代码在内存被共享。
每个oracle实例的session都有包变量的一个副本，不同会话所使用的包变量是相互独立，不存在并发冲突

*/

使用java调用存储过程

       Statement;
       prepareStatement psmt;

       conn.prepareStatement(sql);
  使用CallableStatement--调用存储过程/存储函数
       调用存储过程--传入参数
       cs = conn.prepareCall("{call XXX{?,?}}");
       registeroutParameter(2,java.sql.Types.DOUBLE);                     
       sc.execute();
       //获取第二个参数，单值返回
       cs.getDouble(2);



       函数

          call ? := select_avg_fun(?);


    多表连接插叙----通过内连接查询

    //ojbc
    cs.registerOutParameter(3,oracle.jdbc.OracleTypes.CURSOR)     
    rs = （ResultSet）cs.getObject(3);


    //直接列标
    //通过键值对获取



    //通过包的调用

    call student_pkg.XXX();

    回去复习PL/SQL的数据类型

2018/5/1号笔记
PL/SQL有标量、引用、符合、和大对象等数据类型
1.标量类型只能存储单支的数据：用于存储字符串、数字、布尔值、日期、二进制数据等
char varchar varchar2 nchar nvarchar2
rowid,urowid
number数值
boolean布尔值
date，timestamp,interval日期时间
ref cursor 和ref引用类型
blob,clob大对象

    2.复合类型（记录、集合、对象）
       2.1记录的定义语法
           create type type_name is record{field,,};

           【注】1.表名%ROWTYPE和游标%ROWTYPE返回的数据类型也为记录型
                  2.记录的域均是基本标量类型
        2.2集合类型
             2.2.1变长数组VARRAYS
             2.2.2嵌套表    
             2.2.3索引表

*/

---

此篇还有许多需要修改，未完待续。