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

Description: The Oracle supplied package UTL_FILE can be used to read and write files that are located on the server. It cannot be used to access files locally, that is, on the computer where the client is running. OPEN a file FOR read operations UTL_FILE.fopen( file_location IN VARCHAR2, file_name IN VARCHAR2, open_mode IN VARCHAR2, max_linesize […]
Continue reading…

Enjoyed this post? Share it!

 
 

Update: Update A Partitioned Table

Description: Update records that exist only in a single partition. UPDATE <table_name> PARTITION (<partition_name>) SET <column_name> = <value> WHERE <column_name> <condition> <value>;     — The following w3mentor updates values in a single — partition of the read_messages table:   UPDATE read_messages PARTITION (user_2153) s SET s.msg_id = 1 WHERE read_date = ‘2009-04-01’;
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!