Use user-defined function to combine and format columns : Function Return « Stored Procedure Function « Oracle PL / SQL

1. Oracle PL / SQL
2. Stored Procedure Function
3. Function Return

Use user-defined function to combine and format columns

    

SQL>
SQL> CREATE TABLE EMP(
  2      EMPNO NUMBER(4) NOT NULL,
  3      ENAME VARCHAR2(10),
  4      JOB VARCHAR2(9),
  5      MGR NUMBER(4),
  6      HIREDATE DATE,
  7      SAL NUMBER(7, 2),
  8      COMM NUMBER(7, 2),
  9      DEPTNO NUMBER(2)
 10  );

Table created.

SQL>
SQL> INSERT INTO EMP VALUES(7369, 'SMITH', 'CLERK', 7902,TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES(7499, 'ALLEN', 'SALESMAN', 7698,TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);

1 row created.

SQL> INSERT INTO EMP VALUES(7521, 'WARD', 'SALESMAN', 7698,TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);

1 row created.

SQL> INSERT INTO EMP VALUES(7566, 'JONES', 'MANAGER', 7839,TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES(7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);

1 row created.

SQL> INSERT INTO EMP VALUES(7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);

1 row created.

SQL> INSERT INTO EMP VALUES(7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);

1 row created.

SQL> INSERT INTO EMP VALUES(7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES(7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);

1 row created.

SQL> INSERT INTO EMP VALUES(7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);

1 row created.

SQL> INSERT INTO EMP VALUES(7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES(7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);

1 row created.

SQL> INSERT INTO EMP VALUES(7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES(7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

1 row created.




SQL>
SQL> create or replace function FORMAT_EMP (DeptNo IN number,EmpName IN varchar2,Title IN varchar2) return varchar2
  2  is
  3    concat_rslt   varchar2(100);
  4  begin
  5    concat_rslt :='Department: ' || to_char(DeptNo) ||' emp: ' || initcap(EmpName) ||' Title: ' || initcap(Title);
  6    return (concat_rslt);
  7  end;
  8  /

Function created.

SQL>
SQL> select format_emp(deptno,ename,job) from emp;

FORMAT_EMP(DEPTNO,ENAME,JOB)
--------------------------------------------------------------------------------
Department: 20 emp: Smith Title: Clerk
Department: 30 emp: Allen Title: Salesman
Department: 30 emp: Ward Title: Salesman
Department: 20 emp: Jones Title: Manager
Department: 30 emp: Martin Title: Salesman
Department: 30 emp: Blake Title: Manager
Department: 10 emp: Clark Title: Manager
Department: 20 emp: Scott Title: Analyst
Department: 10 emp: King Title: President
Department: 30 emp: Turner Title: Salesman
Department: 20 emp: Adams Title: Clerk

FORMAT_EMP(DEPTNO,ENAME,JOB)
--------------------------------------------------------------------------------
Department: 30 emp: James Title: Clerk
Department: 20 emp: Ford Title: Analyst
Department: 10 emp: Miller Title: Clerk

14 rows selected.

SQL>
SQL> drop table emp;

Table dropped.

   
    
    
    
  

Related examples in the same category