Dynamic SQL (learning notes) and dynamic SQL learning notes

Dynamic SQL (learning notes) and dynamic SQL learning notes

Dynamic SQL

Execute immediate dynamic SQL string [BUCK COLLECT] INTO custom variable, | record type

USING [IN | OUT | in out] bound parameters]

[RETURNING | RETURN] [bulk collect] INTO binding parameters

Example 1

-- Enter employee information based on the employee ID entered in the note

DECLAREv_ SQL _smst VARCHAR2 (200); -- defines the variable used to store the SQL statement v_emp emp % ROWTYPE; -- defines the variable v_id emp. empno % TYPE: = & empno; -- Define the IDBEGINv_ SQL _smst: = 'select * from emp where empno =: empno'; execute immediate v_ SQL _smst INTO v_emp USING v_id; dbms_output.put_line ('employee No.: '| v_id | 'name:' | v_emp.ename | 'position '| v_emp.job); END;

 

Dynamic Cursor returns multiple rows of data

-- Return employee information based on the input salary

DECLARE cur_emp SYS_REFCURSOR; -- defines the cursor v_sal emp. sal % TYPE: = & sal; -- defines the user input salary v_emp emp % ROWTYPE; -- defines the row variable begin open cur_emp FOR 'select * from emp where sal>: sal order by sal 'using v_sal; -- open the cursor and execute multiple SQL queries. The returned result is dbms_output.put_line ('salary higher than '| v_sal | 'Employees :'); loop fetch cur_emp INTO v_emp; exit when cur_emp % NOTFOUND; dbms_output.put_line ('employee ID: '| v_emp.empno | 'name:' | v_emp.ename | 'salary: '| v_emp.sal); end loop; CLOSE cur_emp; END;

 -- Dynamically create and insert tables

DECLARE v_ SQL _creat VARCHAR2 (220): = 'create TABLE stuinfo (ID NUMBER (5), NAME VARCHAR2 (20), sex VARCHAR2 (5) '; v_1_1 VARCHAR2 (220 ): = 'insert INTO stuinfo VALUES (1, ''zhang san'', ''male') '; v_1_2 VARCHAR2 (220): = 'insert INTO stuinfo VALUES (2, ''lily'', ''femal'') '; begin execute immediate v_ SQL _creat; EXECUTE IMMEDIATE v_1_1; EXECUTE IMMEDIATE v_1_2; commitEND;

-- Dynamic increase

DECLARE  v_id      stuinfo.id%TYPE:=&ID; v_name    stuinfo.name%TYPE:='&name'; v_sex     stuinfo.sex%TYPE:='&sex'; v_into1 VARCHAR2(220):='INSERT INTO stuinfo VALUES(:id,:name,:sex)';BEGIN EXECUTE IMMEDIATE 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):='delete from stuinfo where id=:id ';BEGIN EXECUTE IMMEDIATE v_sql_del USING v_id; EXCEPTION   WHEN OTHERS THEN     ROLLBACK;END;

-- Dynamic Update

-- Dynamically update DECLARE v_id stuinfo. id % TYPE: = & ID; v_name stuinfo. name % TYPE: = '& name'; v_sex stuinfo. sex % TYPE: = '& sex'; v_ SQL _update VARCHAR2 (200): = 'Update stuinfo SET name =: 1, sex =: 2 where id =: 3 '; begin execute immediate v_ SQL _update USING v_name, v_sex, v_id; if SQL % ROWCOUNT> 0 then commit; THEN ('OK'); END IF; EXCEPTION WHEN OTHERS THEN dbms_output.put_line (SQLERRM ); END; -- the binding sequence in USING must be consistent with the order in the execution statement SELECT * FROM stuinfo;

-- Dynamic query

DECLARE  stu      stuinfo%ROWTYPE; cur_r    SYS_REFCURSOR; v_id      stuinfo.id%TYPE:=&ID; v_sql_sel VARCHAR2(220):='select * from stuinfo where id=:id ';BEGIN OPEN  cur_r FOR v_sql_sel USING v_id; LOOP   FETCH cur_r INTO stu;   EXIT WHEN cur_r%NOTFOUND;   dbms_output.put_line(stu.id||' '||stu.name||' '||stu.sex); END LOOP; EXCEPTION   WHEN OTHERS THEN   dbms_output.put_line(SQLERRM);END;