Showing posts sorted by relevance for query plsql. Sort by date Show all posts

Wednesday, July 20, 2022

String Functions with example in SQL ,PLSQL

-: String functions in plsql :--

PL/SQL offers the concatenation operator (||) for joining two strings. The following table provides the string functions provided by PL/SQL .

S.No Function & Purpose
1 ASCII(x);Returns the ASCII value of the character x.

2 CHR(x); Returns the character with the ASCII value of x.

3 CONCAT(x, y);Concatenates the strings x and y and returns the appended string.

4 INITCAP(x);Converts the initial letter of each word in x to uppercase and returns that string.

5 INSTR(x, find_string [, start] [, occurrence]);Searches for find_string in x and returns the position at which it occurs.

7 LENGTH(x);Returns the number of characters in x.

8 LENGTHB(x);Returns the length of a character string in bytes for single byte character set.

9 LOWER(x);Converts the letters in x to lowercase and returns that string.

10 LPAD(x, width [, pad_string]) ;Pads x with spaces to the left, to bring the total length of the string up to width characters.

11 LTRIM(x [, trim_string]);Trims characters from the left of x.

17 NVL(x, value);Returns value if x is null; otherwise, x is returned.

19 REPLACE(x, search_string, replace_string);Searches x for search_string and replaces it with replace_string.

20 RPAD(x, width [, pad_string]);Pads x to the right.

21 RTRIM(x [, trim_string]);Trims x from the right.

22 SOUNDEX(x) ;Returns a string containing the phonetic representation of x.

25 TRIM([trim_char FROM) x);Trims characters from the left and right of x.

26 UPPER(x);Converts the letters in x to uppercase and returns that string.

e.g. 1

set serveroutput on;

DECLARE

v_greet varchar2(100) := 'Good Morning !!!';

BEGIN

dbms_output.put_line(UPPER(v_greet));

dbms_output.put_line(LOWER(v_greet));

dbms_output.put_line(INITCAP(v_greet));

/* retrieve the first character in the string */

dbms_output.put_line ( SUBSTR (v_greet, 1, 1));

/* retrieve the last character in the string */

dbms_output.put_line ( SUBSTR (v_greet, -1, 1));

/* retrieve five characters,

starting from the seventh position. */

dbms_output.put_line ( SUBSTR (v_greet, 7, 5));

/* retrieve the remainder of the string,

starting from the second position. */

dbms_output.put_line ( SUBSTR (v_greet, 2));

/* find the location of the first "e" */

dbms_output.put_line ( INSTR (v_greet, 'e'));

END;

E.g 2 :

DECLARE

VGREET varchar2(30) := '......Hello World.....';

BEGIN

dbms_output.put_line(RTRIM(VGREET,'.'));

dbms_output.put_line(LTRIM(VGREET, '.'));

dbms_output.put_line(TRIM( '.'from VGREET));

END;

Saturday, July 23, 2022

Collections in PLSQL with Sample Examples

-------------------COLLECTIONS>>

-------------------1. VARRAYS.-----------

DECLARE

TYPE VARRY_ENAME IS VARRAY(5) OF VARCHAR2(100);

TYPE VARRAY_EID IS VARRAY(5) OF INTEGER;

VAR1_ENAME VARRY_ENAME;

VAR2_EID VARRAY_EID;

V_TOTAL INTEGER ;

BEGIN

VAR1_ENAME := VARRY_ENAME('AZEEZ','JUNAED', 'RASHID', 'REHAN');--GROUP OF ELEMENTS OF SIMILAR DATATYPE

VAR2_EID := VARRAY_EID (1, 2,3,4);

V_TOTAL := VAR1_ENAME.COUNT;--4

FOR I IN 1 ..V_TOTAL LOOP --4

DBMS_OUTPUT.PUT_LINE ( 'EMPLOYEE NAME : '|| VAR1_ENAME(I) || ' EMPLOYEE ID IS : '|| VAR2_EID(I));

END LOOP;

END;

--ASSOCIATIVE ARRAY/INDEX BY TABLE

A PL/SQL table is very similar to an array in C or Pascal. Like a record, the PL/SQL table must be declared first as a type declaration and then as a variable of the user-defined type, as shown below.

DECLARE

   TYPE Student_SSN_tabtype IS TABLE OF

       integer (9)

       INDEX BY binary_integer;

   Student_SSN_table      Student_SSN_tabtype;

Like records, the PL/SQL table is a composite datatype. The number of rows that can be held in a PL/SQL table is limited only by the range of values for the INDEX variable. The PL/SQL table is indexed using a signed integer and can be navigated either forward or backward (unlike cursors, which can only be moved forward).

As of Oracle9i PL/SQL tables were officially named ASSOCIATIVE ARRAYS. Most developers call associative arrays PL/SQL tables because they can not exist in the database, only in PL/SQL memory structures. The advantage over nested tables and VARRAYs is that a PL/SQL table does not have to be extended to add elements, nor does it have to be initialized. Best of all, elements are added in any order, in any position in the table.

PL/SQL tables before Oracle9i could only be indexed by BINARY_INTEGER, but from Oracle9i and beyond they can be indexed either by BINARY_INTEGER or a string type. You can conceptualize a PL/SQL table as a two-column table, the first being the index and the second being the data element. Like the other collection types, the index value is used to locate the data element.

In the example below, a PL/SQL table is defined and a cursor is used to load the collection and then read the elements out of the collection:

--CREATE OR REPLACE TYPE V_IDXTL_ENAME IS TABLE OF VARCHAR2 INDEX BY VARCHAR2 (10);

DECLARE

TYPE V_IDXTL_SAL IS TABLE OF NUMBER INDEX BY VARCHAR2(10);

SALARY_LIST V_IDXTL_SAL;

NAME VARCHAR2(100);

BEGIN

SALARY_LIST('AJAY') :=1000;

SALARY_LIST('SALMAN') :=2000;

SALARY_LIST('VIJAY') :=3000;

SALARY_LIST('SACHIN') :=4000;

--PRINT

NAME :=SALARY_LIST.FIRST;

WHILE SALARY_LIST IS NOT NULL

LOOP

DBMS_OUTPUT.PUT_LINE ('SALARY OF ' ||NAME ||' IS :'|| SALARY_LIST(NAME) );

NAME := SALARY_LIST.NEXT(NAME);

END LOOP;

END;

--------------------------------------------------------NESTED TABLES------------------------------

TYPE type_name IS TABLE OF element_type [NOT NULL]; --NESTED TABLE

--NESTED TABLES ARE SIMILAR TO VARRAYS IN FUNCTIONALITY EXCEPT IT IS UNBOUNDED,WHEREAS VARRAYS ARE BOUNDED

--NESTED TABLES ARE SIMILAR TO INDEX BY TABLES IN SYNTAX WISE EXCEPT THE "INDEX BY CLAUSE" WHICH IS PRESENT IN INDEX BY TABLE DEFINITION.

DECLARE

--TYPE V_IDXTL_SAL IS TABLE OF NUMBER INDEX BY VARCHAR2(10); --INDEX BY TABLE

TYPE sname_table IS TABLE OF VARCHAR2(10); --NESTED TABLE

TYPE marks_table IS TABLE OF INTEGER;

names sname_table;

marks marks_table;

total integer;

BEGIN

names := sname_table('Akshay', 'Rohit', 'Sachin', 'virat', 'siraj');

marks:= marks_table(100, 88, 87, 90, 92);

total := names.count;

dbms_output.put_line('Total '|| total || ' Students');

FOR i IN 1 .. total LOOP

dbms_output.put_line('Student:'||names(i)||' :: Marks:' || marks(i));

end loop;

END;

--------------------------------------------------------------------------------------

DECLARE

CURSOR c_customers is SELECT Ename,DNAME FROM EMPL E, DEP D WHERE E.DEPNP=D.DEPNO;

TYPE c_list IS TABLE of EMPL.ENAME%type; --NESTED TABLE

name_list c_list := c_list();

counter integer :=0;

BEGIN

FOR n IN c_customers LOOP

counter := counter +1;

name_list.extend;

name_list(counter) := n.ENAME;

dbms_output.put_line('Customer('||counter||'):'||name_list(counter));

END LOOP;

END;

Wednesday, May 11, 2022

Cuesors and Types of Cursors

CURSORS IN PLSQL WITH SIMPLE EXAMPLES

-----------------------------CURSORS--------------------------

CURSOR :

-->> WHENEVER A SQL AUERY WHICH RETRIVES MORE THAN ONE ROW , TO PROCESS THOSE RECORDS/ROWS WE USE CURSORS.

>>CONTEXT AREA OF MEMORY WHERE QUERY RECORDS ARE SAVED.

-----------

DECLARE

V_EMPNO EMPL.EMPNO%TYPE;

V_SAL EMPL.SAL%TYPE;

V_HIREDATE EMPL.HIREDATE%TYPE;

BEGIN

SELECT EMPNO,SAL,HIREDATE

INTO V_EMPNO,V_SAL,V_HIREDATE --ONLY ONE ROW

FROM EMPL

WHERE ROWNUM<10;

DBMS_OUTPUT.PUT_LINE('ID OF EMPLOYE IS :'||V_EMPNO);

DBMS_OUTPUT.PUT_LINE('SALARY OF EMPLOYE IS :'||V_SAL);

DBMS_OUTPUT.PUT_LINE('HIREDATE OF EMPLOYE IS :'||V_HIREDATE);

END;

-----------------------

--TYPES OF CUSRSORS

1. IMPLICIT CURSORS: >> SYSTEM DEFINED

2. EXPLICIT CURSORS >> USER DEFINED

-------------------

DECLARE

CURSOR C IS SELECT * FROM EMPL;

BEGIN

FOR I IN c

LOOP

dbms_output.put_line('EMPLOYEE NAME IS : '||i.ename || ' ::EMPLOYEE ID IS :'|| I.EMPNO ||' ::SALARY IS : '||I.SAL);

END LOOP;

END;

-------------------------------

DECLARE

CURSOR EMP_CUR IS SELECT ENAME, EMPNO FROM EMPL; --DECLARE

V_CUR EMP_CUR%ROWTYPE;

BEGIN

OPEN EMP_CUR; --2. OPEN THE CURSORS

FETCH EMP_CUR INTO V_CUR;

LOOP

EXIT WHEN EMP_CUR%NOTFOUND

DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME IS : ' || V_CUR.ENAME || ':: EMP ID IS : '||V_CUR.EMPNO );

END LOOP;

END;

DECLARE

CURSOR c IS SELECT ename FROM empl; --DECLARE

vname c%rowtype;

BEGIN

OPEN c; --2. OPEN THE CURSORS

loop

fetch c INTO vname; -- FETCH CURSOR

exit WHEN c%notfound;

dbms_output.put_line(vname.ename);

END loop;

CLOSE C; --4. CLOSE CURSOR

END;

--CURSOR FOR LOOPS:

--OPEN , FETCH AND CLOSE THE CURSR INTERNALLY...

--WE NEED NOT INITIALIZE VARIABLE AS WELL.

--WAY001

DECLARE

CURSOR c

SELECT ename FROM empl;

Vname c%rowtype;

BEGIN

OPEN c;

LOOP

FETCH c INTO vname;

EXIT

WHEN c%notfound;

Dbms_output.put_line(vname.ename);

END LOOP;

END;

--WAY _002

DECLARE

CURSOR c IS SELECT ename FROM empl; --DECLARE

BEGIN

FOR I IN C LOOP

DBMS_OUTPUT.PUT_LINE('NAME OF EMPLOYEE IS :'||I.ENAME);

END LOOP;

END;

--WAY _003

BEGIN

FOR I IN ( SELECT ename FROM empl) LOOP

DBMS_OUTPUT.PUT_LINE('NAME OF EMPLOYEE IS :'||I.ENAME);

END LOOP;

END;

--WAY001

DECLARE

CURSOR c

SELECT ename FROM empl;

Vname c%rowtype;

BEGIN

OPEN c;

LOOP

FETCH c INTO vname;

EXIT

WHEN c%notfound;

Dbms_output.put_line(vname.ename);

END LOOP;

END;

----------------------------------------------PARAMETERISED CURSORS:-------------------------------

A CURSOR WHICH CAN ACCEPT THE PARAMETER IS CALLED AS PARAMETERISED CURSOR.

E.G

DECLARE

CURSOR c (X NUMBER,V_ENAME VARCHAR2) IS SELECT HIREDATE,SAL FROM empl WHERE EMPNO = X AND ENAME = V_ENAME; --DECLARE

VAR_1 NUMBER;

VAR_2 VARCHAR2(100);

BEGIN

VAR_1 := &a;

VAR_2 := &B;

FOR I IN C (VAR_1 , VAR_2) LOOP

DBMS_OUTPUT.PUT_LINE('HIREDATE OF EMPLOYEE IS :'||I.HIREDATE);

DBMS_OUTPUT.PUT_LINE('SALARY OF EMPLOYEE IS :'||I.SAL);

END LOOP;

END;

Sunday, September 1, 2019

Sample format to include all in parameter list

📌 Sample Format to Include All `IN` Parameters in PL/SQL Procedures

When designing PL/SQL procedures in Oracle, you often encounter scenarios where multiple inputs are needed. Organizing all IN parameters in a consistent and scalable format not only enhances readability but also improves maintainability. This guide provides a clean and reusable sample format for writing PL/SQL procedures with multiple IN parameters.

Example format for handling IN parameters in PL/SQL

🧾 Sample Procedure Format with All IN Parameters

CREATE OR REPLACE PROCEDURE insert_employee_info (
    p_emp_id       IN NUMBER,
    p_first_name   IN VARCHAR2,
    p_last_name    IN VARCHAR2,
    p_email        IN VARCHAR2,
    p_salary       IN NUMBER,
    p_hire_date    IN DATE,
    p_department   IN VARCHAR2
)
IS
BEGIN
    INSERT INTO employees (
        emp_id, first_name, last_name, email,
        salary, hire_date, department
    )
    VALUES (
        p_emp_id, p_first_name, p_last_name, p_email,
        p_salary, p_hire_date, p_department
    );

    DBMS_OUTPUT.PUT_LINE('Employee record inserted successfully.');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END insert_employee_info;

🧠 Why Use This Format?

Clarity: Clean separation between parameter names and logic.
Scalability: Easy to extend when more parameters are added.
Standardization: Follows Oracle best practices for readable procedures.
Debug-Friendly: Includes DBMS_OUTPUT for status and exception handling.

💻 How to Execute the Procedure

EXEC insert_employee_info (
    101, 'John', 'Doe', 'john.doe@example.com',
    55000, SYSDATE, 'HR'
);

💡 Tip: Use meaningful prefixes like p_ for parameters to differentiate them from column names or variables.

❗ Common Mistakes to Avoid

Omitting `IN` keyword (required in explicit mode).
Using undeclared variables or mismatched data types.
Missing exception block for error handling.
Using same name for parameter and table column (leads to confusion).

📚 Related Resources

📌 Conclusion

Following a structured and readable format for your IN parameters can make your PL/SQL code easier to maintain and debug. As your applications grow in complexity, these best practices become even more important.

Happy coding with PL/SQL!

Saturday, July 23, 2022

SQL And PLSQL OVERVIEW

Here is the **HTML version** of your formatted “SQL & PL/SQL Overview” document. It includes structured sections, syntax-highlighted code blocks, and well-organized content. --- ### ✅ \[Downloadable HTML File Option] If you'd like, I can also give you a `.html` file to upload directly to your Blogspot or view offline. Just let me know. --- ### 📄 Full HTML Code: ```html SQL & PL/SQL Overview

SQL & PL/SQL Overview

🔖 Audience

This tutorial is for software professionals who want to learn PL/SQL in a simple and practical way. After finishing this, you'll understand key PL/SQL concepts and be ready to advance further in database development.

✉️ Prerequisites

What a database is
Programming fundamentals
SQL basics

Tip: Knowledge of any programming language is helpful.

What is a Database?

There are two main types of databases:

Relational Databases (RDBMS)
- Examples: Oracle DB, MySQL, SQL Server, IBM DB2
Non-Relational Databases (NoSQL)
- Examples: MongoDB, Cassandra, Apache HBase

Oracle DB Versions

Common versions: 8i, 10g, 12c

Key Features

Manageability
High Availability
Performance
Security (Roles and Grants)

SQL (Structured Query Language)

Pronounced as "SEQUEL"
Used for accessing RDBMS
Set-oriented: handles multiple records at once
Case-insensitive
Does not support control structures
Can be embedded in other languages

📒 SQL Command Types

1. DDL (Data Definition Language)

Used to define and manage database objects like tables and views.

CREATE: Create database objects
ALTER: Modify existing objects
DROP: Delete objects
TRUNCATE: Remove all rows quickly
COMMENT: Add descriptive notes

CREATE TABLE NEWTAB (SNO NUMBER(5), SNAME VARCHAR2(10));
ALTER TABLE NEWTAB ADD (ID NUMBER(5));
DROP TABLE NEWTAB;

2. Flashback & Recycle Bin

FLASHBACK TABLE: Restore dropped table
PURGE: Permanently delete from recycle bin

SQL Data Types

Data Type	Description
`NUMBER`	Stores numeric values (up to 38 digits)
`CHAR`	Fixed-length strings (up to 2000 bytes)
`VARCHAR2`	Variable-length strings (up to 4000 bytes)
`DATE`	Stores date/time values
`TIMESTAMP`	Includes fractional seconds
`TIMESTAMP WITH TIMEZONE`	Stores time zone info
`LONG, RAW`	Stores large or binary data
`CLOB, BLOB, BFILE`	Stores text, images, or external files
`ROWID`	Stores physical row address
`NCLOB`	Supports multilingual data

PL/SQL Overview

PL/SQL is Oracle's procedural extension to SQL that allows for programming logic and control structures.

Key Features

Modular programming
Supports loops, IF statements
Error handling with EXCEPTION block
Tightly integrated with SQL

Block Structure

DECLARE
BEGIN
  -- code
EXCEPTION
END;

Examples

Print Message:

BEGIN
  DBMS_OUTPUT.PUT_LINE('Welcome to PL/SQL');
END;

Using Constants:

DECLARE
  PI CONSTANT NUMBER(5,3) := 3.142;
  radius NUMBER := 5;
  area NUMBER;
BEGIN
  area := PI * radius * radius;
  DBMS_OUTPUT.PUT_LINE('Area: ' || area);
END;

Bind Variables

Allow runtime input for flexible programs.

DECLARE
  A NUMBER := &n;
  B NUMBER := &m;
BEGIN
  DBMS_OUTPUT.PUT_LINE(A + B);
END;

%TYPE and %ROWTYPE

%TYPE: Assigns data type from table column to variable.

vname emp.ename%TYPE;

%ROWTYPE: Assigns all column types from a table.

vrow emp%ROWTYPE;

Nested Blocks

BEGIN
  DECLARE
    v_inner NUMBER := 10;
  BEGIN
    DBMS_OUTPUT.PUT_LINE(v_inner);
  END;
END;

Lexical Units in PL/SQL

Identifiers: Names of variables, tables, etc.
Reserved Words: BEGIN, END, etc.
Delimiters: Special characters like `;`
Literals: Fixed values like 'Hello', 123
Comments: -- or /* */

``` --- Would you like this in a downloadable `.html` file? I can provide it directly.

Wednesday, July 20, 2022

String Functions with example in SQL ,PLSQL

-: String functions in plsql :--

Saturday, July 23, 2022

Collections in PLSQL with Sample Examples

Collections in PLSQL with Sample Examples

Wednesday, May 11, 2022

Cuesors and Types of Cursors

CURSORS IN PLSQL WITH SIMPLE EXAMPLES

Sunday, September 1, 2019

Sample format to include all in parameter list

📌 Sample Format to Include All IN Parameters in PL/SQL Procedures

🧾 Sample Procedure Format with All IN Parameters

🧠 Why Use This Format?

💻 How to Execute the Procedure

❗ Common Mistakes to Avoid

📚 Related Resources

📌 Conclusion

Saturday, July 23, 2022

SQL And PLSQL OVERVIEW

SQL & PL/SQL Overview

🔖 Audience

✉️ Prerequisites

What is a Database?

Oracle DB Versions

Key Features

SQL (Structured Query Language)

📒 SQL Command Types

1. DDL (Data Definition Language)

2. Flashback & Recycle Bin

SQL Data Types

PL/SQL Overview

Key Features

Block Structure

Examples

Bind Variables

%TYPE and %ROWTYPE

Nested Blocks

Lexical Units in PL/SQL

query to get Shipment number based on Order number

Popular Posts

📌 Sample Format to Include All `IN` Parameters in PL/SQL Procedures