Home > PL/SQL > Dynamic SQL statement or an anonymous PL/SQL block

Dynamic SQL statement or an anonymous PL/SQL block

Tuesday, 20 January, 2009 Leave a comment Go to comments

Some programs must build and process SQL statements where some information is not known in advance. A reporting application might build different SELECT statements for the various reports it generates, substituting new table and column names and ordering or grouping by different columns. Database management applications might issue statements such as CREATE, DROP, and GRANT that cannot be coded directly in a PL/SQL program. These statements are called dynamic SQL statements.

Dynamic SQL statements built as character strings built at run time. The strings contain the text of a SQL statement or PL/SQL block. They can also contain placeholders for bind arguments. Placeholder names are prefixed by a colon, and the names themselves do not matter. For example, PL/SQL makes no distinction between the following strings:
'DELETE FROM emp WHERE sal > :my_sal AND comm < :my_comm'
'DELETE FROM emp WHERE sal > :s AND comm < :c'

To process most dynamic SQL statements, you use the EXECUTE IMMEDIATE statement. To process a multi-row query (SELECT statement), you use the OPEN-FOR, FETCH, and CLOSE statements.

The following PL/SQL block contains several examples of dynamic SQL:

DECLARE
sql_stmt    VARCHAR2(200);
plsql_block VARCHAR2(500);
emp_id      NUMBER(4) := 7566;
salary      NUMBER(7,2);
dept_id     NUMBER(2) := 50;
dept_name   VARCHAR2(14) := 'PERSONNEL';
location    VARCHAR2(13) := 'DALLAS';
emp_rec     emp%ROWTYPE;
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';
sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;';
EXECUTE IMMEDIATE plsql_block USING 7788, 500;
sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1
RETURNING sal INTO :2';
EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;
EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'
USING dept_id;
EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
END;
/

Using:

DECLARE
vsAccounttypeStr   VARCHAR2(50);CURSOR cSaldoNotNull IS
SELECT t.accountno AS accnum, t.remain AS CMS
FROM taccount t
WHERE t.accounttype &lt;&gt; 0
AND (instr(vsAccounttypeStr, '~' || ltrim(rtrim(to_char(t.accounttype))) || '~') != 0
OR vsAccounttypeStr = '~')
AND t.remain &lt;&gt; 0;FUNCTION SaldoDifferenceExists RETURN BOOLEAN IS
vbExists BOOLEAN;
vnCnt    NUMBER := 0;
BEGIN
SELECT nvl(COUNT(*), 0) INTO vnCnt FROM user_all_tables WHERE TABLE_NAME = upper('tSaldoDifference');
IF vnCnt = 0
THEN
vbExists := FALSE;
ELSE
vbExists := TRUE;
END IF;
RETURN vbExists;
END SaldoDifferenceExists;
...

PROCEDURE SaldoDifferenceCreate IS
v_SQLString VARCHAR2(1000);
BEGIN
v_SQLString := 'create table a4m.tSaldoDifference(accountno varchar2(20),cms number, rbo number, difference number, regdate date)';
EXECUTE IMMEDIATE v_SQLString;
END SaldoDifferenceCreate;
...

--DML operations
PROCEDURE up_tSaldoDifference(v_Accountno taccount.ACCOUNTNO%TYPE, v_cms NUMBER, v_rbo NUMBER, v_diff NUMBER, v_date DATE, v_regdate DATE) IS
vnCnt       NUMBER := 0;
v_cms_saldo NUMBER;
v_rbo_saldo NUMBER;
v_SQLQuery  VARCHAR2(2000);
BEGIN
v_SQLQuery := 'SELECT nvl(COUNT(*), 0) as cnt FROM a4m.tSaldoDifference WHERE upper(accountno) = upper(:v_Accountno)';
EXECUTE IMMEDIATE v_SQLQuery
INTO vnCnt
USING v_Accountno;
IF vnCnt = 0
THEN
v_SQLQuery := 'INSERT INTO a4m.tSaldoDifference VALUES (:v_Accountno, :v_cms, :v_rbo, :v_diff, :v_date)';
EXECUTE IMMEDIATE v_SQLQuery
USING v_Accountno, v_cms, v_rbo, v_diff, v_date;
COMMIT;
ELSE
v_SQLQuery := 'SELECT nvl(cms, 0) as cms_saldo, nvl(rbo, 0) as rbo_saldo FROM a4m.tSaldoDifference WHERE upper(accountno) = upper(:v_Accountno)';
EXECUTE IMMEDIATE v_SQLQuery
INTO v_cms_saldo, v_rbo_saldo
USING v_Accountno;
IF (v_cms &lt;&gt; v_cms_saldo) OR (v_rbo &lt;&gt; v_rbo_saldo)
THEN
v_SQLQuery := 'UPDATE a4m.tSaldoDifference SET cms = :v_cms, rbo = :v_rbo, difference = :v_diff, regdate = :v_date WHERE upper(accountno) = upper(:v_Accountno)';
EXECUTE IMMEDIATE v_SQLQuery
USING v_cms, v_rbo, v_diff, v_date, v_Accountno;
COMMIT;
END IF;
END IF;
END up_tSaldoDifference;

...

BEGIN
...
IF NOT SaldoDifferenceExists
THEN
BEGIN
SaldoDifferenceCreate;
EXCEPTION
WHEN OTHERS THEN

('Ошибка при создании таблицы a4m.tSaldoDifference:' || SQLERRM);
RETURN;
END;
END IF;
...
vsAccounttypeStr:='~1~2~5~9~';
...
FOR i IN cSaldoNotNull --cursor
LOOP
up_tSaldoDifference(i.accnum, abs(nvl(i.CMS, 0)), abs(nvl(vRBOSaldo, 0)), nvl(vRBOSaldo, 0) - nvl(i.cms, 0), SYSDATE,null);
END LOOP;
...
END;
/
<a href="http://www.sc.ehu.es/siwebso/KZCC/Oracle_10g_Documentacion/appdev.101/b10807/13_elems017.htm">For more information</a>

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: