数据库 mysql postgresql和Oracle中几种循环结构示例

目录

简介

CASE WHEN 结构

oracle 中应用 

postgresql中的应用 

IF 结构

        oracle中if循环的应用

postgresql中示例

loop 和IF 的结合应用 结构

 postgresq中的应用示例

Oracle中的语法示例

for 结构

loop和FOR 的结合应用

Oracle示例：

postgresql示例

while  结构

oracle中的应用示例

postgresql中的应用示例

简介

        在任何编程语言中，循环语法自然是较为重要的分支，本文以oracle，postgresql数据库的循环结构为例进行讲解。

oracle建表语句

-- Create table

create table FINANCIAL_INDEX

(

id NUMBER(11) not null,

cwzbkm varchar2(100),

"2011" NUMBER,

"2010" NUMBER,

"2009" NUMBER

);

postgresql 建表语句

--drop table FINANCIAL_INDEX;

-- Create table

create table FINANCIAL_INDEX

(

id integer not null,

cwzbkm varchar(100),

"2011" numeric(10,2),

"2010" numeric(10,2),

"2009" numeric(10,2)

);

测试数据 

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('1', '总资产收益率(%)', '33.24', '31.73', '40.08');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('2', '净资产收益率(%)', '42.7', '40.93', '54.41');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('3', '主营业务利润率(%)', '38.93', '30.36', '30.16');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('4', '总资产净利润率(%)', '33.24', '31.73', '40.08');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('5', '成本费用利润率(%)', '64.18', '60.18', '59.89');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('6', '营业利润率(%)', '33.61', '32.17', '33.95');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('7', '主营业务成本率(%)', '41.58', '41.66', '40.99');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('8', '销售净利率(%)', '38.93', '30.36', '30.16');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('9', '销售毛利率(%)', '58.42', '58.34', '59.01');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('10', '主营业务收入增长率(%)', '18.48', '30', '46.74');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('11', '净利润增长率(%)', '51.93', '30.85', '57.64');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('12', '利润总额增长率(%)', '44.93', '28.88', '64.53');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('13', '全部从业人员数增长率(%)', '15.91', '38.33', '33.21');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('14', '总资产增长率(%)', '53.66', '33.57', '142.02');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('15', '应收账款周转率(%)', '14', '9.6', '12.9');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('16', '应收账款周转天数(天)', '25.8', '37.5', '27.8');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('17', '存货周转率(次)', '5.8', '5.8', '6');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('18', '存货周转天数(天)', '61.9', '62.5', '60.3');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('19', '固定资产周转率(次)', '3.9', '6.1', '8.8');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('20', '固定资产周转天数(天)', '92.6', '59.2', '40.7');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('21', '总资产周转率(次)', '0.9', '1', '1.3');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('22', '总资产周天数(天)', '421.7', '344.4', '270.9');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('23', '流动资产周转率(次)', '1.2', '1.5', '1.8');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('24', '流动资产周转天数(天)', '312.1', '247', '194.9');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('25', '股东权益周转率(次)', '1.1', '1.3', '1.8');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('26', '流动比率(-)', '3.47', '3.57', '3.05');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('27', '速动比率(-)', '3.23', '3.19', '2.73');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('28', '利息支付倍数(-)', '-58.24', '0', '-29.72');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('29', '股东权益比率(%)', '77.04', '79.03', '75.54');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('30', '长期负债比率(%)', '0', '1.24', '0.42');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('31', '股东权益比固定资产比率(%)', '325.18', '409.14', '525.87');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('32', '股东权益对负债比率(%)', '335.58', '376.96', '308.78');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('33', '权益乘数(-)', '1.28', '1.29', '1.36');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('34', '资本化比率(%)', '0', '1.54', '0.55');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('35', '资本固定化比率(%)', '30.75', '37.37', '35.28');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('36', '产权比率(%)', '29.8', '26.53', '32.39');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('37', '固定资产比重(%)', '23.69', '19.32', '14.36');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('38', '固定资产对长期负债比率(%)', '0', '1562.56', '3437.92');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('39', '资产负债率(%)', '22.96', '20.97', '24.46');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('40', '销售费用率（%）', '0.84', '0.73', '0.78');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('41', '管理费用率（%）', '2.73', '2.84', '2.69');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('42', '财务费用率（%）', '0.82', '0.75', '0.68');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('43', '息税前利润率（%）', '6.5', '6.2', '5.8');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('44', '销售税费率', '1.2', '1.1', '0.9');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('45', '应付账款周转率', '10', '8.5', '8.4');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('46', '现金流动负债率', '13.38', '12.25', '12.22');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('47', '营业外收支净额', '-52753.8', '-105632.1', '-148830.5');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('48', '资产保值增长率(%)', '43.78', '38.23', '105.27');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('49', '现金比率(%)', '123.5', '98.8', '116.8');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('50', '收益质量比率(%)', '87.3', '67.2', '65.3');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('51', '净利润现金保证比率(%)', '92.4', '96.3', '88.9');

INSERT INTO financial_index (ID, CWZBKM, "2011", "2010", "2009") VALUES ('52', '经营现金结构比率(%)', '45.7', '27.5', '54.3');

CASE WHEN 结构

##第一种写法

CASE case_value

    WHEN when_value THEN

        statement_list

    ELSE

        statement_list

END CASE;

##第二种写法

CASE WHEN case_value [=/>|<|<>] when_value THEN

        statement_list

    ELSE

        statement_list

END CASE;

/*第一种写法相当于 第二种写法为等号时的效果*/

        case when 循环是一个相对简单 也是比较常用的一种语法，在此语法上 两者没有区别 代码可以供用

        示例：测试数据中当字段 2009年 2010年 2011年    指标有三年连续递增的 输出字段values 值为true

select id,

cwzbkm,

"2009",

"2010",

"2011",

case

when "2009" < "2010" and "2010" < "2011" then

'true'

end as "values"

from financial_index

oracle 中应用 

postgresql中的应用 

IF 结构

IF condition

THEN

statement_list

ELSE

statement_list

END IF;

        oracle中if循环的应用

declare

v_a number := '&边长A';

v_b number := '&边长B';

v_c number := '&边长C';

begin

if (v_a+v_b) <= v_c or (v_a+v_c) <= v_b or (v_b+v_c) <= v_a then

dbms_output.put_line( '无法组成三角形');

elsif

v_a = v_b and v_b = v_c then

dbms_output.put_line( '等边三角形');

elsif

v_a = v_b or v_c = v_b or v_c = v_a then

dbms_output.put_line('等腰三角形');

else

dbms_output.put_line('一般三角形');

end if;

end;

         & --是Oracle中键盘输入的意思，每一个参数对应&后的字符不能相同 否则会定义为同一值。

        输入参数值 查看结果

postgresql中示例

do $$

declare

v_a integer := 1 ;

v_b integer := 4;

v_c integer := 2;

begin

if (v_a+v_b) <= v_c or (v_a+v_c) <= v_b or (v_b+v_c) <= v_a then

raise notice '无法组成三角形';

elsif

v_a = v_b and v_b = v_c then

raise notice '等边三角形';

elsif

v_a = v_b or v_c = v_b or v_c = v_a then

raise notice '等腰三角形';

else

raise notice '一般三角形';

end if;

end $$;

在postgresql中单次执行 使用DO  并带上一对$$  $$  作为包头包尾 框住代码块 

在case when 语法和if 语法中  都有一个“短路原则” 当执行到第一个被满足的case when 或者 if/elsif  的条件时  就会跳出循环体。二次单个IF 如何在没有其他控制语言操作的情况 只会循环一次。

loop 和IF 的结合应用 结构

LOOP

statement_list

IF exit_condition THEN

EXIT;

END IF;

END LOOP;

 postgresq中的应用示例

DO $$

DECLARE

v_a INTEGER := 5;

v_b INTEGER := 5;

v_c INTEGER := 7;

BEGIN

LOOP

EXIT WHEN (v_a + v_b + v_c)>100; --设置退出条件

IF v_a = v_b and v_c = v_b and v_c = v_a THEN

RAISE NOTICE '%,%,% 组成等边三角形', v_a, v_b, v_c;

ELSIF (v_a + v_b) > v_c THEN

RAISE NOTICE '%,%,% 组成一般三角形', v_a, v_b, v_c;

ELSIF v_a = v_b or v_c = v_b or v_c = v_a THEN

RAISE NOTICE '%,%,% 组成等边三角形', v_a, v_b, v_c;

else RAISE NOTICE '%,%,% 无法组成三角形', v_a, v_b, v_c;

END IF;

v_a := ROUND(RANDOM() * 10 + v_a);

v_b := ROUND(RANDOM() * 10 + v_b);

v_c := ROUND(RANDOM() * 10 + v_c);

END LOOP;

END $$;

Oracle中的语法示例

DECLARE

v_a INTEGER := 5;

v_b INTEGER := 2;

v_c INTEGER := 1;

BEGIN

LOOP

EXIT WHEN (v_a + v_b + v_c)>100; --设置退出条件

IF v_a = v_b AND v_c = v_b AND v_c = v_a THEN

DBMS_OUTPUT.PUT_LINE(v_a || ',' || v_b || ',' || v_c || ' 组成等边三角形');

ELSIF (v_a + v_b) > v_c THEN

DBMS_OUTPUT.PUT_LINE(v_a || ',' || v_b || ',' || v_c || ' 组成一般三角形');

ELSIF v_a = v_b OR v_c = v_b OR v_c = v_a THEN

DBMS_OUTPUT.PUT_LINE(v_a || ',' || v_b || ',' || v_c || ' 组成等腰三角形');

ELSE

DBMS_OUTPUT.PUT_LINE(v_a || ',' || v_b || ',' || v_c || ' 无法组成三角形');

END IF;

v_a := ROUND(DBMS_RANDOM.VALUE*10 + v_a);

v_b := ROUND(DBMS_RANDOM.VALUE*10 + v_b);

v_c := ROUND(DBMS_RANDOM.VALUE*10 + v_c);

END LOOP;

END;

LOOP 用于控制 IF无次数约束的循环  直到满足loop的退出条件为止。 

for 结构

FOR var IN 1..10 LOOP

statement_list

END LOOP;

loop和FOR 的结合应用

Oracle示例：

loop 的另类使用 添加 特殊变量SQL%NOTFOUND  关于特殊变量后续会再做详细介绍

SQL%NOTFOUND --如果循环语句中没有找到任何行，则退出循环体

DECLARE

ROW_RECCORD FINANCIAL_INDEX%rowtype;

BEGIN

FOR ROW_RECCORD IN (SELECT * FROM FINANCIAL_INDEX) LOOP

IF MOD(ROW_RECCORD.ID, 2) = 0 THEN

DBMS_OUTPUT.PUT_LINE('ID: ' || ROW_RECCORD.ID || ', ' || 'cwzbkm: ' ||

ROW_RECCORD.cwzbkm || ', ' || '2009: ' ||

ROW_RECCORD."2009" || ', ' || '2010: ' ||

ROW_RECCORD."2010" || ', ' || '2011: ' ||

ROW_RECCORD."2011" );

END IF;

END LOOP;

END;

执行完后查看效果

postgresql示例

do $$

DECLARE

ROW_RECCORD FINANCIAL_INDEX%rowtype;

BEGIN

FOR ROW_RECCORD IN SELECT * FROM FINANCIAL_INDEX LOOP

IF MOD(ROW_RECCORD.ID, 2) = 0 THEN

RAISE NOTICE 'ID: %, cwzbkm: %, 2009: %, 2010: %, 2011: %',

ROW_RECCORD.ID, ROW_RECCORD.cwzbkm, ROW_RECCORD."2009",

ROW_RECCORD."2010", ROW_RECCORD."2011";

END IF;

END LOOP;

END$$;

while  结构

while condition

loop

statement_list

end loop;

loop和whilie 的结合应用

oracle中的应用示例

/*

用存储过程对1-100数字进行求和计算

*/

declare

v_sum NUMBER := 0;

v_i NUMBER;

BEGIN

v_i := 1;

WHILE v_i <= 100 LOOP

v_sum := v_sum + v_i;

v_i := v_i+1;

END LOOP;

DBMS_OUTPUT.put_line('1-100的和是：'||v_sum);

END;

postgresql中的应用示例

/*

用存储过程对1-100数字进行求和计算

*/

do $$

declare

v_sum integer := 0;

v_i integer;

BEGIN

v_i := 1;

WHILE v_i <= 100 LOOP

v_sum := v_sum + v_i;

v_i := v_i+1;

END LOOP;

raise notice '1-100的和是：%',v_sum;

END$$;

loop 在循环中常常会伴随这if  ,case when ,while ,for等循环结构使用。

参考链接

 您阅读本篇文章共花了：