Scribd Logo
Upload

Welcome to Scribd!

  • 5 views

    Assginement-21 (User Defined Function and Index by Table)

    Uploaded by

    j32546498

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Assginement-21 (User Defined Function and Index by Table)

    Uploaded by

    j32546498
    5 views4 pages

    Original Title

    Assginement-21(User defined function and index by table )

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    5 views4 pages

    Assginement-21 (User Defined Function and Index by Table)

    Uploaded by

    j32546498

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 4

    21.

    CREATE A USER-DEFINED RECORDS GENERATING


    CONSALIDATED MARKSHEET FROM STUDENT DETAIL (YOU CAN
    HAVE REQUISITE NUMBER OF TABLES ACCORDING TO YOUR
    USER DEFINED RECORDS AND REQUIREMENT APPLY INDEX BY
    TABLE OF RECORDS.

    CREATE TABLE STUDENTS_DETAIL(STU_ID INT PRIMARY


    KEY,STU_NAME VARCHAR(20),STU_DEP
    VARCHAR(20),TOTAL_MARK INT,STU_GRADE VARCHAR(12));

    INSERT INTO STUDENTS_DETAIL


    VALUES(1,'Abhilaash','cse',410,'A');
    INSERT INTO STUDENTS_DETAIL VALUES(2,'Ajmal','AIDS',452,'S');
    INSERT INTO STUDENTS_DETAIL
    VALUES(3,'HARSHA','CSE',347,'B');
    INSERT INTO STUDENTS_DETAIL
    VALUES(4,'AADHARSH','AI&DS',386,'B');
    INSERT INTO STUDENTS_DETAIL
    VALUES(7,'PRANESH','ECE',478,'S');
    INSERT INTO STUDENTS_DETAIL VALUES(6,'Sam','AIDS',298,'D');
    INSERT INTO STUDENTS_DETAIL VALUES(8,'ajith','EEE',369,'B');
    INSERT INTO STUDENTS_DETAIL VALUES(9,'joe','CSE',234,'FAIL');
    INSERT INTO STUDENTS_DETAIL VALUES(5,'kaushik','ECE',479,'A');

    SELECT * FROM STUDENTS_DETAIL;


    USER DEFINED FUNCTION
    DECLARE
    TYPE STUDENT_DETAILS IS RECORD
    (
    STU_ID STUDENTS_DETAIL.STU_ID%TYPE,
    STU_NAME STUDENTS_DETAIL.STU_NAME%TYPE,
    TOTAL_MARK STUDENTS_DETAIL.TOTAL_MARK%TYPE,
    STU_GRADE STUDENTS_DETAIL.STU_GRADE%TYPE
    );
    v_person_student_details STUDENT_DETAILS;
    BEGIN
    SELECT STU_ID,STU_NAME,TOTAL_MARK,STU_GRADE
    INTO v_person_student_details
    FROM STUDENTS_DETAIL
    WHERE STU_ID = 8;
    DBMS_OUTPUT.PUT_LINE('Student ID: '||
    v_person_student_details.STU_ID || ',Name: ' ||
    v_person_student_details.STU_NAME || ',Total Mark: ' ||
    v_person_student_details.TOTAL_MARK || ',Grade: ' ||
    v_person_student_details.STU_GRADE);
    END;
    /

    Index by table
    DECLARE
    TYPE t_hire_date IS TABLE OF STUDENTS_DETAIL.STU_GRADE%TYPE
    INDEX BY VARCHAR(20);
    v_hire_date_tab t_hire_date;
    v_hire_date_count VARCHAR(20);
    BEGIN
    FOR stu_rec IN (SELECT STU_GRADE FROM STUDENTS_DETAIL)
    LOOP
    v_hire_date_tab(stu_rec.STU_GRADE):=stu_rec.STU_GRADE;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(v_hire_date_tab.COUNT);
    END;

    You might also like