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!

 
 

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 File Write To File Example

Description: Example of using UTL_FILE to write to a flat (txt) file. CREATE DIRECTORY test_dir AS ‘c:\’; — CREATE DIRECTORY test_dir AS ‘/tmp’;   DECLARE fileHandler UTL_FILE.FILE_TYPE; BEGIN fileHandler := UTL_FILE.FOPEN(‘test_dir’, ‘test_file.txt’, ‘W’); UTL_FILE.PUTF(fileHandler, ‘Writing TO a file\n’); UTL_FILE.FCLOSE(fileHandler); EXCEPTION WHEN utl_file.invalid_path THEN raise_application_error(-20000, ‘ERROR: Invalid PATH FOR file.’); END; /
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!

 
 

Timestamp_To_Scn

Description: The TIMESTAMP_TO_SCN function takes as an argument a timestamp value and returns the approximate system change number (SCN) associated with that timestamp. The returned value is of datatype NUMBER. This function is useful any time you want to know the SCN associated with a particular timestamp. TIMESTAMP_TO_SCN(<timestamp>)     — The following w3mentor inserts […]
Continue reading…

Enjoyed this post? Share it!