oracle学习之存储过程(一)

oracle存储过程简介

存储过程是事先经过编译并存储在数据库中的一段sql语句的集合，适当的使用存储过程会提升开发人员的工作效率，减少数据库和应用服务器之间的数据传递，提升数据的处理效率

使用存储过程的优点

1.模块化执行 只需要在创建一次过程，就已经编译并存储到数据库中，直接写sql的话会分析检查等再执行，使用存储过程效率会更高，就可以在系统中调用无数次，直接写sql会导致sql注入等安全问题
2.创建过程不会消耗系统资源，只有被调用再会执行
3.存储过程可以用于降低网络流量，存储过程代码直接存储于数据库中，所以不会产生大量T-sql语句的代码流量
4.可维护性高，更新存储过程通常比更改、测试以及重新部署程序集需要较少的时间和精力。
5.代码精简一致，一个存储过程可以用于应用程序代码的不同位置。
6.使用存储过程使您能够增强对执行计划的重复使用，由此可以通过使用远程过程调用 (RPC) 处理服务器上的存储过程而提高性能。RPC 封装参数和调用服务器端过程的方式使引擎能够轻松地找到匹配的执行计划，并只需插入更新的参数值。
7.增强安全性：

a、通过向用户授予对存储过程（而不是基于表）的访问权限，它们可以提供对特定数据的访问；

b、提高代码安全，防止 SQL注入（但未彻底解决，例如，将数据操作语言－－DML，附加到输入参数）；

c、SqlParameter 类指定存储过程参数的数据类型，作为深层次防御性策略的一部分，可以验证用户提供的值类型（但也不是万无一失，还是应该传递至数据库前得到附加验证）。

使用存储过程的缺点

大量的利用过程，会对服务器压力比较大。

plsql编程

概念

procedure language/SQL
hello world，程序结构 ，变量，流程控制，游标
plsql 是 oracle 对 sql 语言的过程化的拓展（类似于 VB Basic）
指在sql命令中增加过程处理语句（分支，循环等），是sql具有过程处理能力

程序结构

通过plsql Developer工具的Test Window创建程序模板或者通过语句在SQL Window编写
tips:plsql语言不区分大小写
PL/SQL 可分为三部分
1.声明部分，可执行部分，异常处理部分

DECLARE
	--声明变量，游标
	I INTEGER;
BEGIN
	--可执行语句
	-- 判断循环等
	--异常处理
	-- 这是注释
END;		

declare 用来声明变量或者游标（结果集类型变量），如果无变量可以省略掉
hello world plsql示例
DBMS_output.put_line(‘hello world’); --oracle内置的输出程序包

示例

BEGIN
	-- 打印字符串
	DBMS_output.put_line('hello world');
END;	

在命令行中结束plsql 代码块要 另起一行加上 /,
因为命令窗口中 sqlplus plsql 输出选项默认是关闭的，所以我们要看到打印效果 要打开输入
set serverouput on
–BEGIN
-----
– end
才能输出
程序结束后没显示输出结果，可能是忘记打开输出选项
set serveroutput on

plsql 中常见的变量

1.普通数据类型
（CHAR,VARCHAR2,DATE,NUMBER,BOOLEAN,LONG）
2.特殊变量类型（引用型变量，记录型变量）
变量名 变量类型（变量长度）
例如：v_name varchar2（20）；
引用型 – 变量类型取决于 表中字段的类型
记录型 – 接受的是一整条记录的值

普通变量

变量赋值的方式有两种：
1.直接赋值
:=
v_name := ‘张三’
2.语句赋值
select … into … 赋值
语法 （selectd 值 into 变量）

示例

--打印 姓名 工资 地址
DECLARE
   -- 姓名
   v_name VARCHAR2(200) := '张三';
   --工资
   v_sal NUMBER;
   --地址
   v_addr VARCHAR2(200);
BEGIN
   -- 直接赋值
   v_sal := 1500;
   --语句赋值
   select '这就是语句赋值' into v_addr from dual;
   --DBMS_output.put_line('姓名'||v-name||‘工资’:v_sal||'地址'||v_addr);
END	

引用型变量

变量的类型和长度取决于表中字段的类型和长度
通过表名.列名%TYPE指定变量的类型和长度例如
v_name emp.ename%TYPE;
示例

--引用示例
DECLARE
   -- 姓名
   v_name emp.ename%TYPE;
   --工资
   v_sal emp.cql%TYPE;
BEGIN
   --语句赋值
   select ENAME,SAL into v_name,v_sal from emp where EMPNO = '7829';
   --DBMS_output.put_line('姓名'||v_name||‘工资’:v_sal||'地址'||v_addr);
END	

查询的字段和赋值的变量顺序 个数 类型 要一致

记录型变量

接受表中的一整行记录，相当于 java的对象
语法： 变量名称 表名%ROWTYPE ,例如：v_emp emp%ROWTYPE

--记录示例
DECLARE
   -- 姓名
   v_row emp%ROWTYPE;

BEGIN
   --语句赋值
   select * into v_row from emp where EMPNO = '7829';
   --DBMS_output.put_line('姓名'||v_row.ename||‘工资’:v_sal||'地址'||v_addr);
END	
-- 不推荐

select 返回的值 和定义的行变量

流程控制

条件分支

语法

BEGIN 
	IF 条件 THEN 执行1 
	ELSIF 条件2 THEN 执行2
	ELSE 执行3
	
	END IF;
END;	

例子：

-- 判断 scott 用户的 emp 表
DECLARE
	-- 声明变量接受emp中的数量
	v_count NUMBER;
BEGIN
	--查询表中的数量赋值给变量
	SELECT COUNT(0) INTO v_count FROM EMP;
	-- 判断打印
	IF v_count >20 THEN 
	DBMS_output.put_line('emp数量超过20条'||v_count);
	ELSIF v_count >10 THEN 
	DBMS_output.put_line('emp数量超过10条'||v_count);
	ELSE 
	DBMS_output.put_line('emp数量10条一下'||v_count);
	END IF; -- 关闭判断 有开有关
END;	

循环

在 oracle 中有三种循环方式，这里之间LOOP循环
语法：
GOTO 用法

DECLARE
  x number;
BEGIN
  x := 9;
  <<repeat_loop>> --循环点
  x := x - 1;
  DBMS_OUTPUT.PUT_LINE(X);
  IF X > 0 THEN
    GOTO repeat_loop; --当x的值小于9时,就goto到repeat_loop
  END IF;
END;

FOR

 DECLARE
   X number; --声明变量
 BEGIN
   x := 1; --给初值
   FOR X IN REVERSE 1 .. 10 LOOP
     --reverse由大到小
     DBMS_OUTPUT.PUT_LINE('内:x=' || x);
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('end loop:x=' || X); --x=1
 END;

WHILE

 DECLARE
    x number;
 BEGIN
   x := 0;
   WHILE x < 9 LOOP
     x := x + 1;
     DBMS_OUTPUT.PUT_LINE('内:x=' || x);
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('外:x=' || x);
 END;

LOOP
%NOTFOUND – 判断存不存在

 DECLARE
   x number;
 BEGIN
   x := 0;
   LOOP
     x := x + 1;
   
     EXIT WHEN x > 9; -- 退出循环条件
     DBMS_OUTPUT.PUT_LINE('内:x=' || x);
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('外:x=' || x);
 END;

示例

DECLARE 
	--	声明循环条件
	V_NUM NUMBER := 1;
BEGIN
	LOOP
		EXIT WHEN V_NUM>10;
		DBMS_OUTPUT.PUT_LINE(V_NUM);
		--循环变量的自增
		V_NUM := V_NUM +1;
	END LOOP;
END;	

无参游标

用于临时存储一个查询中的多行数据（结果集，类似于java的jdbc中的ResultSet集合）通过遍历数据，可以逐行的访问处理结果集的数据。
游标的使用	声明 -->打开 -->读取 -->关闭

语法

游标声明

CURSOR 游标名[(参数列表)] IS 查询语句

游标打开

OPEN 游标名

游标取值

FETCH 游标名 INTO 变量列表

游标关闭

CLOSE 游标名;

游标的属性

游标的属性	返回值类型	说明
%ROWCOUNT	整型	获得FETCH语句返回的数据行数
%FOUND	布尔型	最近的FETCH语句返回一行数据则为真，否则为假
%NOTFOUND	布尔型	与%FOUND属性返回值相反
%ISOPEN	布尔型	游标打开时值为真，否则为假

创建和使用

使用游标查询emp表中所有的员工姓名和工资，并以此打印出来

DECLARE 
	-- 声明游标
	CURSOR c_emp IS SELECT ENAME,SAL FROM EMP;
	--声明变量接受游标中的元素
	v_ename emp.ename%TYPE;
	v_sal emp.sal%TYPE;
BEGIN 
	--打开游标
	OPEN c_emp;
	--遍历游标
	LOOP
	-- 获取游标中的数据
	FETCH c_emp INTO v_ename,v_sal;	
	-- 退出循环条件
	EXIT WHEN c_emp%NOTFOUND;  -- 游标中没东西退出
	--输出
	DBMS_OUTPUT.PUT_LINE(v_ename||'-'||v_sal);
	END LOOP;
	--关闭游标
	CLOSE c_emp;
END;	

有参游标

使用游标查询emp表中某部门的员工姓名和工资，并以此打印出来

DECLARE 
	-- 声明有参游标
	CURSOR c_emp(v_deptno emp.deptno%TYPE) IS SELECT ENAME,SAL FROM EMP where deptno = v_deptno;
	--声明变量接受游标中的元素
	v_ename emp.ename%TYPE;
	v_sal emp.sal%TYPE;
BEGIN 
	--打开游标 时传参
	--10号部门
	OPEN c_emp(10);
	--遍历游标
	LOOP
	-- 获取游标中的数据
	FETCH c_emp INTO v_ename,v_sal;	
	-- 退出循环条件
	EXIT WHEN c_emp%NOTFOUND;  -- 游标中没东西退出
	--输出
	DBMS_OUTPUT.PUT_LINE(v_ename||'-'||v_sal);
	END LOOP;
	--关闭游标
	CLOSE c_emp;
END;	

注意 %NOTFOUND 属性默认为false,所以在循环中要注意判断条件的位置，如果先判断再FETCH，会导致最后一条的记录被多打印两次（多循环一次默认）

存储过程语法

概念，无参，有参存储（输入，输出）
存储过程可以理解为将上面的一个个plsql 程序存储起来反复调用

语法

CREATE OR REPLACE PROCEDURE 过程名称[(参数列表)] IS
BEGIN

END [过程名称]

根据参数传入的类型可以分为三类
不带参数的
带输入参数的
带带输入输出参数（返回值）的

无参存储过程

通过 plsql developer 或者语句创建存储过程
1.Program Window->Procedure

CREATE OR REPLACE PROCEDURE myDemo01
as
-- 这里依然可以声明变量，只不过不用 declare 了
BEGIN
  dbms_output.put_line('hello word, my name is stored procedure');
END myDemo01;
-- 可以直接写 end; 默认就是前面的名字

create or replace procedure：关键字用来创建或覆盖一个原有的存储过程。
myDemo01：自定义的存储过程的名字。
as：关键字。
注：
在存储过程(PROCEDURE)和函数(FUNCTION)中没有区别；

   在视图(VIEW)中只能用AS不能用IS；

   在游标(CURSOR)中只能用IS不能用AS。

begin：关键字。
dbms_output.put_line(‘hello word, my name is stored procedure’); 输出内容。
end;关键字。

plsql中调用存储过程

直接 BEGIN END; 之间直接写存储过程的名字就可以调用
可以多次调用

BEGIN
   -- PLSQL调用存储过程	
   myDemo01;
   -- 可以调用多次
   myDemo01;
END;

命令窗口中调用存储过程

在命令窗口中调用存储过程
exec 存储过程名称；
如果里面有输出，不显示的话，在执行之前运行一下
set serveroutput on

set serveroutput on
exec myDemo01;

有参存储过程

示例
查询并打印某个员工（如456号员工）的姓名和薪水 存储过程：
要求，调用的时候传入员工编号，自动控制台打印

-- 查询并打印某个员工（如456号员工）的姓名和薪水
-- 要求调用的时候传入员工编号，自动控制台打印
CREATE OR REPLACE PROCEDURE P_QUERYNAMEANDSAL(IN_EMPNO IN EMP.EMPNO%TYPE) IS 
-- 声明变量接受查询结果
	V_NAME EMP.ENAME%TYPE;
	V_SAL EMP.SAL%TYPE;

BEGIN
	--根据用户传入的员工号查询姓名和薪水 
	SELECT ENAME,SAL INTO V_ENAME,V_SAL FROM EMP WHERE 
	EMPNO = IN_EMPNO;
	--打印结果
	DBMS_OUTPUT.PUT_LINE('姓名：'||v_ename||'-'||'薪水：'||v_sal);
END P_QUERYNAMEANDSAL;

调用
1.

DECLARE 
  	P_EMPNO VARCHAR2 := '465';
BEGIN 
	P_QUERYNAMEANDSAL(P_EMPNO);
END;  	

BEGIN 
	P_QUERYNAMEANDSAL('456');
END;  

3.命令行

exec p_querynameandsal(456);

带输入输出的存储过程

示例
要求输入某个员工号查询某个员工（如456号员工）的信息
要求将薪水作为返回值输出，给调用的程序使用

-- 要求输入某个员工号查询某个员工（如456号员工）的信息
-- 要求将薪水作为返回值输出，给调用的程序使用
CREATE OR REPLACE PROCEDURE P_QUERYSAL_OUT(IN_EMPNO IN EMP.EMPNO%TYPE,OUT_SAL OUT EMP.SAL%TYPE) IS
	-- 声明变量 可无
	
BEGIN
	--
	SELECT SAL INTO OUT_SAL FROM EMP WHERE EMPNO = IN_EMPNO;
	
END;

调用方式

1. declare 中声明变量接受 存储过程 返回值

DECLARE 
	--声明变量接收存储过程中的输出参数
	v_sal emp.sal%TYPE;
BEGIN 
	p_querysal_out('456',v_sal);
	dbms_output.put_line(v_sal);
END;	

SQL> var v_sal number
SQL> exec p_querysal_out('7935',:v_sal );

java中调用存储过程

查看 jdk的api
通过CallableStatement 操作函数
Connection.prepareCall 方法调用存储过程

通过 Connection.prepareCall 方法传递一个转义 的 sql 语句 调用存储过程
输入参数直接调用set 方法传递
输出参数需要注册后，执行存储过程，通过get 方法获取，参数列表的下标是从1开始的

package cn.jdy.ojdbc.test;

import java.sql.CallableStatement
import java.sql.Connection;
import java.sql.DriverManager;

public class JdbcTest{
	public static void main(String[] args)throws Exception{
		//1.加载驱动
		Class.forName("oracle.jdbc.driver.OracleDriver");
		//2.获取连接对象 两种方式 一种 oci 一种 thin
		// oci 必须要有oracle客户端或才能连接 thin 不用
		//oci jdbc:oracle:oci:@service 
		//thin jdbc:oracle:thin:@server ip: service
	// ---------thin 的三种方式--------				
		//jdbc:oracle:thin:@host:port:SID 
		//Example: jdbc:oracle:thin:@localhost:1521:orcl 
		//jdbc:oracle:thin:@//host:port/service_name 
		//Example:jdbc:oracle:thin:@//localhost:1521/orcl.city.com 
		//jdbc:oracle:thin:@TNSName 
		//Example: jdbc:oracle:thin:@TNS_ALIAS_NAME 
		String url = "jdbc:oracle:thin:@localhost:1521:myoracle";
		String uname = "scott";
		String password = "tiger";
		Connection conn = DriverManager.getConnection(url,uname,password);
		//3.获得语句对象  --调用存储过程
		String sql = "{call p_querysal_out(?,?)}";
		
		CallableStatement call = conn.perpareCall(sql);	
		//4.基于call对象对参数进行设置 设置输入参数
		//call.setInt(1,456);
		call.setString(1,"456");
		//5.设置输出参数 -- 注册输出参数
		call.registerOutParameter(2,OracleTypes.Double);
		//6.执行存储过程
		call.execute();
		//7.获取输出参数
		double sal = call.getDouble(2);
		System.out.print(sal);
		//8.释放资源
		call.close();
		conn.close()；
	}
}

trunc 函数简介

用于截取时间或者数值，返回指定的值。

语法

（1）日期处理：
　　　　TRUNC(date,[fmt])
　　 date 为必要参数，是输入的一个date日期值
　　fmt 参数可忽略，是日期格式，缺省时表示指定日期的0点。
（2）数值处理：
　　　　TRUNC(number,[decimals])
　　number 为必要参数，是输入的一个number数值
　　decimals 参数可忽略，是要截取的位数，缺省时表示截掉小数点后边的值。

举例

（1）处理日期：

–sysdate和trunc(sysdate)是不一样的 sysdate在当天的零时零分零秒等于trunc(sysdate)
select to_char(sysdate, ‘yyyy-mm-dd hh24:mi:ss’),
to_char(trunc(sysdate), ‘yyyy-mm-dd hh24:mi:ss’)
from dual t;

–年
select trunc(sysdate, ‘yy’) “当年第一天” from dual;
select trunc(sysdate, ‘yyyy’) “当年第一天” from dual;
select trunc(sysdate, ‘year’) “当年第一天” from dual;
–季度
select trunc(sysdate, ‘q’) “当前季度的第一天” from dual;
select trunc(add_months(sysdate,3), ‘Q’) -1/24 “这个季度最后一天23点” from dual;
–月
select trunc(sysdate, ‘mm’) “当月第一天” from dual;
select trunc(sysdate, ‘month’) “当月第一天” from dual;
select trunc(last_day(sysdate)+1) “下个月第一天的0点” from dual;
–周
select trunc(sysdate, ‘d’) “本周的第一天，周日起” from dual;
select trunc(sysdate,‘day’) “本周的第一天，周日起” from dual;
select next_day(TRUNC(SYSDATE ), ‘TUESDAY’ ) + 12/24 “下个星期二中午12点” from dual;
select TRUNC(LEAST(NEXT_DAY(SYSDATE, ‘SATURDAY’), NEXT_DAY(SYSDATE, ‘SUNDAY’))) + （660+10）/（2460） “下个周六日早上6点10分” from dual;
–天
select trunc(sysdate+1) “今晚12点” from dual;
select trunc(sysdate+1) + (860+30)/(2460) “明天早上8点30分” from dual;
–时
select trunc(sysdate, ‘hh’)“当前小时” from dual;
select trunc(sysdate, ‘hh24’) “当前小时” from dual;
–分
select trunc(sysdate, ‘mi’) “当前分钟” from dual;
select trunc(sysdate,‘mi’) + 10/ (24*60) "10分钟后的时间"from dual;
–秒
select sysdate + 10/(24 * 60 * 60) “10秒钟后” from dual;–trunc没有精确到秒的精度

（2）处理数值：
–123.56,将小数点右边指定位数后面的截去;
select trunc(123.567,2) from dual;
–100,第二个参数可以为负数，表示将小数点左边指定位数后面的部分截去，即均以0记;
select trunc(123.567,-2) from dual;
–123,默认截去小数点后面的部分;
select trunc(123.567) from dual;

其他使用日期函数

S_DATE := TO_CHAR(LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -7)) + 1,‘YYYY-MM-DD’);
1
从里面的函数看起：

TRUNC（）：
select trunc(sysdate) from dual --2020-05-29 今天的日期为2020-05-29；
此处就是取存过执行的时候当前的日子

ADD_MONTHS（）：
有两个参数，第一个参数是日期在这里指的就是TRUNC(SYSDATE)，
第二个参数是对日期进行加减的数字(以月为单位的)：-7 今天的日期开始 减7个月。

LAST_DAY(date)函数
：LAST_DAY函数返回指定日期对应月份的最后一天。
ADD_MONTHS(TRUNC(SYSDATE), -7)) 但是后面又+1 ;
LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -7)) + 1 :这句话的涵义:取当前日期的前7个月的这个月份的最后一天日期，+1 则为后一个月的第一天日期。

to_char(date,‘格式’)函数：
是将数值型或者日期型转化为字符型 。

将例子中时间( LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -7)) + 1)转换成后面的格式‘YYYY-MM-DD’的字符串类型；

merge into

语法

MERGE INTO merge_target target
USING (SELECT B.name,B.age,B.target_id FROM merge_source B) source
ON (target.id=source.target_id)
WHEN MATCHED THEN
UPDATE
SET target.name = source.name,
target.age = source.age
WHEN NOT MATCHED THEN
INSERT(target.name,target.age) VALUES (source.name,source.age);

MERGE INTO语法如下：

MERGE INTO table_name alias1
USING (table|view|sub_query) alias2
ON (join condition)
WHEN MATCHED THEN
UPDATE
SET col1 = col1_val1,
col2 = col2_val2
WHEN NOT MATCHED THEN
INSERT (column_list) VALUES (column_values);

其中，table_name 指的是更新的表，using()里边的指的是数据来源表/视图/子查询结果集，condition指的是连接条件，如果满足连接条件，set 字段1=值1，字段2=值2…
MATCHED 条件满足
如果连接条件不满足，则停止更新进行插入。
例子：
建表A

create table A_MERGE
(
id NUMBER not null,
name VARCHAR2(12) not null,
year NUMBER
);
commit;

建表B

create table B_MERGE
(
id NUMBER not null,
aid NUMBER not null,
name VARCHAR2(12) not null,
year NUMBER,
city VARCHAR2(12)
);
commit;

建表C

create table C_MERGE
(
id NUMBER not null,
name VARCHAR2(12) not null,
city VARCHAR2(12) not null
);
commit;

插入A

insert into A_MERGE values(1,'liuwei',20);

insert into A_MERGE values(2,'zhangbin',21);

insert into A_MERGE values(3,'fuguo',20); 

commit;

插入B

 insert into B_MERGE values(1,2,'zhangbin',30,'吉林');

insert into B_MERGE values(2,4,'yihe',33,'黑龙江');

 insert into B_MERGE values(3,3,'fuguo','','山东');

 commit;

megre into 测试

MERGE INTO A_MERGE A USING (select B.AID,B.NAME,B.YEAR from B_MERGE B) C ON (A.id=C.AID)

WHEN MATCHED THEN

UPDATE SET A.YEAR=C.YEAR

WHEN NOT MATCHED THEN

INSERT(A.ID,A.NAME,A.YEAR) VALUES(C.AID,C.NAME,C.YEAR); 

commit;

merge into 目标表 a
 
using 源表 b
 
on(a.条件字段1=b.条件字段1 and a.条件字段2=b.条件字段2 ……)  
 
when matched then update set a.更新字段=b.字段
 
when  not matched then insert into a(字段1,字段2……)values(值1,值2……)

番外-存储函数

本质上与存储过程没有区别
函数存在的意义是给存储过程使用，存储过程里面调用函数
可以在sql选中直接执行
唯一的区别，语法上 procedure 换成 function
function(输入输出参数) return 返回值类型
BEGIN end 中 return 返回值；
语法

create/replace function 函数名 （参数名 输入输出类型(IN/OUT) 字段类型） return 字段类型 is
变量名 字段类型； --函数的内部变量，需要包含字段类型的长度
begin
主体；
return(变量名)；
end 函数名；

定义函数的参数和输出类型的时候不需要考虑字段类型的长度；
函数的内部变量需包含字段类型的长度；
函数可以没有输入输出参数，但一定要有返回（return）的数据类型，因此必须有内部变量存储return的数据；
函数的输入输出参数可以在主体中直接使用。

例子

CREATE OR REPLACE FUNCTION F_MSG(
  NAME_U IN VARCHAR2,
  NAME_P IN VARCHAR2
)
   RETURN NUMBER IS
   ALL_T NUMBER;

   BEGIN 
     
   SELECT SUM(A.PRODUCT_NUM*A.PRICE)
   INTO ALL_T
   FROM
   PRODUCT_RELEASE A,USER_M B
   WHERE 
   A.USER_ID=B.USER_ID
   AND B.USER_NAME=NAME_U
   AND A.PRODUCT_NAME=NAME_P
   GROUP BY B.USER_NAME,A.PRODUCT_NAME;
   RETURN ALL_T;
   
   END F_MSG;

CREATE OR REPLACE FUNCTION F_MSG_O(
  NAME_U IN VARCHAR2,
  NAME_P OUT VARCHAR2
)
   RETURN NUMBER IS
   ALL_T NUMBER;

   BEGIN 
     
   SELECT SUM(A.PRODUCT_NUM*A.PRICE),B.USER_NAME
   INTO ALL_T,NAME_P 
   FROM
   PRODUCT_RELEASE A,USER_M B
   WHERE 
   A.USER_ID=B.USER_ID
   AND B.USER_NAME=NAME_U
   GROUP BY B.USER_NAME,A.PRODUCT_NAME;
	
   RETURN ALL_T;
   
   END F_MSG;

create or replace function get_sal
(v_id IN emp.empno%TYPE)
return number
is
    v_salary        emp.sal%TYPE   :=0;
begin
    SELECT sal INTO v_salary
    FROM emp
    WHERE empno=v_id;
    RETURN v_salary;
end get_sal;

调用
select 直接调

SELECT F_MSG('张力','护肤水') AS"总额" FROM DUAL;

call

SQL> var get_salary1 number
SQL> call get_sal(7902) into :get_salary1;    --需要用分号
--variable同var

execute

SQL> variable g_salary number
SQL> execute  :g_salary     :=get_sal(7902)    --不用分号

函数作为另一个子程序的参数

SQL> execute dbms_output.put_line(get_sal(7902));

PLSQL代码块

set serveroutput on
SQL> declare
  2    v_sal emp.sal%type;
  3  begin
  4    v_sal :=get_sal(7902);
  5    dbms_output.put_line('7902的工资是'||v_sal);
  6  END;

项目中常见应用方式
带输入参数

CREATE OR REPLACE FUNCTION SF_PUB_STRMINUSINT
(p_beg in varchar2,
 p_end in varchar2
)
return int
/**
AUTH  
FUNC  两个随机号相减得数
**/
AS
     v_ret  int;
     vs_geb  varchar2(30);
     vs_str  varchar2(30);
     vn_beg  int;
     vs_dne  varchar2(30);
     vn_end  int;
begin
    --反转
    select reverse(p_beg) into vs_geb from dual;
    --正则
    select regexp_substr(vs_geb,'[0-9]+',1,1) into vs_str from dual;
    --取得随机码
    select to_number(reverse(vs_str)) into vn_beg from dual;
    --止号
    select reverse(p_end) into vs_dne from dual;
    --正则
    select regexp_substr(vs_dne,'[0-9]+',1,1) into vs_str from dual;
    --取得随机码
    select to_number(reverse(vs_str)) into vn_end from dual;
    v_ret:=vn_end-vn_beg+1;
    return v_ret;
end SF_PUB_STRMINUSINT;　　

以对象返回数据列表

create or replace function fn_test
( pid in number
)
return
  tabTEST
is
 Result tabTEST =tabTEST();
 USERID number(12);
 VNAME varchar(200);
 orgid  number(12);
 type tab is table of number;
 tab_test1 tab;
 /***
 create or replace type ctest as object
 (
  cUser  number(12),  --不可注释
  cName  varchar(200),
  cOrg   number(12)
 )
 create or replace type tabTEST is table of ctest
 测试
 select  from table(cast(fn_test({0})as tabTEST))
 ***/
begin
 select ID bulk collect into tab_test1 from tuser where id=pid;
 Result =tabTEST();
 for i in 1..tab_test1.count loop
  select ID,name,belongorg into USERID,VNAME,orgid from tuser where ID=tab_test1(i);
  
  Result.extend;
  Result(Result.count)=ctest(NULL,NULL,NULL);
  Result(Result.count).cUser=USERID;
  Result(Result.count).cName=VNAME;
  Result(Result.count).cOrg =orgid;
  end loop;
  return(Result);
  end fn_test;　;

触发器

当用户执行了 insert update delete 这些操作后，可以触发一系列其他的动作、业务
语法
在动作执行之前，做一些逻辑处理
如 插入之前做数据校验
语句级触发器 不管影响多少行，只触发一次

行级触发器（for each row） 影响多少行，触发多少次
:old --改之前的数据
:new --改制后的数据

create or replace trigger 触发器的名字  
before|after
insert|update|delete
 on 表名
[for each row] -- 加上它是行级触发，影响多少行，触发多少次，否则就是语句级触发，只触发一次
declare

begin 

end;

链接: 触发器.

异常

declare 

begin

exception
	when 异常1 then s1
	when 异常21 then s2	
	when others then sT
	处理其他异常
end;

zero_divide 除0异常
value_error 类型转换异常
too_many_rows
no_data_found

--测试自定义异常
 
create or replace package body exception_test_pkg is


  exp_difin_error exception;
  g_msg_data varchar2(2000);

  procedure test(p1 in number, p2 in number) is
  begin
    if p1 > p2 then
      g_msg_data := 'p1大于p2';
      raise exp_difin_error;
    
    end if;
  end;

  procedure main(p1 in number, p2 in number) is
  begin
    
    test(p1, p2);
    
  exception
    when exp_difin_error then
      raise_application_error(-20001, g_msg_data || ';  ' || SQLERRM);
  END;

end exception_test_pkg;