Oracle SQL & PL/SQL

SQL Introduction

2025-02-05T20:54:00.000-08:00

SQL stands for Structured Query Language. and it is generally referred to as SEQUEL. SQL is simple language to learn. SQL is a Nonprocedural language, as compared to the procedural or third generation languages (3GLs) such as COBOL and C. SQL was developed by IBM in the 1970s.The American National Standards Institute (ANSI) published its first SQL standard in 1986 and a second widely adopted

Sitemap

2025-02-05T20:40:00.000-08:00

Sitemap - All posts on this blog 1. SQL Introduction 2. SQL Update 3. SQL Delete 4. SQL / PLSQL Interview Questions 5. PLSQL Cursors 6. PLSQL Triggers 7. SQL Joins 8. SQL Insert 9. SQL Syntax 10. PLSQL Collections 11. PLSQL Introduction 12. PLSQL Tables 13. Oracle PLSQL Varrays 14. Oracle PLSQL Nested Tables 15. Oracle PLSQL Tables 16. Oracle PLSQL Records 17. Oracle PLSQL Ref Cursors 18.

SQL Update

2024-12-26T10:05:00.000-08:00

SQL Update Update Statement is used to change existing values in a table or a view's base table.Example 1 :update emp set sal = sal*2.25 where deptno = 30Example 2 :update emp &

SQL Delete

2024-12-26T10:02:00.000-08:00

SQL Delete How to delete all records from a table?Delete from dept;How to delete specific records from a table?Delete from emp where empno=20;How to delete duplicate records from the table?Suppose we have a table t1(id integer, name varchar(10))select * from t1;

SQL PL/SQL Interview Questions

2024-12-26T07:52:00.000-08:00

SQL PL/SQL Interview Questions These questions will be updated and new questions will be added on regular basis, so stay tuned and subscribe to RSS Feed. What special operators does Oracle provide for

Oracle PL/SQL Cursors

2024-12-26T07:44:00.001-08:00

Oracle PL/SQL Cursors We can manipulate the information within a SQL statement by means of assigning a name to a "select statement", this concept is known a cursor. A cursor is used for processing individual rows returned as a result for a query. Oracle

PL/SQL Triggers

2024-12-26T07:44:00.000-08:00

PL/SQL Triggers A PL/SQL trigger is a construct in PL/SQL that runs or "triggered" on event of changes being made to a table in the database. The triggering event is a INSERT, UPDATE or DELETE done on a table. The trigger can be made so it can

SQL Joins

2024-12-26T07:39:00.000-08:00

SQL Joins You will get vast amount of data/ information on this page regarding Oracle SQL JOINS likeSQL Inner Join SQL Outer Join SQL Self Join SQL Cross Join SQL Equijoin Join using MULTIPLE TABLES

SQL Insert

2024-12-26T06:58:00.000-08:00

Inserting a new row into the dept table insert into dept (deptno, dname, loc)values (10,'Apple','Boston') You can omit the column list in the insert statement but then you have to enter those in the same order as they appear in the table and you have to include all the columns in the values. The

SQL Syntax

2024-12-26T03:45:00.000-08:00

SQL Syntax On this page, you will find SQL syntax for some of the most important SQL commands. These below SQL Syntaxes will be suitable for quick reference.SELECT [hint][DISTINCT] select_listFROM table_list[WHERE conditions][GROUP BY group_by_list][HAVING search_conditions][ORDER BY order_list [

PL/SQL Collections

2024-12-26T03:15:00.000-08:00

PL/SQL Collections Records Table Based Records Cursor Based Records Programmer-defined Records PL/SQL Tables Varrays Nested Tables

PL/SQL Introduction

2024-12-26T02:41:00.000-08:00

PL/SQL Introduction PL/SQL stands for Procedural Language/SQL. PL/SQL is Oracle's Procedural Language extension to SQL. PL/SQL expands SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL. Basically it runs on the

PL/SQL Tables

2024-12-25T23:05:00.000-08:00

PL/SQL Tables Nested tablesVarraysIndex-by-tablesDeclare Cursor name_cur IS Select last_name From student Where rownum <= 10; Type last_name_type Is Table Of student.last_name%Type; last_name_tablast_name_type :=last_name_type();v_counter INTEGER := 0;BEGIN FOR

Oracle PL/SQL Varrays

2024-12-25T09:59:00.001-08:00

Oracle PL/SQL Varrays Varray stands for variable-size array. Varray can be stored in the columns of your tables. When you create varrays you must provide the maximum size for them. They retain their ordering and subscripts when stored in and retrieved from a database table. They

Oracle PL/SQL Nested Tables

2024-12-25T09:59:00.000-08:00

Oracle PL/SQL Nested Tables Nested tables are similar to index by table but these can be stored in database columns but index by tables cannot be stored in database columns. A Nested tablecan be considered as a single-column table that can either be in memory,

Oracle PL/SQL Tables

2024-12-25T09:58:00.000-08:00

PL/SQL tables PL/SQL has two composite datatypes: TABLE and RECORD. Objects of type TABLE are known as PL/SQL tables. PL/SQL tables use a primary key to give you array-like access to rows. The number of rows in a PL/SQL table can increase

Oracle PL/SQL Records

2024-12-25T09:57:00.000-08:00

PL/SQL Records A PL/SQL record is a collection of basic types of data and can be accessed as a single unit. PL/SQL records are similar in structure to a row in a database table. A record consists of components of any

Oracle PL/SQL REF Cursors

2024-12-24T23:19:00.002-08:00

REF Cursors A REF CURSOR or cursor variable is just a reference or a handle to a static cursor. It allows a user to pass this "reference to the same cursor" among all the programs that need access to the cursor.

Oracle PL/SQL WHERE CURRENT OF & FOR UPDATE

2024-12-24T23:19:00.001-08:00

WHERE CURRENT OF & FOR UPDATE The WHERE CURRENT OF clause is used in some UPDATE and DELETE statements. The WHERE CURRENT OF clause in an UPDATE or DELETE statement states that the most recent row fetched from the table should be updated or deleted.

Examples of Oracle PL/SQL Cursors

2024-12-24T23:19:00.000-08:00

Examples of Oracle PL/SQL Cursors: 1) Retrieving the records from the emp table and displaying them one by one using cursors: CREATE OR REPLACE PROCEDURE my_proc IS var_empno emp.empno%type; var_ename emp.ename%type; var_sal emp.sal

Oracle PL/SQL Explicit Cursors

2024-12-24T23:18:00.002-08:00

Explicit Cursors Programmers create explicit cursors, and with these you can do operations on a set of rows, which can be processed one by one. You use explicit cursors when you are sure that the SQL statement will return more than

Oracle PL/SQL Cursor For Loops

2024-12-24T23:18:00.001-08:00

Cursor For Loop The following procedure is followed in most of the situations in PL/SQL: Open a cursor Start a loop Fetch the cursor Check whether rows are returned Process Close the loop Close the cursor Cursor For Loop allows us to simplify this

Oracle PL/SQL Cursors With Parameters

2024-12-24T23:18:00.000-08:00

Cursors With Parameters We can pass parameters into a cursor and use them in the query. We can only pass values to the cursor; and cannot pass values out of the cursor through parameters. Only the datatype of the parameter is

Oracle PL/SQL Implicit Cursors

2024-12-24T23:17:00.000-08:00

Implicit cursors Implicit cursors are automatically created and used by Oracle every time you issue a Select statement in PL/SQL. If you use an implicit cursor, Oracle will perform the open, fetches, and close for you automatically. Implicit cursors are

SQL Group By Examples

2024-12-24T10:14:00.000-08:00

SQL Group By Examples Example 1 of SQL Group ByLet us say we have a table name Orders.Orders (O_Id, OrderDate, OrderPrice, Customer)we want to find the total sum (total order) of each customer.SELECT Customer,SUM(OrderPrice)FROM OrdersGROUP BY CustomerSource: