728x90
반응형
PL/SQL 14버전 전까지는 OUT 이 없음. IN, INOUT만 존재 함.
DBMS_OUTPUT.PUT_LINE('I got here:'||:new.col||' is the new value'); --> RAISE NOTICE 'I got here:% is the new value', NEW.col;
프로시저는 V11부터 사용가능
Anonymous
| \timing on create table t( id serial primary key, n numeric not null ); insert into t(n) select 1 from generate_series(1,100000); do $$ begin for i in 1..150 loop for j in 1..10000 loop insert into part_test_tri values ( i , j , to_date('20211231','YYYYMMDD')+i ); end loop; end loop; end $$; do $$ declare i record; begin for i in (select id from t) loop execute 'update t set n = n + 1 where id = '||i.id; end loop; end; $$ language plpgsql; |
For Loop with SQL
| CREATE OR REPLACE FUNCTION emp_members() RETURNS NUMERIC AS $$ DECLARE r RECORD; total NUMERIC := 0; BEGIN FOR r IN SELECT * FROM emp LOOP total := total + 1; RAISE NOTICE 'empno : % ename : % salary : %' , r.empno ,r.ename, r.sal ; END LOOP; RETURN total; END; $$ LANGUAGE plpgsql; select emp_members() ; NOTICE: empno : 7369 ename : SMITH salary : 800.00 NOTICE: empno : 7499 ename : ALLEN salary : 1600.00 NOTICE: empno : 7521 ename : WARD salary : 1250.00 NOTICE: empno : 7566 ename : JONES salary : 2975.00 NOTICE: empno : 7654 ename : MARTIN salary : 1250.00 NOTICE: empno : 7698 ename : BLAKE salary : 2850.00 NOTICE: empno : 7782 ename : CLARK salary : 2450.00 NOTICE: empno : 7788 ename : SCOTT salary : 3000.00 NOTICE: empno : 7839 ename : KING salary : 5000.00 NOTICE: empno : 7844 ename : TURNER salary : 1500.00 NOTICE: empno : 7876 ename : ADAMS salary : 1100.00 NOTICE: empno : 7900 ename : JAMES salary : 950.00 NOTICE: empno : 7902 ename : FORD salary : 3000.00 NOTICE: empno : 7934 ename : MILLER salary : 1300.00 +-------------+ | emp_members | +-------------+ | 14 | +-------------+ |
| CREATE OR REPLACE PROCEDURE p_emp_members() AS $$ DECLARE r RECORD; BEGIN FOR r IN SELECT * FROM emp LOOP RAISE NOTICE 'empno : % ename : % salary : %' , r.empno ,r.ename, r.sal ; END LOOP; END; $$ LANGUAGE plpgsql; call p_emp_members() ; NOTICE: empno : 7369 ename : SMITH salary : 800.00 NOTICE: empno : 7499 ename : ALLEN salary : 1600.00 NOTICE: empno : 7521 ename : WARD salary : 1250.00 NOTICE: empno : 7566 ename : JONES salary : 2975.00 NOTICE: empno : 7654 ename : MARTIN salary : 1250.00 NOTICE: empno : 7698 ename : BLAKE salary : 2850.00 NOTICE: empno : 7782 ename : CLARK salary : 2450.00 NOTICE: empno : 7788 ename : SCOTT salary : 3000.00 NOTICE: empno : 7839 ename : KING salary : 5000.00 NOTICE: empno : 7844 ename : TURNER salary : 1500.00 NOTICE: empno : 7876 ename : ADAMS salary : 1100.00 NOTICE: empno : 7900 ename : JAMES salary : 950.00 NOTICE: empno : 7902 ename : FORD salary : 3000.00 NOTICE: empno : 7934 ename : MILLER salary : 1300.00 |
create table normal ( id int ) ;
| DO $$ DECLARE v_cnt int ; v_err varchar(255) ; v_user_err varchar(255) := 'User Error' ; BEGIN delete from normal where id = 1 ; -- 영향 받는 데이터 0 건 GET DIAGNOSTICS v_cnt = ROW_COUNT; -- sql%rowcount RAISE NOTICE '%',v_cnt ; -- dbms_output.put_line select 1/v_cnt ; -- 1/0 으로 하려니 에러 발생 exception when others then v_err := sqlerrm ; -- raise exception using message = v_err ; -- raise_application_error (sqlerrm 출력) raise exception using message = v_user_err ; -- user Error 출력 END; $$ LANGUAGE plpgsql; |
| NOTICE: 0 ERROR: User Error CONTEXT: PL/pgSQL function inline_code_block line 15 at RAISE (14861) |
| DO $$ DECLARE v_cnt int ; v_err varchar(255) ; v_user_err varchar(255) := 'User Error' ; BEGIN delete from normal where id = 1 ; -- 영향 받는 데이터 0 건 GET DIAGNOSTICS v_cnt = ROW_COUNT; -- sql%rowcount RAISE NOTICE '%',v_cnt ; -- dbms_output.put_line select 1/v_cnt ; -- 1/0 으로 하려니 에러 발생 exception when others then v_err := sqlerrm ; raise exception using message = v_err ; -- raise_application_error (sqlerrm 출력) --raise exception using message = v_user_err ; -- user Error 출력 END; $$ LANGUAGE plpgsql; |
| NOTICE: 0 ERROR: division by zero (11385) CONTEXT: PL/pgSQL function inline_code_block line 14 at RAISE (14861) |
Procedure에서 Function call
| CREATE OR REPLACE PROCEDURE p_test() LANGUAGE plpgsql AS $$ DECLARE v_return varchar(20) ; BEGIN --v_return := execute f_test(); select f_test() into v_return ; RAISE NOTICE '%',v_return ; END; $$; CREATE OR REPLACE FUNCTION f_test() RETURNS varchar AS $$ BEGIN RETURN 'executed f_test'; END; $$ LANGUAGE plpgsql; postgres=# call p_test() ; NOTICE: executed f_test |
반응형