oracle 函数

函数创建：

CREATE [OR REPLACE] FUNCTION [schema.] function_name [parameter_list]
{AS | IS}
declaration_section
BEGIN
　　executable_section
EXCEPTION
　　exceptioon_section
END [function_name];

 

创建一个函数：

CREATE OR REPLACE FUNCTION f1(par1 IN NUMBER DEFAULT 500)
RETURN NUMBER 
IS 
V1 NUMBER;
BEGIN
SELECT 1 INTO V1 FROM DUAL;
RETURN(V1);
END;

 

函数的调用：
可以在SQL语句中调用:SELECT f1(2) FROM dual;
可以在表达式中调用函数：IF f1(1)+7>10 THEN ..
函数的编译
alter function f1 COMPIL;

--1.用函数返回一个结果集--

CREATE OR REPLACE FUNCTION fun_getresult
RETURN pack_return_result.type_cursor IS
p pack_return_result.type_cursor;
BEGIN
OPEN p FOR
SELECT name,producer,price FROM phone_info 
WHERE price > 900;    
RETURN(p);
END;
SQL> var var_bind_ref REFCURSOR;
SQL> execute :var_bind_ref := fun_getresult();

PL/SQL 过程已成功完成。

已用时间: 00: 00: 00.00
SQL> PRINT var_bind_ref;

NAME PRODUCER PRICE
-------------------- ------------------------------ ----------
iphone APPLE 3999
C510c 爱立信 1000

已选择2行。

 

--2.在函数中执行DDL--

CREATE OR REPLACE FUNCTION f2(tablename IN VARCHAR2) 
RETURN NUMBER
IS
PRAGMA AUTONOMOUS_TRANSACTION;--自治事务，
V1 NUMBER;
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE '|| tablename ||'(name VARCHAR2(20))';
RETURN(V1);
END;
--运行f2（），创建一个新的table bb。
DECLARE 
v_r NUMBER;
BEGIN
v_r :=f2('bb');
END;

（自治事务机制允许用户在事务中建立一个新的事务，也就是事务中嵌套一个新事务
把这个新事务叫做自治事务，把原来的事务叫主事务，或父事务。自治事务的提交和回滚
不会影响到父事务的状态。自治事务由主事务调用，但是独立于主事务，在自治事务被调
用执行的时候，主事务被挂起，在自治事务内部，可以执行一系列的DML语句，并且可以提交
或回滚，使用PRAGMA AUTONOMOUS_TRANSACTION指定自治事务。）

--3在函数中执行DML---

CREATE OR REPLACE FUNCTION f_phone RETURN VARCHAR2 IS
BEGIN
INSERT INTO phone_info VALUES('iphone5c','apple',2890);--插入数据
COMMIT;
RETURN 'ok';
END;
--在块中调用函数f_phone:---
DECLARE 
v_r VARCHAR2(100);
BEGIN
v_r :=f_phone;
dbms_output.put_line(v_r);
END;

 

各种常用函数 略。