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;
---------------------------------------------------------