Oracle存储过程

--存储过程
--语法上来说，比函数少了返回值
CREATE OR REPLACE PROCEDURE pro_add(in1  NUMBER,
                                    in2  NUMBER,
                                    out1 OUT NUMBER) AS
BEGIN
  out1 := in1 + in2;
END pro_add;

--调用存储过程
DECLARE
  v_re NUMBER;
BEGIN
  pro_add(3, 5, v_re);
  dbms_output.put_line(v_re);
END;

--返回游标的存储过程
CREATE OR REPLACE PROCEDURE pro_table(deptid  NUMBER,
                                      cur_emp OUT SYS_REFCURSOR) AS
BEGIN
  OPEN cur_emp FOR
    SELECT * FROM emp WHERE deptno = deptid;
END pro_table;

--调用存储过程
DECLARE
  cur_re  SYS_REFCURSOR;
  deptid  NUMBER := &id;
  rec_emp emp%ROWTYPE;
BEGIN
  pro_table(deptid, cur_re);
  LOOP
    FETCH cur_re
      INTO rec_emp;
    EXIT WHEN cur_re%NOTFOUND;
    dbms_output.put_line(rec_emp.ename || ',' || rec_emp.deptno);
  END LOOP;
END;

--删除函数或者存储过程
drop function get_salary;
drop procedure 存储过程名;

--定义一个存储过程，返回学号是xxx的学生的姓名，所学课程，成绩，该科教师
CREATE OR REPLACE PROCEDURE pro_stu(stuid  NUMBER,
                                      cur_stu OUT SYS_REFCURSOR) AS
BEGIN
  OPEN cur_stu FOR
    SELECT st.s_name, su.su_name, sc.sco,te.t_name
      FROM student st, teacher te, subject su, score sc
     WHERE st.s_id = sc.s_id
       AND sc.su_id = su.su_id
       AND su.t_id = te.t_id
       and st.s_id=stuid;
END pro_stu;

--调用存储过程
DECLARE
  cur_st  SYS_REFCURSOR;
  stuid  NUMBER := &id;
  v_name  student.s_name%TYPE;
  v_sname subject.su_name%TYPE;
  v_sco   score.sco%TYPE;
  v_tname teacher.t_name%TYPE;
BEGIN
  pro_stu(stuid, cur_st);
  LOOP
    FETCH cur_st
      INTO v_name,v_sname,v_sco,v_tname;
    EXIT WHEN cur_st%NOTFOUND;
    dbms_output.put_line(v_name||','||v_sname||','||v_sco||','||v_tname);
  END LOOP;
END;

--定义一个游标变量：cur_stu（变量名） OUT（输出参数） SYS_REFCURSOR（数据类型是游标变量）

--综合练习，创建函数fun_emp(6,5),查询emp表中工资排序后，第6人开始，一共查5个。
--利用函数或者存储过程执行复杂的查询，供其它语言调用，效率更高，提高代码的可读性
--利用视图来简化查询
CREATE OR REPLACE FUNCTION fun_emp(startnum NUMBER, mach NUMBER)
  RETURN SYS_REFCURSOR IS
  cur_re SYS_REFCURSOR;
BEGIN
  OPEN cur_re FOR
    SELECT *
      FROM ord_emp
     WHERE id < startnum + mach
       AND id >= startnum;
  RETURN cur_re;
END fun_emp;
--调用
DECLARE
  cur_emp SYS_REFCURSOR;
  TYPE rec_emp IS RECORD(
    v_id   NUMBER,
    v_no   emp.empno%TYPE,
    v_name emp.ename%TYPE,
    v_sal  emp.sal%TYPE);
  emp1 rec_emp;
BEGIN
  cur_emp := fun_emp(3, 4);
  LOOP
    FETCH cur_emp
      INTO emp1;
    EXIT WHEN cur_emp%NOTFOUND;
    dbms_output.put_line(emp1.v_id || ',' || emp1.v_name || ',' ||
                         emp1.v_sal || ',' || emp1.v_no);
  END LOOP;
  CLOSE cur_emp;
END;