PLSQL中Function和Procedures的简单使用

FUNCTION(函数方法):

  • 作用:
用于返回特定的数据(数据处理),关注点在于返回的结果。
  • 格式:
CREATE OR REPLACE FUNCTION 函数名(参数类型)
RETURN 类型
IS 名称 类型
BEGIN 函数主体
EXCEPTION
END 函数名;
  • 关键字:
RETRUN : 定义函数返回的类型;
IS :定义变量,可用于赋值;
BEGIN :函数方法体,结束通过return()返回结果;
EXCEPTION :异常处理(可省略);
END :函数结束符。
  • 示例:
CREATE OR REPLACE FUNCTION get_employee_salary(empoyee_id NUMBER)
RETURN NUMBER
IS
salary NUMBER;
BEGIN
SELECT e.salary
INTO salary
FROM employees e
WHERE e.employee_id = empoyee_id;
RETURN(salary);
EXCEPTION
WHEN no_data_found THEN
RETURN(-1);
WHEN OTHERSTHEN
RETURN(-2);
END get_employee_salary;
  • 测试:
方式一: 鼠标右击方法名,在出现的菜单中点击 test,会出现Test Window窗口,且窗口下方有方法参数的输入框(Variable),输入对应参数后,按 f8 即执行测试,并返回测试结果;
方式二:在SQL Window窗口,编写sql语句直接调用function。
  • 方法体中可能用到的语句:
1. 条件判断:
IF 条件
ELSE
END IF;
2. 循环遍历:
FOR 变量名 IN (SELECT语句)
LOOP RESULT := 变量名.对应字段;
END LOOP;
2. 将查询结果赋值给变量:
SELECT 字段INTO 变量名FROM 表名where 条件


PROCEDURE(存储过程):
  • 作用:
用作完成特定功能的sql语句,关注点在于过程中的处理。
  • 格式:
CREATE OR REPLACE PROCEDURE 名称(参数名称 in/out 类型)
BEGIN 程序体
EXCEPTION
END 名称;
  • 关键字:
输入参数使用in关键字标识
输出参数使用out参数表示
( Procedure的参数模式:
IN:默认模式.在调用Procedure的时候,Procedure的实参的值被传递到该Procedure,在Procedure的内部,Procedure的形参是只读的:
OUT:在调用Procedure时,任何的Procedure的实参都将被忽略,在Procedure的内部,形参是只可写的:
IN OUT:IN与OUT的组合.在调用Procedure的时候,实参的值可以被传递给该Procedure; 在其内部,形参也可以被读出也可以被写入; 该Procedure结束时,控制会返回给控制环境,而形参的内容将赋给调用时的实参. )
  • 示例:
CREATE OR REPLACE PROCEDURE update_employee_salary(empoyee_id IN NUMBER,
employee_name OUT VARCHAR2,
salary OUT VARCHAR2)
IS
BEGIN
BEGIN
SELECT e.first_name || ' ' || e.last_name employee_name,
e.salary
INTO employee_name,
salary
FROM employees e
WHERE e.employee_id = empoyee_id;
EXCEPTION
WHEN no_data_found THEN
employee_name := 'E';
salary:= 0;
WHEN OTHERS THEN
employee_name := 'N';
salary:= 0;
END;
UPDATE employees e
SET e.salary = e.salary + 2000
WHERE e.employee_id = empoyee_id;
ENDupdate_employee_salary;

----------------------------------------------------------------------------------------------------------------

  • 调用及测试方法:
1. Command Window:
execute/exec 方法名(参数..);
2. SQL Window:
declare
参数名 类型 :=值;
begin
方法名(参数);
dbms_output.put_line(参数); ---输出打印作用
end;
3. 右击该过程名,并点击test,输入相应参数,然后f8执行.

---------------------------------------------------------------------------------------------------------------

  • 方法体中可能用到的语句:
SELECT 字段 INTO变量名 FROM表名 WHERE条件; 将查的结果赋值给变量
SELECT 字段 INTO变量名 FROM DUAL; 将字段值赋值给变量
遇到例如像 '''' || p_calcNo ||'''' 这样的语句,红色' 作用是对字符串定界作用, 蓝色'' 作为字符串一部分(plsql中需要用 '' 来表示 ' ),下面有详细解释;


知识补充:
  • 可能用到的函数:
1) 处理字符的函数
|| 或 CONCAT---并置运算符。
格式∶CONCAT(STRING1, STRING2)
例:’ABC’|| ’DE’=’ABCDE’
CONCAT(‘ABC’,’DE’) =’ABCDE’
ASCII---返回字符的ASCII码。
例:ASCII(‘A’) = 65
CHR---返回指定ASCII码的字符。
例:CHR(65) = ‘A’
INSTR---搜索子串位置
格式∶INSTR(STRING , SET[ , 开始位置[ , 出现次数]])
例∶ INSTR (‘this is a test’ , ‘i’ , 1,2)=6
INITCAP---将字符串每个单词首字母均变为大写
例: INITCAP(‘this is a test’)=’ This Is A Test’’
LENGTH----计算串长
格式∶ LENGTH(string)
RPAD,LPAD---右填充、左填充。默认为填充空格。
格式: RPAD(字符串 , 字符个数 , 填充字符)
例: RPAD(‘ABC’ , 6 , ’H’)=’ABCHHH’
LTRIM,RTRIM-----左右截断。默认为删除空格。
格式∶ LTRIM(STRING[,’SET’])
例∶ LTRIM(‘***tes*t***’ , ’*’)=’ tes*t***’
LOWER----将字符串转换为小写
格式∶LOWER(string)
UPPER----将字符串转换为大写
格式∶UPPER(string)
SUBSTR----提取子串。START为正数时从左开始、为负数时从右开始
格式∶ SUBSTR(STRING , START [ , COUNT])
例∶ SUBSTR(‘WORDSTAR’ , 2 , 3)=’ ORD’
REPLACE---搜索指定字符串并替换
格式∶REPLACE(string , substring , replace_string)
例∶ REPLACE(‘this is a test’ , ‘this’ , ‘that an’)=’that an is a test’
TRIM---删除字符串前缀或尾随字符
格式∶TRIM( [LEADING | TRAILING |BOTH] [ trimchar FROM ] string)
LEADING---删除前缀字符
TRAILING---删除后缀字符
BOTH---前后缀字符均删除(默认方式)
Trimchar---指定删除的字符
注:INSTR,LENGTH,SUBSTR加B时针对字节.

2) 处理数字的函数
LEAST---返回参数列表中的最小值。返回参数类型以第一参数为准
格式∶LEAST(value,value,value,value,value,….)value为数字或字符串
【PLSQL中Function和Procedures的简单使用】例∶ LEAST(1,2,5,-10,9)= -10
SIGN---返回参数的符号位,负数--- -1,0----0,正数---1
格式∶SIGN(value)
CELL---返回大于等于特定值的最小整数
格式∶CELL(value)
例∶ CELL(-10,9)= -10

3)处理日期
SYSDATE---系统时间。精确至秒
ADD_MONTH—加减月份。numvalue为负数时减去相应月份
格式: ADD_MONTH (data1,numvalue)
MONTHS_BETWEEN---返回两日期之间的月数,当data1
格式: MONTHS_BETWEEN(data1,data2)
LAST_DAY---返回指定日期的最后一天。
格式∶LAST_DAY(date)
TRUNC---将日期按照format格式截短,缺省为DD(不是四舍五入)
格式:TRUNC(date, format)
例∶ TRUNC (to_date(‘2002-11-07’,’yyyy-mm-dd’),’MM’)= 2002-11-01
4)分组函数

返回基于多个行的单一结果.
常用函数:
AVG---求平均值
COUNT---返回查询的行数
MAX---返回查询列的最大值
MIN---返回查询列的最小值
SUM---返回查询列的总和
MAX, MIN常与GROUP BY配套使用

5)转换函数

TO_DATE---转换字符串为日期型
格式∶ TO_DATE(STRING[,’FORMAT’])
TO_CHAR---转换日期型或数值型为字符串。最重要的函数之一.其FORMAT格式多种多样
格式∶TO_CHAR(DATE [,’FORMAT’])
FORMAT---具体格式参考ORACLE8i DBA 宝典P835数字格式元素
P836 日期格式元素
TO_NUMBER---转换字符串为数字
格式∶TO_NUMBER(string [ , format])
FORMAT---具体格式参考ORACLE8i DBA 宝典P835数字格式元素
6)其他

DECODE---IF语句的另一形式。将输入数值与参数列表比较,返回对应值。应用于将表的行转换成列以及IF语句无法应用的场合。当与SIGN联合使用时功能扩展,可以判断大于小于的情况.
格式: DECODE(input_value , value1 , result1 , value2 , result2 , ….defult_result)
例∶ DECODE(VALUE,1,100,2,300,500)
当VALUE=https://www.it610.com/article/1时返回100
当VALUE=https://www.it610.com/article/2时返回300
否则返回500
DECODE(SIGN(VALUES-100), -1,-10,1,10,0)
当VALUE<100时返回-10
当VALUE>100时返回10
当VALUE=https://www.it610.com/article/100时返回0
SELECT SUM(DECODE(EST_INT_KEY,77771,1,0)) A,
SUM(DECODE(EST_INT_KEY,77772,1,0)) B,
SUM(DECODE(EST_INT_KEY,77773,1,0)) C
FROM PMS_BLK
NVL---空值置换
格式: NVL(value,替换值)
例: NVL(value,’nullvalue’)当value为NULL值时返回nullvalue否则返回value的值
VSIZE---以字节为单位返回数据类型尺寸
格式∶VSIZE(数据类型)
例∶ VSIZE(SYSDATE)=8
USER---得到当前用户名
ROWNUM--- oracle系统顺序分配为从查询返回的行的编号,返回的第一行分配的是1,第二行是2,依此类推,这个伪字段可以用于限制查询返回的总行数。不能以任何基表的名称作为前缀。可以用在限制返回记录条数的地方不受ORDER BY的影响.
例∶ select rownum,month,sell from sale where rownum=1
或select rownum,month,sell from sale where rownum<2(返回第一条记录)
返回rownum在4—10之间的记录
select rownum,month,sell from sale where rownum<10
minus
select rownum,month,sell from sale where rownum<5;
以下均错误:
select rownum,month,sell from sale where rownum=2(1以上)
select rownum,month,sell from sale where rownum>2
ROWID---数据行在物理磁盘上的物理地址,Oracle通过ROWID来定位数据的具体位置,这是存取表中数据的最快的方法.
RETURN---用于函数和过程中.遇见RETURN程序立刻中断,返回,不再执行剩余部分.
SQLCODE---返回ORACLE错误号。
SQLERRM---返回ORACLE错误信息。

--------------------------------------------------------------------------------------------------------------

[关于符号‘’的使用与解释:]
例如:
sql_string:='alter session set nls_date_language='''||'AMERICAN'||'''';
execute immediate sql_string;
1. || 的作用是连接字符串,即拼接作用。
如:
字符串常量连接: 'abc'||'def'; ---'abcdef'
字符串变量连接: v_variable1 || v_variable2 等。 ---两字符串变量相拼接
2. 首先, ' 有两个作用:
1).对字符串定界 ’。
如v_sqlstr :='abcdef';
就界定了一个字符串abcdef; ‘本身不是字符串的一部份。
2). ' 本身作为一个字符,' 是字符串的一部份。
如:v_sqlstr :=''''; 指代字符串' 。 这时,' 需要转义。
begin
dbms_output.put_line('''');
end;
/
'
PL/SQL procedure successfully completed.
要表示一个' 字符 , plsql中需要用'' 来表示。
‘’ 相当于字符 ‘。
输出字符a:
dbms_output.put_line('a');
输出字符‘:
dbms_output.put_line('''');
3. 上述SQL语句中开始的alter前面的'不用转义,
因为它是定界符。
4.我们知道:
alter session set nls_date_language='AMERICAN';
是完整的sql语句,
其中的两个' 在sql 中 ,是定界符,但是到了plsql中,整个sql语句成了一个字符串,所以'成为了两个字符。
字符就需要转义,
所以:
nls_date_language='
在plsql字符串中应该写成:
nls_date_language=''
5. 如果我们做如下置换:
假设:表定界的' 可以用 () 来置换,
表字符的' 可以用 " 来置换,那么:
sql_string 可以写成:
sql_string := (alter session set nls_date_language=")||(AMERICAN)||(");

    推荐阅读