oracle数据库函数以及触发器

--oracle自定义函数
CREATE OR REPLACE FUNCTION fun_sayhello
RETURN STRING
IS 
say STRING(64):='你好，我是某某某';
BEGIN
  RETURN say;
END;

--使用plsql中调用函数
DECLARE
say STRING(98):=fun_sayhello;
BEGIN
  dbms_output.put_line(say);
END;  
--带in参数的函数
CREATE OR REPLACE FUNCTION fun_test(NUM IN NUMBER)
RETURN NUMBER
IS 
BEGIN
  RETURN NUM*2;
END fun_test;

DECLARE
    n NUMBER:=12;
BEGIN
  dbms_output.put_line(fun_test(n));

END;

CREATE OR REPLACE FUNCTION fun_hello(NAME IN STRING)
RETURN STRING  IS
BEGIN 
  RETURN '你好'||NAME;
END;

BEGIN
  dbms_output.put_line(fun_hello('王二小'));

END;

--带out参数的函数
CREATE OR REPLACE FUNCTION fun_outres (NUM IN INT,res OUT INT)
RETURN INT IS
      -- mess STRING(40):='已经计算完成';
BEGIN 
  res:=NUM*2;
  RETURN res;
END;
  

DECLARE 
res INT:=0;
BEGIN
  dbms_output.put_line(fun_outres(20,res));
  dbms_output.put_line(res);
END;

--使用in out参数的函数
CREATE OR REPLACE FUNCTION fun_in_out(NUM IN OUT NUMBER)
RETURN NUMBER AS
BEGIN
  NUM:=SQRT(NUM);
  RETURN NUM*2;
END;

DECLARE 
NUM NUMBER:=9;
BEGIN
  dbms_output.put_line(fun_in_out(NUM));
END;

--定义一个in out的函数来判断年龄
CREATE OR REPLACE FUNCTION fun_age(age IN NUMBER,mess OUT STRING)
RETURN STRING AS
jieguo STRING(64):='成年人' ;
BEGIN
  IF(age > 18)THEN
  mess:=jieguo;
  RETURN mess;
  END IF;
  IF(age <18) THEN
  jieguo:='未成年';
  mess:=jieguo;
  RETURN mess;
  END IF;
END;
DECLARE
mess STRING(64);
BEGIN
   dbms_output.put_line(fun_age(22,mess));
END;


--oracle存储过程
CREATE OR REPLACE procedure pro_mj(up IN INT,down IN INT,height IN INT,ress OUT INT) IS
BEGIN
  ress:=(up+down)*height/2;
END;
DROP TABLE teacher;
DECLARE
ress INT:=0;
BEGIN
   dbms_output.put_line(ress);
    pro_mj(2,4,5,ress);
    dbms_output.put_line(ress);
    END;
SELECT * FROM student;


--创建数据更新触发器
CREATE OR REPLACE TRIGGER tri_stu
BEFORE UPDATE
ON student
REFERENCING OLD AS O NEW AS N
FOR EACH ROW --行级触发器(如果不加for each 就是一个语句级触发器，那样子的话就只对sql动作执行一次，而行级触发器作用在每个行上)
BEGIN
  INSERT INTO stu VALUES(:O.name,:O.id);
END;
SELECT * FROM student;
UPDATE student SET ID=ID+1;
SELECT * FROM stu;