1. Variabler i SQL*Plus

På denna sida beskrivs hur du använder variabler i SQL*Plus. Variabler är särskilt användbara om du har ett script med kod som du vill köra många gånger och inte vill gå in och ändra i scriptet varje gång.

2. Skapa variabler med &

I Oracle kan det ibland vara bekvämt att kunna skapa variabler. Man gör detta genom att sätta ett & framför en sträng. När SQL*Plus tolkar satsen dyker en prompt upp där du ska fylla värdet för variablen. Se exempel:

SQL> SELECT * FROM emp WHERE ename='&name'; Ange värdet för name: SCOTT gammal 1: select * from emp where ename='&name' ny 1: select * from emp where ename='SCOTT' EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- ---------- ---------- ---------- 7788 SCOTT ANALYST 7566 1987-04-19 3000 SQL> / Ange värdet för name: KING gammal 1: select * from emp where ename='&name' ny 1: select * from emp where ename='KING' EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- ---------- ---------- ---------- 7839 KING PRESIDENT 1981-11-17 5000

Vi ser att man med hjälp av variabler kan köra samma SQL-sats om igen och få olika resultat utan att behöva ändra själva satsen. Fnuttarna i SQL-satsen kommer sig av att datatypen för ename är VARCHAR2. Om vi har en variabel av t.ex. NUMBER behövs inga fnuttar:

SQL> SELECT ename, deptno FROM emp WHERE deptno=&dep; Ange värdet för dep: 30 gammal 1: SELECT ename, deptno FROM emp WHERE deptno=&dep ny 1: SELECT ename, deptno FROM emp WHERE deptno=30 ENAME DEPTNO ---------- ---------- ALLEN 30 WARD 30 MARTIN 30 BLAKE 30 TURNER 30 JAMES 30 6 rader.

3. accept

Om man skriver ett script kanske man vill att utskriften av frågan ska vara lite snyggare, då kan man använda accept. Vi skapar skriptet get_emp.sql:

-- This script ask for input with accept accept var_name prompt 'Type the name of the employee:' SELECT * FROM emp WHERE ename='&var_name';

Vi testar scriptet:

SQL> @get_emp; Type the name of the employee:TURNER gammal 1: SELECT * FROM emp WHERE ename='&var_name' ny 1: SELECT * FROM emp WHERE ename='TURNER' EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- ---------- ---------- ---------- 7844 TURNER SALESMAN 7698 1981-09-08 1500 0

4. Skapa variabler med &&

Med dubbla och-tecken (&&) får man en variabel som minns det senaste värdet man skrev in. Vi testar detta:

SQL> SELECT * FROM emp WHERE deptno=&&var_dep AND sal > &&var_minsal; Ange värdet för var_dep: 10 Ange värdet för var_minsal: 2000 gammal 1: SELECT * FROM emp WHERE deptno=&&var_dep AND sal > &&var_minsal ny 1: SELECT * FROM emp WHERE deptno=10 AND sal > 2000 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ---------- ---------- ---------- --------- 7782 CLARK MANAGER 7839 1981-06-09 2450 10 7839 KING PRESIDENT 1981-11-17 5000 10

Än så länge är allt som vanligt. Men vad händer om vi nu skriver en ny SELECT-sats och använder samma variabelnamn var_dep som ovan:

SQL> SELECT ename, job, sal FROM emp WHERE deptno=&&var_dep; gammal 1: SELECT ename, job, sal FROM emp WHERE deptno=&&var_dep ny 1: SELECT ename, job, sal FROM emp WHERE deptno=10 ENAME JOB SAL ---------- --------- ---------- CLARK MANAGER 2450 KING PRESIDENT 5000 MILLER CLERK 1300

Vi ser att variabeln var_dep behåller sitt värde.

5. define

Ett annat sätt att få en variabel att minnas sitt värde är att sätta värdet med define. Vi skapar först ett script get_emp_row.sql:

-- This script contains a variable SELECT * FROM emp WHERE ename='&var_name';

Nu när vi anropar scriptet kan vi sätta variabeln med define:

SQL> define var_name=SCOTT SQL> @get_emp_row gammal 1: SELECT * FROM emp WHERE ename='&var_name' ny 1: SELECT * FROM emp WHERE ename='SCOTT' EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- ---------- ---------- ---------- 7788 SCOTT ANALYST 7566 1987-04-19 3000

Vi behövde inte skriva in något värde för variabeln var_name.

6. set define

Om man vill använda ampersand (&) till något annat i sina strängar, går det bra att definiera något annat tecken som variabelmarkör. Detta gör man med set define:

SQL> set define $ SQL> SELECT * FROM emp WHERE ename='$name'; Ange värdet för name: BLAKE gammal 1: SELECT * FROM emp WHERE ename='$name' ny 1: SELECT * FROM emp WHERE ename='BLAKE' EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- ---------- ---------- ---------- 7698 BLAKE MANAGER 7839 1981-05-01 2850

I exemplet ovan använder vi $ som variabelmarkör, men för att det inte ska bil så förvirrande återgår vi till ampersand:

SQL> set define &

7.

8.