в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T09:58:37Z

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==



<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>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/SQL Plus/variable - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия