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");
设置自增函数
CREATE SEQUENCE AutoID8 -- 函数名AutoIDView Code
START WITH 2 -- 起始值 2
INCREMENT BY 2 -- 步长 2
MINVALUE 2 -- 最小值 2
NOMAXVALUE;
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;
/