Oracle PL/SQL/SQL Plus/variable
Содержание
- 1 Bind variables can be declared in your SQL*Plus script,Preface a bind variable with a colon to reference it in a PL/SQL block
- 2 Bind variables can even be referenced by SQL queries
- 3 Define variable and print its value out
- 4 Define variable and set value
- 5 Define variable and use it in sql statement
- 6 refcursor varible
- 7 Save returning value from a function to a variable
- 8 Undefine variable
- 9 User defined-variable in update set statement
Bind variables can be declared in your SQL*Plus script,Preface a bind variable with a colon to reference it in a PL/SQL block
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SQL> VARIABLE s_table_name varchar2(30) SQL> SQL> SQL> BEGIN
2 :s_table_name := "emp"; 3 END; 4 /
PL/SQL procedure successfully completed. SQL>
</source>
Bind variables can even be referenced by SQL queries
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SQL> VARIABLE s_table_name varchar2(30) SQL> SQL> SQL> BEGIN
2 :s_table_name := "emp"; 3 END; 4 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> SELECT index_name
2 FROM user_indexes 3 WHERE table_name = :s_table_name;
no rows selected SQL> SQL> SET SERVEROUTPUT ON SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE(:s_table_name); 3 END; 4 /
emp PL/SQL procedure successfully completed. SQL>
</source>
Define variable and print its value out
<source lang="sql">
SQL> VARIABLE x NUMBER SQL> BEGIN
2 :x := 1; 3 END; 4 /
PL/SQL procedure successfully completed. SQL> PRINT :x;
X
1
SQL> SQL>
</source>
Define variable and set value
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SQL> VARIABLE ename VARCHAR2(30) SQL> EXECUTE :ename := "Mike"; PL/SQL procedure successfully completed. SQL> SELECT :ename FROM dual; Mike 1 row selected. SQL> SQL>
</source>
Define variable and use it in sql statement
<source lang="sql">
SQL> SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 startDate DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
Table created. SQL> SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7900, "E", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> CREATE TABLE DEPT(
2 DEPTNO NUMBER(2), 3 DNAME VARCHAR2(14), 4 LOC VARCHAR2(13) 5 );
Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> SQL> select empno, ename, sal from emp where deptno = 10;
EMPNO ENAME SAL
---------- ----------
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
SQL> variable empno number SQL> variable ename varchar2(20) SQL> variable sal number SQL> SQL> exec :empno := 7934; :ename := "MILLER"; :sal := 1300; PL/SQL procedure successfully completed. SQL> SQL> select empno, ename, sal
2 from emp
3 where empno = :empno
4 and ename = :ename
5 and sal = :sal
6 for update nowait
7 /
EMPNO ENAME SAL
---------- ----------
7934 MILLER 1300
SQL> SQL> SQL> drop table emp; Table dropped. SQL> drop table dept; Table dropped. SQL>
</source>
refcursor varible
<source lang="sql">
SQL> SQL> SQL> set echo on SQL> SQL> create table t as select * from all_users; Table created. SQL> SQL> variable x refcursor SQL> SQL> begin
2 open :x for select * from t; 3 end; 4 /
PL/SQL procedure successfully completed. SQL> SQL> delete from t; 14 rows deleted. SQL> SQL> commit; Commit complete. SQL> SQL> print x USERNAME USER_ID CREATED
---------- --------------------
sqle 44 13-JUN-2008 20:30:45 FLOWS_020100 35 07-FEB-2006 22:52:43 FLOWS_FILES 34 07-FEB-2006 22:52:43 HR 33 07-FEB-2006 22:51:21 MDSYS 32 07-FEB-2006 22:44:47 ANONYMOUS 28 07-FEB-2006 22:40:15 XDB 27 07-FEB-2006 22:40:14 CTXSYS 25 07-FEB-2006 22:38:38 DBSNMP 23 07-FEB-2006 22:35:21 TSMSYS 20 07-FEB-2006 22:27:15 DIP 18 07-FEB-2006 22:17:03 OUTLN 11 07-FEB-2006 22:10:24 SYSTEM 5 07-FEB-2006 22:10:13 SYS 0 07-FEB-2006 22:10:13 14 rows selected. SQL> SQL> drop table t; Table dropped. SQL> SQL> --
</source>
Save returning value from a function to a variable
<source lang="sql">
SQL> SQL> CREATE TABLE myStudent (
2 student_id NUMBER(5) NOT NULL, 3 department CHAR(3) NOT NULL, 4 course NUMBER(3) NOT NULL, 5 grade CHAR(1) 6 );
Table created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "CS", 102, "A");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "CS", 102, "B");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10003, "CS", 102, "C");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "HIS", 101, "A");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10001, "HIS", 101, "B");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "HIS", 101, "B");
1 row created. SQL> CREATE OR REPLACE FUNCTION AverageGrade (
2 p_Department IN myStudent.department%TYPE, 3 p_Course IN myStudent.course%TYPE) RETURN CHAR AS 4 v_AverageGrade CHAR(1); 5 v_NumericGrade NUMBER; 6 v_NumberStudents NUMBER; 7 8 CURSOR c_Grades IS 9 SELECT grade 10 FROM myStudent 11 WHERE department = p_Department 12 AND course = p_Course; 13 BEGIN 14 SELECT COUNT(*) 15 INTO v_NumberStudents 16 FROM myStudent 17 WHERE department = p_Department 18 AND course = p_Course; 19 20 IF v_NumberStudents = 0 THEN 21 RAISE_APPLICATION_ERROR(-20001, "No students registered for " || 22 p_Department || " " || p_Course); 23 END IF; 24 25 SELECT AVG(DECODE(grade, "A", 5, 26 "B", 4, 27 "C", 3, 28 "D", 2, 29 "E", 1)) 30 INTO v_NumericGrade 31 FROM myStudent 32 WHERE department = p_Department 33 AND course = p_Course; 34 35 SELECT DECODE(ROUND(v_NumericGrade), 5, "A", 36 4, "B", 37 3, "C", 38 2, "D", 39 1, "E") 40 INTO v_AverageGrade 41 FROM dual; 42 43 RETURN v_AverageGrade; 44 END AverageGrade; 45 /
Function created. SQL> SQL> SQL> VARIABLE v_AveGrade VARCHAR2(1) SQL> EXEC :v_AveGrade := AverageGrade("HIS", 101) PL/SQL procedure successfully completed. SQL> PRINT v_AveGrade V_AVEGRADE
B SQL> EXEC :v_AveGrade := AverageGrade("NUT", 307) BEGIN :v_AveGrade := AverageGrade("NUT", 307); END;
ERROR at line 1: ORA-20001: No students registered for NUT 307 ORA-06512: at "sqle.AVERAGEGRADE", line 21 ORA-06512: at line 1
SQL> PRINT v_AveGrade V_AVEGRADE
B SQL> EXEC :v_AveGrade := AverageGrade("MUS", 410) BEGIN :v_AveGrade := AverageGrade("MUS", 410); END;
ERROR at line 1: ORA-20001: No students registered for MUS 410 ORA-06512: at "sqle.AVERAGEGRADE", line 21 ORA-06512: at line 1
SQL> PRINT v_AveGrade V_AVEGRADE
B SQL> EXEC :v_AveGrade := AverageGrade("CS", 102) PL/SQL procedure successfully completed. SQL> PRINT v_AveGrade V_AVEGRADE
B SQL> SQL> SQL> SQL> SQL> drop table myStudent; Table dropped. SQL>
</source>
Undefine variable
<source lang="sql">
SQL> SQL> UNDEFINE user_name SQL> VARIABLE l_user VARCHAR2(30) SQL> EXECUTE :l_user := "&user_name"; Enter value for user_name: PL/SQL procedure successfully completed. SQL> SELECT "User " || :l_user || " has " || TO_CHAR(COUNT(*)) || " tables."
2 FROM all_tables 3 WHERE owner = UPPER(:l_user);
User has 0 tables. 1 row selected. SQL>
</source>
User defined-variable in update set statement
<source lang="sql">
SQL> SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 startDate DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
Table created. SQL> SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7900, "E", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> CREATE TABLE DEPT(
2 DEPTNO NUMBER(2), 3 DNAME VARCHAR2(14), 4 LOC VARCHAR2(13) 5 );
Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> SQL> select empno, ename, sal from emp where deptno = 10;
EMPNO ENAME SAL
---------- ----------
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
SQL> variable empno number SQL> variable ename varchar2(20) SQL> variable sal number SQL> SQL> exec :empno := 7934; :ename := "MILLER"; :sal := 1300; PL/SQL procedure successfully completed. SQL> update emp
2 set ename = :ename, sal = :sal 3 where empno = :empno;
1 row updated. SQL> commit; Commit complete. SQL> SQL> SQL> drop table emp; Table dropped. SQL> drop table dept; Table dropped. SQL>
</source>
