Create a procedure to count every row of every column in a list of table name in Oracle?

Hugan 0
Viewed 76

I have a list consisting of: table names, column names.

I need a procedure that can count value_rows and null_rows of each column in each table.

Assume the number of columns of each table may change in the future.

Force to use procedure and display results when calling procedure (don't use DML method to update or create another table)

My idea for this is to use 2 variables in 2 loop: @v_table_name, @v_column_name

create or replace procedure hc_test
as
declare
 v_table_name varchar2(255),
 v_column_name varchar2(255)
BEGIN
  for v_table_name in (select table_name FROM USER_TAB_COLUMNS WHERE table_name IN ('CTHD','HOADON','KHACHHANG','NHANVIEN','SANPHAM')
    loop
      for v_column_name in (select column_name from USER_TAB_COLUMNS WHERE table_name = v_table_name)
        loop
          dbms_output.put_line
          (
          select 
          @v_table_name as table_name, 
          @v_column_name as column_name, 
          count(@v_column_name)as row_num,
          count(*) - count(@v_column_name) as null_row
          from @v_table_name
          )
END;

I know my code is syntax error, but i really dont know how to handle, then I can't finish writing a complete procedure, even though I've searched and watched some videos on youtube it's still hard to do.

Expected output like this: enter image description here

I use PL/SQL Developer 12 software for Oracle database, then it's very difficult to apply code from other programs here.

Your solution will help me so much in increase my skill in Oracle dev,

Thank you so much, and have a nice day.

Below code to create sample table

/* SQLINES DEMO *** le CTHD    Script Date: 18/04/2020 1:16:47 PM ******/
CREATE TABLE CTHD(
  SOHD Number(10) NOT NULL,
  MASP char(4) NOT NULL,
  SL Number(10) NULL,
 CONSTRAINT pk_cthd PRIMARY KEY 
(
  SOHD,
  MASP 
) 
) ;
/* SQLINES DEMO *** le HOADON    Script Date: 18/04/2020 1:16:47 PM ******/
CREATE TABLE HOADON(
  SOHD Number(10) NOT NULL,
  NGHD Timestamp NULL,
  MAKH char(4) NULL,
  MANV char(4) NULL,
  TRIGIA Number NULL,
 CONSTRAINT pk_hd PRIMARY KEY 
(
  SOHD 
) 
) ;
/* SQLINES DEMO *** le KHACHHANG    Script Date: 18/04/2020 1:16:47 PM ******/
CREATE TABLE KHACHHANG(
  MAKH char(4) NOT NULL,
  HOTEN Varchar2(40) NULL,
  DCHI Varchar2(50) NULL,
  SODT Varchar2(20) NULL,
  NGSINH Timestamp NULL,
  NGDK Timestamp NULL,
  DOANHSO Number NULL,
 CONSTRAINT pk_kh PRIMARY KEY 
(
  MAKH 
) 
) ;

/* SQLINES DEMO *** le NHANVIEN    Script Date: 18/04/2020 1:16:47 PM ******/
CREATE TABLE NHANVIEN(
  MANV char(4) NOT NULL,
  HOTEN Varchar2(40) NULL,
  SODT Varchar2(20) NULL,
  NGVL Timestamp NULL,
 CONSTRAINT pk_nv PRIMARY KEY 
(
  MANV 
) 
) ;
/* SQLINES DEMO *** le SANPHAM    Script Date: 18/04/2020 1:16:47 PM ******/
CREATE TABLE SANPHAM(
  MASP char(4) NOT NULL,
  TENSP Varchar2(40) NULL,
  DVT Varchar2(20) NULL,
  NUOCSX Varchar2(40) NULL,
  GIA Number NULL,
 CONSTRAINT pk_sp PRIMARY KEY 
(
  MASP 
) 
) ;

INSERT CTHD (SOHD, MASP, SL) SELECT  1001, N'BC01', 5  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1001, N'BC02', 10  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1001, N'ST01', 5  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1001, N'ST08', 10  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1001, N'TV02', 10  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1002, N'BB01', 20  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1002, N'BB02', 20  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1002, N'BC04', 20  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1003, N'BB03', 10  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1004, N'TV01', 20  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1004, N'TV02', 10  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1004, N'TV03', 10  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1004, N'TV04', 10  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1005, N'TV05', 50  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1005, N'TV06', 50  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1006, N'ST01', 30  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1006, N'ST02', 10  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1006, N'TV07', 20  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1007, N'ST03', 10  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1008, N'ST04', 8  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1009, N'ST05', 10  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1010, N'ST04', 50  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1010, N'ST07', 50  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1010, N'ST08', 100  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1010, N'TV03', 100  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1010, N'TV07', 50  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1011, N'ST06', 50  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1012, N'ST07', 3  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1013, N'ST08', 5  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1014, N'BB01', 50  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1014, N'BB02', 100  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1014, N'BC02', 80  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1014, N'BC04', 60  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1015, N'BB02', 30  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1015, N'BB03', 7  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1016, N'TV01', 5  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1017, N'TV02', 1  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1017, N'TV03', 1  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1017, N'TV04', 5  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1018, N'ST04', 6  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1019, N'ST05', 1  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1019, N'ST06', 2  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1020, N'ST07', 10  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1021, N'ST08', 5  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1021, N'TV01', 7  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1021, N'TV02', 10  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1022, N'ST07', 1  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1023, N'ST04', 6  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1024, N'BB05', 10  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1025, N'BC05', 8  FROM dual;
commit;
INSERT CTHD (SOHD, MASP, SL) SELECT  1026, N'BB06', 5  FROM dual;
commit;
INSERT HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) SELECT  1001, to_date('2006-07-23 ', 'YYYY-MM-DD'), N'KH01', N'NV01', 320000.0000  FROM dual;
commit;
INSERT HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) SELECT  1002, to_date('2006-08-12 ', 'YYYY-MM-DD'), N'KH01', N'NV02', 840000.0000  FROM dual;
commit;
INSERT HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) SELECT  1003, to_date('2006-08-23 ', 'YYYY-MM-DD'), N'KH02', N'NV01', 100000.0000  FROM dual;
commit;
INSERT HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) SELECT  1004, to_date('2006-09-01 ', 'YYYY-MM-DD'), N'KH02', N'NV01', 180000.0000  FROM dual;
commit;
INSERT HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) SELECT  1005, to_date('2006-10-20 ', 'YYYY-MM-DD'), N'KH01', N'NV02', 3800000.0000  FROM dual;
commit;
INSERT HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) SELECT  1006, to_date('2006-10-16 ', 'YYYY-MM-DD'), N'KH01', N'NV03', 2430000.0000  FROM dual;
commit;
INSERT HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) SELECT  1007, to_date('2006-10-28 ', 'YYYY-MM-DD'), N'KH03', N'NV03', 510000.0000  FROM dual;
commit;
INSERT HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) SELECT  1008, to_date('2006-10-28 ', 'YYYY-MM-DD'), N'KH01', N'NV03', 440000.0000  FROM dual;
commit;
INSERT HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) SELECT  1009, to_date('2006-10-28 ', 'YYYY-MM-DD'), N'KH03', N'NV04', 200000.0000  FROM dual;
commit;
INSERT HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) SELECT  1010, to_date('2006-11-01 ', 'YYYY-MM-DD'), N'KH01', N'NV01', 5200000.0000  FROM dual;
commit;
INSERT HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) SELECT  1011, to_date('2006-11-04 ', 'YYYY-MM-DD'), N'KH04', N'NV03', 250000.0000  FROM dual;
commit;
INSERT HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) SELECT  1012, to_date('2006-11-30 ', 'YYYY-MM-DD'), N'KH05', N'NV03', 21000.0000  FROM dual;
commit;
INSERT HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) SELECT  1013, to_date('2006-12-12 ', 'YYYY-MM-DD'), N'KH06', N'NV01', 5000.0000  FROM dual;
commit;
INSERT HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) SELECT  1014, to_date('2006-12-31 ', 'YYYY-MM-DD'), N'KH03', N'NV02', 3150000.0000  FROM dual;
commit;
INSERT HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) SELECT  1015, to_date('2007-01-01 ', 'YYYY-MM-DD'), N'KH06', N'NV01', 910000.0000  FROM dual;
commit;
INSERT HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) SELECT  1016, to_date('2007-01-01 ', 'YYYY-MM-DD'), N'KH07', N'NV02', 12500.0000  FROM dual;
commit;
INSERT HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) SELECT  1017, to_date('2007-01-02 ', 'YYYY-MM-DD'), N'KH08', N'NV03', 35000.0000  FROM dual;
commit;
INSERT HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) SELECT  1018, to_date('2007-01-13 ', 'YYYY-MM-DD'), N'KH08', N'NV03', 330000.0000  FROM dual;
commit;
INSERT HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) SELECT  1019, to_date('2007-01-13 ', 'YYYY-MM-DD'), N'KH01', N'NV03', 30000.0000  FROM dual;
commit;
INSERT HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) SELECT  1020, to_date('2007-01-14 ', 'YYYY-MM-DD'), N'KH09', N'NV04', 70000.0000  FROM dual;
commit;
INSERT HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) SELECT  1021, to_date('2007-01-16 ', 'YYYY-MM-DD'), N'KH10', N'NV03', 67500.0000  FROM dual;
commit;
INSERT HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) SELECT  1022, to_date('2007-01-16 ', 'YYYY-MM-DD'), NULL, N'NV03', 7000.0000  FROM dual;
commit;
INSERT HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) SELECT  1023, to_date('2007-01-17 ', 'YYYY-MM-DD'), NULL, N'NV01', 330000.0000  FROM dual;
commit;
INSERT HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) SELECT  1025, to_date('2020-04-17 ', 'YYYY-MM-DD'), N'KH12', N'NV3 ', 800000.0000  FROM dual;
commit;
INSERT HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) SELECT  1026, to_date('2020-04-17 ', 'YYYY-MM-DD'), N'KH10', N'NV4 ', 500000.0000  FROM dual;
commit;
INSERT KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, NGDK, DOANHSO) SELECT  N'KH01', N'Nguyen Van A', N'731 Tran Hung Dao, Q5, TpHCM', N'8823451', to_date('1960-10-22 ', 'YYYY-MM-DD'), to_date('2006-07-22 ', 'YYYY-MM-DD'), 13060000.0000  FROM dual;
commit;
INSERT KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, NGDK, DOANHSO) SELECT  N'KH02', N'Tran Ngoc Han', N'23/5 Nguyen Trai, Q5, TpHCM', N'908256478', to_date('1974-04-03 ', 'YYYY-MM-DD'), to_date('2006-07-30 ', 'YYYY-MM-DD'), 280000.0000  FROM dual;
commit;
INSERT KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, NGDK, DOANHSO) SELECT  N'KH03', N'Tran Ngoc Linh', N'45 Nguyen Canh Chan, Q1, TpHCM', N'938776266', to_date('1980-06-12 ', 'YYYY-MM-DD'), to_date('2006-05-08 ', 'YYYY-MM-DD'), 3860000.0000  FROM dual;
commit;
INSERT KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, NGDK, DOANHSO) SELECT  N'KH04', N'Tran Minh Long', N'50/34 Le Dai Hanh, Q10, TpHCM', N'917325476', to_date('1965-03-09 ', 'YYYY-MM-DD'), to_date('2006-02-10 ', 'YYYY-MM-DD'), 250000.0000  FROM dual;
commit;
INSERT KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, NGDK, DOANHSO) SELECT  N'KH05', N'Le Nhat Minh', N'34 Truong Dinh, Q3, TpHCM', N'8246108', to_date('1950-03-10 ', 'YYYY-MM-DD'), to_date('2006-10-28 ', 'YYYY-MM-DD'), 21000.0000  FROM dual;
commit;
INSERT KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, NGDK, DOANHSO) SELECT  N'KH06', N'Le Hoai Thuong', N'227 Nguyen Van Cu, Q5, TpHCM', N'8631738', to_date('1981-12-31 ', 'YYYY-MM-DD'), to_date('2006-11-24 ', 'YYYY-MM-DD'), 915000.0000  FROM dual;
commit;
INSERT KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, NGDK, DOANHSO) SELECT  N'KH07', N'Nguyen Van Tam', N'32/3 Tran Binh Trong, Q5, TpHCM', N'916783565', to_date('1971-04-06 ', 'YYYY-MM-DD'), to_date('2006-01-12 ', 'YYYY-MM-DD'), 12500.0000  FROM dual;
commit;
INSERT KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, NGDK, DOANHSO) SELECT  N'KH08', N'Phan Thi Thanh', N'45/2 An Duong Vuong, Q5, TpHCM', N'938435756', to_date('1971-01-10 ', 'YYYY-MM-DD'), to_date('2006-12-13 ', 'YYYY-MM-DD'), 365000.0000  FROM dual;
commit;
INSERT KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, NGDK, DOANHSO) SELECT  N'KH09', N'Le Ha Vinh', N'873 Le Hong Phong, Q5, TpHCM', N'8654763', to_date('1979-09-03 ', 'YYYY-MM-DD'), to_date('2007-01-14 ', 'YYYY-MM-DD'), 70000.0000  FROM dual;
commit;
INSERT KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, NGDK, DOANHSO) SELECT  N'KH10', N'Ha Duy Lap', N'34/34B Nguyen Trai, Q1, TpHCM', N'8768904', to_date('1983-05-02 ', 'YYYY-MM-DD'), to_date('2007-01-16 ', 'YYYY-MM-DD'), 67500.0000  FROM dual;
commit;
INSERT NHANVIEN (MANV, HOTEN, SODT, NGVL) SELECT  N'NV01', N'Nguyen Nhu Nhut', N'927345678', to_date('2006-04-13 ', 'YYYY-MM-DD')  FROM dual;
commit;
INSERT NHANVIEN (MANV, HOTEN, SODT, NGVL) SELECT  N'NV02', N'Le Thi Phi Yen', N'987567390', to_date('2006-04-21 ', 'YYYY-MM-DD')  FROM dual;
commit;
INSERT NHANVIEN (MANV, HOTEN, SODT, NGVL) SELECT  N'NV03', N'Nguyen Van B', N'997047382', to_date('2006-04-27 ', 'YYYY-MM-DD')  FROM dual;
commit;
INSERT NHANVIEN (MANV, HOTEN, SODT, NGVL) SELECT  N'NV04', N'Ngo Thanh Tuan', N'913758498', to_date('2006-06-24 ', 'YYYY-MM-DD')  FROM dual;
commit;
INSERT NHANVIEN (MANV, HOTEN, SODT, NGVL) SELECT  N'NV05', N'Nguyen Thi Truc Thanh', N'918590387', to_date('2006-07-20 ', 'YYYY-MM-DD')  FROM dual;
commit;
INSERT NHANVIEN (MANV, HOTEN, SODT, NGVL) SELECT  N'NV06', N'Le Ngoc Tram', N'987828019', to_date('2020-04-17 ', 'YYYY-MM-DD')  FROM dual;
commit;
INSERT SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) SELECT  N'BB01', N'But bi', N'cay', N'Viet Nam', 5000.0000  FROM dual;
commit;
INSERT SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) SELECT  N'BB02', N'But bi', N'cay', N'Trung Quoc', 7000.0000  FROM dual;
commit;
INSERT SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) SELECT  N'BB03', N'But bi', N'hop', N'Thai Lan', 100000.0000  FROM dual;
commit;
INSERT SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) SELECT  N'BB06', N'But bi', N'cay', N'USA', 120000.0000  FROM dual;
commit;
INSERT SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) SELECT  N'BC01', N'But chi', N'cay', N'Singapore', 3000.0000  FROM dual;
commit;
INSERT SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) SELECT  N'BC02', N'But chi', N'cay', N'Singapore', 5000.0000  FROM dual;
commit;
INSERT SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) SELECT  N'BC03', N'But chi', N'cay', N'Viet Nam', 3500.0000  FROM dual;
commit;
INSERT SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) SELECT  N'BC04', N'But chi', N'hop', N'Viet Nam', 30000.0000  FROM dual;
commit;
INSERT SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) SELECT  N'ST01', N'So tay 500 trang', N'quyen', N'Trung Quoc', 40000.0000  FROM dual;
commit;
INSERT SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) SELECT  N'ST02', N'So tay loai 1', N'quyen', N'Viet Nam', 55000.0000  FROM dual;
commit;
INSERT SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) SELECT  N'ST03', N'So tay loai 2', N'quyen', N'Viet Nam', 51000.0000  FROM dual;
commit;
INSERT SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) SELECT  N'ST04', N'So tay', N'quyen', N'Thai Lan', 55000.0000  FROM dual;
commit;
INSERT SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) SELECT  N'ST05', N'So tay mong', N'quyen', N'Thai Lan', 20000.0000  FROM dual;
commit;
INSERT SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) SELECT  N'ST06', N'Phan viet bang', N'hop', N'Viet Nam', 5000.0000  FROM dual;
commit;
INSERT SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) SELECT  N'ST07', N'Phan khong bui', N'hop', N'Viet Nam', 7000.0000  FROM dual;
commit;
INSERT SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) SELECT  N'ST08', N'Bong bang', N'cai', N'Viet Nam', 1000.0000  FROM dual;
commit;
INSERT SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) SELECT  N'ST09', N'But long', N'cay', N'Viet Nam', 5000.0000  FROM dual;
commit;
INSERT SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) SELECT  N'ST10', N'But long', N'cay', N'Trung Quoc', 7000.0000  FROM dual;
commit;
INSERT SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) SELECT  N'TV01', N'Tap 100 giay mong', N'quyen', N'Trung Quoc', 2500.0000  FROM dual;
commit;
INSERT SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) SELECT  N'TV02', N'Tap 200 giay mong', N'quyen', N'Trung Quoc', 4500.0000  FROM dual;
commit;
INSERT SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) SELECT  N'TV03', N'Tap 100 giay tot', N'quyen', N'Viet Nam', 3000.0000  FROM dual;
commit;
INSERT SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) SELECT  N'TV04', N'Tap 200 giay tot', N'quyen', N'Viet Nam', 5500.0000  FROM dual;
commit;
INSERT SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) SELECT  N'TV05', N'Tap 100 trang', N'chuc', N'Viet Nam', 23000.0000  FROM dual;
commit;
INSERT SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) SELECT  N'TV06', N'Tap 200 trang', N'chuc', N'Viet Nam', 53000.0000  FROM dual;
commit;
INSERT SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA) SELECT  N'TV07', N'Tap 100 trang', N'chuc', N'Trung Quoc', 34000.0000  FROM dual;
commit;
procedure oracle count loops
3 Answers

One 'execute immediate' per table's column, is a bit overkilling... is it not ?

This template should do the work:

SELECT * FROM (
    SELECT 
        COUNT(colname) AS n_colname
        [, ....]
    FROM table_name
)
UNPIVOT (
    non_nulls
    FOR colname IN (
        n_colname AS 'n_colname'
        [,...]
    )
);

The number of NULLs in each column being the count(PrimaryKey) - the non_nulls for the column in the result of the query... if you have primary keys in your table... but why should you not... (you will find the PK in all_constraints as constraint of type 'P', be careful with multi columns ones)

It's a bit more complicated to generate the SQL for the EXECUTE IMMEDIATE but should be much faster.

p3consulting0

There are lots of issues with your code.

  • you don't need the declare keyword for a procedure;
  • your first declaration ends with a comma instead of a semicolon, and your second doesn't have a semicolon;
  • your first loop query is missing a closing parenthesis;
  • both loops are against the same USER_TAB_COLUMNS table, which will end up generating more results than you expect;
  • neither declared variable is actually used because your loops have independent names which are the same and take precedence;
  • you can't have a query within a dbms_output call - put_line() takes a string value, not a query;
  • you don't have a semicolon after your output call;
  • you don't have either end loop;
  • @variable isn't Oracle syntax;
  • you need to refer to the table/column name field within the loop record, not the entire record;
  • you can't use variables for table/column names in a static query anyway, so it would have to be dynamic SQL;
  • count(*) counts all rows, not just those with values (which seems to be what you want).

So to use two loops like that you could do:

create or replace procedure hc_test
as
  v_sql varchar2(4000);
  v_count number;
  v_null_count number;
begin
  for v_tables in (
      select table_name
      FROM USER_TABLES
      WHERE table_name IN ('CTHD','HOADON','KHACHHANG','NHANVIEN','SANPHAM')
    )
    loop
      for v_columns in (
          select column_name
          from USER_TAB_COLUMNS
          WHERE table_name = v_tables.table_name
        )
        loop
          v_sql := 'select count("' || v_columns.column_name || '"),'
            || ' count(case when "' || v_columns.column_name || '" is null then 1 end)'
            || ' from "' || v_tables.table_name || '"';
          execute immediate v_sql into v_count, v_null_count;
          dbms_output.put_line (
            v_tables.table_name 
              || ' ' || v_columns.column_name
              || ' ' || v_count
              || ' ' || v_null_count
          );
        end loop;
    end loop;
end;
/

Or you could do it with a single loop:

create or replace procedure hc_test
as
  v_sql varchar2(4000);
  v_count number;
  v_null_count number;
begin
    for v_tab_cols in (
      select table_name, column_name
      from USER_TAB_COLUMNS
      WHERE table_name IN ('CTHD','HOADON','KHACHHANG','NHANVIEN','SANPHAM')
    )
    loop
      v_sql := 'select count("' || v_tab_cols.column_name || '"),'
        || ' count(case when "' || v_tab_cols.column_name || '" is null then 1 end)'
        || ' from "' || v_tab_cols.table_name || '"';
      execute immediate v_sql into v_count, v_null_count;
      dbms_output.put_line (
        v_tab_cols.table_name 
          || ' ' || v_tab_cols.column_name
          || ' ' || v_count
          || ' ' || v_null_count
        );
    end loop;
end;
/

Both generate the output:

CTHD SOHD 51 0
CTHD MASP 51 0
CTHD SL 51 0
HOADON SOHD 25 0
HOADON NGHD 25 0
HOADON MAKH 23 2
HOADON MANV 25 0
HOADON TRIGIA 25 0
KHACHHANG MAKH 10 0
KHACHHANG HOTEN 10 0
KHACHHANG DCHI 10 0
KHACHHANG SODT 10 0
KHACHHANG NGSINH 10 0
KHACHHANG NGDK 10 0
KHACHHANG DOANHSO 10 0
NHANVIEN MANV 6 0
NHANVIEN HOTEN 6 0
NHANVIEN SODT 6 0
NHANVIEN NGVL 6 0
SANPHAM MASP 25 0
SANPHAM TENSP 25 0
SANPHAM DVT 25 0
SANPHAM NUOCSX 25 0
SANPHAM GIA 25 0

You could also do it without loops and without PL/SQL, using an XML trick, which is a bit off-topic for your question but for fun:

with cte (table_name, column_name, xml) as (
  select table_name,
    column_name,
    dbms_xmlgen.getxmltype(
      'select count("' || column_name || '") as value_rows,'
         || ' count(case when "' || column_name || '" is null then 1 end) as null_rows'
         || ' from "' || table_name || '"'
    )
  from user_tab_columns
  where table_name IN ('CTHD','HOADON','KHACHHANG','NHANVIEN','SANPHAM')
)
select cte.table_name, cte.column_name, x.value_rows, x.null_rows
from cte
cross apply xmltable(
  '/ROWSET/ROW'
  passing cte.xml
  columns value_rows number path 'VALUE_ROWS',
    null_rows number path 'NULL_ROWS'
) x;

which generates a more useful result set:

TABLE_NAME COLUMN_NAME VALUE_ROWS NULL_ROWS
CTHD SOHD 51 0
CTHD MASP 51 0
CTHD SL 51 0
HOADON SOHD 25 0
HOADON NGHD 25 0
HOADON MAKH 23 2
HOADON MANV 25 0
HOADON TRIGIA 25 0
KHACHHANG MAKH 10 0
KHACHHANG HOTEN 10 0
KHACHHANG DCHI 10 0
KHACHHANG SODT 10 0
KHACHHANG NGSINH 10 0
KHACHHANG NGDK 10 0
KHACHHANG DOANHSO 10 0
NHANVIEN MANV 6 0
NHANVIEN HOTEN 6 0
NHANVIEN SODT 6 0
NHANVIEN NGVL 6 0
SANPHAM MASP 25 0
SANPHAM TENSP 25 0
SANPHAM DVT 25 0
SANPHAM NUOCSX 25 0
SANPHAM GIA 25 0

db<>fiddle

as @p3consulting pointed out, running a separate count query for each column does seem a bit inefficient; and I hadn't thought about unpivoting, which is neat. Either approach could be modified to work like that, but with the XML approach you could do:

with cte (table_name, xml) as (
  select table_name,
    dbms_xmlgen.getxmltype(
      'select * from ( select '
         || listagg('count("' || column_name || '") as "V_' || column_name || '",'
           || ' count(case when "' || column_name || '" is null then 1 end) as "N_' || column_name || '"', ', ') within group (order by column_id)
         || ' from "' || table_name || '"'
         || ') unpivot ((value_rows, null_rows) for column_name in ('
         || listagg('("V_' || column_name || '",  "N_' || column_name || '") as ''' || column_name || '''', ', ') within group (order by column_id)
         || '))'
    )
  from user_tab_columns
  where table_name IN ('CTHD','HOADON','KHACHHANG','NHANVIEN','SANPHAM')
  and column_id is not null
  group by table_name
)
select cte.table_name, x.value_rows, x.null_rows
from cte
cross apply xmltable(
  '/ROWSET/ROW'
  passing cte.xml
  columns column_name varchar2(30) path 'COLUMN_NAME',
    value_rows number path 'VALUE_ROWS',
    null_rows number path 'NULL_ROWS'
) x;

which gets the same output.

db<>fiddle (including some of the intermediate XML to maybe make it slightly clearer.)

Alex Poole0

Here a complete solution, without limitation implied by LISTAGG and GETXMLTYPE on the length of the parameter (4000 char unless "extended type" used in the DB)

CREATE OR REPLACE TYPE t_tablestats AS OBJECT (
      table_name  VARCHAR2(64),
      column_name  VARCHAR2(128),
      value_rows  NUMBER(19,0),
      null_rows  NUMBER(19,0)
    );
    /
    
CREATE OR REPLACE TYPE t_tablestats_tab AS TABLE OF t_tablestats ;
    /
  


CREATE OR REPLACE FUNCTION gen_stats_tablecolumns_str( i_table_name IN VARCHAR2)
RETURN CLOB 
AS
  w_result CLOB ;
BEGIN
  SELECT
    q'~SELECT '~' || i_table_name || q'~' AS table_name, d.* FROM ( SELECT ~' ||
    REPLACE( REPLACE( REPLACE(
      JSON_ARRAYAGG( 
        q'~COUNT('~' || column_name || q'~') AS V_~' || column_name || q'~, COUNT(CASE WHEN ~' || column_name || q'~ IS NULL THEN 1 END) AS N_~' || column_name
        RETURNING CLOB)
      , '["', ''), '","', ',' || CHR(10)), '"]', '')
     || ' FROM ' || table_name 
     || ') UNPIVOT( (value_rows,null_rows) FOR column_name IN (' 
     || REPLACE( REPLACE( REPLACE(
        JSON_ARRAYAGG( '(V_' || column_name || ', N_' || column_name || ')' RETURNING CLOB)
      , '["', ''), '","', ',' || CHR(10)), '"]', '')  || ')) d'
    INTO w_result
  FROM user_tab_columns
  WHERE table_name = i_table_name
  AND column_id is not null
  GROUP BY table_name
  ;

  RETURN w_result ;
END ;
/

-- i_table_names  comma sep list of table names
    CREATE OR REPLACE FUNCTION gen_statscolvalues( i_table_names IN VARCHAR2)
    RETURN t_tablestats_tab
    PIPELINED
    IS
      w_query CLOB ;
      curs_dynamic    sys_refcursor ;
      w_result t_tablestats := t_tablestats(null,null,null,null) ;
    BEGIN
      FOR rec IN (
        SELECT CAST(REGEXP_SUBSTR(i_table_names ,'[^,]+',1,LEVEL) AS VARCHAR2(64)) AS table_name FROM DUAL
        CONNECT BY REGEXP_SUBSTR(i_table_names ,'[^,]+', 1,LEVEL) IS NOT NULL
      )
      LOOP
        SELECT gen_stats_tablecolumns_str( rec.table_name ) INTO w_query FROM DUAL ;
        OPEN curs_dynamic FOR w_query ;
        LOOP
          FETCH curs_dynamic INTO w_result.table_name, w_result.column_name, w_result.value_rows, w_result.null_rows  ;
          w_result.column_name := SUBSTR(w_result.column_name,3, INSTR( w_result.column_name, '_N_')-3) ;
          
          EXIT WHEN curs_dynamic%NOTFOUND ;
          PIPE ROW(w_result) ;
          
        END LOOP ;
        CLOSE curs_dynamic;
      END LOOP ;
      
      RETURN ;
    END ;
    /
p3consulting0
Related