Go Back

PLSQL 6

PL/SQL -> PLSQLBasics

INTRODUCTION TO PL/SQL 5:

PLSQL STORED PROCEDURES, FUNCTIONS::

PROCEDURE with IN parameter:
 
SQL> CREATE TABLE EMP_DUP
    AS
    SELECT * FROM EMP;

Table created.

SQL> SELECT * FROM EMP_DUP;

     EMPNO ENAME      JOB         	MGR 	HIREDATE      SAL       COMM     DEPTNO                                             
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                             
      7369 SMITH      	CLERK           	7902 	17-DEC-80        800                    	20                                             
      7499 ALLEN      	SALESMAN      7698 	20-FEB-81       1600        300         	30                                             
      7521 WARD       	SALESMAN      7698 	22-FEB-81       1250        500         	30                                             
      7566 JONES     	MANAGER        7839 	02-APR-81       2975                    	20                                             
      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 	19-APR-87       3000                    	20                                             
      7839 KING       	PRESIDENT            	17-NOV-81       5000                    	10                                             
      7844 TURNER     	SALESMAN        7698 	08-SEP-81       1500          0         	30                                             
      7876 ADAMS      	CLERK           	  7788 	23-MAY-87       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                                             

14 rows selected.

CREATE OR REPLACE 
PROCEDURE Xxaoa_procedure (v_empno IN emp_dup.empno%TYPE) 
IS 
BEGIN 
    UPDATE emp_dup 
       SET sal = sal + 1000 
     WHERE empno = v_empno; 
END; 
/
Procedure created.

SQL> EXECUTE XXAOA_PROCEDURE(7369);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM EMP_DUP;

     EMPNO ENAME      	JOB              	MGR HIREDATE         SAL       COMM     DEPTNO                                             
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                             
      7369 SMITH      	CLERK           	7902 17-DEC-80       1800                    		20                                             
      7499 ALLEN      	SALESMAN      7698 20-FEB-81       1600        300         		30                                             
      7521 WARD       	SALESMAN      7698 22-FEB-81       1250        500         		30                                             
      7566 JONES     	MANAGER        7839 02-APR-81       2975                    		20                                             
      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 19-APR-87       3000                    		20                                             
      7839 KING       	PRESIDENT            17-NOV-81       5000                    		10                                             
      7844 TURNER     	SALESMAN      7698 08-SEP-81       1500          0         		30                                             
      7876 ADAMS      	CLERK           	7788 23-MAY-87       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                                             

14 rows selected.

PROCEDURE with OUT parameter:

 

CREATE OR replace PROCEDURE Xxaoa_procedure1 (v_empno  IN emp.empno%TYPE, 
                                              v_ename  OUT emp.ename%TYPE, 
                                              v_sal    OUT emp.sal%TYPE, 
                                              v_deptno OUT emp.deptno%TYPE) 
IS 
BEGIN 
    SELECT ename, 
           sal, 
           deptno 
      INTO v_ename, v_sal, v_deptno 
      FROM emp 
     WHERE empno = v_empno; 
END; 
/
Procedure created.

DECLARE 
    p_ename  emp.ename%TYPE; 
    p_sal    emp.sal%TYPE; 
    p_deptno emp.deptno%TYPE; 
    p_empno  emp.empno%TYPE := 7369; 
BEGIN 
    Xxaoa_procedure1(p_empno, p_ename, p_sal, p_deptno); 

    dbms_output.Put_line(p_ename 
                         ||p_sal 
                         ||p_deptno); 
END; 
/
PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT ON
SQL> /

SMITH80020                                                                                                                        

PL/SQL procedure successfully completed.

SQL> VARIABLE G_ENAME VARCHAR2(10)

SQL> VARIABLE G_SAL NUMBER

SQL> VARIABLE G_DEPTNO  NUMBER

SQL> EXECUTE XXAOA_PROCEDURE1(7369,:G_ENAME,:G_SAL,:G_DEPTNO);

PL/SQL procedure successfully completed.

SQL> PRINT G_ENAME

G_ENAME                                                                                                                           
--------------------------------                                                                                                  
SMITH                                                                                                                             

SQL> PRINT G_SAL

     G_SAL                                                                                                                        
----------                                                                                                                        
       800                                                                                                                        

SQL> PRINT G_DEPTNO

  G_DEPTNO                                                                                                                        
----------                                                                                                                        
        20                                                                                                                        

PROCEDURE WITH IN OUT PARAMETER:

 
CREATE OR replace PROCEDURE Xxaoa_procedure2 (v_empno_sal IN OUT NUMBER) 
IS 
BEGIN 
    SELECT sal 
      INTO v_empno_sal 
      FROM emp 
     WHERE empno = v_empno_sal; 
END; 
/
Procedure created.

DECLARE 
    p_empno_sal NUMBER := 7369; 
BEGIN 
    Xxaoa_procedure2(p_empno_sal); 

    dbms_output.Put_line(p_empno_sal); 
END; 
/
800                                                                                                                               

PL/SQL procedure successfully completed.

SQL> VARIABLE g_empno_sal  NUMBER

   BEGIN
      :g_empno_sal:=7369;
   END;
SQL> /

PL/SQL procedure successfully completed.

SQL> EXECUTE XXAOA_PROCEDURE2(:G_EMPNO_SAL);

PL/SQL procedure successfully completed.

SQL> PRINT G_EMPNO_SAL

G_EMPNO_SAL                                                                                                                       
-----------                                                                                                                       
        800                                                                                                                       

USING DEFAULT IN PROCEDURES:

 
SQL> CREATE TABLE DEPT1
     AS
     SELECT * FROM DEPT;

Table created.

SQL> DELETE FROM DEPT1;

4 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> CREATE SEQUENCE ADD_DEPT_SEQ
     INCREMENT BY 1
     START WITH 100
     MAXVALUE 120
     NOCACHE;

Sequence created.

CREATE OR replace PROCEDURE Add_dept ( 
v_dname IN dept1.dname%TYPE DEFAULT 'RAJESH', 
v_loc   IN dept1.loc%TYPE DEFAULT 'RAGHU') 
IS 
BEGIN 
    INSERT INTO dept1 
         VALUES (add_dept_seq.NEXTVAL, 
                 v_dname, 
                 v_loc); 
END; 
/
Procedure created.

BEGIN 
    add_dept; 

    Add_dept('SALES', 'NEWYORK'); 

    Add_dept(v_loc => 'DALLAS', v_dname => 'EDUCATION'); 

    Add_dept(v_loc => 'BOSTON'); 
END; 
/
PL/SQL procedure successfully completed.

SQL> SELECT * FROM DEPT1;

    DEPTNO DNAME          LOC                                                                                                     
---------- -------------- -------------                                                                                           
       100 RAJESH          RAGHU                                                                                                 
       101 SALES          NEWYORK                                                                                                 
       102 EDUCATION      DALLAS                                                                                                  
       103 RAJESH          BOSTON                                                                                                  

SQL> CREATE TABLE LOG_TABLE
     (USERID   VARCHAR2(10),
     LOG_DATE   DATE);

Table created.

DECLARING SUBPROGRAMS:

 

CREATE OR replace PROCEDURE Leave_emp2 (v_empno IN emp.empno%TYPE) 
IS 
  PROCEDURE Log_exec 
  IS 
  BEGIN 
      INSERT INTO log_table 
                  (userid, 
                   log_date) 
           VALUES (USER, 
                   SYSDATE); 
  END log_exec; 
BEGIN 
    DELETE FROM emp_dup 
     WHERE empno = v_empno; 

    log_exec; 
END; 
/

Procedure created.

SQL> SELECT * FROM LOG_TABLE;

no rows selected
SQL> SELECT * FROM EMP_DUP;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     		DEPTNO                                             
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                             
      7369 SMITH      	CLERK           7902 17-DEC-80        800                    		20                                             
      7499 ALLEN      	SALESMAN    7698 20-FEB-81       1600        300         		30                                             
      7521 WARD       	SALESMAN    7698 22-FEB-81       1250        500         		30                                             
      7566 JONES      	MANAGER      7839 02-APR-81       2975                    		20                                             
      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 19-APR-87       3000                    		20                                             
      7839 KING       	PRESIDENT            17-NOV-81       5000                    		10                                             
      7844 TURNER     	SALESMAN      7698 08-SEP-81       1500          0         		30                                             
      7876 ADAMS      	CLERK              7788 23-MAY-87       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                                             

14 rows selected.

SQL> BEGIN

  2  LEAVE_EMP2(7369);

  3  END;

  4  /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM LOG_TABLE;

USERID     LOG_DATE                                                                                                               
---------- ---------                                                                                                              
SCOTT      09-JUN-05                                                                                                              

SQL> SELECT * FROM EMP_DUP;

     EMPNO ENAME      JOB              	MGR HIREDATE         SAL       COMM     DEPTNO                                             
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                             
      7499 ALLEN      	SALESMAN        7698 20-FEB-81       1600        300         	30                                             
      7521 WARD       	SALESMAN        7698 22-FEB-81       1250        500         	30                                             
      7566 JONES      	MANAGER         7839 02-APR-81       2975                    		20                                             
      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 19-APR-87       3000                    		20                                             
      7839 KING       	PRESIDENT                17-NOV-81       5000                    		10                                             
      7844 TURNER     	SALESMAN        7698 08-SEP-81       1500          0         		30                                             
      7876 ADAMS      	CLERK                7788 23-MAY-87       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                                             

13 rows selected.


CREATE OR replace PROCEDURE Raise_salary (v_empno IN emp.empno%TYPE) 
IS 
BEGIN 
    UPDATE emp_dup 
       SET sal = sal * 1.10 
     WHERE empno = v_empno; 
END; 
/
Procedure created.


SQL> SELECT * FROM EMP_DUP;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                             
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                             
      7369 SMITH      	CLERK           7902 17-DEC-80        800                    	20                                             
      7499 ALLEN      	SALESMAN   7698 20-FEB-81       1600        300         	30                                             
      7521 WARD       	SALESMAN   7698 22-FEB-81       1250        500         	30                                             
      7566 JONES      	MANAGER     7839 02-APR-81       2975                    	20                                             
      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 19-APR-87       3000                    	20                                             
      7839 KING       	PRESIDENT    	        17-NOV-81       5000                    	10                                             
      7844 TURNER     	SALESMAN     7698 08-SEP-81       1500          0         	30                                             
      7876 ADAMS      	CLERK             7788 23-MAY-87       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                                             

14 rows selected.

Invoking a PROCEDURE from a STORED PROCEDURE::

 
CREATE OR replace PROCEDURE Raghu 
IS 
  CURSOR c IS 
    SELECT empno 
      FROM emp_dup; 
BEGIN 
    FOR e IN c LOOP 
        Raise_salary(e.empno); 
    END LOOP; 

    COMMIT; 
END; 
/
Procedure created.

SQL> EXECUTE RAGHU;

PL/SQL procedure successfully completed.

SQL> SELECT * FROM EMP_DUP;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                             
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                             
      7369 SMITH      	CLERK           7902 17-DEC-80        880                    	20                                             
      7499 ALLEN      	SALESMAN   7698 20-FEB-81       1760        300         	30                                             
      7521 WARD       	SALESMAN   7698 22-FEB-81       1375        500         	30                                             
      7566 JONES      	MANAGER     7839 02-APR-81     3272.5                    	20                                             
      7654 MARTIN     	SALESMAN    7698 28-SEP-81       1375       1400         	30                                             
      7698 BLAKE      	MANAGER     7839 01-MAY-81       3135                    	30                                             
      7782 CLARK      	MANAGER     7839 09-JUN-81       2695                    	10                                             
      7788 SCOTT      	ANALYST       7566 19-APR-87       3300                    	20                                             
      7839 KING       	PRESIDENT            17-NOV-81       5500                    	10                                             
      7844 TURNER     	SALESMAN     7698 08-SEP-81       1650          0         	30                                             
      7876 ADAMS      	CLERK           7788 23-MAY-87       1210                    	20                                             
      7900 JAMES      	CLERK           7698 03-DEC-81       1045                    	30                                             
      7902 FORD       	ANALYST       7566 03-DEC-81       3300                    	20                                             
      7934 MILLER     	CLERK           7782 23-JAN-82       1430                    	10                                             

14 rows selected.

Droping a STORED PROCEDURE:

 

SQL> DROP PROCEDURE RAGHU;

Procedure dropped.

Quering STORED PROCEDURES stored in database:

 
SQL> SELECT OBJECT_NAME,OBJECT_TYPE
  2  FROM USER_OBJECTS
  3  WHERE OBJECT_TYPE = 'PROCEDURE';

OBJECT_NAME                                                                                                                       
--------------------------------------------------------------------------------------------------------------------------------  
OBJECT_TYPE                                                                                                                       
------------------                                                                                                                
ADD_DEPT                                                                                                                          
PROCEDURE                                                                                                                         
                                                                                                                                  
XXAOA_PROCEDURE                                                                                                                            
PROCEDURE                                                                                                                         
                                                                                                                                  
XXAOA_PROCEDURE1                                                                                                                           
PROCEDURE                                                                                                                         
                                                                                                                                  
XXAOA_PROCEDURE2                                                                                                                           
PROCEDURE                                                                                                                         
                                                                                                                                  
LEAVE_EMP2                                                                                                                        
PROCEDURE                                                                                                                         
                                                                                                                                  
RAISE_SALARY                                                                                                                      
PROCEDURE                                                                                                                         
                                                                                                                                
6 rows selected.

Quering STORED PROCEDURES text stored in database:

 
SQL> DESC USER_SOURCE
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 NAME                                                                             VARCHAR2(30)
 TYPE                                                                             VARCHAR2(12)
 LINE                                                                             NUMBER
 TEXT                                                                             VARCHAR2(4000)

SQL> SELECT TEXT FROM USER_SOURCE
  2  WHERE NAME='XXAOA_PROCEDURE'
  3  ORDER BY LINE;

TEXT                                                                                                                              
----------------------------------------------------------------------------------------------------------------------------------
PROCEDURE XXAOA_PROCEDURE                                                                                                                  
 (V_EMPNO IN EMP.EMPNO%TYPE)                                                                                                      
 IS                                                                                                                               
 BEGIN                                                                                                                            
UPDATE EMP_DUP                                                                                                                    
SET SAL = SAL + 1000                                                                                                              
WHERE EMPNO = V_EMPNO;                                                                                                            
END;                                                                                                                              

8 rows selected.


  1  SELECT TEXT FROM USER_SOURCE
  2  WHERE NAME='XXAOA_PROCEDURE1'
  3* ORDER BY LINE
SQL> /

TEXT                                                                                                                              
----------------------------------------------------------------------------------------------------------------------------------
PROCEDURE XXAOA_PROCEDURE1                                                                                                                 
(V_EMPNO IN EMP.EMPNO%TYPE,                                                                                                       
V_ENAME OUT EMP.ENAME%TYPE,                                                                                                       
V_SAL OUT EMP.SAL%TYPE,                                                                                                           
V_DEPTNO OUT EMP.DEPTNO%TYPE                                                                                                      
 )                                                                                                                                
IS                                                                                                                                
BEGIN                                                                                                                             
SELECT ENAME,SAL,DEPTNO INTO V_ENAME,V_SAL,V_DEPTNO                                                                               
FROM EMP WHERE EMPNO=V_EMPNO;                                                                                                     
END;                                                                                                                              

11 rows selected.


  1  SELECT TEXT FROM USER_SOURCE
  2  WHERE NAME='XXAOA_PROCEDURE2'
  3* ORDER BY LINE
SQL> /

TEXT                                                                                                                              
----------------------------------------------------------------------------------------------------------------------------------
PROCEDURE XXAOA_PROCEDURE2                                                                                                                 
(V_EMPNO_SAL IN OUT NUMBER)                                                                                                       
IS                                                                                                                                
BEGIN                                                                                                                             
SELECT SAL INTO V_EMPNO_SAL                                                                                                       
FROM EMP WHERE EMPNO=V_EMPNO_SAL;                                                                                                 
END;                                                                                                                              

7 rows selected.

CREATE OR replace PROCEDURE Log_execution 
IS 
BEGIN 
    INSRT INTO log_table 
         VALUES (USER, 
                 SYSDATE); 
END; 
/
Warning: Procedure created with compilation errors.

Quering errors in STORED PROCEDURES stored in database:

 
SQL> DESC USER_ERRORS
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 NAME                                                                    NOT NULL VARCHAR2(30)
 TYPE                                                                             VARCHAR2(12)
 SEQUENCE                                                                NOT NULL NUMBER
 LINE                                                                    NOT NULL NUMBER
 POSITION                                                                NOT NULL NUMBER
 TEXT                                                                    NOT NULL VARCHAR2(4000)

SQL> SELECT LINE||'/'|| POSITION POS,TEXT FROM
  2  USER_ERRORS
  3  WHERE NAME='LOG_EXECUTION'
  4  ORDER BY LINE;

POS                                                                                                                               
---------------------------------------------------------------------------------                                                 
TEXT                                                                                                                              
----------------------------------------------------------------------------------------------------------------------------------
4/7                                                                                                                               
PLS-00103: Encountered the symbol "INTO" when expecting one of the following:                                                     
                                                                                                                                  
   := . ( @ % ;                                                                                                                   
                                                                                                                                  
7/1                                                                                                                               
PLS-00103: Encountered the symbol "END"                                                                                           
                                                                                                                                  

SQL> SHOW ERRORS PROCEDURE LOG_EXECUTION
Errors for PROCEDURE LOG_EXECUTION:

LINE/COL ERROR                                                                                                                    
-------- -----------------------------------------------------------------                                                        
4/7      PLS-00103: Encountered the symbol "INTO" when expecting one of                                                           
         the following:                                                                                                           
         := . ( @ % ;                                                                                                             
                                                                                                                                  
7/1      PLS-00103: Encountered the symbol "END"                                                                                  

STORED FUNCTIONS:

 
CREATE OR replace FUNCTION Get_sal (v_empno IN emp.empno%TYPE) 
RETURN NUMBER 
IS 
  v_sal emp.sal%TYPE; 
BEGIN 
    SELECT sal 
      INTO v_sal 
      FROM emp 
     WHERE empno = v_empno; 

    RETURN v_sal; 
END; 
/
Function created.

Calling a FUNTION:

 
SQL> VARIABLE G_SALARY NUMBER
SQL> EXECUTE :G_SALARY :=GET_SAL(7369)

PL/SQL procedure successfully completed.

SQL> PRINT G_SALARY

  G_SALARY                                                                                                                        
----------                                                                                                                        
       800                                                                                                                        

DECLARE 
    v_salary NUMBER(10); 
BEGIN 
    v_salary := Get_sal(7369); 

    dbms_output.Put_line(v_salary); 
END; 
/
PL/SQL procedure successfully completed.

Calling FUNCTION in various places:

 

CREATE OR replace FUNCTION Tax (v_value IN NUMBER) 
RETURN NUMBER 
IS 
BEGIN 
    RETURN ( v_value * 0.08 ); 
END; 
/

Function created.

SQL> SELECT EMPNO,ENAME,SAL,TAX(SAL) FROM EMP;

     EMPNO ENAME             SAL   TAX(SAL)                                                                                       
---------- ---------- ---------- ----------                                                                                       
      7369 SMITH             	800         64                                                                                       
      7499 ALLEN            		1600        128                                                                                       
      7521 WARD             	1250        100                                                                                       
      7566 JONES            	2975        238                                                                                       
      7654 MARTIN           	1250        100                                                                                       
      7698 BLAKE            		2850        228                                                                                       
      7782 CLARK            	2450        196                                                                                       
      7788 SCOTT            	3000        240                                                                                       
      7839 KING             		5000        400                                                                                       
      7844 TURNER           	1500        120                                                                                       
      7876 ADAMS            	1100         88                                                                                       
      7900 JAMES             	950         76                                                                                       
      7902 FORD             		3000        240                                                                                       
      7934 MILLER           	1300        104                                                                                       

14 rows selected.


  1  SELECT EMPNO FROM EMP
  2  WHERE TAX(SAL) > (SELECT MAX(TAX(SAL)) FROM EMP
  3* WHERE DEPTNO=30) ORDER BY TAX(SAL) DESC
  4  /

     EMPNO                                                                                                                        
----------                                                                                                                        
      7839                                                                                                                        
      7788                                                                                                                        
      7902                                                                                                                        
      7566                                                                                                                        

  1  SELECT *  FROM EMP
  2  WHERE TAX(SAL) > (SELECT MAX(TAX(SAL)) FROM EMP
  3* WHERE DEPTNO=30) ORDER BY TAX(SAL) DESC
SQL> /

     EMPNO ENAME      JOB              MGR 	HIREDATE         	SAL       COMM     DEPTNO                                             
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                             
      7839 KING       	PRESIDENT            	17-NOV-81      		5000                    	10                                             
      7788 SCOTT      	ANALYST         7566 	19-APR-87       		3000                    	20                                             
      7902 FORD       	ANALYST         7566 	03-DEC-81       		3000                    	20                                             
      7566 JONES      	MANAGER       7839 	02-APR-81       		2975                    	20                                             

SQL> SELECT SAL,TAX(SAL) FROM EMP
  2  WHERE TAX(SAL) > (SELECT MAX(TAX(SAL)) FROM EMP
  3  WHERE DEPTNO=30) ORDER BY TAX(SAL) DESC;

       SAL   TAX(SAL)                                                                                                             
---------- ----------                                                                                                             
      5000        400                                                                                                             
      3000        240                                                                                                             
      3000        240                                                                                                             
      2975        238                                                                                                             

Droping a FUNCTION:

 
SQL> DROP FUNCTION TAX;

Function dropped.

Selecting FUNCTION from database:

 
SQL> SELECT OBJECT_NAME,OBJECT_TYPE
  2  FROM USER_OBJECTS
  3  WHERE OBJECT_TYPE='FUNCTION';

OBJECT_NAME                                                                                                                       
--------------------------------------------------------------------------------------------------------------------------------  
OBJECT_TYPE                                                                                                                       
------------------                                                                                                                
GET_SAL                                                                                                                           
FUNCTION                                                                                                                          

Source code of FUNCTION:

 
  1  SELECT TEXT FROM USER_SOURCE
  2* WHERE NAME='GET_SAL'
  3  /

TEXT                                                                                                                              
----------------------------------------------------------------------------------------------------------------------------------
FUNCTION GET_SAL                                                                                                                  
(V_EMPNO  IN EMP.EMPNO%TYPE)                                                                                                      
RETURN NUMBER                                                                                                                     
IS                                                                                                                                
V_SAL EMP.SAL%TYPE;                                                                                                               
BEGIN                                                                                                                             
SELECT SAL INTO V_SAL FROM EMP                                                                                                    
WHERE EMPNO=V_EMPNO;                                                                                                              
RETURN V_SAL;                                                                                                                     
END;                                                                                                                              

10 rows selected.

Describing FUNCTION in SQL*PLUS:

 
SQL> DESC GET_SAL
FUNCTION GET_SAL RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 V_EMPNO                        NUMBER(4)               IN    

 

Describing PROCEDURE in SQL*PLUS:

 

SQL> DESC  PROCEDURE_NAME/FUNCTION_NAME;

LOCATIONS TO CALL USER-DEFINED FUNCTIONS:

 
   1. SELECT LIST OF A SELECT COMMAND.
   2. CONDITION OF WHERE AND HAVING CLAUSES.
   3. CONNECT BY ,START WITH ,ORDER BY AND GROUP BY CLAUSES.
   4. VALUES CLAUSES OF INSERT COMMAND.
   5. SET CLAUSE OF THE UPDATE COMMAND.
-------------------------------------------------------------------------------
|PROCEDURE	                          |                   FUNCTION            |
|------------------------------------------------------------------------------
| EXECUTE AS A PL/SQL STATEMENT       | INVOKE AS PART OF A N EXPRESSION.     |
|------------------------------------------------------------------------------
| NO RETURN DATA TYPE.	              | MUST CONTAIN A RETURN DATA TYPE.      |
|------------------------------------------------------------------------------
|CAN RETURN NONE,ONE OR MANY VALUES.  | MUST RETURN A SINGLE VALUE.           |
-------------------------------------------------------------------------------

BENEFITS OF STORED PROCEDURES AND STORED FUNCTIONS:


1. IMPROVED PERFORMANCE. 2. IMPROVED MAINTENANCE. 3. IMPROVED DATA SECURITY AND INTEGRITY.

Go Back

Go Back