1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

RE:Using a Record, nested table and table by index as Parameter to a Procedure

Discussion in 'SQL PL/SQL' started by Ramayan, Dec 14, 2012.

  1. Ramayan

    Ramayan Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    India
    hello forum ,
    Can someone clear me on the Using a Record, nested table and index by table as Parameter to a Procedure and how it is done

    Code (SQL):

    DECLARE
    TYPE EmpRecTyp IS RECORD (
    emp_id NUMBER(6),
    emp_sal NUMBER(8,2) );  --- I am clear on this
    PROCEDURE raise_salary (emp_info EmpRecTyp) IS -- emp_info as input  going to have two column ?
    BEGIN
    UPDATE employees SET salary = salary + salary * .10
    WHERE employee_id = emp_info.emp_id; -- there is no data in the emp_info.emp_id so why are we comparing it with the emp.employee_id ?
    END raise_salary;
    BEGIN
    NULL;
    END;
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Re: Using a Record, nested table and table by index as Parameter to a Procedure

    One of steps of the analysis of the unit PL/SQL is comparison of types of parameters, variables, etc.
    Use in dml the operator of update of a condition of employee_id = emp_info.emp_id won't cause errors since everything is correct.
    At an execution stage as won't cause errors even if to transfer in quality of the null parameter

    Here an example of your modified pl/sql of a code :
    Code (Text):

    set serveroutput on
    set echo off
    cl scr
    Declare
    TYPE EmpRecTyp IS RECORD (
         emp_id NUMBER(6),
         emp_sal NUMBER(8,2) );  ---this declaration of elements of type for type record    
    v_emp_rec EmpRecTyp;--this declaration variable of type record EmpRecTyp
    PROCEDURE raise_salary (emp_info in  EmpRecTyp) IS -- emp_info as input  going to have two column ?
      --local variable and initialization of emp_info
      v_tmp_rec EmpRecTyp:=emp_info;
    BEGIN  
      dbms_output.put_line('Before exec this block :EMP_ID IS '||nvl(to_char(v_tmp_rec.emp_id),' NULL')||',EMP_SAL IS '||nvl(to_char(v_tmp_rec.emp_sal),' NULL'));  
      UPDATE employees SET salary = salary + salary * .10
      WHERE employee_id = emp_info.emp_id -- there is no data in the emp_info.emp_id so why are we comparing it with the emp.employee_id ?
      returning salary into v_tmp_rec.emp_sal;
      dbms_output.put_line('Updating of '||sql%rowcount||' records');
      dbms_output.put_line('After exec this block :EMP_ID IS '||nvl(to_char(v_tmp_rec.emp_id),' NULL')||',EMP_SAL IS '||nvl(to_char(v_tmp_rec.emp_sal),' NULL'));
      dbms_output.put_line(chr(10));
    END raise_salary;
    BEGIN      
        -- for example
        select
            emp.employee_id
            ,emp.salary
        into v_emp_rec    
        from employees emp
        where emp.employee_id=100;        
        raise_salary (null);
        raise_salary (v_emp_rec);
        v_emp_rec.emp_id:=null;
        raise_salary (v_emp_rec);
        rollback;
    END;
    /
     
     
    Ramayan likes this.
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Re: Using a Record, nested table and table by index as Parameter to a Procedure

    The following code explains how to pass the nested table to procedure.
    Code (Text):

    SQL> CREATE OR REPLACE PACKAGE emppack
      2  AS
      3
      4      TYPE empdtls_tab IS TABLE OF emp%ROWTYPE; --empdtls_rec;
      5      TYPE empno_tab IS TABLE OF emp.empno%TYPE;
      6      PROCEDURE add_emp (emp_list IN empdtls_tab);
      7      PROCEDURE incr_sal (empno_list IN empno_tab, perc_incr IN NUMBER DEFAULT 10);
      8      PROCEDURE rem_emp (empno_list IN empno_tab);
      9      END emppack;
      10
     11  /

    Package created.

    SQL> CREATE OR REPLACE PACKAGE BODY emppack
      2  AS
      3
      4      PROCEDURE add_emp (emp_list IN empdtls_tab)
      5      AS
      6      BEGIN
            FOR i IN emp_list.FIRST..emp_list.LAST
      7    8          LOOP
      9              INSERT INTO emp values (emp_list(i).EMPNO,
     10                                      emp_list(i).ENAME,
     11                                      emp_list(i).JOB,
     12                                      emp_list(i).MGR,
     13                                      emp_list(i).HIREDATE,
     14                                      emp_list(i).SAL,
     15                                      emp_list(i).COMM,
     16                                      emp_list(i).DEPTNO);
     17          END LOOP;
     18          COMMIT;
     19      END add_emp;
     20
     21      PROCEDURE incr_sal (empno_list IN empno_tab, perc_incr IN NUMBER)
        AS
     22   23      BEGIN
     24          FOR i IN empno_list.FIRST..empno_list.LAST
     25          LOOP
     26              UPDATE emp
     27              SET SAL =SAL + SAL * (perc_incr/100)
     28              WHERE EMPNO = empno_list(i);
     29          END LOOP;
     30          COMMIT;
     31      END incr_sal;
     32
        PROCEDURE rem_emp (empno_list IN empno_tab)
     33   34      AS
        BEGIN
     35   36          FOR i IN empno_list.FIRST..empno_list.LAST
     37          LOOP
     38              DELETE emp
     39              WHERE EMPNO = empno_list(i);
     40          END LOOP;
     41          COMMIT;
        END rem_emp;

     42   43   44  END emppack;
     45  /

    Package body created.

    SQL> select * from emp;
         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
          7369 SMITH      CLERK           7902 17-DEC-80        800                    20
          7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
          7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
          7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
          7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
          7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
          7839 KING       PRESIDENT            17-NOV-81       5000                    10
          7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
          7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
          7900 JAMES      CLERK           7698 03-DEC-81        950                    30
          7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
          7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
          7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
          7566 JONES      MANAGER         7839 02-APR-81       2975                    20

    14 rows selected.

    SQL>
    SQL> -- Adding employee by using nested tables
    SQL> DECLARE
      2      elist emppack.empdtls_tab := emppack.empdtls_tab ();
      3      enolist emppack.empno_tab;
      4  BEGIN
      5      elist.extend (2);
      6
      7      elist(1).EMPNO    := 1000;
      8      elist(1).ENAME    := 'RAJ';
      9      elist(1).JOB      := 'CLERK';
     10      elist(1).MGR      := 7698;
     11      elist(1).HIREDATE := TO_DATE('01012010','DDMMYYYY');
     12      elist(1).SAL      := 2500;
     13      elist(1).COMM     := NULL;
     14      elist(1).DEPTNO   := 20;
     15
     16      elist(2).EMPNO    := 2000;
     17      elist(2).ENAME    := 'RAM';
     18      elist(2).JOB      := 'SALESMAN';
     19      elist(2).MGR      := 7698;
     20      elist(2).HIREDATE := TO_DATE('01012012','DDMMYYYY');
     21      elist(2).SAL      := 2800;
     22      elist(2).COMM     := NULL;
     23      elist(2).DEPTNO   := 20;
     24
     25      enolist     := emppack.empno_tab(1000,2000);
     26
     27       emppack.add_emp(elist);
     28       --emppack.incr_sal(enolist,50);
     29       --emppack.rem_emp(enolist);

     30   31  END  ;
     32  /

    PL/SQL procedure successfully completed.

    SQL>  select * from emp;

         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
          7369 SMITH      CLERK           7902 17-DEC-80        800                    20
          7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
          7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
          7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
          7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
          7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
          7839 KING       PRESIDENT            17-NOV-81       5000                    10
          7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
          7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
          7900 JAMES      CLERK           7698 03-DEC-81        950                    30
          7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
          7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
          1000 RAJ        CLERK           7698 01-JAN-10       2500                    20
          2000 RAM        SALESMAN        7698 01-JAN-12       2800                    20
          7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
          7566 JONES      MANAGER         7839 02-APR-81       2975                    20

    16 rows selected.

    SQL> -- 16 employees
    SQL> -- incrementing salary by using nested tables
    SQL>
    SQL> DECLARE
      2      elist emppack.empdtls_tab := emppack.empdtls_tab ();
      3      enolist emppack.empno_tab;
      4  BEGIN
      5      elist.extend (2);
      6
      7      elist(1).EMPNO    := 1000;
      8      elist(1).ENAME    := 'RAJ';
      9      elist(1).JOB      := 'CLERK';
        elist(1).MGR      := 7698;
     10   11      elist(1).HIREDATE := TO_DATE('01012010','DDMMYYYY');
     12      elist(1).SAL      := 2500;
     13      elist(1).COMM     := NULL;
     14      elist(1).DEPTNO   := 20;
     15
     16      elist(2).EMPNO    := 2000;
     17      elist(2).ENAME    := 'RAM';
        elist(2).JOB      := 'SALESMAN';
     18   19      elist(2).MGR      := 7698;
     20      elist(2).HIREDATE := TO_DATE('01012012','DDMMYYYY');
     21      elist(2).SAL      := 2800;
     22      elist(2).COMM     := NULL;
     23      elist(2).DEPTNO   := 20;
     24
     25      enolist     := emppack.empno_tab(1000,2000);
     26
     27       --emppack.add_emp(elist);
     28       emppack.incr_sal(enolist,50);
         --emppack.rem_emp(enolist);
     29   30
     31  END  ;
     32  /

    PL/SQL procedure successfully completed.

    SQL> select * from emp;

         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
          7369 SMITH      CLERK           7902 17-DEC-80        800                    20
          7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
          7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
          7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
          7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
          7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
          7839 KING       PRESIDENT            17-NOV-81       5000                    10
          7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
          7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
          7900 JAMES      CLERK           7698 03-DEC-81        950                    30
          7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
          7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
          1000 RAJ        CLERK           7698 01-JAN-10       3750                    20
          2000 RAM        SALESMAN        7698 01-JAN-12       4200                    20
          7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
          7566 JONES      MANAGER         7839 02-APR-81       2975                    20

    16 rows selected.

    SQL> -- Incremented
    SQL>
    SQL> -- removing employees by using nested tables
    SQL>
    SQL> DECLARE
      2      elist emppack.empdtls_tab := emppack.empdtls_tab ();
        enolist emppack.empno_tab;
      3    4  BEGIN
      5      elist.extend (2);
      6
      7      elist(1).EMPNO    := 1000;
      8      elist(1).ENAME    := 'RAJ';
      9      elist(1).JOB      := 'CLERK';
     10      elist(1).MGR      := 7698;
     11      elist(1).HIREDATE := TO_DATE('01012010','DDMMYYYY');
     12      elist(1).SAL      := 2500;
     13      elist(1).COMM     := NULL;
     14      elist(1).DEPTNO   := 20;
     15
     16      elist(2).EMPNO    := 2000;
     17      elist(2).ENAME    := 'RAM';
     18      elist(2).JOB      := 'SALESMAN';
     19      elist(2).MGR      := 7698;
     20      elist(2).HIREDATE := TO_DATE('01012012','DDMMYYYY');
     21      elist(2).SAL      := 2800;
     22      elist(2).COMM     := NULL;
     23      elist(2).DEPTNO   := 20;
     24
     25      enolist     := emppack.empno_tab(1000,2000);
     26
     27       --emppack.add_emp(elist);
     28       --emppack.incr_sal(enolist,50);
     29       emppack.rem_emp(enolist);
     30
     31  END  ;
     32  /

    PL/SQL procedure successfully completed.

    SQL> select * from emp;

         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
          7369 SMITH      CLERK           7902 17-DEC-80        800                    20
          7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
          7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
          7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
          7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
          7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
          7839 KING       PRESIDENT            17-NOV-81       5000                    10
          7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
          7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
          7900 JAMES      CLERK           7698 03-DEC-81        950                    30
          7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
          7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
          7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
          7566 JONES      MANAGER         7839 02-APR-81       2975                    20

    14 rows selected.

    SQL> -- removed
    SQL>