PL/SQL programming methods

PL/SQL programming method Daquan I. dynamic SQL splicing (using replace) www.2cto.com pay attention to using replace to achieve dynamic SQL splicing effect, v_psql can be used to pass variables [plain] 1 declare 2 a number: = 2; 3 v_ SQL varchar2 (4000): = 'select ename from # '; 4 v_psql varchar2 (2000 ): = 'emp' where rownum = '| 2 | ''; 5 begin 6 dbms_output.put_line (v_psql); 7 v_ SQL: = replace (v_ SQL,' # ', v_psql ); 8 dbms_output.put_line (v_ SQL); 9 * end; SQL>/emp where rownum = 2 select ename from emp where rownum = 2 PL /The SQL process has been completed successfully. Ii. Universal separation functions www.2cto.com [SQL] create or replace function split (p_string varchar2, p_sep varchar2: = ',' -- by default, they are separated by commas) return type_split pipelined -- The pipelined keyword specifies that the returned result set uses the pipe output, and the output is of the table type is v_l pls_integer; v_string varchar2 (30000): = p_string; begin loop v_l: = instr (v_string, p_sep); if v_l> 0 then pipe row (substr (v_string, 1, v_l-1); -- use the pipe function v_string: = substr (v_string, v_l + length (p_sep )); else pipe ro W (v_string); exit; end if; end loop; return; end split; Remember to create a PL/SQL table to save the output result: create or replace type type_split is table of varchar2 (1000) usage: select * from table (split ('12, aa, qwwq ,',',')); result COLUMN_VALUE 12 aa qwwq NULL 3. Obtain the name of the current function or stored procedure (generally used for error processing) [SQL] CREATE OR REPLACE FUNCTION f_getprocname RETURN VARCHAR2 -- get the name of the FUNCTION OR stored procedure and the caller IS l_owner VARCHAR2 (30); l_name VARCHAR2 (30); l_lineno NUMB ER; l_type VARCHAR2 (30); BEGIN OWA_UTIL.who_called_me (l_owner, l_name, l_lineno, l_type); RETURN l_owner | '. '| l_name; END; usage [SQL] create or replace procedure p_test as EX_P01 EXCEPTION ;... begin... exception when EX_P01 THEN RAISE_APPLICATION_ERROR (-20001, 'in' | f_getprocname | 'error: (' | sqlcode | ') --' | sqlerrm | ', please check! ');... End p_test;