--创建过程,带有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;
SQL编程(六)
最新推荐文章于 2024-04-27 08:00:00 发布