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;
