Oracle custom functions, no parameters, input parameters, output parameters, and other usage

[SQL] www.2cto.com -- 19-1: create or replace function cur_datetime RETURN VARCHAR2 is begin return TO_CHAR (sysdate, 'yyyy "year" MM "month" DD "day" HH24 "Hour" MI "Minute" SS "seconds" '); END;/-- 19-2: create or replace function get_sal (name VARCHAR2) return number as v_sal emp. sal % TYPE; begin select sal INTO v_sal FROM emp WHERE upper (ename) = upper (name); RETURN v_sal; END;/-- 19-3: create or replace function get_info (eno NUMBER, title OUT VARCHAR2) RETURN VARCHAR2 AS name emp. ename % TYPE; begin select ename, job INTO name, title FROM emp WHERE empno = eno; RETURN name; END;/-- 19-4: create or replace function get_upd_info (eno NUMBER, sal_chg in out number) RETURN VARCHAR2 AS name emp. ename % TYPE; begin update emp SET sal = sal + sal_chg WHERE empno = eno RETURNING ename, sal INTO name, sal_chg; RETURN name; END;/-- 19-5: create or replace function get_name (no VARCHAR2) return number RESULT_CACHE RELIES_ON (emp) AS v_name emp. ename % TYPE; begin select ename INTO v_name FROM emp WHERE empno = no; RETURN v_name; END;/-- 19-6: Call the function BEGIN dbms_output.put_line (cur_datetime); END; /-- 19-7: Call the BEGIN dbms_output.put_line ('salary: '| get_sal (' & name'); END;/-- 19-8: call the DECLARE v_name emp function with output parameters. ename % TYPE; v_job emp. job % TYPE; BEGIN v_name: = get_info (& eno, v_job); dbms_output.put_line ('name: '| v_name |', position: '| v_job); END; /-- 19-9: Call the DECLARE v_empno emp function with input and output parameters. empno % TYPE; v_name emp. ename % TYPE; v_salchg emp. sal % TYPE; BEGIN v_empno: = & eno; v_salchg: = & incret; v_name: = get_upd_info (v_empno, v_salchg); dbms_output.put_line ('name: '| v_name | ', new salary: '| v_salchg); END;/-- 19-10: use location transfer as a parameter to pass variables and data SELECT get_sal (' & name') salary FROM dual; -- 19-11: Pass the variable and data VAR salary number exec: salary: = get_sal (name => '& name') -- 19-12: pass variables and data VAR name VARCHAR2 (10) VAR sal_chg number exec: sal_chg: = 200 EXEC: name: = get_upd_info (& eno,: sal_chg) as parameters using combinations) PRINT name sal_chg -- 19-13: In an SQL statement, call the pl/SQL function SELECT get_sal (name => 'Scott ') salary FROM dual; -- 19-14: exception Handling create or replace function get_sal (name VARCHAR2) return number as v_sal emp. sal % TYPE; begin select sal INTO v_sal FROM emp WHERE upper (ename) = upper (name); RETURN v_sal; exception when NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20000, 'This employee does not exist'); END;/-- 19-15: Use the record type as the RETURN type create or replace function get_info (eno NUMBER) RETURN emp % rowtype is emp_record emp % ROWTYPE; begin select * INTO emp_record FROM emp WHERE empno = eno; RETURN emp_record; exception when NO_DATA_FOUND THEN partition (-20000, 'this employee does not exist'); END;/DECLARE emp_record emp % ROWTYPE; BEGIN emp_record: = get_info (& eno); dbms_output.put_line ('name: '| emp_record.ename |', Department No.: '| emp_record.deptno); END;/-- 19-16: use the set TYPE as the return type create or replace type ename_table_type is table of VARCHAR2 (10);/create or replace function get_name (dno NUMBER) RETURN ename_table_type IS ename_table ename_table_type; begin select ename bulk collect into ename_table FROM emp WHERE deptno = dno; RETURN ename_table; exception when analyze THEN values (-20099, 'this department does not exist'); END;/DECLARE ename_table ename_table_type; BEGIN ename_table: = get_name (& dno); FOR I IN 1 .. ename_table.COUNT LOOP dbms_output.put_line ('name: '| ename_table (I); END LOOP; END;/-- 19-17: Delete the FUNCTION DROP FUNCTION get_name; -- 19-18: show errors -- 19-19: Check the FUNCTION status SELECT object_name FROM user_objects WHERE status = 'invalid' AND object_type = 'function'; -- 19-20: COMPILE the function alter function get_info COMPILE; -- 19-21: view the FUNCTION code SELECT text FROM user_source WHERE name = 'get _ info ';