分类:
Oracle
存储过程
在应用程序中经常需要执行特定的一些操作，基于这些操作建立一个特定的过程。
建立过程语法：
SQL>CREATE [OR REPLACE] -- REPLACE表示更新
PROCEDURE PROCEDURE_NAME --PROCEDURE指定过程名称
(argument1 [model1] datatype1,argument2 [model2] datatype2,......) -- argument等指定过程的参数，当指定参数数据类型时,不能指定长度。
IS [AS] -- IS或者AS用于开始一个PL/SQL块
PL/SQL BLOCK; ----model是参数模式，包括输入参数(IN)、输出参数(OUT)、输入输出参数(IN OUT)，默认为输入参数(IN)。
例子：
-- 建立一个不带参数的过程
SQL>CREATE OR REPLACE PROCEDURE TIME_OUT
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(SYSTIMETAMP);
END;
-- 建立一个带入输入参数的添加员工的存储过程
SQL>CREATE OR REPLACE PROCEDURE TIME_OUT
(eno VARCHAR2, name VARCHAR2,deptno VARCHAR2,sex VARCHAR2 default '男',
edate DATE,wdate DATE,natno VARCHAR2 ,pho VARCHAR2 ,esal NUMBER,ecomm NUMBER)
IS
BEGIN
INSERT INTO scott.emp VALUES(eno ,name,deptno,sex,edate,wdate,natno,pno,esal)
END TIME_OUT; -- 该过程没有指定参数模式，默认全是输入参数。
-- 调用过程
EXECUTE TIME_OUT [OR TIME_OUT(参数)];
存储过程不仅仅可以执行特定操作，也可以输出数据，输出数据使用OUT或者IN OUT 参数来完成。
OUT为输出参数，调用结束后Oracle会通过该变量将过程产生的结果传递给应用程序。
IN OUT为输入输出参数，在调用过程之前需要通过变量给该参数传递数据，在调用结束后，Oracle会通过该变量将过程产生的结果传递给应用程序
再举个例子:
-- 创建一个带OUT参数的过程
SQL>CREATE OR REPLACE PROCEDURE TestOut
(value1 NUMBER,value2 OUT NUMBER)
IS
BEGIN
value2 := value1+50;
END;
-- 调用存储过程
SQL>DECLARE
RESULT NUMBER;
BEGIN
TestOut(10,RESULT );
DBMS_OUT_PUT.PUT_LINE(RESULT );
END;
-- 输出结果
60
-- 删除存储过程
drop procedure 过程名称;
函数
在应用程序中经常需要通过执行SQL语句来返回特定数据，可以建立相应的函数。
建立函数语法:
SQL>CREATE [OR REPLACE] -- REPLACE表示更新
FUNCTION FUNCTION_NAME --PROCEDURE指定函数名称
(argument1 [model1] datatype1,argument2 [model2] datatype2,......) -- argument等指定函数的参数，当指定参数数据类型时,不能指定长度。
RETURN datatype -- 用于指定函数返回值的数据类型
IS [AS] -- IS或者AS用于开始一个PL/SQL块
PL/SQL BLOCK; ----model是参数模式，包括输入参数(IN)、输出参数(OUT)、输入输出参数(IN OUT)，默认为输入参数(IN)。
例子：
-- 创建函数
SQL>CREATE OR REPLACE FUNCTION Get_User
RETURN VARCHAR2
IS
V_User VARCHAR2(100);
BEGIN
SELECT ename INTO V_User FROM scott.emp WHERE EMPNO='7369';
RETURN V_User;
END;
-- 调用函数
一、使用变量接收函数返回值
SQL>DECLARE
username VARCHAR2(50);
BEGIN
username := Get_User;
DBMS_OUTPUT.PUT_LINE(username);
END;
二、在SQL语句中直接调用函数。
SQL>SELECT Get_User FROM DUAL;
三、在包中，如DBMS_OUTPUT调用函数
DBMS_OUTPUT.PUT_LINE('用户名: '|| Get_User);
-- 删除函数
DROP FUNCTION 函数名称；
注意：由于函数是必须要返回数据,所以只能作为表达式的一部分调用，可以在SQL中调用函数，
但并不是所有的·函数都可以在SQL语句中调用，带OUT和IN OUT 参数的函数不能在SQL中被调用，
而且必须只能使用SQL所支持的标准数据类。
