Dynamic SQL (Learning notes)

Dynamic SQL

EXECUTE IMMEDIATE Dynamic SQL string [BUCK COLLECT] into custom variable, | record type

USING [In | Out| In out] binding parameters

[Returning | RETURN][bulk Collect]into Binding parameters

Example 1

--Enter employee information according to the employee ID entered in the record

DECLAREV_sql_smstVARCHAR2( $);--define variables to hold SQL statementsV_emp EMP%ROWTYPE;--Define a measure line variablev_id Emp.empno%TYPE:=&Empno--define the ID of the user inputBEGINV_sql_smst:='SELECT * from EMP WHERE empno=:empno';EXECUTEIMMEDIATE V_sql_smst intov_emp USING V_id;dbms_output.put_line ('Employee Number:'||v_id||'Name:'||V_emp.ename||'Job Title'||v_emp.job);END;

Dynamic cursors return multiple rows of data

--Return employee information according to the salary entered

DECLAREcur_emp Sys_refcursor; --Defining CursorsV_sal Emp.sal%TYPE:=&Sal--Define user input PayrollV_emp EMP%ROWTYPE;--Defining row VariablesBEGIN  OPENCur_emp for 'SELECT * from EMP WHERE sal>:sal ORDER by SAL'USING v_sal;--opens a cursor and executes multiple rows of SQL query, returning the resultDbms_output.put_line ('Wages above'||V_sal||'employees are:'); LOOPFETCHCur_emp intov_emp; EXIT  whenCur_emp%NOTFOUND; Dbms_output.put_line ('Employee Number:'||V_emp.empno||'Name:'||V_emp.ename||'Salary:'||v_emp.sal); ENDLOOP; CLOSEcur_emp;END;

--Dynamically create tables and insert Tables

DECLAREv_sql_creatVARCHAR2( -):='CREATE TABLE Stuinfo (ID number (5), NAME VARCHAR2, Sex VARCHAR2 (5))'; V_into1VARCHAR2( -):='INSERT into Stuinfo VALUES (1,"'Zhang San"',"'male"')'; V_into2VARCHAR2( -):='INSERT into Stuinfo VALUES (2,"'John Doe"',"'female"')';BEGIN EXECUTEIMMEDIATE v_sql_creat;EXECUTEIMMEDIATE v_into1;EXECUTEIMMEDIATE V_into2;CommitEND;

--Dynamic increase

DECLAREv_id stuinfo.id%TYPE:=&ID; v_name stuinfo.name%TYPE:='&name'; V_sex Stuinfo.sex%TYPE:='&sex'; V_into1VARCHAR2( -):='INSERT into Stuinfo VALUES (: id,:name,:sex)';BEGIN EXECUTEIMMEDIATE v_into1 USING v_id,v_name,v_sex;COMMIT;END;

--Dynamic deletion

 declare   v_id stuinfo.id  %  type:=&  ID; V_sql_del  varchar2  ( 220 ): =   '  

--Dynamic update

--Dynamic UpdateDECLAREv_id stuinfo.id%TYPE:=&ID; v_name stuinfo.name%TYPE:='&name'; V_sex Stuinfo.sex%TYPE:='&sex'; V_sql_updateVARCHAR2( $):='UPDATE stuinfo SET name=:1,sex=:2 where Id=:3';BEGIN EXECUTEIMMEDIATE v_sql_update USING v_name,v_sex,v_id;IFSql%ROWCOUNT > 0  Then   COMMIT; Dbms_output.put_line ('OK'); END IF; EXCEPTION whenOTHERS ThenDbms_output.put_line (SQLERRM);END;--The binding order in the using is consistent with the order in which the statements are executedSELECT *  fromStuinfo;

--Dynamic Query

DECLAREStu Stuinfo%ROWTYPE, Cur_r sys_refcursor; v_id stuinfo.id%TYPE:=&ID; V_sql_selVARCHAR2( -):='SELECT * from Stuinfo where Id=:id';BEGIN OPENCur_r forV_sql_sel USING v_id; LOOPFETCHCur_r intoStu; EXIT  whenCur_r%NOTFOUND; Dbms_output.put_line (Stu.id||' '||Stu.name||' '||stu.sex); ENDLOOP; EXCEPTION whenOTHERS ThenDbms_output.put_line (SQLERRM);END;

Dynamic SQL (Learning notes)