oracle存储过程

最新推荐文章于 2024-06-05 16:17:52 发布

原创最新推荐文章于 2024-06-05 16:17:52 发布 · 304 阅读

0 ·

CC 4.0 BY-SA版权

ORACLE数据库专栏收录该内容

6 篇文章

订阅专栏

--匿名块

--存储过程

--1 无参存储过程

create or replace procedure 存储过程名称 is

begin

dbms_output.putline('你好'||user);

dbms_output.putline('现在是'||to_char（sysdate,'yyyy-mm-dd'）);

-----测试用例 ,在代码块中对存储过程进行调用

begin

存储过程名称

end

--2 有参存储过程

----带有输入输出参数的存储过程

--1 使用标量变量作为输入参数

create or replace procedure add_dept1 （deptno1 dept.deptno%type, dname dept.deptno%type, loc dept.deptno%type） is

begin

insert into dept values (deptno, dname, loc);

commit;

------异常块

exception

when dup_val_on_index then----------dup_val_on_index 就是表示主键冲突异常

dbms_output.putline('主键冲突，请重新输入');

end add_dept1;

---测试

BEGIN
--add_dept1(70,'山科大','青岛开发区');
--add_dept1(deptno=>80,dname=>'青岛大学',loc=>'崂山区'); ---=>表示的是指向
add_dept1(&deptno,'&dname','&loc'); ------------------------------&deptno 表示从键盘接收值
END;

----2使用记录类型作为输入参数

create or replace procedure pro_dept2 (dept_record dept%rowtype) is

begin

insert into dept valuses dept_record;

exception

when dup_val_on_index then

dbms_output,putlne('主键冲突');

end pro_dept;

-----------------------测试用例

declare

dept_record dept%rowtype;

begin

dept_record.deptno:=&deptno;

dept_record.dname:=&dname;

dept_record:loc：=&loc;

pro_dept(dept_record);

end;

--3 使用集合类型作为输入参数

CREATE TABLE department AS
SELECT * FROM dept;

declare

type deptno_table_type is table of number(3);

type dname_table_type is table of varchar2(20);

type loc_table_type is table of varchar2(20);

create or replace procedure pro_dept3 ( deptno_table deptno_table_type, dname_table dname_table_type, loc_table loc_table_type) is

begin

for i in 1..deptno_table.count loop---------------count 查行数

insert into department values(deptno_table(i),dname_table(i),loc_table(i));

end loop;

EXCEPTION
WHEN dup_val_on_index THEN
dbms_output.put_line('主键冲突，请重新设置');
WHEN SUBSCRIPT_BEYOND_COUNT THEN
dbms_output.put_line('部分集合的元素数值不够');

end pro_dept3;

--------测试用例

把dept表中的数据插入到aparment表中,并把deptno值分别加2；

declare

deptno_table deptno_table_type;

dname_table dname_table_type;

loc_table loc_table_type;

begin

select* bulk coolection into deptno_table ,dname_table,loc_table from dept;-------------BULK COLLECT INTO可以批量查询，提高查询效率，into后面跟的必须是集合

for i in 1..deptno_table.count loop

deptno_table(i):=deptno_table(i)+2;

end loop;

pro_dept3(dept_table,dname_table,loc_table);

end;

--带有输出参数的存储过程
--1 标量用例
create or replace pro_dept5 (deptno_type dept.deptno%type,dname_type out dept.dname%type,loc_type out dept.loc%type) is --------默认为int表示输入，当为out时则表示输出
begin
select dname,loc into dname_type,loc_type from dept where deptno=deptno_type;
exception
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('该部门不存在');
end pro_dept5;

-----测试用例
declare
deptno dept.deptno%type:=&deptno;
dname dept.dname%type;
loc dept.loc%type;
begin
pro_dept5(deptno,dname,loc);
dbms_output.put_line('部门号：'||p_deptno);
dbms_output.put_line('部门名称：'||dname);
dbms_output.put_line('部门地址：'||loc);
end;

--2.记录类型
CREATE OR REPLACE PROCEDURE get_dept2(p_deptno dept.deptno%TYPE, dept_record OUT dept%ROWTYPE) IS

BEGIN
SELECT * INTO dept_record FROM dept WHERE deptno=p_deptno;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('该部门不存在');
END get_dept2;
--调用测试
DECLARE
p_deptno dept.deptno%TYPE;
dept_record dept%ROWTYPE;
BEGIN
p_deptno:=&deptno;
get_dept2(p_deptno,dept_record);
dbms_output.put_line('部门号：'||dept_record.deptno);
dbms_output.put_line('部门名称：'||dept_record.dname);
dbms_output.put_line('部门地址：'||dept_record.loc);

END;

--3.集合类型
CREATE TYPE deptno_table_type IS TABLE OF NUMBER(2);
CREATE TYPE dname_table_type IS TABLE OF VARCHAR2(14);
CREATE TYPE loc_table_type IS TABLE OF VARCHAR2(13);

CREATE OR REPLACE PROCEDURE get_dept3(p_loc dept.loc%TYPE,deptno_table OUT deptno_table_type,
dname_table OUT dname_table_type) IS
BEGIN
SELECT deptno,dname BULK COLLECT INTO deptno_table,dname_table FROM dept WHERE lower(loc)=p_loc;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('该部门不存在');
END get_dept3;

--调用测试
DECLARE
p_loc dept.loc%TYPE;
deptno_table deptno_table_type;
dname_table dname_table_type;
BEGIN
p_loc:=LOWER('&loc');
get_dept3(p_loc,deptno_table,dname_table);
--使用循环对数据进行处理
FOR i IN 1..deptno_table.count LOOP
dbms_output.put_line('部门号：'||deptno_table(i));
dbms_output.put_line('部门名称：'||dname_table(i));
dbms_output.put_line('部门地址：'||p_loc);
dbms_output.put_line('=====================');
END LOOP;

END;

-----带有输入输出参数的存储过程

create or replace pro_num(num1 in out number,num2 in out number) is
bedin
num1:=num1+num2;
num2:=num1-num2;

end pro_num;
--调用测试
DECLARE
num1 NUMBER:=&num1;
num2 NUMBER:=&num2;
BEGIN
add_sub(num1,num2);
dbms_output.put_line('和：'||num1);
dbms_output.put_line('差：'||num2);
END;