Bind Variable
create in sql and refer in plsql
create procedure p2(n1 in number,n2 in number,n3 out number) is
2 begin
3 n3 := n1+n2;
4 dbms_output.put_line(n3);
5 end;
6 /
Procedure created.
SQL> var n3 number;
SQL> exec p2(23,34,:n3);
57
PL/SQL procedure successfully completed.
SQL> print n3;
N3
----------
57
SQL> print :n3;
N3
----------
57
Dynamic SQL
Example 1:
DECLARE
var_user VARCHAR2(30);
BEGIN
SELECT user INTO var_user FROM dual;
DBMS_OUTPUT.PUT_LINE (''Current User is ''||var_user);
END;
SET SERVEROUTPUT ON;
DECLARE
plsql_blk VARCHAR2 (250);
BEGIN
plsql_blk := 'DECLARE
var_user VARCHAR2 (10);
BEGIN
SELECT user INTO var_user FROM dual;
DBMS_OUTPUT.PUT_LINE (''Current User is
''||var_user);
END;';
EXECUTE IMMEDIATE plsql_blk;
END;
Example 2:
CREATE OR REPLACE FUNCTION circle_area (radius NUMBER)
RETURN NUMBER IS
pi CONSTANT NUMBER(7,2) := 3.141;
area NUMBER(7,2);
BEGIN
area := pi * ( radius * radius );
RETURN area;
END;
SET SERVEROUTPUT ON;
DECLARE
plsql_blk VARCHAR2 (500);
BEGIN
plsql_blk :='
CREATE OR REPLACE FUNCTION circle_area (radius NUMBER)
RETURN NUMBER IS
pi CONSTANT NUMBER(7,2) := 3.141;
area NUMBER(7,2);'||
‘BEGIN
area := pi * (radius * radius);
RETURN area;
END;';
EXECUTE IMMEDIATE plsql_blk;
END;
Example 3:
Create or replace procedure drop_table(n in varchar2) is
Begin
Execute immediate ‘DROP TABLE ‘||n);
End;
SET SERVEROUTPUT ON;
DECLARE
sql_smt VARCHAR2 (150);
BEGIN
sql_smt := 'INSERT INTO students (stud_name) VALUES (:stud_name)';
EXECUTE IMMEDIATE sql_smt USING 'Steve';
END;
Set Serveroutput On;
Declare
Sql_Smt Varchar2(150);
Begin
Sql_Smt := 'UPDATE students SET stud_name = :new_name
WHERE stud_name = :old_name ';
Execute Immediate Sql_Smt Using 'Leo','Steve';
End;
Bulk Collect with Execute Immediate:
SET SERVEROUTPUT ON;
DECLARE
TYPE nt_Fname IS TABLE OF VARCHAR2 (60);
fname nt_Fname;
sql_qry VARCHAR2(150);
BEGIN
sql_qry := 'SELECT first_name FROM employees';
EXECUTE IMMEDIATE sql_qry BULK COLLECT INTO fname;
FOR idx IN 1..fname.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(idx||' - '||fname(idx));
END LOOP;
END;
