oracle存储过程写法 并行_Oracle存储过程基本写法(组图)

Oracle存储过程基本写法(组图)

08-07栏目:技术

TAG:oracle 存储过程

oracle 存储过程

oracle 存储过程的基本语法

1.基本结构

https://www.jhua.org

CREATE OR REPLACE PROCEDURE 存储过程名字

copyright www.jhua.org

( www.jhua.org

参数1 IN NUMBER, www.jhua.org

参数2 IN NUMBER copyright jhua.org

) IS www.jhua.org

变量1 integer :=0; https://www.jhua.org

变量2 DATE; copyright jhua.org

BEGIN

copyright jhua.org

END 存储过程名字

https://www.jhua.org

2.SELECT INTO statement www.jhua.org

将select查询的结果存入到变量中,可以同时将多个列存储多个变量中,必须有一条

copyright jhua.org

记录,否则抛出异常(如果没有记录抛出NO_DATA_FOUND) copyright jhua.org

例子:

jhua.org

BEGIN

copyright www.jhua.org

SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;

www.jhua.org

EXCEPTION jhua.org

WHEN NO_DATA_FOUND THEN

copyright www.jhua.org

xxxx; copyright jhua.org

END;

https://www.jhua.org

...

www.jhua.org

3.IF 判断

copyright www.jhua.org

IF V_TEST=1 THEN https://www.jhua.org

BEGIN

https://www.jhua.org

do something

www.jhua.org

END; copyright jhua.org

END IF;

jhua.org

4.while 循环

www.jhua.org

WHILE V_TEST=1 LOOP

copyright www.jhua.org

BEGIN

https://www.jhua.org

XXXX

copyright jhua.org

END;

copyright www.jhua.org

END LOOP;

copyright jhua.org

5.变量赋值 copyright www.jhua.org

V_TEST := 123;

www.jhua.org

6.用for in 使用cursor

copyright jhua.org

...

https://www.jhua.org

IS www.jhua.org

CURSOR cur IS SELECT * FROM xxx;

jhua.org

BEGIN copyright jhua.org

FOR cur_result in cur LOOP

www.jhua.org

BEGIN copyright jhua.org

V_SUM :=cur_result.列名1+cur_result.列名2 https://www.jhua.org

END;

jhua.org

END LOOP;

copyright jhua.org

END; https://www.jhua.org

7.带参数的cursor

jhua.org

CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;

copyright www.jhua.org

OPEN C_USER(变量值);

www.jhua.org

LOOP https://www.jhua.org

FETCH C_USER INTO V_NAME;

jhua.org

EXIT FETCH C_USER%notfound;

www.jhua.org

do something copyright www.jhua.org

END LOOP; www.jhua.org

CLOSE C_USER;

copyright jhua.org

8.用pl/sql developer debug www.jhua.org

连接数据库后建立一个Test WINDOW copyright jhua.org

在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试 jhua.org

简单实例,通过DBMS_OUTPUT来看结果 copyright jhua.org

CREATE OR REPLACE PROCEDURE bb (lic_para IN VARCHAR2,out_para OUT VARCHAR2)

copyright jhua.org

AS

www.jhua.org

temp   VARCHAR2 (100); www.jhua.org

BEGIN

https://www.jhua.org

SELECT lic_no

https://www.jhua.org

INTO temp copyright www.jhua.org

FROM t_vehicle_info

copyright jhua.org

WHERE lic_no = lic_para;

https://www.jhua.org

out_para:=temp;

https://www.jhua.org

DBMS_OUTPUT.put_line (out_para);

www.jhua.org

END bb; copyright www.jhua.org

下面是调用: https://www.jhua.org

begin copyright jhua.org

-- Call the procedure

https://www.jhua.org

bb(lic_para => :lic_para,

copyright www.jhua.org

out_para => :out_para); copyright jhua.org

end;

jhua.org

实例: copyright jhua.org

CREATE OR REPLACE PROCEDURE 要创建的存储过程名称 (

copyright www.jhua.org

入参a IN VARCHAR2,

www.jhua.org

出参b out VARCHAR2

https://www.jhua.org

)

copyright jhua.org

IS 变量c VARCHAR2 (2000) ;  copyright www.jhua.org

变量d NUMBER (10) ; copyright www.jhua.org

异常变量e EXCEPTION ; jhua.org

BEGIN

www.jhua.org

IF 入参a IS NULL THEN

www.jhua.org

raise 异常e ;

www.jhua.org

END IF ;// 跳到异常处理

copyright www.jhua.org

FOR cc IN (select语句) LOOP // 执行sql

www.jhua.org

v_sql := 'select语句' ;

copyright jhua.org

EXECUTE IMMEDIATE v_sql INTO v ;// 把查询的结果放到v中;  www.jhua.org

// sql查询,并放到v2中  copyright jhua.org

SELECT

www.jhua.org

COUNT (*) INTO v2..; // 进行相关判断

https://www.jhua.org

IF v2 = 1 THEN

jhua.org

update、insert等操作 ;

copyright www.jhua.org

END IF ; https://www.jhua.org

END loop ;  copyright www.jhua.org

COMMIT ;

copyright www.jhua.org

MSG := 'success' ;  copyright www.jhua.org

// 异常处理

www.jhua.org

EXCEPTION jhua.org

WHEN NECE THEN

https://www.jhua.org

MSG := '入参a不能为空' ;

jhua.org

WHEN OTHERS THEN

jhua.org

MSG := sqlerrm ; jhua.org

rollback ; jhua.org

END 要创建的存储过程名称 ; copyright jhua.org

原文转载:https://www.cnblogs.com/chinafine/articles/1776085.html

copyright jhua.org https://www.jhua.org

copyright jhua.org copyright jhua.org

jhua.org

相关阅读

Matlab基本函数 length函数

jhua.org

分享一下我老师大神的人工智能教程!零基础,通俗易懂!http://blog.csdn.net/jiangjunshow也欢迎大家转载本篇文章。分享知识,造福人民,

www.jhua.org

Android startActivityForResult基本用法 copyright jhua.org

从A页面使用startActivityForResult()跳转到B页面,B页面点击返回时将新写入的值传回到A页面。

MainActivity:

public class MainAct copyright www.jhua.org

innerHTML的基本用法 copyright jhua.org

定义:innerHTML 属性设置或返回表格行的开始和结束标签之间的 HTML。

/*这串代码的var a的变量是我瞎写的。我忘记js咋样定义变量 copyright jhua.org

UML类图入门,看完基本懂了怎么画图了 copyright jhua.org

第一篇,原文链接http://blog.csdn.net/monkey_d_meng/article/details/6005764UML类图新手入门级介绍 举一个简单的例子,来看这样 copyright www.jhua.org

第一章 基本句型及补语

www.jhua.org

第一章 基本句型及补语

五种单句的基本句型

补语

需要补语的动词

宾语补语的句型

补语的词类

没有补语的be动词

有两个宾语的句型

copyright jhua.org

阅读量:100000+

上一篇:特殊符号-希腊符号

推荐量:8560

下一篇:Origin下载速度慢怎

已标记关键词 清除标记
表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
©️2020 CSDN 皮肤主题: 游动-白 设计师:白松林 返回首页