Create a cursor in SQL

A cursor can be created using the DECLARE statement, most commonly with the name of the cursor and takes a SELECT statement and optional WHERE and other clauses. Below is an example of creating a cursor in DB2, MariaDB, MySQL, and SQL Server: DECLARE MyUserCursor CURSOR FOR SELECT * FROM Users WHERE useraddress IS NULL […]
Continue reading…

Enjoyed this post? Share it!

 
 

XMLELEMENT function in PL/SQL

The XMLELEMENT function allows developers to read values from Oracle tables and present them in XML format along with wrapping the values in descriptive XML tags. The general format for XMLELEMENT is: SELECT XMLELEMENT("tag_name", TABLE.field_name1), XMLELEMENT("tag_name", TABLE.field_name2) FROM TableName; The XMLELEMENT function creates an XML element in XMLType by taking the tag_name and the information […]
Continue reading…

Enjoyed this post? Share it!

 
 

XMLFOREST function in PL/SQL

The XMLFOREST function allows you to read values from Oracle tables and present them in XML format. The general format for the XMLFOREST function is: SELECT XMLFOREST(TABLE.field_name1, TABLE.field_name2, …) "alias" FROM TableName; This function converts each of its argument parameters (field names from tables) to XML and returns an XML fragment that is a concatenation […]
Continue reading…

Enjoyed this post? Share it!

 
 

View

Description: A view is a SQL statement stored in the database in the system tablespace. Data for a view is built in a table created by the database engine in the TEMP tablespace. CREATE OR REPLACE VIEW test_v AS SELECT owner, table_name FROM all_tab_columns;   — any valid sql statement can follow the create or […]
Continue reading…

Enjoyed this post? Share it!

 
 

Where Clause – Joins

Description: The WHERE Clause servers two different purposes. One is to join two or more tables, view, or materialized views for DML. SELECT p.last_name, t.title_name FROM person p, title t WHERE p.title_1 = t.title_abbrev;   SELECT p.last_name, r.role_name FROM person p, person_role_ie i, person_role r WHERE p.person_id = i.person_id AND i.role_id = r.role_id;   Left […]
Continue reading…

Enjoyed this post? Share it!

 
 

While Loop Example

Description: Basic WHILE loop skeleton code. DECLARE salary emp.sal%TYPE; mgr_num emp.mgr%TYPE; last_name emp.ename%TYPE; starting_empno CONSTANT NUMBER(4) := 7902; BEGIN SELECT sal, mgr INTO salary, mgr_num FROM emp WHERE empno = starting_empno; WHILE salary < 4000 LOOP SELECT sal, mgr, ename INTO salary, mgr_num, last_name FROM emp WHERE empno = mgr_num; END LOOP;   INSERT INTO […]
Continue reading…

Enjoyed this post? Share it!

 
 

With Clause: Double Alias

Description: The WITH query_name clause provides a way assign a name to a subquery block. The subquery block can then be referenced in multiple places in the query by specifying the query name. The query is optimized by treating it as either an inline view or as a temporary table. WITH <alias_one> AS (subquery_sql_statement) <alias_two> […]
Continue reading…

Enjoyed this post? Share it!

 
 

With Clause: Single Alias

Description: The WITH query_name clause provides a way assign a name to a subquery block. The subquery block can then be referenced in multiple places in the query by specifying the query name. The query is optimized by treating it as either an inline view or as a temporary table. WITH <alias_name> AS (subquery_sql_statement) SELECT […]
Continue reading…

Enjoyed this post? Share it!

 
 

Utl_File Import Data

Description: This procedure makes use of the built-in Oracle package “UTL_FILE” and its ability to import data. PROCEDURE load_import (file_name IN VARCHAR2) IS vSFile UTL_FILE.FILE_TYPE; vNewLine VARCHAR2(2000); BEGIN vSFile := UTL_FILE.FOPEN (’c:\temp\’,file_name,’r’); IF UTL_FILE.IS_OPEN(vSFile) THEN LOOP BEGIN utl_file.get_line (vSFile, vNewLine); IF vNewLine IS NULL THEN EXIT; END IF; INSERT INTO test (fld1, fld2) VALUES (vNewLine, […]
Continue reading…

Enjoyed this post? Share it!

 
 

Verify Password

Description: A function useful in password verification and maintenance. CREATE OR REPLACE FUNCTION verify_pwd ( username VARCHAR2, password VARCHAR2, old_password VARCHAR2) RETURN BOOLEAN IS   chararray VARCHAR2(52); differ INTEGER; digarray VARCHAR2(20); ischar BOOLEAN; isdigit BOOLEAN; ispunct BOOLEAN; m INTEGER; n BOOLEAN; punctarray VARCHAR2(25);   BEGIN digitarray := ‘0123456789’; chararray := ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ’; punctarray := ‘!"#$%&()”*+,-/:;<=>?_’;   […]
Continue reading…

Enjoyed this post? Share it!

 
 

Update: Update Based On A Record

Description: Update a table based on the value or presence of a record. UPDATE <table_name> SET ROW = <record_name> WHERE <column_name> <condition> <value>;   CREATE TABLE my_table AS SELECT table_name, tablespace_name FROM all_tables;   SELECT DISTINCT tablespace_name FROM my_table;   DECLARE trec my_table%ROWTYPE; BEGIN trec.table_name := ‘DUAL’; trec.tablespace_name := ‘NEW_TBSP’;   UPDATE my_table SET ROW […]
Continue reading…

Enjoyed this post? Share it!

 
 

Subst And Instr Together

Description: Using SUBST and INSTR together allow you to do complex extractions of characters from strings. — String parsing using both SUBSTR And INSTR     – get ALL characters up TO the FIRST comma SELECT SUBSTR(’abc,def,ghi’, 1 ,INSTR(’abc,def,ghi’, ‘,’, 1, 1)-1) FROM dual;     — get the character(s) in between the commas SELECT […]
Continue reading…

Enjoyed this post? Share it!