Oracle數(shù)據(jù)庫(kù)創(chuàng)建存儲(chǔ)過(guò)程的示例詳解

create [or replace] procedure 存儲(chǔ)過(guò)程名（param1 in type，param2 out type）
as
 變量1 類(lèi)型（值范圍）;
 變量2 類(lèi)型（值范圍）;
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';--:=則是對(duì)屬性進(jìn)行賦值
 age := 18;
 dbms_output.put_line ( 'name=' || name || ', age=' || age );--這條是輸出語(yǔ)句
END;
--存儲(chǔ)過(guò)程調(diào)用(下面只是調(diào)用存儲(chǔ)過(guò)程語(yǔ)法)
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;

--存儲(chǔ)過(guò)程調(diào)用
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;
--調(diào)用存儲(chǔ)過(guò)程
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是賦值的關(guān)鍵字
 select to_char(sysdate,'yyyy-mm-dd')into v_date FROM EMP_TEST WHERE ENAME ='郭芙蓉';
 DBMS_OUTPUT.put_line('總?cè)藬?shù):'||v_total);
 DBMS_OUTPUT.put_line('當(dāng)前日期'||v_date);
END;

--調(diào)用存儲(chǔ)過(guò)程
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;

--調(diào)用存儲(chǔ)過(guò)程
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:='未找到數(shù)據(jù)';
END;
--調(diào)用存儲(chǔ)過(guò)程
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;
--調(diào)用存儲(chǔ)過(guò)程
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是隱式游標(biāo) 作用:判斷SQL語(yǔ)句是否成功執(zhí)行,當(dāng)有作用行時(shí)則成功執(zhí)行為true，否則為false。 6 THEN
  DBMS_OUTPUT.PUT_LINE('成功修改雇員工資！');
  commit;
  else
  DBMS_OUTPUT.PUT_LINE('修改雇員工資失?。?);
  END IF;
 END;
 --調(diào)用存儲(chǔ)過(guò)程
 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'; --聲明游標(biāo)
 cname EMPS.NAME%type; --%type 作用: 聲明的變量ename與EMPS表的NAME列類(lèi)型一樣
 csal EMPS.SAL%type;
BEGIN
 open emp_sor; --打開(kāi)游標(biāo)
 loop
-- 取游標(biāo)值給變量
 FETCH emp_sor into cname,csal;
 dbms_output.put_line('name:'||cname);
 exit when emp_sor%notfound;
 end loop;
 close emp_sor; --關(guān)閉游標(biāo)
end;
end;
--調(diào)用存儲(chǔ)過(guò)程
BEGIN
 job_day07();
END;