IN SQL DEVELOPER With the table below write a PLSQL stored f

IN SQL DEVELOPER: With the table below, write a PL/SQL stored function that takes username as input and returns number of documents that user has permissions to view. If username is not in the table, your procedure should return “user not found”.

CREATE TABLE UserPermissions(

    Document VARCHAR(50) NOT NULL,

    UserName VARCHAR(50) NOT NULL,

CONSTRAINT pk_UserPermissions PRIMARY KEY (UserName, Document));

/

INSERT INTO UserPermissions VALUES(\'Policy\', \'System\');

INSERT INTO UserPermissions VALUES(\'Menu\', \'JDOW\');

INSERT INTO UserPermissions VALUES(\'W2\', \'USAM\');

INSERT INTO UserPermissions VALUES(\'Permissions\', \'System\');

INSERT INTO UserPermissions VALUES(\'W2\', \'JDOW\');

INSERT INTO UserPermissions VALUES(\'Form 1040\', \'USAM\');

INSERT INTO UserPermissions VALUES(\'Policy\', \'JDOW\');

INSERT INTO UserPermissions VALUES(\'W2\', \'System\');

Document

UserName

Policy

SYSTEM

Menu

JDOW

W2

USAM

Permissions

SYSTEM

W2

JDOW

Form 1040

USAM

Policy

JDOW

W2

SYSTEM

Solution

CREATE OR REPLACE FUNCTION user_permission(username_in VARCHAR2)
RETURN int   
AS
document_count int;
BEGIN
SELECT count(*) INTO document_count from UserPermissions WHERE UserName = username_in;

IF document_count > 0 THEN
dbms_output.put_line(username_in || \' can view \' || document_count || \' document/s\');
ELSE
dbms_output.put_line(username_in || \' not found\');
END IF;

RETURN document_count;

END;