set serveroutput on size 1000000
declare
r_tab2 tab99%rowtype;
begin
for i in 1..1000 loop
select * into r_tab2 from tab99 where rownum = 1;
insert into tab1 values(r_tab2.col1);
commit;
end loop;
exception
when others then
dbms_output.put_line(SQLCODE);
dbms_output.put_line(SQLERRM);
end;
/
https://utano.jp/entry/2014/04/mysql_stored_procedure_loop/
create table member
(id int,name char(20));
insert into member values(1,'utano'),(2,'kobayashi');
--単にループさせてselect文を複数回実行
delimiter //
create procedure loop_select_member(in x int, in y int)
begin
while x <= y do
select * from member where id = x;
set x = x + 1;
end while;
end
//
delimiter ;
call loop_select_member(1, 2);
--idのmaxを取得して、そのレコードを検索する
delimiter //
create procedure get_last_member()
begin
declare max_id int;
select max(id) into max_id from member;
select * from member where id = max_id;
end
//
delimiter ;
call get_last_member();
--ループさせて大量にサンプルデータをinsertする。
drop procedure loop_insert_member;
delimiter //
create procedure loop_insert_member(in x int)
begin
declare max_id int;
declare i int;
set i = 0;
select max(col1) into max_id from member;
while i < x do
set i = i + 1;
insert into member values(max_id + i, concat('sample_', i));
end while;
end
//
delimiter ;
call loop_insert_member(3000);
select * from member;
http://everything-you-do-is-practice.blogspot.com/2017/09/postgresql.html
SET client_min_messages TO notice;
DO $$
DECLARE
var_none text;
BEGIN
FOR i IN 1..10 LOOP
RAISE NOTICE '%', i;
EXECUTE 'SELECT pg_sleep(1)';
END LOOP;
END
$$
;
https://www.postgresql.jp/document/9.4/html/sql-do.html
SET client_min_messages TO notice;
DO $$
DECLARE
r record;
BEGIN
FOR r IN SELECT table_schema, table_name FROM information_schema.tables
WHERE table_type = 'BASE TABLE' AND table_schema = 'public'
LOOP
RAISE NOTICE '%', r.table_name;
EXECUTE 'GRANT ALL ON ' || quote_ident(r.table_schema) || '.' || quote_ident(r.table_name) || ' TO user1';
END LOOP;
END$$;
DO $$
DECLARE
r record;
i integer;
BEGIN
i := 0;
FOR r IN SELECT table_schema, table_name FROM information_schema.tables
WHERE table_type = 'BASE TABLE' AND table_schema = 'public'
LOOP
i := i + 1;
RAISE NOTICE '%', r.table_name;
EXECUTE 'insert into tab2 values(' || i || ',' || quote_literal(r.table_name) || ')';
END LOOP;
END$$;
DO $$
DECLARE
var_none text;
BEGIN
FOR i IN 1..10000000 LOOP
RAISE NOTICE '%', i;
EXECUTE 'insert into random_t values((random() * 10000)::int % 10)';
END LOOP;
END
$$
;
SET DATEFIRST 1; -- 月曜日を１とする
DECLARE @StartDate DATE = '2020-01-01',
@EndDate DATE = '2020-01-15';
WHILE @StartDate <= @EndDate
BEGIN
IF DATEPART(DW, @StartDate) IN (6,7) -- 土・日
BEGIN
SET @StartDate = DATEADD(dd, 1, @StartDate);
CONTINUE;
END
PRINT @StartDate;
SET @StartDate = DATEADD(dd, 1, @StartDate);
END
go
----------------------------------------
DECLARE @col1 int
DECLARE @col2 varchar(100)
--作成対象データの抽出
DECLARE info CURSOR FOR SELECT col1,col2 FROM tab10 WHERE col2 LIKE '%'
OPEN info
FETCH NEXT FROM info INTO @col1, @col2
WHILE (@@FETCH_STATUS = 0)
BEGIN
--レコードの追加
INSERT INTO tab11 (col1,col2) VALUES (@col1, @col2)
--次のレコード
FETCH NEXT FROM info INTO @col1, @col2
END
CLOSE INFO
DEALLOCATE INFO
go
----------------------------------------
DECLARE @counter int;
SET @counter = 0;
WHILE (@counter < 100)
BEGIN
INSERT INTO tab1 (col1) VALUES (@counter);
SET @counter = @counter + 1;
END
----------------------------------------
--GOコマンドで繰り返し実行可能
BEGIN
INSERT INTO tab1 VALUES (123)
END
GO 10
