本文由 发布，转载请注明出处，如有问题请联系我们！ 发布时间: 2021-08-01oracle存储过程入门详解-oracle存储过程语法

课程标准:

PLSQL程序编写：Hello World，程序结构，自变量，步骤操纵，游标卡尺.sql语句：定义，无参储存，有参储存（键入，輸出）.JAVA启用储存sql语句.

1.PLSQL程序编写。

1.1.定义和目地。

什么叫PL/SQL？

PL/SQL（Procedure Language/SQL）PLSQL是Oracle对sql语言的全过程化拓展 (类似Basic)指在SQL指令语言表达中提升了全过程解决句子（如支系，循环系统等），使SQL语言表达具备全过程解决工作能力。

1.2.程序结构。

根据Plsql开发者专用工具的检测对话框建立程序流程模版，或是根据句子将其载入sql对话框。

提醒:PLSQL语言表达的具体情况是没法区别的。

PL/SQL能够分成三个一部分:申明一部分，可实行一部分和错误处理一部分。

-- Created on 2018/3/21 by ADMINISTRATOR DECLARE -- 申明自变量，游标卡尺。 I INTEGER;BEGIN -- 实行句子 --[错误处理]END;

DECLARE一部分用以申明自变量或游标卡尺(結果集种类自变量)，假如程序流程中沒有自变量申明，能够省去。

1.3.你好世界

BEGIN --打印出hello world DBMS_OUTPUT.PUT_LINE('hello world');END;

在其中DBMS_OUTPUT是oracle内嵌包，等同于Java中的System.out，PUT_LINE()是被启用的方式，等同于println()方式。

您还可以在sqlplus中撰写和运作PLSQL程序流程:

SQL> BEGIN 2 3 --打印出hello world 4 5 DBMS_OUTPUT.PUT_LINE('hello world'); 6 7 END; 8 /PL/SQL 全过程已取得成功进行。

实行后，无法显示輸出結果。默认设置状况下，輸出选择项处在关掉情况。大家必须打开set serveroutput。

在SQLPLUS中实行PLSQL程序流程必须在程序流程的结尾加上一个/来标识程序流程的完毕。

1.4.自变量

PLSQL程序编写中常用的自变量分成两大类:

一般基本数据类型（char,varchar2, date, number, boolean, long）独特变量类型（引入型自变量，纪录型自变量）

申明自变量的办法是。

用户标识符 变量类型（自变量长短） 比如： v_name varchar2(20);

1.4.1.一般自变量。

分派自变量有这两种方式:

立即赋值语句 := 例如: v_name := ‘zhangsan’句子取值，应用select …into … 取值：（英语的语法 select 值 into 自变量）

[实例]打印出工作人员的私人信息，包含名字，薪水和详细地址。

-- 打印出工作人员私人信息，包含： 名字，工资，详细地址DECLARE -- 名字 V_NAME VARCHAR2(20) := '张三'; -- 申明自变量立即取值 --工资 V_SAL NUMBER; --详细地址 V_ADDR VARCHAR2(200);BEGIN --在程序流程中立即取值 V_SAL := 1580; --句子取值 SELECT '上海传智播客' INTO V_ADDR FROM DUAL; --打印出自变量 DBMS_OUTPUT.PUT_LINE('名字：' || V_NAME || ',工资：' || V_SAL || ',详细地址：' ||V_ADDR);END;

1.4.2.引用类型自变量。

自变量的类别和长短在于表格中字段名的类别和长短。

根据表名和字段名%TYPE特定自变量的类别和长短，比如:v _ name emp.ename % TYPE

【实例】查看emp表格中7839号人的私人信息，打印出名字和薪水。

-- 查看emp表格中7839号人的私人信息，打印出名字和工资DECLARE -- 名字 V_NAME EMP.ENAME%TYPE; -- 申明自变量立即取值 --工资 V_SAL EMP.SAL%TYPE;BEGIN --查看表格中的名字和工资并取值给自变量 --留意查看的字段名和取值的自变量的次序，数量，种类要一致 SELECT ENAME, SAL INTO V_NAME, V_SAL FROM EMP WHERE EMPNO = 7839; --打印出自变量 DBMS_OUTPUT.PUT_LINE('名字：' || V_NAME || ',工资：' || V_SAL);END;

参照自变量的益处:

应用通用性变量定义方式，必须了解表中列的种类，而应用引用类型，则不用考虑到列的种类。应用%TYPE是一种非常不错的编写程序设计风格，因为它使PL/SQL更为灵便，更合适升级数据库查询界定。

1.4.3.纪录自变量。

接纳表格中的一整行纪录，等同于Java中的一个目标。

英语的语法:用户标识符表名%ROWTYPE，比如:v _ emp emp % rowtype

[实例]

并查看打印出7839号职工名字及薪水

-- 查看emp表格中7839号人的私人信息，打印出名字和工资DECLARE -- 纪录型自变量接纳一行 V_EMP EMP%ROWTYPE;BEGIN --纪录型自变量默认设置接纳表格中的一行数据信息，不可以特定字段名。 SELECT * INTO V_EMP FROM EMP WHERE EMPNO = 7839; --打印出自变量，根据用户标识符.特性的方法获得自变量中的值 DBMS_OUTPUT.PUT_LINE('名字：' || V_EMP.ENAME || ',工资：' || V_EMP.SAL);END;

假如有一个表有100个字段名，假如你的程序流程想要这100个字段名，假如用引入自变量一个一个的去申明，会尤其不便，而纪录自变量能够非常容易的处理这个问题。

不正确应用:

1.纪录的自变量只有储存一行详细的数据信息。

2.回到的行过多，没法接受纪录的自变量。

1.5.过程管理。

1.5.1.标准支系。

英语的语法:

BEGIN IF 标准1 THEN 实行1 ELSIF 标准2 THEN 实行 2 ELSE 实行3 END IF; END;

注:ELSIF。

[实例]分辨emp表格中是不是有20条之上的纪录，10到20条不同的纪录，或是10条下列的纪录。

DECLARE --申明自变量接纳emp表格中的纪录数 V_COUNT NUMBER;BEGIN --查看emp表格中的纪录数取值给自变量 SELECT COUNT(1) INTO V_COUNT FROM EMP; --分辨打印出 IF V_COUNT > 20 THEN DBMS_OUTPUT.PUT_LINE('EMP表格中的纪录数超出了20条为：' || V_COUNT || '条。'); ELSIF V_COUNT >= 10 THEN DBMS_OUTPUT.PUT_LINE('EMP表格中的纪录数在10~20条中间为：' || V_COUNT || '条。'); ELSE DBMS_OUTPUT.PUT_LINE('EMP表格中的纪录数在10条下列为：' || V_COUNT || '条。'); END IF;END;

1.5.2.循环系统。

ORACLE中有三种循环系统方式。大家这儿不进行，只详细介绍在其中一个:loop loop。

英语的语法:

BEGIN LOOP EXIT WHEN 撤出循环系统标准 END LOOP;END;

[实例]打印出数据1-10。

DECLARE --申明循环系统自变量并赋初始值 V_NUM NUMBER := 1;BEGIN LOOP EXIT WHEN V_NUM > 10; DBMS_OUTPUT.PUT_LINE(V_NUM); --循环系统自变量自增 V_NUM := V_NUM 1; END LOOP;END;

2.鼠标光标。

2.1.什么叫鼠标光标？

它用以临时性储存查看回到的几行数据信息(ResultSet，类似Java中Jdbc联接回到的ResultSet结合)。根据解析xml鼠标光标，您能够一行行浏览和事件处理集的数据信息。

游标卡尺的使用方法:申明->开启->载入->关掉。

2.2.英语的语法。

游标卡尺申明:

CURSOR游标卡尺名字[(主要参数目录)]是一个查看句子；

鼠标光标开启:

开启鼠标光标名字；

鼠标光标的值:

FETCH游标卡尺名字在自变量目录中；

鼠标光标关掉:

关掉鼠标光标名字；

2.3.鼠标光标的特性。

游标卡尺的特性传参形式的叙述。

在其中%NOTFOUND在游标卡尺中找不着原素时回到TRUE，一般用以分辨是不是撤出循环系统。

2.4.艺术创作和应用。

[实例]应用鼠标光标查看emp表格中全部职工的名字和薪水，并先后打印出出去。

--应用游标卡尺查看emp表格中全部职工的名字和薪水，并将其先后打印出出去。DECLARE --申明游标卡尺 CURSOR C_EMP IS SELECT ENAME, SAL FROM EMP; --申明自变量用于接纳游标卡尺中的原素 V_ENAME EMP.ENAME%TYPE; V_SAL EMP.SAL%TYPE;BEGIN --开启游标卡尺 OPEN C_EMP; --解析xml游标卡尺中的值 LOOP --根据FETCH句子获得游标卡尺中的值并取值给自变量 FETCH C_EMP INTO V_ENAME, V_SAL; --根据%NOTFOUND分辨是不是有值,有值打印出,沒有则撤出循环系统 EXIT WHEN C_EMP%NOTFOUND; DBMS_OUTPUT.PUT_LINE('名字:' || V_ENAME || ',工资:' || V_SAL); END LOOP; --关掉游标卡尺 CLOSE C_EMP;END;

实行結果:

2.5.带主要参数的鼠标光标。

【实例】用鼠标光标查看打印出某单位职工的名字和薪水，运作时手动式键入单位号。

--应用游标卡尺查看并打印某单位的职工的名字和薪酬，单位序号为打开时手动式键入。DECLARE --申明游标卡尺传送主要参数 CURSOR C_EMP(V_EMPNO EMP.EMPNO%TYPE) IS SELECT ENAME, SAL FROM EMP WHERE EMPNO = V_EMPNO; --申明自变量用于接纳游标卡尺中的原素 V_ENAME EMP.ENAME%TYPE; V_SAL EMP.SAL%TYPE;BEGIN --开启游标卡尺并传送主要参数 OPEN C_EMP(10); --解析xml游标卡尺中的值 LOOP --根据%NOTFOUND分辨是不是有值,有值打印出,沒有则撤出循环系统 EXIT WHEN C_EMP%NOTFOUND; --根据FETCH句子获得游标卡尺中的值并取值给自变量 FETCH C_EMP INTO V_ENAME, V_SAL; DBMS_OUTPUT.PUT_LINE('名字:' || V_ENAME || ',工资:' || V_SAL); END LOOP; --关掉游标卡尺 CLOSE C_EMP;END;

留意:%NOTFOUND特性的初始值是FLASE，因此要留意分辨标准在循环过程中的部位。如果我们先分辨最终一条数据的值会在FETCH中打印出2次(默认设置是反复循环系统一次)；

3.sql语句。

3.1.定义人物角色。

大家以前写的PLSQL程序流程能够对表的逻辑性开展计算，分辨和循环系统，可是不可以反复启用。

能够了解，以前任何的编码全是用main方式撰写的，这是一个密名程序流程。JAVA能够根据封裝目标和方式来处理器重难题。

PLSQL储存和器重PLSQL的工作流程，这种储存的PLSQL程序流程称之为sql语句。

sql语句的功效:

1.在开发设计程序流程中，针对特殊的业务流程作用，数据库查询会数次联接关掉(联接关掉十分消耗資源)，因而必须对数据开展数次读写能力I/O，特性相对性较低。假如将这种服务项目放进PLSQL中，大家只须要在应用软件中启用PLSQL，就可以一次联接关掉数据库查询，能够大幅提高高效率。

2.甲骨文字官方网提议:不必把数据库操作放到程序流程里。大部分数据库查询的完成不容易有不正确，程序流程的运作也有可能有不正确。(假如实际操作数据库查询中的数据信息，能够有日志修复等特殊作用。).

3.2.英语的语法

CREATE OR REPLACE PROCEDURE 全过程名字[(主要参数目录)] ISBEGINEND [全过程名字];

依据主要参数的种类，大家将其分成三类:

l无主要参数。

l带键入主要参数。

l含有键入和輸出主要参数(传参)。

3.3.无主要参数储存。

3.3.1.建立储存。

根据Plsql Developer或句子建立sql语句:

[实例]根据启用sql语句打印出hello world。

建立sql语句:

--根据启用sql语句打印出hello worldCREATE OR REPLACE PROCEDURE P_HELLO ISBEGIN DBMS_OUTPUT.PUT_LINE('hello world');END P_HELLO;

根据专用工具查验建立的sql语句:

3.3.2.启用sql语句。

1.根据PLSQL程序流程启用:

BEGIN --立即键入启用sql语句的名字 P_HELLO;END P_HELLO;

2.根据SQLPLUS中的运行命令启用:

提醒:在SQLPLUS中表明結果的前提条件是规定将serveroutput设定为on。

留意:

第一个难题:is和as是可互操作性的，应用哪一个并不重要。第二个难题:步骤中沒有declare关键词，句子块中采用了declare。

3.4.含有键入主要参数的sql语句。

【实例】查看并打印某职工的名字和薪水(如职工序号7839)-sql语句:总输项，启用时传到职工序号，开展控制面板全自动打印出。

--查看并打印某一职工（如7839号职工）的名字和工资--规定，启用的是传到职工序号，全自动控制面板打印出。CREATE OR REPLACE PROCEDURE P_QUERYNAMEANDSAL(I_EMPNO IN EMP.EMPNO%TYPE) IS --申明自变量接纳查看結果 V_ENAME EMP.ENAME%TYPE; V_SAL EMP.SAL%TYPE;BEGIN --依据客户传送的职工号查看名字和工资 SELECT ENAME, SAL INTO V_ENAME, V_SAL FROM EMP WHERE EMPNO = I_EMPNO; --打印出結果 DBMS_OUTPUT.PUT_LINE('名字:' || V_ENAME || ',工资:' || V_SAL);END P_QUERYNAMEANDSAL;

指令通话:

SQL> exec p_querynameandsal(7839);名字:KING,工资:5000PL/SQL 全过程已取得成功进行。

PLSQL程序流程启用:

BEGIN P_QUERYNAMEANDSAL(7839);END;

实行結果:

3.5.含有輸出主要参数的sql语句。

[实例]键入职工序号以查看职工的信息内容(职工序号7839)，并要求将薪水做为传参輸出给被启用的程序流程。

--键入职工号查看某一职工（7839号职工）信息内容，规定，将工资做为传参輸出，给启用的程序流程应用。CREATE OR REPLACE PROCEDURE P_QUERYSAL_OUT(I_EMPNO IN EMP.EMPNO%TYPE,O_SAL OUT EMP.SAL%TYPE) ISBEGIN SELECT SAL INTO O_SAL FROM EMP WHERE EMPNO = I_EMPNO;END P_QUERYSAL_OUT;

PLSQL程序流程启用:

DECLARE --申明一个自变量接纳sql语句的輸出主要参数 V_SAL EMP.SAL%TYPE;BEGIN P_QUERYSAL_OUT(7839, V_SAL); --留意主要参数的次序 DBMS_OUTPUT.PUT_LINE(V_SAL);END;

留意:启用时，主要参数应与界定主要参数的次序和种类一致。

3.7.JAVA程序流程启用sql语句。

规定:假如一个句子不可以完成結果集，例如多表查询或是繁杂逻辑性查看，我们可以挑选启用储存查看来获得你的結果。

3.7.1.剖析jdk API。

您能够根据Connection目标的prepareCall方式启用sql语句。

结果:Connection目标启用prepareCall方式传送转义的sql语句启用sql语句，键入主要参数立即启用set方式传送。在必须申请注册輸出主要参数以后，根据get方式实行并获得sql语句。主要参数目录的字符从1逐渐。

3.7.2.完成编码。

提前准备自然环境:

l 导进Oracle的jar包【实例】根据职工号查看职工的名字和薪酬package cn.itcast.oracle.jdbc;import oracle.jdbc.OracleTypes;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;public class ProcedureTest { public static void main(String[] args) throws Exception { //1.载入推动 Class.forName("oracle.jdbc.driver.OracleDriver"); //2.得到联接目标 //2.1 设定联接字符串数组 String url ="jdbc:oracle:thin:@localhost:1521:xe" ; String name = "scott"; String password = "tiger"; Connection conn = DriverManager.getConnection(url, name, password); //3.获得句子目标 String sql = "{call p_querysal_out(?,?)}";//转义英语的语法,{call sql语句(主要参数目录)} CallableStatement call = conn.prepareCall(sql); //4.设定键入主要参数 call.setInt(1,7839); //5.申请注册輸出主要参数 call.registerOutParameter(2, OracleTypes.DOUBLE); //6.实行sql语句 call.execute(); //7.获得輸出主要参数 double sal = call.getDouble(2); System.out.println("工资:" sal); //8.释放出来資源 call.close(); conn.close(); }}