Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Tuesday, 27 June 2017

PL/SQL Cursor with Parameter

0 comments
 
To process any DML or SELECT queries, Oracle allocates an area of memory on the database server, known as context area
PL/SQL Cursor is a pointer to the context area
Context area contains information about the SQL statement and the set of data returned or affected by that statement
         
           PL/SQL Cursor is a mechanism by which one can name that work area and  manipulate the information within it

Parameter Cursor:

 

Parameters allow values to be passed to a cursor when it is opened and to be used in the query when it executes
Different active set each time based on a parameter value that is passed to a cursor


Parameter Cursor Syntax:



CURSOR cursor_name(para_name1  datatype,
    para_name2  datatype,…)                           
          IS  select_statement;

  
Parameter Cursors Example:

 

DECLARE
   CURSOR par_cur (p_deptno NUMBER, p_job VARCHAR2)
   IS
      SELECT empno, ename
        FROM emp
       WHERE deptno = p_deptno AND UPPER (job) = UPPER (p_job);
   v_deptno   emp.deptno%TYPE   := &dno;
   v_job      emp.job%TYPE      := '&job';
BEGIN
   FOR emp_record IN par_cur (v_deptno, v_job)
   LOOP
      DBMS_OUTPUT.put_line('Empno      :' || emp_record.empno);
      DBMS_OUTPUT.put_line('Ename       :' || emp_record.ename);
   END LOOP;
END;

Continue reading >>

Alter Table add Primary key Oracle

0 comments

This is used to avoid duplicates and nulls. This will work as combination of unique and not null. Primary key always attached to the parent table.

 Alter Table add Primary key Example


SQL> ALTER TABLE student ADD PRIMARY KEY(no);
          SQL> ALTER TABLE student ADD CONSTRAINT pk PRIMARY KEY(no);
Continue reading >>

SQL Create Table Primary Key

0 comments


This is used to avoid duplicates and nulls. This will work as combination of unique and not null. Primary key always attached to the parent table.

We can add this constraint in all three levels.

Example:

TABLE LEVEL PRIMARY KEY


SQL>CREATE TABLE student
(
   no      NUMBER (2),
   name    VARCHAR (10),
   marks   NUMBER (3),
   PRIMARY KEY (no)
);
SQL>CREATE TABLE student
(
   no      NUMBER (2),
   name    VARCHAR (10),
   marks   NUMBER (3),
   CONSTRAINT pk PRIMARY KEY (no)
);

COLUMN LEVEL PRIMARY KEY     



SQL>CREATE TABLE student
(
   no      NUMBER (2) PRIMARY KEY,
   name    VARCHAR (10),
   marks   NUMBER (3)
);
SQL>CREATE TABLE student
(
   no      NUMBER (2) CONSTRAINT pk PRIMARY KEY,
   name    VARCHAR (10),
   marks   NUMBER (3)
); un UNIQUE(no);
          

ALTER TABLE ADD PRIMARY KEY


SQL> ALTER TABLE student ADD PRIMARY KEY(no);
         SQL> ALTER TABLE student ADD CONSTRAINT pk PRIMARY KEY(no); 
Continue reading >>

Friday, 23 June 2017

Difference between Procedures and Functions

0 comments
Continue reading >>

Oracle PLSQL Functions

0 comments


PL/SQL blocks are anonymous, cannot be called from other PL/SQL blocks
Subprogram is a named PL/SQL block that could be either
Function, or
          Package

Syntax for Creating Oracle Function

A function is a named PL/SQL block that performs a task and returns a value to the calling environment.

Syntax:
CREATE  [ OR REPLACE ] FUNCTION  function_name
[( argument   [mode]   data type, argument   [mode]  data type )]
RETURN  data type
IS
       Local PL/SQL variable declarations
BEGIN
     Define task performed by the function and return
     result using   RETURN statement
EXCEPTION
       Handle exceptions if any
END [ function_name];


Functions in PL/SQL

Parameters Modes in Oracle Functions

Formal parameters can have three modes IN, OUT or IN OUT which decides the behavior of parameters


Parameters Modes in Procedures and Functions



Example 1
               
CREATE OR REPLACE FUNCTION cal_bonus (p_eno emp.empno%TYPE)
   RETURN NUMBER
IS
   v_sal   emp.sal%TYPE;
BEGIN
   SELECT sal
     INTO v_sal
     FROM emp
    WHERE empno = p_eno;

   RETURN (v_sal * 0.1);
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN -1;
END;

Example 2
        
CREATE OR REPLACE FUNCTION chk_dept (p_deptno dept.deptno%TYPE)
   RETURN BOOLEAN
IS
   v_deptno   dept.deptno%TYPE;
BEGIN
   SELECT deptno
     INTO v_deptno
     FROM dept
    WHERE deptno = p_deptno;

   RETURN TRUE;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN FALSE;
END;

Calling Stored Functions from Different Locations

Functions can be called from different locations through SELECT and DML statements like:
Column list of SELECT command
WHERE  and HAVING Clause
ORDER BY, GROUP BY Clauses
VALUES clause of  INSERT  command
SET clause of UPDATE  command
                                        
Note: Not all functions can be called from above mentioned locations.

Rules for Calling Oracle Function through a SQL Statement

To be callable from SQL statement:
The function must be stored in database as either stand alone function or as a part of a package
The function can take only IN type of parameters
The formal parameters and return type of the function must be oracle data types
The function must not end the current transaction (commit or rollback) or rollback to a savepoint prior to the function execution
The function must not issue any alter session or alter system commands

Referencing Objects from Other Schemas

To reference objects from other schemas we first need to have appropriate privileges on the objects
To refer to objects in schemas other than our own, prefix the schema name with the object name:
                   Schema_name.Object_name
Example: To get information from the emp table belonging to scott's schema the user HR should use the following command
                   SELECT * FROM scott.emp;

Note: The command will run successfully only if the user HR is granted SELECT right on Scott's emp table

To execute a procedure belonging to scott’s schema the user HR will use the following command
          EXECUTE scott.procedure_name

Granting Privileges on Functions


The object privileges applicable to functions are
EXECUTE
Grants right to execute the functions belonging to the schema of the other user
Syntax
          GRANT EXECUTE ON functions_name
  TO username[, username….]|PUBLIC;
Example
   GRANT EXECUTE ON emp_salary_increase TO PUBLIC;

DEBUG
Grants right to debug the functions belonging to the schema of the other user
Syntax
          GRANT DEBUG ON functions_name TO username[, username….]|PUBLIC;
Example
  GRANT DEBUG ON emp_salary_increase TO PUBLIC

Revoking Privileges on functions

To take back a privilege that is granted to a user
Syntax
   REVOKE EXECUTE/DEBUG ON functions_name FROM
   Username[,username…..] |PUBLIC;

Example
   REVOKE  EXECUTE ON emp_salary_increase FROM itp_jul_01, itp_jul_02;

Dropping FUNCTIONS 

To Delete a FUNCTION:


 DROP FUNCTION < Functionname >;
 e.g.
 DROP FUNCTION  chk_dept;

Local Procedures and Functions

The subprograms can also be defined within the declarative section of a block, such subprograms are known as Local Subprograms

Local subprograms can also be defined within an anonymous block

These subprograms follow the same scope and visibility rules as any other PL/SQL identifier

It is only visible in the block in which it is declared

No other block can call local subprograms, as they are not visible to any other block

Local Procedures and Functions within a Stored Subprogram


CREATE OR REPLACE PROCEDURE emp_pro
AS
   CURSOR c_allemp
   IS
      SELECT deptno, ename
        FROM emp;

   v_dname   dept.dname%TYPE;

   -- Local function ,local to the procedure which will return the dept name for an employee
   FUNCTION show_deptname (p_dno dept.deptno%TYPE)
      RETURN VARCHAR2
   IS
      v_dname   dept.dname%TYPE;
   BEGIN
      SELECT dname
        INTO v_dname
        FROM dept
       WHERE deptno = p_dno;

      RETURN v_dname;
   END show_deptname;
BEGIN
   FOR v_rec IN c_allemp
   LOOP
      v_dname := show_deptname (v_rec.deptno);
      DBMS_OUTPUT.put_line(v_rec.ename || ' belongs to      ' || v_dname);
   END LOOP;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line
                          ('Wrong department                          number');
END emp_pro;


Difference between Stand Alone and Local Subprograms



Difference between Stand Alone and Local Subprograms

Methods of Passing Parameters


 The various methods available for passing parameters to subprograms are:

Positional
          Pass actual parameters in the same order as formal parameters
Named
          Pass actual parameters in arbitrary order by associating each with its corresponding formal parameter using special syntax (=>)
Combination
          Pass some of the actual parameters as positional and some as named

  Note: While using combination the positional parameters   should be passed first

Methods of Passing Parameters: Example


CREATE TABLE employee AS SELECT ename, sal, comm
                                                              FROM emp;
TRUNCATE            TABLE employee;

CREATE OR REPLACE PROCEDURE add_employee (
   p_name   IN   emp.ename%TYPE DEFAULT 'unknown',
   p_sal    IN   emp.sal%TYPE DEFAULT 1000,
   p_comm   IN   emp.comm%TYPE DEFAULT 0
)
IS
BEGIN
   INSERT INTO employee
               (ename, sal, comm
               )
        VALUES (p_name, p_sal, p_comm
               );

   COMMIT;
ENDadd_employee;
/

Methods of Passing Parameters: Example

BEGIN
   add_employee ;
   add_employee ('SMITH', 2000,600);
   add_employee (p_sal=> 6000,p_comm=>200, p_name =>'STEVE');
   add_employee (p_sal =>4000) ;
   add_employee('MARK', p_sal=> 6000,p_comm=>200); 
END;
/
SELECT * FROM employee;
          
Note: All the positional parameters should precede the named parameters in a subprogram call.

Data Dictionary View

USER_SOURCE
Is used to obtain the text of a stored procedure or a stored function

USER_ERRORS
Is used to find out the compilation errors in the subprogram, currently getting compiled
One can use the SQL*Plus command SHOW ERRORS, instead of firing a SELECT query on USER_ERRORS

USER_OBJECTS
Is used to get the details of all the objects created in a particular schema

USER_PROCEDURES 
                   Is used to get the details of all procedures in that user’s schema

You May Like:

PLSQL Record
Continue reading >>