Oracle_子程序_存储过程select user from dual;set serveroutput on--存储过程CREATE OR REPLACE PROCEDURE proce_Test --创建过程is--局部变量BEGINdbms_output.put_line('大家好' );END proce_Test;--执行存储过程建议按f5 执行存储过程execute proce_test;beginproce_test;end;---------------------------------------------------------------------------------------------------------------------------------------- 此处说明定义存储过程时不能给形参施加限制(大小),存储过程创建出错，也会保留在服务器上------------------------------------------------------错误问题说明Create procedure Sp_Test1(a varchar2,b out varchar2) -- 此处不能加size,执行存储过程会出错.但该存储过程依然保存在数据库中,此名字已经被占用,当修改正确后,再执行时会抱该对象已经存在的异常,所以建议使用or replaceisBeginb :=a;End Sp_Test1;drop procedure sp_Test1--create or replace procedure--test(a varchar2,b out varchar2) is--begin--end;------执行存储过程declarevalue varchar2(10);Beginsp_Test1('i202',value);dbms_output.put_line('value的值为' || to_char(value));end;---------------------------------------存储过程示例程序/************************/drop table empCREATE TABLE EMP(EMPNO NUMBER(4) NOT NULL,ENAME VARCHAR2(10),JOB VARCHAR2(9),MGR NUMBER(4),HIREDATE DATE,SAL NUMBER(7, 2),COMM NUMBER(7, 2),DEPTNO NUMBER(2));--drop table empINSERT INTO EMP VALUES(7369, 'SMITH', 'CLERK', 7902,TO_DATE('17-12-1980', 'DD-MM-YYYY'), 800, NULL, 20);INSERT INTO EMPselect 7828, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-02-1981', 'DD-MM-YYYY'), 1600, 300, 30 from dualunionselect 7521, 'WARD', 'SALESMAN', 7698, TO_DATE('22-02-1981', 'DD-MM-YYYY'), 1250, 500, 10 from dualunionselect 7698, 'JONES', 'MANAGER', 7839, TO_DATE('2-04-1981', 'DD-MM-YYYY'), 2975, NULL, 20 from dualunionselect 7639, 'MARTIN', 'SALESMAN', 7698, TO_DATE('28-09-1981', 'DD-MM-YYYY'), 1250, 1400, 30 from dualselect 7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('1-05-1981', 'DD-MM-YYYY'), 2850, NULL, 30 from dualunionselect 7688, 'MARTIN', 'SALESMAN', 7698, TO_DATE('28-09-1981', 'DD-MM-YYYY'), 1250, 1400, 30 from dualunionselect 7789, 'BLAKE', 'MANAGER', 7839, TO_DATE('1-05-1981', 'DD-MM-YYYY'), 2850, 500, 40 from dualselect * from emp/*************************************示例1：定义一个存储过程通过传递的参数值empno，来判断当前员工是否有值，如果有就返回该员工的信息，如果没找到则提示异常信息，没有找到***************************************/create or replace procedurefind_emp(emp_no number)asempname varchar2(20);beginselect ename into empname from emp where empno = emp_no;dbms_output.put_line('雇员姓名: '||empname);exceptionwhen no_data_found thendbms_output.put_line('雇员编号未找到');end find_emp;------执行过程exec find_emp(7789); --execute或beginfind_emp(7789);end;-------------------CREATE TABLE DEPT(DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );--drop table deptINSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');commit;---------------------------------------------------------------------------------/*************************************示例2:过程的创建以及执行,以以前的练习为例编写程序，完成以下功能，在emp表中增加记录，记录数和部门编号作为传入参数,如果部门不存在则返回提示信息***************************************/create or replace procedure add_emp(vnum number,vdeptno emp.deptno%type)isvmax emp.empno%type;vdnum number;beginselect count(*) into vdnum from dept where deptno=vdeptno;if vdnum=0 thendbms_output.put_line(vdeptno||'号部门不存在!');elseselect max(empno) into vmax from emp;for i in 1..vnum loopinsert into emp(empno,deptno) values(vmax+i,vdeptno);end loop;dbms_output.put_line('增加成功!');end if;end;drop procedure add_empselect * from empselect * from dept------------------------------------------------------------调用过程exec add_emp(3,30)------------------/********************************************************************将上一章游标练习改为存储过程给每个雇员加薪5%的，同时检查如果某个雇佣工作超过过60个月，则给他额外加薪800.*******************************************************************/select * from emp;create or replace procedureadd_sal(vrate number)isv_empinfo emp%rowtype;cursor cemp is select * from emp;beginopen cemp;loopfetch cemp into v_empinfo;exit when cemp%notfound;update emp set sal=sal*vrate where empno=v_empinfo.empno;if v_empinfo.hiredate < add_months(sysdate,-60) thenupdate emp set sal=sal+800 where empno=v_empinfo.empno;end if;end loop;close cemp;end;desc empexecute add_sal(4);select * from emp-------------------------------------------------------------/********************************************************************将上一章游标练习改为存储过程给每个雇员加薪5%的，同时检查如果某个雇佣工作超过过60个月，则给他额外加薪800. *******************************************************************/select * from emp;create or replace procedureadd_sal(vrate number)isv_empinfo emp%rowtype;cursor cemp is select * from emp;beginopen cemp;loopfetch cemp into v_empinfo;exit when cemp%notfound;update emp set sal=sal*vrate where empno=v_empinfo.empno;if v_empinfo.hiredate < add_months(sysdate,-60) thenupdate emp set sal=sal+800 where empno=v_empinfo.empno;end if;end loop;close cemp;end;desc empexecute add_sal(4);-- sal->800：3200 + 800-- sal->1250 5000 + 800select * from emp---------------------------------------------------------------------class over----ohter--------过程参数模式--------------------------------inCREATE OR REPLACE PROCEDURE raise_salary (emp_id in INTEGER, increase in REAL) is current_salary REAL;salary_missing EXCEPTION;BEGINSELECT sal INTO current_salary FROM empWHERE empno = emp_id;IF current_salary IS NULL THENRAISE salary_missing;ELSEUPDATE emp SET sal = sal + increaseWHERE empno = emp_id;END IF;EXCEPTIONWHEN NO_DATA_FOUND THENINSERT INTO emp_audit VALUES (emp_id, 'No such number'); WHEN salary_missing THENINSERT INTO emp_audit VALUES (emp_id, 'Salary is null');END raise_salary;-------------------------------------------------------------------------------outcreate or replace procedureSp_Test(a varchar2,b out varchar2) isBeginb :=a;End;declarevalue varchar2(10);Beginsp_Test('i202',value);dbms_output.put_line('value的值为' || to_char(value));end;-------SQL * PLUS里也可以用这种方法执行存储过程.set serveroutput onvar c varchar(10);Exec sp_Test('01', :c);Print c;/SET SERVEROUT ONEXECUTE find_emp(7900);------CREATE OR REPLACE PROCEDUREitemdesc(item_code IN VARCHAR2)ISv_itemdesc VARCHAR2(5);BEGINSELECT itemdesc INTO v_itemdescFROM itemfileWHERE itemcode = item_code;DBMS_OUTPUT.PUT_LINE(item_code||'项目的说明为'||v_itemdesc); END;/EXECUTE itemdesc ('i201');------CREATE OR REPLACE PROCEDUREtest(value1 IN VARCHAR2,value2 OUT NUMBER)ISidentity NUMBER;BEGINSELECT ITEMRATE INTO identityFROM itemFileWHERE itemcode = value1;IF identity < 200 THENvalue2:=100;ELSEvalue2:=50;END IF;END;/DECLAREvalue2 NUMBER;BEGINtest ('i202' ,value2);DBMS_OUTPUT.PUT_LINE('value2 的值为'||TO_CHAR(value2)); END;/--------------------------in outCREATE OR REPLACE PROCEDUREswap(p1 IN OUT NUMBER, p2 IN OUT NUMBER) ISv_temp NUMBER;BEGINv_temp := p1;p1 := p2;p2 := v_temp;END;/DECLAREnum1 NUMBER := 100;num2 NUMBER := 200;BEGINswap(num1, num2);DBMS_OUTPUT.PUT_LINE('num1 = ' ||num1);DBMS_OUTPUT.PUT_LINE('num2 = ' || num2); END;/GRANT EXECUTE ON find_emp TO MARTIN;GRANT EXECUTE ON swap TO PUBLIC;DROP PROCEDURE test;。