SQL编程(六)

--创建过程，带有IN 和 OUT 的参数 执行时必须定义输入参数和接受参数
CREATE OR REPLACE PROCEDURE computes
(num1 IN OUT NUMBER,num2 IN OUT NUMBER)
IS
  v1 NUMBER;
  v2 NUMBER;
BEGIN
  v1:=num1/num2;
  v2:=MOD(num1,num2);
  num1:=v1;
  num2:=v2;
END
--创建函数
CREATE OR REPLACE FUNCTION results
(num1 NUMBER,num2 IN OUT NUMBER)
RETURN NUMBER;
AS
  v1 NUMBER;
  v2 NUMBER;
BEGIN
  v1:=num1/num2;
  v2:=MOD(num1,num2);
  num1:=v1;
  num2:=v2;
  RETURN v1;
 EXCEPTION
  WHEN ZERO_DIVIDE THEN
  raise_application_error(-20000,'不能除0');
 END;
--触发器
CREATE OR REPLACE TRIGGER tr_sec
BEFORE INSERT OR UPDATE OR DELETE ON emp
BEGIN
 IF to_char(sysdate,'DY','nls_date_language=AMERTCAN')
 IN('SAT','SUN') THEN
 CASE
   WHEN INSERTING THEN
    raise_application_error(-20001,'不能在休息日增加雇员');
   WHEN UPDATING THEN
   raise_application_error(-20001,'不能在休息日更新雇员');
   WHEN DELETING THEN
   raise_application_error(-20001,'不能在休息日删除雇员');
   END CASE;
   END IF;
  END;
  --行触发器 DML触发器不能包含对表的查询操作
  CREATE OR REPLACE TRIGGER tr_emp
  BEFORE UPDATE OF sal ON emp
  FOR EACH ROW
  WHEN(old.job='engineer')--限制触发
  BEGIN
   IF :new.sal<:old.sal THEN
      raise_application_error(-20001,'工资只升不降');
      END IF;
   END;
 --动态sQL 语句
 DECLARE
 salary  NUMBER(6,2);
 sql_stat VARCHAR2(10);
 BEGIN
  sql_stat:='UPDATE emp SET sal =sal*(1+:percent/100)'
  ||'WHERE deptno=:dno';
  EXECUTE IMMEDIATE sql_stat USING &1,&2
  RETURNING INTO salary;
  dbms_output.put_line(salary);
 END;
 --多行查询
 DECLARE
 TYPE empcurtype IS REF CURSOR;
 emp_cv empcurtype;
 emp_record emp%ROWTYPE;
 sal_stat VARCHAR2(100);
 BEGIN
 sql_stat:='SELECT * FROM emp WHERE deptno=:dno';
 OPEN emp_cv FOR sql_stat USING &dno;
 LOOP;
 FETCH emp_cv INTO emp_record;
 EXIT WHEN emp_cv%NOTFOUND;
 dbms_output.put_line(emp_rcord.sal);
 END LOOP;
 CLOSE emp_cv;
END;