基于开发者空间GaussDB云数据库的PLpgSQL实践一

[ <<标签>> ] [ 声明 变量; 变量 := 赋值 ] BEGIN SQL QUERY END [ 标签]; 

name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := | = } expression ]; 

DECLARE var1 TEXT; var2 INTEGER := 10; url VARCHAR; quantity NUMERIC(5); myrow tablename%ROWTYPE; myfield tablename.columnname%TYPE; arow RECORD; CURSOR c1 IN SELECT col1, col2 FROM table_name WHER 谓词过滤条件; 

DECLARE quantity integer DEFAULT 32; url varchar := 'http://mysite.com'; transaction_time CONSTANT timestamp with time zone := now(); tax := subtotal * 0.06; my_record.user_id := 20; my_array[j] := 20; my_array[1:3] := array[1,2,3]; complex_array[n].realpart = 12.3; 

DECLARE x integer := 1; y integer := x + 1; 

CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$ BEGIN RETURN subtotal * 0.06; END; $$ LANGUAGE plpgsql; 

name ALIAS FOR $n; 

DROP FUNCTION IF EXISTS sales_tax(); CREATE OR REPLACE FUNCTION sales_tax(real) RETURNS real AS $$ DECLARE subtotal ALIAS FOR $1; BEGIN RETURN subtotal * 0.06; END; $$ LANGUAGE plpgsql; 

CREATE TABLE test(f1 int, f3 int, f5 int, f7 int); CREATE FUNCTION concat_selected_fields(in_t test) RETURNS text AS $$ BEGIN RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7; END; $$ LANGUAGE plpgsql; 

CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$ DECLARE v_string ALIAS FOR $1; index ALIAS FOR $2; BEGIN -- some computations using v_string and index here END; $$ LANGUAGE plpgsql; 

CREATE OR REPLACE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$ BEGIN tax := subtotal * 0.06; END; $$ LANGUAGE plpgsql; 

SELECT sales_tax(100.00); 

CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$ BEGIN sum := x + y; prod := x * y; END; $$ LANGUAGE plpgsql; SELECT * FROM sum_n_product(2, 4); 

DROP FUNCTION sum_n_product; CREATE PROCEDURE sum_n_product(x int, y int, OUT sum int, OUT prod int) AS BEGIN sum := x + y; prod := x * y; END; 

CALL sum_n_product(2, 4, NULL, NULL); 

CREATE FUNCTION extended_sales(p_itemno int) RETURNS TABLE(quantity int, total numeric) AS $$ BEGIN RETURN QUERY SELECT s.quantity, s.quantity * s.price FROM sales AS s WHERE s.itemno = p_itemno; END; $$ LANGUAGE plpgsql; 

CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement) RETURNS anyelement AS $$ DECLARE result ALIAS FOR $0; BEGIN result := v1 + v2 + v3; RETURN result; END; $$ LANGUAGE plpgsql; 

DROP FUNCTION IF EXISTS add_three_values; CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement, OUT sum anyelement) AS $$ BEGIN sum := v1 + v2 + v3; END; $$ LANGUAGE plpgsql; 

CREATE FUNCTION add_three_values(v1 anycompatible, v2 anycompatible, v3 anycompatible) RETURNS anycompatible AS $$ BEGIN RETURN v1 + v2 + v3; END; $$ LANGUAGE plpgsql; 

SELECT add_three_values(1, 2, 4.7); 

newname ALIAS FOR oldname; 

DECLARE prior ALIAS FOR old; updated ALIAS FOR new; 

name table.column%TYPE name variable%TYPE 

declare variable users.user_id%TYPE; 

declare variables users.user_id%TYPE[]; variables users.user_id%TYPE ARRAY[4]; -- equivalent to the above 

CREATE TABLE sal_emp ( name text, pay_by_quarter integer[], schedule text[][] ); CREATE TABLE tictactoe ( pay_by_quarter integer ARRAY, sales integer ARRAY[4], squares integer[3][3] ); 

name table_name%ROWTYPE; name composite_type_name; 

name RECORD; 

CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$ BEGIN RETURN a < b; END; $$ LANGUAGE plpgsql; 

SELECT less_than(f1, f3) FROM test; 

CREATE FUNCTION less_than_1 (a text, b text) RETURNS boolean AS $$ DECLARE local_a text := a; local_b text := b; BEGIN RETURN local_a < local_b; END; $$ LANGUAGE plpgsql; 

CREATE FUNCTION less_than_c(a text, b text) RETURNS boolean AS $$ BEGIN RETURN a < b COLLATE "C"; END; $$ LANGUAGE plpgsql; 

IF ... THEN ... END IF IF ... THEN ... ELSE ... END IF IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF 

CASE ... WHEN ... THEN ... ELSE ... END CASE CASE WHEN ... THEN ... ELSE ... END CASE 

IF boolean-expression THEN statements END IF; 

IF v_user_id <> 0 THEN UPDATE users SET email = v_email WHERE user_id = v_user_id; END IF; 

IF boolean-expression THEN statements ELSE statements END IF; 

IF parentid IS NULL OR parentid = '' THEN RETURN fullname; ELSE RETURN hp_true_filename(parentid) || '/' || fullname; END IF; IF v_count > 0 THEN INSERT INTO users_count (count) VALUES (v_count); RETURN 't'; ELSE RETURN 'f'; END IF; 

IF boolean-expression THEN statements [ ELSIF boolean-expression THEN statements [ ELSIF boolean-expression THEN statements ... ] ] [ ELSE statements ] END IF; 

IF number = 0 THEN result := 'zero'; ELSIF number > 0 THEN result := 'positive'; ELSIF number < 0 THEN result := 'negative'; ELSE -- hmm, the only other possibility is that number is null result := 'NULL'; END IF; IF demo_row.sex = 'm' THEN pretty_sex := 'man'; ELSE IF demo_row.sex = 'f' THEN pretty_sex := 'woman'; END IF; END IF; 

CASE search-expression WHEN expression [, expression [ ... ]] THEN statements [ WHEN expression [, expression [ ... ]] THEN statements ... ] [ ELSE statements ] END CASE; 

CASE x WHEN 1, 2 THEN msg := 'one or two'; ELSE msg := 'other value than one or two'; END CASE; SELECT * INTO myrec FROM emp WHERE empname = myname; IF NOT FOUND THEN RAISE EXCEPTION 'employee % not found', myname; END IF; BEGIN SELECT * INTO STRICT myrec FROM emp WHERE empname = myname; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE EXCEPTION 'employee % not found', myname; WHEN TOO_MANY_ROWS THEN RAISE EXCEPTION 'employee % not unique', myname; END; 

CASE WHEN boolean-expression THEN statements [ WHEN boolean-expression THEN statements ... ] [ ELSE statements ] END CASE; 

CASE WHEN x BETWEEN 0 AND 10 THEN msg := 'value is between zero and ten'; WHEN x BETWEEN 11 AND 20 THEN msg := 'value is between eleven and twenty'; END CASE; 

[ <<label>> ] LOOP statements END LOOP [ label ]; 

EXIT [ label ] [ WHEN boolean-expression ]; 

LOOP -- some computations IF count > 0 THEN EXIT; -- exit loop END IF; END LOOP; LOOP -- some computations EXIT WHEN count > 0; -- same result as previous example END LOOP; <<ablock>> BEGIN -- some computations IF stocks > 100000 THEN EXIT ablock; -- causes exit from the BEGIN block END IF; -- computations here will be skipped when stocks > 100000 END; 

CONTINUE [ label ] [ WHEN boolean-expression ]; 

LOOP -- some computations EXIT WHEN count > 100; CONTINUE WHEN count < 50; -- some computations for count IN [50 .. 100] END LOOP; 

[ <<label>> ] WHILE boolean-expression LOOP statements END LOOP [ label ]; 

WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP -- some computations here END LOOP; WHILE NOT done LOOP -- some computations here END LOOP; 

[ <<label>> ] FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOP statements END LOOP [ label ]; 

-- 迭代变量i增序遍历，从1到10，增量默认为1 FOR i IN 1..10 LOOP -- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop END LOOP; -- 迭代变量i降序遍历(REVERSE)，从10到1，降量默认为1 FOR i IN REVERSE 10..1 LOOP -- i will take on the values 10,9,8,7,6,5,4,3,2,1 within the loop END LOOP; -- 迭代变量i降序遍历(REVERSE)，从10到1，降量默认为2(BY 2) FOR i IN REVERSE 10..1 BY 2 LOOP -- i will take on the values 10,8,6,4,2 within the loop END LOOP; 

[ <<label>> ] FOR target IN query LOOP statements END LOOP [ label ]; 

CREATE FUNCTION refresh_mviews() RETURNS integer AS $$ DECLARE mviews RECORD; BEGIN RAISE NOTICE 'Refreshing all materialized views...'; FOR mviews IN SELECT n.nspname AS mv_schema, c.relname AS mv_name, pg_catalog.pg_get_userbyid(c.relowner) AS owner FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace) WHERE c.relkind = 'm' ORDER BY 1 LOOP -- Now "mviews" has one record with information about the materialized view RAISE NOTICE 'Refreshing materialized view %.% (owner: %)...', quote_ident(mviews.mv_schema), quote_ident(mviews.mv_name), quote_ident(mviews.owner); EXECUTE format('REFRESH MATERIALIZED VIEW %I.%I', mviews.mv_schema, mviews.mv_name); END LOOP; RAISE NOTICE 'Done refreshing materialized views.'; RETURN 1; END; $$ LANGUAGE plpgsql; 

[ <<label>> ] FOR target IN EXECUTE text_expression [ USING expression [, ... ] ] LOOP statements END LOOP [ label ]; 

[ <<label>> ] FOREACH target [ SLICE number ] IN ARRAY expression LOOP statements END LOOP [ label ]; 

CREATE FUNCTION sum_a (int[]) RETURNS int8 AS $$ DECLARE s int8 := 0; x int; BEGIN FOREACH x IN ARRAY $1 LOOP s := s + x; END LOOP; RETURN s; END; $$ LANGUAGE plpgsql; 

CREATE FUNCTION scan_rows(int[]) RETURNS void AS $$ DECLARE x int[]; BEGIN FOREACH x SLICE 1 IN ARRAY $1 LOOP RAISE NOTICE 'row = %', x; END LOOP; END; $$ LANGUAGE plpgsql; SELECT scan_rows(ARRAY[[1,2,3],[4,5,6],[7,8,9],[10,11,12]]); 

[ <<label>> ] [ DECLARE declarations ] BEGIN statements EXCEPTION WHEN condition [ OR condition ... ] THEN handler_statements [ WHEN condition [ OR condition ... ] THEN handler_statements ... ] END; 

WHEN division_by_zero THEN ... WHEN SQLSTATE '22012' THEN ... 

INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones'); BEGIN UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones'; x := x + 1; y := x / 0; EXCEPTION WHEN division_by_zero THEN RAISE NOTICE 'caught division_by_zero'; RETURN x; END; 

GET STACKED DIAGNOSTICS variable { = | := } item [ , ... ]; 

DECLARE text_var1 text; text_var2 text; text_var3 text; BEGIN -- some processing which might cause an exception ... EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT, text_var2 = PG_EXCEPTION_DETAIL, text_var3 = PG_EXCEPTION_HINT; END; 

BEGIN y := x / 0; EXCEPTION WHEN division_by_zero THEN NULL; -- ignore the error END; 

create or replace procedure cursor_function() as declare var1 int; var2 int; cursor c1 for select va1, va2 from table where xxx; begin open c1; loop fetch c1 into var1, var2; exit when c1%notfound; body; raise notice 'xx%, xx%',var1,var2; end loop; close c1; end; / 

create table student (id int,vdate timestamp); create or replace procedure ins_student(num int) as declare id int:= 100000; var int; jishu int; begin for var in 1 .. num loop insert into student values (id, now()); id := id + 1; end loop; select count(*) into jishu from student; raise info '已插入%行, 目前student表共有%行', num, jishu; end; / 

create table sjh_cursor (a int,b int,c int); insert into sjh_cursor values(1,2,3); insert into sjh_cursor values(4,5,6); --创建游标，使用游标从表里查询并输出2字段 create or replace procedure pro_sjh() as declare cursor c1 is select a, b from sjh_cursor; var1 int; var2 int; begin open c1; loop fetch c1 into var1, var2; exit when c1%notfound; raise notice 'sjh_cursor表a列数据为: %, b列数据为: %', var1, var2; end loop; close c1; end; / call pro_sjh(); 

create or replace procedure avg_score(id int,coursename varchar(20) ,avgscore out int) as begin case when coursename='math' then select avg(math) into avgscore from student where student_id=id; when coursename='pysical' then select avg(pysical) into avgscore from student where student_id=id; when coursename='music' then select avg(music) into avgscore from student where student_id=id; when coursename='art' then select avg(art) into avgscore from student where student_id=id; end case; end; /