-- Function: public.emp_comp(numeric, numeric)

-- DROP FUNCTION public.emp_comp(numeric, numeric);

CREATE OR REPLACE FUNCTION public.emp_comp(p_sal numeric, p_comm numeric)
RETURNS numeric AS
$BODY$
BEGIN
RETURN (p_sal + NVL(p_comm, 0)) * 24;
END$BODY$
LANGUAGE edbspl VOLATILE SECURITY DEFINER
COST 100;
ALTER FUNCTION public.emp_comp(numeric, numeric)
OWNER TO enterprisedb;
 

-- Function: public.hire_clerk(character varying, numeric)

-- DROP FUNCTION public.hire_clerk(character varying, numeric);

CREATE OR REPLACE FUNCTION public.hire_clerk(p_ename character varying, p_deptno numeric)
RETURNS numeric AS
$BODY$
v_empno NUMBER(4);
v_ename VARCHAR2(10);
v_job VARCHAR2(9);
v_mgr NUMBER(4);
v_hiredate DATE;
v_sal NUMBER(7,2);
v_comm NUMBER(7,2);
v_deptno NUMBER(2);
BEGIN
v_empno := new_empno;
INSERT INTO emp VALUES (v_empno, p_ename, 'CLERK', 7782,
TRUNC(SYSDATE), 950.00, NULL, p_deptno);
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno INTO
v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno
FROM emp WHERE empno = v_empno;
DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno);
DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_empno);
DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename);
DBMS_OUTPUT.PUT_LINE('Job : ' || v_job);
DBMS_OUTPUT.PUT_LINE('Manager : ' || v_mgr);
DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_hiredate);
DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal);
DBMS_OUTPUT.PUT_LINE('Commission : ' || v_comm);
RETURN v_empno;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:');
DBMS_OUTPUT.PUT_LINE(SQLCODE);
RETURN -1;
END$BODY$
LANGUAGE edbspl VOLATILE SECURITY DEFINER
COST 100;
ALTER FUNCTION public.hire_clerk(character varying, numeric)
OWNER TO enterprisedb;
 

-- Function: public.hire_salesman(character varying, numeric, numeric)

-- DROP FUNCTION public.hire_salesman(character varying, numeric, numeric);

CREATE OR REPLACE FUNCTION public.hire_salesman(p_ename character varying, p_sal numeric, p_comm numeric)
RETURNS numeric AS
$BODY$
DECLARE
v_empno NUMERIC(4);
v_ename VARCHAR(10);
v_job VARCHAR(9);
v_mgr NUMERIC(4);
v_hiredate DATE;
v_sal NUMERIC(7,2);
v_comm NUMERIC(7,2);
v_deptno NUMERIC(2);
BEGIN
v_empno := new_empno();
INSERT INTO emp VALUES (v_empno, p_ename, 'SALESMAN', 7698,
CURRENT_DATE, p_sal, p_comm, 30);
SELECT INTO
v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno
empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp WHERE empno = v_empno;
RAISE INFO 'Department : %', v_deptno;
RAISE INFO 'Employee No: %', v_empno;
RAISE INFO 'Name : %', v_ename;
RAISE INFO 'Job : %', v_job;
RAISE INFO 'Manager : %', v_mgr;
RAISE INFO 'Hire Date : %', v_hiredate;
RAISE INFO 'Salary : %', v_sal;
RAISE INFO 'Commission : %', v_comm;
RETURN v_empno;
EXCEPTION
WHEN OTHERS THEN
RAISE INFO 'The following is SQLERRM:';
RAISE INFO '%', SQLERRM;
RAISE INFO 'The following is SQLSTATE:';
RAISE INFO '%', SQLSTATE;
RETURN -1;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION public.hire_salesman(character varying, numeric, numeric)
OWNER TO enterprisedb;
 

-- Function: public.new_empno()

-- DROP FUNCTION public.new_empno();

CREATE OR REPLACE FUNCTION public.new_empno()
RETURNS numeric AS
$BODY$
v_cnt INTEGER := 1;
v_new_empno NUMBER;
BEGIN
WHILE v_cnt > 0 LOOP
SELECT next_empno.nextval INTO v_new_empno FROM dual;
SELECT COUNT(*) INTO v_cnt FROM emp WHERE empno = v_new_empno;
END LOOP;
RETURN v_new_empno;
END$BODY$
LANGUAGE edbspl VOLATILE SECURITY DEFINER
COST 100;
ALTER FUNCTION public.new_empno()
OWNER TO enterprisedb;