-- SQL Stored procedure

create or replace procedure FIND (
		EMPID in integer,
		EMPNAME out varchar2 
	) as 
	begin 
		select ENAME into EMPNAME from EMP where EMPNO = EMPID;
	end FIND;
/
show errors;

create or replace procedure FINDDEF (
		EMPID in integer default 7902,
		EMPNAME out varchar2
	) as 
	begin 
		select ENAME into EMPNAME from EMP where EMPNO = EMPID;
	end FINDDEF;
/
show errors;

create or replace function NEG1 
	return integer as 
	begin 
		return(-1);
	end NEG1;
/
show errors;

create or replace function FINDPHOTO (
		PHOTONAME in varchar2
	) return BLOB
	as
	PHOTOBLOB BLOB;
	begin
		select PHOTO into PHOTOBLOB from BLOBTEST
			where NAME = PHOTONAME;
		return PHOTOBLOB;
	end FINDPHOTO;
/
show errors;

create or replace package EMP_ACTIONS as
	type ref_cursor is ref cursor;
	type string_tab is table of varchar2(20) index by binary_integer;

	function FIND (
		EMPID integer
	) return varchar2;

	procedure FINDP (
		EMPID in integer,
		EMPNAME out varchar2
	); 

	procedure FINDY (
		EMPID in integer,
		EMPNAME out varchar2,
		EJOB in out varchar2
	); 

	procedure FINDR (
		EMPID in integer,
		MGRID in out integer
	);

	procedure FINDMGR (
		MGRID in integer,
		CUR in out ref_cursor
	); 

	procedure TESTI (
		NAME in varchar2,
		ID in integer
	);

	procedure FINDEMPBYNAME (
		NAME in varchar2,
		ID out number
	);

	function FINDEMPBYNAMEI (
		NAME in varchar2
	) return integer;

	procedure TAKETAB (
		IDS in string_tab
	);

	procedure RETURNTAB (
		POEM out string_tab
	);

	procedure NUM(
		VALUE in out number
	);

	procedure EMPCUR(
		CSR out REF_CURSOR
	);
		


end EMP_ACTIONS;
/

show errors;
create or replace package body EMP_ACTIONS as

	function FIND (
		EMPID integer
	) return varchar2 is EMPNAME varchar2(64);
	begin
		select ENAME into EMPNAME from EMP where EMPNO = EMPID;
		return EMPNAME;
	end FIND;

	procedure FINDP (
		EMPID in integer,
		EMPNAME out varchar2
	) is
	begin
		select ENAME into EMPNAME from EMP where EMPNO = EMPID;
	end FINDP;

	procedure FINDY (
		EMPID in integer,
		EMPNAME out varchar2,
		EJOB in out varchar2
	) is
	begin
	
		select ENAME, JOB  into EMPNAME, EJOB
		from EMP where EMPNO = EMPID;
	end FINDY;

	procedure FINDR (
		EMPID in integer,
		MGRID in out integer
	) is 
	begin
		SELECT MGR  into MGRID from EMP where EMPNO = EMPID;
	end FINDR;

	procedure FINDMGR (
		MGRID in integer,
		CUR in out ref_cursor
	) is 
	begin

		open CUR for select * from EMP where MGR = MGRID;

	end FINDMGR;

	procedure TESTI (
		NAME in varchar2,
		ID in integer
	) is
	begin
		insert into TEST (name, id) values (name, id);
	end TESTI;

	procedure FINDEMPBYNAME (
		NAME in varchar2,
		ID out number
	) is 
	begin
		select EMPNO into ID from EMP where ENAME = NAME;
	end FINDEMPBYNAME;

	function FINDEMPBYNAMEI (
		NAME in varchar2
	) return integer is
	ID integer;
	begin
		select EMPNO into ID from EMP where ENAME = NAME;
		return ID;
	end FINDEMPBYNAMEI;

	procedure NUM (
		VALUE in out number
	) is
	begin
		NULL;
	end NUM;

	procedure TAKETAB (
		IDS in string_tab
	) is

	id varchar2(20);

	begin	
		id := IDS.FIRST;
		loop
			exit when id is NULL;
			id := IDS.NEXT(id);
		end LOOP;

	end TAKETAB;

	procedure RETURNTAB (
		POEM out string_tab
	) is

	begin
		POEM(1) := 'Mary';
		POEM(2) := 'had';
		POEM(3) := 'a';
		POEM(4) := 'little';
		POEM(5) := 'lamb';
	end RETURNTAB;

	procedure EMPCUR (
		CSR out ref_cursor
	) is 
	begin
		open csr for select * from emp;
	end EMPCUR;


end EMP_ACTIONS;
/

show errors;

