–順序插入設備表數據，Ps:暫時無法識別下劃線參數需要修改-------
–可以去掉帶下劃線的參數，或者暫時把下劃線修改再改回來
–place_code，setup_state，create_time，update_time，is_online

– 如果存在刪除函數INSERT_DATA_TO_DEVICE…
DROP PROCEDURE
IF EXISTS INSERT_DATA_TO_DEVICE;
– 創建函數
CREATE OR REPLACE PROCEDURE “public”.“insert_data_to_device”()
AS $BODY$BEGIN
DECLARE n int;
BEGIN
– 定義n的初始數值··············································
n = 100;
– 設置n的終止值··············································
WHILE n < 200 LOOP
INSERT INTO app_device( id, name, type ,createtime,enabled )
VALUES
( n, ‘stu’, ( 1 + ceil(random()*10)) , now(),(1+ceil(random()*1)));
n = n +1 ;
END LOOP;
end;
END$BODY$
LANGUAGE plpgsql;
– 執行函數
CALL INSERT_DATA_TO_DEVICE ();

–查詢
SELECT * FROM app_device;

創建表
DROP TABLE IF EXISTS “public”.“app_device”;
CREATE TABLE “public”.“app_device” (
“id” varchar(32) COLLATE “pg_catalog”.“default” NOT NULL,
“name” varchar(256) COLLATE “pg_catalog”.“default” NOT NULL,
“building_id” varchar(32) COLLATE “pg_catalog”.“default”,
“organization_id” varchar(32) COLLATE “pg_catalog”.“default”,
“type” varchar(2) COLLATE “pg_catalog”.“default” NOT NULL,
“mac” varchar(128) COLLATE “pg_catalog”.“default”,
“external_id” varchar(256) COLLATE “pg_catalog”.“default”,
“gb_id” varchar(20) COLLATE “pg_catalog”.“default”,
“address” varchar(256) COLLATE “pg_catalog”.“default”,
“place_code” varchar(20) COLLATE “pg_catalog”.“default”,
“latitude” float8,
“longitude” float8,
“manufacturer” varchar(256) COLLATE “pg_catalog”.“default”,
“model” varchar(20) COLLATE “pg_catalog”.“default”,
“setup_state” varchar(2) COLLATE “pg_catalog”.“default”,
“setup_time” timestamp(6),
“description” text COLLATE “pg_catalog”.“default”,
“createtime” timestamp(6),
“create_by” varchar(32) COLLATE “pg_catalog”.“default”,
“update_time” timestamp(6),
“update_by” varchar(32) COLLATE “pg_catalog”.“default”,
“last_report_time” timestamp(6),
“last_report_data” text COLLATE “pg_catalog”.“default”,
“maintenance_time” timestamp(6),
“is_online” varchar(1) COLLATE “pg_catalog”.“default”,
“contact” varchar(256) COLLATE “pg_catalog”.“default”,
“phone_number” varchar(256) COLLATE “pg_catalog”.“default”,
“total_running_time” int8,
“soft_version” varchar(256) COLLATE “pg_catalog”.“default”,
“warranty” timestamp(6),
“control_password” varchar(256) COLLATE “pg_catalog”.“default”,
“enabled” varchar(1) COLLATE “pg_catalog”.“default”,
“heading” float8,
“pitch” float8,
“height” float8,
“area_id” varchar(32) COLLATE “pg_catalog”.“default”,
“sub_type” varchar(20) COLLATE “pg_catalog”.“default”,
“region_id” varchar(32) COLLATE “pg_catalog”.“default”,
“features” varchar(1024) COLLATE “pg_catalog”.“default”,
“is_indoor” varchar(1) COLLATE “pg_catalog”.“default” DEFAULT ‘0’::character varying,
“sync_exclude_col” varchar(1024) COLLATE “pg_catalog”.“default”,
“last_online_time” timestamp(6),
“building_floor_id” varchar(32) COLLATE “pg_catalog”.“default”,
“direction” varchar(3) COLLATE “pg_catalog”.“default”,
“building_room_id” varchar(32) COLLATE “pg_catalog”.“default”,
“entrance_or_exit” varchar(1) COLLATE “pg_catalog”.“default” DEFAULT ‘0’::character varying,
“geohash” varchar(12) COLLATE “pg_catalog”.“default”,
“ip_address” varchar(15) COLLATE “pg_catalog”.“default”,
“port” int4,
“link_url” varchar(2048) COLLATE “pg_catalog”.“default”,
“protocol_version” varchar(20) COLLATE “pg_catalog”.“default”,
“login_name” varchar(256) COLLATE “pg_catalog”.“default”,
“login_passwd” varchar(256) COLLATE “pg_catalog”.“default”,
“camera_soft_version” varchar(256) COLLATE “pg_catalog”.“default”,
“user_name” varchar(256) COLLATE “pg_catalog”.“default”,
“control_port” int4
)
;
COMMENT ON COLUMN “public”.“app_device”.“id” IS ‘設備ID’;
COMMENT ON COLUMN “public”.“app_device”.“name” IS ‘設備名稱’;
COMMENT ON COLUMN “public”.“app_device”.“building_id” IS ‘建筑ID’;
COMMENT ON COLUMN “public”.“app_device”.“organization_id” IS ‘所屬組織’;
COMMENT ON COLUMN “public”.“app_device”.“type” IS ‘設備類型
1：攝像機
2：環境傳感器
3：測溫設備
4：報警主機
5：車牌設備
6：門禁控制器
7：智能井蓋
8：智能垃圾箱
9：地磁
10:人臉門禁
11：塔吊’;
COMMENT ON COLUMN “public”.“app_device”.“mac” IS ‘設備MAC地址’;
COMMENT ON COLUMN “public”.“app_device”.“external_id” IS ‘外部平臺ID’;
COMMENT ON COLUMN “public”.“app_device”.“gb_id” IS ‘28181國標編碼’;
COMMENT ON COLUMN “public”.“app_device”.“address” IS ‘安裝地址’;
COMMENT ON COLUMN “public”.“app_device”.“place_code” IS ‘所屬地區’;
COMMENT ON COLUMN “public”.“app_device”.“latitude” IS ‘緯度’;
COMMENT ON COLUMN “public”.“app_device”.“longitude” IS ‘經度’;
COMMENT ON COLUMN “public”.“app_device”.“manufacturer” IS ‘生產廠家’;
COMMENT ON COLUMN “public”.“app_device”.“model” IS ‘設備型號’;
COMMENT ON COLUMN “public”.“app_device”.“setup_state” IS ‘設備安裝狀態：1：已安裝 0：未安裝’;
COMMENT ON COLUMN “public”.“app_device”.“setup_time” IS ‘安裝時間’;
COMMENT ON COLUMN “public”.“app_device”.“description” IS ‘描述’;
COMMENT ON COLUMN “public”.“app_device”.“createtime” IS ‘創建時間’;
COMMENT ON COLUMN “public”.“app_device”.“create_by” IS ‘創建用戶ID’;
COMMENT ON COLUMN “public”.“app_device”.“update_time” IS ‘最后修改時間’;
COMMENT ON COLUMN “public”.“app_device”.“update_by” IS ‘最后修改用戶ID’;
COMMENT ON COLUMN “public”.“app_device”.“last_report_time” IS ‘最后上報時間’;
COMMENT ON COLUMN “public”.“app_device”.“last_report_data” IS ‘最后上報數據’;
COMMENT ON COLUMN “public”.“app_device”.“maintenance_time” IS ‘最后一次維護時間’;
COMMENT ON COLUMN “public”.“app_device”.“is_online” IS ‘在線狀態：0-離線 1-在線’;
COMMENT ON COLUMN “public”.“app_device”.“contact” IS ‘聯系人’;
COMMENT ON COLUMN “public”.“app_device”.“phone_number” IS ‘聯系方式’;
COMMENT ON COLUMN “public”.“app_device”.“total_running_time” IS ‘運行時長（分鐘）’;
COMMENT ON COLUMN “public”.“app_device”.“soft_version” IS ‘固件版本’;
COMMENT ON COLUMN “public”.“app_device”.“warranty” IS ‘保修期’;
COMMENT ON COLUMN “public”.“app_device”.“control_password” IS ‘控制密碼（MD5后）’;
COMMENT ON COLUMN “public”.“app_device”.“enabled” IS ‘啟用狀態：0-禁用 1-啟用’;
COMMENT ON COLUMN “public”.“app_device”.“heading” IS ‘朝向角（取值：0 ~ 360，0 代表正北）’;
COMMENT ON COLUMN “public”.“app_device”.“pitch” IS ‘俯仰角（取值：-180 ~ 180, 0 代表水平）’;
COMMENT ON COLUMN “public”.“app_device”.“height” IS ‘高度（厘米）’;
COMMENT ON COLUMN “public”.“app_device”.“area_id” IS ‘地區ID’;
COMMENT ON COLUMN “public”.“app_device”.“sub_type” IS ‘設備子分類：
1-攝像機子分類：1-槍機 2-球機 3-半球’;
COMMENT ON COLUMN “public”.“app_device”.“region_id” IS ‘區域ID’;
COMMENT ON COLUMN “public”.“app_device”.“features” IS '設備功能集合（有多個功能時，以半角;分隔）
取值：
人臉識別能力：face-anal

車輛識別能力：motor-vehicle-anal

車牌識別能力：plate-no-anal

人體識別能力：body-anal

夜視星光：night

高空拋物識別能力：throw-anal

車輛異常行為識別能力：motor-vehicle-exception-anal

高點相機：high-point

人體測溫能力：person-temp

環境測溫能力：env-temp

塔吊：tower-crane

例子：face-anal;night’;
COMMENT ON COLUMN “public”.“app_device”.“is_indoor” IS ‘是否室內設備：0-否 1-是’;
COMMENT ON COLUMN “public”.“app_device”.“sync_exclude_col” IS ‘不需要同步的字段名稱，支持多個字段用;分隔’;
COMMENT ON COLUMN “public”.“app_device”.“last_online_time” IS ‘最后在線時間’;
COMMENT ON COLUMN “public”.“app_device”.“building_floor_id” IS ‘所屬樓層ID’;
COMMENT ON COLUMN “public”.“app_device”.“direction” IS ‘設備方向：1-EAST 2-WEST 3-SOUTH 4-NORTH 5-NORTHEAST 6-SOUTHWEST 7-SOUTHEAST 8-NORTHWEST 9-OTHER’;
COMMENT ON COLUMN “public”.“app_device”.“building_room_id” IS ‘房間ID’;
COMMENT ON COLUMN “public”.“app_device”.“entrance_or_exit” IS ‘出入口：0-非出入口 1-入口 2-出口’;
COMMENT ON COLUMN “public”.“app_device”.“geohash” IS ‘GEOHASH’;
COMMENT ON COLUMN “public”.“app_device”.“ip_address” IS ‘ip地址’;
COMMENT ON COLUMN “public”.“app_device”.“port” IS ‘端口號’;
COMMENT ON COLUMN “public”.“app_device”.“link_url” IS ‘鏈接url’;
COMMENT ON COLUMN “public”.“app_device”.“protocol_version” IS ‘通信協議版本’;
COMMENT ON COLUMN “public”.“app_device”.“login_name” IS ‘用戶名’;
COMMENT ON COLUMN “public”.“app_device”.“login_passwd” IS ‘密碼’;
COMMENT ON COLUMN “public”.“app_device”.“camera_soft_version” IS ‘設備軟件版本’;
COMMENT ON COLUMN “public”.“app_device”.“user_name” IS ‘控制用戶名’;
COMMENT ON COLUMN “public”.“app_device”.“control_port” IS ‘控制端口號’;
COMMENT ON TABLE “public”.“app_device” IS ‘設備’;

總結

以上是生活随笔為你收集整理的navicat循环插入gp数据库，设备数据的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。