SQL开发知识：Oracle数据库创建存储过程的使用方法

create [or replace] procedure 存储过程名（param1 in type，param2 out type）
as
变量1 类型（值范围）;
变量2 类型（值范围）;
begin
select count(*) into 变量1 from 表A where列名=param1；
if (判断条件) then
select 列名 into 变量2 from 表A where列名=param1；
dbms_output.Put_line(‘打印信息’);
elsif (判断条件) then
dbms_output.Put_line(‘打印信息’);
else
raise 异常名（NO_DATA_FOUND）;
end if;
exception
when others then
rollback;
end;

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;

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 EMP_TEST WHERE ENAME =’燕小六’; –into是赋值的关键字
select to_char(sysdate,’yyyy-mm-dd’)into v_date FROM EMP_TEST WHERE ENAME =’郭芙蓉’;
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 int,v_name out varchar2)
IS
BEGIN
SELECT ENAME into v_name FROM EMP_TEST WHERE EMPNO = 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(‘1002’,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 job_day05(do1 in varchar,dn1 in varchar,eo1 in number,en1 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,do1);
COMMIT;
EXCEPTION WHEN OTHERS THEN
App_Code:=-1;
error_Msg:=’插入失败’;
END;
–调用存储过程
DECLARE
  do1 varchar(10);
  dn1 varchar(10);
   eo1 number(20);
   App_Code varchar(20);
error_Msg varchar(20);
BEGIN
    do1:= ‘张三丰’;
dn1:=’新桥’;
eo1:=1001;
    JOB_DAY04(do1,dn1,App_Code,error_Msg);
    dbms_output.put_line(ermg);
END;

CREATE OR REPLACE PROCEDURE job_day06(epo in number)
AS
BEGIN
UPDATE EMPS SET SAL=(SAL+100) WHERE empno = epo;
IF SQL%FOUND –SQL%FOUND是隐式游标 作用:判断SQL语句是否成功执行,当有作用行时则成功执行为true，否则为false。 6 THEN
DBMS_OUTPUT.PUT_LINE(‘成功修改雇员工资！’);
commit;
else
DBMS_OUTPUT.PUT_LINE(‘修改雇员工资失败！’);
END IF;
END;
–调用存储过程
declare
e_number number;
begin
e_number:=1001;
job_day06(e_number);
end;

CREATE OR REPLACE PROCEDURE job_day07
IS
BEGIN
DECLARE
cursor emp_sor is select name,sal FROM EMPS WHERE EMPNO = ‘1001’; –声明游标
cname EMPS.NAME%type; –%type 作用: 声明的变量ename与EMPS表的NAME列类型一样
csal EMPS.SAL%type;
BEGIN
open emp_sor; –打开游标
loop
— 取游标值给变量
FETCH emp_sor into cname,csal;
dbms_output.put_line(‘name:’||cname);
exit when emp_sor%notfound;
end loop;
close emp_sor; –关闭游标
end;
end;
–调用存储过程
BEGIN
job_day07();
END;