Oracle存储过程由例子到理论

时间:2021-10-25 21:51:15

1.基础环境

oracle HR环境添加新表

CREATE TABLE "HR"."cus_test" (
"id"
VARCHAR2 (255 BYTE) NOT NULL,
"mobile"
VARCHAR2 (255 BYTE) NULL,
"name"
VARCHAR2 (255 BYTE) NULL,
"times" DATE
NULL
);

ALTER TABLE "HR"."cus_test" ADD CHECK ("id" IS NOT NULL);

ALTER TABLE "HR"."cus_test" ADD PRIMARY KEY ("id");

设置自增函数

Oracle存储过程由例子到理论Oracle存储过程由例子到理论
CREATE SEQUENCE AutoID8 -- 函数名AutoID
START WITH 2 -- 起始值 2
INCREMENT BY 2 -- 步长 2
MINVALUE 2 -- 最小值 2
NOMAXVALUE;
View Code

2.存储过程不带参数

CREATE
OR REPLACE PROCEDURE vvinstat AS
BEGIN
INSERT INTO "HR"."cus_test" (
"id",
"mobile",
"name",
"times"
)
VALUES
(
AutoID.nextval,
'18575511371',
'kamil',
SYSDATE
) ;
COMMIT ;
END ;
/

调用存储过程

SET SERVEROUTPUT ON
DECLARE
BEGIN
VVINSTAT;
END ;

 3.带参数IN(scott环境)

CREATE
OR REPLACE PROCEDURE find_emp1 (emp_no NUMBER) AS emp_name VARCHAR2 (20) ;
BEGIN
SELECT
ename
INTO emp_name
FROM
EMP
WHERE
empno
= emp_no ; DBMS_OUTPUT.PUT_LINE (
'雇员姓名:' || emp_name
) ; EXCEPTION
WHEN no_data_found THEN
DBMS_OUTPUT.PUT_LINE (
'雇员编号没有找到') ;
END ;
/

执行

SET SERVEROUTPUT ON
DECLARE
BEGIN
FIND_EMP1(
7566);
END ;
-- PL/SQL 过程已成功完成。
--
雇员姓名:JONES
/*

雇员编号没有找到
*/

 4.参数out(scott)

CREATE
OR REPLACE PROCEDURE my_proc_out (
value1
IN NUMBER,
value2 out
NUMBER
)
IS salary NUMBER ;
BEGIN
SELECT
sal
INTO salary
FROM
emp
WHERE
empno
= value1 ;
IF salary < 8000 THEN
value2 :
= salary + 500 ; UPDATE emp
SET sal = value2
WHERE
empno
= value1 ;
ELSE
value2 :
= salary ;
END
IF ;
END ;
/

执行:

SET SERVEROUTPUT ON
DECLARE v1 NUMBER := 7566 ; v2 NUMBER ;
BEGIN
MY_PROC_OUT (v1, v2) ; DBMS_OUTPUT.PUT_LINE (
'v2 的值为' || TO_CHAR (v2)
) ;
END ;
/

 5.IN OUT(scott)

CREATE or REPLACE PROCEDURE test_IN_OUT 
(v1
in out number,
v2
in out number)
is
value1
number:=0;
BEGIN
value1 :
=v1;
v1 :
=v2;
v2 :
=value1;
dbms_output.put_line(
'v1:'||v1||' '||'v2:'||v2);
end;
/

调用()

set SERVEROUTPUT ON
DECLARE
value1
number :=11111;
value2
number :=22222;
BEGIN
test_IN_OUT(value1,value2);
end;
/