PL/SQL存储过程

时间:2022-04-18 07:14:00

一、概述

过程和函数统称为PL/SQL子程序,他们是被命名的PL/SQL块,均存储于数据库中。

并通过输入、输出和输入输出参数与其调用者交换信息。唯一区别是函数总向调用者返回数据。

二、存储过程详解

创建过程语法:

CREATE [ OR REPLACE ] PROCEDURE [ schema. ] procedure_name
[ ( parameter_declaration [, parameter_declaration ]... ) ]
[ invoker_rights_clause ]
{ IS | AS }
{ [ declare_section ] body | call_spec | EXTERNAL} ;

说明:

  • procedure_name:过程名称。
  • parameter_declaration:参数声明,格式如下:
  • parameter_name [ [ IN ] datatype [ { := | DEFAULT } expression ]
    | { OUT | IN OUT } [ NOCOPY ] datatype
  • IN:输入参数。
  • OUT:输出参数。
  • IN OUT:输入输出参数。
  • invoker_rights_clause:这个过程使用谁的权限运行,格式:
  • AUTHID { CURRENT_USER | DEFINER }
  • declare_section:声明部分。
  • body:过程块主体,执行部分

创建存储过程

带有输入、输出参数的过程

CREATE OR REPLACE PROCEDURE proc_demo
(
dept_no NUMBER DEFAULT 10,
sal_sum OUT NUMBER,
emp_count OUT NUMBER
)
IS
BEGIN
SELECT SUM(salary), COUNT(*) INTO sal_sum, emp_count
FROM employees WHERE department_id = dept_no;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END proc_demo;


调用存储过程

调用方式:
1)、当在SQL*PLUS中调用过程时,需要使用CALL或者EXECUTE命令,而在PL/SQL块中过程可以直接引用。

-- 调用删除员工的过程
EXEC remove_emp(1); -- 调用插入员工的过程
EXECUTE insert_emp(1, 'tommy', 'lin', 2);

2)、在PL/SQL语句块中直接调用。

DECLARE
V_num NUMBER;
V_sum NUMBER(8, 2);
BEGIN
Proc_demo(30, v_sum, v_num);
DBMS_OUTPUT.PUT_LINE('温馨提示:30号部门工资总和:'||v_sum||',人数:'||v_num);
Proc_demo(sal_sum => v_sum, emp_count => v_num);
DBMS_OUTPUT.PUT_LINE('温馨提示:10号部门工资总和:'||v_sum||',人数:'||v_num);
END;

C# 调用:

OracleCommand cmd = new OracleCommand("prroc_demo", myOracleConnection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("v_companycode", OracleType.Char);
cmd.Parameters["v_companycode"].Value = "aa";
cmd.Parameters.Add("v_returnvalue", OracleType.Float).Direction = ParameterDirection.Output;
cmd.ExecuteNoQuery();
string eval = cmd.Parameters["v_returnvalue"].Value.ToString();

三、存储过程返回记录集SYS_REFCURSOR

cursor与REF cursor大致有以下几点区别:

  1. 静态游标不能返回到客户端,只有PL/SQL才能利用它。ref游标则可以,是从Oracle的存储过站返回结果集的方式。
  2. PL/SQL静态游标可以是全局的,而ref游标只能在定义它的过程中使用,但ref游标可以从子例程传递到子例程,而普通游标则不能。
  3. 静态光标比ref游标效率要高。
  4. sys_refcursor在oracle9i以后系统定义的一个refcursor,主要用于在过程中返回结果集。

1、返回单行语法

create or replace procedure proc_query_rent (
param_region varchar2, --定义区
param_room number, --定义室
param_hall number, --定义厅
param_rentMin number, --定义租金上限
param_rentMax number, --定义租金下限
param_resultSet OUT SYS_REFCURSOR --定义out参数返回结果集
)
as
begin
open param_resultSet for select * from tb_rent
where region like case when param_region IS null then '%' else param_region end
AND room like case when param_room IS null then '%' else to_char(param_room) end
AND hall like case when param_hall IS null then '%' else to_char(param_hall) end
AND rent between case when param_rentMin IS null then 0 else param_rentMin end
AND case when param_rentMax IS null then 99999999 else param_rentMax end;
end;

调用:

declare
v_rent_rows SYS_REFCURSOR;
v_rent_row tb_rent % rowType;
begin
proc_query_rent('山区', null, null, 1200, null, v_rent_rows);
Dbms_output.put_line('所在区 室 厅 租金');
loop
      fetch v_rent_rows into v_rent_row;//单行
exit when v_rent_rows % NOTFOUND;
Dbms_output.put_line(v_rent_row.region || ' ' || v_rent_row.room || ' ' || v_rent_row.hall || ' ' || v_rent_row.rent);
end loop;
close v_rent_rows;
end;

2、返回多行语法

存储过程:

create or replace procedure getEmpByDept(in_deptNo in emp.deptno%type,  out_curEmp out SYS_REFCURSOR) as
begin
open out_curEmp for
SELECT * FROM emp WHERE deptno = in_deptNo ;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20101,
'Error in getEmpByDept' || SQLCODE );
end getEmpByDept;

调用(执行存储过程):

declare
cur_emp sys_refcursor;
type emp emp_type is table of yemp%rowtype;
vemps emp_type;
begin
sp_getEmp(line=>'A5',curemp=>cur_emp);
fetch cur_emp bulk collect into vemps;
for i in v_emps.first..v_emps.last loop
dbms_output.putline(v_emps(i).empid);
end loop;
close cur_emp;
end;

C# 调用:

OracleCommand cmd = new OracleCommand("prroc_demo", myOracleConnection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("v_companycode", OracleType.Char).Value = "aa";
cmd.Parameters.Add("curEmp", OracleType.Cursor).Direction = ParameterDirection.Output;
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
this.dataGridView1.DataSource = ds.Tables[0];

四、维护存储过程

1、删除过程:

可以使用DROP PROCEDURE命令对不需要的过程进行删除

DROP PROCEDURE logexecution;

2、显示过程代码

select text from user_source where name='存储过程名(大写)' and type='PROCEDURE';

3、查看过程状态

select  object_type ,object_name ,status from user_objects where  object_name  = 'procedure';

4、重新编译过程

alter procedure pro_backup compile;

五. 过程与函数比较

相同点:

  1. 都使用IN模式的参数传入数据、OUT模式的参数返回数据。
  2. 输入参数都可以接受默认值,都可以传值或传引导。
  3. 调用时的实际参数都可以使用位置表示法、名称表示法或组合方法。
  4. 都有声明部分、执行部分和异常处理部分。
  5. 其管理过程都有创建、编译、授权、删除、显示依赖关系等。

不同点:

  1. 过程:作为PL/SQL语句执行;函数:作为表达式的一部分执行
  2. 过程:在规范中不包含RETURN子句;函数:必须在规范中包含RETURN子句
  3. 过程:不返回任何值;函数:必须返回单个值
  4. 过程:可以RETURN语句,但是与函数不同,它不能用于返回值;函数:必须包含至少一条RETURN语句

六、 与过程相关数据字典

USER_SOURCE, ALL_SOURCE, DBA_SOURCE, USER_ERRORS,

ALL_PROCEDURES,USER_OBJECTS,ALL_OBJECTS,DBA_OBJECTS

相关的权限:

CREATE ANY PROCEDURE

DROP ANY PROCEDURE

在SQL*PLUS 中,可以用DESCRIBE 命令查看过程的名字及其参数表。

DESC[RIBE] Procedure_name;

PL/SQL存储过程的更多相关文章

  1. PL/SQL存储过程编程

    PL/SQL存储过程编程 /**author huangchaobiao *Email:huangchaobiao111@163.com */ PL/SQL存储过程编程(上) 1. Oracle应用编 ...

  2. PL/SQL — 存储过程

    存储过程子程序的一种类型,能够完成一些任务,作为schema对象存储于数据库.是一个有名字的PL/SQL代码块,支持接收或不接受参数,同时也支持参数输出.一个存储过程通常包含定于部分,执行部分,Exc ...

  3. PL/SQL 存储过程

    PL/SQL复习九 存储过程 无参数的存储过程: create or replace procedure out_time is begin dbms_output.put_line(to_char( ...

  4. pl/sql 存储过程执行execute immediate 卡住

    在存储过程中,执行了create table.update table.insert into table 但是在使用pl/sql的存储过程调试的时候,一有问题就直接卡住(标识:执行中.....) 后 ...

  5. 关于oracle PL/SQL存储过程 PLS-00905 object is invalid,statement ignored问题的解决

    昨天在学习oracle存储过程的时候,写了一个存储过程的demo,语句是这样的: )) AS psssal TESTDELETE.TESTID%TYPE; BEGIN SELECT TESTID IN ...

  6. Oracle笔记 十、PL/SQL存储过程

    --create or replace 创建或替换,如果存在就替换,不存在就创建 create or replace procedure p is cursor c is select * from ...

  7. oracle pl/sql 存储过程

    存储过程用于执行特定的操作,当建立存储过程时,既可以指定输入参数(in),也可以指定输出参数(out),通过在过程中使用输入参数,可以将数据传递到执行部分:通过使用输出参数,可以将执行部分的数据传递到 ...

  8. pl sql 存储过程 执行sql 锁死状态

    背景 这是在一个不知如何表达的项目中,我在这个项目中做的就是不知如何表达的事情.只是想着技术,到是通过这个项目把存储过程基本能用的都用了,oracle开发的技术我感觉基本都全活了.别人没搞定的我搞定了 ...

  9. Oracle数据库--PL/SQL存储过程和函数的建立和调用

    1.存储过程建立的格式: create or replace procedure My_Procedure is begin --执行部分(函数内容); end; / 例子:(以hr表为例) crea ...

随机推荐

  1. Windows 10 Build 14997中Edge浏览器已默认阻止Flash运行

    在上周末偷跑的 Windows 10 Build 14997 向我们传递了很多信息,新增了蓝光过滤器等功能,并有望装备在即将到来的 Creators Update 中.经过深入发掘,外媒发现新版系统中 ...

  2. Docker基础技术:Linux Namespace(上)

    时下最热的技术莫过于Docker了,很多人都觉得Docker是个新技术,其实不然,Docker除了其编程语言用go比较新外,其实它还真不是个新东西,也就是个新瓶装旧酒的东西,所谓的The New “O ...

  3. linux:如何修改用户的密码

    1.首先,要用CRT软件连接Linux系统. 2.首选,确认是用root用户登录系统的. 输入命令: id ,查看登录用户信息. 3.若修改root自己的密码,直接输入 passwd . 输入两遍,新 ...

  4. UVA11549 计算机谜题(Floyd判圈算法)

    #include<iostream> #include<cstdio> #include<cstring> #include<cmath> #inclu ...

  5. HTML中的target标记

    HTML:target=_blank -- 在新窗口中打开链接 _parent -- 在父窗体中打开链接 _self -- 在当前窗体打开链接,此为默认值 _top -- 在当前窗体打开链接,并替换当 ...

  6. tensorflow&lowbar;目标识别object&lowbar;detection&lowbar;api&comma;RuntimeError&colon; main thread is not in main loop&comma;fig &equals; plt&period;figure&lpar;frameon&equals;False&rpar;&lowbar;tkinter&period;TclError&colon; no display name and no &dollar;DISPLAY environment variable

    最近在使用目标识别api,但是报错了: File "/usr/local/lib/python2.7/dist-packages/tensorflow/python/ops/script_o ...

  7. &lbrack;视频&rsqb;K8飞刀 HackIE&bsol;EXP测试&bsol;Post提交

    [视频]K8飞刀 HackIE VS Firefox Hackbar插件功能对比 视频中可看到HackBar有缺陷导致Payload无法执行 链接:https://pan.baidu.com/s/15 ...

  8. 【Redis】5、Redis事务处理

    MULTI .EXEC .DISCARD 和WATCH 是 Redis 事务的基础 1.MULTI  命令用于开启一个事务,它总是返回 OK .MULTI 执行之后,客户端可以继续向服务器发送任意多条 ...

  9. unity的inputField文本框赋值问题

    GameObject t = GameObject.Find("InputFieldT"); Text tt = t.transform.Find("Text" ...

  10. JavaScript -- History

    -----042-History.html----- <!DOCTYPE html> <html> <head> <meta http-equiv=&quot ...