Skip to content

Instantly share code, notes, and snippets.

@composite

composite/delete.sql

Last active August 29, 2015 13:57
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save composite/9595189 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save composite/9595189 to your computer and use it in GitHub Desktop.
Download ZIP
Table CRUD Generator query for Oracle (limited current login schema due to infomation schema table.)
Raw
delete.sql
WITH TABLE_NM AS
( SELECT 'TABLE_NAME' as TABLE_NM
,'/* ' as desc01
,' */' as desc02
FROM DUAL)
SELECT col_comments
FROM (
SELECT -1 AS COLUMN_ID,
'DELETE FROM ' || TABLE_NM AS col_comments
FROM DUAL, TABLE_NM
UNION ALL -- Normally All PK tables will listed in where cause.
SELECT A.position + 2000 AS COLUMN_ID,
DECODE(A.position,1,'WHERE ', 'AND ') || A.COLUMN_NAME || lpad(' ',30 - LENGTHB(A.COLUMN_NAME)) ||'= #{'
|| lower(SUBSTR(A.COLUMN_NAME, 1,1))||SUBSTR(replace(INITCAP(lower(A.COLUMN_NAME)), '_',''), 2) || '}'
|| lpad(' ', 25 - LENGTHB(LOWER(a.column_name) ))
|| desc01 || (SELECT comments FROM user_col_comments WHERE table_name = a.table_name AND column_name = a.column_name)
|| decode(a.position, null, '', '(PK' || a.position||')') || desc02
FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B, TABLE_NM
WHERE A.TABLE_NAME = B.TABLE_NAME
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND B.CONSTRAINT_TYPE = 'P'
AND A.TABLE_NAME = TABLE_NM
)
ORDER BY COLUMN_ID;
Raw
delete_ide_friendly.sql
WITH TABLE_NM AS
( SELECT UPPER(:A_TBL_NAME) as TABLE_NM
,'-- ' as desc01
,' ' as desc02
FROM DUAL)
SELECT col_comments
FROM (
SELECT -1 AS COLUMN_ID,
'DELETE FROM ' || TABLE_NM AS col_comments
FROM DUAL, TABLE_NM
UNION ALL -- Normally All PK tables will listed in where cause.
SELECT A.position + 2000 AS COLUMN_ID,
DECODE(A.position,1,'WHERE ', 'AND ') || A.COLUMN_NAME || lpad(' ',30 - LENGTHB(A.COLUMN_NAME)) ||'= P_'
|| A.COLUMN_NAME
|| lpad(' ', 25 - LENGTHB(LOWER(a.column_name) ))
|| desc01 || (SELECT comments FROM user_col_comments WHERE table_name = a.table_name AND column_name = a.column_name)
|| decode(a.position, null, '', '(PK' || a.position||')') || desc02
FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B, TABLE_NM
WHERE A.TABLE_NAME = B.TABLE_NAME
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND B.CONSTRAINT_TYPE = 'P'
AND A.TABLE_NAME = TABLE_NM
)
ORDER BY COLUMN_ID;
Raw
insert.sql
WITH TABLE_NM AS
( SELECT 'TABLE_NAME' as TABLE_NM
,'/* ' as desc01
,' */' as desc02
FROM DUAL)
SELECT COLUMN_NM
FROM (
SELECT -10 AS COLUMN_ID
,'INSERT INTO ' || TABLE_NM AS COLUMN_NM
FROM DUAL, TABLE_NM
UNION ALL
SELECT A.COLUMN_ID,
DECODE(A.COLUMN_ID,1,' ( ' ||A.COLUMN_NAME,' , ' || A.COLUMN_NAME) ||
lpad(' ', 26 - LENGTHB(DECODE(A.COLUMN_ID,1,' ' ||A.COLUMN_NAME,', ' || A.COLUMN_NAME))) || desc01 ||
(SELECT comments FROM user_col_comments WHERE table_name = a.table_name AND column_name = a.column_name) ||
decode(b.position, null, '', '(PK' || b.position||')') || desc02
as col_comments
FROM USER_TAB_COLUMNS A, TABLE_NM,
(SELECT A.TABLE_NAME, A.COLUMN_NAME, A.CONSTRAINT_NAME, A.POSITION
FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B
WHERE A.TABLE_NAME = B.TABLE_NAME
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND B.CONSTRAINT_TYPE = 'P') B
WHERE A.TABLE_NAME = B.TABLE_NAME(+)
AND A.COLUMN_NAME = B.COLUMN_NAME(+)
AND A.TABLE_NAME = TABLE_NM
-- AND A.COLUMN_NAME NOT IN('UPDTR_ID','UPDT_DT','UPDT_IP')
UNION ALL
SELECT 1000,
' ) VALUES ('
FROM DUAL
UNION ALL
SELECT A.COLUMN_ID + 1000,
DECODE(A.COLUMN_ID,1,' ',' ,')
|| CASE WHEN a.column_name = 'RGST_DT' THEN ' SYSTIMESTAMP' -- Inserted date column here.
WHEN a.column_name = 'RGSTR_ID' THEN ' #' || '%user_id' || '#' -- Inserted ID column here.
WHEN a.column_name = 'UPDT_DT' THEN ' SYSTIMESTAMP' -- Updated date column here.
WHEN a.column_name = 'UPDTR_ID' THEN ' #' || '%user_id' || '#' -- Updated ID column here.
ELSE ' #{' || lower(SUBSTR(A.COLUMN_NAME, 1,1))||SUBSTR(replace(INITCAP(lower(A.COLUMN_NAME)), '_',''), 2) || '}' END
|| lpad(' ', 26 - LENGTHB(CASE WHEN a.column_name = 'RGST_DT' THEN 'SAAAA'
ELSE LOWER(a.column_name) END))
|| desc01 || (SELECT comments FROM user_col_comments WHERE table_name = a.table_name AND column_name = a.column_name) ||
decode(b.position, null, '', '(PK' || b.position||')') || desc02
as col_comments
FROM USER_TAB_COLUMNS A, TABLE_NM,
(SELECT A.TABLE_NAME, A.COLUMN_NAME, A.CONSTRAINT_NAME, A.POSITION
FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B
WHERE A.TABLE_NAME = B.TABLE_NAME
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND B.CONSTRAINT_TYPE = 'P') B
WHERE A.TABLE_NAME = B.TABLE_NAME(+)
AND A.COLUMN_NAME = B.COLUMN_NAME(+)
AND A.TABLE_NAME = TABLE_NM
-- AND A.COLUMN_NAME NOT IN('UPDTR_ID','UPDT_DT','UPDT_IP')
UNION ALL
SELECT 2000,
' ) '
FROM DUAL
)
ORDER BY COLUMN_ID;
Raw
insert_ide_friendly.sql
WITH TABLE_NM AS
( SELECT UPPER(:A_TBL_NAME) as TABLE_NM
,'-- ' AS desc01
,' ' as desc02
FROM DUAL)
SELECT COLUMN_NM
FROM (
SELECT -10 AS COLUMN_ID
,'INSERT INTO ' || TABLE_NM AS COLUMN_NM
FROM DUAL, TABLE_NM
UNION ALL
SELECT A.COLUMN_ID,
DECODE(A.COLUMN_ID,1,' ( ' ||A.COLUMN_NAME,' , ' || A.COLUMN_NAME) ||
lpad(' ', 26 - LENGTHB(DECODE(A.COLUMN_ID,1,' ' ||A.COLUMN_NAME,', ' || A.COLUMN_NAME))) || desc01 ||
(SELECT comments FROM user_col_comments WHERE table_name = a.table_name AND column_name = a.column_name) ||
decode(b.position, null, '', '(PK' || b.position||')') || desc02
as col_comments
FROM USER_TAB_COLUMNS A, TABLE_NM,
(SELECT A.TABLE_NAME, A.COLUMN_NAME, A.CONSTRAINT_NAME, A.POSITION
FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B
WHERE A.TABLE_NAME = B.TABLE_NAME
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND B.CONSTRAINT_TYPE = 'P') B
WHERE A.TABLE_NAME = B.TABLE_NAME(+)
AND A.COLUMN_NAME = B.COLUMN_NAME(+)
AND A.TABLE_NAME = TABLE_NM
-- AND A.COLUMN_NAME NOT IN('UPDTR_ID','UPDT_DT','UPDT_IP')
UNION ALL
SELECT 1000,
' ) VALUES ('
FROM DUAL
UNION ALL
SELECT A.COLUMN_ID + 1000,
DECODE(A.COLUMN_ID,1,' ',' ,')
|| CASE WHEN a.column_name = 'CRT_DT' THEN ' SYSDATE' -- Inserted date column here.
WHEN a.column_name = 'UPD_DT' THEN ' SYSDATE' -- Updated date column here.
ELSE ' P_' || A.COLUMN_NAME || '' END
|| lpad(' ', 26 - LENGTHB(CASE WHEN a.column_name = 'RGST_DT' THEN 'SAAAA'
ELSE LOWER(a.column_name) END))
|| desc01 || (SELECT comments FROM user_col_comments WHERE table_name = a.table_name AND column_name = a.column_name) ||
decode(b.position, null, '', '(PK' || b.position||')') || desc02
as col_comments
FROM USER_TAB_COLUMNS A, TABLE_NM,
(SELECT A.TABLE_NAME, A.COLUMN_NAME, A.CONSTRAINT_NAME, A.POSITION
FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B
WHERE A.TABLE_NAME = B.TABLE_NAME
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND B.CONSTRAINT_TYPE = 'P') B
WHERE A.TABLE_NAME = B.TABLE_NAME(+)
AND A.COLUMN_NAME = B.COLUMN_NAME(+)
AND A.TABLE_NAME = TABLE_NM
-- AND A.COLUMN_NAME NOT IN('UPDTR_ID','UPDT_DT','UPDT_IP')
UNION ALL
SELECT 2000,
' ) '
FROM DUAL
)
ORDER BY COLUMN_ID;
Raw
select.sql
WITH TABLE_NM AS
( SELECT 'TABLE_NAME' as TABLE_NM
,'/* ' as desc01
,' */' as desc02
FROM DUAL)
SELECT COLUMN_NM
FROM (
SELECT -10 AS COLUMN_ID
,'SELECT ' AS COLUMN_NM
FROM DUAL
UNION ALL
SELECT A.COLUMN_ID,
DECODE(A.COLUMN_ID,1,' ' ||A.COLUMN_NAME,' , ' || A.COLUMN_NAME) ||
lpad(' ', 26 - LENGTHB(DECODE(A.COLUMN_ID,1,' ' ||A.COLUMN_NAME,', ' || A.COLUMN_NAME))) || 'AS ' || lower(SUBSTR(A.COLUMN_NAME, 1,1))||SUBSTR(replace(INITCAP(lower(A.COLUMN_NAME)), '_',''), 2) ||
lpad(' ', 26 - LENGTHB(DECODE(A.COLUMN_ID,1,' ' ||A.COLUMN_NAME,', ' || A.COLUMN_NAME))) || desc01 ||
(SELECT comments FROM user_col_comments WHERE table_name = a.table_name AND column_name = a.column_name) ||
decode(b.position, null, '', '(PK' || b.position||')') || desc02
as col_comments
FROM USER_TAB_COLUMNS A, TABLE_NM,
(SELECT A.TABLE_NAME, A.COLUMN_NAME, A.CONSTRAINT_NAME, A.POSITION
FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B
WHERE A.TABLE_NAME = B.TABLE_NAME
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND B.CONSTRAINT_TYPE = 'P') B
WHERE A.TABLE_NAME = B.TABLE_NAME(+)
AND A.COLUMN_NAME = B.COLUMN_NAME(+)
AND A.TABLE_NAME = TABLE_NM
-- AND A.COLUMN_NAME NOT IN('UPDTR_ID','UPDT_DT','UPDT_IP')
UNION ALL
SELECT 1000,
'FROM ' || TABLE_NM AS COLUMN_NM
FROM DUAL, TABLE_NM
)
ORDER BY COLUMN_ID;
Raw
select_ide_friendly.sql
WITH TABLE_NM AS
( SELECT UPPER(:A_TBL_NAME) as TABLE_NM
,'-- ' as desc01
,' ' as desc02
FROM DUAL)
SELECT COLUMN_NM
FROM (
SELECT -10 AS COLUMN_ID
,'SELECT ' AS COLUMN_NM
FROM DUAL
UNION ALL
SELECT A.COLUMN_ID,
DECODE(A.COLUMN_ID,1,' ' ||A.COLUMN_NAME,' , ' || A.COLUMN_NAME) ||
lpad(' ', 26 - LENGTHB(DECODE(A.COLUMN_ID,1,' ' ||A.COLUMN_NAME,', ' || A.COLUMN_NAME))) || desc01 ||
(SELECT comments FROM user_col_comments WHERE table_name = a.table_name AND column_name = a.column_name) ||
decode(b.position, null, '', '(PK' || b.position||')') || desc02
as col_comments
FROM USER_TAB_COLUMNS A, TABLE_NM,
(SELECT A.TABLE_NAME, A.COLUMN_NAME, A.CONSTRAINT_NAME, A.POSITION
FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B
WHERE A.TABLE_NAME = B.TABLE_NAME
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND B.CONSTRAINT_TYPE = 'P') B
WHERE A.TABLE_NAME = B.TABLE_NAME(+)
AND A.COLUMN_NAME = B.COLUMN_NAME(+)
AND A.TABLE_NAME = TABLE_NM
AND A.COLUMN_NAME NOT IN ('INSERT EXCEPT COLUMN HERE IF YOU WANT.')
UNION ALL
SELECT 1000,
'FROM ' || TABLE_NM AS COLUMN_NM
FROM DUAL, TABLE_NM
)
ORDER BY COLUMN_ID;
Raw
update.sql
WITH TABLE_NM AS
( SELECT 'TABLE_NAME' as TABLE_NM
,'/* ' as desc01
,' */' as desc02
FROM DUAL)
SELECT col_comments
FROM (
SELECT -1 AS COLUMN_ID,
'UPDATE ' || TABLE_NM AS col_comments
FROM DUAL, TABLE_NM
UNION ALL
SELECT A.COLUMN_ID,
DECODE(ROW_NUMBER() OVER(PARTITION BY A.TABLE_NAME order by A.COLUMN_ID),1,'SET ' ||A.COLUMN_NAME,' , ' || A.COLUMN_NAME)
|| lpad(' ',30 - LENGTHB(A.COLUMN_NAME)) ||'= '
|| CASE WHEN a.column_name = 'UPDT_DT' THEN 'SYSTIMESTAMP' -- Updated date column here.
WHEN a.column_name = 'UPDTR_ID' THEN ' #' || '%user_id' || '#' -- Updated ID column here.
ELSE '#{'|| lower(SUBSTR(A.COLUMN_NAME, 1,1))||SUBSTR(replace(INITCAP(lower(A.COLUMN_NAME)), '_',''), 2) || '}'
END
|| lpad(' ', 30 - LENGTHB(CASE WHEN a.column_name = 'UPDT_DT' THEN 'SAAAA'
ELSE LOWER(a.column_name)
END))
|| desc01
|| (SELECT comments FROM user_col_comments WHERE table_name = a.table_name AND column_name = a.column_name)
|| desc02 as col_comments
FROM USER_TAB_COLUMNS A, TABLE_NM,
(SELECT A.TABLE_NAME, A.COLUMN_NAME, A.CONSTRAINT_NAME, A.POSITION, B.CONSTRAINT_TYPE
FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B
WHERE A.TABLE_NAME = B.TABLE_NAME
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND B.CONSTRAINT_TYPE = 'P') B
WHERE A.TABLE_NAME = B.TABLE_NAME(+)
AND A.COLUMN_NAME = B.COLUMN_NAME(+)
AND A.TABLE_NAME = TABLE_NM
AND NVL(B.CONSTRAINT_TYPE,' ') <> 'P'
AND A.COLUMN_NAME NOT IN('RGSTR_ID','RGST_DT','RGST_IP') -- for Insert only columns here.
UNION ALL -- Normally All PK tables will listed in where cause.
SELECT A.position + 2000 AS COLUMN_ID,
DECODE(A.position,1,'WHERE ', 'AND ') || A.COLUMN_NAME || lpad(' ',30 - LENGTHB(A.COLUMN_NAME)) ||'= #{'
|| lower(SUBSTR(A.COLUMN_NAME, 1,1))||SUBSTR(replace(INITCAP(lower(A.COLUMN_NAME)), '_',''), 2) || '}'
|| lpad(' ', 30 - LENGTHB(LOWER(a.column_name)))
|| desc01 || (SELECT comments FROM user_col_comments WHERE table_name = a.table_name AND column_name = a.column_name)
|| decode(a.position, null, '', '(PK' || a.position||')') || desc02
FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B, TABLE_NM
WHERE A.TABLE_NAME = B.TABLE_NAME
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND B.CONSTRAINT_TYPE = 'P'
AND A.TABLE_NAME = TABLE_NM
)
ORDER BY COLUMN_ID;
Raw
update_ide_friendly.sql
WITH TABLE_NM AS
( SELECT UPPER(:A_TBL_NAME) as TABLE_NM
,'-- ' as desc01
,' ' as desc02
FROM DUAL)
SELECT col_comments
FROM (
SELECT -1 AS COLUMN_ID,
'UPDATE ' || TABLE_NM AS col_comments
FROM DUAL, TABLE_NM
UNION ALL
SELECT A.COLUMN_ID,
DECODE(ROW_NUMBER() OVER(PARTITION BY A.TABLE_NAME order by A.COLUMN_ID),1,'SET ' ||A.COLUMN_NAME,' , ' || A.COLUMN_NAME)
|| lpad(' ',30 - LENGTHB(A.COLUMN_NAME)) ||'= '
|| CASE WHEN a.column_name = 'UPD_DT' THEN 'SYSDATE' -- Updated date column here.
ELSE 'P_'|| A.COLUMN_NAME
END
|| lpad(' ', 30 - LENGTHB(CASE WHEN a.column_name = 'UPDT_DT' THEN 'SAAAA'
ELSE LOWER(a.column_name)
END))
|| desc01
|| (SELECT comments FROM user_col_comments WHERE table_name = a.table_name AND column_name = a.column_name)
|| desc02 as col_comments
FROM USER_TAB_COLUMNS A, TABLE_NM,
(SELECT A.TABLE_NAME, A.COLUMN_NAME, A.CONSTRAINT_NAME, A.POSITION, B.CONSTRAINT_TYPE
FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B
WHERE A.TABLE_NAME = B.TABLE_NAME
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND B.CONSTRAINT_TYPE = 'P') B
WHERE A.TABLE_NAME = B.TABLE_NAME(+)
AND A.COLUMN_NAME = B.COLUMN_NAME(+)
AND A.TABLE_NAME = TABLE_NM
AND NVL(B.CONSTRAINT_TYPE,' ') <> 'P'
AND A.COLUMN_NAME NOT IN('CRT_DT','CRT_ID','CRT_IP','CRT_MAC') -- for Insert only columns here.
UNION ALL -- Normally All PK tables will listed in where cause.
SELECT A.position + 2000 AS COLUMN_ID,
DECODE(A.position,1,'WHERE ', 'AND ') || A.COLUMN_NAME || lpad(' ',30 - LENGTHB(A.COLUMN_NAME)) ||'= P_'
|| A.COLUMN_NAME || ''
|| lpad(' ', 30 - LENGTHB(LOWER(a.column_name)))
|| desc01 || (SELECT comments FROM user_col_comments WHERE table_name = a.table_name AND column_name = a.column_name)
|| decode(a.position, null, '', '(PK' || a.position||')') || desc02
FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B, TABLE_NM
WHERE A.TABLE_NAME = B.TABLE_NAME
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND B.CONSTRAINT_TYPE = 'P'
AND A.TABLE_NAME = TABLE_NM
)
ORDER BY COLUMN_ID;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment