4.0 Stored Procedures In this section you will be creating and executing stored
ID: 3596781 • Letter: 4
Question
4.0 Stored Procedures In this section you will be creating and executing stored procedures. You will be creating various types of stored procedures that take input and output parameters.
4.1 Basic Stored Procedure Task – Create a stored procedure that selects the first and last names of all the employees.
4.2 Stored Procedure Input Parameters Task – Create a stored procedure that updates the personal information of an employee. Task – Create a stored procedure that returns the managers of an employee.
4.3 Stored Procedure Output Parameters Task – Create a stored procedure that returns the name and company of a customer.
Please just use hypothetical tables and columns for these questions, thanks
Explanation / Answer
4.1
==============
create or replace procedure stpr_first_last_emp
(
vc_cursor out SYS_REFCURSOR
)
as
begin
open vc_cursor for
select firstname,lastname from temp_emp;
end;
==============================
4.3 answer
==============
create or replace procedure stpr_name_company_emp
(
vc_cursor out SYS_REFCURSOR
)
as
begin
open vc_cursor for
select name,comapany from temp_emp;
end;
=====================
4.2
-------------------
CREATE OR REPLACE PROCEDURE updateemp(
i_vemp_ID IN tool_description.tool_ID%TYPE,
vcflag IN OUT varchar
)
IS
i_vcflag varchar2(10);
BEGIN
update temp_emp set emp_id=i_vemp_ID where emp_id='123';
COMMIT;
i_vcflag:='success';
select i_vcflag into vcflag from dual;
exception
when NO_DATA_FOUND then
i_vcflag:='failure';
dbms_output.put_line('No data' ||i_vcflag );
rollback;
end;
-------------------------------------------------
create or replace procedure stpr_manager_emp
(
vc_cursor out SYS_REFCURSOR
)
as
begin
open vc_cursor for
select manager from temp_emp where emp_id='123' ;
end;
----------------------------
If you have quiries please comment
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.