Informatica Data Quality
- Informatica Data Quality 10.4.0
- All Products
create or replace function sp_ListEmp return types.cursortype as l_cursor types.cursorType; begin open l_cursor for select ename, empno from emp order by ename; return l_cursor; end;
Create PROCEDURE InOut( @inout varchar(100) OUT ) AS BEGIN set @inout = concat(@inout,'__') select * from mytable; END
CREATE PROCEDURE TESTMULTIRS (IN i_cmacct CHARACTER(5)) RESULT SETS 2 LANGUAGE SQL BEGIN DECLARE csnum INTEGER; --Declare serial cursors to consume less resources --You do not need a rollable cursor. DECLARE getDeptNo CHAR(50); --Be careful with the estimated length. DECLARE getDeptName CHAR(200); DECLARE c1 CURSOR WITH RETURN FOR s1; SET getDeptNo = 'SELECT DEPTNO FROM DEPT'; SET getDeptName = 'SELECT DEPTNAME FROM DEPT'; PREPARE s1 FROM getDeptNo; OPEN c1; END;
CREATE PROCEDURE FETCHEMPINFO ( @p_State VARCHAR(5), @e_id INT OUTPUT, @e_name VARCHAR(50) OUTPUT ) AS BEGIN SET NOCOUNT ON SELECT EMP_ID, NAME FROM EMP WHERE STATE = @p_State ORDER BY EMP_ID SET NOCOUNT OFF SELECT @e_id AS EMP_ID, @e_name AS NAME RETURN END GO --Configure the following variables to execute the procedure. DECLARE @p_State VARCHAR(5) DECLARE @EMPID int DECLARE @EMPNAME varchar(50) SET @p_State = 'CA' exec FETCHEMPINFO @p_State, @e_id = @EMPID, @e_name = @EMPNAME GO