oracle存储过程的使用方式及与语法

语法

DECLARE
– 声明变量游标

BEGIN
– 执行语句

– 异常处理

– 输出helloworld: syso(“hello world”)
DBMS_OUTPUT.PUT_LINE(‘hello world’);

END;
/
SET SERVEROUTPUT ON ;

– 普通变量

变量赋值两种方式：
1、直接赋值： = va_name=‘zhagnsan1’

2、语句赋值，select … INTO …赋值（语法select 值into 变量）

DECLARE
– 姓名
v_name VARCHAR(20):= ‘小明’ ;
– 薪水
v_sal NUMBER;
– 地址
v_addr VARCHAR(200);

BEGIN
– 直接赋值
v_sal:=15000;

– 语句赋值
SELECT ‘努力学习’ INTO v_addr FROM DUAL;
–打印输出
DBMS_output.put_line(‘姓名：’||v_name||’,薪水’||v_sal||’,地址:’||v_addr)

END;

引用型变量

变量类型类型

查询emp表中7839号员工的个人信息，打印姓名和薪水

DECLARE
– 姓名
v_name VARCHAR(20);
v_sal NUMBER();

BEGIN
SELECT ename,sal INTO v_name,v_sal FROM emp WHERE empno =7839
DBMS_OUTPUT.PUT_LINE(‘姓名：’||v_name||’,薪水：’||v_sal);

END;

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;

– 记录型变量

查询emp表中7839号员工的个人信息，打印姓名和薪水等所有信息

DECLARE
– 记录型变量
v_emp emp%ROWTYPE

BEGIN
SELECT * INTO V_EMP FROM emp WHERE empno =7839
DBMS_OUTPUT.PUT_LINE(‘姓名：’||v_emp.name||’,薪水：’||v_emp.sal);

END;

流程控制

判断emp表中记录是否超过20条，10-20，或者10条以下
BEGIN
v_count NUMBER;
BEGIN
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;

循环三种循环（此处用 LOOP）

打印数字1-10
DECLARE
– 声名循环变量
v_num NUMBER:=1;

BEGIN
LOOP
EXIT WHERE v_num>10
dbms_output_.put_line
v_num :=v_num + 1;
END LOOP;
END

游标：

什么是游标 ：用于临时存储一个查询返回的多行数据（结果集，类似于java 中jdbc连接的resultSet集合）
通过遍历游标，可以逐行访问处理该结果集数据

游标的使用方式：声名->打开->读取->关闭

语法：
游标声明：cursor 游标名（参数列表） IS 查询语句

游标的打开：
OPEN 游标名：
游标的取值：

FETCH 游标名 INTO 变量列表；

游标关闭：
CLOSE 游标名；

游标的属性

%ROWCOUNT 整形 获取FETCH 返回的数据行数
%FOUND 布尔型 最近的FETCH 语句返回一行数据则为真，否则为假
%NOTFOUND 和%FOUND 相反
%isopen 布尔型 游标已经打开则为真，否则为假
其中%NOTFOUND 是在游标中找不到元素时候返回TRUE ，通常用来判断退出循环

– 无参游标

– 使用游标查询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

– 遍历游标
LOOP

– 获取游标中的数据
FETCH c_emp INFO v_name,vsal;

– 退出游标循环条件
EXIT WHERE c_emp%NOTFOUND;
dbms_output.put_line(v_name||’–’||v_sal);
END LOOP;
– 关闭游标
CLOSE c_emp
END

– 有参游标

– 使用游标查询emp表某部门员工的姓名和工资，部门编号为运行时手动输入

DECLARE
– 声名游标
CURSOR c_emp（v_deptno emp.deptno%TYPE） IS SELECT ename,sal FROM emp WHERE deptno=v_deptno;
– 声名变量接受游标中的元素

v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE
BEGIN
– 打开游标
OPEN c_emp(10)

– 遍历游标
LOOP

– 获取游标中的数据
FETCH c_emp INFO v_name,vsal;

– 退出游标循环条件
EXIT WHERE c_emp%NOTFOUND;
dbms_output.put_line(v_name||’–’||v_sal);
END LOOP;
– 关闭游标
CLOSE c_emp
END

– 存储过程

语法：
CREATE OR REPLACE PROCEDURE 过程名称 （参数列表） IS
BEGIN

END[过程名称];

1、带参数的
2、不带参数的
3、带输出输出参数（返回值）的

– 第一个存储过程（无参存储过程） 创建一个p_hello的存储过程 可以省略 declare关键词

CREATE OR REPLACE PROCEDURE p_hello IS/AS

– 声名变量
BEGIN
dbms_out.put_line(‘hello world’);

– plsql调用存储过程
p_hell;

END p_hello

– 通过plsql 存储过程名字 调用存储过程

BEGIN
– plsql调用存储过程
p_hello;

END

– 后台调用
SET serveroutput ON
exec p_hello

– 查询并打印某个员工（如7839员工） 的姓名和薪水 --存储过程：要求，调用的时候传入员工编号。自动控制台打印

带参数的存储过程 1、输入参数（i_empno IN emp.empno%TYPE） IN 2、输出参数 OUT

CREATE OR REPLACE p_query (i_name IN emp.empno%TYPE) AS

– 声明变量
v_name emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
– 查询emp 表中某个员工的姓名和薪水并赋值给变量
SELECT ename,sal INTO v_name,v_sal FROM emp WHERE empno =i_empno;

dbms_output.put_line(vname||’——’||v_sal );
END p_query

– 调用
BEGIN
p_query(10)
END;

– 带输入输出存储过程

通过员工号查询某员工的信息，要求：将薪水作为返回值输出，给调用的程序使用

CREATE OR REPLACE p_querySal (i_name IN emp.empno%TYPE，o_sal OUT emp.sal%TYPE) AS

BEGIN
– 查询emp 表中某个员工的姓名和薪水并赋值给变量
SELECT sal INTO O_SAL FROM emp WHERE empno =i_empno;

END p_querySal

– 执行
DECLARE
– 声名变量接收存储过程中的输出参数
v_sal emp.sal %TYPE
– 调用
BEGIN
p_quety_out(1001,v_sal)
dbms_output.put_line(v_sal)
END