oracle10 存储过程,2020-10-20_Oracle存储过程的基本用法

一、不带参数的存储过程：

--oracle调用存储过程；

CREATE OR REPLACE PROCEDURE MYDEMO02

AS

name VARCHAR(10);

age NUMBER(10);

BEGIN

name := ‘xiaoming‘;--:=则是对属性进行赋值

age := 18;

dbms_output.put_line ( ‘name=‘ || name || ‘, age=‘ || age );--这条是输出语句

END;

--存储过程调用(下面只是调用存储过程语法)

BEGIN

MYDEMO02();

END;

call MYDEMO02();

二、带参数的存储过程

CREATE OR REPLACE procedure MYDEMO03(name in varchar,age in int)

AS

BEGIN

dbms_output.put_line(‘name=‘||name||‘, age=‘||age);

END;

--存储过程调用

BEGIN

MYDEMO03(‘姜煜‘,18);

END;

三、出现异常的输出存储过程:

CREATE OR REPLACE PROCEDURE MYDEMO04

AS

age INT;

BEGIN

age:=10/0;

dbms_output.put_line(age);

EXCEPTION when others then   --处理异常

dbms_output.put_line(‘error‘);

END;

--调用存储过程

BEGIN

MYDEMO04;

END;

四、获取当前时间和总人数:

CREATE OR REPLACE PROCEDURE TEST_COUNT01

IS

v_total int;

v_date varchar(20);

BEGIN

select count(*) into v_total from SYS_LOGIN_USERS WHERE LOGIN_NAME =‘姜南‘;  --into是赋值的关键字

select to_char(sysdate,‘yyyy-mm-dd‘)into v_date from SYS_LOGIN_USERS WHERE LOGIN_NAME =‘姜南‘;

DBMS_OUTPUT.put_line(‘总人数:‘||v_total);

DBMS_OUTPUT.put_line(‘当前日期‘||v_date);

END;

--调用存储过程

BEGIN

TEST_COUNT01();

END;

五、带输入参数和输出参数的存储过程：

CREATE OR REPLACE PROCEDURE TEST_COUNT04(v_id in varchar2,v_name out varchar2)

AS

BEGIN

SELECT LOGIN_NAME into v_name FROM SYS_LOGIN_USERS WHERE login_id = v_id ;

DBMS_OUTPUT.put_line(‘姓名:‘||v_name);

EXCEPTION

when no_data_found then dbms_output.put_line(‘no_data_found‘);

END;

--调用存储过程

DECLARE

v_name varchar(200);

BEGIN

TEST_COUNT04(‘jiangnan‘,v_name);

END;

六、查询存储过程及其它；

CREATE OR REPLACE PROCEDURE job_day04(de in varchar,name out varchar,App_Code out varchar,error_Msg out varchar)

AS

BEGIN

SELECT ENAME into name FROM EMP_TEST WHERE ENAME=de;

EXCEPTION WHEN others THEN

error_Msg:=‘未找到数据‘;

END;

--调用存储过程

DECLARE

de varchar(10);

ab varchar(10);

appcode varchar(20);

ermg varchar(20);

BEGIN

de:= ‘张三丰‘;

JOB_DAY04(de,ab,appcode,ermg);

dbms_output.put_line(ermg);

END;

七、向数据库中添加数据的存储过程

CREATE OR REPLACE PROCEDURE ZJMES.job_day05(do1 in varchar,dn1 in varchar,eo1 in number,en1 in varchar,dn2 in varchar,App_Code out varchar,error_Msg out varchar)

AS

BEGIN

INSERT INTO STUDENT(NAME,CLASS)VALUES(do1,dn1);

INSERT INTO COMPANY(EMPID,NAME,DEPARNAME)VALUES(eo1,en1,dn2);

COMMIT;

EXCEPTION WHEN OTHERS THEN

App_Code:=-1;

error_Msg:=‘插入失败‘;

END;

--调用存储过程

DECLARE

do1 varchar(10);

dn1 varchar(10);

eo1 number(20);

en1 varchar(20);

dn2 varchar(20);

App_Code varchar(20);

error_Msg varchar(20);

BEGIN

do1:= ‘张三丰‘;

dn1:= ‘新桥‘;

eo1:= 1001;

en1:= ‘老子‘;

dn2:= ‘道家办公室‘;

ZJMES.job_day05(do1,dn1,eo1,en1,dn2,App_Code,error_Msg);

dbms_output.put_line(error_Msg);

END;

原文：https://www.cnblogs.com/wbly2019/p/13847796.html