62 lines
5.6 KiB
Text
62 lines
5.6 KiB
Text
|
<span class="hljs-comment">-- PL/pgSQL</span>
|
||
|
|
|
||
|
|
<span class="hljs-symbol"><< outerblock >></span>
|
||
|
|
<span class="hljs-keyword">DECLARE</span>
|
||
|
|
quantity <span class="hljs-type">integer</span> := <span class="hljs-number">30</span>;
|
||
|
|
subtotal <span class="hljs-keyword">ALIAS</span> <span class="hljs-keyword">FOR</span> <span class="hljs-meta">$1</span>;
|
||
|
|
prior <span class="hljs-keyword">ALIAS</span> <span class="hljs-keyword">FOR</span> <span class="hljs-built_in">old</span>;
|
||
|
|
arow <span class="hljs-type">record</span>;
|
||
|
|
curs1 <span class="hljs-type">refcursor</span>;
|
||
|
|
curs2 <span class="hljs-keyword">CURSOR</span> <span class="hljs-keyword">FOR</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> tenk1;
|
||
|
|
<span class="hljs-keyword">BEGIN</span>
|
||
|
|
<span class="hljs-keyword">DECLARE</span>
|
||
|
|
quantity <span class="hljs-keyword">CONSTANT</span> <span class="hljs-type">integer</span> := <span class="hljs-number">80</span>;
|
||
|
|
myrow tablename<span class="hljs-meta">%ROWTYPE</span>;
|
||
|
|
myfield tablename.columnname<span class="hljs-meta">%TYPE</span>;
|
||
|
|
<span class="hljs-keyword">BEGIN</span>
|
||
|
|
<span class="hljs-keyword">PERFORM</span> pg_sleep(<span class="hljs-number">1</span>);
|
||
|
|
<span class="hljs-keyword">RAISE</span> <span class="hljs-keyword">NOTICE</span> <span class="hljs-string">'Quantity here is %'</span>, quantity;
|
||
|
|
<span class="hljs-keyword">END</span>;
|
||
|
|
|
||
|
|
<span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">INTO</span> myrec <span class="hljs-keyword">FROM</span> emp <span class="hljs-keyword">WHERE</span> empname = myname;
|
||
|
|
|
||
|
|
<span class="hljs-keyword">IF</span> <span class="hljs-keyword">NOT</span> <span class="hljs-built_in">FOUND</span> <span class="hljs-keyword">THEN</span>
|
||
|
|
<span class="hljs-keyword">EXIT</span> <span class="hljs-symbol"><<outer_block>></span>;
|
||
|
|
<span class="hljs-keyword">ELSIF</span> quantity < <span class="hljs-number">0</span> <span class="hljs-keyword">THEN</span>
|
||
|
|
<span class="hljs-keyword">ASSERT</span> a > b, <span class="hljs-string">'Bad luck'</span>;
|
||
|
|
<span class="hljs-keyword">END</span> <span class="hljs-keyword">IF</span>;
|
||
|
|
|
||
|
|
<span class="hljs-keyword">FOR</span> r <span class="hljs-keyword">IN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> foo <span class="hljs-keyword">LOOP</span>
|
||
|
|
<span class="hljs-keyword">CONTINUE</span> <span class="hljs-keyword">WHEN</span> count < <span class="hljs-number">50</span>;
|
||
|
|
<span class="hljs-keyword">END</span> <span class="hljs-keyword">LOOP</span>;
|
||
|
|
|
||
|
|
<span class="hljs-keyword">FOR</span> i <span class="hljs-keyword">IN</span> <span class="hljs-keyword">REVERSE</span> <span class="hljs-number">10.</span><span class="hljs-number">.1</span> <span class="hljs-keyword">LOOP</span>
|
||
|
|
<span class="hljs-keyword">FOREACH</span> x <span class="hljs-keyword">IN</span> <span class="hljs-keyword">ARRAY</span> <span class="hljs-meta">$1</span>
|
||
|
|
<span class="hljs-keyword">LOOP</span>
|
||
|
|
s := s + x;
|
||
|
|
<span class="hljs-keyword">END</span> <span class="hljs-keyword">LOOP</span>;
|
||
|
|
<span class="hljs-keyword">END</span> <span class="hljs-keyword">LOOP</span>;
|
||
|
|
|
||
|
|
<span class="hljs-keyword">WHILE</span> <span class="hljs-keyword">NOT</span> done <span class="hljs-keyword">LOOP</span>
|
||
|
|
<span class="hljs-keyword">CASE</span> x
|
||
|
|
<span class="hljs-keyword">WHEN</span> <span class="hljs-number">1</span>, <span class="hljs-number">2</span> <span class="hljs-keyword">THEN</span> <span class="hljs-keyword">RETURN NEXT</span> r;
|
||
|
|
<span class="hljs-keyword">ELSE</span> <span class="hljs-keyword">RETURN QUERY</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> sales;
|
||
|
|
<span class="hljs-keyword">END</span> <span class="hljs-keyword">CASE</span>;
|
||
|
|
<span class="hljs-keyword">END</span> <span class="hljs-keyword">LOOP</span>;
|
||
|
|
|
||
|
|
<span class="hljs-keyword">EXECUTE</span> <span class="hljs-string">'SELECT count(*) FROM mytable WHERE inserted_by = $1'</span> <span class="hljs-keyword">INTO</span> c <span class="hljs-keyword">USING</span> checked_user;
|
||
|
|
|
||
|
|
<span class="hljs-keyword">OPEN</span> curs1 <span class="hljs-keyword">SCROLL FOR</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> foo <span class="hljs-keyword">WHERE</span> key = mykey;
|
||
|
|
<span class="hljs-keyword">FETCH LAST</span> <span class="hljs-keyword">FROM</span> curs1 <span class="hljs-keyword">INTO</span> x, y;
|
||
|
|
<span class="hljs-keyword">MOVE RELATIVE</span> <span class="hljs-number">-2</span> <span class="hljs-keyword">FROM</span> curs1;
|
||
|
|
<span class="hljs-keyword">UPDATE</span> foo <span class="hljs-keyword">SET</span> dataval = myval <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">CURRENT</span> <span class="hljs-keyword">OF</span> curs1;
|
||
|
|
<span class="hljs-keyword">CLOSE</span> curs1;
|
||
|
|
|
||
|
|
<span class="hljs-keyword">RETURN</span> quantity;
|
||
|
|
<span class="hljs-keyword">EXCEPTION</span>
|
||
|
|
<span class="hljs-keyword">WHEN</span> <span class="hljs-built_in">NO_DATA_FOUND</span> <span class="hljs-keyword">THEN</span>
|
||
|
|
<span class="hljs-keyword">GET</span> <span class="hljs-keyword">DIAGNOSTICS</span> integer_var = <span class="hljs-built_in">ROW_COUNT</span>;
|
||
|
|
<span class="hljs-keyword">WHEN</span> <span class="hljs-built_in">SQLSTATE</span> <span class="hljs-string">'22012'</span> <span class="hljs-keyword">THEN</span>
|
||
|
|
<span class="hljs-keyword">NULL</span>;
|
||
|
|
<span class="hljs-keyword">END</span>;
|