SQL: Bind Variable

Create Table 

CREATE TABLE ch13_physicist(

    ids       NUMBER,

    names    VARCHAR2(20),

    birth_dt    DATE);

 

Declare

    vn_emp_id    employees.employee_id%type;

    vn_emp_name employees.emp_name%TYPE;

    vn_job_id      employees.job_id%TYPE;

    vs_job    employees.job_id%TYPE := 'SA_REP'

    vn_sal    employees.salary%TYPE := 7000;

    vn_manager    employees.manager%TYPE := 148;

    vs_sql    VARCHAR2(1000);

 

Select Using Bind Variables

BEGIN

    vs_sql := 'INSERT INTO ch13_physicist VALUES (:a, :a, :a)';

    EXECUTE IMMEDIATE vs_sql USING vn_ids, vs_name, vd_dt

END;

 

Insert Using Bind Variables

    vs_sql := 'SELECT    employee_id. emp_name, job_id

                    FROM        employees

                    WHERE     job_id := a

                    AND           salsary <= :b

                    AND           manager_id = :c';

 

BEGIN

    vs_sql := '

    EXECUTE IMMEDIATE vs_sql INTO vn_emp_id, vs_emp_name, vs_job_id USING vs_job, vn_sal, vn_manager;

    EXECUTE IMMEDIATE vs_sql INTO vn_emp_id, vs_emp_name, vs_job_id USING vs.job, vn_sal, vn_manager;

    COMMIT;

END;

 

 

 

Delete Using Bind Variable

 

DECLARE

    vn_ids    ch13_physicist.ids%TYPE := 10;

    vs_name    ch13_physicist.name%TYPE := 'Max';

    vd_dt    ch13_physicist.birth_dt%TYPE := TO_DATE('18580423','YYYYMMDD');

    

    vs_sql    VARCHAR2(1000);

    vn_cnt    NUMBER := 0;

 

BEGIN

    vs_sql := 'UPDATE ch13_physicist SET names = :a, birth_dt = :a WHERE ids = :a';

    EXECUTE IMMEDIATE vs_sql INTO vn_emp_id, vs_emp_name, vs_job_id USING vs_job, vn_sal, vn_manager;

END;

 

DECLARE

    vn_ids    ch13_physicist.ids%TYPE := 10;

    vs_name    ch13_physicist.names%TYPE := 'Albert';

    vd_dt    ch13_physicist.birth_dt%TYPE := TO_DATE('1879-03-14','YYYY-MM-DD');

 

BEGIN

    vs_sql := 'UPDATE ch13_physicist SET names = :a, birth_dt = :a WHERE ids = :a';

    EXECUTE IMMEDIATE vs_sql INTO vn_emp_id, vs_emp_name, vs_job_id USING vs_job, vn_sal, vn_manager;

END;

 

Delete Using Bind Variables

 

DECLARE

    vn_ids    ch13_physicist.ids%TYPE := 10;

 

vs_sql := 'DELETE ch13_physicist WHERE ids=:a';

EXECUTE IMMEDIATE vs_sql USING vn_ids;