1. Use scenario
Both DBMS SQL package and EXECUTE IMMEDIATE in oracle can be used to parse and execute / dynamic SQL statements or PL/SQL blocks created at non runtime. In comparison, EXECUTE IMMEDIATE is relatively simple to use and can meet more common needs.
1.1 basic data
CREATE TABLE stu(
ID NUMBER(10),
xm VARCHAR2(10)
);
INSERT INTO student(SID, SNAME) VALUES(1, 'Little wanderer');2. Detailed explanation of execute immediate
2.1 three uses of static sql
DECLARE
v_sql VARCHAR2(2000);
v_xm system.stu.xm%TYPE;
v_num NUMBER;
BEGIN
-- usage1 : Immediate execution SQL Sentence.
v_sql := 'create or replace view view_stu as select id,xm from stu';
execute IMMEDIATE v_sql ;
-- Usage 2: execute immediately SQL Statement and assign to a variable
v_sql := 'select count(1) from stu';
EXECUTE IMMEDIATE v_sql INTO v_num;
dbms_output.put_line('Total number of records:'|| v_num);
-- Usage 3: immediate execution with parameters SQL Sentence
v_sql := 'select * from stu where id =: 1 and xm =: 2';
EXECUTE IMMEDIATE v_sql
USING 1, 'Little wanderer';
-- special* : Even if the statement is wrong, there will be no pop-up prompt (forced error), but there will be an error message
v_sql := 'select * from the world';
EXECUTE IMMEDIATE v_sql ; -- sqlerrm : Table or view does not exist
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END; 2.2 dynamic dml - explicit commit required
Note the character representation '' within the '' character (instead of the '' output by the shortcut key)
DECLARE
v_sql VARCHAR2(1000);
v_table VARCHAR2(30) := 'stu';
BEGIN
v_sql := 'insert into '||v_table||' (id, xm) values( :1, :2)' ; -- In this case, the sql End not required ';'
EXECUTE IMMEDIATE v_sql
USING 2, 'Xiao Qian Zi';
COMMIT; --Explicit submission required, Otherwise, it will be deemed not to submit
dbms_output.put_line('Execution complete!');
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;3,dbms_sql
1. The DBMS? SQL package provides an interface for executing dynamic SQL (including DDL and DML).
2. DBMS SQL defines an entity called cursor ID, which is a PL/SQL integer. With cursor ID, you can operate the cursor
Reference resources: https://www.cnblogs.com/zjfjava/p/7979633.html
3.1 DBMS_SQL.NATIVE
Constant, which is used to resolve the corresponding version of SQL. When the version is retrieved automatically, DBMS_SQL.NATIVE = 1