Oracle存储过程

DECLARE
v_i NUMBER :=1;
BEGIN
FOR v_i IN 1..3 LOOP
DBMS_OUTPUT.put_line(v_i);
END LOOP;
END;
/

DECLARE
v_i NUMBER :=1;
BEGIN
FOR v_i IN REVERSE 1..3 LOOP
DBMS_OUTPUT.put_line(v_i);
END LOOP;
END;
/

DECLARE
v_i NUMBER :=1;
BEGIN
FOR v_i IN 1..10 LOOP
IF MOD(v_i,2)=0 THEN
CONTINUE;
END IF;

DBMS_OUTPUT.put_line(v_i);
END LOOP ;
END;
/

DECLARE
v_i NUMBER :=1;
BEGIN
FOR v_i IN 1..10 LOOP
IF v_i=3 THEN
EXIT;
END IF;

DBMS_OUTPUT.put_line(v_i);
END LOOP ;
END;
/

DECLARE
v_i NUMBER;
BEGIN
v_i := 10/0;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('异常！');
END;
/

定义存储过程
CREATE OR REPLACE PROCEDURE mldn_pro
IS
BEGIN
DBMS_OUTPUT.put_line('www.mldnjava.com');
END;
/

执行存储过程
EXEC mldn_pro;

CREATE OR REPLACE procedure get_emp_pro (p_eno emp.empno%TYPE)
IS
p_ename emp.ename%TYPE;
p_sal emp.sal%TYPE;
p_count NUMBER;
BEGIN
SELECT COUNT(1) INTO p_count FROM emp WHERE empno=p_eno;
IF p_count=0 THEN
RETURN;
END IF;
SELECT ename,sal INTO p_ename,p_sal FROM emp WHERE empno=p_eno;
DBMS_OUTPUT.put_line(p_ename || ',' || p_sal);
END;
/

EXEC get_emp_pro(7369);

 

有out参数，in out同理
CREATE OR REPLACE PROCEDURE dept_insert_proc(
v_deptno dept.deptno%TYPE,
v_dname dept.dname%TYPE,
v_loc dept.loc%TYPE,
v_result OUT NUMBER)
IS
v_deptCount NUMBER;
BEGIN
SELECT COUNT(1)INTO v_deptCount FROM dept WHERE deptno=v_deptno ;
IF v_deptCount>0 THEN
v_result:=-1;
DBMS_OUTPUT.put_line('部门已存在！');
ELSE
INSERT INTO dept(deptno,dname,loc)VALUES(v_deptno,v_dname,v_loc);
v_result:=0;
DBMS_OUTPUT.put_line('新部门添加成功！');
COMMIT;
END IF;
END;
/

DECLARE
v_result NUMBER;
BEGIN
dept_insert_proc(50,'MLDN','北京',v_result);
END;
/