Oracle中的子程序和程序包

--存储过程的语法
CREATE [OR REPLACE] PROCEDURE
   <procedure name> [(<parameter list>)]
IS|AS
   <local variable declaration>
BEGIN
   <executable statements>
[EXCEPTION
   <exception handlers>]
END <procedure name> ;
--存储过程
CREATE OR REPLACE PROCEDURE
       search_emp(emp_no NUMBER)--不写参数类型时，默认为输入参数
       AS
       emp_name VARCHAR2(20);--声明变量
BEGIN
      SELECT ename INTO empname FROM emp WHERE empno =emp_no;
      dbms_output.put_line('职员的名字是：'||emp_name);
EXCEPTION
      WHEN no_data_found THEN
      dbms_output.put_line('职员的名字未找到!');
END search_emp;
--带输入参数in的存储过程(示例)
CREATE OR REPLACE PROCEDURE
        p1(emp_no IN NUMBER)
       AS
       empname VARCHAR2(20);
BEGIN SELECT ename INTO empname FROM emp WHERE empno =emp_no;
      dbms_output.put_line('职员的名字是：'||empname);
EXCEPTION
      WHEN no_data_found THEN
      dbms_output.put_line('职员的名字未找到!');
END p1;
--执行带输入参in的存储过程
SET SERVEROUTPUT ON;
execute p1(7788);
--带输出参数out的存储过程
CREATE OR REPLACE PROCEDURE
       p2( sal IN NUMBER ,rs OUT VARCHAR2)
IS
       maxsal NUMBER(7,2);
       minsal NUMBER(7,2);
BEGIN
       SELECT MAX(sal),MIN(sal) INTO maxsal,minsal FROM emp;
       IF sal >= minsal AND sal<maxsal THEN
       rs:='猜的不错，在中间';
       ELSE
       rs:='猜错了';
       END IF;
END p2;
--调用带输出参数out的存储过程
DECLARE
outvalue VARCHAR2(20);
BEGIN
p2(1500,outvalue);
dbms_output.put_line(outvalue);
END;

--带int out参数的存储过程， 交换数据（即是输入参数，又是输出参数）
CREATE OR REPLACE PROCEDURE swap
(
   num1 IN OUT NUMBER,
   num2 IN OUT NUMBER
)
AS
temp NUMBER;
BEGIN
temp:=num1;
num1:=num2;
num2:=temp;
END swap;
-- 调用带int out参数的存储过程
DECLARE --定义两个变量
num1 NUMBER:=100;
num2 NUMBER:=200;
BEGIN
swap(num1,num2);--调用存储过程
dbms_output.put_line(num1);
dbms_output.put_line(num2);
END;

--授权
GRANT EXECUTE ON find_enm TO martin;
GRANT EXECUTE ON swap TO PUBLIC ;
--删除
DROP PROCEDURE find_emp;

--函数语法
CREATE [OR REPLACE] FUNCTION
  <function name> [(param1,param2)]
RETURN <datatype>  IS|AS
  [local declarations]
BEGIN
  Executable Statements;
  RETURN result;
EXCEPTION
  Exception handlers;
END;
--示例
CREATE OR REPLACE FUNCTION f_hello
RETURN VARCHAR2
AS
BEGIN
RETURN 'hello,您好';
END;
--调用函数
SELECT f_hello FROM dual;
--返回较大值的函数
CREATE OR REPLACE FUNCTION f_max
(
v1 NUMBER,
v2 NUMBER
)
RETURN NUMBER
AS
BEGIN
IF v1>v2 THEN RETURN v1;
ELSE
RETURN v2;
END IF;
END;
--自主事务处理
CREATE TABLE  t
(ID number,NAME VARCHAR2(10));
INSERT INTO t VALUES(1,'a');
INSERT INTO t VALUES(2,'b');
INSERT INTO t VALUES(3,'c');
--第一个(子事务)
CREATE OR REPLACE PROCEDURE t_p1
AS
     n VARCHAR2(2);
     --PRAGMA AUTONOMOUS_TRANSACTION;--声明为自主事务
BEGIN
     SELECT  NAME INTO n FROM t WHERE ID=1;
     dbms_output.put_line(n);--打印id=1的name值
     ROLLBACK;
END;
--第二个(主事务)
CREATE OR REPLACE PROCEDURE t_p2
AS
     n VARCHAR2(2);
BEGIN
     UPDATE t SET NAME ='e' WHERE ID=1;
     t_p1();
     SELECT  NAME INTO n FROM t WHERE ID=1;
     dbms_output.put_line(n);--打印id=1的name值
     ROLLBACK;
END;
--程序包
--程序包规范（语法）
CREATE [OR REPLACE]
  PACKAGE
  package_name IS|AS
[Public item declarations]
[Subprogram specification]
END [package_name];
--程序包规范(示例)--只创建空包
CREATE OR REPLACE PACKAGE pack_me IS
PROCEDURE emp_prc(emp_no NUMBER);--存储过程
FUNCTION emp_fun(emp_no NUMBER) RETURN NUMBER;--函数
CURSOR emp_cur(emp_no NUMBER)--游标
RETURN emp%ROWTYPE;
END pack_me;

--程序包主体（语法）
CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS
[Private item declarations]
[Subprogram bodies]
[BEGIN
Initialization]
END [package_name];
--程序包主体(示例)
CREATE OR REPLACE PACKAGE BODY pack_me AS
--r emp % ROWTYPE;--此处定义的变量为私有的
--实现存储过程
  PROCEDURE emp_prc(emp_no NUMBER)IS
    empname VARCHAR2(20);
  BEGIN
    SELECT ename INTO empname FROM emp WHERE empno =emp_no;
    dbms_output.put_line('职员的名字是：'||empname);
  EXCEPTION
    WHEN no_data_found THEN
    dbms_output.put_line('职员的名字未找到!');
  END emp_prc;
--实现函数（调用时需定义变量来接收）
  FUNCTION emp_fun(empno NUMBER) RETURN NUMBER IS
    tempsal NUMBER;
  BEGIN
    SELECT sal INTO temsal FROM emp WHERE empno=emp_no;
    RETURN tempsal;
  EXCEPTION
    WHEN  no_data_found THEN
    dbms_output.put_line('职员的名字未找到!');
  END emp_fun;
  --实现游标
     CURSOR emp_cur(emp_no NUMBER)
     RETURN emp%ROWTYPE AS
     SELECT * FROM emp WHERE empno = emp_no;
  --为调用游标而新一个存储过程
  PROCEDURE ord_pro(vcode VARCHAR2) IS
  or_rec emp%ROWTYPE;
  BEGIN
         OPEN emp_cur(vcode);
     LOOP
         FETCH emp_cur INTO or_rec;
         EXIT WHEN emp_cur%NOTFOUND;
         DBMS_OUTPUT.PUT_LIne('返回的值为' || or_rec.ename);
     END LOOP;
  END ord_pro;
END pack_me;
--程序包的调用(示例)
EXECUTE pack_me.

--在同一个包中创建一个游标和一个存储过程，
--在包内利用存储过程来调用游标
---------------------------------------------------------
--创建包中的游标规范
CREATE OR REPLACE PACKAGE pack_t AS
CURSOR emp_cur(emp_no1 NUMBER )
RETURN emp%ROWTYPE ;          --定义带参游标
PROCEDURE emp_pro(emp_no2 NUMBER);
END pack_t;                   --定义存储过程
--创建包中的游标主体
CREATE  OR REPLACE PACKAGE BODY pack_t AS
--实现游标
  CURSOR emp_cur(emp_no1 NUMBER )
  RETURN emp%ROWTYPE IS--此处返回一行，要返回多行可用 return emp;
  SELECT * FROM emp WHERE empno=emp_no1;
--实现存储过程（并调用包中的游标）
  PROCEDURE emp_pro(emp_no2 NUMBER) IS
            emp_row emp%ROWTYPE;
  BEGIN
    OPEN pack_t.emp_cur(emp_no2);--打开游标并传参
    LOOP
    FETCH pack_t.emp_cur INTO emp_row;
    EXIT WHEN pack_t.emp_cur%NOTFOUND;
    dbms_output.put_line('工号为'||emp_row.empno ||'的姓名：'|| emp_row.ename);
    END LOOP;
  END emp_pro;
END pack_t;
--调用包中的存储过程（无法直接调用其内部的游标）
execute pack_t.emp_pro(7788);
---------------------------------------------------------


---------------------------------------------------------
--查询所有的视图
SELECT object_name,object_type FROM User_Objects
WHERE object_type IN('PROCEDURE','FUNCTION','PACKAGE');
--查询原代码
SELECT line,text FROM user_source WHERE NAME='SWAP';
--查询包规范（包中的过程，函数 ，游标）
DESC PACK_ME;
---------------------------------------------------------