Just turned from SQL Server and half acquainted with oracle,
Excuse me the following written SQL server, oracle, how to write?
After first define variables and assignment, SQL queries can use this variable,

 DECLARE @ name varchar (20) 
The SET @ name='zhang' 

SELECT * FROM TableName WHERE NAME=@ NAME 

thank you

CodePudding user response:

 
Declare 
V_name varchar2 (20) :='zhang'; 
The begin 
Select * from tablename where name=v_name; 
end; 

CodePudding user response:

DECLARE
V_NAME EMP. Name % TYPE:='* *';
SQL_STMT VARCHAR2 (300);
The BEGIN
SQL_STMT:='SELECT * FROM tableName WHERE name=: 1'.
The EXECUTE IMMEDIATE SQL_STMT
USING V_NAME;
END;

CodePudding user response:

 DECLARE 
A number:=10; 
The BEGIN 
Dbms_output. Put_line (a + 10); 
END; 

CodePudding user response:

 DECLARE 
Sql_stmt VARCHAR2 (200); 
Emp_id NUMBER (4) :=1001; 
Salary NUMBER (7, 2); 
Dept_id NUMBER (2) :=6; 
Dept_name VARCHAR2 (13) :='1'; 
Locations VARCHAR2 (13) :='1' address; 
Emp_rec Scott % ROWTYPE; 
The BEGIN 
The EXECUTE IMMEDIATE 'CREATE TABLE bonus4 (id NUMBER, amt NUMBER)'; 
Sql_stmt:="INSERT INTO dept VALUES (: 1, 2, 3) '. 
The EXECUTE IMMEDIATE sql_stmt 
USING dept_id, dept_name, locations; 
Sql_stmt:='SELECT * FROM Scott where empno=: id'; 
The EXECUTE IMMEDIATE sql_stmt INTO emp_rec 
USING emp_id; 
Sql_stmt:='UPDATE Scott SET sal=2000 WHERE empno=: 1 
RETURN sal INTO: 2 '; 
The EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary; 

The EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno=: num' 
USING dept_id; 

END; 

CodePudding user response:

reference 1st floor w67995822 response:

 
Declare 
V_name varchar2 (20) :='zhang'; 
The begin 
Select * from tablename where name=v_name; 
end; 

The forehead,,, sorry oracle block can't use the select, and can only use a select into or dynamic statement, you can query into a record type, then the output record data,

CodePudding user response:

DECLARE
V_NAME EMP. Name % TYPE:='* *';
SQL_STMT VARCHAR2 (300);
The BEGIN
SQL_STMT:='SELECT * FROM tableName WHERE name=: 1'.
The EXECUTE IMMEDIATE SQL_STMT
USING V_NAME;
END;

CodePudding user response:

Use declare define variables