Oracle的FUNCTION

来源：互联网发布：场馆预订系统源码编辑：程序博客网时间：2024/05/13 05:59

打开oracle自带的输出方法dbms_output。

在执行set serveroutput on以后，使用dbms_output方法可以输出信息，

例如：dbms_output.put_line('This is');

---取得雇员工资的函数

CREATE OR REPLACE FUNCTION get_sal(no NUMBER)RETURN NUMBER ISsalary NUMBER(6,2);BEGINSELECT sal into salary FROM emp WHERE empno = no;RETURN salary;END;/

--语句块，批量执行语句，工资低于2000的加上10%。

DECLARE CURSOR emp_cursor IS SELECT ename,sal FROM emp FOR UPDATE;emp_record emp_cursor%ROWTYPE;BEGIN OPEN emp_cursor;LOOPFETCH emp_cursor INTO emp_record;EXIT WHEN emp_cursor%NOTFOUND;IF emp_record.sal < 2000 THENUPDATE emp SET sal = sal*1.1 WHERE CURRENT OF emp_cursor;END IF;END LOOP;END;

---处理运行错误语句块

DECLAREname VARCHAR2(10);BEGIN SELECT ename INTO  name FROM emp where empno = &no;dbms_output.put_line('雇员名：'||name);EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('该雇员不存在');END;

CREATE OR REPLACE PROCEDURE insert_dept(no NUMBER,name VARCHAR2)ISBEGIN INSERT INTO dept(deptno,dname) VALUES (no,name);END;/

---取得雇员工资的函数

CREATE OR REPLACE FUNCTION get_sal2(name VARCHAR2)RETURN NUMBER ISv_sal NUMBER(6,2);BEGINSELECT sal into v_sal FROM emp WHERE upper(ename) = upper(name);RETURN v_sal;END;/

----&no是SQL*Plus的替代变量

CREATE OR REPLACE FUNCTION annual_income(name varchar2)RETURN NUMBER ISsalary NUMBER(19,2);BEGINSELECT sal*12 + nvl(comm,0) into salary FROM emp WHERE lower(ename) = lower(name);RETURN salary;END;/