I am doing homework for my intro class and we are using PL/SQL to do stored proc

ID: 3821430 • Letter: I

Question

I am doing homework for my intro class and we are using PL/SQL to do stored procedures/functions. I am having trouble understanding the problems and need help. Please explain your solutions in detail with explanations so I learn. I will upvote helpful answer. Thanks and god bless!

Problem One. Please type and run the scripts shown below to do the following: create tesdate stored procedure, call the procedure and show the results.

create or replace procedure testdate (ttt number)

vMo varchar2(2);

vDa varchar2(2);

mo number;

dd number;

thedate date;

r number;

begin

select sysdate into thedate from dual;

thedate :=SYSDATE; -- same thing

vMo := TO_CHAR(thedate, 'MM');

vDa := TO_CHAR(thedate, 'DD');

DBMS_OUTPUT.PUT_LINE(vMo);

DBMS_OUTPUT.PUT_LINE(vDa);

mo:= To_NUMBer(vMo, '99');

dd:= To_number(vDa, '99');

DBMS_OUTPUT.PUT_LINE(To_char(mo));

DBMS_OUTPUT.PUT_LINE(To_char(dd));

r:=trunc(dbms_random.value(0,4));

DBMS_OUTPUT.PUT_LINE(To_char(r));

end;

-- to call the above procedure, you can use the following anonymous PL/SQL block.

-- hints: An anonymous PL/SQL block is a block of PL/SQL statements that doesn’t have a name.

--An anonymous block which does not persist in the database, can consist of up to three sections: an optional declaration section, a mandatory executable section, and an optional exception section

set serveroutput on

BEGIN

testdate(33);

End;

set serveroutput off

Problem Two. What is anonymous PL/SQL block? (30 points) Show a simple anonymous PL/SQL block example made by yourself. Make sure that you find hints from my sample script and the previous problem and make up your own anonlymous PL/SQL block example.

Problem 3. Stored Procedure.

Hint 1: sample scripts on the course website, especially proceduremore2.sql.txt, some more sample

Hint 2: Do your best to accomplish it, . It will take you a while to debug your first stored procedure, but you should be able to work it out given enough time and with certain amount of patience. If you finally cannot get the procedure complied successfully, please also document the scripts you have been able to come up with and report whatever error messages Oracle has returned to you.

(1) Suppose you have created a table using command “create table hw6tusers(userid varchar2(10) primary key, password varchar2(10), status number default 1, username varchar2(10));”.

userid(varchar2(10)

password(varchar2(10))

status (number)

name(varchar2(10))

user1

666666

john

user2

555555

Kelly

Please write a stored procedure in PL/SQL language. The name of the stored procedure is hw6pnewuser.

It will take three input parameters, the first one is luser of varchar2 data type, the second one is lpassword of varchar2 and the third one is lname of varchar 2 data type. The functionality of the stored procedure is to insert a new record to table hw6tusers using information passed to the three parameters.(10 points)

/*Sample code only, not solution of your homework, more complicated version is in sample script rental example. */

create or replace procedure rentevent

( lssn varchar2, lvideo_id varchar2 )

begin

insert into renttransaction(ssn, video_id) values(lssn, lvideo_id);

end rentevent;

/*End of sample code!*/

(2) show how to invoke the above store procedure. (10 points)

/*Sample code of how to call the sample stored procedure only, not solution of your homework, more sample code is in proceduremore2.txt*/

declare

testssn varchar2(10) := '213132';

testvideo varchar2(10) := '0002';

begin

rentevent(testssn, testvideo);

end;

/*End of sample code of calling a stored procedure!*/

Problem 4: Stored Function.

Hint 2: see proceduremore2.sql.txt for the fcheck stored function sample code.

(1) Suppose you have created a table using command “create table hw6tusers(userid varchar2(10) primary key, password varchar2(10), status number default 1, username varchar2(10));”.

Please write a stored function named as hw6flogincheck in PL/SQL language, which will take two varchar2 input parameters and return a number value. The functionality is to check whether the provided userid and password can be successfully matched with any record in the table. It will return 1 if both userid and password are valid; it will return -1 if the username is right but password is wrong; it will return -2 if username is wrong.(10 points)

/*Please erase the following sample code in your homework solution!*/

/*Start of the sample code!*/

create or replace function fchecks ( lssn varchar2, lvideo_id varchar2 )

return number

--to define local variables

memberexist number := 0;

videoexist number := 0;

stocknumber number := 0;

begin

-- To check whether the input member ID is a valid ID

select count(*) into memberexist from member where ltrim(rtrim(ssn)) = ltrim(rtrim(lssn));

if (memberexist = 0)

then

return (-1);

end if;

-- To check whether the input video_id is a valid ID

select count(*) into videoexist from video where ltrim(rtrim(id)) = ltrim(rtrim(lvideo_id));

if (videoexist = 0)

then

return (-2);

end if;

-- To check whether there is at least one copy left for video_id

select copies into stocknumber from video where ltrim(rtrim(id)) = ltrim(rtrim(lvideo_id));

if (stocknumber <= 0)

then

return (-3);

end if;

return(1);

end fchecks;

/*End of the sample code!*/

(2) How to invoke the above stored function and output proper messages depending on the different return values.(10 points)

Don't forget to issue "set serveroutput on " the following sqlplus command before you invoke the stored procedure.

/*Please erase the following sample code in your homework solution!*/

/*Start of the sample code to invoke the above sample stored function.*/

set serveroutput on

declare

testssn varchar2(10) := '141333333';

testvideo varchar2(10) := '0001';

resultflag number :=0;

begin

select fchecks(testssn, testvideo)into resultflag from dual;

if (resultflag=-1)

then

dbms_output.put_line('Member ID is invalid');

end if;

if (resultflag=-2)

then

dbms_output.put_line('Vedio ID is invalid');

end if;

if (resultflag=-3)

then

dbms_output.put_line('Stock number is already low');

end if;

end;

/*End of the sample code to invoke the above sample stored function.*/

userid(varchar2(10)

password(varchar2(10))

status (number)

name(varchar2(10))

user1

666666

john

user2

555555

Kelly

Explanation / Answer

Answer to Problem 1 is already given.

To call an anonymous block (from the same schema/User which created the block) you may use the begin end block as described or may use the exec or execute command.

Such as, any of the below approaches would be fine.

EXEC testdata(40);

BEGIN

testdate(33);

End;

Problem 2

A PLSQL block has the below structure.

But in case of anonymous block, it has only three basic sections that are the declaration, execution, and exception handling. The Header section is not required as it is a block without a name and also it’s is not stored as a database object. It is only used one time till the User session persists.

Only the execution section is mandatory and the others are optional. The execution section must contain at least a single executable statement. It may also contain NULL which is also an executable statement.

In the example provided in the question, create or replace procedure testdate (ttt number)

Is the header section.

So the simplest PLSQL anonymous block would be:

BEGIN

NULL;

END;

Another example of PLSQL anonymous block is shown below

DECLARE

Name varchar2(100);

Current_date Date;

BEGIN

Name := ‘Michael’; -- Input your name

Current_date := SYSDATE; -- Returns the current date time

DBMS_OUTPUT.PUT_LINE(‘Today’s date and time is :’|| Current_date||’ and my name is : ’|| Name);

END;

PROBLEM 3

Can you please let me know what is “proceduremore2.sql.txt” as I don’t see any such references provided. Anyway I will try to answer the remaining.

Please write a stored procedure in PL/SQL language. The name of the stored procedure is hw6pnewuser.

Answer:

CREATE OR REPLACE PROCEDURE hw6pnewuser (luser varchar2, lpassword varchar2, lname varchar2)

BEGIN

INSERT INTO hw6tusers (userid, password, name) values(luser , lpassword , lname );

--considering the status column of the table allows NULL values

END hw6pnewuser;

(2) Invoking the Stored procedure (10 Points Question)

EXEC hw6pnewuser (‘User3’,’password1’,’Martin’);

OR you may also use the below approach to invoke the procedure

DECLARE

Userid varchar2(10) := ‘User4’;

Password varchar2(10) := ‘Password2’;

Username varchar2(10) := ‘Lucky’;

BEGIN

hw6pnewuser (‘Userid, Password, Username);

END;

Problem 4:

I hope you have clear understanding of this now. Please try out the problem 4 for better understanding. In case of any issues you may refer to the below code snippet written for Problem 4.

CREATE OR REPLACE FUNCTION hw6flogincheck(userid VARCHAR2

,password VARCHAR2) RETURN NUMBER IS

match NUMBER;

userfound NUMBER := 0;

CURSOR getrows IS

SELECT *

FROM hw6tusers;

BEGIN

FOR i IN getrows LOOP

IF (i. userid = userid AND i. password = password) THEN

match := 1;

elseif(i. userid = userid AND i. password <> password) THEN

match = := -1;

elseif(i. userid = userid) THEN userfound = 1;

END IF;

IF userfound = 0 THEN

RETURN - 2;

ELSE

RETURN match;

END IF;

END LOOP;

END;

EXEC testdata(40);

Navigate

I am doing homework and need to verify my answers. Please give a brief descripti

I am doing in eclipse can you send me the code Thank You Finish reading Deitel,

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.

I am doing homework for my intro class and we are using PL/SQL to do stored proc

Question

Explanation / Answer

Related Questions

Navigate