oracle 存储过程和函数

最新推荐文章于 2022-11-16 18:44:43 发布

tan

最新推荐文章于 2022-11-16 18:44:43 发布

阅读量874

点赞数

分类专栏： datebase 文章标签： oracle 存储 list csv function user

datebase 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

oracle 存储过程和函数

--子程序
/*
子程序是被命名的PL/SQL块，可以带参数，多次被调用，模块化
过程&&函数
过程：执行特定操作
函数：返回特定数据
定义：
CREATE [OR REPLACE] PROCEDURE procedure_name(argument [mode] datatype...)
IS|AS
--这里可以声明一些变量，相当于declare块，不过没有declare关键字
BEGIN
statement...
END;
-------******-------
procedure_name 过程名
argument 参数名
mode 参数类型， IN 输入参数 || OUT 输出参数 ||IN OUT . 默认是输入参数
datatype 参数类型，不需要指定长度
*/
--不带参数
CREATE OR REPLACE PROCEDURE p_time
IS
BEGIN
dbms_output.put_line(sysdate);
END;
--调用
call p_time();
--默认IN型参数
CREATE OR REPLACE PROCEDURE emp_1(num int)
AS
v_name emp.ename%type;
v_deptno emp.deptno%type;
BEGIN
IF num<10000 THEN--根据传入的参数，是否执行查询
select ename,deptno into v_name,v_deptno from emp where emp.empno=num;
dbms_output.put_line(v_name||'--'||v_deptno);
ELSE
dbms_output.put_line('too big');
END IF;
END;
--调用
BEGIN
emp_1(77880);
END;
--明确定义参数模式
/*
IN read-only ：pass values into 相当于一个常量
OUT write-only ：pass values back 相当于一个变量
IN OUT read/write
*/
CREATE OR REPLACE PROCEDURE emp_count_1(v_deptid IN int,v_count OUT int)--一个IN,一个OUT
IS
BEGIN
select count(emp.empno) into v_count from emp where emp.deptno=v_deptid;
dbms_output.put_line(v_count);
END;
--调用
DECLARE
v_count int;
BEGIN
--传递OUT类型的参数是传递一个类型相符的变量，因为out的类型的是要作为输出的，所以需要具体的变量来存放
emp_count_1(10,v_count);
--传递参数默认按定义顺序，但也可以如下按照参数名称
emp_count_1(v_count=>v_count,v_deptid => 10);
--当然也可以两种方式混合使用
emp_count_1(10,v_count => v_count);
END;
--感觉out的参数很麻烦的，还不如在过程内直接定义一个变量
CREATE OR REPLACE PROCEDURE emp_count_2(v_deptid IN INT)
IS
v_count int;
BEGIN
select count(emp.empno) into v_count from emp where emp.deptno=v_deptid;
dbms_output.put_line(v_count);
END;
--调用
BEGIN
emp_count_2(10);
END;
--过程调用过程
CREATE OR REPLACE PROCEDURE p_main
IS
BEGIN--调用上面定义的两个过程
p_time();
emp_count_2(10);
END;
--调用
BEGIN
p_main();
END;
--函数
/*
有返回值，其它的和过程差不多
CREATE [OR REPLACE] FUNCTION function_name(argument [mode] datatype...)
RETURN datatype --返回类型必须
IS|AS
[variable datatype...]
BEGIN
statement...
Pl/SQL块中必须要有一条return语句
END;
*/
--
CREATE OR REPLACE FUNCTION get_name(u_id IN int) RETURN varchar2
AS
v_name emp.ename%type;
BEGIN
select ename into v_name from emp where emp.empno=u_id;
RETURN v_name;
END;
--调用
DECLARE
v_name emp.ename%type;
v_job emp.job%type;
BEGIN
v_name :=get_name(7788);--直接给变量赋值
select job into v_job from emp where ename=get_name(7788) and rownum<2;--在查询语句中调用
dbms_output.put_line(v_name);
END;
--返回record
CREATE OR REPLACE FUNCTION get_record(pno in number) RETURN emp%rowtype
IS
v_record emp%rowtype;
BEGIN
select * into v_record from emp where empno=pno;
return v_record;
END;
--
DECLARE
v_record emp%rowtype;
BEGIN
v_record := get_record(7788);
dbms_output.put_line(v_record.ename||'--'||v_record.job);
END;
--如果一个函数没有参数，那么调用他的时候可以不用加()
--exp
CREATE OR REPLACE FUNCTION get_num(v_id in int) RETURN INT
IS
CURSOR v_cs is select count(empno) tt,max(empno) mx from emp where emp.deptno=v_id group by deptno;
v_num v_cs%rowtype;
BEGIN
OPEN v_cs;
FETCH v_cs into v_num;
dbms_output.put_line(v_num.tt||'--'||v_num.mx);
close v_cs;
RETURN v_num.tt;
END;
--
select get_num(10) from dual;
--综合例子
--统计emp表中每个部门的人员
DECLARE
--声明一个嵌套表用来存放要查询的部门编号
TYPE v_array IS VARRAY(5) OF INT;
v_arr v_array;
--声明一个嵌套表用来存放每一个部门的员工名字
TYPE v_list_type IS TABLE OF VARCHAR2(30);
v_list v_list_type := v_list_type();
--声明一个函数根据部门查询统计该部门的员工总数和员工名字
FUNCTION get_num(v_dpno IN INT) RETURN v_list_type
IS
v_num INT;
v_i int :=1;
v_name varchar2(30);
v_names v_list_type := v_list_type();
TYPE v_cs_type IS REF CURSOR;
v_cs v_cs_type;
BEGIN
--统计员工总数
select count(empno) into v_num from emp where emp.deptno= v_dpno;
dbms_output.put_line('编号是：'||v_dpno||'的部门的员工数是：'||v_num);
--查询该部门员工名字
OPEN v_cs FOR select ename from emp where emp.deptno =v_dpno;
LOOP
FETCH v_cs into v_name;
EXIT WHEN v_cs%NOTFOUND;
v_names.EXTEND;
v_names(v_i) :=v_name;
v_i :=v_i+1;
END LOOP;
RETURN v_names;--返回查询的员工的结果集
END;
BEGIN
v_arr :=v_array(10,20,30);
FOR i in 1..v_arr.count LOOP
v_list := get_num(v_arr(i));
FOR j in 1..v_list.COUNT LOOP
dbms_output.put_line(v_list(j));
END LOOP;
END LOOP;
END;
/*
--输出结果
编号是：10的部门的员工数是：3
CLARK
KING
MILLER
编号是：20的部门的员工数是：5
SMITH
JONES
SCOTT
ADAMS
FORD
编号是：30的部门的员工数是：6
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES
*/
--查看当前用户的过程和函数
select object_name,created,status from user_objects
where object_type in('PROCEDURE','FUNCTION');
--查看过程源代码
/*
创建过程后，ORACLE将过程名，源代码及执行代码放到数据字典中。
调用时，程序按照其执行代码直接执行，而不需要从新解析代码。
所以使用子程序性能要优于执行SQL语句。数据字典表 USER_SOURCE
*/
select text from user_source where name ='EMP_1'; --字符串要大写
--删除过程
--DROP PROCEDURE procedure_name; --procedure_name过程名字