• Author / Uploaded
• Anonymous 7r2OlOFV

Citation preview

Examples… execute anystring(‘create table emp(name varchar2 , age number(3), salary number(20))’);

10G PL/SQL

1

Using USING clause USING clause is used along with EXECUTE IMMEDIATE statement to pass parameter as Bind variable. Example: Declare Symbol varchar(4) := ‘WOND’; Begin Execute immediate ‘delete from stock where symbol = :symbol’ Using Symbol; End;

10G PL/SQL

2

Using RETURNING INTO clause RETURNING INTO clause allows us to return column values for rows affected by DML statements. Example: Declare Emp_id number := 1001; Salary_Bonus_percent number := 10; New_salary number; Begin Execute immediate ‘Update emp set salary = salary + (salary* : Salary_Bonus_percent ) where emp_id = :emp_id returning salary into :New_salary ’ using Salary_Bonus_percent , emp_id Returning into New_salary; End;

10G PL/SQL

3

Using DBMS_SQL Prior to Oracle 9i , Dynamic SQL required the use of DBMS_SQL package which gives greated control over the processing flow with in the Dynamic SQL, but it is generally more complicated to write than the native dynamic SQL. Create or replace procedure anystring(string IN varchar2) is Begin Cursor_name integer; Ret integer; Cursor_name := DBMS_SQL.open_cursor; DBMS_SQL.parse(cursor_name , string,DBMS_SQL.Native); Ret := DBMS_SQL.execute(cursor_name); DBMS_SQL.close_cursor(cursor_name); End;

10G PL/SQL

4

Using DBMS_SQL Execute anystring (‘drop table CD’); Execute anystring(‘create table CD (Artist varchar2(25).Title varchar2(25)); Execute anystring(‘Insert into CD values (‘’MLTR’’,’’Sleeping Child’’)’);

Using BIND_VARIABLE and BIND_ARRAY procedures: This procedures are used in passing parameter as Bind variables to dynamic sql statements.BIND_ARRAY is used to pass more than one parameter. Usage:-

10G PL/SQL

5

Using DBMS_SQL Usage:DBMS_SQL.Parse(cursor_name,’delete from CD where Artist = :artist’,DBMS_SQL.Native); DBMS_SQL.BIND_VARIABLE(cursor_name,’:artist’,artist_name); Where artist_name is a parameter which is already declared or passed from calling environment. Use BIND_ARRAY To pass more than one parameter. Usage:Declare cursor_name Integer := dbms_sql.open_cursor; Ret Integer; emp_num dbms_sql.number_table; emp_var dbms_sql.varchar2_table;

10G PL/SQL

6

Using DBMS_SQL begin dbms_sql.parse(cursor_name,'insert into emp(emp_id,emp_name) values ( :a, :b )',dbms_sql.native ); emp_num(1) := 1001; emp_num(2) := 1002; emp_var(1) := 'John'; emp_var(2) := 'Naveen'; dbms_sql.bind_array(cursor_name, ':a', emp_num ); dbms_sql.bind_array(cursor_name, ':b', emp_var ); Ret := dbms_sql.execute(cursor_name); dbms_sql.close_cursor(cursor_name); end;

10G PL/SQL

7

Using DBMS_SQL Using DEFINE_COLUMN function: If your cursor performs a query, you must execute this function once for each column being selected.Is it basically used to define the datatype and size of the variables that will receive data from the Fetch_Rows() function

If the column defined with LONG datatype , then DEFINE_COLUMN_LONG must be used. Usage:…. DBMS_SQL.parse(cursor_name, ’select artist,title from CD’, dbms_sql.native); DBMS_SQL.DEFINE_COLUMN(cursor_name,1,artist,25); DBMS_SQL.DEFINE_COLUMN(cursor_name,2,title,25);

….

10G PL/SQL

8

Using DBMS_SQL Using FETCH_ROWS:

This function fetches a single row of data into the local buffer. This data can then be stored in local variables by using the Column_Value() procedure. Ret := DBMS_SQL.FETCH_ROWS(cursor_name); Using COLUMN_VALUE: This procedure stores the fetched single row of data into local variables. DBMS_SQL.COLUMN_VALUE(cursor_name,1,artist); DBMS_SQL.COLUMN_VALUE(cursor_name,2,title);

10G PL/SQL

9

Using DBMS_SQL Using VARIABLE_VALUE:

… BEGIN cursor_name := dbms_sql.open_cursor; dbms_sql.parse(cursor_name, 'INSERT INTO emp VALUES (:empid,:salary,:bonus_percent) ' || 'returning salary+(salary*bonus_percent) into :new_salary', dbms_sql.native); dbms_sql.bind_variable(cursor_name, 'empid', v_empid); dbms_sql.bind_variable(cursor_name, 'salary', v_salary); dbms_sql.bind_variable(cursor_name, 'bonus_percent', v_bonus_percent); dbms_sql.bind_variable(cursor_name, 'new_salary', v_new_salary); n := dbms_sql.execute(c); dbms_sql.variable_value(cursor_name, 'new_salary', v_new_salary); -- get value of outbind dbms_sql.close_cursor(c); END;

10G PL/SQL

10