在PL/SQL中只有DML SQL可以直接执行,使用Oracle内置的DBMS_SQL包,可以执行动态SQL语句(在运行时生成一个SQL的串,将该串提交给DBMS包来执行).
Data Manlpulation Language:DML
包括:
SELECT,INSERT,DELETE,SET TRANSACTION,EXPLAIN PLAN
Data Definition Language:DDL
包括:
DROP,CREATE,ALTER,GRANT,REVOKE
Demo示例:
DECLARE
v_sqlstr VARCHAR2(500);
v_id NUMBER;
v_col VARCHAR2(50);
BEGIN
v_sqlstr := 'CREATE TABLE lab_temp(id number,col varchar2(20))';
EXECUTE IMMEDIATE v_sqlstr;
/*插入
v_sqlstr='INSERT INTO lab_temp(:v1, :v2)';
FOR v_cnt 1..5 LOOP
EXECUTE IMMEDIATE v_sqlstr USING v_cnt, 'row '||v_cnt||' inserted';
END LOOP
*/
/*查询
v_sqlstr='SELECT * FROM lab_temp where id =:v1';
EXECUTE IMMEDIATE v_sqlstr INTO v_id,v_col USING 1;
DBMS_OUTPUT.PUT_LINE('ID: '||v_id|| 'CONTENT: '||v_col);
*/
END;
Data Manlpulation Language:DML
包括:
SELECT,INSERT,DELETE,SET TRANSACTION,EXPLAIN PLAN
Data Definition Language:DDL
包括:
DROP,CREATE,ALTER,GRANT,REVOKE
Demo示例:
DECLARE
v_sqlstr VARCHAR2(500);
v_id NUMBER;
v_col VARCHAR2(50);
BEGIN
v_sqlstr := 'CREATE TABLE lab_temp(id number,col varchar2(20))';
EXECUTE IMMEDIATE v_sqlstr;
/*插入
v_sqlstr='INSERT INTO lab_temp(:v1, :v2)';
FOR v_cnt 1..5 LOOP
EXECUTE IMMEDIATE v_sqlstr USING v_cnt, 'row '||v_cnt||' inserted';
END LOOP
*/
/*查询
v_sqlstr='SELECT * FROM lab_temp where id =:v1';
EXECUTE IMMEDIATE v_sqlstr INTO v_id,v_col USING 1;
DBMS_OUTPUT.PUT_LINE('ID: '||v_id|| 'CONTENT: '||v_col);
*/
END;