存储过程练习

--游标为输出参数
CREATE OR REPLACE PROCEDURE pro_emp_select(empsal OUT sys_refcursor)AS

BEGIN
 OPEN empsal FOR SELECT empno,sal FROM emp ;  
END pro_emp_select;
--调用
DECLARE
v_no emp.empno%TYPE;
v_sal emp.sal%TYPE;
emp_sal SYS_REFCURSOR;
BEGIN
  pro_emp_select(emp_sal);
  LOOP
  FETCH emp_sal INTO v_no,v_sal;
  EXIT WHEN emp_sal%NOTFOUND;
  dbms_output.put_line('编号'||v_no||'薪水'||v_sal);
  END LOOP;
  CLOSE emp_sal;
END;

--游标为输入参数
CREATE OR REPLACE PROCEDURE pro_emp_in(empsal IN sys_refcursor)AS
v_no emp.empno%TYPE;
v_sal emp.sal%TYPE;
BEGIN
  LOOP
    FETCH empsal INTO v_no,v_sal;
    EXIT WHEN empsal%NOTFOUND;
    dbms_output.put_line('编号'||v_no||'薪水'||v_sal);
  END LOOP;
END pro_emp_in;

DECLARE
emp_sal SYS_REFCURSOR;
BEGIN
  OPEN emp_sal FOR SELECT empno,sal FROM emp;
  pro_emp_in(emp_sal);
  CLOSE emp_sal;
END;

　　自主事务处理：

　　自主事务：是由零一个事务启动的独立事务处理。自主事务处理可以暂停主事务处理，也就是处理自己存储过程内部的事务，当自主事务处理完之后会恢复主事务处理。

　　PRAGMA AUTONOMOUS_TRANSACTION;  --定义为自主事务，不受其他事务提交，回滚的影响

　　例：

--存储过程和事务的用法（主事务）
CREATE OR REPLACE PROCEDURE  pro_emp1 AS 
b VARCHAR(50);
BEGIN
  UPDATE employees e SET e.name='白猫警长'
  WHERE e.id='0';
  pro_emp2();
  SELECT ee.name INTO b FROM employees ee
  WHERE ee.id='0';
  dbms_output.put_line(b);
END;

CREATE OR REPLACE PROCEDURE pro_emp2 AS
 a VARCHAR2(50);
 PRAGMA AUTONOMOUS_TRANSACTION;--定义为自主事务处理
BEGIN
  SELECT e.name INTO a FROM employees e
  WHERE e.id='0';
  dbms_output.put_line(a);
  ROLLBACK;
END;

--调用
BEGIN
  pro_emp1();
END;

 函数 

--无参数模式
CREATE OR REPLACE FUNCTION FUNC_DATETIME RETURN VARCHAR2 IS
BEGIN
  RETURN TO_CHAR(SYSDATE, 'yyyy"年"mm"月"dd"日"hh24"时"mi"分"ss"秒"');
END FUNC_DATETIME;
--调用
BEGIN
  dbms_output.put_line(func_datetime);
END;

--判断是否是星期六日（输入类型参数IN）
CREATE OR REPLACE FUNCTION FUNC_ISHOLIDAY(P_DATE DATE) RETURN INTEGER IS
  V_WEEKDAY INTEGER := -1; --存储今天是星期几
BEGIN
  SELECT TO_CHAR(P_DATE, 'd') INTO V_WEEKDAY FROM DUAL; --自动进行类型转换
  IF V_WEEKDAY < 2 OR V_WEEKDAY > 6 THEN
    RETURN 1;
  ELSE
    RETURN 0;
  END IF;
END FUNC_ISHOLIDAY;

--调用
DECLARE
vdate DATE:=DATE'2012-12-1';--声明一个日期型的变量
BEGIN
dbms_output.put_line(func_isholiday(vdate));
END;